1

Apologies for any duplication, but most questions I've come across relate to getting a specific value from a field in a row, or using tail to get n tailing lines from a file, where n is known a priori. I'm looking to find a row where a value is matched, and then get all fields in that row AND all following rows. Details below.

I have data files returned from an online database that have a variable number of metadata header rows containing information about the query criteria used to search the database. After these header rows is a tidy dataframe. Example:

Query date: February 3, 2020, 1:34:57 PM
Database: <database name>
\n
Search criteria:
\n
Geographic bounding box coordinates: -130.00 20.00; -130.00 24.00; -120.00 24.00; -120.00 20.00
Sample type: rocks > sediments > dust
\n
SAMPLE ID,REFERENCE,LONGITUDE,LATITUDE,X,Y,Z,A
56,Author (YYYY) Title: Journal,-127.3,22,1.7,2.3,0,0.55
56,Author (YYYY) Title: Journal,-127.34,22.4,1.9,1.3,0.5

I have successfully found the row containing data field names using:

SID=$(awk -F, '{ if ($1 == "SAMPLE ID") print NR }' data.csv)

echo $SID returns 9, as expected

Now I want to take that row of field names and all of the following rows that contain the data and send them to a new file. In other words, I wish to parse the whole input file, and send the rows where NR >= $SID to a new file.

This is the code I've been using, but it instead just returns almost all of the data, except for a few rows. I can't figure out how to get the data I want, or why it's omitting the rows that it is.

awk -F, -v r=$SID '{ if (NR >= $r) print $0}' data.csv > output.csv

Here's my expected output:

SAMPLE ID,REFERENCE,LONGITUDE,LATITUDE,X,Y,Z,A
56,Author (YYYY) Title: Journal,-127.3,22,1.7,2.3,0,0.55
56,Author (YYYY) Title: Journal,-127.34,22.4,1.9,1.3,0.5

Any help would be great! If it wasn't clear, I'm totally new to awk! Meaning I'd also welcome links to any good introductory materials for learning.

Improve this question
0

1 Answer 1

Reset to default
5

In awk, $r would refer to the value of the rth field, rather than the value of r itself. Your solution should work if you just replace $r by r :

awk -F, -v r=$SID '{ if (NR >= r) print $0}' data.csv

or (more idiomatically, using the default print action)

awk -F, -v r=$SID 'NR >= r' data.csv

However there's really no need to do it in two steps - either

awk -F, '$1 == "SAMPLE ID" {p=1} p' data.csv

or even (ignoring the CSV structure altogether)

awk '/^SAMPLE ID,/{p=1} p' data.csv

should work as well.

Improve this answer
9
  • Interestingly, your first 2 suggestions continued to return the full contents of the file, but the 3rd suggestion: awk -F, '$1 == "SAMPLE ID" {p=1} p' data.csv > output.csv did the trick! Can I ask what {p=1} p does? Thanks for your help :)
    – jaymullr
    Feb 13, 2020 at 0:32
  • For the intellectually curious, I did find another solution: 1) save sid variable as in the question 2) nrecords=awk -F, 'END {print NR}' data.csv` 3) let "nout = $nrecords - $sid + 1" 4) tail -n $nout data.csv > output.csv
    – jaymullr
    Feb 13, 2020 at 0:35
  • @jaymullr the first 2 suggestions assume that you have assigned the appropriate value to shell variable SID (using your SID=$(awk -F, '{ if ($1 == "SAMPLE ID") print NR }' data.csv) command for example). Did you do so? Otherwise (if $SID evaluates to zero) then all lines will be printed.
    – steeldriver
    Feb 13, 2020 at 0:36
  • I thought I had defined SID as stated in the question, but I wonder if there was a typo somewhere, because I quit my shell, started fresh, and now your first solution works correctly. Who knew that it was the $r that was getting me, though: I didn't realize variables declared didn't need the leading $
    – jaymullr
    Feb 13, 2020 at 0:42
  • @jaymullr yes in awk, $r would refer to the value of the rth field, rather than the value of r itself
    – steeldriver
    Feb 13, 2020 at 0:43

You must log in to answer this question.

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