使用Pandas将关系型DataFrame合并为嵌套JSON及实现嵌套列合并

Got it, let's tackle this step by step. You want to embed the full contents of DF2 into a single column of DF1, where DF2's XID (foreign key) matches DF1's unique key cid. We'll use pandas to create both a nested DataFrame and a nested JSON output.

Step 1: Read the CSV Files #

First, we'll load both CSVs into pandas DataFrames. Since your files use semicolons as separators, we need to specify sep=';':

import pandas as pd

# Load the CSV data
df1 = pd.read_csv('df1.csv', sep=';')
df2 = pd.read_csv('df2.csv', sep=';')

Step 2: Nest DF2 by Foreign Key #

Next, we group DF2 by XID (the column that links to DF1's cid) and convert each group into a list of dictionaries. This creates a nested structure where each XID maps to all its associated DF2 records:

# Group DF2 by XID and convert each group to a list of row dictionaries
nested_tickets = df2.groupby('XID').apply(lambda group: group.to_dict('records')).reset_index(name='tickets')

Step 3: Merge Nested DF2 into DF1 #

Now we merge this nested structure back into DF1, matching cid (from DF1) to XID (from our grouped DF2). We use a left join to keep all rows from DF1, even if there's no matching data in DF2:

# Merge the nested tickets into DF1
merged_df = df1.merge(nested_tickets, left_on='cid', right_on='XID', how='left')

# Clean up: Drop the duplicate XID column and replace NaN with empty lists
merged_df = merged_df.drop(columns='XID')
merged_df['tickets'] = merged_df['tickets'].fillna([])

Example Output of the Nested DataFrame #

Your merged DataFrame will look like this (using your sample data):

Step 4: Convert to Nested JSON #

If you need the output as nested JSON, just use pandas' to_json method with orient='records' to preserve the structure:

# Generate nested JSON
nested_json = merged_df.to_json(orient='records', indent=2)
print(nested_json)

Example JSON Output #

[
  {
    "cid": 1,
    "name": "Mueller",
    "surname": "Hans",
    "address": 42553,
    "tickets": [
      {
        "OID": 1,
        "ticketid": 9,
        "XID": 1,
        "message": "fgsgfs"
      }
    ]
  },
  {
    "cid": 2,
    "name": "Meier",
    "surname": "Peter",
    "address": 42873,
    "tickets": [
      {
        "OID": 2,
        "ticketid": 8,
        "XID": 2,
        "message": "gdfg"
      }
    ]
  },
  {
    "cid": 3,
    "name": "Schmidt",
    "surname": "Micha",
    "address": 42567,
    "tickets": [
      {
        "OID": 3,
        "ticketid": 7,
        "XID": 3,
        "message": "gfsfgfg"
      }
    ]
  },
  {
    "cid": 4,
    "name": "Pauli",
    "surname": "Ulli",
    "address": 98790,
    "tickets": [
      {
        "OID": 4,
        "ticketid": 6,
        "XID": 4,
        "message": "fgsfdgfd"
      }
    ]
  },
  {
    "cid": 5,
    "name": "Dick",
    "surname": "Franz",
    "address": 45632,
    "tickets": []
  }
]

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