Python: Writing Counter to a csv file

Question

I have a csv file of data that has the columns ‘number’, ’colour’, ’number2’, ’foo’, ’bar’, which looks like:

12, red, 124, a, 15p
14, blue, 353, c, 7g
12, blue, 125, d, 65h
12, red, 124, c, 12d

I want to count the number of times number, colour and number2 occur together, so for example, the output from the above list would be: ’12, red, 124 :2’,’14, blue, 353: 1’, ’12, blue, 125: 1’. I’ve done this by using:

import csv
datafile=open('myfile.csv','r')
usefuldata=[] 
for line in datafile: 
    usefuldata.append(line) 
from collections import Counter
outfile1=Counter((line[1],line[2],line[3]) for line in usefuldata)  
print(outfile1)

This gives me :

Counter({(‘12’,’red’,’135’): 21, (‘15’,’blue’,’152’):18, (‘34’,’green’,’123’):16 etc})

Which is great, but I’d like to write this out to a file. I'd like the file to have 4 columns: number, colour, number2, and count. I realise this is a common question and I’ve tried a few different approaches suggested on other threads, but none have worked.

Newfile=open(‘newfile.csv’,’wb’)
fieldnames=['a','b']
csvwriter=csv.DictWriter(newfile, delimiter=',', fieldnames=fieldnames)
csvwriter.writerow(dict((fn,fn) for fn in fieldnames))
for row in outfile1:
    csvwriter.writerow(row)

And

with open('newfile.csv','wb') as csvfile:
    fieldnames=['number','colour','number2']
    writer=csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerow(Counter((line[1],line[2],line[3]) for line in usefuldata))
    countwriter=csv.writer(csvfile, delimiter=', ')
    countwriter.writerow(outfile1)

Both give me the error

    return self.writer.writerow(self._dict_to_list(rowdict))
TypeError: 'str' does not support the buffer interface

I've also tried using pickle:

import pickle
with open('newfile.csv','wb') as outputfile:
    pickle.dump(outfile1, outputfile)

gives me gibberish files.

My current attempt is to use

writer=csv.DictWriter(newfile, outfile1)
for line in outfile1:
    writer.writerow(line)

but this gives me an error about fieldnames.

I know this is a common question and I'm conscious that I'm only struggling because I really don't know what I'm doing- it has been a few years since I've used python and I've forgotten so much. Any help would be greatly appreciated.

If you are passing a generator/sequence, use "writerows", not "writerow". Pickle files are gibberish to people, but readable to Pickle, if you want to load somewhere else. The arguments to DictWriter is: csvfile, fieldnames, ..., so your first argument is a file, your second is a list of field names. — swstephe, Sep 16 '15 at 15:32

score 6 · Accepted Answer · answered Sep 16 '15 at 15:31

First of all, the reason for the main issue -

TypeError: 'str' does not support the buffer interface

is that you are openning the file in binary mode, you should open the file in text mode ( without b ).

Secondly, I would say it would be easier to use normal csv.writer than csv.DictWriter() in your case, because of the way your dictionary is created.

A way to write your result to csv would be -

#Assuming you have previously created the counter you want to write
#lets say you stored the counter in a variable called cnter
with open('newfile.csv','w') as csvfile:
    fieldnames=['number','colour','number2','count']
    writer=csv.writer(csvfile)
    writer.writerow(fieldnames)
    for key, value in cnter.items():
        writer.writerow(list(key) + [value])

score 3 · Answer 2 · answered Mar 02 '19 at 14:53

import csv

Assuming count is a Python 3 Counter.
If key is a string, to not split it in every character it contains :

with open(root+'counter_test.csv','w') as csvfile:
    writer=csv.writer(csvfile)
    for key, value in count.items():
        writer.writerow([key] + [value])

And even simpler (take care of the 's' to writerows() function) :

with open(root+'counter_test.csv','w') as csvfile:
    writer=csv.writer(csvfile)
    writer.writerows(count.items())

score 1 · Answer 3 · answered Sep 17 '18 at 04:46

for me the above solution did not work. It splitted all the characters of the word in separate columns so the output was "every character in a separate column followed by the count" rather than entire word in one column followed by count. It might have been due to some other errors that i might have made. However for me the below code worked ::

    with open(outfile, encoding='utf-8-sig', mode='w') as fp:
        fp.write('KMC,freq\n')  
        for tag, count in cnt.items():  
            fp.write('{},{}\n'.format(tag, count))

I hope this is helpful for others

Yes. Thanks for sharing this alternative. I was also encountering a similar issue. — Mitali Patel, Jun 24 '21 at 06:49

score 1 · Answer 4 · answered Feb 21 '22 at 22:38

Using pandas DataFrame

you can use this simple one liner :

pd.DataFrame(Counter(my_list).most_common(), columns=["item", "count"]).to_csv("./myfile.csv")

Pandas can create a DataFrame from a dictionary (and a Counter), you have to specify the names of the columns for the DataFrame in the constructor (hence the columns=["item", "count"])

Then, the DataFrame class has a simple to_csv() method (you may wish to use index=False when calling this method)

score 0 · Answer 5 · answered Jun 26 '19 at 11:09

Simply by using for loop we can add sequence counter to the CSV reade following code will desplay the counter to the csv file

import csv

x=0
reader = csv.reader(open("c:/path/abc.csv"))
for raw in reader:
    x += 1
    print(raw)
    print(x)

above code will desplay output like this

['id', 'fname', 'lname'] 1 ['1', 'a', 'x'] 2 ['2', 'b', 'y'] 3 ['3', 'c', 'z'] 4

Python: Writing Counter to a csv file

5 Answers5

Linked