I have a csv file with around 50 columns, can be anywhere between 20 and 100 rows.

The individual records have IDs, and some records can be in a group of 2. Essentially what I need to do is add an ID to the same row that another ID in that group is in. Example:

ID     ,group,blank column
2019-1 ,     ,
2019-2 ,GRP1 ,
2019-3 ,GRP2 ,
2019-4 ,GRP1 ,
2019-5 ,     ,
2019-6 ,GRP2 ,

And the output I would like is:

ID     ,group,blank column
2019-1 ,     ,
2019-2 ,GRP1 ,2019-4
2019-3 ,GRP2 ,2019-6
2019-5 ,     ,

In my attempts using awk, I haven't had any luck. I either end up leaving out rows that have no group, or I end up repeating values.

  • 1
    and what with a third line with GRP1 ?
    – ctac_
    Feb 11, 2019 at 19:15

1 Answer 1

perl -a -F, -ne 'if($F[1]=~/\S/) { push @{$d{$F[1]}}, $F[0]; } else { print ; }
  END {
    for(keys %d){
      print shift @{$d{$_}},",$_, ",@{$d{$_}},"\n"
  }' my.csv
  • Thank you for the help. This absolutely does what is intended. I have next to no experience with perl however, and I am trying to implement this for use with a file that has 50+ columns. I'm not sure of the best way to accomplish this however. I basically need it to go in, and update the columns I've mentioned, but still print all the other information. Feb 11, 2019 at 21:07

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.