Subset a data based on column values with column values from another data and merge in Bash

user_8408726 0
Viewed 94

I have two data and I want to subset data1 based on another data2's column value and merge, my data1 looks like:

ID   address   phone
123 ....      .....
456 ....      .....
789 ....      .....
101 ....      .....

and data2 looks like:

ID   City     Zipcode   if_travel
123  ....     ....        ....
456  ....     ....        ....

I hope to get data like:

ID   address   phone   City     Zipcode   if_travel
123  ....      .....   ....     ....        ....
456  ....      .....   ....     ....        ....
789  ....      .....    NA       NA          NA
101  ....      .....    NA       NA          NA

The process seems to alike left-join in python, yet is there any way to do the same process in bash command? Thanks!

dataframe join linux bash
1 Answers

There are lots of ways to do this; here's one: 

join -o '0,1.2,1.3,2.2,2.3,2.4' -a1 -e 'NA' <(sort file1.txt) <(sort file2.txt) | awk '{printf "%-7s %-7s %-7s %-7s %-7s %7s\n",$1,$2,$3,$4,$5,$6}' | sort -nk1

input :

file 1:

ID   address phone
123 jordan   123
456 usa      144
789 bla      606
101 bla      1616

file 2 :

ID   City Zipcode if_travel
123  amman 2222   yes
456  zarqa 3030   no

output :

ID      address phone   City    Zipcode if_travel
101     bla     1616    NA      NA           NA
123     jordan  123     amman   2222        yes
456     usa     144     zarqa   3030         no
789     bla     606     NA      NA           NA
user_129573400
Related