0

I have a tab delimitated file with multiple columns. I'd like to extra the number of times I see something in column A and print the data associated with the value in column A in a new column B.

Ex:

1 blue                                        
1 green
1 red            
100 blue           
100 red

I'd like an output file that reads

3 1 blue,green,red
2 100 blue,red

Is there away to do this using awk or perl?

1

in awk:

{
  if (count[$1] == "") {
    count[$1] = 1;
    results[$1] = $2;
  } else {
    count[$1] = count[$1] + 1;
    results[$1] = results[$1] "," $2;
  }
}
END {
  for (number in count) {
    print count[number],number,results[number];
  }
}

results in the output of: 2 100 blue,red 3 1 blue,green,red

for your sample data above.

The order of the results may not be quite what you want, I'm not sure how critical that is for you.

  • Maybe I'm not experienced enough with awk. I was unable to get this to work. I've used awk in simple command line before. Is there a perl alternative? – mlr Jan 30 '17 at 18:25
  • Probably but I don't know Perl. The easiest way to run a multi line awk command is to save it as a text file and run awk -f script data with an optional > output on the end to save the results to a file rather than printing to the screen. – Andrew Jan 31 '17 at 19:58
0

Here is what I have tried that may work for you. NOTE: "\011" = tab char, change to " " for space)

awk 'BEGIN { s = "\011"; c = "," ; cnt = 0; all_colors = "" } {
    if ( NR == 1 ) { num = $1; colors[cnt++] = $2 }
    else {
        if ( num != $1 ) {
            for (x=0; x<cnt; x++) {
                all_colors = all_colors colors[x]
                }
            print cnt s num s all_colors; cnt = 0; all_colors = ""
            num = $1; colors[cnt++] = $2
            }
        else { colors[cnt++] = c $2 }
        }
    }
END {
    all_colors = ""
    for (x=0; x<cnt; x++) { all_colors = all_colors colors[x] }
    print cnt s num s all_colors
}' tab_file

   tab_file                output
1       blue          3       1       blue,green,red
1       green         2       100     blue,red
1       red
100     blue
100     red

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.