I have a csv file with around 15000 lines and multiple columns. The data set looks like this:

A,B,C,   message               D, E, F
a,b,c,Hi how are you ?          d,e,f
a,b,c,Hi Hello.Are you ok?     d,e,f
a,b,This >are< should not be counted,d,e,f
a,b,c,Hi I'm good. Are you ok ? d,e,f
a,b,c,Hi You are good, are you? d,e,f
a,b,c,I dare to add this line   d,e,f

Here, how can I have to count the occurrences of the word 'are' in the message column using UNIX shell commands?

  • 1) Could there be are in other columns which should not be counted? (Otherwise it's trivial!) 2) Should Are with capital A be counted, too? 3) Is there a comma missing after the C in the first line?
    – Philippos
    Sep 25, 2019 at 6:00
  • 1)Yes, there could be 'are' in different columns which should not be counted. 2) 'Are' is case - insensitive. 3) No, I think I didn't added it. Those are column headers. @philippos Sep 25, 2019 at 6:59
  • I added some special cases to example: are in wrong column, two are in one line and are being part of dare. Is it right you want 1 are in lines 2, 3, 5 and 7, 2 are in line 6 and none in line 4?
    – Philippos
    Sep 25, 2019 at 8:39
  • 1
    1. this is not valid CSV. Fields containing the field separator should be enclosed in double-quotes. 2. the line with You are good, are you? contains a comma, which is the field separator. That line has 7 fields rather than the usual 6. 3. it looks like there's a missing , before column D. if that's the case then the You are good line has 8 fields rather than the normal 7.
    – cas
    Sep 25, 2019 at 9:38

2 Answers 2


Try this,

 grep -oc are file
  • -o Print only the matched part.
  • -c print the count of matching line.

You can add -i option if u need the result for Are also. Which will ignore the case sensitive of matching pattern

If we have multiple matching patterns in same line...

grep -o are file | wc -l

Using AWK:

awk -F ',' '$4 ~ /are/ {count++} END{print count}' file
awk -F ',' '$4 ~ /[Aa]re/ {count++} END{print count}' file
  • Thanks for the help. It works @msp9011. How to copy the rows containing the word 'are' and store them in a text file? Sep 25, 2019 at 5:28
  • @KrishnaManchodu grep are file > fileOut.txt
    – Siva
    Sep 25, 2019 at 5:43
  • Thanks @msp9011. It's really working. Sep 25, 2019 at 7:02
  • I found a strange thing @msp9011. It collects all the rows that are having 'are' in any one column. But I need to collect those rows which have 'are' in their fourth column i.e. even if it is present in third column and not in fourth column it should not consider that row. Also for the rows that contain 'are' in their fourth row should have the numerical values in fifth column above a threshold value, say 10. All the rows that satisfy these conditions should be written in the text file. Regards. Sep 25, 2019 at 8:16
  • 1
    1) You should probably mention the -o is a GNU grep extension. 2) The grep solutions count matches in the wrong column. 3) The awk solution doesn't count multiple occurences in one line. 4) All solutions find a match in I don't care
    – Philippos
    Sep 25, 2019 at 8:54

Here are two ways to tackle this problem:

sed -Ee '
' file.csv | wc -l

Here we are using GNU sed and in extended regex mode. The idea is to first isolate the fourth field and then in a case-insensitive way turn all word(s) "are" found in the pattern space now into newline(s). Then strip away any nonnewline(s) and take away a newline in lieu of sed placing of it's own every time it prints something to stdout. then we count the number of lines outputted by sed which will give us the sum of all case insensitive patterns of the word "are" in the fourth fields of every line.

perl -F, -lane '
   $k += split /(?<=\Ware)(?=\W)/i, $F[3];
   }{print $k-$.;
' file.csv

Perl we split the fourth field ($F[3]) around a case-insensitive word "are" , determined by surrounding "are" with \Ws on both ends. then we update the kount of the number of fields split. Note that the fields shall always be 1 more than the number of "are" words.

At the end of it when all lines have been read and processed, we print the kount minus the number of number of lines in the file.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .