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 lists or dictionaries. But to apply calculations or analysis using packages like pandas, we need to convert this data into DataFrames. In this article we will see how we can convert a given Python list whose elements are nested dictionaries, into a pandas DataFrame.

Basic Approach

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 a new list which was originally created empty. Finally we apply the DataFrame function in the pandas library to create the DataFrame ?

Example

import pandas as pd

# Given nested dictionary
fruits_data = [
    {
        "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 fruits_data:
    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)

The output of the above code is ?

   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_table

We can also apply the pivot_table() function to re-organize the data in a more structured format ?

Example

import pandas as pd

# List of nested dictionary initialization
fruits_data = [
    {
        "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 = []

# Appending rows
for data in fruits_data:
    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)

The output of the above code is ?

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

How It Works

The process involves three key steps:

• Flattening the nested structure: Extract individual records from the nested lists
• Adding context information: Include the parent key (Name) in each record
• DataFrame creation: Convert the flattened list into a pandas DataFrame

Conclusion

Converting nested dictionary lists to DataFrames requires flattening the structure and preserving context information. Use pivot_table() to reorganize the data into a more analytical format when needed.