如何用Pandas惯用方式展开含JSON数组的列生成多行？

If you need to split each loan's repayment records (stored as JSON arrays in the Payments column) into individual rows while retaining the loan's core details, here's a step-by-step solution using pandas:

Step 1: Understand the Input/Output #

Sample Input (Your Out[3]) #

Expected Output (Your Out[5]) #

Step 2: Full Code Solution #

import pandas as pd
import json

# Replace this with your actual Out[3] DataFrame
df = pd.DataFrame({
    "Loan ID": [101, 102, 103],
    "Start Date": ["2023-01-01", "2023-02-01", "2023-03-01"],
    "End Date": ["2023-12-31", "2024-01-31", "2024-02-29"],
    "Amount": [10000, 5000, 15000],
    "Payments": [
        '[{"timestamp": "2023-02-01", "amount": 800}, {"timestamp": "2023-03-01", "amount": 800}]',
        "[]",
        '[{"timestamp": "2023-04-01", "amount": 1200}]'
    ]
})

# 1. Parse JSON strings into actual lists of dictionaries
df["Payments"] = df["Payments"].apply(json.loads)

# 2. Explode the Payments column to create one row per repayment
exploded_df = df.explode("Payments", ignore_index=True)

# 3. Normalize repayment details into separate columns
repayment_details = pd.json_normalize(exploded_df["Payments"]).rename(
    columns={"timestamp": "repayment_timestamp", "amount": "repayment_amount"}
)

# 4. Combine loan details with repayment details
final_df = pd.concat([exploded_df.drop("Payments", axis=1), repayment_details], axis=1)

# This is your Out[5]
print(final_df)

Step 3: Breakdown of Each Step #

• Parse JSON: The Payments column starts as a string, so we use json.loads() to convert it into a list of Python dictionaries that pandas can manipulate.
• Explode: The explode() method takes each element in the Payments list and creates a new row for it, repeating the loan's fixed details (Loan ID, dates, amount). For loans with no repayments, this results in a row with NaN for the repayment data.
• Normalize: pd.json_normalize() converts the repayment dictionaries into separate columns (e.g., repayment_timestamp, repayment_amount) for better readability and analysis.
• Concatenate: We merge the original loan columns (minus the now-unused Payments column) with the normalized repayment columns to get the final structure.

Optional: Drop Rows With No Repayments #

If you don't want to keep loans that have no repayment records, add this line after creating final_df:

final_df = final_df.dropna(subset=["repayment_timestamp"])

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