3

I want to write a code to be able to manipulate text on my CSV file which contains the following:

71w - Rus,51200
71w - Phi,307200
71w - Ukr,307200
71w - Ukr,51200
71w - Mic,102400
71w - Mic,51200
71w - Jul,256000
71w - Jul,51200
71w - Pro,256000
71w - Uni,51200
71w - Ind,50176
71w - Ind,40960
71w - Sin,358400
71w - May,20480
71w - Tha,512000
71w - Tha,972800
71w - Bar,1280000
71w - Bar,102400
71w - Bar,2048000
71w - Upg,358400
71w - Leg,20480
71w - Res,153600

I want to gather columns with the same values and put it on their corresponding rows like below:

71w - Rus,51200
71w - Phi,307200
71w - Ukr,307200,51200
71w - Mic,102400,51200
71w - Jul,256000,51200
71w - Pro,256000
71w - Uni,51200
71w - Ind,50176,40960
71w - Sin,358400
71w - May,20480
71w - Tha,512000,972800
71w - Bar,1280000,102400,2048000
71w - Upg,358400
71w - Leg,20480
71w - Res,153600

Thank you.

3

A good way to do this is to use an associative array or hash. The key for each hash is the first field (I'll call them "ids", for want of a better term), and the values stored for each key will either be a string containing a comma-separated list of values seen for that id, or an array containing the same.

awk:

This awk version uses a comma-separated string because (in awk) it's easier than dealing with an associative array containing an array.

#!/usr/bin/awk -f

BEGIN {
  FS=" *, *";
  OFS="";
}

{
  key=$1; $1=""; $0=$0;

  if (length(ids[key]) > 0) {
    ids[key]=ids[key]","$0;
   } else {
    ids[key] = $0
   };
}

END {
  for (k in ids) {
    print k "," ids[k]
  }
}

In perl, it's no harder (and a lot more useful/flexible) to deal with a Hash of Arrays (or "HoA") than a concatenated string:

#!/usr/bin/perl -w

use strict;

my %ids = ();

while(<>) {
  chomp;
  my @F = split /\s*,\s*/;
  push @{ $ids{$F[0]} }, $F[1];
};


END {
  foreach my $key (keys %ids) {
    print $key . ',' .  join(",",@{ $ids{$key} }), "\n";
  }
}

Output for both awk and perl versions is the same:

71w - Ukr,307200,51200
71w - Bar,1280000,102400,2048000
71w - Res,153600
71w - Upg,358400
71w - Sin,358400
71w - Mic,102400,51200
71w - May,20480
71w - Tha,512000,972800
71w - Jul,256000,51200
71w - Uni,51200
71w - Ind,50176,40960
71w - Pro,256000
71w - Rus,51200
71w - Leg,20480
71w - Phi,307200

Note: the output for both the awk and perl versions is not in any particular order, and will probably come out in a different order every time you run it. That's because awk "associative arrays" and perl "hashes" (two names for the same thing) are inherently unordered.

You can pipe the output to sort if you want. or, in perl you can use:

      foreach my $key (sort keys %ids) {

instead of:

      foreach my $key (keys %ids) {

Also - because we're storing the individual values for each id in an array, in perl it's easy to also sort those values. e.g. replace the entire END block in the perl version with:

END {
  foreach my $key (sort keys %ids) {
    print $key . ',' .  join(",",sort @{ $ids{$key} }), "\n";
  }
}

and the output will be:

71w - Bar,102400,1280000,2048000
71w - Ind,40960,50176
71w - Jul,256000,51200
71w - Leg,20480
71w - May,20480
71w - Mic,102400,51200
71w - Phi,307200
71w - Pro,256000
71w - Res,153600
71w - Rus,51200
71w - Sin,358400
71w - Tha,512000,972800
71w - Ukr,307200,51200
71w - Uni,51200
71w - Upg,358400
4
  • Wow, I did not think that it was this complicated. I just tested it out and the output is what I am looking for! Thank you! – Waltiki Sucaldino Aug 28 '19 at 7:22
  • it's not terribly complicated. the only real complicating factor is that you have to read in ALL the input before you can print any output - and you need some way to store all the values for each of the "ids" (aka keys) until you're ready to print. – cas Aug 28 '19 at 7:23
  • the other complication is the need to avoid printing an extra comma in the output. in awk, i checked whether ids[key] existed (had a length > 0) to decide whether to append a comma and the data or to set it equal to just the data. In perl, I just pushed the data into the array (auto-creating it if it didn't already exist) and then used perl's join() function to join the array with commas. lots of text-processing tasks involve splitting data into fields/arrays, doing something to those fields, and then joining them back together again. – cas Aug 28 '19 at 7:32
  • Perfect. both works as intended. Thanks again! – Waltiki Sucaldino Aug 28 '19 at 7:42
2

Using GNU datamash:

$ datamash -t, -g 1 collapse 2 < file.csv
71w - Rus,51200
71w - Phi,307200
71w - Ukr,307200,51200
71w - Mic,102400,51200
71w - Jul,256000,51200
71w - Pro,256000
71w - Uni,51200
71w - Ind,50176,40960
71w - Sin,358400
71w - May,20480
71w - Tha,512000,972800
71w - Bar,1280000,102400,2048000
71w - Upg,358400
71w - Leg,20480
71w - Res,153600

Sorted output using -s:

$ datamash -s -t, -g 1 collapse 2 < file.csv
71w - Bar,1280000,102400,2048000
71w - Ind,50176,40960
71w - Jul,256000,51200
71w - Leg,20480
71w - May,20480
71w - Mic,102400,51200
71w - Phi,307200
71w - Pro,256000
71w - Res,153600
71w - Rus,51200
71w - Sin,358400
71w - Tha,512000,972800
71w - Ukr,307200,51200
71w - Uni,51200
71w - Upg,358400

Options explained:

  • -s sort input before grouping
  • -t, use comma as field separator
  • -g 1 group on first field
  • collapse 2 collapse on second field

Have a look at the "collapse" example in the manual.

1
  • nice. i often forget that datamash exists. – cas Aug 28 '19 at 15:15
0

Assuming your input is, or can be e.g by sorting, grouped on the $1 values as shown in your input:

$ cat tst.awk
BEGIN { FS=OFS="," }
$1 != prev { if (NR>1) print rec; rec=prev=$1 }
{ rec = rec OFS $2 }
END { print rec }

.

$ awk -f tst.awk file
71w - Rus,51200
71w - Phi,307200
71w - Ukr,307200,51200
71w - Mic,102400,51200
71w - Jul,256000,51200
71w - Pro,256000
71w - Uni,51200
71w - Ind,50176,40960
71w - Sin,358400
71w - May,20480
71w - Tha,512000,972800
71w - Bar,1280000,102400,2048000
71w - Upg,358400
71w - Leg,20480
71w - Res,153600

or if you prefer brevity over clarity:

$ awk -F, '$1!=p{if(NR>1)print r;r=p=$1}{r=r FS$2}END{print r}' file
71w - Rus,51200
71w - Phi,307200
71w - Ukr,307200,51200
71w - Mic,102400,51200
71w - Jul,256000,51200
71w - Pro,256000
71w - Uni,51200
71w - Ind,50176,40960
71w - Sin,358400
71w - May,20480
71w - Tha,512000,972800
71w - Bar,1280000,102400,2048000
71w - Upg,358400
71w - Leg,20480
71w - Res,153600

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.