如何用Pandas将嵌套JSON转换为扁平化表格？

Got it, let's walk through converting your nested JSON structure into a clean Pandas DataFrame. Since your data has multiple levels of nested arrays (like f inside c, and m inside f), we'll use a combination of pd.json_normalize() and DataFrame.explode() to flatten everything properly.

Step 1: Import Pandas and Define Your Data #

First, let's set up the basics:

import pandas as pd

# Your original JSON data
data = {
    "a": "a_1",
    "b": "b_1",
    "c": [
        {"d": "d_1", "e": "e_1", "f": [], "g": "g_1", "h": "h_1"},
        {"d": "d_2", "e": "e_2", "f": [], "g": "g_2", "h": "h_2"},
        {"d": "d_3", "e": "e_3", "f": [
            {"i": "i_1", "j": "j_1", "k": "k_1", "l": "l_1", "m": []},
            {"i": "i_2", "j": "j_2", "k": "k_2", "l": "l_2", "m": [
                {"n": "n_1", "o": "o_1", "p": "p_1", "q": "q_1"}
            ]}
        ], "g": "g_3", "h": "h_3"}
    ]
}

Step 2: Flatten the Top-Level c Array #

We'll start by normalizing the c array, keeping the top-level a and b values as metadata for each entry in c:

# Normalize the 'c' array, attach 'a' and 'b' to each row
df_c = pd.json_normalize(data, record_path='c', meta=['a', 'b'])

This gives us a DataFrame where each row corresponds to an entry in c, with columns d, e, f, g, h, a, b. The f column still contains arrays (empty or with nested objects).

Step 3: Expand the f Array #

Next, we'll explode the f array to turn each element in the array into its own row, while preserving all other columns:

# Explode the 'f' array; empty arrays become NaN
df_f = df_c.explode('f', ignore_index=True)

Now, rows where f was empty will have NaN in the f column, and rows with multiple f entries will be split into separate rows.

Step 4: Normalize the Nested f Objects #

We'll normalize the f column to flatten its nested fields (like i, j, k, etc.), and add a prefix (f.) to keep track of which fields come from f:

# Normalize the 'f' objects and add a prefix
df_f_normalized = pd.json_normalize(df_f['f']).add_prefix('f.')

# Combine the original DataFrame (without 'f') with the normalized 'f' data
df_combined = pd.concat([df_f.drop('f', axis=1), df_f_normalized], axis=1)

Step 5: Expand and Normalize the m Array #

Finally, we repeat the process for the f.m array (nested inside some f objects):

# Explode the 'f.m' array
df_m = df_combined.explode('f.m', ignore_index=True)

# Normalize the 'm' objects and add a prefix
df_m_normalized = pd.json_normalize(df_m['f.m']).add_prefix('f.m.')

# Combine to get the final flattened DataFrame
final_df = pd.concat([df_m.drop('f.m', axis=1), df_m_normalized], axis=1)

Final Result #

Your final DataFrame will look like this (flattened with all nested fields exposed):

This approach ensures all nested levels are flattened, and empty arrays are handled gracefully with NaN values.

内容的提问来源于stack exchange，提问作者Pankaj Singhal