How could I match data from two files?
Show older comments
I have two .txt files. The first column of a FILE 1 contains some of the strings/ names that also exist in the first column of the FILE 2. In FILE 2 for each item / name corresponds a number in the same order. I want for each element of the first column of file 1 to find the number of column 2 of file 2.
[For example I want the first cell of FILE1 ("B") to correspont to number 56 of FILE2 etc - I am uploading these files]
Could you please help me ?
I am importing also the final file I want to create (FINAL.txt)
Accepted Answer
6 Comments
Ivan Mich
on 8 Mar 2021
This error occurs because not all of the strings in FILE1.txt occur in FILE2.txt:
T1 = readtable('FILE1.txt', 'ReadVariableNames',false);
T2 = readtable('FILE2.txt', 'ReadVariableNames',false);
[X,Y] = ismember(T1.Var1,T2.Var1);
find(~X)
T1(~X,:) % these strings are not in FILE2.txt
Because you did not define how to handle missing strings, I did not include any such handling into my answer. Do you want to ignore them, or replace them with some default value, or some other action?
PS: it looks like you have used Excel to manipulate those data, and Excel has automatically converted "JAN3" (in FILE1.txt)
..
HER3
ITC1
JAN3 % looks okay!
KAC1
KAL3
..
into "3-Jan" (in FILE2.txt):
..
GRE3 170.22
HER2 546.6
HER3 545.34
ITC1 32.75
3-Jan 110.36 % Ooops, what happened here?
KAC1 101.17
KAL3 223.89
KAR2 137.4
..
What your files show exactly matches what is described here:
I have spent many years for a large multinational fighting Excel on exactly the issue of how if automagically imports, identifies, and converts data based on locale date formats and other number formats. Unfortunately there is no way to turn off this "feature", as many engineers and scientists have discovered.
Avoid any strings or short sequences of numbers and delimiters that could possibly be identified as date/number. Ensuring reliable importing of short strings over multiple locales is very very challenging (read: not really feasible).
Summary: if you want reliable data processing which does not alter your data without warning, avoid Excel.
Ivan Mich
on 8 Mar 2021
Stephen23
on 8 Mar 2021
"..is there a way to add not only the first column of file 2 , but also the rest of columns?"
The code I gave in my answer already saves all of the columns of FILE2.txt.
"I mean I want to add all the three columns of file 1 (not only the first column) to my final file.":
It is not clear to me how you will "add" 3 columns to 24 columns to get 24 columns.
If you want to compare 3 columns of the tables, use indexing as required to select the required inputs to ismember (which operates by row for table inputs).
Do you want to compare only the first column (as your question stated), or compare the first three columns?
This solution compares the the first column only:
T1 = readtable('FILE1.txt', 'ReadVariableNames',false);
T1.Properties.VariableNames(2:3) = {'Num1','Num2'}
Note that your new FILE2.txt contains numeric data stored as char (also common with Excel):
T2 = readtable('FILE2.txt', 'ReadVariableNames',false)
Tout = join(T1,T2)
writetable(Tout, 'OutFile.txt', 'WriteVariableNames',false)
More Answers (0)
Categories
Find more on Workspace Variables and MAT Files in Help Center and File Exchange
