List and Dictionaries with Pandas

[Balinor]

Hi...

I'm trying to use pandas to read data from an excel file:

1	df = pd.read_excel('C:testfile.xls', sheet)

My excel file has a list (of strings) in one of the cells (header row in bold)...
test input1 input2
TEST1 HELLO ['VAL1', 'VAL2']
TEST2 HELLO2 ['VAL3', 'VAL4', 'VAL5', 'VAL6']

My problem is that when I read in input2 I seem to get a string: ['VAl1', 'VAL2'], instead of a list containing VAL1 and VAL2.

Is there a way to use panda to read in the value as a python list? I will need to read in a list of dictionaries later on, so it will get even more complex...

test expva1 expva2
TEST1 BYE [{indent: 'VAL1', indent2: 'VAL2', indent3: 'VAL3}, {indent: 'VAL4', indent2: 'VAL5', indent3: 'VAL6}, {indent: 'VAL7', indent2: 'VAL8', indent3: 'VAL9}]
TEST2 BYE2 [{indent: 'VALA', indent2: 'VALB', indent3: 'VALC}, {indent: 'VALD', indent2: 'VALE', indent3: 'VALF}]

I'd like to be able to read this in as a python list of dictionaries using panda. Is this possible? I cannot find examples anywhere, so I'm wondering if perhaps panda is not the right module to use here...

Any help is appreciated, including other non-panda solutions.

Thanks!!!

[Vysero]

I am a bit confused about the format of your excel file. Could you just take a screen shot of it and post the picture or show the output of pd.head()?

[Balinor]

It turns out that a simple call using "eval" fixed my issue.

i.e. df_list = eval(page_headers['df_list'])

This converts the list or list of dictionaries into their native types of string, None, etc...

Hope this helps someone else.

[ichabod801]

Just be careful with eval. If you're making the spreadsheets, that's fine. But if you're getting spreadsheets from other people, and they know you're using eval, they could put malicious code in the spreadsheet.