CODEWITHSUNDEEP
regexbashcsv
Zuhaib Ahmed
Zuhaib Ahmed

Reputation: 497

Bash - Extract a column from a tsv file whose header matches a given pattern

I've got a tab-delimited file called dataTypeA.txt. It looks something like this:

Probe_ID    GSM24652    GSM24653    GSM24654    GSM24655    GSM24656    GSM24657
1007_s_at   1149.82818866431    1156.14191288693    743.515922643437    1219.55564561635    1291.68030259557    1110.83793199643
1053_at 253.507372571459    150.907554200493    181.107054946649    99.0610660103702    147.953428467212    178.841519788697
117_at  157.176825094869    147.807257232552    162.11169957066 248.732378039521    176.808414979907    112.885784025819
121_at  1629.87514240262    1458.34809770171    1397.36209234134    1601.83045996129    1777.53949459116    1256.89054921471
1255_g_at   91.9622298972477    29.644137111864 61.3949774595639    41.2554576367652    78.4403716513328    66.5624213750532
1294_at 313.633291641829    305.907304474766    218.567756319376    335.301256439494    337.349552407502    316.760658896597
1316_at 195.799277107983    163.176402437481    111.887056644528    194.008323756222    211.992656497053    135.013920706472
1320_at 34.5168433158599    19.7928225262233    21.7147425051394    25.3213322300348    22.4410631949167    29.6960283168278
1405_i_at   74.938724593443 24.1084307838881    24.8088845994911    113.28326338746 74.6406975005947    70.016519414531
1431_at 88.5010900723741    21.0652011409692    84.8954961447585    110.017339630928    84.1264201735067    49.8556999547353
1438_at 26.0276274326623    45.5977459152141    31.8633816890024    38.568939176828 43.7048363737468    28.5759163094148
1487_at 1936.80799770498    2049.19167519573    1902.85054762899    2079.84030768241    2088.91036902825    1879.84684705068
1494_f_at   358.11266607978 271.309665853292    340.738488775022    477.953251687206    388.441738062896    329.43505750512
1598_g_at   2908.90515715761    4319.04621682741    2405.62061966298    3450.85255814957    2573.97860992156    2791.38660060659
160020_at   416.089910909237    327.353902186303    385.030831004533    385.199279534446    256.512900212781    217.754025190117
1729_at 43.1079499314469    114.654670657195    133.191500889286    86.4106614983387    122.099426341898    218.536976034472
177_at  75.9653827137444    27.4348937420347    16.5837374743166    50.6758325717831    58.7568500760629    18.8061888366161
1773_at 31.1717741953018    158.225161489953    161.976679771553    139.173486349393    218.572194156366    103.916119454
179_at  1613.72113870554    1563.35465407698    1725.1817757679 1694.82209331327    1535.8108561345 1650.09670894426

Let's say I have a variable col="GSM24655". I want to extract the column from dataTypeA.txt that corresponds to this column name.

Additionally, I'd like to put this in a function, where I can just give it a file (i.e. dataTypeA.txt), and a column (i.e. GSM24655), and it'll return that column. I'm not very proficient in Bash, so I've been having some trouble with this. I'd appreciate the help.

Upvotes: 0

Views: 919

Answers (3)

nisetama
nisetama

Reputation: 8863

This also works for printing multiple columns in the specified order:

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 GSM24655 Probe_ID<input.tsv

Or another option is csvtk -t cut -fGSM24655,Probe_ID input.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, like 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, like "bb" is changed to bb.

Upvotes: 0

j23
j23

Reputation: 3530

Below script using awk can be used to achieve the objective.

col="GSM24655"; 
awk -v column_val="$col" '{ if (NR==1) {val=-1; for(i=1;i<=NF;i++) { if ($i == column_val) {val=i;}}} if(val != -1) print $val} ' dataTypeA.txt

Working: Initially, value of col is passed to awk script using -v column_val="$col" . Then the column number is find out. (when NR==1, i.e the first row, it iterates through all the fields (for(i=1;i<=NF;i++), awk variable NF contains the number of columns) and then compare the value of column_val (if ($i == column_val)), when a match is found the corresponding column number is found and stored ( val=i )). After that, from next row onwards, the values in that column is printed (print $val).

If you copy the below code into a file called say find_column.sh, you can call sh find_column.sh GSM24655 dataTypeA.txt to display the column having value of first parameter (GSM24655) in the file named second parameter (dataTypeA.txt). $1 and $2 are positional parameters. The lines column=$1 and file=$2 will assign the input values to the variables.

column=$1;
file=$2; 
awk -v column_val="$column" '{ if (NR==1) {val=-1; for(i=1;i<=NF;i++) { if ($i == column_val) {val=i;}}} if(val != -1) print $val} ' $file

Upvotes: 2

Marco
Marco

Reputation: 1232

I would use the following, it is quick and easy.

In your script, you get the name of the file, let's say $1, and word, $2.

Then, in my for each I am using the whole header, but you can just add a head -1 $1, and in the IF, the $2, this is going to output column name.

c=0;
for each in `echo "Probe_ID    GSM24652    GSM24653    GSM24654    GSM24655    GSM24656    GSM24657"`;do if [[ $each == "Probe_ID" ]];then 
echo $c;
col=$c; 
else c=$(( c + 1 )); 
fi;
done

Right after this, you just do a cat $1| cut -d$'\t' -f$col

Upvotes: 1

Related Questions