41

I'm struggling with writing a dictionary of lists to a .csv file.

This is how my dictionary looks like:

dict[key1]=[1,2,3]
dict[key2]=[4,5,6]
dict[key3]=[7,8,9]

I want the .csv file to look like:

key1  key2  key3
1     4     7  
2     5     8
3     6     9

At first I write the header:

outputfile = open (file.csv,'wb')
writefile = csv.writer (outputfile)
writefile.writerow(dict.keys())

So far so good... However, my problem is that I don't know how I could assign one list to the corresponding column. e.g.:

for i in range(0,len(dict[key1])):
    writefile.writerow([dict[key1][i],dict[key2][i],dict[key3][i])

will randomly fill the columns. Another problem is, that I have to manually fill in the keys and can't use it for another dictionary with 4 keys.

suschi
  • 493
  • 2
  • 5
  • 8

6 Answers6

46

If you don't care about the order of your columns (since dictionaries are unordered), you can simply use zip():

d = {"key1": [1,2,3], "key2": [4,5,6], "key3": [7,8,9]}
with open("test.csv", "wb") as outfile:
   writer = csv.writer(outfile)
   writer.writerow(d.keys())
   writer.writerows(zip(*d.values()))

Result:

key3    key2    key1
7       4       1
8       5       2
9       6       3

If you do care about order, you need to sort the keys:

keys = sorted(d.keys())
with open("test.csv", "wb") as outfile:
   writer = csv.writer(outfile, delimiter = "\t")
   writer.writerow(keys)
   writer.writerows(zip(*[d[key] for key in keys]))

Result:

key1    key2    key3
1       4       7
2       5       8
3       6       9
Tim Pietzcker
  • 328,213
  • 58
  • 503
  • 561
  • how can we change rows to columns in here key1 \tab 7 \tab 4 \tab 1 – Chit Khine Sep 21 '16 at 06:59
  • 3
    If you look at the question, I used `zip()` to transpose the original data. If you do the operation again, you'll change rows to columns again. Note that `key1 - 7 - 4 - 1` is probably not what you want... – Tim Pietzcker Sep 21 '16 at 07:40
  • 1
    This runs for me and I get the output I want, but it still throws a typeError "zip argument #1 must support iteration". Any reason for this? – jwoe Aug 01 '17 at 15:16
  • 1
    Right, on Python 2, you need to use `wb`, whereas on Python 3, you need to use the `newline=''` option when opening a CSV file. – Tim Pietzcker Sep 19 '18 at 18:24
  • what if one has another key, say `"key4":[1,2,3,4]` that have different length, but one wants to output all of the values in it as well? – Jia Gao Oct 30 '18 at 23:08
  • 1
    @JasonGoal it maybe very late but if you are still wondering about the answer here is a gist where the person explained it perfectly https://gist.github.com/captmomo/7836eb53b7cee9fb7d38460f70942902 – Yaboku Feb 20 '20 at 09:57
  • @TimPietzcker how do you do the zip operation again? Would one do zip(zip())? – Prithvi Boinpally Jul 01 '22 at 04:05
  • @PrithviBoinpally: I don't understand what you mean by "again"? What would be the desired result? – Tim Pietzcker Jul 02 '22 at 10:03
  • @TimPietzcker oh I was referring to this: "If you do the operation again, you'll change rows to columns again.". The desired result would be to have the header as a row across the top and the columns going down should contain the values from different entries in the dict. With the current solution, I'm getting the keys on the left in one column and the values on the right horizontally. – Prithvi Boinpally Jul 03 '22 at 13:18
4

This will work even when the list in key are of different length.

    with myFile:  
        writer = csv.DictWriter(myFile, fieldnames=list(clusterWordMap.keys()))   
        writer.writeheader()
        while True:
            data={}
            for key in clusterWordMap:
                try:
                    data[key] = clusterWordMap[key][ind]
                except:
                    pass
            if not data:
                break
            writer.writerow(data)

You can use pandas for saving it into csv:

df = pd.DataFrame({key: pd.Series(value) for key, value in dictmap.items()})
df.to_csv(filename, encoding='utf-8', index=False)
Kumar Shubham
  • 39
  • 3
2

Given

dict = {}
dict['key1']=[1,2,3]
dict['key2']=[4,5,6]
dict['key3']=[7,8,9]

The following code:

COL_WIDTH = 6
FMT = "%%-%ds" % COL_WIDTH

keys = sorted(dict.keys())

with open('out.csv', 'w') as csv:
    # Write keys    
    csv.write(''.join([FMT % k for k in keys]) + '\n')

    # Assume all values of dict are equal
    for i in range(len(dict[keys[0]])):
        csv.write(''.join([FMT % dict[k][i] for k in keys]) + '\n')

produces a csv that looks like:

key1  key2  key3
1     4     7
2     5     8
3     6     9
jedwards
  • 29,432
  • 3
  • 65
  • 92
2

Roll your own without the csv module:

d = {'key1' : [1,2,3],
     'key2' : [4,5,6],
     'key3' : [7,8,9]}

column_sequence = sorted(d.keys())
width = 6
fmt = '{{:<{}}}'.format(width)
fmt = fmt*len(column_sequence) + '\n'

output_rows = zip(*[d[key] for key in column_sequence])

with open('out.txt', 'wb') as f:
    f.write(fmt.format(*column_sequence))
    for row in output_rows:
        f.write(fmt.format(*row))
wwii
  • 23,232
  • 7
  • 37
  • 77
2

save:

with open(path, 'a') as csv_file:
    writer = csv.writer(csv_file)
    for key, value in dict_.items():
        writer.writerow([key, ','.join(value)])
csv_file.close()        
print ('saving is complete')

read back:

with open(csv_path, 'rb') as csv_file:
    reader = csv.reader(csv_file);
    temp_dict = dict(reader);
mydict={k:v.split(',') for k,v in temp_dict.items()}    
csv_file.close()
return mydict
Alireza
  • 21
  • 1
1
key_list = my_dict.keys()    
limit = len(my_dict[key_list[0]])    

for index in range(limit):    
  writefile.writerow([my_dict[x][index] for x in key_list])
jcfollower
  • 3,103
  • 19
  • 25