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.

3

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.

  • 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 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 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 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 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 at 0:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.