如何将JSON响应中的列表转为DataFrame并合并至同行数DataFrame？

Got it, let's walk through how to turn that JSON-derived list into a structured DataFrame and merge it with your existing 44928-row DataFrame. I'll use pandas (the standard library for this kind of work in Python) and assume you've already parsed your JSON response into a Python object or DataFrame.

Step 1: Extract the Target List #

First, grab the list from your parsed JSON structure. Based on the path you provided, adjust the code below to match your actual data:

import pandas as pd

# If your parsed JSON is a dictionary (common case)
parsed_json = ...  # Your parsed JSON object here
target_list = parsed_json["Axes"]["Tuples"][1]

# If "Data" is already a pandas DataFrame
# target_list = Data["Axes"].iloc[0]["Tuples"][1]

Step 2: Process the List into Structured Rows #

Each entry in your list has leading ordinal/index values followed by comma-separated categories (like 1 0 2017, Orcamento, Faturamento, Total, Total das Áreas de Negócios). Let's split these into meaningful columns:

processed_rows = []
for entry in target_list:
    # Split the entry into the leading numeric part and category string
    leading_part, categories_str = entry.split(", ", 1)
    # Split leading part into ordinal, index, and year
    ordinal, idx, year = leading_part.split()
    # Split categories into individual values
    categories = categories_str.split(", ")
    # Combine all parts into a single row
    processed_rows.append([ordinal, idx, year] + categories)

# Define column names (customize these to match your data's meaning!)
column_names = ["Ordinal", "Index", "Year", "BudgetType", "Metric", "Aggregation", "BusinessArea"]

# Create the new DataFrame
new_df = pd.DataFrame(processed_rows, columns=column_names)

Truncated entries (like the "D..." in your example) will automatically get NaN for missing columns, which pandas handles gracefully.

Step 3: Merge with Your Existing DataFrame #

Since both DataFrames have exactly 44928 rows, you have two solid options:

Option 1: Merge on a Common Key #

If your existing DataFrame has a column that matches the Ordinal or Index in new_df, use a merge to ensure rows align correctly:

# Replace "matching_column" with your actual shared key (e.g., "Ordinal")
merged_df = pd.merge(existing_df, new_df, on="matching_column", how="inner")

Option 2: Concatenate by Row Order #

If you're 100% sure the rows are in the exact same order in both DataFrames, concatenate them side-by-side directly:

merged_df = pd.concat([existing_df, new_df], axis=1)

Quick Tips #

• Double-check the column names in new_df—rename them to something meaningful for your use case (I used example names like BudgetType and BusinessArea).
• If your list entries don't have the leading ordinal/index/year, skip that splitting step and just split the comma-separated categories directly.

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