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

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
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!!!
Reply
#2
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()?
Reply
#3
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.
Reply
#4
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 - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to most effectively unpack list of name-value pair dictionaries in a dataframe? zlim 1 615 Nov-07-2023, 10:56 PM
Last Post: zlim
  Comparing results within a list and appending to pandas dataframe Aryagm 1 2,321 Dec-17-2020, 01:08 PM
Last Post: palladium
  [pandas] How to reshape the list Mekala 6 7,260 Jul-26-2020, 12:49 AM
Last Post: Mekala
  how to list/count the number of dictionaries paul18fr 2 1,945 Nov-18-2019, 09:50 PM
Last Post: paul18fr
  Inserting data from python list into a pandas dataframe mahmoud899 0 2,581 Mar-02-2019, 04:07 AM
Last Post: mahmoud899
  missing pandas even if in conda list metalray 2 3,403 May-29-2018, 12:52 PM
Last Post: metalray
  1 I cant read many stocks(list) with pandas-datareader from yahoo Davidii111 1 4,909 Dec-27-2017, 04:47 PM
Last Post: nilamo

Forum Jump:

User Panel Messages

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