Merge two files adding values of columns

Question

How can I merge multiple files, of the same format, adding the values of specific columns while keeping others fixed?

If the columns of my files are of the format(where there are multiple rows):

File 1:
a1 b1 c1 d1 e1

File 2:
a2 b2 c2 d2 e2

File n:
an bn cn dn en

where a1=a2=an, b1=b2=bn

Output:
a b (c1+c2+...cn) (d1+d2+...dn) (e1+e2+...en)

For example,

File 1:
1 0 200 5 50
2 1 345 9 22
3 2 287 4 33

File 2:
1 0 355 4 12
2 1 366 5 7
3 2 202 7 16

I want the Output:
1 0 555 9 62
2 1 711 14 29
3 2 489 11 49

awk and friends come to mind ...

Benjamin Bannier
– Benjamin Bannier

2012-10-12 13:53:47 +00:00
Commented Oct 12, 2012 at 13:53 — Benjamin Bannier
– Benjamin Bannier, Commented Oct 12, 2012 at 13:53

Kent · Accepted Answer · 2012-10-12 14:39:09Z

1

awk-oneliner:

awk '{i=$1" "$2;a[i]+=$3;b[i]+=$4;c[i]+=$5}END{for(x in a)print x,a[x],b[x],c[x]}' file*

test with 3 files (the oneliner works with n files)

kent$  head file*
==> file1 <==
1 0 200 5 50
2 1 345 9 22
3 2 287 4 33

==> file2 <==
1 0 355 4 12
2 1 366 5 7
3 2 202 7 16

==> file3 <==
1 0 30 41 120
2 1 306 15 70
3 2 230 7 20

kent$  awk '{i=$1" "$2;a[i]+=$3;b[i]+=$4;c[i]+=$5}END{for(x in a)print x, a[x],b[x],c[x]}' file*
1 0 585 50 182
2 1 1017 29 99
3 2 719 18 69

answered Oct 12, 2012 at 14:39

Kent

197k36 gold badges248 silver badges317 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

choroba · Accepted Answer · 2012-10-12 14:20:04Z

0

$ awk '{getline x <f; split(x,a," ");$3+=a[3];$4+=a[4];$5+=a[5]}1' f="file1" file2
1 0 555 9 62
2 1 711 14 29
3 2 489 11 49

Using getline, you can parallely read the content of the other line as well. Once we split the second files content as well, it can be summed up.

edited Oct 12, 2012 at 14:20

choroba

245k27 gold badges221 silver badges304 bronze badges

answered Oct 12, 2012 at 14:14

Guru

17.1k2 gold badges37 silver badges47 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:52:47Z

A similar solution of what Guru gave but generalised for n files:

Assuming following input files:

==> file1 <==
1 0 200 5 50
2 1 345 9 22
3 2 287 4 33

==> file2 <==
1 0 355 4 12
2 1 366 5 7
3 2 202 7 16

==> file3 <==
1 0 400 6 14
2 1 500 5 7
3 2 202 7 16

Run this awk script:

awk '
    FNR < NR { 
        exit; 
    } 
    {
        for ( i = ARGC - 1; ARGV[i] != FILENAME; i-- ) {
            getline line < ARGV[i];
            r = split( line, fields );
            if ( r < 3 ) {
                continue;
            }
            if ( $1 == fields[1] && $2 == fields[2] ) {
                for ( j = 3; j <= NF; j++ ) {
                    $j += fields[j];
                }
            }
        }
        print;
    }
' file[123]

That yields:

1 0 955 15 76
2 1 1211 19 36
3 2 691 18 65

Collectives™ on Stack Overflow

Merge two files adding values of columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related