how to retrieve specific entries from a csv file in python

Question

I have a CSV file rsvp1.csv:

                _id  event_id  comments
                 1 |  x      | hello..
                 2 |  y      | bye
                 3 |  y      | hey
                 4 |  z      | hi

My question is:
For each event e how can I get the comments written to a separate text file?

There is some error with the following code:

import csv

with open('rsvps1.csv','rU') as f:
    reader = csv.DictReader(f, delimiter=',')
    rows = list(reader)
fi = open('rsvp.txt','wb')
k=0
for row in rows:
  if k == row['event_id']:
    fi.write(row['comment']+"\n")
  else:
    fi.write(row['event_id']+"\t")
    fi.write(row['comment']+"\n")
    k= row['event_id']
f.close()
fi.close()

One thing I immediately notice is that you have `delimiter=','` when it looks like the file is using a delimiter of `|`. — Jakob Weisblat, May 26 '15 at 13:19
No It is a csv file so it is separated by , i just used | to demonstrate the way data is represented. — MEH, May 26 '15 at 14:30

score 0 · Answer 1 · edited May 23 '17 at 12:30

I would suggest that you use pandas as your import tool. It creates a clear datastructure of your csv-file, similar to a spreadsheet in MS Excel. You could then use iterrows to loop over your event_id's and process your comments.

import pandas as pd

data = pd.read_csv('rsvps1.csv', sep = ',')
for index, row in data.iterrows():
       print(row['event_id'], row['comment') #Python 3.x

However, I not sure what you want to write in the file. Just the comment for all event_id's? The complete 'comment'-column can be exported to a separate file by

data.to_csv('output.csv', columns = ['comment'])

Additional information according to comment:

When you want to save only certain comments that have the same event_id, then you have to select the corresponding rows at first. This is done by

selected_data = data[data['event_id'] == 'x']

for the event_id 'x'. selected_data now contains a dataframe that only holds rows that have 'x' in the 'event_id'-column. You can then loop through this dataframe as shown above.

I can save the event_id and comments to a text file. But in the end i want to make several small text files which have their name as the event_id and the text file should contain all the comments corresponding to that event_id. For all event_ids I want to export the comments to that event_id named file. — MEH, May 26 '15 at 14:32

score 0 · Answer 2 · answered May 26 '15 at 15:06

I think it is best to just forget you're working with a csv file and think of it as a normal file in which you can to the following.

with open('file.csv', 'r') as f:
    lines = f.readlines()
for line in lines:
    if not line.startswith('_id'):
        line_values = line.split(',')
        with open('%s.txt' % line_values[1], 'a') as fp:
            fp.write(line_values[2] + '\n')

Mike Müller · Answer 3 · 2015-05-26T15:55:38.950

Splitting the csv File

Given a file rsvps1.csv with this content:

_id,event_id,comments
1,x,hello
2,y,bye
3,y,hey
4,z,hi

This:

import csv
import itertools as it
from operator import itemgetter

with open('rsvps1.csv') as fin:
    fieldnames = next(csv.reader(fin))
    fin.seek(0)
    rows = list(csv.DictReader(fin))

for event_id, event in it.groupby(rows, key=itemgetter('event_id')):
    with open('event_{}.txt'.format(event_id), 'w') as fout:
        csv_out = csv.DictWriter(fout, fieldnames)
        csv_out.writeheader()
        csv_out.writerows(event)

splits it into three files:

event_x.txt

_id,event_id,comments
1,x,hello

event_y.txt

_id,event_id,comments
2,y,bye
3,y,hey

and event_z.txt

_id,event_id,comments
4,z,hi

Adapt the output to your needs.

Only Comments

If you do not want a csv as output, this becomes simpler:

import csv
import itertools as it
from operator import itemgetter

with open('rsvps1.csv') as fin:
    rows = list(csv.DictReader(fin))

for event_id, event in it.groupby(rows, key=itemgetter('event_id')):
    with open('event_{}_comments.txt'.format(event_id), 'w') as fout:
        for item in event:
            fout.write('{}\n'.format(item['comments']))

Now event_y_comments.txt has this content:

bye
hey

how to retrieve specific entries from a csv file in python

3 Answers3

Splitting the csv File

Only Comments