csv file column reading and extracting using python

Question

i have the following code...

reader=csv.DictReader(open("test1.csv","r"))
allrows = list(reader)

keepcols = [c for c in allrows[0] if all(r[c] != '0' for r in allrows)]

print keepcols
writer=csv.DictWriter(open("output1.csv","w"),fieldnames='keepcols',extrasaction='ignore')
writer.writerows(allrows)

i have a csv file which has about 45 cols..
the first column has some names..
except the first column, all others have only 0's and 1's... and of course, the whole table has some titles as well..
i m trying to read columns from csv file and i need to extract only those cols with 1's
the problem is the output file is empty even though there are a few columns in the table with 1's..

could somebody please help me out.... :( i m stuck terribly..

Title    3003_contact    3003_backbone   3003_sidechain  3003_polar  3003_hydrophobic    3003_acceptor   3003_donor  3003_aromatic
l1  1   1   0   1   1   0   0   0
l1  1   0   1   0   0   0   1   0
l1  1   0   0   0   0   0   0   0
l1  1   0   0   0   1   0   0   1
l1  1   0   0   0   0   0   0   0
l2  1   0   0   0   1   0   0   0
l2  1   0   0   0   0   1   0   0
l3  1   0   0   0   0   0   0   0
l3  1   0   0   0   0   0   1   0
l3  1   0   0   0   0   0   0   1
l3  1   0   0   0   0   0   0   0
l3  1   0   0   0   0   0   0   0
l4  1   0   0   0   0   0   0   0
l4  1   0   0   0   0   0   0   0
l4  1   0   0   0   0   0   0   0

Answer 1

Edit: If the input file is a comma-separated values file, then to maintain the order of the keys, use reader.fieldnames instead of the keys in allrows[0].

So the solution would be:

keepcols = [c for c in reader.fieldnames if any(r[c] != '0' for r in allrows)]

The input file posted above looks like it has space-separated columns. In this case, I don't think csv is the right tool for parsing it. Instead, you can use split:

import csv
with open("test1.csv","r") as f:
    fields=next(f).split()
    # print(fields)
    allrows=[]
    for line in f:
        line=line.split()
        row=dict(zip(fields,line))
        allrows.append(row)
        # print(row)
    keepcols = [c for c in fields if any(row[c] != '0' for row in allrows)]
    print keepcols
    writer=csv.DictWriter(open("output1.csv","w"),fieldnames=keepcols,extrasaction='ignore')
    writer.writerows(allrows)

Edit2: The reason why the column order was changing is because for c in allrows[0] returns the keys of allrows[0] in an unspecified order. dict keys are not ordered by default. The above code works around this by defining fields to be a list, not a dict.

Original answer: Change fieldnames='keepcols' to fieldnames=keepcols.

fieldnames needs to be a sequence of keys, such as ['fieldA','fieldB',...].

A potential pitfall to be aware of in Python is that strings are sequences. When you iterate over a string, you get the characters of the string. So when you say fieldnames='keepcols', you are setting fieldnames to be the sequence of characters ['k','e','e','p','c','o','l','s']. You don't get an error because this is a valid sequence of keys. But your list of dicts, allrows doesn't happen to have these keys. writer.writerows blithely ignores this since extrasaction='ignore'.