How to read a list from an excel cell

Question

I put a list into an excel cell, and when I read it with pandas, it did not return me a list, it returned me a string, is there anyway I can get a list in return instead?

eg. in the cell: ['a', 'b', 'c'] output from pandas: '['a', 'b', 'c']'

here is my code:

df = pd.read_excel('example.xlsx', index_col=None, header=None)

print(df.iloc[5, 3])
print(type(df.iloc[5, 3]))
## and the code would return the type of df.iloc[5, 3] is equal to a list

score 3 · Accepted Answer · answered Oct 21 '19 at 05:33

In excel are lists converted to string repr of lists.

df = pd.DataFrame({
        'A':list('abcdef'),
         'B':[4,5,4,5,5,4],
         'C':[7,8,9,4,2,3],
         'D':[1,3,5,7,1,['a', 'b', 'c']],
         'E':[5,3,6,9,2,4],
         'F':list('aaabbb')
})

print(df.iloc[5, 3])
['a', 'b', 'c']

df.to_excel('example.xlsx', header=None, index=False)

df = pd.read_excel('example.xlsx', index_col=None, header=None)

print(df.iloc[5, 3])
['a', 'b', 'c']

print(type(df.iloc[5, 3]))
<class 'str'>

So is necessary convert it to lists by ast.literal_eval

import ast

print(ast.literal_eval(df.iloc[5, 3]))
['a', 'b', 'c']

print(type(ast.literal_eval(df.iloc[5, 3])))
<class 'list'>

Or eval, but it is bad practise, so not recommended:

print(eval(df.iloc[5, 3]))
['a', 'b', 'c']

print(type(eval(df.iloc[5, 3])))
<class 'list'>

Thank you for your solution! but would you please explain to me the difference between ```ast.literal_eval``` and normal ```eval```? — Andrew Anjun Hou, Oct 21 '19 at 18:04
@Andrew Anjun Hou check [this post](https://stackoverflow.com/q/15197673/2901002). — jezrael, Oct 21 '19 at 19:15

score 0 · Answer 2 · answered Oct 21 '19 at 05:33

0

You can use eval(). Here is an example :

a = "['a', 'b']"
print(eval(a))

This prints:

['a', 'b']

answered Oct 21 '19 at 05:33

Kostas Charitidis

2,991
1
12
23

How to read a list from an excel cell

2 Answers2