Python Forum
List and Dictionaries with Pandas
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
List and Dictionaries with Pandas

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

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

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()?
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.
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.
Craig "Ichabod" O'Brien -
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures

Possibly Related Threads…
Thread Author Replies Views Last Post
  Comparing results within a list and appending to pandas dataframe Aryagm 1 531 Dec-17-2020, 01:08 PM
Last Post: palladium
  [pandas] How to reshape the list Mekala 6 1,410 Jul-26-2020, 12:49 AM
Last Post: Mekala
  pandas.read_sas with chunksize: IndexError list index out of range axelle 0 977 Jan-28-2020, 09:30 AM
Last Post: axelle
  how to list/count the number of dictionaries paul18fr 2 776 Nov-18-2019, 09:50 PM
Last Post: paul18fr
  Inserting data from python list into a pandas dataframe mahmoud899 0 1,185 Mar-02-2019, 04:07 AM
Last Post: mahmoud899
  Pandas - Write to Exisitng Excel File - Sorted List dj99 4 7,342 Jul-29-2018, 07:56 AM
Last Post: dj99
  missing pandas even if in conda list metalray 2 2,056 May-29-2018, 12:52 PM
Last Post: metalray
  1 I cant read many stocks(list) with pandas-datareader from yahoo Davidii111 1 3,011 Dec-27-2017, 04:47 PM
Last Post: nilamo

Forum Jump:

User Panel Messages

Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020