Compare two excel files and generate a new one that contains all columns

Hello everyone,

I have two excel files. They are filled out with numbers and text. The first file has 6 columns, say (a, b, c, d, e, f), while the second file has one column called "g" in addition to some similar columns like (a, b, c) , i.e, second files has 4 columns (a, b, c, g).

I would like to go through each row of the second file, figure out it is equivalent with which row of the first file (which row in the first file has the same values in columns (a, b, c)), and then add the value in column "g" to the first file and generate a new excel with 7 columns (a,b,c,d,e,f,g).

Any input would be greatly appreciated! Thanks!

[dpb]

By any chance are the columns named and are the names consistent between workbooks? That would be the simplest and most convenient. If not and have to match data, then it gets a little more complex if aren't all numeric or text.

Perhaps attaching a couple of small sample files to illustrate the content would be easiest for folks to work with rather than just taking a stab at it...

[Susan]

Thanks for your reply. Yes, the columns are named and the names consist between workbooks. I have attached 2 files to illustrate the content.

1st file has several columns, among those 3 of them are common between 2 files. The second file has only 4 columns and I would like to add the 4th column to the first file accordingly.

There is a chance that in one row, we cannot match the data in 3 coulmns of two files and I would like to figure out in which row it may happen.

Any help would be greatly appreciated. Thanks!

Answer 1

[Cris LaPierre]

Sounds like you want to join tables using a,b, and c as your key variables. I would use readtable to import the two spreadsheets into MATLAB (or the import tool) as tables, and then use the interactive Join Tables task in a live script to generate the correct output.

[Susan]

Thanks for your reply! I will check the "Join tabels task" out.

[Susan]

As you said, I have been using readtable to import import the two spreadsheets into MATLAB as tables and then using the interactive Join Tables task in a live script to generate the correct output.

I have three columns in common between two tables (as attached above). 1st tabel= 53*256 and 2ntable = 39*4. If I use

JoinedData = innerjoin(1stFile, 2ndFile, 'Keys', {'pid'})

the result ia a 2*259 table. I have title_1stFile, title_2ndFile, ... as columns. Since the titles should be the same, I expect to have only one "title" column. How can I do that?

If I use

JoinedData = innerjoin(FirstFile, SecondFile, 'Keys', {'pid', 'title','abstract'})

result is a 0*257 empty table.

Is there anyway that I can mark the rows that have same 'pid' (or title/abstract) but different title/ abstract (pid)? I would like to keep the 1st table and add the 4th column of 2nd table to it, i.e, the out put is a 53*257 table. This new column can has a value (from 2nd table) or can be empty (if there is no match between two tables).

Thanks!

[Cris LaPierre]

You've got some interesting formatting going on in those files, which made importing the data a little less automatic. Here's some code that I think does what you want. The issue I encountered is that there are differences in the titles/abstracts, so it wasn't able to merge those fields. That meant I had to do some hacking. In the end, it only found two entries on both tables.

% Set up import options for file1
opts1 = detectImportOptions("1stFile.csv");
opts1.VariableNames=["pid","Var2","title","doi","pmcid","pubmed_id","license","abstract","Var9","authors","Var11","MicrosoftAcademicPaperID"];
opts1.SelectedVariableNames=["pid", "title", "doi", "pmcid", "pubmed_id", "license", "abstract", "authors", "MicrosoftAcademicPaperID"];
opts1.VariableTypes = ["categorical","double","string","categorical","categorical","categorical","categorical","string","double","string","double","string"];
opts1.DataLines = [10,inf];
opts1.ExtraColumnsRule = "ignore";

data1 = readtable("1stFile.csv",opts1);

% Set up import options for file2
opts2 = detectImportOptions("2ndfile.csv");
opts2.VariableNames=["pid","title","abstract","affiliations"];
opts2.VariableTypes = ["categorical","string","string","string"];
opts2.EmptyLineRule = "skip";

data2 = readtable("2ndFile.csv",opts2);
data2 = rmmissing(data2,"MinNumMissing",4);

% Create table of shared data. Use title and abstract from left table
[joinedData,iL,iR] = outerjoin(data1,data2(:,[1,4]),'Type','left','Keys','pid',...
    'MergeKeys',true)

% Now add data unique to data2
data2(iR(iR>0),:)=[];
joinedDataFinal = outerjoin(joinedData,data2,"MergeKeys",true)

[Susan]

Thank you very much for your help.I really appreciate it.

When I execute your code, I end up at a weird point that I don't understand why it is the case. The code is executed in a sec or less and generates data 1 and then it takes forever to produce data2. However, if I run just the portion of code related to data2, i.e.,

% Set up import options for file2
opts2 = detectImportOptions("2ndfile.csv");
opts2.VariableNames=["pid","title","abstract","affiliations"];
opts2.VariableTypes = ["categorical","string","string","string"];
opts2.EmptyLineRule = "skip";
data2 = readtable("2ndFile.csv",opts2);
data2 = rmmissing(data2,"MinNumMissing",4);

it generates a result in less than a second. Any idea? Thanks again!

[Cris LaPierre]

It runs quickly for me. You have about 2000 extra rows of data in this file (delimited, but no entries). Perhaps it is struggling with that? Try removing various lines to see if you can identify which one is causing the problem.

The only real options are the 1st, 4th and 6th. You might not need the 4th anyway. The last line just gets rid of the extra rows added to the table.

[Susan]

Thanks for the info. Sure, I will. Thanks again!