Exporting DataFrame Containing Lists To Excel

Question

So I am trying to export a Pandas DataFrame to an .xlsx file using the 'to_excel' method; I've scoured SO and not found any questions that seem to answer this completely. The problem is that individual elements within the dataframe are themselves lists, let me illustrate:

Say we have the following dataframe:

data = [[['a','b','c']],[['a']],[[]],[['a', 'b']],[['a']]]
df = pd.DataFrame(data=data)
df.to_excel('data.xlsx')

the 'to_excel' call results in the following error:

TypeError: Unsupported type <class 'list'> in write()

I've since worked around the issue by doing the following:

for key in df:
    for i in df[key].index:
        df[key].loc[i] = ' ,'.join(df[key].loc[i])
df.to_excel('data.xlsx')

and thus accessing the file in my new program like so:

newDF = pd.read_excel('data.xlsx')
for key in newDF:
    for i in newDF[key].index:
        newDF[key].loc[i] = str(newDF[key].loc[i]).split(',')

but obviously this feels ridiculous and I want to believe there is a better way. Does anyone know of a more efficient means of saving a DataFrame containing lists to an excel file, or at the very least performing the above operations more efficiently?

EdChum · Accepted Answer · 2015-10-26 15:34:50Z

1

Not sure about efficiently but a cleaner method is to call apply and pass ' ,',join as the func to call:

In [75]:
data = [[['a','b','c']],[['a']],[[]],[['a', 'b']],[['a']]]
df = pd.DataFrame(data=data)
df[0].apply(' ,'.join)

Out[75]:
0    a ,b ,c
1          a
2           
3       a ,b
4          a
Name: 0, dtype: object

Besides storing lists as data elements is problematic and should be avoided IMO

answered Oct 26, 2015 at 15:34

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

Collectives™ on Stack Overflow

Exporting DataFrame Containing Lists To Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related