filter csv file based on list of id in another file

Question

I have a large csv file with these format file 1:

id,ICD,CT
16214497,008.8,1
16227244,401.1,45
27154870,780.79,1

I have another txt file with one column of list of id (header id). file 2:

id
16214497
27154870

I want another file that has only data limited to the list of id in file 2

output file:

id,ICD,CT
16214497,008.8,1
27154870,780.79,1

Answer 1

If the files have the common lines in the same order,

join -t, -j1 file1.txt file2.txt

Explanation

See man join for more information. Basically, join the two files, skipping "unpairable lines". The delimiter is a comma -t,, and join on field 1 -j1.

Answer 2

Using csvjoin from the Python-based csvkit

$ csvjoin -Ic id file\ 1 file\ 2
id,ICD,CT
16214497,008.8,1
27154870,780.79,1

The -I flag disables type inference so that fields like 008.8 are treated as strings rather than as numerics (which may cause re-formating). This method appears to handle DOS/Windows style line endings transparently.

---

Alternative quick'n'dirty join using awk:

awk -F, 'NR==FNR {a[$1]; next} $1 in a' file\ 2 file\ 1

If your file has DOS/Windows line endings (CRLF in place of plain LF) then you can try

awk -vRS='\r\n' -F, 'NR==FNR {a[$1]; next} $1 in a' file\ 2 file\ 1

or convert your file first using dos2unix, tr, sed etc.

Answer 3

With grep and sed:

You could add a ^ to the beginning of each line and a , to the end of each line of file2 and use grep:

grep -f <(sed 's/^/^/;s/$/,/' file2) file1

Option -f reads the patterns from a file (in our example not from a real file, but from a process substitution <(…)).