# Counting number of entries in column 2 where column 1 is similar

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?

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

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

• 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. ;) May 15, 2015 at 6:39
• @lcd047 Everytime little time there's a line break, something dies within me... May 15, 2015 at 6:43
• @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. 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. 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. May 15, 2015 at 14:02