0

I have a large file containing two fields the first one representing an object name and the second field represents the size of that object:

A 1
A 2
B 4
ABC 12
C 5
A 9
B 3
ABC 6

I would like to summarize the list with the following format:

A 1,2,9
ABC 12,6
B 4,3
C 5

the solution I came up with is to create a unique list of the objects present in the file, iterate through it and match it with the original file

for object in $(awk '{print $1}' objects_with_sizes.txt | sort -u);do
    echo -n "$object "
    awk -v pattern="$object" '$1==pattern{printf "%s%s" ,sep,$2;sep=","} END{print ""}' objects_with_sizes.txt  
done 

This implementation takes a long time to run, is there a more efficient way of creating the desired output?

5
$ awk '{ object[$1]= (object[$1]==""?"":object[$1] ",") $2 }
  END  { for(obj in object) print obj, object[obj] }' infile
A 1,2,9
B 4,3
C 5
ABC 12,6

A bit more efficient (use of the memory; matter for a huge file that cannot fit into the memory), i.e not buffering the file partially into the memory that awk command alone does above but only until a object key changes:

$ <infile sort -k1,1 -k2,2n |\
  awk 'pre!=$1 { if(obj) { print obj; obj="" } }
               { obj= (obj==""?$1 " ":obj ",") $2; pre=$1 }
  END{ if(obj) print obj }'
A 1,2,9
ABC 6,12
B 3,4
C 5
0
5

Using GNU datamash:

$ datamash -t ' ' -s -g 1 collapse 2 <file
A 1,2,9
ABC 12,6
B 4,3
C 5

Options:

  • -t '_' use the space character as field delimiter
  • -s sort the input before grouping
  • -g 1 group on the first field
  • collapse 2 collapse values of the second field into comma-separated list
1

We can sort then feed to GNU sed which compares the current first field with previous to generate the comma separated OR print till that point.

$ < file sort -s -k1,1 \
| sed -Ee '
  :a
    $!N
    s/^((\S+)\s.*)\n\2\s+(\S+)/\1,\3/
  ta
  P;D
' -
A 1,2,9
ABC 12,6
B 4,3
C 5

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.