Creating Nested Dataframes from Nested Dictionaries

Introduction

In this article, we’ll explore how to create a nested dataframe from a nested dictionary using pandas and Python. This is a common requirement in data science and machine learning tasks where datasets can be represented as dictionaries.

Understanding the Problem

We are given a nested dictionary with different classes and their corresponding values. We need to transform this dictionary into a pandas dataframe that follows a specific structure. The resulting dataframe should have three columns: Class, Type, and Counter.

Let’s take a look at the input dictionary:

nested_dict = {
    'Type A':   {'Type A':  10, 'Type B': 20},
    'Type EE':  {'Type B':  40, 'Type C': 50, 'Type A': 60},
    'Type FFF': {'Type ZZ': 70, 'Type FFF': 80, 'Type A': 90, 'Type AA': 1}
}

This dictionary represents a nested structure where each key has two types of values: another dictionary and a numerical value. The task is to flatten this structure into a dataframe that looks like this:

|--------------------------------|
|    class  |      predictions   |
|           |--------------------|
|           |  TYPE    | COUTER  |
|--------------------------------|
|           | Type A   |  10     | 
| Type A    | Type B   |  20     |
|--------------------------------|
|           | Type B   |  40     |
| Type EE   | Type C   |  50     |
|           | Type A   |  60     |
|--------------------------------|
|           | Type zz  |  70     |
| Type FFF  | Type FFF |  80     |
|           | Type A   |  90     |
|           | Type AA  |  1      |
|--------------------------------|

Solution Overview

To solve this problem, we’ll use the following steps:

1. Convert the dictionary into a pandas dataframe.
2. Use the stack() method to flatten the dataframe.
3. Swap the levels of the resulting dataframe using the swaplevel() method.
4. Reset the index using the reset_index() method.
5. Set the new column names using the set_axis() method.

Let’s break down each step:

Step 1: Convert Dictionary to DataFrame

We’ll start by converting the nested dictionary into a pandas dataframe. This is done using the following code:

import pandas as pd

nested_dict = {
    'Type A':   {'Type A':  10, 'Type B': 20},
    'Type EE':  {'Type B':  40, 'Type C': 50, 'Type A': 60},
    'Type FFF': {'Type ZZ': 70, 'Type FFF': 80, 'Type A': 90, 'Type AA': 1}
}

# Convert dictionary to dataframe
df = pd.DataFrame(nested_dict)

Step 2: Flatten the DataFrame

Next, we’ll use the stack() method to flatten the dataframe. This is done using the following code:

# Flatten the dataframe
flattened_df = df.stack()

The stack() method returns a new Series with the index as the original column names and the values as the flattened column names.

Step 3: Swap Levels

Now, we’ll use the swaplevel() method to swap the levels of the resulting dataframe. This is done using the following code:

# Swap levels
swapped_df = flattened_df.swaplevel(0, 1)

The swaplevel() method returns a new Series with the levels swapped.

Step 4: Reset Index

Next, we’ll use the reset_index() method to reset the index of the resulting dataframe. This is done using the following code:

# Reset index
reset_df = swapped_df.reset_index()

The reset_index() method returns a new DataFrame with the index as a column.

Step 5: Set New Column Names

Finally, we’ll use the set_axis() method to set the new column names of the resulting dataframe. This is done using the following code:

# Set new column names
final_df = reset_df.set_axis(['Class', 'Type', 'Counter'], axis=1)

The set_axis() method returns a new DataFrame with the specified column names.

Step 6: Sort Values

We’ll sort the values in each row to match the desired output:

# Sort values
sorted_df = final_df.sort_values(['Class', 'Type'])

This gives us the final output dataframe that matches our requirements.

Example Usage

Here’s an example usage of the above code:

nested_dict = {
    'Type A':   {'Type A':  10, 'Type B': 20},
    'Type EE':  {'Type B':  40, 'Type C': 50, 'Type A': 60},
    'Type FFF': {'Type ZZ': 70, 'Type FFF': 80, 'Type A': 90, 'Type AA': 1}
}

# Create nested dataframe
df = (pd.DataFrame(nested_dict).stack().swaplevel().reset_index()
      .set_axis(['Class', 'Type', 'Counter'], axis=1)
      .sort_values(['Class', 'Type']))

# Print the resulting dataframe
print(df)

Output:

    Class     Type  Counter
0    Type A   Type A     10.0
3    Type A   Type B     20.0
1   Type EE   Type A     60.0
4   Type EE   Type B     40.0
5   Type EE   Type C     50.0
8   Type FFF  Type AA      1.0
9   Type FFF  Type FFF     80.0
2   Type FFF   Type A     90.0
10  Type FFF   Type ZZ     70.0

Conclusion

In this example, we’ve demonstrated how to flatten a nested dictionary into a pandas dataframe using the stack() method and then swap the levels of the resulting dataframe using the swaplevel() method. We’ve also reset the index using the reset_index() method and set new column names using the set_axis() method. Finally, we’ve sorted the values in each row to match the desired output.

By following these steps, you can easily flatten nested dictionaries into dataframes with the desired structure.

Last modified on 2024-09-11