Delete empty column from csv file with bash script

Question

Looking for some way to look at a csv file and delete columns including the header that have no values in the subsequent lines that follow.

Perhaps if I wanted to delete column Test03 from below including Test03 in the first line.

Test01,Test02,Test03,Test04  
11,22,,44  
11,22,,44  
11,22,,44  
11,22,,44  
11,22,,44  
11,22,,44  

Answer 1

Here's an awk solution that performs agnostic to whichever columns are empty (ignoring the header).

awk -F, '{
    a[NR]=$0
}NR>1{
    for (i=1;i<=NF;i++) 
        if(length($i)!=0) b[i]++
}END{
    for (k=1;k<=NR;k++) { 
        LINE="" ; 
        split(a[k],c,",") ; 
        for (j=1;j<=NF;j++) 
            if(b[j]>0) 
                LINE=LINE","c[j] ; 
        print substr(LINE,2,length(LINE)-1)
    } 
}' test.csv

Answer 2

In current case you can just do:

sed 's/,,/,/g' test.csv > new.csv

This'll replace all double commas with just one, effectively removing your empty column. Note that you'll need to remove the column from the header yourself.

Answer 3

If you want to delete possibly non-empty columns (including in the header), use the 'cut' command:

cut -d , -f 1,2,4 test.csv > new.csv

Answer 4

awk joins the party.

awk -F "," '{print $1","$2","$4}' test.csv > new.csv

Answer 5

This calls for a program rather than a quick command. The best way to do it would be, as suggested by Andrew Medico, to employ a proper CSV parser (in the case of perl you have Text::CSV).

However, I thought I'd write a perl script that works in very simple cases:

perl -F, -lane 'if($.==1){@a=@F;next};for($i=0;$i<@F;$i++){if($F[$i]!=""){push @c,$F[$i];push @b,$i}}if(@a){foreach(@b){push @t,$a[$_]};print join(",",@t);undef @a}print join(",",@c);undef @c' file.csv

This saves the first line and goes on to see if there are any empty fields in the next line. It then prints only the relevant headers, skipping the empty field in all lines.

Please note that it doesn't handle commas inside quoted strings. It does, however, turn:

Test01,Test02,Test03,Test04
11,22,,44
11,22,,44
11,22,,44
11,22,,44
11,22,,44
11,22,,44

into:

Test01,Test02,Test04
11,22,44
11,22,44
11,22,44
11,22,44
11,22,44
11,22,44

Answer 6

While trying different bash approaches I needed to remove all empty columns (including the header) reliably. To solve this I used Python with Pandas.

import pandas as pd

data = pd.read_csv('test.csv', sep='\t')
data.dropna(axis=1).to_csv('test_clean.csv')

The important thing here is to add the axis=1 to tell Pandas to apply the dropna to columns instead of rows.

Answer 7

For a typical user the easiest way would be to import data in Excel from this CSV file and export it once again after removing the column.