CODEWITHSUNDEEP
bashawk
user2380782
user2380782

Reputation: 1446

comparing two files based on a column and append elements in common to a file

Basically I want to combine the power of grepf with awk or 'bash' commands. I have two files like this:

$file1
ENSG00000000003 TSPAN6  ensembl_havana  TSPAN6
ENSG00000000419 DPM1    ensembl_havana  DPM1
ENSG00000000457 SCYL3   ensembl_havana  SCYL3
ENSG00000000460 C1orf112    ensembl_havana  C1orf112
ENSG00000000971 CFH ensembl_havana  CFH
ENSG00000001036 FUCA2   ensembl_havana  FUCA2

$file2
ENSG00000000003.12  0.0730716237772557  -0.147970450702234
ENSG00000000419.5   0.156405616866614   -0.0398488625782745
ENSG00000000457.3   -0.110396121325736  -0.0147093758392248
ENSG00000000460.15  -0.0457144601264149 0.322340330477282
ENSG00000000971.12  0.0613967504891434  -0.0198254029339757
ENSG00000001036.4   0.00879628204710496 0.0560438506950908

And here my desired output

ENSG00000000003.12  TSPAN6  0.0730716237772557  -0.147970450702234
ENSG00000000419.5   DPM1    0.156405616866614   -0.0398488625782745 
ENSG00000000457.3   SCYL3   -0.110396121325736  -0.0147093758392248 
ENSG00000000460.15  C1orf112    -0.0457144601264149 0.322340330477282   
ENSG00000000971.12  CFH 0.0613967504891434  -0.0198254029339757 
ENSG00000001036.4   FUCA2   0.00879628204710496 0.0560438506950908

This output also will be useful

ENSG00000000003 TSPAN6  0.0730716237772557  -0.147970450702234
ENSG00000000419 DPM1    0.156405616866614   -0.0398488625782745 
ENSG00000000457 SCYL3   -0.110396121325736  -0.0147093758392248 
ENSG00000000460 C1orf112    -0.0457144601264149 0.322340330477282   
ENSG00000000971 CFH 0.0613967504891434  -0.0198254029339757 
ENSG00000001036 FUCA2   0.00879628204710496 0.0560438506950908

I have tried the command from Obtain patterns from a file, compare to a column of another file, print matching lines, using awk

awk 'NR==FNR{a[$0]=1;next} {n=0;for(i in a){if($0~i){print; break}}} n' file2 file

But obviously it does not give me the desired output

Thanks

Upvotes: 0

Views: 119

Answers (2)

Ed Morton
Ed Morton

Reputation: 203209

$ awk 'NR==FNR{m[$1]=$2;next} {sub(/[[:space:]]/," "m[$1])} 1' file1 FS='.' file2
ENSG00000000003.12 TSPAN6 0.0730716237772557  -0.147970450702234
ENSG00000000419.5 DPM1  0.156405616866614   -0.0398488625782745
ENSG00000000457.3 SCYL3  -0.110396121325736  -0.0147093758392248
ENSG00000000460.15 C1orf112 -0.0457144601264149 0.322340330477282
ENSG00000000971.12 CFH 0.0613967504891434  -0.0198254029339757
ENSG00000001036.4 FUCA2  0.00879628204710496 0.0560438506950908

Upvotes: 1

Wintermute
Wintermute

Reputation: 44023

With awk:

awk 'NR == FNR { a[$1] = $2; next } { split($1, b, "."); print $1, a[b[1]], $2, $3 }' file1 file2

This works as follows:

NR == FNR {                  # While processing the first file
  a[$1] = $2                 # just remember the second field by the first
  next
}
{                            # while processing the second file
  split($1, b, ".")          # split first field to isolate the key
  print $1, a[b[1]], $2, $3  # print relevant fields and the remembered
                             # bit from the first file.
}

Upvotes: 1

Related Questions