CODEWITHSUNDEEP
bashawk
Vahid
Vahid

Reputation: 47

How to merge two files into a unique file based on 2 columns of each file

I have two tab delimitated fires as follow:
File1: 

cg00000292  0.780482425 chr1    10468   10470
cg00002426  0.914482257 chr3    57757816    57757817
cg00003994  0.017355388 chr1    15686237    15686238
cg00005847  0.065539061 chr1    176164345   176164346
cg00006414  0.000000456 chr7    10630   10794
cg00007981  0.018839033 chr11   94129428    94129429
cg00008493  0.982994402 chr3    10524   10524
cg00008713  0.018604172 chr18   11980954    11980955
cg00009407  0.002403351 chr3    88824577    88824578

File2: 

chr1    10468   10470   2   100 78  0.780
chr1    10483   10496   4   264 244 0.924
chr3    10524   10524   1   47  44  0.936
chr1    10541   10541   1   64  50  0.781
chr3    10562   10588   5   510 480 0.941
chr1    10608   10619   3   243 231 0.951
chr7    10630   10794   42  5292    5040    0.952
chr1    10810   10815   3   135 102 0.756

I want to merge these two files in a unique file if both values in columns 3 and 4 of file1 are equal to columns 1 and 2 of file2 and to keep all columns of file2 plus column 2 of file1.
output like this: 

chr1    10468   10470   2   100 78  0.780   0.780482425  
chr3    10524   10524   1   47  44  0.936   0.982994402  
chr7    10630   10794   42  5292    5040    0.952   0.000000456

Thank you so much,
Vahid.

I tried this awk command: 

awk 'NR==FNR{a[$3,$4]=$1OFS$2;next}{$6=a[$1,$2];print}' file1.tsv file2.tsv

Bu it does not give me the unique output I an looking for and the out put is a combination of both files like this: 

chr1 10468 10470 2 100 cg00000292 0.780482425 0.78                                                                      
chr1 10483 10496 4 264  0.924                                                                                           
chr3 10524 10524 1 47 cg00008493 0.982994402 0.936                                                                      
chr1 10541 10541 1 64  0.781                                                                                            
chr3 10562 10588 5 510  0.941                                                                                           
chr1 10608 10619 3 243  0.951                                                                                           
chr7 10630 10794 42 5292 cg00006414 0.000000456 0.952                                                                   
chr1 10810 10815 3 135  0.756

Upvotes: 0

Views: 226

Answers (2)

Shawn
Shawn

Reputation: 52539

The basic idea here to to read the first file, and using each line's third and fourth columns as a key, save the second column in an array. Then for each line in the second file, if its first two columns were seen in the first file, print that line and the saved second column of the first file.

$ awk 'BEGIN{ FS=OFS="\t" }
       NR==FNR { seen[$3,$4]=$2; next }
       ($1,$2) in seen { print $0, seen[$1,$2] }' file1.tsv file2.tsv
chr1    10468   10470   2   100 78  0.780   0.780482425
chr3    10524   10524   1   47  44  0.936   0.982994402
chr7    10630   10794   42  5292    5040    0.952   0.000000456

Upvotes: 1

KamilCuk
KamilCuk

Reputation: 141503

# I want to merge these two files in a unique file 
# if both values in columns 3 and 4 of file1
# are equal to columns 1 and 2 of file2 
# and to keep all columns of file2 plus column 2 of file1.
join -t$'\t' -11 -21 -o2.2,2.3,2.4,2.5,2.6,2.7,2.8,1.3 <(
  <file1 awk -vFS=$'\t' -vOFS=$'\t' '{ print $3 $4,$0 }' |
  sort -t$'\t' -k1,1
) <(
  <file2 awk -vFS=$'\t' -vOFS=$'\t' '{ print $1 $2,$0 }' |
  sort -t$'\t' -k1,1
)
  1. First preprocess the files and extract the fields you want to join on.
  2. Sort and join
  3. Specify the output format to join.

Tested on repl against:

# recreate input files
tr -s ' ' <<EOF | tr ' ' '\t' >file1
cg00000292  0.780482425 chr1    10468   10470
cg00002426  0.914482257 chr3    57757816    57757817
cg00003994  0.017355388 chr1    15686237    15686238
cg00005847  0.065539061 chr1    176164345   176164346
cg00006414  0.000000456 chr7    10630   10794
cg00007981  0.018839033 chr11   94129428    94129429
cg00008493  0.982994402 chr3    10524   10524
cg00008713  0.018604172 chr18   11980954    11980955
cg00009407  0.002403351 chr3    88824577    88824578
EOF
tr -s ' ' <<EOF | tr ' ' '\t' >file2
chr1    10468   10470   2   100 78  0.780
chr1    10483   10496   4   264 244 0.924
chr3    10524   10524   1   47  44  0.936
chr1    10541   10541   1   64  50  0.781
chr3    10562   10588   5   510 480 0.941
chr1    10608   10619   3   243 231 0.951
chr7    10630   10794   42  5292    5040    0.952
chr1    10810   10815   3   135 102 0.756
EOF

Upvotes: 0

Related Questions