Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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?

share|improve this question
5  
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
1  
@rozcietrzewiacz The name; he wants to say id instead of $1 and age instead of $3 –  Michael Mrozek Nov 22 '11 at 17:25

7 Answers 7

up vote 10 down vote accepted

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

share|improve this answer
    
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
    
Updated my answer. Stop putting off learning it if you want to do stuff with it :) –  Mat Dec 3 '11 at 11:07

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
share|improve this answer

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"

share|improve this answer
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

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
}
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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 }
      }
} '
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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