i have a data set tab separated like this:
A B C D
1 aaa 1 2
1 aaa 3 4
1 aaa 5 6
1 bbb 7 8
1 ccc 9 1
1 ccc 2 3
1 ddd 4 5
1 ddd 6 7
1 ddd 8 9
1 ddd 1 2
I want to add a new E column to show sum of C of groupped B
Desired output:
A B C D E
1 aaa 1 2 9
1 aaa 3 4 9
1 aaa 5 6 9
1 ddd 4 5 19
1 ddd 6 7 19
1 ddd 8 9 19
1 ddd 1 2 19
awk 'BEGIN{ FS=OFS="\t" }
NR==FNR{
if (FNR>1) a[$2]+=$3
next
}
{ $(NF+1)=(FNR==1 ? "E" : a[$2]) }
1
' file file
Read the input file twice. In the first run, skip the header line (if (FNR>1)...) and add the value of field $3 to the value of array a with key $2.
Continue with the next record.
In the second run, assign E to new field $(NF+1) for the first line or the value of a[$2] for the other lines. Print the record.
Output:
A B C D E
1 aaa 1 2 9
1 aaa 3 4 9
1 aaa 5 6 9
1 bbb 7 8 7
1 ccc 9 1 11
1 ccc 2 3 11
1 ddd 4 5 19
1 ddd 6 7 19
1 ddd 8 9 19
1 ddd 1 2 19
if (FNR>1) a[$2]+=$3 to a[$2]+=$3 and the line with the assignment to { $(NF+1)=a[$2] } if there is no header.