如何使用Pandas或Python将CSV平面文件转换为指定分组嵌套结构的JSON格式

Looks like you're trying to group rows with the same id and name into a single JSON object, where all associated addresses are collected into an array. Your current code adds an address object to each row but doesn't handle grouping—here's how to fix it:

Solution Code #

import pandas as pd

input_file = "your_input.csv"
# Read the CSV as before
df = pd.read_csv(input_file, sep=",", header=0)

# Group by id and name, then aggregate line fields into an address list
grouped_df = df.groupby(['id', 'name']).apply(
    lambda group: group[['line1', 'line2', 'line3']].to_dict('records')
).reset_index(name='address')

# Rename 'id' to match your desired 'source_id' field
grouped_df = grouped_df.rename(columns={'id': 'source_id'})

# Convert to formatted JSON
output_json = grouped_df.to_json(orient='records', indent=2)
print(output_json)

How This Works #

1. Grouping: groupby(['id', 'name']) clusters all rows that share the same ID and name together—this is the key step you were missing earlier.
2. Aggregate Addresses: For each group, to_dict('records') converts the line1, line2, line3 columns into a list of dictionaries (your address array).
3. Clean Up: We reset the index to turn the grouped result into a regular DataFrame, rename id to source_id to match your desired output, then convert to JSON with proper indentation.

Test Result #

Running this code with your input CSV will produce exactly the JSON structure you want:

[ 
  { 
    "source_id": 5, 
    "name": "ABC", 
    "address": [ 
      { "line1": "123", "line2": "456", "line3": 67 }, 
      { "line1": "456", "line2": "456", "line3": 67 } 
    ] 
  } 
]

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