0

I have a structure like the following in .xlsx format (written with pandas .to_xlsx() function)

|---------------------|------------------|
|      A              |     B            |
|---------------------|------------------|
| [[2 3] [3 4] [4 5]] |         34       |
|---------------------|------------------|

enter image description here

When I read this using df = pd.read_xlsx(), the df['A'] is a string - '[[2 3]\n[3 4]\n[4 5]]'

Is there a way to read it as a list of list of int or float?

user2880062
  • 27
  • 6
  • I think [this question](https://stackoverflow.com/questions/23111990/pandas-dataframe-stored-list-as-string-how-to-convert-back-to-list/23112008) and it's answers might be helpful to you. – SimonT Jul 29 '20 at 12:29

1 Answers1

2

You can try to convert string into list:

cell = eval('[[2 3]\n[3 4]\n[4 5]]'.replace('\n',' ').replace(' ',','))

result:

[[2, 3], [3, 4], [4, 5]]

Preferred method is to use literal_eval from ast:

from ast import literal_eval
cell = literal_eval('[[2 3]\n[3 4]\n[4 5]]'.replace('\n',' ').replace(' ',','))

Result is the same as above, but method is better than eval, because it safely evaluates strings containing values from untrusted sources without the need to parse the values by hand.

ipj
  • 3,488
  • 1
  • 14
  • 18
  • 1
    `ast.literal_eval` is safer: `ast.literal_eval('[[2 3]\n[3 4]\n[4 5]]'.replace('\n',' ').replace(' ',','))` – anky Jul 29 '20 at 12:32