2

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.

  • Use awk: awk 'NF == 3 { print $1,$3} file` – Valentin Bajrami Jan 4 at 10:32
  • Hi Valentin, thanks for your reply. The thing is sometimes REF column can exists sometimes not. Is with your command line it will remove always column 2? – user979974 Jan 4 at 10:40
  • No. The NF checks if there are at least 3 fields. You might also want awk -vOFS="\t" 'NF.....} file. – Valentin Bajrami Jan 4 at 12:29
  • what about awk '{ print $1, $NF}' OFS='\t' infile? – αғsнιη Jan 4 at 14:43
  • 2
    sed is not the right tool for this job. The word REF does not appear on every line. – glenn jackman Jan 4 at 16:03
4

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
  • 2
    This miller tool works good :-) – user979974 Jan 7 at 11:05
4

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
  • 1
    Nice solution. I improved your variable name for clarity. – glenn jackman Jan 4 at 16:01
1

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.

1

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
  • Very nice. Great idea to use --complement. You might want to add -d $'\t' to the cut command. I would use grep -nx REF, but given the previous grep, that's no different. – glenn jackman Jan 4 at 15:58
  • After a 2nd look, this would be safer given it's a tab-separated file and spaces would be allowed as valid field characters: grep -Eo [^\t]+ | grep -nx REF – glenn jackman Jan 4 at 16:05
  • @glennjackman: I agree, updated. – Thor Jan 4 at 16:21

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.