CODEWITHSUNDEEP
bash
Blaisem
Blaisem

Reputation: 637

How to add two columns from three different files with reference to matching values in another column?

File 1:

1  0.3
2  0.1
3  0.4
4  0.8

File a:

2  0.7
4  0.2
6  0.5
8  0.9

File b:

2 0.4
6 0.6

Goal

Step-by-step:

  1. Find common values in field 1 ($1) between Files 1, a, and b. As a sample solution to the above files:
    • File 1, File a, and File b all have a common value in $1 = 2.
    • File 1 and File a also have a common value in $1 = 4
  2. Output File 1, with the following changes:
    • For $1 = 2, then $2 = 0.1 + 0.7 + 0.4 = 1.2
    • For $1 = 4, then $2 = 0.8 + 0.2 = 1.0

Desired output in File 2:

1 0.3
2 1.2
3 0.4
4 1.0

Namely, File 2 = File 1, except the rows, where $1 in File 1 matches $1 in File a or b, have been added together in $2.

similar problem

I have already received advice on this problem for matching and adding two files, using the code 

 awk 'FNR==NR{a[$1]=$2;next} {$2=$1 in a?$2+a[$1]:$2} 1' Input_file2  Input_file1 >> output_file

However, the code cannot process 3 input files simultaneously. If I do them separately (two files at a time, ie. File 1 + File a, then File 1 + File b), I end up adding File 1's value twice.

As I potentially have 3-6 files to compare, I cannot simply do File a + File b >> File c, followed by File 1 + File c >> File 2.

Summary

I would like a script that can search for matches in $1 between three files, then print $2 (File 1) + $2 (File a and/or File b) wherever a $1 match is found. The output is File 2, which prints File 1 with the new summed values in $2 whereever matches in $1 occurred. Any assistance is much appreciated!

Upvotes: 1

Views: 80

Answers (1)

James Brown
James Brown

Reputation: 37394

Here in one:

$ awk '
NR==FNR {             # store the file1 to a hash and add to its elements
    a[$1]=$2
    next
}
($1 in a) {
    a[$1]+=$2
}
END {
    for(i in a)
        print i,a[i]  # print in no particular order
}' file1 filea fileb

Output:

1 0.3
2 1.2
3 0.4
4 1

Upvotes: 1

Related Questions