如何在Pandas中高效按DateSent组批量填充多列缺失值（填充值为组最小值的1/3）

Great question! When you're dealing with tens of thousands of numeric columns, writing repetitive code or looping through each column isn't just annoying—it's also inefficient. Pandas has built-in tools to handle this kind of batch operation cleanly and quickly, no loops required.

Here's the streamlined approach: #

1. First, identify your target numeric columns
   Use select_dtypes to automatically pick out all numeric columns, then exclude any non-target columns (like ID, DateAcquired, or DateSent itself) that you don't want to modify:

   # Select all numeric columns
   numeric_cols = df.select_dtypes(include=['float64', 'int64']).columns
   # Remove columns we don't need to process
   cols_to_process = numeric_cols.drop(['ID', 'DateAcquired', 'DateSent'])
   

2. Batch impute missing values with group-based logic
   Instead of processing each column individually, pass the entire list of target columns to groupby.transform. This leverages pandas' vectorized operations, which are way faster than looping for large datasets:

   df[cols_to_process] = df.groupby('DateSent')[cols_to_process].transform(
       lambda x: x.fillna(x.min() / 3)
   )
   

Why this works better: #

• No repetitive code: You don't have to write a line for every column—one line handles all target columns at once.
• Faster performance: Pandas' groupby and transform operations are optimized under the hood, so they'll handle your 100k+ columns much more efficiently than a Python loop would.
• Scalable: This approach works whether you have 4 columns or 100,000—just adjust the cols_to_process list if needed.

Quick validation with your sample data: #

Suppose one of your rows had a missing value in the data column for the 20220110 group. The minimum data value in that group is 6358.434713, so the missing value would be filled with 6358.434713 / 3 ≈ 2119.48—exactly the logic you wanted, applied across all columns in one go.

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