Python: Read from and write to a CSV file

Question

I am trying to read data from a CSV file (A), extract data, and write that to a different CSV file (B). In the new file B, I want to have two columns. Column 1 to list names of column 1 in file A and column 2 to list the count of column 1 in file A. So for example, if the file A looks like this without ':' (they are lined up in two columns):

Animal: Gender
Rabbit: Male
Dog: Male
Rabbit: Female
Cat: Male
Cat: Male
Dog: Female
Dog: Male
Turtle: Male

I want the output in file B to look like this (actually in different columns without ':' again):

Animal: Count
Cat: 2
Dog: 3
Rabbit: 2
Turtle: 1

This is the very first time I am doing anything like this and this is what I have so far, but I am failing in having the data printed out in file B and have the "count" done correctly. Could anybody please help me with this?

import csv
ReadData=csv.reader(open('C:\Users\..\FileA.csv','rb'), delimiter=',')

def column(ReadData, i):
    return [row[i] for row in ReadData]

for line in ReadData:
   WriteData=csv.writer(open('C:\Users\..\FileB.csv','wb'),
                        delimiter=' ', quotechar=':', quoting=csv.QUOTE_ALL)
   print column(ReadData,1)

Thank you for your help in advance!

Answer 1

To do the counting in Python >=2.7, see this example for collections.Counter. With a collections.defaultdict, see here.

In your call to csv.writer, the quotechar=':' is probably an error (this would make WriteData.writerow(['Hello World', 12345]) emit ":Hello World: 12345", as if the colon was a quotation mark.

Also note that your function column(ReadData, i) consumes ReadData; subsequent calls to ReadData will probably return an empty list (not tested). This is not a problem for your code (at least not now).

This is a solution without the CSV module (after all, these files do not look too much like CSV):

import collections

inputfile = file("A")

counts = collections.Counter()

for line in inputfile:
    animal = line.split(':')[0]
    counts[animal] += 1

for animal, count in counts.iteritems():
    print '%s: %s' % (animal, count)

Answer 2

I will answer the counting part of your question, and perhaps you can combine it with the csv part of your question.

l = [
    ('Animal','Gender'),
    ('Rabbit','Male'),
    ('Dog','Male'),
    ('Rabbit','Female'),
    ('Cat','Male'),
    ('Cat','Male'),
    ('Dog','Female'),
    ('Dog','Male'),
    ('Turtle','Male')
    ]

d = {}
for k,v in l:
    if not k in d:
        d[k] = 1
    else:
        d[k] += 1

for k in d:
    print "%s: %d" % (k,d[k])

I didn't filter your header line, the output of this code is:

Turtle: 1
Cat: 2
Rabbit: 2
Animal: 1
Dog: 3

EDIT:

You can replace this:

if not k in d:
    d[k] = 1
else:
    d[k] += 1

With this:

d[k] = d.setdefault(k,0) + 1

Answer 3

Have a look at the itertools module and the groupby function. For example:

from itertools import groupby

animals = [
    ('Rabbit', 'Male'),
    ('Dog', 'Male'),
    ('Rabbit', 'Female'),
    ('Cat', 'Male'),
    ('Cat', 'Male'),
    ('Dog', 'Female'),
    ('Dog', 'Male'),
    ('Turtle', 'Male')
    ]

def get_group_key(animal_data):
    return animal_data[0]

animals = sorted(animals, key=get_group_key)
animal_groups = groupby(animals, get_group_key)

grouped_animals = []
for animal_type in animal_groups:
    grouped_animals.append((animal_type[0], len(list(animal_type[1]))))

print grouped_animals

>>> [('Cat', 2), ('Dog', 3), ('Rabbit', 2), ('Turtle', 1)]

Answer 4

Depending on the size of data and complexity... you may want to consider using pandas - info at http://pandas.pydata.org/ and available on PyPi.

Note however, this is likely over-kill, but thought I'd throw it in to the mix.

from pandas import DataFrame

# rows is processed from string in the OP
rows = [['Rabbit', ' Male'], ['Dog', ' Male'], ['Rabbit', ' Female'], ['Cat', ' Male'], ['Cat', ' Male'], ['Dog', ' Female'], ['Dog', ' Male'], ['Turtle', ' Male']]

df = pandas.DataFrame(rows, columns=['animal', 'gender'])

>>> df.groupby('animal').agg(len)
        gender
animal        
Cat          2
Dog          3
Rabbit       2
Turtle       1

>>> df.groupby(['animal', 'gender']).agg(len)
animal  gender 
Cat      Male      2
Dog      Female    1
         Male      2
Rabbit   Female    1
         Male      1
Turtle   Male      1