1

I have an input CSV file that looks like this:

john,Hello my name
john,is John
katie,Whereas my
katie,name is Katie
bob,And I am Bob.

The file is ordered such that names (in the first column) are contiguous, and text (the second column) is ordered logically.

Is there a standardized way to "group by" (using SQL terminology) the first column and concatenate the second column?

My desired output is:

john,Hello my name is John
katie,Whereas my name is Katie
bob,And I am Bob.
0

There's no standard tool for that, no. This task is pretty well-suited for awk: read lines one by one, save the first field and accumulate the second field, and print out the result if the first field changes. The main (minor) difficulty is that the result also has to be printed out when the last line is reached.

awk -F, '
    1 {current = $1; sub(/^[^,]*,/,"")}
    current == previous {acc = acc " " $0; next}
    NR != 1 {print previous "," acc}
    1 {previous = current; acc = $0}
    END {if (NR) print previous "," acc}'
1

Advanced knowledge of each block's length spares us from the minor complication of dealing with a boundary condition which would otherwise require an END block of replicated functionality.

I do not advocate for this approach in favor of Gilles' accepted answer. I merely present it to demonstrate an alternative approach which, when dealing with complicated problems (this not being one of them), could yield a significant reduction in complexity (at the cost of i/o and possibly memory).

a is a name-indexed array of block lengths. n is the number of remaining lines in a block:

awk -F, '
    FNR==NR  {a[$1]++; next}
          n  {print " "$2}
         !n  {print; n=a[$1]}
       !--n  {print "\n"}
' ORS= data data
-1
awk -F, '{a[$1]=a[$1]? a[$1]" "$2 : $2;}END{for (i in a)print i, a[i];}' OFS=, filename

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.