How to slice a single CSV file into several smaller ones grouped by a field and deleting columns in the final csv's?

Question

Even thought this might sound as a repeated question, I have not found a solution. Well, I have a large .csv file that looks like:

prot_hit_num,prot_acc,prot_desc,pep_res_before,pep_seq,pep_res_after,ident,country
1,gi|21909,21 kDa seed protein [Theobroma cacao],A,ANSPV,L,F40,EB
1,gi|21909,21 kDa seed protein [Theobroma cacao],A,ANSPVL,D,F40,EB
1,gi|21909,21 kDa seed protein [Theobroma cacao],L,SSISGAGGGGLA,L,F40,EB
1,gi|21909,21 kDa seed protein [Theobroma cacao],D,NYDNSAGKW,W,F40,EB
....

The aim is to slice this .csv file into multiple smaller .csv files according to the last two columns ('ident' and 'country').

I have used a code from an answer in a previous post and is the following:

csv_contents = []
with open(outfile_path4, 'rb') as fin:
  dict_reader = csv.DictReader(fin)   # default delimiter is comma
  fieldnames = dict_reader.fieldnames # save for writing
  for line in dict_reader:            # read in all of your data
    csv_contents.append(line)         # gather data into a list (of dicts)

# input to itertools.groupby must be sorted by the grouping value 
sorted_csv_contents = sorted(csv_contents, key=op.itemgetter('prot_desc','ident','country'))


for groupkey, groupdata in it.groupby(sorted_csv_contents, 
                                  key=op.itemgetter('prot_desc','ident','country')):

  with open(outfile_path5+'slice_{:s}.csv'.format(groupkey), 'wb') as fou:
    dict_writer = csv.DictWriter(fou, fieldnames=fieldnames)    
    dict_writer.writerows(groupdata)

However, I need that my output .csv's just contain the column 'pep_seq', a desired output like:

pep_seq    
ANSPV
ANSPVL
SSISGAGGGGLA
NYDNSAGKW

What can I do?

Consider using `pandas.read_csv()`, and `pandas.to_csv()`, you can find a lot about these. — mehmetminanc, Mar 15 '16 at 14:31

score 2 · Accepted Answer · answered Mar 15 '16 at 15:06

Your code was almost correct, it just needed the fieldsnames to be set correctly and for extraaction='ignore' to be set. This tells the DictWriter to only write the fields you specify:

import itertools   
import operator    
import csv

outfile_path4 = 'input.csv'    
outfile_path5 = r'my_output_folder\output.csv'
csv_contents = []

with open(outfile_path4, 'rb') as fin:
    dict_reader = csv.DictReader(fin)   # default delimiter is comma
    fieldnames = dict_reader.fieldnames # save for writing

    for line in dict_reader:            # read in all of your data
        csv_contents.append(line)         # gather data into a list (of dicts)

group = ['prot_desc','ident','country']
# input to itertools.groupby must be sorted by the grouping value 
sorted_csv_contents = sorted(csv_contents, key=operator.itemgetter(*group))

for groupkey, groupdata in itertools.groupby(sorted_csv_contents, key=operator.itemgetter(*group)):
    with open(outfile_path5+'slice_{:s}.csv'.format(groupkey), 'wb') as fou:
        dict_writer = csv.DictWriter(fou, fieldnames=['pep_seq'], extrasaction='ignore')    
        dict_writer.writeheader()
        dict_writer.writerows(groupdata)

This will give you an output csv file containing:

pep_seq
ANSPV
ANSPVL
SSISGAGGGGLA
NYDNSAGKW

srowland · Answer 2 · 2016-03-15T15:04:05.947

The following would output a csv file per country containing only the field you need.

You could always add another step to group by the second field you need I think.

import csv

# use a dict so you can store the list of pep_seqs found for each country
# the country value with be the dict key
csv_rows_by_country = {}
with open('in.csv', 'rb') as csv_in:
    csv_reader = csv.reader(csv_in)
    for row in csv_reader:
        if row[7] in csv_rows_by_country:
            # add this pep_seq to the list we already found for this country
            csv_rows_by_country[row[7]].append(row[4])
        else:
            # start a new list for this country - we haven't seen it before
            csv_rows_by_country[row[7]] = [row[4],]

for country in csv_rows_by_country:
    # create a csv output file for each country and write the pep_seqs into it.
    with open('out_%s.csv' % (country, ), 'wb') as csv_out:
        csv_writer = csv.writer(csv_out)
        for pep_seq in csv_rows_by_country[country]:
            csv_writer.writerow([pep_seq, ])

How to slice a single CSV file into several smaller ones grouped by a field and deleting columns in the final csv's?

2 Answers2