1

I have a file which has several duplicating lines that only differ in the first column.

Original file:

2 A 3 rr 44 5 t y uuu 8
3 A 3 rr 44 5 t y uuu 8
0 B f 1
1 C 6 5 55 yy 7 4 3 4-5 tt efvho 44 3 5 gg 5  4 t rr 33
5 C 6 5 55 yy 7 4 3 4-5 tt efvho 44 3 5 gg 5  4 t rr 33
5 C 6 5 55 yy 7 4 3 4-5 tt efvho 44 3 5 gg 5  4 t rr 33
3 D tt v 44 f1 p
1 D tt v 44 f1 p

What I would like is to get rid of the duplicates and sum the values from column 1.

Desired output:

5 A 3 rr 44 5 t y uuu 8
0 B f 1
11 C 6 5 55 yy 7 4 3 4-5 tt efvho 44 3 5 gg 5  4 t rr 33
4 D tt v 44 f1 p

This script is quite close to what I want to do:

awk  '{a[$2]+=$1}END{for(i in a)print a[i] , i |"sort"}' file

Giving this output:

5 A 
0 B 
11 C 
4 D 

Is there a way to change $2 to all columns but the first in this script?

2
  • do we need to keep the order of lines?
    – pLumo
    Nov 17, 2022 at 9:56
  • No, it's not necessary.
    – Lia
    Nov 17, 2022 at 9:58

3 Answers 3

0

This will work, but won't keep the order of lines:

awk '{v=$1; $1=""; s[$0]=s[$0]+v} END {for (r in s) { printf "%s%s\n",s[r],r }}' file
  • save first field in a variable, then empty it.
  • save an array with the row (with emptied $1) as key and the sum up the saved $1 as its value.
  • in the end, print the array. I use printf to avoid the extra space that comes, because we cannot delete, but only empty $1.

add | sort -k2 to sort on the second column.

Output:

5 A 3 rr 44 5 t y uuu 8
0 B f 1
11 C 6 5 55 yy 7 4 3 4-5 tt efvho 44 3 5 gg 5 4 t rr 33
4 D tt v 44 f1 p
0

Using datamash and awk:

Because duplicating lines differ in first column only, this datamash command would work.

$ datamash -s -t' ' groupby 2 sum 1 --full <file | 
awk '{$1=$NF; NF -= 1}1'
1
  • Decrementing NF is undefined behavior per POSIX so different awk versions will do different things with that.
    – Ed Morton
    Nov 18, 2022 at 17:27
0

Only storing 1 line at a time in memory and reproducing the input order in the output using any awk:

$ cat tst.awk
{
    currKey = $0
    sub(/[^[:space:]]+ /,"",currKey)
}
currKey != prevKey {
    if ( NR > 1 ) {
        print prev0
    }
    prevKey = currKey
    prev1 = 0
}
{
    $1 += prev1
    prev1 = $1
    prev0 = $0
}
END {
    print prev0
}

$ awk -f tst.awk file
5 A 3 rr 44 5 t y uuu 8
0 B f 1
11 C 6 5 55 yy 7 4 3 4-5 tt efvho 44 3 5 gg 5 4 t rr 33
4 D tt v 44 f1 p

The above assumes your duplicate lines are grouped together. If not just run sort -k2 file | awk '...'.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .