3

I have a TSV tab-separated file with 3 cols:

ID\tTEXT\tTYPE

To print the TYPE column I do

cat /dataset.csv | awk -F $'\t' '{print $3}'

Those values are an enumeration of values like {CLASS_A,CLASS_B,CLASS_C}, etc.

I need a inline way with AWK to count the number of occurrences (NF?) of the column TYPE when matching each value in the enumeration to obtain:

CLASS_A 1300
CLASS_B 450
CLASS_C 988

[UPDATE]

According to the solutions below, I'm putting here my last version of this script

#!/bin/bash

COL=$1
FILE=$2

awk -v col="$COL" -F $'\t' '   {c[$col]++}
                 END{
                     for (i in c) printf("%s\t%s\n",i,c[i])
                 }' $FILE

and the usage to count occurrences of rows in col 3 is

$ ./count_cols.sh 3 /myfile.csv
  • Leaving variable expansion unquoted is a sure recipe for future problems. The use of capital variables is a sure path to future conflicts with environment variables. I strongly suggest that you replace $FILE with "$file" and COL with col. – sorontar Apr 26 '17 at 20:35
4

There is no need to use cat to read the file. AWK is perfectly capable to read it.

A core c[$3]++ statement should get the count of lines of each type.
Then, at the end, just print (as tab separated values) all the counts:

#!/bin/bash

awk -F '\t' '   {c[$3]++}
                 END{
                     for (i in c) printf("%s\t%s\n",i,c[i])
                 }' dataset.csv 

Appended

Given the comment from the OP that:

I get some issues for colums that have quotes like that doesn\'t mean that you\'re not worth remembering think of the people who need to know they need to know so you need ​to show.... In this case the parsing on \t will fail.

I got to review the answer. I created this file:

$ cat dataset.csv 
1233    that doesn\'t mean that you\'re not worth remembering think of the people who need to know they need to know so you need to show...    CLASS_0
1234    here    CLASS_A
1235    goes the values CLASS_B
1236    "that need counting"    CLASS_B
1237    "\like \this"   CLASS_B
1238    \or \this       CLASS_C
1239    including spaces        CLASS_B
1240    but not tabs    CLASS_A
1241    which could not work    CLASS_B
1242    finally CLASS_C
1243    this is CLASS_A
1244    over    CLASS_B
1245    988     CLASS_C

That file, when used with the script, gives the correct result:

$ ./script
CLASS_A 3
CLASS_B 6
CLASS_C 3
CLASS_0 1

Which is the correct result.

Of course, the file

  1. has the correct amount of tabs for 3 fields, and
  2. variables are correctly quoted when expanded and are not in upper case.

To test that a file does comply with the first requirement, you may use this script:

#!/bin/bash

filetoread="$2"

<"$filetoread" tr -dc '\t\n' |
    awk '(length!=2){printf("Error in line: %s, has %s tabs\n",NR,length)}'

awk -F '\t' '(NF!=3){printf("Error in line: %s, has %s fields\n",NR,NF)}' "$filetoread"

Which checks that there are exactly two tabs per line, and
That the number of fields (as seen by awk) are actually three.

Adding a couple of test lines:

… …
1239    including spaces        CLASS_B
1       but not     tabs    CLASS_A
2       but not \ttabs  CLASS_A
1240    but not tabs    CLASS_A
… …

And running the script above:

$ ./script 3 dataset.csv
Error in line: 8, has 4 tabs
Error in line: 8, has 5 fields

detects the line ID 1 that has four tabs (two added) and doesn't get fooled by line ID 2 with a \t.

As for the quoting and use of variables, that is something you should improve all by yourself.

  • This solution worked. Not sure why the first one did not. – loretoparisi Apr 20 '17 at 12:51
  • I get some issues for colums that have quotes like that doesn\'t mean that you\'re not worth remembering think of the people who need to know they need to know so you need ​to show.... In this case the parsing on \t will fail. – loretoparisi Apr 26 '17 at 13:01
  • 1
    @loretoparisi It is dificult to debug a problem that is described as "some issues". The only way is to assume, and that is what I had to do in my extended answer. If you care to build a MCVE (A Minimal, Complete, and Verifiable example)(which should be a must at this point), I may be able to better address your "some issues", otherwise, I shall pass. – sorontar Apr 26 '17 at 21:43
  • You are right, very difficult to find the bad sample, but at the end I got it via sed -n $l,$mp ./dataset.csv getting this gist.github.com/loretoparisi/80c3dc482a95cb535c8a244a73d0c1ba It seems there is an illegal quoting somewhere. – loretoparisi Apr 26 '17 at 23:17
  • 1
    @loretoparisi In the file example from your github example there is an incorrect tab in this text: 'good for you?&#9; all i need is some time'. The tab shown here as a &#9; code. In the file there is an actual tab character. – sorontar Apr 27 '17 at 0:46
3

Something like this can do the job:

awk -F'\t' ' 
            $3=="CLASS_A" {a+=1} 
            $3=="CLASS_B" {b+=1} 
            $3=="CLASS_C" {c+=1} 
            END {
                printf "%s %d\n%s %d\n%s %d", CLASS_A,a,CLASS_B,b,CLASS_C,c
            }' /dataset.csv
3

I hope I understood correctly that column 3 can contain either "CLASS_A" "CLASS_B" or "CLASS_C" ?

Then

awk -F'\t'  '
 { seen[$3]++ ;}
 END { for (i in seen) {
         printf "%s : %s\n",i,seen[i]
       }
      } 
 ' /dataset.csv

Should do the trick?

Note that "for (i in seen)" doesn't guarantee that they are read in the "right order", but you can add a | sort (after the whole awk thing) to sort those, or use more convulated tricks (inside awk).

If you need to also get over the first line (if it contains headers, for example) add before the first line in the awk script:

( NR==1 ) { next ;}

or change the first line as follows:

( NR > 1 ) { seen[$3]++ ;}
  • Thanks Olivier, I have put it into a bash script, but I get only one class repeated several times as output. The input files is tab separated and the third column is the type. The second one is text. But thanks to be so quick! – loretoparisi Apr 20 '17 at 12:49
  • 1
    sorry I put $3 instead of i in the END section.... so you only saw the last one... – Olivier Dulac Apr 20 '17 at 14:07
  • Thank you! I have tested it against real data, and it works, but both having some issues with quotes. See above comment. – loretoparisi Apr 26 '17 at 13:02

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.