Finding a substring in a column of CSV file

Question

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?

Answer 1

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

Answer 2

Here are two ways to tackle this problem:

sed -Ee '
   y/,/\n/;s/\n/,/3;s//,/3;y/\n,/,\n/
   s/.*\n(.*)\n.*/\1/;s/\<[aA][rR][eE]\>/\n/g
   s/[^\n]*//g;/\n/!d;s///
' 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.