如何使用Pandas按入住退房日期拆分营收并生成单date列

实现代码 #

import pandas as pd

# 原始数据集
df = pd.DataFrame({
    'check_in':['2020-02-27','2020-02-28'],
    'check_out':['2020-02-29','2020-03-02'],
    'revenue':[100,66]
})

# 转换日期列为datetime类型
df['check_in'] = pd.to_datetime(df['check_in'])
df['check_out'] = pd.to_datetime(df['check_out'])

# 生成每个订单覆盖的有效入住日期列表（排除退房日）
df['date'] = df.apply(lambda x: pd.date_range(start=x['check_in'], end=x['check_out'] - pd.Timedelta(days=1), freq='D'), axis=1)

# 计算单订单入住天数、单日分摊营收
df['stay_days'] = df['date'].str.len()
df['daily_revenue'] = df['revenue'] / df['stay_days']

# 拆分日期后按日期汇总营收
result = df.explode('date')[['date', 'daily_revenue']].groupby('date', as_index=False).sum().rename(columns={'daily_revenue':'revenue'})

# 如需转换为整数格式可打开下方注释
# result['revenue'] = result['revenue'].astype(int)

print(result)

运行输出 #

date  revenue
0 2020-02-27     50.0
1 2020-02-28     72.0
2 2020-02-29     22.0
3 2020-03-01     22.0

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