6

I have two tables with different numbers of columns and rows. I want to find rows using a common column (column B is in common). Here is an example. Could you please help?

file1.txt

A  B    C   D
a  b    c   d
i  ii  iii iV
*  **   #  ##

file2.txt

E  B  
f  ff 
h  b
g  gg
k  ii

output:

A  B    C   D  E
a  b    c   d  h
i  ii  iii iV  k
  • 2
    Could you explain what you are doing in a bit more detail please? Why is the last line of file1 not in the output? And why is column B unchanged in the output while column E is added? – terdon Dec 17 '18 at 11:59
  • @terdon: I have two tables including some parameters of many galaxies. Assume table one include "mass" , "name","color" and "distance" of galaxies and table 2 include "velocity" and "name". . some galaxies are in common so have same name (column B). Now, I want to have all parameters for this galaxies in one table. – Negar Dec 17 '18 at 12:46
7

You can do this kind of thing by building a hash / associative array / lookup table e.g. using Awk:

$ awk 'NR==FNR{B[$2]=$1; next} $2 in B {print $0,B[$2]}' file2.txt file1.txt
A  B    C   D E
a  b    c   d h
i  ii  iii iV k

There's also the join command - but that requires inputs to be sorted on the common field.

  • It works. Thanks, but it doesn't print all columns of file1. could you please tell me how can I print all columns? – Negar Dec 17 '18 at 15:11
  • @Negar it should print all of the original columns from file1.txt (since $0 in Awk represents the whole record) - is there something additional about your actual files that you forgot to mention? – steeldriver Dec 17 '18 at 15:26
  • @ steeldriver It works right. I made a small mistake. Thanks – Negar Dec 18 '18 at 0:55
5

To add to the answer by @steeldriver, to do it with sort and join:

join -j 2 -o 1.1,0,1.3,1.4,2.1 <(sort -k 2 file1.txt)  <(sort -k 2 file2.txt)
  • -j 2 tells join which field is the key.
  • -o gives the order of the fields in the output, where 0 is the common key, and the others are FILENUM.FIELD. See man join for details.
  • -k 2 tells sort which field is the key.
  • <( ) is bash process substitution.

Output is:

a b c d h
A B C D E
i ii iii iV k
  • 1
    You may find that the column header position can be preserved by enforcing an appropriate locale e.g. LC_COLLATE=C sort ... – steeldriver Dec 17 '18 at 14:16
  • @Christoffer Hammarström: Thanks. could you please explain about 1.1, 0 ,1.3 ,... since my file1 has 13 columns and this command don't print all of them. – Negar Dec 17 '18 at 15:13
  • @Negar: Edited. See man join for further details. – Christoffer Hammarström Dec 17 '18 at 15:17

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.