11

I have a text file like so

foo bar baz
1   a   alpha
2   b   beta
3   c   gamma

I can use awk to print certain columns, like 1 and 3, with {print $1, $3}, but I want to specify the columns to print by specifying the header of the column instead, something like {print $foo, $baz}. This is useful so I don't have to open the file and count the columns manually to see which column is which, and I don't have to update the script if the column number or order changes. Can I do this with awk (or another shell tool)?

16
awk '
NR==1 {
    for (i=1; i<=NF; i++) {
        f[$i] = i
    }
}
{ print $(f["foo"]), $(f["baz"]) }
' file
foo baz
1 alpha
2 beta
3 gamma

That is an immensely useful idiom. I have a lot of data in spreadsheets and different spreadsheets might have a common subset of columns I'm interested in but not necessarily in the same order across all spreadsheets or with the same numbers of other columns before/between them so being able to export them as CSV or similar and then simply run an awk script using the column names instead of column numbers is absolutely invaluable.

|improve this answer|||||
  • This is great thanks and works for my purposes. Are you able to clarify how this works for an awk beginner? What is the f[$i] syntax doing in this, and how does awk work out which columns match the strings? – AlexLipp Jul 30 '19 at 13:19
  • You're welcome. That's absolutely basic awk syntax, just look up fields and arrays in the awk man page (or google it). Add print i and print $i and print f[$i]` statements in the loop, etc. to trace whats happening if that helps. – Ed Morton Jul 30 '19 at 14:32
0

You ask for awk, but you could also use a more specialized tool for this: csvtool.

csvtool -t ' ' -u ' ' namedcol foo,baz file

or

csvtool -t ' ' -u ' ' col 1,3 file
|improve this answer|||||
0

Assuming that the file is a TSV ("tab separated values") file, using csvkit:

$ csvcut -t -c foo,baz file.tsv
foo,baz
1,alpha
2,beta
3,gamma

The output will be properly formatted CSV, but could easily be changed back to TSV:

$ csvcut -t -c foo,baz file.tsv | csvformat -T
foo     baz
1       alpha
2       beta
3       gamma

The -c option to csvcut can also take numbers and ranges, and can also be used to rearrange the columns of the input data (a feature I often miss in the standard cut utility).

|improve this answer|||||

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.