3

I am trying to count the number of entries in column 2 for the same column1 and also number of times the first entry in column2 for similar column1 occur.

For example my input TSV file is:

C1  NC01
    NC01
C2  NC01
    NC01
    NC02
C3  NC01
    NC02
    NC03

And my desired output is:

C1  NC01  2  2
    NC01
C2  NC01  3  2
    NC01
    NC02
C3  NC01  3  1
    NC02
    NC03

How can I do this using command-line?

Improve this question
2
  • Do you need to count col1 in col2?Can you please explain more?
    – PersianGulf
    May 15, 2015 at 6:06
  • col3 = col1+col2, but col4?
    – PersianGulf
    May 15, 2015 at 6:09

1 Answer 1

Reset to default
3

Use awk:

awk '{a[NR]=$0} {if(NF!=1){y=$2;x=$0;b[x]=1;c[x]=1}else{b[x]++;if(y==$1){c[x]++}}} END{for (n in a){z=a[n];print z" "b[z]" "c[z]}}' file

Admittedly it isn't the simplest one, but it works for me with your given input.

Explanation:

  • {a[NR]=$0}: First load all content of the file in an array called a.
  • if(NF!=1): If it's a row where we have multiple columns (more than one)...
  • {y=$2;x=$0;b[x]=1;c[x]=1}: ... set a variable y to the second column value which we want to count, and a variable x to the whole line. Those variables are used in later iterations. Those values are also indexes in two arrays b and c, where we store the count values.
  • else{b[x]++;if(y==$1){c[x]++}}: If it's a row with just one column, we increment the value in the b array. And if that column matches the second column in the primary line, which we saved in y, we increment that value, too.
  • END{for (n in a){z=a[n];print z" "b[z]" "c[z]}}: At the end we loop trough the array a and print it's values (which is a simple dump of the files contents). We also print the counted values in the other two arrays b and c.

Output gives:

C1  NC01 2 2
    NC01
C2  NC01 3 2
    NC01
    NC02
C3  NC01 3 1
    NC02
    NC03
Improve this answer
7
  • 2
    I'm not sure why so many of you guys are so fond of cramping everything on a single line. Machines these days should be powerful enough to handle a few more spaces and newlines. ;)
    – lcd047
    May 15, 2015 at 6:39
  • 1
    @lcd047 Everytime little time there's a line break, something dies within me...
    – chaos
    May 15, 2015 at 6:43
  • 1
    @lcd047; It's certainly not performance. The problem with such code is rather that it's a) not that obvious to see what it does, and b) that you don't easily see how good or badly it is actually written. Here, in SE, it's certainly better to write not only effective, but also good and legible answers.
    – Janis
    May 15, 2015 at 13:54
  • @Janis Nearly 90 percent of my answer is the explanation, that takes the command apart and explains how it works.
    – chaos
    May 15, 2015 at 13:58
  • @chaos; There's nothing wrong with your explanation. I was commenting on your code. - Note that the explanation does not show how good/bad it is written; for that you'd need to "beautify" the code first.
    – Janis
    May 15, 2015 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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