Python Dictionary to CSV

Question

I have written code to read a CSV into a python dictionary, which works fine. I'm trying to get the dictionary back to a CSV. I have written the following:

import csv

itemDict={}

listReader = csv.reader(open('/Users/broberts/Desktop/Sum_CSP1.csv','rU'), delimiter = ',', quotechar='|')

for row in listReader:
    fID = row[0]
    fClassRange = row[1]
    fArea = row[2]

    if itemDict.has_key(fID):
        itemDict[fID][fClassRange]=fArea
    else:
        itemDict[fID] = {'5.0 to 5.25':'','5.25 to 5.5':'','5.5 to 5.75':'','5.75 to 6.0':'','6.0 to 6.25':'','6.25 to 6.5':'','6.5 to 6.75':'','6.75 to 7.0':'','7.0 to 7.25':'','7.25 to 7.5':'','7.5 to 7.75':'','7.75 to 8.0':'','8.0 to 8.25':'',}
        itemDict[fID][fClassRange]=fArea

listWriter = csv.writer(open('/Users/broberts/Desktop/Sum_CSP1_output.csv', 'wb'), delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL)

for a in itemDict:
    print a
    listWriter.writerow(a)

In the last block, listWriter will not write anything to the CSV though it will print a. I believe this has something to do with a dictionary being unordered. I really need to write out the fID and each of the keys associated with each fID (fClassRange ex. "5.0 to 5.25") and then the value fArea associated with each fClassRange to the CSV, but I haven't even gotten that far in my code since I can't figure out how to write out even the fID.

I looked into using DictWriter, but I can't figure out how to tell it what the required fieldnames are.

Have to recommend Kenneth Reitz's [tablib](http://docs.python-tablib.org/en/latest/). It does more than what you were looking for here, so this is not a direct answer, just want to recommend this library to others. It has a great, easy to use API and you can serialize to csv, tsv, json, yaml, and xlsx effortlessly. — floer32, Nov 07 '13 at 18:30

Jeff · Answer 1 · 2018-04-27T19:20:37.730

35

Sample data:

mydict = [{"col1": 1000, "col2": 2000}, {"col1": 3000, "col2": 4000}]

One-liner for converting a list of dicts to CSV, using pandas:

import pandas as pd

pd.DataFrame(mydict).to_csv('out.csv', index=False)

Results:

col1,col2
1000,2000
3000,4000

edited Apr 27 '18 at 19:20

answered Jan 26 '18 at 21:46

Jeff

469
4
5

1

Great answer! Is it possible for the values to go in columns instead of rows? – Carlos Junior Jun 07 '22 at 14:31

score 25 · Answer 2 · edited Nov 01 '19 at 01:02

25

The default writer expects a list, which is why it won't work for you. To use the dictwriter, just change your listwriter = line to this:

with open('/Users/broberts/Desktop/Sum_CSP1_output.csv', 'wb') as outfile:
    listWriter = csv.DictWriter(
       outfile,
       fieldnames=itemDict[itemDict.keys()[0]].keys(),
       delimiter=',',
       quotechar='|',
       quoting=csv.QUOTE_MINIMAL
    )

Or, you can just set fieldnames to be fieldnames=['arbitrary','list','of','keys'] if you know what the fields are supposed to be.

edited Nov 01 '19 at 01:02

jpmc26

28,463
14
94
146

answered Nov 30 '11 at 19:20

Spencer Rathbun

14,510
6
54
73

Wow, thanks! But now I'm getting this message: ValueError: dict contains fields not in fieldnames: 4, 6, 3, 3, 9 – bojo Nov 30 '11 at 20:00
@bojo Whoops! You're making a dict of dicts. So to get the correct fieldnames you need the *internal dictionary* fieldnames. Also, each dictionary has to have the same fieldnames. Or else you need to use the DictWriter options for incorrect fieldnames. I've updated the line to use the correct dictionary for getting the fieldnames. – Spencer Rathbun Nov 30 '11 at 20:27
I'm sorry Spencer, I'm still getting the same ValueError message. – bojo Nov 30 '11 at 21:22
@bojo Hmm, on your `itemDict[fid] =` line, you set the fieldnames of your new dictionary to some default values. Could you use those in a list as your fields? Don't forget the fClassRange field you add afterwards. Or, change your print a statement to `print a.keys()` and just use that. – Spencer Rathbun Nov 30 '11 at 21:37
I really, really appreciate your help. I tried changing "print a" to "print a.keys()" just to see what happened, and I got: AttributeError: 'str' object has no attribute 'keys' -- maybe this indicates a problem? I also tried using the DictWriter statement where "fieldnames = myFieldnames" and "myFieldnames" was equal to the list of default class ranges as you suggest, but I still get the same ValueError. – bojo Nov 30 '11 at 22:17
If it's helpful, let me describe the problem and what I'm trying to do. The input CSV has a number of rows. The first column contains an ID number, the second column contains a class range, and the third column contains an area value. The second row may contain the same ID number, a different class range and area value, and so on. The ID may be repeated anywhere from zero to half a dozen times, depending on how many class ranges are appropriate for that ID number, and each class range has an area. The class ranges are the same for all ID numbers, but not all ID numbers have all class ranges. – bojo Nov 30 '11 at 22:18
What I'm trying to do is get a CSV with ONE ID number per row, establish the standard class ranges as columns, and populate each column with the appropriate area value. Class ranges that are absent for a particular ID number in the input ID should be populated with zero for the area value. – bojo Nov 30 '11 at 22:18
@bojo Ah, the print a.keys() bit is insightful. That means the itemDict does not have dictionaries in it. Which, come to think of it, is a mistake on my part. I missed that you have `for a in itemDict`. This won't work. You need `for key,value in itemDict.iteritems()`. Then use `listwriter.writerow(value)`. – Spencer Rathbun Dec 01 '11 at 13:48

score 5 · Answer 3 · answered Nov 23 '21 at 04:58

An example with writerows:

import csv

def main():

    results = [
            {'File': 'test.txt', 'Temperature': 32.33, 'Day': 'Monday'},
            {'File': 'test2.txt', 'Temperature': 44.33, 'Day': 'Tuesday'},
            {'File': 'test3.txt', 'Temperature': 44.23, 'Day': 'Sunday'}
        ]

    with open('results.csv', 'w') as f:  
        w = csv.DictWriter(f, results[0].keys())
        w.writeheader()        
        w.writerows(results)                    
        
if __name__ == "__main__":
    main()

which will result in results.csv of:

File,Temperature,Day
test.txt,32.33,Monday
test2.txt,44.33,Tuesday
test3.txt,44.23,Sunday

score 3 · Answer 4 · answered Mar 29 '17 at 01:42

This is what i use, its simple and works fine for me. when you have only one dictionary, use this

my_dict = {"tester": 1, "testers": 2}
with open('mycsvfile.csv', 'wb') as f:  
    w = csv.DictWriter(f, my_dict.keys())
    w.writerow(dict((fn,fn) for fn in my_dict.keys()))
    w.writerow(my_dict)

$ cat mycsvfile.csv
testers,tester
2,1

When you have a list of dictionaries, like what you get from SQL queries, you do like this.

my_dict = ({"tester": 1, "testers": 2},{"tester": 14, "testers": 28})
with open('mycsvfile.csv', 'wb') as f:  
    w = csv.DictWriter(f, my_dict[0].keys())
    w.writerow(dict((fn,fn) for fn in my_dict[0].keys()))
    w.writerows(my_dict)

cat mycsvfile.csv
testers,tester
2,1
28,14

`w.writerow(dict((fn,fn) for fn in my_dict.keys())) ` is cool, but - why don't just just call `w.writeheader()`, it does the same thing and is more understandable? :) Is there a benefit in the transposed dict? — Todor Minakov, Sep 30 '20 at 19:15

score 1 · Answer 5 · answered Oct 14 '15 at 07:36

1

For posterity:

You should use iteritems() to iterate over a dictionary, so the last part becomes

for name, values in itemDict.iteritems():
    print values
    listWriter.writerow(values)

answered Oct 14 '15 at 07:36

Arjen

11
1

Voilin · Answer 6 · 2020-06-02T20:00:38.383

0

d = [{'a': 1, 'b': 2},{'a': 3, 'b': 4}]

with open('csv_file.csv', 'w', newline='\n') as f:
    w = csv.DictWriter(f, d[0].keys())
    w.writeheader()
    for i in d:
        w.writerow(i)

gets you

a,b
1,2
3,4

edited Jun 02 '20 at 20:00

answered Jun 02 '20 at 13:29

Voilin

353
5
11

1

The formatting on your answer appears to be broken - the last few lines were indented an extra time :) – Christian Scott Jun 02 '20 at 14:05

Duck Ling · Answer 7 · 2022-01-04T19:14:16.443

0

What helped me was adding newline="" argument when opening file.

ex:

with open("sample.csv", "w", newline="") as outfile:
    writer = csv.writer(outfile)
    for num in range(0, 10):
        writer.writerow([num])

src

edited Jan 04 '22 at 19:14

answered Jan 04 '22 at 16:44

Duck Ling

1,577
13
20

score -1 · Answer 8 · answered Apr 13 '19 at 14:39

Easiest Way

You can convert the dictionary into Dataframe and write it to csv Eg

import pandas as pd
my_dict = {"tester": 1, "testers": 2}
df=pd.DataFrame(my_dict,index=[0])
df.to_csv("path and name of your csv.csv")

output

   tester  testers
0       1        2

Python Dictionary to CSV

8 Answers8

Linked

Related