Create DataFrame from list of dicts in Pandas series

Question

I have a pandas series with string data structured like this for each "row":

["[{'id': 240, 'name': 'travolta'}, {'id': 378, 'name': 'suleimani'}, {'id': 730, 'name': 'pearson'}, {'id': 1563, 'name': 'googenhaim'}, {'id': 1787, 'name': 'al_munir'}, {'id': 10183, 'name': 'googenhaim'}, {'id': 13072, 'name': 'vodkin'}]"]

When I use a standard solutions to get a DataFrame I got:

> 0 [{'id': 240, 'name': 'travolta'}, {'id': 378, ...   
> 1 [{'id': 240, m'name': 'suleimani'}, {'id': 378,...

How to make an explicit DataFrame with columns named by dict keys?

note that what you show is one-element list, the element being a string — buran
– buran, Commented Nov 2, 2020 at 19:18

Cristian Contrera · Accepted Answer · 2020-11-02 19:21:52Z

2

You can use json module to load that structure:

import json

data = ["[{'id': 240, 'name': 'travolta'}, {'id': 378, 'name': 'suleimani'}, {'id': 730, 'name': 'pearson'}, {'id': 1563, 'name': 'googenhaim'}, {'id': 1787, 'name': 'al_munir'}, {'id': 10183, 'name': 'googenhaim'}, {'id': 13072, 'name': 'vodkin'}]"]

data = ''.join(data).replace('\'', '"')
data = json.loads(data)
df = pd.DataFrame(data)

#print result df
#    id name
0   240 travolta
1   378 suleimani
2   730 pearson
3   1563    googenhaim
4   1787    al_munir

answered Nov 2, 2020 at 19:21

Cristian Contrera

7233 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

buran · Accepted Answer · 2020-11-02 19:22:36Z

2

import pandas
import ast
spam = ["[{'id': 240, 'name': 'travolta'}, {'id': 378, 'name': 'suleimani'}, {'id': 730, 'name': 'pearson'}, {'id': 1563, 'name': 'googenhaim'}, {'id': 1787, 'name': 'al_munir'}, {'id': 10183, 'name': 'googenhaim'}, {'id': 13072, 'name': 'vodkin'}]"]
eggs = ast.literal_eval(spam[0])

df = pandas.DataFrame(eggs)
print(df)

output

      id        name
0    240    travolta
1    378   suleimani
2    730     pearson
3   1563  googenhaim
4   1787    al_munir
5  10183  googenhaim
6  13072      vodkin

as mentioned in my comment, you don't have list of dicts, but single-element list, in which the element is string literal representing list of dicts.

answered Nov 2, 2020 at 19:22

buran

14.4k13 gold badges45 silver badges76 bronze badges

Comments

Dani Mesejo · Accepted Answer · 2020-11-02 19:20:56Z

1

For the input of your example, you could use ast.literal_eval, followed by a flattening of the main list, lst, as follows:

import pandas as pd
import ast

lst = ["[{'id': 240, 'name': 'travolta'}, {'id': 378, 'name': 'suleimani'}, {'id': 730, 'name': 'pearson'}, {'id': 1563, 'name': 'googenhaim'}, {'id': 1787, 'name': 'al_munir'}, {'id': 10183, 'name': 'googenhaim'}, {'id': 13072, 'name': 'vodkin'}]"]

rows = [d for l in [ast.literal_eval(e) for e in lst] for d in l]

frame = pd.DataFrame(rows)
print(frame)

Output

      id        name
0    240    travolta
1    378   suleimani
2    730     pearson
3   1563  googenhaim
4   1787    al_munir
5  10183  googenhaim
6  13072      vodkin

answered Nov 2, 2020 at 19:20

Dani Mesejo

62.2k6 gold badges57 silver badges86 bronze badges

Collectives™ on Stack Overflow

Create DataFrame from list of dicts in Pandas series

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related