This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Find Matched records in Multiple Files based on first column

I have three files with multiple columns but the I want to find the common first column entries. The desired output is at the end of the post. The data entries in files are displayed below.

First file
"OG0000000"
"OG0000001"
"OG0000003"
"OG0000004"
"OG0000005"

second file
"OG0000000"
"OG0000003"
"OG0000004"
"OG0000005"
"OG0000006"


Third file
"OG0000000"
"OG0000004"
"OG0000005"
"OG0000006"
"OG0000007"

output file
"OG0000000"
"OG0000004"
"OG0000005"
awk
• 2,099 views • link
updated by shenwei356 shenwei356 • written by khanirfanbioinformatics • khanirfanbioinformatics

I want to find the common first column entries

That means there are more columns, not shown in your example?

• 0 views • link
written by WouterDeCoster WouterDeCoster

Yes there are multiple columns but I want to take out the list of the first column in a separate file so that I can grep them from individual files later.

• 0 views • link
written by khanirfanbioinformatics • khanirfanbioinformatics

So it's not a problem to lose the other columns now? In that case, something like this: (not tested)

cat <(cut -f1 file1.txt) <(cut -f1 file2.txt) | sort | uniq -d > file12.common.txt
cat file12.common.txt <(cut -f1 file3.txt) | sort | uniq -d > file123.common.txt

But this assumes your file is tab delimited.

• 0 views • link
written by WouterDeCoster WouterDeCoster

2 answers

I'll give you a quick but 'dirty' solution:

cat file1 file2 fil3 | cut -f1 | sort |  uniq -c | grep "3 " |  cut -d "\"" -f2
• 0 views • link
written by Santosh Anand Santosh Anand

If you sort uniq first column from each file separetely first, then this is great idea, otherwise it assumes each file has only single entry for every element in the first columns. So if the first file was:

"OG0000000"
"OG0000001"
"OG0000003"
"OG0000003"
"OG0000005"

Instead. Your command line would output the result below, right?

"OG0000000"
"OG0000003"
"OG0000005"
• 0 views • link
written by Petr Ponomarenko Petr Ponomarenko

Sure. That's why it's a quick n "dirty" solution. No bioinformatician will hard code grep "3 " in his/her program :)

• 0 views • link
written by Santosh Anand Santosh Anand

One command using csvtk for tab-delimited files. Usage of csvtk inter.

$ csvtk inter --no-header-row --tabs file*
OG0000000
OG0000004
OG0000005
• 0 views • link
written by shenwei356 shenwei356

Log in to answer this question.

More posts like this