CODEWITHSUNDEEP
pythondictionaryexport-to-csv
turtle_in_mind
turtle_in_mind

Reputation: 1152

python dictionary to csv where each key is in seperate row and value in separate columns

I am having trouble trying to output my dictionary to CSV file. I have a Dictionary that contains as keys time dates and as values, companies pertaining to those dates which are in string format. I tried looking through the website for identical question, but it doesn't really help my case. I tried the following code and managed to get the key in first row, values in second column but thats not what i want. 

import csv
with open('dict1.csv','w') as f:
    w = csv.writer(f,delimiter=',')
    for key,values in sorted(a.items()):
        w.writerow([key,values])

But this gives me a CSV file in following format:

2009/01/02  ['AA' 'BB' 'AAPL'] etc
2009/01/03  ['AA' 'CC' 'DD' 'FF']

Hence I only have two columns. But I want:

2009/01/02  'AA' 'BB' 'AAPL'
2009/01/02  'AA' 'CC' 'DD'  'FF'

in 4 separate columns for first row and 5 for the second row respectively.
I even tried 

for dates in sorted(a):
    w.writerow([date] + my_dict[date] )

But this gives me error saying unsupported operand types for + 'timestamp' and 'str'.

Any help will be appreciated. Thanks

Upvotes: 1

Views: 3215

Answers (3)

blooby
blooby

Reputation: 1

You may need to put something like this:

for key,values in sorted(a.items()):
    w.writerow(str(key) + "," + ","join(values))

",".join(values) will split the list of values into a string delimited by commas. I'm assuming you want commas separating your columns because you are writing a csv file, even though in your example the columns are separated by tab.

Upvotes: 0

RedDevil
RedDevil

Reputation: 13

Am sorry if I read this wrong, but are you using python pandas?

" I have a Dictionary that contains as keys pandas time dates and as values, >companies pertaining to those dates which are in string format."

in that case something like this might work

import pandas as pd
df = pd.DataFrame(mydict)
df = df.transpose()
df.to_csv('dict1.csv',encoding='utf-8')

the to_csv method be default uses ',' as a delimiter which you can change if needed.

Upvotes: 1

Roadmaster
Roadmaster

Reputation: 5357

This line is putting the key (the date) in the key variable, and the values, as a list in values. So values will indeed contain something like ['AA' 'BB' 'AAPL'].

for key,values in sorted(a.items()):

Next, you're telling writerow "write a row with two elements: the first is the key, the second is whatever is in values" (which is a list so it's just converted to a string representation and output like that).

    w.writerow([key,values])

so [key, values] looks like this:

[2009/01/02, ['AA','BB','AAPL']]
 ^^^^^^^^^^  ^^^^^^^^^^^^^^^^^^
 the key     this whole thing
             is the single value

What you'd want, I think, is to create a single list containing the key and strings, not one containing the key and a list of strings. You could extend a list with the extra values like so:

    the_row = [key]  # Create the initial row with just key
    the_row.extend(values)  # Add the values, one by one, to the row
    w.writerow(the_row)  # Write the full row

list.extend(L) does: Extend the list by appending all the items in the given list.

Upvotes: 2

Related Questions