如何在DataFrame中按共同键（ID）聚合关联值至指定位置

Got it, let's tackle this problem. You want to aggregate the value entries from Slave rows that share the same ID into their matching Master row (where Name starts with "Master") in your DataFrame. Here's a straightforward, flexible solution:

Step 1: Prepare the Data #

First, let's confirm we're working with your original dataset:

import pandas as pd

df1 = pd.DataFrame({
    "ID": ["x13", "x13", "", "x14", "", "x13"],
    "Name": ["Master1", "Slave1", "Master2", "Master3", "Master4", "Slave2"],
    "value": ["", "5", "7", "8", "", "1"]
})

Step 2: Aggregate Slave Values by ID #

We'll first group all Slave rows by their ID and concatenate their non-empty value entries into a single string:

# Filter Slave rows and aggregate their values per ID
slave_groups = df1[df1["Name"].str.startswith("Slave")].groupby("ID")["value"].agg(lambda x: ",".join(x[x != ""]))

This creates a Series where each key is an ID, and the value is a comma-separated list of all relevant Slave values for that ID.

Step 3: Update Master Rows with Aggregated Values #

Next, we'll iterate over the Master rows and fill in their empty value field with the aggregated Slave values if there's a matching ID in our grouped data:

# Update Master rows with aggregated Slave values
for idx, row in df1[df1["Name"].str.startswith("Master")].iterrows():
    if row["ID"] in slave_groups.index and row["value"] == "":
        df1.at[idx, "value"] = slave_groups[row["ID"]]

Final Result #

After running the code, your DataFrame will look exactly like the desired output:

If you'd rather remove the Slave rows entirely after aggregating, just add this line at the end:

df1 = df1[~df1["Name"].str.startswith("Slave")]

This will leave you with only the Master rows, fully populated with aggregated values where applicable.

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