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


 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers How to count lines of CSV file where 2 fields match variables?
# 1  
Old 07-07-2016
How to count lines of CSV file where 2 fields match variables?

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...
Code:
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...
Code:
countvar=`awk ' $2~/$color/ && $3=/Yes/ {c++} END{print c}' myfile.csv`

Assuming the variable is Blue, I would like to see
Code:
$ 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  
Old 07-07-2016
Code:
awk -F, '$2 ~ /Blue/ && $3 == "Yes" {c++} END {print c}' myfile.csv

This User Gave Thanks to Yoda For This Post:
# 3  
Old 07-07-2016
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?
Code:
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  
Old 07-07-2016
Define an awk variable using shell variable:-
Code:
color="Blue"

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

This User Gave Thanks to Yoda For This Post:
# 5  
Old 07-07-2016
Thank you!

That was exactly what I needed. I'd been trying to figure that out for hours!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Matching two fields in two csv files, create new file and append match

I am trying to parse two csv files and make a match in one column then print the entire file to a new file and append an additional column that gives description from the match to the new file. If a match is not made, I would like to add "NA" to the end of the file Command that Ive been using... (6 Replies)
Discussion started by: dis0wned
6 Replies

2. Shell Programming and Scripting

awk to combine lines if fields match in lines

In the awk below, what I am attempting to do is check each line in the tab-delimeted input, which has ~20 lines in it, for a keyword SVTYPE=Fusion. If the keyword is found I am splitting $3 using the . (dot) and reading the portion before and after the dot in an array a. If it does have that... (12 Replies)
Discussion started by: cmccabe
12 Replies

3. Shell Programming and Scripting

awk to remove lines where field count is greather than 1 in two fields

I am trying to remove all the lines and spaces where the count in $4 or $5 is greater than 1 (more than 1 letter). The file and the output are tab-delimited. Thank you :). file X 5811530 . G C NLGN4X 17 10544696 . GA G MYH3 9 96439004 . C ... (1 Reply)
Discussion started by: cmccabe
1 Replies

4. Shell Programming and Scripting

awk to output match and mismatch with count using specific fields

In the below awk I am trying output to one file those lines that match between $2,$3,$4 of file1 and file2 with the count in (). I am also trying to output those lines that are missing between $2,$3,$4 of file1 and file2 with the count of in () each. Both input files are tab-delimited, but the... (7 Replies)
Discussion started by: cmccabe
7 Replies

5. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

6. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

7. Shell Programming and Scripting

Print only lines where fields concatenated match strings

Hello everyone, Maybe somebody could help me with an awk script. I have this input (field separator is comma ","): 547894982,M|N|J,U|Q|P,98,101,0,1,1 234900027,M|N|J,U|Q|P,98,101,0,1,1 234900023,M|N|J,U|Q|P,98,54,3,1,1 234900028,M|H|J,S|Q|P,98,101,0,1,1 234900030,M|N|J,U|F|P,98,101,0,1,1... (2 Replies)
Discussion started by: Ophiuchus
2 Replies

8. Shell Programming and Scripting

How to find lines that match exact input and count?

I am writing a package manager in BASH and I would like a small snippet of code that finds lines that match exact input and count them. For example, my file contains: xyz xyz-lib2.0+ xyz-lib2.0 xyz-lib1.5 and "grep -c xyz" returns 4. The current function is: # $1 is the package name.... (3 Replies)
Discussion started by: cooprocks123e
3 Replies

9. Shell Programming and Scripting

How to (n)awk lines of CSV with certain number of fields?

I have a CSV file with a variable number of fields per record. How do I print lines of a certain number of fields only? Several permutations of the following (including the use of escape characters) have failed to retrieve the line I'm after (1,2,3,4)... $ cat myfile 1,2,3,4 1,2,3 $ # Print... (1 Reply)
Discussion started by: cs03dmj
1 Replies

10. Shell Programming and Scripting

Getting variables out of .csv lines

I have a csv file looking like: echo,w-cai,w-cai-ssl echo,countrywide,countrywide-ssl haystack,intranet3,intranet3-ssl haystack,pnf,pnf-ssl Basically, I want to process this file row by row assigning each word delimited by a comma to a variable. ie. for the first row, $variable1=echo... (5 Replies)
Discussion started by: Sn33R
5 Replies
Login or Register to Ask a Question