How to count lines of CSV file where 2 fields match variables?

UNIX for Beginners Q & A
1

I'm trying to use awk to count the occurrences of two matching fields of a CSV file.

For instance, for data that looks like this...

Joe,Blue,Yes,No,High
Mike,Blue,Yes,Yes,Low
Joe,Red,No,No,Low
Joe,Red,Yes,Yes,Low

I've been trying to use code like this...

countvar=`awk ' $2~/$color/ && $3=/Yes/ {c++} END{print c}' myfile.csv`

Assuming the variable is Blue, I would like to see

$ echo $countvar
2

The third column seems to work, but I can't get the second to work with a variable. Can anybody tell me what I'm doing wrong?

Thank you

2 
awk -F, '$2 ~ /Blue/ && $3 == "Yes" {c++} END {print c}' myfile.csv
1 Like
3

Thanks for the reply, Yoda. But the $3 variable was working okay. I need to get use a variable for the second field instead of /Blue/.

Something like this?

countvar=`awk -F, '$2 ~ /$color/ && $3 == "Yes" {c++} END {print c}' myfile.csv`

I think it's my use of the variable that is not working.

Thanks again

4

Define an awk variable using shell variable:-

color="Blue"

awk -F, -v COL="$color" '$2 == COL && $3 == "Yes" {c++} END{print c}' myfile.csv
1 Like
5

Thank you!

That was exactly what I needed. I'd been trying to figure that out for hours!