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?
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=1; i <= length(out); i++)
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.)
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.
Mar 4, 2016 at 10:32
OFS printing. Also, a more elegant and direct way of getting the out length is from the split: len=split(cols,out,",". Now the 2nd for loop becomes (also resolves extra field printing): for(i=1; i<=len; i++) {if(i<len) printf "%s%s", $ix[out[i]], OFS}; if(i=len) print $ix[out[i]]} (can't write code block here)
Dec 2, 2022 at 17:36
awk answer gives no warning if duplicate column names are present, and returns duplicate copies of the first-named column it encounters, instead of moving on to output an identically-named second column.
Apr 15, 2023 at 1:02
csvkitConvert 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.
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.
-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.
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
}
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
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"
time { command(s); }).
Dec 1, 2011 at 15:45
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
Dec 1, 2011 at 19:03
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
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.
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.
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.
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):
github.com links fare?
Apr 15, 2023 at 0:46
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
See below command-
awk 'NR==1{for (i=1; i<=NF; i++) if ($i == "id" ){a=i} else if($i == "age"){b=i}}
{print $a, $b}' file.tsv
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 }
}
} '
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)
I made the solution from srk (https://unix.stackexchange.com/a/115226/79404) a little bit more universal.
Note: my answer doesn't work for the initially mentioned case with space as a separator. Please let me know if have ideas how to fix it.
Put this into .bashrc:
printcol() {
FILE="$1"
COLUMN="$2"
SEPARATOR="${3:-|}" # You can change default separator here.
cut -f $(head -1 "$FILE" | \
# Remove BOM characters
sed '1s/^\xEF\xBB\xBF//' | \
# Print each column name on a separate line
sed 's/'$SEPARATOR'/\'$'\n/g' | \
grep -P -n '^'$COLUMN'$' | \
cut -f1 -d":") -d $SEPARATOR "$FILE"
}
Usage for the file with | separator:
printcol my_file.csv ColumnName
Usage for the file with , separator:
printcol my_file.csv ColumnName ","
mo=$(awk '{for(i=1;i<=NF;i++){if ($i ~ /name|age/){print i}}}' filename|awk 'ORS=" "')
col1=`echo $mo| awk '{print $1}'`
col2=`echo $mo| awk '{print $2}'`
awk -v col1="$col1" -v col2="$col2" '{print $col1,$col2}' filename
name age
ed 50
joe 70
I use this awk function which prints the columns in the specified order (and not in the original order like how cut -f3,1 is the same as cut -f1,3):
tcut(){ awk -F\\t 'NR==FNR{a[NR]=$0;next}FNR==1{for(i=1;i<=NF;i++)b[$i]=i}{for(i in a)printf"%s"(i==length(a)?RS:FS),$(b[a[i]])}' <(printf %s\\n "$@") -;};tcut id age<file.tsv
Another option:
csvtk -t cut -f id,age file.tsv
The -t flag uses tab as field separator but it doesn't disable CSV-style treatment of double quotes. The -l flag (--lazy-quotes) allows including unescaped double quotes as part of the data, but it still prints pairs of double quotes around fields that contain double quotes, so for example the field aa"aa is changed to ""aa"aa"". -l also doesn't disable removing double quotes around fields that don't need to be quoted in CSV, so for example the field "bb" is changed to bb.
idinstead of$1andageinstead of$3