使用Pandas处理订单JSON数据并转换为目标格式求助

Hey there! Looks like you need to flatten that nested DataFrame with line items—totally get it, dealing with nested JSON in Pandas can be a bit of a headache. Here's a straightforward solution to transform your data into a flat, easy-to-work-with format:

Step-by-Step Solution #

First, let's build on your existing code to expand the nested line_items into individual rows with their own columns:

import pandas as pd

# Your original code to load the JSON data
desired_width = 920
file_name = "trimmedorders"
file_ext = ".json"
pd.set_option('display.width', desired_width)
df = pd.read_json(file_name + file_ext, orient='columns')

# 1. Explode the line_items list into separate rows
# This creates one row per line item, tied to its parent order ID
df_exploded = df.explode('line_items', ignore_index=True)

# 2. Convert the line_items dictionaries into proper columns
# We'll rename the line item ID to avoid conflict with the order ID
line_items_df = pd.json_normalize(df_exploded['line_items']).rename(columns={'id': 'line_item_id'})

# 3. Combine the order metadata with the flattened line item data
final_df = pd.concat([df_exploded.drop('line_items', axis=1), line_items_df], axis=1)

# Check the result
print(final_df.head())

What Each Step Does #

• Exploding line_items: The explode() method takes every entry in the line_items list and turns it into its own row, keeping the original order ID linked to each line item. ignore_index=True ensures the row numbers are sequential instead of keeping the original order's index.
• Normalizing the dictionaries: pd.json_normalize() unpacks each line item's dictionary (with keys like id, variant_id, etc.) into separate columns. Renaming the line item's id to line_item_id prevents confusion with the order's id column.
• Combining the data: We glue the original order columns (minus the now-unneeded line_items column) to the normalized line item columns, resulting in a fully flat DataFrame.

Example Output #

Your final DataFrame will look something like this (cleaned up for readability):

This structure makes it easy to analyze individual line items, calculate totals per product, or join with other datasets.

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