awk: canonical way to reference a field by name (first line as header)

user_440172 0
Viewed 89

I often experience this kind of situation: initially my data looks like this:

$ cat data.csv
id name age
0 Mark 20
1 Robert 35
2 John 15

Given that I want to access the age field, I write my awk script like this:

$ awk '{ print $3; /* print age */ }' data.csv                                                                          
age
20
35
15

Then, eventually, the columns order got changed. For example, surname is inserted between name and age, so that now age is shifted to the 4th position:

$ cat data.csv                                                                                                              
id name surname age
0 Mark Ross 20
1 Robert Green 35
2 John Doe 15

If I run my script again, I obviously get the wrong output:

$ awk '{ print $3; /* print age */ }' data.csv                                                                              
surname
Ross
Green
Doe

To avoid this issue I use to reference the fields by name using this bit of code:

$ awk 'NR == 1 { split($0, keys) }; { for (i=1; i<=length(keys); i++) { f[keys[i]]=$i }; print f["age"]; /* print age */ }' data.csv
age
20
35
15

This works fine and allows me to reference any fields by name, such as f["id"], f["name"] etc. as desired.

What I don't like here is that the script looks hackish and verbose. I have the feeling that I'm missing out a native function or a more straightforward way to achieve something similar.

Does awk offer a canonical way to reference fields by name, using the first line as a header?

awk
5 Answers

I don't know what is canonical but you can avoid a lot of unnecessary copying.

From your code:

# why split again? we already have $1..$NF
NR == 1 { split($0, keys) }
{
    # why do this on every line of input?
    for (i=1; i<=length(keys); i++){ f[keys[i]]=$i }

    print f["age"]; /* print age */
}

to:

NR==1 { while (i++ < NF) f[$i]=i }

{ print $f["age"] }
#       ^

Note: I have tried to make the first-line pattern/action as concise as possible to address your verbosity concern but the more straightforward for(i=1;i<=NF;i++) is nearly as short and will be more maintainable than this while, and without the potential gotcha if i has already been used.

user_109715810

Setup:

$ head data.?.csv
==> data.1.csv <==
id name age
0 Mark 20
1 Robert 35
2 John 15

==> data.2.csv <==
id name surname age
0 Mark Ross 20
1 Robert Green 35
2 John Doe 15

I'd convert the keys[] array to an associative array (column headers/names are the keys of the associative array), eg:

colname='age'

for fname in data.1.csv data.2.csv
do
    echo "########## file: ${fname}"

    awk -v cname="${colname}" '
    FNR==1 { for (i=1;i<=NF;i++)
                 keys[$i]=i
           }
           { print $(keys[cname]) }
    ' "${fname}"
done

This generates:

########## file: data.1.csv
age
20
35
15
########## file: data.2.csv
age
20
35
15
user_73661000

If you are not strictly limited to GNU AWK then you might exploit GNU datamash as follows, let data.csv content be

id name age
0 Mark 20
1 Robert 35
2 John 15

then

datamash --header-in --field-separator=' ' cut age < data.csv

gives output

20
35
15

Explanation: --header-in informs that first line of input is header with names, --field-separator=' ' that columns are space-sheared, cut is operation age is field name.

(tested in GNU datamash 1.7)

user_107859750

I am not aware of the pure-awk standard method. I suggest for this task to use miller toolkit to manipulate tab-delimited files using column names. Below is a one-liner that filters a tab-delimited file (delimiter is specified using --tsv) and writes the results to STDOUT together with the header. The header is removed using tail and the lines are counted with wc.

mlr --tsv filter '$colA == "c" && $colB == "water"' file.txt | tail -n +2 | wc -l

SEE ALSO:

Note that miller can be easily installed, for example, using conda, specifically miniconda, like so:

conda create --name miller miller
user_9676210

Using GNU Awk

Function

#!/bin/bash

getcolumns(){
  local OPTIND fname header before after fname
  
  __usage(){
    echo "Usage: ${FUNCNAME[1]} [OPTION]... -c [COLUMN],... [FILE]..."
    echo "Options:"
    echo "  -c <STR,...>   columns to be found"
    echo "  -a <STR>       add string after last column"
    echo "  -b <STR>       add string before first column"
    echo "  -s <STR>       set output delimiter"
    echo "  -d <STR>       set input delimiter"
    echo "  -h             display column header"
    echo "  -n             display filename"
    echo "Example: "
    printf "  %s -d, -s'\\\t' -c name,age file1 file2\n" "${FUNCNAME[1]}"
    exit 1;
  }

  while getopts "a:b:c:d:hns:" o; do
    case "${o}" in
        a) after=${OPTARG};;
        b) before=${OPTARG};;
        c) columns=${OPTARG};;
        d) delimiter=${OPTARG};;
        h) header=1;;
        n) fname=1;;
        s) separator=${OPTARG};;
        \?) echo "ERROR: Invalid option -$OPTARG";;
    esac
  done
  
  shift $((OPTIND-1))

  if [ -z "${columns}" ] || [ "${#}" -eq 0 ]; then
    __usage
  fi

  awk -v columns="$columns" \
      -v delimiter="${delimiter:= }" \
      -v separator="${separator:0 }" \
      -v header="$header" \
      -v fname="$fname" \
      -v before="$before" \
      -v after="$after" '
      BEGIN{ FS=delimiter; OFS=separator; split(columns,cols,",") }
      BEGINFILE{
        if(ERRNO){ print "Error: no such file " FILENAME; nextfile }
        if (fname) print "-----" FILENAME "-----"
      } 
      FNR==1{
        delete key; i=0;
        while(i++<NF)keys[$i]=i
        if(! header) next 
      }
      { 
        before ? out=before OFS : out=""
        #out=""
        for (x in cols){
          if(cols[x] in keys != 0 )
            out=sprintf("%s%s%s", out, $(keys[cols[x]]), OFS)
        }
        after ? out=out after : sub(OFS"$","",out)
      }
      out { print out }
  ' "${@}"
}

Usage

Usage: getcolumns [OPTION]... -c [COLUMN],... [FILE]...
Options:
  -c <STR,...>   columns to be found
  -a <STR>       add string after last column
  -b <STR>       add string before first column
  -s <STR>       set output delimiter
  -d <STR>       set input delimiter
  -h             display column header
  -n             display filename
Example: 
  getcolumns -d, -s'\t' -c name,age file1 file2

Files

$ head file[12]
==> file1 <==
id,name,surname,age
0,Mark,Ross,24
1,Robert,Green,45
2,John,Doe,19

==> file2 <==
n,age,foo,name
x,20,111,Kate
x,35,222,Nancy
x,15,333,Jimmy

Tests

getcolumns -d, -c age file2

20
35
15

getcolumns -d, -s'\t' -c name,age -b "foo" -a "bar baz" file1 file2 

foo     Mark    24      bar baz
foo     Robert  45      bar baz
foo     John    19      bar baz
foo     Kate    20      bar baz
foo     Nancy   35      bar baz
foo     Jimmy   15      bar baz
user_41778280
Related