Python Pandas - Convert Nested Dictionary to Multiindex Dataframe

Converting a nested dictionary to a Pandas MultiIndex DataFrame involves restructuring the dictionary keys as hierarchical column indices. This creates a DataFrame with multi-level column headers that represent the nested structure.

Creating a Nested Dictionary

First, let's create a nested dictionary with sports data −

import pandas as pd

# Create nested dictionary
nested_dict = {
    'Cricket': {
        'Boards': ['BCCI', 'CA', 'ECB'],
        'Country': ['India', 'Australia', 'England']
    },
    'Football': {
        'Boards': ['TFA', 'TCSA', 'GFA'],
        'Country': ['England', 'Canada', 'Germany']
    }
}

print("Nested Dictionary:")
print(nested_dict)

Nested Dictionary:
{'Cricket': {'Boards': ['BCCI', 'CA', 'ECB'], 'Country': ['India', 'Australia', 'England']}, 'Football': {'Boards': ['TFA', 'TCSA', 'GFA'], 'Country': ['England', 'Canada', 'Germany']}}

Converting to MultiIndex DataFrame

Create a new dictionary with tuple keys for the MultiIndex columns −

import pandas as pd

nested_dict = {
    'Cricket': {
        'Boards': ['BCCI', 'CA', 'ECB'],
        'Country': ['India', 'Australia', 'England']
    },
    'Football': {
        'Boards': ['TFA', 'TCSA', 'GFA'],
        'Country': ['England', 'Canada', 'Germany']
    }
}

# Convert nested structure to tuple keys
multiindex_dict = {}
for outer_key, inner_dict in nested_dict.items():
    for inner_key, values in inner_dict.items():
        multiindex_dict[(outer_key, inner_key)] = values

# Create MultiIndex DataFrame
df = pd.DataFrame(multiindex_dict)
print("MultiIndex DataFrame:")
print(df)

MultiIndex DataFrame:
   Cricket           Football         
    Boards  Country    Boards Country
0     BCCI    India       TFA England
1       CA Australia     TCSA  Canada
2      ECB  England       GFA Germany

Alternative Method Using pd.concat()

You can also use pd.concat() to create the MultiIndex DataFrame directly −

import pandas as pd

nested_dict = {
    'Cricket': {
        'Boards': ['BCCI', 'CA', 'ECB'],
        'Country': ['India', 'Australia', 'England']
    },
    'Football': {
        'Boards': ['TFA', 'TCSA', 'GFA'],
        'Country': ['England', 'Canada', 'Germany']
    }
}

# Create DataFrames for each sport and concatenate
dataframes = []
for sport, data in nested_dict.items():
    sport_df = pd.DataFrame(data)
    sport_df.columns = pd.MultiIndex.from_tuples([(sport, col) for col in sport_df.columns])
    dataframes.append(sport_df)

df_concat = pd.concat(dataframes, axis=1)
print("MultiIndex DataFrame using concat:")
print(df_concat)

MultiIndex DataFrame using concat:
   Cricket           Football         
    Boards  Country    Boards Country
0     BCCI    India       TFA England
1       CA Australia     TCSA  Canada
2      ECB  England       GFA Germany

Comparison

Conclusion

Converting nested dictionaries to MultiIndex DataFrames creates hierarchical column structures. Use dictionary comprehension with tuple keys for simple cases, or pd.concat() for more complex transformations.