Python：为DataFrame新增列并按供应商分组复制指定行值

Alright, let's break this down. You want to create a new column tempTot2 where, for each supplier group, the value from the "TOTAL" row gets assigned to the "OOSLATotal" row. I'll walk you through a couple of straightforward approaches using pandas.

First, let's start with a sample DataFrame to mimic your scenario:

import pandas as pd

# Sample data matching your use case
df = pd.DataFrame({
    "Supplier": ["Alpha", "Alpha", "Bravo", "Bravo", "Charlie", "Charlie"],
    "Category": ["OOSLATotal", "TOTAL", "OOSLATotal", "TOTAL", "OOSLATotal", "TOTAL"],
    "Value": [20, 5, 30, 8, 15, 3]
})

Approach 1: Use a Supplier-to-Total mapping (simple & readable) #

This method first creates a dictionary that maps each supplier to their "TOTAL" value, then uses that to populate tempTot2 only for "OOSLATotal" rows.

# Create a dict: Supplier -> TOTAL value
supplier_total = df[df["Category"] == "TOTAL"].set_index("Supplier")["Value"].to_dict()

# Assign tempTot2 where Category is OOSLATotal; leave others as NaN
df["tempTot2"] = df.apply(
    lambda row: supplier_total[row["Supplier"]] if row["Category"] == "OOSLATotal" else None,
    axis=1
)

Approach 2: Groupby + Apply (intuitive for per-group logic) #

If you prefer handling each supplier group explicitly, using groupby().apply() makes the per-group operation clear:

def populate_tempTot2(group):
    # Get the TOTAL value from the current supplier group
    total_value = group[group["Category"] == "TOTAL"]["Value"].iloc[0]
    # Assign this value to tempTot2 in the OOSLATotal row
    group.loc[group["Category"] == "OOSLATotal", "tempTot2"] = total_value
    return group

# Apply the function to each supplier group
df = df.groupby("Supplier").apply(populate_tempTot2)

Result for either approach #

After running either method, your DataFrame will look like this:

Supplier     Category  Value  tempTot2
0     Alpha  OOSLATotal     20       5.0
1     Alpha        TOTAL      5       NaN
2     Bravo  OOSLATotal     30       8.0
3     Bravo        TOTAL      8       NaN
4   Charlie  OOSLATotal     15       3.0
5   Charlie        TOTAL      3       NaN

Edge Case Handling #

If some suppliers might be missing either the "TOTAL" or "OOSLATotal" row, you can add a check to avoid errors. For example, modifying the populate_tempTot2 function:

def populate_tempTot2_safe(group):
    total_rows = group[group["Category"] == "TOTAL"]
    oosla_rows = group[group["Category"] == "OOSLATotal"]
    if not total_rows.empty and not oosla_rows.empty:
        total_value = total_rows["Value"].iloc[0]
        group.loc[oosla_rows.index, "tempTot2"] = total_value
    return group

df = df.groupby("Supplier").apply(populate_tempTot2_safe)

This way, any supplier missing either row won't cause an error, and tempTot2 will just stay NaN for those groups.

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