現在この質問をフォロー中です
- フォローしているコンテンツ フィードに更新が表示されます。
- コミュニケーション基本設定に応じて電子メールを受け取ることができます。
Creating a new matrix based on matching two columns
2 ビュー (過去 30 日間)
古いコメントを表示
User
2023 年 10 月 17 日
I have generated two matrices from two different csv files. Matrix 1 contains two columns of information (matrix contain middle names and first name). Matrix 2 only contains one Column of information (middle name). I want to create a loop where I take the middle name from matrix 2 and match it to the middle names in matrix 1. Once the names have been matched I want to read out each matches corresponding first name in a new matrix
3 件のコメント
the cyclist
2023 年 10 月 17 日
Can you upload the data? You can use the paper clip icon in the INSERT section of the toolbar.
One thing that is confusing is that you refer to the data as "matrices", but in MATLAB matrices are numeric. So, it's not clear if you have cell arrays, or string arrays, or tables. Uploading the data is the easiest way for us to make sure a solution works for you.
Walter Roberson
2023 年 10 月 26 日
回答 (1 件)
Voss
2023 年 10 月 26 日
T1 = readtable('example1.xlsx');
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
T2 = readtable('example2.xlsx');
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
disp(T1);
firstName lastName
___________ ____________________
{'ben' } {'smith' }
{'sarah' } {'doe' }
{'rebecca'} {'martinez, garcia'}
{'lucy' } {'griffin' }
{'grace' } {'lockwood' }
{'sam' } {'sanchez' }
{'brian' } {'pina' }
{'stef' } {'ngyugen' }
{'maria' } {'lee' }
{'emily' } {'grady' }
{'giselle'} {'curry' }
{'andrea' } {'green, martinez' }
disp(T2);
lastName
____________
{'smith' }
{'green' }
{'miller' }
{'wilson' }
{'davis' }
{'allen' }
{'moore' }
{'cooper' }
{'adams' }
{'thompson'}
{'lopez' }
{'hill' }
{'adams' }
{'bailey' }
{'thatcher'}
{'raven' }
{'elsher' }
{'levine' }
{'brown' }
{'williams'}
{'anderson'}
{'wilson' }
{'gonzales'}
{'garcia' }
"I want to [...] take the [last] name from [array] 2 and match it to the [last] names in [array] 1. Once the names have been matched I want to read out each [match's] corresponding first name in a new [array]"
If you want exact matches:
result = T1{ismember(T1{:,2},T2{:,1}),1}
result = 1×1 cell array
{'ben'}
Or, if you want to split the cells where there are more than one last name separated by commas into multiple separate entries, then something like this:
% make a new table with only one first and last name per row:
C = regexp(T1{:,2},',\s*','split');
idx = arrayfun(@(x,c)x(ones(1,numel(c{1}))),1:numel(C),C.','un',0);
T1_new = table(T1{[idx{:}],1},[C{:}].')
T1_new = 14×2 table
Var1 Var2
___________ ____________
{'ben' } {'smith' }
{'sarah' } {'doe' }
{'rebecca'} {'martinez'}
{'rebecca'} {'garcia' }
{'lucy' } {'griffin' }
{'grace' } {'lockwood'}
{'sam' } {'sanchez' }
{'brian' } {'pina' }
{'stef' } {'ngyugen' }
{'maria' } {'lee' }
{'emily' } {'grady' }
{'giselle'} {'curry' }
{'andrea' } {'green' }
{'andrea' } {'martinez'}
% this part is the same as before with T1 but now using T1_new:
result = T1_new{ismember(T1_new{:,2},T2{:,1}),1}
result = 3×1 cell array
{'ben' }
{'rebecca'}
{'andrea' }
5 件のコメント
User
2023 年 11 月 1 日
How do I get the result to display the original last name as well? for instance I want to create a new array that has the matched first name ben with the respective last name smith?
Voss
2023 年 11 月 1 日
編集済み: Voss
2023 年 11 月 1 日
T1 = readtable('example1.xlsx');
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
T2 = readtable('example2.xlsx');
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
% exact matches only:
T_out = T1(ismember(T1{:,2},T2{:,1}),:)
T_out = 1×2 table
firstName lastName
_________ _________
{'ben'} {'smith'}
result = join(T_out{:,:},' ')
result = 1×1 cell array
{'ben smith'}
% matches any last name in given row of T1:
C = regexp(T1{:,2},',\s*','split');
idx = arrayfun(@(x,c)x(ones(1,numel(c{1}))),1:numel(C),C.','un',0);
T1_new = table(T1{[idx{:}],1},[C{:}].');
T_out = T1_new(ismember(T1_new{:,2},T2{:,1}),:)
T_out = 3×2 table
Var1 Var2
___________ __________
{'ben' } {'smith' }
{'rebecca'} {'garcia'}
{'andrea' } {'green' }
result = join(T_out{:,:},' ')
result = 3×1 cell array
{'ben smith' }
{'rebecca garcia'}
{'andrea green' }
User
2023 年 11 月 1 日
I am trying to repeat this process on this other sample data set but am running into issues. How do I match the Drug in the first file (drugID_names_matched)to the DrugIds in the second file(allpharm1) and if there is a match how do I output the respective DrugIDs name from the second file with its original Drug name from the first file?
Walter Roberson
2023 年 11 月 1 日
With the original last name is something I posted code for several days ago at https://www.mathworks.com/matlabcentral/answers/2034499-loading-in-a-table-that-has-multiple-values-in-a-single-cell-seperated-by-a-comma#comment_2938846
Voss
2023 年 11 月 1 日
@User: Does this produce the expected result? The only change is changing the comma to a semicolon in the regexp() call, since the IDs are separated by semicolons in the real data file (it was commas in the example files).
T1 = readtable('allPharm1.csv')
Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
T1 = 5258×2 table
Name DrugIDs
_______________________________________________________________ ___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{'Peptidoglycan synthase FtsI' } {'DB00303' }
{'Histidine decarboxylase' } {'DB00114; DB00117' }
{'Glutaminase liver isoform, mitochondrial' } {'DB00142' }
{'Coagulation factor XIII A chain' } {'DB02340; DB11300; DB11311; DB11571; DB13151' }
{'Nitric oxide synthase, inducible' } {'DB00125; DB00155; DB01017; DB01110; DB01234; DB01686; DB01835; DB01997; DB02044; DB02207; DB02234; DB02462; DB02644; DB03100; DB03144; DB03366; DB03449; DB03953; DB04400; DB04534; DB05214; DB05252; DB05383; DB06879; DB06916; DB07002; DB07003; DB07007; DB07008; DB07011; DB07029; DB07306; DB07318; DB07388; DB07389; DB07405; DB08214; DB08750; DB08814; DB09237; DB11327; DB14649' }
{'Estradiol 17-beta-dehydrogenase 2' } {'DB00157; DB13952; DB13953; DB13954; DB13955; DB13956' }
{'NAD(P) transhydrogenase, mitochondrial' } {'DB00157; DB01763; DB03461; DB09092' }
{'Alcohol dehydrogenase class-3' } {'DB00157; DB03017; DB03704; DB04153' }
{'Aminomethyltransferase, mitochondrial' } {'DB00116; DB00157; DB04789' }
{'Isocitrate dehydrogenase [NAD] subunit alpha, mitochondrial'} {'DB00157; DB06757; DB09092; DB09130' }
{'Voltage-dependent T-type calcium channel subunit alpha-1I' } {'DB00381; DB00568; DB00617; DB00909; DB01118; DB01388; DB04841; DB06152; DB09061; DB09235; DB14009; DB14011' }
{'Adenosine receptor A1' } {'DB00193; DB00201; DB00277; DB00555; DB00640; DB00651; DB00806; DB00824; DB00996; DB01223; DB01303; DB01412; DB04932; DB04954; DB06471; DB09061; DB11757; DB12569; DB12670' }
{'Tyrosine-protein kinase ABL1' } {'DB00171; DB00619; DB01254; DB03878; DB04868; DB05184; DB06616; DB07831; DB08043; DB08231; DB08339; DB08350; DB08583; DB08896; DB08901; DB12010; DB12267; DB12323' }
{'High affinity immunoglobulin epsilon receptor subunit alpha'} {'DB00043; DB00895; DB05797' }
{'Coagulation factor VIII' } {'DB00055; DB00100; DB06050; DB11300; DB11312; DB11571; DB11572; DB12872; DB13133; DB13151; DB13152; DB13933; DB14700' }
{'Prostaglandin G/H synthase 1' } {'DB00154; DB00159; DB00244; DB00316; DB00328; DB00350; DB00461; DB00465; DB00469; DB00500; DB00554; DB00573; DB00586; DB00605; DB00711; DB00712; DB00749; DB00784; DB00788; DB00795; DB00812; DB00814; DB00821; DB00861; DB00870; DB00936; DB00939; DB00945; DB00963; DB00991; DB01009; DB01014; DB01050; DB01283; DB01397; DB01399; DB01401; DB01419; DB01435; DB01600; DB01837; DB01892; DB02047; DB02110; DB02198; DB02266; DB02379; DB02709; DB02773; DB03667; DB03752; DB03753; DB03783; DB04552; DB04557; DB04817; DB06725; DB06736; DB06802; DB07981; DB07983; DB07984; DB08814; DB09061; DB09212; DB09213; DB09214; DB09215; DB09216; DB09288; DB09295; DB11071; DB11079; DB11201; DB11323; DB12445; DB13346; DB13501; DB13783; DB14009; DB14011'}
T2 = readtable('drugID_names_matched_CCM.csv')
T2 = 188×2 table
Drug Target
___________ ________________________________________
{'DB00112'} {'Vascular endothelial growth factor A'}
{'DB01017'} {'Vascular endothelial growth factor A'}
{'DB01120'} {'Vascular endothelial growth factor A'}
{'DB01136'} {'Vascular endothelial growth factor A'}
{'DB01270'} {'Vascular endothelial growth factor A'}
{'DB03088'} {'Vascular endothelial growth factor A'}
{'DB05294'} {'Vascular endothelial growth factor A'}
{'DB05434'} {'Vascular endothelial growth factor A'}
{'DB05890'} {'Vascular endothelial growth factor A'}
{'DB05932'} {'Vascular endothelial growth factor A'}
{'DB05969'} {'Vascular endothelial growth factor A'}
{'DB06642'} {'Vascular endothelial growth factor A'}
{'DB06779'} {'Vascular endothelial growth factor A'}
{'DB08885'} {'Vascular endothelial growth factor A'}
{'DB09301'} {'Vascular endothelial growth factor A'}
{'DB10772'} {'Vascular endothelial growth factor A'}
C = regexp(T1{:,2},';\s*','split');
idx = arrayfun(@(x,c)x(ones(1,numel(c{1}))),1:numel(C),C.','un',0);
T1_new = table(T1{[idx{:}],1},[C{:}].');
T_out = T1_new(ismember(T1_new{:,2},T2{:,1}),:)
T_out = 2099×2 table
Var1 Var2
_______________________________________________________________ ___________
{'Nitric oxide synthase, inducible' } {'DB01017'}
{'Nitric oxide synthase, inducible' } {'DB03144'}
{'Alcohol dehydrogenase class-3' } {'DB03017'}
{'Isocitrate dehydrogenase [NAD] subunit alpha, mitochondrial'} {'DB06757'}
{'Isocitrate dehydrogenase [NAD] subunit alpha, mitochondrial'} {'DB09130'}
{'Tyrosine-protein kinase ABL1' } {'DB01254'}
{'Tyrosine-protein kinase ABL1' } {'DB05184'}
{'Tyrosine-protein kinase ABL1' } {'DB06616'}
{'Tyrosine-protein kinase ABL1' } {'DB08231'}
{'Tyrosine-protein kinase ABL1' } {'DB08896'}
{'Tyrosine-protein kinase ABL1' } {'DB08901'}
{'Tyrosine-protein kinase ABL1' } {'DB12010'}
{'30S ribosomal protein S4' } {'DB01017'}
{'Vascular endothelial growth factor receptor 3' } {'DB00398'}
{'Vascular endothelial growth factor receptor 3' } {'DB05932'}
{'Vascular endothelial growth factor receptor 3' } {'DB08896'}
result = join(T_out{:,:},' ')
result = 2099×1 cell array
{'Nitric oxide synthase, inducible DB01017' }
{'Nitric oxide synthase, inducible DB03144' }
{'Alcohol dehydrogenase class-3 DB03017' }
{'Isocitrate dehydrogenase [NAD] subunit alpha, mitochondrial DB06757'}
{'Isocitrate dehydrogenase [NAD] subunit alpha, mitochondrial DB09130'}
{'Tyrosine-protein kinase ABL1 DB01254' }
{'Tyrosine-protein kinase ABL1 DB05184' }
{'Tyrosine-protein kinase ABL1 DB06616' }
{'Tyrosine-protein kinase ABL1 DB08231' }
{'Tyrosine-protein kinase ABL1 DB08896' }
{'Tyrosine-protein kinase ABL1 DB08901' }
{'Tyrosine-protein kinase ABL1 DB12010' }
{'30S ribosomal protein S4 DB01017' }
{'Vascular endothelial growth factor receptor 3 DB00398' }
{'Vascular endothelial growth factor receptor 3 DB05932' }
{'Vascular endothelial growth factor receptor 3 DB08896' }
{'Vascular endothelial growth factor receptor 3 DB09079' }
{'Vascular endothelial growth factor receptor 3 DB12010' }
{'Vascular endothelial growth factor receptor 1 DB00398' }
{'Vascular endothelial growth factor receptor 1 DB05932' }
{'Vascular endothelial growth factor receptor 1 DB08896' }
{'Vascular endothelial growth factor receptor 1 DB09079' }
{'Vascular endothelial growth factor receptor 1 DB09221' }
{'Vascular endothelial growth factor receptor 1 DB12010' }
{'Insulin receptor DB12010' }
{'RAF proto-oncogene serine/threonine-protein kinase DB00398' }
{'RAF proto-oncogene serine/threonine-protein kinase DB04973' }
{'RAF proto-oncogene serine/threonine-protein kinase DB05190' }
{'RAF proto-oncogene serine/threonine-protein kinase DB05268' }
{'RAF proto-oncogene serine/threonine-protein kinase DB08862' }
参考
カテゴリ
Help Center および File Exchange で Tables についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!エラーが発生しました
ページに変更が加えられたため、アクションを完了できません。ページを再度読み込み、更新された状態を確認してください。
Web サイトの選択
Web サイトを選択すると、翻訳されたコンテンツにアクセスし、地域のイベントやサービスを確認できます。現在の位置情報に基づき、次のサイトの選択を推奨します:
また、以下のリストから Web サイトを選択することもできます。
最適なサイトパフォーマンスの取得方法
中国のサイト (中国語または英語) を選択することで、最適なサイトパフォーマンスが得られます。その他の国の MathWorks のサイトは、お客様の地域からのアクセスが最適化されていません。
南北アメリカ
- América Latina (Español)
- Canada (English)
- United States (English)
ヨーロッパ
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
アジア太平洋地域
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)