0

I have input as shown below, and I need to keep only unique strings from the second column, where the first column will become the sum of all values for each unique string.

For ex: take the OIA in the 2nd column and add the respective values of OIA from the first column and print before OIA.

INPUT:

1079 OIA
1079 OIA
975  OIA
975  OIA
372  CLN
243  TLN
224  TLN
224  TLN
224  TLN
224  TLN
224  TLN
224  TLN
224  TLN
224  TLN
224  TLN
224  TLN

Expected Output:

4108    OIA
372     CLN
2483    TLN
0

3 Answers 3

3
awk '{m[$2]+=$1}END{for(i in m)print m[i],i}' file

this should work fine for the majority of cases with whitespace-separated columns.

4
  • It's worth mentioning that that'll keep all $2 values in memory and so could fail or slow down significantly for huge input files and it will produce output in "random" order rather than retaining the input order (which might be fine of course).
    – Ed Morton
    Jun 10, 2022 at 17:43
  • @EdMorton the advantage of this technique is that it can handle unsorted data. I use similar approach to parse log file of some GB and it seem not to be particulary slow (to be honest i use it on a very powerfull machines i don't know how it can be on a normal workstation. I think it can work well in the majority of cases). The fact that it print data in "random" order dosnt seem a problem to me, or a requests of th OP. Jun 11, 2022 at 20:45
  • 1
    Understood. Sorting the input first is often more efficient and can handle larger amounts of data. We just had a question recently where someone posted a similar solution storing all the keys in memory (unix.stackexchange.com/a/704378/133219) and we discovered that with a huge file it was an order of magnitude slower than one that doesn't store the keys in memory due to the way dynamic array allocation works. You're right, it'll be fine for most cases, it's just not obvious to a newcomer what the implications are.
    – Ed Morton
    Jun 12, 2022 at 0:54
  • 1
    @EdMorton amazing, you're absolutely right :) Jun 12, 2022 at 9:23
3

Assuming your input is always grouped by the 2nd column values as shown in your sample input:

$ awk '$2!=prev{if (NR>1) print sum, prev; sum=0; prev=$2} {sum+=$1} END{print sum, prev}' file
4108 OIA
372 CLN
2483 TLN

The above will keep almost nothing in memory and so will work for arbitrarily large files and will produce the output in the same order as the input $2 values.

2
  • Morton, will this work on un sorted order of second column?
    – Renga
    Jun 12, 2022 at 3:13
  • It doesn't have to be sorted but it does have to be grouped, as I mentioned Assuming your input is always grouped (so a doesn't have to come before b but all as must be together and all bs must be together). If that's not what you have then edit your question to show that the input can be ungrouped and then use sort -k2,2 file | awk 'script' instead of awk 'script' file to group it before the awk script works on it.
    – Ed Morton
    Jun 12, 2022 at 13:08
1

Assuming the data is sorted on the second column, using GNU datamash:

datamash -W groupby 2 sum 1 <file

This reads the input as whitespace-delimited fields, groups the data by the second field and sums the first field for each group.

The output given the data in the question will be tab-delimited:

CLN     372
OIA     4108
TLN     2483

Note that the fields are swapped from what's expected in the question. To fix this, and also sort the input data on the second field (in case it may not be as neatly sorted as in the question):

sort -b -k 2,2 file | datamash -W groupby 2 sum 1 | awk -v OFS='\t' '{ print $2, $1 }'
1
  • I wonder why datamash -W -s -g 2 sum 1 <file doesn't seem to work?
    – r_31415
    Sep 3, 2022 at 0:36

You must log in to answer this question.

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