Read column value in a CSV file starting from another column value?

Question

I am working on extracting certain data from OECD statistics. These are available in a CSV file and look as follows (excerpt):

"COUNTRY","Country","DAGEGR","Age groups","DSEX","Gender","DSTATUS","Status of population","YEAR","Year","Value","Flag Codes","Flags"
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2002","2002",19640979,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2003","2003",19872646,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2004","2004",20091504,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2005","2005",20339759,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2006","2006",20605488,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2007","2007",21015042,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2008","2008",21431781,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2009","2009",21874920,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2010","2010",22342398,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2011","2011",22620554,,
"AUS","Australia","900000","All ages","90","Total males+females","90","All population","2012","2012",22683573,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2002","2002",444050,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2003","2003",448300,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2004","2004",451600,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2005","2005",455000,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2006","2006",469086,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2007","2007",476187,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2008","2008",483799,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2009","2009",493500,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2010","2010",502066,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2011","2011",511840,,
"LUX","Luxembourg","900000","All ages","90","Total males+females","90","All population","2012","2012",524853,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2002","2002",40409330,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2003","2003",41550584,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2004","2004",42345342,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2005","2005",43038035,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2006","2006",43758250,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2007","2007",44474631,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2008","2008",45283259,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2009","2009",45828172,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2010","2010",45989016,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2011","2011",46152926,,
"ESP","Spain","900000","All ages","90","Total males+females","90","All population","2012","2012",46818221,,

What I am trying to do is to automatically read out how many inhabitants Spain (ESP) had in 2007. To achieve this I use the following command:

cat RPOP_16012023145346836.csv | cut -d "," -f 1,9,11 | sort

This command returns me the country, the year and the number of inhabitants, e.g.:

"AUS","2002",19640979
"AUS","2003",19872646
"AUS","2004",20091504
"AUS","2005",20339759
"AUS","2006",20605488
"AUS","2007",21015042
"AUS","2008",21431781
"AUS","2009",21874920
"AUS","2010",22342398
"AUS","2011",22620554
"AUS","2012",22683573
"AUT","2002",8139310
"AUT","2003",8067289
"AUT","2004",8140122
"AUT","2005",8206524
"AUT","2006",8265925
"AUT","2007",8298923
"AUT","2008",8331930
"AUT","2009",8355260
"AUT","2010",8375290
"AUT","2011",8404252
"AUT","2012",8443018
"ESP","2002",40409330
"ESP","2003",41550584
"ESP","2004",42345342
"ESP","2005",43038035
"ESP","2006",43758250
"ESP","2007",44474631
"ESP","2008",45283259
"ESP","2009",45828172
"ESP","2010",45989016
"ESP","2011",46152926
"ESP","2012",46818221
"LUX","2002",444050
"LUX","2003",448300
"LUX","2004",451600
"LUX","2005",455000
"LUX","2006",469086
"LUX","2007",476187
"LUX","2008",483799
"LUX","2009",493500
"LUX","2010",502066
"LUX","2011",511840
"LUX","2012",524853

I would like to pass this result using a pipe to a statement that returns the third column (column 3 = population) based on the country (column 1 = ESP) and the year (column 2 = 2007). Unfortunately, I do not have the appropriate command for this. Can someone help me with this?

Expected output is: 44474631

Answer 1

Using Miller (mlr) to first filter the data for only the record relating to Spain in 2007 using the two named fields COUNTRY and YEAR, and then to cut out the Value field from that record. The output will be presented without a header.

mlr --csv --headerless-csv-output \
    filter '$COUNTRY == "ESP" && $YEAR == 2007' then \
    cut -f Value \
    RPOP_16012023145346836.csv

Given the data in the question, this would output

44474631

Instead of the filter expression

$COUNTRY == "ESP" && $YEAR == 2007

... you could use

$Country == "Spain" && $Year == 2007

... since those named fields also exist.

Answer 2

If the aim really is to simply return the number 44474631, you can do all in one using awk on the original input file:

awk -F, '$1=="\"ESP\"" && $9=="\"2007\"" {print $11}' RPOP_16012023145346836.csv

This will set the field separator to , and print the content of column 11, but only if column 1 is equal to "ESP" (including quotes), and column 9 is equal to "2007" (again, including quotes).

Answer 3

You could use awk for that:

$ sed 's/"//g' input_file | awk -F, '($1=="ESP" && $9==2007){print $1,$9,$11}'
ESP 2007 44474631