基于前一日Close值填充Pandas透视表NaN值的技术问询

我通过嵌套循环生成并处理数据后，用pandas的pivot_table生成了透视表，索引为Store、Currency、Record、sort（sort保证Record顺序固定为opening->activity->closing），列为Date，值是聚合求和后的Amount Local。生成透视表的代码如下：

df = df.pivot_table(
    index=["Store", "Currency", "Record", "sort"],
    columns="Date",
    values="Amount Local",
    aggfunc="sum",
).sort_values(by=["Custodian Account", "Currency", "sort"])

当前需要处理透视表中的NaN值，规则为：

• 当某一日期对应的三条Record（opening/activity/closing）全为NaN时，次日的Opening和Closing取前一日的Closing值，Activity填充为0。

此前尝试掩码填充activity的NaN为0未生效，使用前向填充（ffill）时Opening会取前一日的Opening值，不符合需求。

解决方案 #

1. 将透视表转为长格式

多层索引和列的结构不利于逐日期处理，先转为长格式方便操作：

import pandas as pd

# 重置索引后，将日期列转为行
df_reset = df.reset_index()
df_melted = df_reset.melt(
    id_vars=["Store", "Currency", "Record", "sort"],
    var_name="Date",
    value_name="Amount Local"
)

2. 标记全NaN的日期

按Store+Currency+Date分组，判断该日期下的三条记录是否全为NaN：

# 给每条记录标记其所属日期是否全为NaN
df_melted['is_all_nan'] = df_melted.groupby(["Store", "Currency", "Date"])['Amount Local'].transform(
    lambda x: x.isna().all()
)

3. 按分组定制填充逻辑

对每个Store+Currency分组，按日期排序后处理填充规则：

def fill_group_missing(group):
    group = group.sort_values("Date")
    # 记录历史有效的closing值
    last_valid_closing = None

    # 先提取所有有效closing值，便于快速查找
    closing_values = group[group['Record'] == 'closing']['Amount Local'].dropna()
    
    for date in group['Date'].unique():
        # 跳过全NaN的日期，处理次日
        if group[group['Date'] == date]['is_all_nan'].iloc[0]:
            continue
        
        # 检查前一日是否为全NaN
        prev_date = date - pd.Timedelta(days=1)
        if prev_date in group['Date'].unique() and group[group['Date'] == prev_date]['is_all_nan'].iloc[0]:
            # 取最近的有效closing值
            if not closing_values.empty:
                target_closing = closing_values[closing_values.index < group[group['Date'] == date].index[0]].iloc[-1]
                # 填充当前日期的opening、closing和activity
                group.loc[
                    (group['Date'] == date) & (group['Record'] == 'opening'),
                    'Amount Local'
                ] = target_closing
                group.loc[
                    (group['Date'] == date) & (group['Record'] == 'closing'),
                    'Amount Local'
                ] = target_closing
                group.loc[
                    (group['Date'] == date) & (group['Record'] == 'activity'),
                    'Amount Local'
                ] = 0
        
        # 更新有效closing值
        current_closing = group.loc[(group['Date'] == date) & (group['Record'] == 'closing'), 'Amount Local'].iloc[0]
        if not pd.isna(current_closing):
            last_valid_closing = current_closing
            # 更新closing_values系列
            closing_values = pd.concat([closing_values, pd.Series([current_closing], index=[group[group['Date'] == date].index[0]])])
    
    return group

# 应用到每个分组
df_filled = df_melted.groupby(["Store", "Currency"]).apply(fill_group_missing).reset_index(drop=True)

4. 重新生成透视表

将处理后的长格式数据转回原透视表结构：

df_final = df_filled.pivot_table(
    index=["Store", "Currency", "Record", "sort"],
    columns="Date",
    values="Amount Local",
    aggfunc="first"
).sort_values(by=["Store", "Currency", "sort"])

核心逻辑说明 #

• 转长格式后，能更灵活地按分组和日期遍历处理，避开多层索引的复杂操作；
• 通过标记全NaN日期，精准定位需要处理的次日数据，确保Opening/Closing继承最近的有效Closing值，而非前一日的Opening；
• 单独处理Activity的0填充，避免普通填充方法的误操作。

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