Convert list of dictionaries into dataframe python

Question

I have a list that i would like to convert to dataframe. I have around 30000 lists in a variable called data. how do i convert this into a dataframe with columns properties, product_id,description,ustomer_id and country. I want the element properties to be converted to a dataframe

data[0]
Out[16]: 
 {'event': 'Product',
     'properties': {'invoice_no': '44',
      'product_id': '67',
      'description': 'cloth',
      'customer_id': 55,
      'country': 'US'}}


data[1]
 Out[17]: 
    {'event': 'Product',
     'properties': {'invoice_no': '55',
      'product_id': '66',
      'description': 'shoe',
      'customer_id': 23,
      'country': 'China'}}

Tried this ,

new = pd.DataFrame.from_dict(data)

but it gave only two columns such as 'event' and 'properties'. I want properties to form a dataframe

juanpa.arrivillaga · Accepted Answer · 2018-07-07 03:15:23Z

2

Using your small example set:

>>> from pprint import pprint
>>> pprint(data)
[{'event': 'Product',
  'properties': {'country': 'US',
                 'customer_id': 55,
                 'description': 'cloth',
                 'invoice_no': '44',
                 'product_id': '67'}},
 {'event': 'Product',
  'properties': {'country': 'China',
                 'customer_id': 23,
                 'description': 'shoe',
                 'invoice_no': '55',
                 'product_id': '66'}}]

You can simply use a generator expression to munge your dict into an appropriate form:

>>> pd.DataFrame(d['properties'] for d in data)
  country  customer_id description invoice_no product_id
0      US           55       cloth         44         67
1   China           23        shoe         55         66

answered Jul 7, 2018 at 3:15

juanpa.arrivillaga

97.6k14 gold badges141 silver badges190 bronze badges

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

Comments

ababuji · Accepted Answer · 2018-07-07 03:21:19Z

0

You can also do:

from pandas.io.json import json_normalize
import pandas as pd
resultDf = pd.DataFrame()

for dictionary in data:
    for key, value in dictionary.items():

        if key == 'properties':
            df = json_normalize(value)
            resultDf = resultDf.append(df)

print(resultDf) gives:

  country  customer_id description invoice_no product_id
0      US           55       cloth         44         67
1   China           23        shoe         55         66

answered Jul 7, 2018 at 3:21

ababuji

1,7412 gold badges17 silver badges41 bronze badges

Collectives™ on Stack Overflow

Convert list of dictionaries into dataframe python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related