SED delete csv column if exists

Question

I need to remove a column from my tabulated CSV file if this column exists.

My CSV file:

GENE REF ALT
AKT  A   G
AKT  G   G

Desired output: if column REF exists delete this column

GENE  ALT
AKT    G
AKT    G

I tried to do that:

sed 's/\tREF.[^\t]*//' filename.csv

but it doesn't work.

Answer 1

Hi with miller (http://johnkerl.org/miller/doc) and this input.csv

GENE,REF,ALT
AKT,A,G
AKT,G,G

is very easy

mlr --csv cut -x -f REF input.csv

The output is

GENE,ALT
AKT,G
AKT,G

Answer 2

With perl

$ perl -F'\t' -lane '@non_ref_cols = grep { $F[$_] ne "REF" } 0..$#F if $. == 1;
                     print join "\t", @F[@non_ref_cols]' ip.txt
GENE    ALT
AKT     G
AKT     G

• -F'\t' use tab as field separator
• @non_ref_cols = grep { $F[$_] ne "REF" } 0..$#F if $. == 1 for the header line, get index of all fields that is NOT REF
• print join "\t", @F[@non_ref_cols] print all fields using the index array we got for the header line, using tab as field separator

Answer 3

With awk:

awk -F'\t' -v OFS='\t' '
  NR == 1 {for (i=1; i<=NF; i++) if ($i == "REF") refCol = i}
  refCol {for (i = refCol + 1; i <=NF; i++) $(i-1) = $i; NF--}
  1
' file.csv

The first line finds which column is the REF column. The refCol variable will be uninitialized if there is no such column.
The second line removes the column if it has been found.
The third line prints the record.

Answer 4

You could determine which column REF is at with grep and then decide to delete it, e.g. with GNU cut and assuming your headings are single words and delimiters are tabs:

colnumber=$(head -n1 file.tsv | grep -o '[^\t]\+' | grep -nx 'REF' | cut -d: -f1)
[[ -n $colnumber ]] && cut --complement -f$colnumber file.tsv > file.tsv.new

Output:

GENE    ALT
AKT     G
AKT     G