4

I have the following csv file with 2 columns:

Header1,Header2
AU3CB0222255,EBFXFR
AU3CB0222271,DBFXFR
AU3CB0225233,DBFXFR
AU3CB0225662,DBFXFR
AU3CB0226264,DBFXFR

I want to count the fields in column 2 which don't start with E.

I tried the command below but it's not working properly:

awk '$2 !~ /^E_/ { count++ }END{ print count }' FinalOutput.csv
  • You seem to have a typo in your RegExp: /^E_/ looks for column 2 fields that start with E_, which none of your example columns does. Removing the _ should do the trick. Also, you need to set the field separator to , by stating awk -F',' '$2 !~ etc' FinalOutput.csv – AdminBee Aug 4 at 14:31
  • Yes.. below command does the trick - awk -F',' '$7 !~ /^E/ { count++ }END{ print count }' FinalOutput.csv But it's also counting header, how we can omit the header. – RAHUL SONI Aug 4 at 14:36
  • @RAHULSONI See my answer (the one by Tsumo also skips the header) – AdminBee Aug 4 at 14:39
6

Your awk command has several issues.

  • You have not specified the field separator, so awk splits the lines at whitespace, not ,. You can use the -F',' command-line option to set the field separator.
  • Your RegExp states /^E_/ and hence would look for fields that don't start with E_ (which none of your column 2 values does), not merely those that don't start with E. Remove the _.
  • Your command would also count the header line. You can use the FNR internal variable (which is automatically set to the current line number within the current file) to exclude the first line.
  • As noted by Rakesh Sharma, if all lines start with E, the command would print the empty string at the end instead of a 0 because of the use of an uninitialized variable. You can force interpretation as number by printing count+0 instead of count.

A corrected version would be

awk -F',' 'FNR>1 && $2!~/^E/{count++} END{print count+0}' FinalOutput.csv

Note that since I used the FNR per-file line-counter (rather then the global line-counter NR), this would also work with more than one input file where all of them have a header line, i.e. you could even use it as

awk -F',' ' ... ' FinalOutput1.csv FinalOutput2.csv ...
| improve this answer | |
  • There is one more issue with the OP's awk command and that is the END {print count} . In the event that none of the lines satisfy the criterion for printing, then an empty shall be output instead of 0. To fix we can coerce var count to a number. – Rakesh Sharma Aug 5 at 1:57
  • @RakeshSharma Good point, I included the usual "add a zero" modification to enforce interpretation as numerical value. – AdminBee Aug 5 at 7:28
5

Some other approaches:

  • awk defaults to printing if a condition is true, so you could simply do:

    $ awk -F, 'NR>1 && $2!~/^E/' file | wc -l
    4
    
  • print the file starting from the second line, and count how many times you see a comma followed by a non-E character (note that this assumes only one comma per line as shown in your example):

    $ tail -n+2 file | grep -c ',[^E]'
    4
    
  • perl

    $ perl -F, -lane '$c++ if $.>1 && $F[1] !~ /^E/ }{ print $c' file
    4
    
  • sed and wc

    $ sed -n '1d; /,[^E]/p' file | wc -l
    4
    
| improve this answer | |
4

You're very close, awk -F, 'NR>1{if ($2 !~ /^E/){count++}} END {print count}' should work.

-F, tells awk that , is the delimiter

NR>1 strips the header

I ran this on your sample file and it produced the correct output

| improve this answer | |
1

Python

#!/usr/bin/python
import re
j=re.compile(r'^E')
r=[]
k=open('file','r')

k.readline()
for i in k:
    m=i.strip().split(",")
    if not re.search(j,m[1]):
        r.append(i.strip())
print len(r)

awk and sed

sed -e  '1d' -n -e '/,E/!p' file| awk 'END{print NR}'
4
| improve this answer | |
1

awk with slurping the whole file (so that NR is 1)

$ awk -F '\n[^\n]*,[^E]' '{ print NF-1 }' RS='^$' file
4

GNU grep in PCRE mode

$ < grep -zoP '\n.*\K,[^E]'  | xargs -r0 -n1 | wc -l
4

perl in slurp mode

$ perl -F'\n.*,[^E]' -pal -0777e '$_=$#F' file 
4

POSIX grep /head

$ { head -n 1 > /dev/null; grep -c ',[^E]'; } < file
4

GNU sed in extended regex mode

$ sed -Ee "
    1d;/,[^E]/{z;H;}
    \$!d;g;y/\n/d/
    :a
      s/d{10}/#/g
      s/#([0-9]*)$/#0\1/
      $(seq 9 -1 1 | xargs -I {} printf 's/d{%d}/%d/;' {} {})
      y/#/d/
    ta
    s/^\$/0/
" file
4
| improve this answer | |
0

A "safe" approach: we don't assume a field without "E" at the beginning is automatically the field value you want (in your example: ignoring "Header2" there is only 1 other value, but maybe in your real file there can be more?)

Let's say to awk : using "," as a separator, count the occurence of each values of field 2 and print them at the end:

awk -F',' '
/Header2/ { rem="This is a title line, we skip it..." ; next ;}
          { nbseen[$2]++ }
END       { for(value in nbseen) {
               printf("%-20s : %6d\n",value,nbseen[value]) 
             } 
           }
' FinalOutput.csv | sort

This will display, sorted alphabetically, all the values seen in 2nd column and their number of occurences, and it ignores the "title lines" (be they in the 1st line or even if they are repeated from time to time)

This will show:

DBFXFR                   : 4
EBFXFR                   : 1

(you can then "grep -v -E 'this|that'" to ignore lines with "this" or "that" (ex: grep -Ev "^E" , and will see any value[s] that didn't start with an "E", and their occurences)

If you are positive any line that doesn't start with an 'E' is the right value and thus just needs be added together, you can use the less generic:

awk -F',' '
/Header2/ { next }
/^[^E]/   { count++ }
END       { print count + 0 }

'

| improve this answer | |

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.