32

I have the following file:

id  name  age
1   ed    50
2   joe   70   

I want to print just the id and age columns. Right now I just use awk:

cat file.tsv | awk '{ print $1, $3 }'

However, this requires knowing the column numbers. Is there a way to do it where I can use the name of the column (specified on the first row), instead of the column number?

  • 7
    cat isn't necessary, BTW. You could use awk '{ print $1, $3 }' file.tsv – Eric Wilson Nov 22 '11 at 16:58
  • If not column number, then what would you like to depend on? – rozcietrzewiacz Nov 22 '11 at 17:22
  • 2
    @rozcietrzewiacz The name; he wants to say id instead of $1 and age instead of $3 – Michael Mrozek Nov 22 '11 at 17:25
  • see also discussion on stackoverflow – Hotschke Feb 23 '15 at 10:41

12 Answers 12

37

Maybe something like this:

$ cat t.awk
NR==1 {
    for (i=1; i<=NF; i++) {
        ix[$i] = i
    }
}
NR>1 {
    print $ix[c1], $ix[c2]
}
$ awk -f t.awk c1=id c2=name input 
1 ed
2 joe
$ awk -f t.awk c1=age c2=name input 
50 ed
70 joe

If you want to specify the columns to print on the command line, you could do something like this:

$ cat t.awk 
BEGIN {
    split(cols,out,",")
}
NR==1 {
    for (i=1; i<=NF; i++)
        ix[$i] = i
}
NR>1 {
    for (i in out)
        printf "%s%s", $ix[out[i]], OFS
    print ""
}
$ awk -f t.awk -v cols=name,age,id,name,id input 
ed 1 ed 50 1 
joe 2 joe 70 2 

(Note the -v switch to get the variable defined in the BEGIN block.)

  • I've been putting off learning awk...what's the best way to support a variable number of columns? awk -f t.awk col1 col2 ... coln input would be ideal; awk -f t.awk cols=col1,col2,...,coln input would work too – Brett Thomas Dec 1 '11 at 15:03
  • 1
    Updated my answer. Stop putting off learning it if you want to do stuff with it :) – Mat Dec 3 '11 at 11:07
  • 3
    The 2nd example does not output the columns in the expected order, for (i in out) has no inherent ordering. gawk offers PROCINFO["sorted_in"] as a solution, iterating over the index with a for( ; ; ) is probably better. – mr.spuratic Mar 4 '16 at 10:32
  • @BrettThomas, highly recommend this tutorial. (If you have access to lynda.com, I even more highly recommend "Awk Essential Training," which covers all the same material but more concisely and with practice exercises.) – Wildcard May 14 '16 at 6:05
  • Mr. Spuratic, you da man. I ran across the for( i in out) problem, worked fine w/ 3 fields, when I added 2 it did 4,5,1,2,3, instead of 1,2,3,4,5 like I was expecting. To get them in order you have to do for(i=1; i <= length(out); i++) – Severun Sep 25 '17 at 22:13
5

csvkit

Convert the input data to a csv format and use a csv tool such as csvcut from the csvkit:

$ cat test-cols.dat 
id  name  age
1   ed    50
2   joe   70 

Install csvkit:

$ pip install csvkit

Use tr with its squeeze option -s to convert it into a valid csv file and apply csvcut:

$ cat test-cols.dat | tr -s ' ' ',' | csvcut -c id,age
id,age
1,50
2,70

If you want to return to the old data format, you can use tr ',' ' ' | column -t

$ cat test-cols.dat | tr -s ' ' ',' | csvcut -c id,age | tr ',' ' ' | column -t
id  age
1   50
2   70

Notes

  • csvkit supports also different delimiters (shared option -d or --delimiter), but returns a csv file:

    • If the file uses only spaces to separate columns (no tabs at all), following works

      $ csvcut -d ' ' -S -c 'id,age' test-cols.dat
      id,age
      1,50
      2,70
      
    • If the file uses a tab to separate columns, following works and csvformat can be used to get back tsv file:

      $ csvcut -t -c 'id,age' test-cols.dat | csvformat -T
      id  age
      1   50
      2   70
      

      As far as I have checked, only a single tab is allowed.

  • csvlook can format the table in a markdown table format

    $ csvcut -t -c "id,age" test-cols.dat | csvlook
    | id | age |
    | -- | --- |
    |  1 |  50 |
    |  2 |  70 |
    
  • UUOC (Useless Use Of Cat): I like it this way to construct the command.

  • +1. But unnecessary uses of tr, too. TSV files are supported directly, without any need to convert them to CSV. The -t (aka --tabs) option tells cvscut to use tabs as field delimiter. And -d or --delimiter to use any character as delimiter. – cas May 14 '16 at 7:19
  • With some testing, it seems the -d and -t options are semi-broken. they work to specify the input delimiter, but the output delimiter is hardcoded to always be a comma. IMO that's broken - it should either be the same as the input delimiter or have another option to allow the user to set the output delimiter, like awk's FS and OFS vars. – cas May 14 '16 at 7:25
4

If you just want to refer to those fields by their names instead of numbers, you can use read:

while read id name age
do
  echo "$id $age"
done < file.tsv 

EDIT

I saw your meaning at last! Here's a bash function that will print out only the columns you specify on the command line (by name).

printColumns () 
{ 
read names
while read $names; do
    for col in $*
    do
        eval "printf '%s ' \$$col"
    done
    echo
done
}

Here's how you can use it with the file you've presented:

$ < file.tsv printColumns id name
1 ed 
2 joe 

(The function reads stdin. < file.tsv printColumns ... is equivalent of printColumns ... < file.tsv and cat file.tsv | printColumns ...)

$ < file.tsv printColumns name age
ed 50 
joe 70 

$ < file.tsv printColumns name age id name name name
ed 50 1 ed ed ed 
joe 70 2 joe joe joe

Note: Pay attention to the names of the columns you request! This version lacks sanity checks, so nasty things can happen if one of the arguments is something like "anything; rm /my/precious/file"

  • 1
    This also requires knowing the column numbers. Just because you name them id, name and age, does not change the fact that the order is hard-coded in your read line. – janmoesen Nov 22 '11 at 19:54
  • 1
    @janmoesen Yes, I finally got the point :) – rozcietrzewiacz Nov 23 '11 at 0:43
  • This is nice, thanks. I'm working with large files (1000 columns, millions of rows) so am using awk for speed. – Brett Thomas Dec 1 '11 at 15:06
  • @BrettThomas Oh I see. I'm very curious then: could you post some benchmark that gives the time comparison? (Use time { command(s); }). – rozcietrzewiacz Dec 1 '11 at 15:45
  • @rozceitrewaicz: time cat temp.txt | ./col1 CHR POS > /dev/null 99.144u 38.966s 2:19.27 99.1% 0+0k 0+0io 0pf+0w time awk -f col2 c1=CHR c2=POS temp.txt > /dev/null 0.294u 0.127s 0:00.50 82.0% 0+0k 0+0io 0pf+0w – Brett Thomas Dec 1 '11 at 19:03
4

Just trowing a Perl solution into the lot:

#!/usr/bin/perl -wnla

BEGIN {
    @f = ('id', 'age');   # field names to print
    print "@f";           # print field names
}

if ($. == 1) {            # if line number 1
    @n = @F;              #   get all field names
} else {                  # or else
    @v{@n} = @F;          #   map field names to values
    print "@v{@f}";       #   print values based on names
}
3

For what it's worth. This can handle any number of columns in the source, and any number of columns to print, in whatever output sequence you choose; just re-arrange the args...

eg. call: script-name id age

outseq=($@)
colnum=($( 
  for ((i; i<${#outseq[@]}; i++)) ;do 
    head -n 1 file |
     sed -r 's/ +/\n/g' |
      sed -nr "/^${outseq[$i]}$/="
  done ))
tr ' ' '\t' <<<"${outseq[@]}"
sed -nr '1!{s/ +/\t/gp}' file |
  cut -f $(tr ' ' ','<<<"${colnum[@]}") 

output

id      age
1       50
2       70
2

If the file you're reading could never possibly be user-generated, you could abuse the read builtin:

f=file.tsv
read $(head -n1 "$f") extra <<<`seq 100`
awk "{print \$$id, \$$age}" "$f"

The input file's entire first line is substituted into the argument list, so read is passed all the field names from the header line as variable names. The first of these gets assigned the 1 that seq 100 generates, the second gets the 2, the third gets the 3 and so on. Excess seq output is soaked up by the dummy variable extra. If you know the number of input columns ahead of time, you can change the 100 to match and get rid of extra.

The awk script is a double-quoted string, allowing the shell variables defined by read to be substituted into the script as awk field numbers.

1

Usually it is easier just to look at the file header, count the number of the column you need (c) and then use Unix cut:

cut -f c -d, file.csv

But when there are many columns or many files I use the following ugly trick:

cut \
  -f $(head -1 file.csv | sed 's/,/\'$'\n/g' | grep -n 'column name' | cut -f1 -d,) \
  -d, \ 
  file.csv

Tested on OSX, the file.csv is comma-delimted.

1

Here's one quick way for selecting a single column.

Say we want the column named "foo":

f=file.csv; colnum=`head -1 ${f} | sed 's/,/\n/g' | nl | grep 'foo$' | cut -f 1 `; cut -d, -f ${colnum} ${f}

Basically, take the header line, split it into multiple lines with one column name per line, number the lines, select the line with the desired name, and retrieve the associated line number; then use that line number as the column number to the cut command.

0

Looking for a similar solution (I need the column named id, which might have a varying column number), I came across this one:

head -n 1 file.csv | awk -F',' ' {
      for(i=1;i < NF;i++) {
         if($i ~ /id/) { print i }
      }
} '
0

I wrote a Python script for this purpose that basically works like this:

with fileinput.input(args.file) as data:
    headers = data.readline().split()
    selectors = [any(string in header for string in args.fixed_strings) or
                 any(re.search(pat, header) for pat in args.python_regexp)
                 for header in headers]

    print(*itertools.compress(headers, selectors))
    for line in data:
        print(*itertools.compress(line.split(), selectors))

I called it hgrep for header grep, it can be used like this:

$ hgrep data.txt -F foo bar -P ^baz$
$ hgrep -F foo bar -P ^baz$ -- data.txt
$ grep -v spam data.txt | hgrep -F foo bar -P ^baz$

The whole script is a bit longer, because it uses argparse to parse command line arguments and the code is as follows:

#!/usr/bin/python3

import argparse
import fileinput
import itertools
import re
import sys
import textwrap


def underline(s):
    return '\033[4m{}\033[0m'.format(s)


parser = argparse.ArgumentParser(
    usage='%(prog)s [OPTIONS] {} [FILE]'.format(
        underline('column-specification')),
    description=
        'Print selected columns by specifying patterns to match the headers.',
    epilog=textwrap.dedent('''\
    examples:
      $ %(prog)s data.txt -F foo bar -P ^baz$
      $ %(prog)s -F foo bar -P ^baz$ -- data.txt
      $ grep -v spam data.txt | %(prog)s -F foo bar -P ^baz$
    '''),
    formatter_class=argparse.RawTextHelpFormatter,
)

parser.add_argument(
    '-d', '--debug', action='store_true', help='include debugging information')
parser.add_argument(
    'file', metavar='FILE', nargs='?', default='-',
    help="use %(metavar)s as input, default is '-' for standard input")
spec = parser.add_argument_group(
    'column specification', 'one of these or both must be provided:')
spec.add_argument(
    '-F', '--fixed-strings', metavar='STRING', nargs='*', default=[],
    help='show columns containing %(metavar)s in header\n\n')
spec.add_argument(
    '-P', '--python-regexp', metavar='PATTERN', nargs='*', default=[],
    help='show a column if its header matches any %(metavar)s')

args = parser.parse_args()

if args.debug:
    for k, v in sorted(vars(args).items()):
        print('{}: debug: {:>15}: {}'.format(parser.prog, k, v),
              file=sys.stderr)

if not args.fixed_strings and not args.python_regexp:
    parser.error('no column specifications given')


try:
    with fileinput.input(args.file) as data:
        headers = data.readline().split()
        selectors = [any(string in header for string in args.fixed_strings) or
                     any(re.search(pat, header) for pat in args.python_regexp)
                     for header in headers]

        print(*itertools.compress(headers, selectors))
        for line in data:
            print(*itertools.compress(line.split(), selectors))

except BrokenPipeError:
    sys.exit(1)
except KeyboardInterrupt:
    print()
    sys.exit(1)
0

awk, for all its vintage, is inherently integer-indexed, as is cut.

Here are several tools designed to handle name-indexed data (most of them handling just CSV and TSV, which are very popular file formats):

0

Try this small awk utility to cut specific headers - https://github.com/rohitprajapati/toyeca-cutter

Example usage -

awk -f toyeca-cutter.awk -v c="col1, col2, col3, col4" my_file.csv

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.