如何用Python遍历DataFrame时间区间并批量计算补全缺失值

首先修正你代码里的小问题：你定义数据时列名是ReportingDate，设置索引时写的Reporting Date多了空格，会执行报错，先调整这个细节。 #

步骤1：基础数据预处理 #

先把空格形式的缺失值转成标准NaN，同时把日期列转成pandas可识别的日期格式：

import pandas as pd
import numpy as np

# 你的原始数据
data = {'ReportingDate':['2013/5/31','2013/5/31','2013/5/31','2013/5/31','2013/5/31','2013/5/31',
                         '2013/6/28','2013/6/28',
                         '2013/6/28','2013/6/28','2013/6/28'],
        'MarketCap':[' ',0.35,0.7,0.875,0.7,0.35,' ',1,1.5,0.75,1.25],
       'AUM':[3.5,3.5,3.5,3.5,3.5,3.5,5,5,5,5,5],
       'weight':[' ',0.1,0.2,0.25,0.2,0.1,' ',0.2,0.3,0.15,0.25]}

df = pd.DataFrame(data)
# 替换空格为标准缺失值
df = df.replace(' ', np.nan)
# 转换日期格式后设置索引
df['ReportingDate'] = pd.to_datetime(df['ReportingDate'])
df.set_index('ReportingDate', inplace=True)

步骤2：批量填充所有月份缺失值 #

推荐用groupby分组处理，不需要手动写循环遍历日期，执行效率更高：

def fill_month_missing(group):
    # 取当月统一的AUM值
    monthly_aum = group['AUM'].iloc[0]
    # 计算第一行缺失的weight：1减去其余行weight总和
    missing_weight = 1 - group.iloc[1:]['weight'].sum()
    # 计算对应MarketCap
    missing_marketcap = missing_weight * monthly_aum
    # 填充第一行两个缺失值
    group.iloc[0, group.columns.get_loc('weight')] = missing_weight
    group.iloc[0, group.columns.get_loc('MarketCap')] = missing_marketcap
    return group

# 按月份分组执行填充
df = df.groupby(pd.Grouper(freq='M')).apply(fill_month_missing)

如果你习惯用显式循环写法，也可以用如下代码实现：

# 提取所有不重复的月份
all_months = df.index.to_period('M').unique()

for month in all_months:
    # 筛选当月所有数据
    month_data = df[df.index.to_period('M') == month]
    monthly_aum = month_data['AUM'].iloc[0]
    # 计算缺失值
    missing_weight = 1 - month_data.iloc[1:]['weight'].sum()
    missing_marketcap = missing_weight * monthly_aum
    # 定位当月第一行填充值
    first_row_idx = month_data.index[0]
    df.loc[first_row_idx, 'weight'] = missing_weight
    df.loc[first_row_idx, 'MarketCap'] = missing_marketcap

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