26

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)?

0

3 Answers 3

45
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.

3
  • 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
    Commented Jul 30, 2019 at 13:19
  • 2
    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
    Commented Jul 30, 2019 at 14:32
  • 1
    I am struck that this answer is not accepted and not at the top.
    – Jona Engel
    Commented Jun 11, 2021 at 9:31
5

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
0
2

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).

You must log in to answer this question.

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