如何按指定日期、指定日期所属月份及上月汇总数据

需求说明 #

需基于选定日期，以COA Code为分组维度，分别聚合三类数据：选定日期的单条数据、选定日期所属月份的汇总数据、选定日期上月的汇总数据，最终合并成目标格式结果。

示例数据 #

import pandas as pd
df = pd.read_excel('https://github.com/hoatranobita/hoatranobita/raw/refs/heads/main/Check%20data%20(1).xlsx', sheet_name='Data')
# 数据结构示例
"""
    COA Code    USDConversion Amount    Base Date 2
0   19010000000 26924582.44            2024-10-01
1   19010000000 38835600.44            2024-10-02
...
69  58000000000 38323598.99            2024-11-18
"""

期望输出（选定日期为2024-11-18时） #

COA Code        2024-11-18  October         November
0   19010000000 42625047.24 1354513618.61   584813860.97
1   58000000000 38323598.99 820927014.08    456265522.64

实现步骤与代码 #

1. 预处理日期列 #

确保日期列转为datetime类型，便于时间维度筛选：

df['Base Date 2'] = pd.to_datetime(df['Base Date 2'])

2. 定义目标日期并计算时间范围 #

target_date = pd.to_datetime('2024-11-18')
# 计算当月的起止日期
current_month_start = target_date.replace(day=1)
current_month_end = target_date + pd.offsets.MonthEnd(0)
# 计算上月的起止日期
last_month_start = current_month_start - pd.offsets.MonthBegin(1)
last_month_end = current_month_start - pd.offsets.Day(1)

3. 分别提取三类数据并聚合 #

# 1. 选定日期的单条数据
date_data = df[df['Base Date 2'] == target_date].set_index('COA Code')['USDConversion Amount'].rename(str(target_date.date()))

# 2. 上月数据汇总求和
last_month_data = df[
    (df['Base Date 2'] >= last_month_start) & 
    (df['Base Date 2'] <= last_month_end)
].groupby('COA Code')['USDConversion Amount'].sum().rename(last_month_start.strftime('%B'))

# 3. 当月数据汇总求和
current_month_data = df[
    (df['Base Date 2'] >= current_month_start) & 
    (df['Base Date 2'] <= current_month_end)
].groupby('COA Code')['USDConversion Amount'].sum().rename(current_month_start.strftime('%B'))

4. 合并数据得到最终结果 #

result = pd.concat([date_data, last_month_data, current_month_data], axis=1).reset_index()
print(result)

执行后即可得到与期望格式一致的输出。

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