如何利用Pandas根据多列匹配结果条件填充DataFrame的Brand列缺失值

Got it, let's tackle this problem step by step. You need to fill missing brand values by matching the combination of device_id, head, and supplement—using existing brand values from the same group, or 'Other' if there's no existing value for that combination.

First, let's recap your initial DataFrame for clarity:

import pandas as pd
import numpy as np
df_start = pd.DataFrame({
    'device_id':[1,1,1,1,2,2,3,3,3,3,4,4,4,4], 
    'head':['a','a','b','b','a','b','a','b','b','b','a','b','c','d'], 
    'supplement':['Salt','Salt','Pepper','Pepper','Pepper','Pepper','Salt','Pepper','Salt','Pepper','Pepper','Salt','Pepper','Salt'], 
    'brand':['white',np.nan,np.nan,'white','white','black',np.nan,np.nan,'white','black',np.nan,'white','black',np.nan]
})

Approach #

The core idea is to group rows by the three matching columns, then fill missing values within each group using the existing non-null brand value (if any). For groups with no existing brand values, we'll fill those with 'Other'.

Step-by-Step Code #

Here's the code that will get you to your target DataFrame:

# Step 1: Fill missing brand values using existing values in the same (device_id, head, supplement) group
df_start['brand'] = df_start.groupby(['device_id', 'head', 'supplement'])['brand'].transform(
    lambda group: group.fillna(group.dropna().iloc[0] if not group.dropna().empty else np.nan)
)

# Step 2: Replace any remaining NaNs (groups with no existing brand values) with 'Other'
df_start['brand'] = df_start['brand'].fillna('Other')

Explanation #

1. Grouping and Transform:

   • groupby(['device_id', 'head', 'supplement']) clusters rows where all three columns match exactly.
   • The transform function applies logic to each group:
     • If the group has non-null brand values, we fill all NaNs in the group with the first non-null value (your example assumes each valid group has a single consistent brand value).
     • If the group has no non-null values, we leave it as NaN for now.

2. Final Fill with 'Other':

   • Any remaining NaNs after the first step belong to groups where there were no existing brand values. We replace these with 'Other' using fillna('Other').

Verify the Result #

If you print df_start now, it will match your target df_end exactly:

print(df_start)
# Output matches your df_end:
#    device_id head supplement   brand
# 0          1    a        Salt   white
# 1          1    a        Salt   white
# 2          1    b      Pepper   white
# 3          1    b      Pepper   white
# 4          2    a      Pepper   white
# 5          2    b      Pepper   black
# 6          3    a        Salt   Other
# 7          3    b      Pepper   black
# 8          3    b        Salt   white
# 9          3    b      Pepper   black
# 10         4    a      Pepper   Other
# 11         4    b        Salt   white
# 12         4    c      Pepper   black
# 13         4    d        Salt   Other

This approach is efficient and leverages Pandas' built-in grouping functions to handle the matching logic cleanly.

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