CODEWITHSUNDEEP
awkcut
wkde
wkde

Reputation: 453

How to remove fields with all zeros

I have a file that looks like this :

header,d0,d1,d2,d3, ...
s1,0,5,2,8, ...
s2,0,8,2,4, ...
s3,0,7,3,4, ...
s4,0,3,2,1, ...
...

I want to remove any column with all zeros like d0
I can manually inspect for columns with all zeros and find d0 and execute 

cut -d "," -f 1,3- file> file_revised

The desired output is 

header,d1,d2,d3, ...
s1,5,2,8, ...
s2,8,2,4, ...
s3,7,3,4, ...
s4,3,2,1, ...
...

But since I have so many columns, it is hard to inspect manually.
How can I automatically remove columns with all zeros?
Thank you.

Upvotes: 1

Views: 279

Answers (5)

stack0114106
stack0114106

Reputation: 8711

Using Perl

> cat sumin.txt
header,d0,d1,d2,d3
s1,0,5,2,8
s2,0,8,2,4
s3,0,7,3,4
s4,0,3,2,1
> cat rem_zero.sh
perl -F, -lane '
@FH=@F if $.==1;

if($.>1)
{
$F[$_] and $nz[$_]||=1 for 0..$#F;
push(@L,[@F]);
}

END {
@cols = grep $nz[$_], 0..$#nz;
print join(",",@FH[@cols]);
for my $line (@L) { print "@{$line}[@cols]" }
}

' $1
> rem_zero.sh sumin.txt
header,d1,d2,d3
s1 5 2 8
s2 8 2 4
s3 7 3 4
s4 3 2 1
>

Upvotes: 0

F. Knorr
F. Knorr

Reputation: 3055

Provided that the first column does not contain all zeros, this awk script should do the job

awk -F',' '(NR==FNR && NR >1){for(i = 1; i <= NF; i++)
                              {a[i] = a[i]+$i}}
           (FNR!=NR){out=$1
                     for(i = 2; i<= NF; i++){
                      if(a[i]!=0){out=out","$i}
                     }
                    print out
                    }' file_name file_name

Note that the sript takes the name of the input file file_name twice!

For example, for the input:

header,d0,d
s1,0,5,2,8,
s2,0,8,2,4,
s3,0,7,3,4,
s4,0,3,2,1,

the script yields as output

header,d
s1,5,2,8
s2,8,2,4
s3,7,3,4
s4,3,2,1

Upvotes: 2

James Brown
James Brown

Reputation: 37404

Here is one that gathers the fields to print to a variable (p="$1,$3" ... etc.) and uses system to call awk to print p: 

$ awk '
BEGIN { FS=OFS="," }
NR==1 {
    for(i=1;i<=NF;i++)               # gather all field numbers to c[]
        c[i]
    next }
{
    for(i in c)                      # test all fields that still are all zeros
        if($i!=0)
            delete c[i] }
END {                                # after testing all the records
    for(i=1;i<=NF;i++)
        if(!(i in c))
            p=p (p==""?"":OFS) "$" i # make list of list of fields to print
    p="print " p                     # p="print $1,$3,$4,$5,$6"
    system("awk \047BEGIN{FS=OFS=\",\"}{" cmd "}\047 " FILENAME)
}' file

Output:

header,d1,d2,d3, ...
s1,5,2,8, ...
s2,8,2,4, ...
s3,7,3,4, ...
s4,3,2,1, ...

If all fields are all zeros, p="print" and the whole file gets printed.

Upvotes: 2

GerryLon
GerryLon

Reputation: 64

maybe you can use sed command like below:

$ sed 's/\b0\,\b//g' test.txt
header,d0,d1,d2,d3
s1,5,2,8
s2,8,2,4
s3,7,3,4
s4,3,2,1

Upvotes: 0

oguz ismail
oguz ismail

Reputation: 50750

$ cat file
header,d0,d1,d2,d3
s1,0,5,2,8
s2,0,8,2,4
s3,0,7,3,4
s4,0,3,2,1
$
$ cat tst.awk
NR==1 {
    for (i=1; i<=NF; ++i)
        a[i]
    next
}
NR==FNR {
    for (i in a)
        if ($i != "0")
            delete a[i]
    next
}
{
    sep = ""
    out = ""
    for (i=1; i<=NF; ++i) {
        if (i in a)
            continue
        out = out sep $i
        sep = FS
    }
    print out
}
$
$ awk -F, -f tst.awk file file
header,d1,d2,d3
s1,5,2,8
s2,8,2,4
s3,7,3,4
s4,3,2,1

Upvotes: 1

Related Questions