Python - Convert list of nested dictionary into Pandas Dataframe

Many times python will receive data from various sources which can be in different formats like csv, JSON etc which can be converted to python list or dictionaries etc. But to apply the calculations or analysis using packages like pandas, we need to convert this data into a dataframes. In this article we will see how we can convert a given python list whose elements are a nested dictionary, into a pandas Datframe.

We first take the list of nested dictionary and extract the rows of data from it. Then we create another for loop to append the rows into the new list which was originally created empty. Finally we apply the DataFrames function in the pandas library to create the Data Frame.

Example

import pandas as pd

# Given nested dictionary
list = [
   {
      "Fruit": [{"Price": 15.2, "Quality": "A"},
         {"Price": 19, "Quality": "B"},
         {"Price": 17.8, "Quality": "C"},
      ],
      "Name": "Orange"
   },
   {
      "Fruit": [{"Price": 23.2, "Quality": "A"},
         {"Price": 28, "Quality": "B"}
      ],
      "Name": "Grapes"
   }
]

rows = []

# Getting rows
for data in list:
   data_row = data['Fruit']
   n = data['Name']

   for row in data_row:
      row['Name'] = n
      rows.append(row)

# Convert to data frame
df = pd.DataFrame(rows)
print(df)

Running the above code gives us the following result −

Output

Price Quality   Name
0 15.2    A Orange
1 19.0    B Orange
2 17.8    C Orange
3 23.2    A Grapes
4 28.0    B Grapes

Applying pivot

We can also apply the pivot_table function to re-organize the data the way we want.

Example

import pandas as pd

# List of nested dictionary initialization
list = [
   {
      "Fruit": [{"Price": 15.2, "Quality": "A"},
         {"Price": 19, "Quality": "B"},
         {"Price": 17.8, "Quality": "C"},
      ],
      "Name": "Orange"
   },
   {
      "Fruit": [{"Price": 23.2, "Quality": "A"},
         {"Price": 28, "Quality": "B"}
      ],
      "Name": "Grapes"
   }
]

#print(list)
rows = []

# appending rows
for data in list:
   data_row = data['Fruit']
   n = data['Name']

   for row in data_row:
      row['Name'] = n
      rows.append(row)

   # using data frame
df = pd.DataFrame(rows)

df = df.pivot_table(index='Name', columns=['Quality'],
               values=['Price']).reset_index()
print(df)

Running the above code gives us the following result −

Output

       Name Price         
Quality          A    B    C
0      Grapes 23.2 28.0 NaN
1      Orange 15.2 19.0 17.8

Pradeep Elance

Published on 28-Dec-2020 10:39:57

• Related Questions & Answers
• Python Pandas - Convert Nested Dictionary to Multiindex Dataframe
• Python - Convert flattened dictionary into nested dictionary
• Python Convert nested dictionary into flattened dictionary?
• Python - Convert given list into nested list
• How to convert a DataFrame into a dictionary in Pandas?
• Convert a nested list into a flat list in Python
• Python program to convert a list of tuples into Dictionary
• How to convert dictionary into list of JavaScript objects?
• Python - How to group DataFrame rows into list in Pandas?
• Python - Convert a set into dictionary
• Convert list into list of lists in Python
• Convert list of tuples into list in Python
• Convert dictionary object into string in Python
• Convert dictionary to list of tuples in Python
• Python - Convert List to custom overlapping nested list