如何按规则填充Pandas DataFrame中Sub-ID=0的年份列空值（勿用groupby）

Approach #

1. Calculate the sum of year columns (2018, 2019, 2020) for each C-ID using rows where Sub-ID is 1, 2, or 3.
2. Merge these sums back into the original DataFrame to align with their corresponding C-ID.
3. For rows where Sub-ID=0 and the year column value is null, replace the null with the precomputed sum for that C-ID and year.
4. Clean up the temporary sum columns to restore the original DataFrame structure.

Code Implementation #

import pandas as pd
import numpy as np

# Sample input DataFrame
data = {
    'C-ID': [1,1,1,1,2,2,2,2,3,3,3,3],
    'Sub-ID': [0,1,2,3,0,1,2,3,0,1,2,3],
    '2018': [10,5,3,2,np.nan,7,1,2,np.nan,np.nan,np.nan,np.nan],
    '2019': [np.nan,2,4,1,np.nan,8,2,0,5,np.nan,np.nan,np.nan],
    '2020': [30,10,15,5,np.nan,9,3,1,np.nan,np.nan,np.nan,np.nan]
}
df = pd.DataFrame(data)

# Step 1: Compute sum of year columns for Sub-ID 1-3 per C-ID
sum_df = df[df['Sub-ID'].isin([1,2,3])].groupby('C-ID')[['2018','2019','2020']].sum().reset_index()
sum_df.columns = ['C-ID', '2018_sum', '2019_sum', '2020_sum']

# Step 2: Merge sums with original DataFrame
merged_df = df.merge(sum_df, on='C-ID', how='left')

# Step3: Fill null values in Sub-ID=0 rows with computed sums
for year in ['2018','2019','2020']:
    merged_df.loc[(merged_df['Sub-ID'] == 0) & merged_df[year].isna(), year] = merged_df[f'{year}_sum']

# Step4: Remove temporary sum columns
result_df = merged_df.drop(['2018_sum','2019_sum','2020_sum'], axis=1)

# Show result
print(result_df)

Example Output #

Key Notes #

• Existing non-null values in Sub-ID=0 rows are preserved, as we only replace nulls.
• C-IDs with all null values (like C-ID 3) remain unchanged since there's no valid sum to fill with.
• The groupby operation is only used to calculate sums for Sub-ID 1-3, not to overwrite any existing data.

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