list to string in df.to-csv()

Question

I have a dataframe which includes a column that has a list. When I write the dataframe to a file then re-open it, I end up converting the list to a string. Is there a way to safely read/write dataframes that have lists as members?

df1 = DataFrame({'a':[['john quincy', 'tom jones', 'jerry rice'],['bob smith','sally ride','little wayne'],['seven','eight','nine'],['ten','eleven','twelve']],'b':[9,2,4,5], 'c': [7,3,0,9]})

df1.to_csv('temp.csv')
df2 = read_csv('temp.csv')

#note how the list (df1) has been converted to a string (df2)
df1['a'][0]
['john quincy', 'tom jones', 'jerry rice']

df2['a'][0]
"['john quincy', 'tom jones', 'jerry rice']"

After reading in don't you want the reverse? lambda L: L.split(',') - not join again... — Jon Clements
– Jon Clements, Commented Nov 1, 2012 at 20:01
i put that in to show that it has been converted to a string. It was just illustrating the case. if you open the tempfile you will see that the list column has quotes around it. — zach
– zach, Commented Nov 1, 2012 at 20:18
submitted an issue on pandas github github.com/pydata/pandas/issues/2158 — zach
– zach, Commented Nov 1, 2012 at 20:51

root · Accepted Answer · 2012-11-01 20:33:17Z

2

No need to covert the list into string in the first place, list's will be converted to string automatically. Just write the dataframe containing a list, and use ast.literal_eval on df2

                                             a  b  c
0   ['john quincy', 'tom jones', 'jerry rice']  9  7
1  ['bob smith', 'sally ride', 'little wayne']  2  3
2                   ['seven', 'eight', 'nine']  4  0
3                  ['ten', 'eleven', 'twelve']  5  9

df1.to_csv('temp.csv')
df2 = read_csv('temp.csv')

Use ast.literal_eval to get the string back to list:

import ast
fd2['a']=df2['a'].apply(lambda x: ast.literal_eval(x))
type(df2['a'][1])

Output:

list

edited Nov 1, 2012 at 20:33

answered Nov 1, 2012 at 20:15

root

81.1k25 gold badges111 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

zach Over a year ago

root, thanks for your input. The string conversion part was misleading. I want to do that as part of my script but I was getting an error because although I expected a list I returned as string.

zach Over a year ago

@ root. Your answer works great. I rewrote the question for clarity. Although I would prefer if the pandas read/write parsers would recognize this automatically.

Andy Hayden Over a year ago

Note: you can do this without the lambda: just do .apply(ast.literal_eval)

Jon Clements · Accepted Answer · 2012-11-01 20:03:42Z

1

The problem is here:

df2['a'] =df2['a'].map(f)
                   ^^^^^^

Where f = lambda x : ','.join(x)

There's no point joining it again, you want to split it to a list:

df2['a'] = df2['a'].map(lambda L: L.split(','))

answered Nov 1, 2012 at 20:03

Jon Clements

143k34 gold badges254 silver badges288 bronze badges

1 Comment

zach Over a year ago

I rewrote the answer to remove the map function as it was more confusing than helpful. thank you for your answer

Collectives™ on Stack Overflow

list to string in df.to-csv()

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related