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

Answer 1

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'>