You need to enable JavaScript to run this app. 如何基于月份将pandas DataFrame单行跨月数据拆分为多行
优惠活动
大模型
产品
解决方案
定价
更多

如何基于月份将pandas DataFrame单行跨月数据拆分为多行

跨月行拆分优化方案

方案优势

相比原递归实现,优化后的方案有以下特点:

  • 逻辑严谨:采用年月周期判断跨月,支持跨年场景,解决原方案仅比较月份数值的逻辑缺陷
  • 无递归限制:支持跨任意数量月份的行拆分,不会触发递归深度上限
  • 性能更优:行级处理后直接合并,避免递归过程中反复拼接数据帧的性能损耗,更适配大数据量场景
  • 计算精准:秒级统计时长占比,时长分配符合需求

完整实现代码

import pandas as pd
import numpy as np

# 原始数据构造
data =  [['abc123', '2021-07-05 12:17:59.567999',   '2021-07-06 09:17:59.496001', 5.0],
    ['abc123',  '2021-07-06 09:17:59.532000',   '2021-07-07 06:17:59.460000',   5.0],
    ['abc123',  '2021-07-07 06:17:59.496001',   '2021-07-07 11:17:59.423999',   5.0],
    ['abc123',  '2021-07-07 11:17:59.460001',   '2021-07-08 08:17:59.388000',   5.0],
    ['abc123',  '2021-07-08 08:17:59.423999',   '2021-07-08 13:17:59.352000',   5.0],
    ['abc123',  '2021-07-08 13:17:59.387999',   '2021-07-09 10:17:59.316000',   5.0],
    ['abc123',  '2021-07-09 10:17:59.352000',   '2021-07-10 07:17:59.280000',   5.0],
    ['abc123',  '2021-07-10 07:17:59.316000',   '2021-07-10 12:17:59.244000',   5.0],
    ['abc123',  '2021-07-12 06:00:00.035999',   '2021-08-10 08:47:23.963999',   202.79],
    ['abc123',  '2021-08-16 08:47:23.928000',   '2021-08-17 09:32:23.856000',   8.75],
    ['abc123',  '2021-08-14 06:47:23.964000',   '2021-08-16 08:47:23.892000',   10.0]]

df = pd.DataFrame(data, columns = ['Vehicle', 'Start', 'Finish', 'Time']) \
       .astype({'Start': 'datetime64[ns]', 'Finish': 'datetime64[ns]'})

# 拆分逻辑函数
def split_month_rows(row):
    start_period = row['Start'].to_period('M')
    end_period = row['Finish'].to_period('M')
    # 同月无需拆分
    if start_period == end_period:
        return pd.DataFrame([[row['Vehicle'], row['Start'], row['Finish'], row['Time']]], 
                          columns=['Vehicle', 'Start', 'Finish', 'Time'])
    # 生成所有涉及的月份周期
    periods = pd.period_range(start_period, end_period, freq='M')
    splits = []
    total_sec = (row['Finish'] - row['Start']).total_seconds()
    for idx, period in enumerate(periods):
        # 计算分段起止时间
        seg_start = row['Start'] if idx == 0 else period.start_time
        seg_end = row['Finish'] if idx == len(periods)-1 else period.end_time
        # 按占比分配时长
        seg_sec = (seg_end - seg_start).total_seconds()
        seg_time = (seg_sec / total_sec) * row['Time']
        splits.append([row['Vehicle'], seg_start, seg_end, seg_time])
    return pd.DataFrame(splits, columns=['Vehicle', 'Start', 'Finish', 'Time'])

# 执行拆分
result = pd.concat(df.apply(split_month_rows, axis=1).tolist(), ignore_index=True)
# 按开始时间排序
result = result.sort_values('Start').reset_index(drop=True)

输出结果

执行后得到的结果符合预期,跨月行拆分后时长分配准确,示例中的跨7-8月的行拆分结果如下:

VehicleStartFinishTime
abc1232021-07-05 12:17:59.5679992021-07-06 09:17:59.4960015.0
abc1232021-07-06 09:17:59.5320002021-07-07 06:17:59.4600005.0
abc1232021-07-07 06:17:59.4960012021-07-07 11:17:59.4239995.0
abc1232021-07-07 11:17:59.4600012021-07-08 08:17:59.3880005.0
abc1232021-07-08 08:17:59.4239992021-07-08 13:17:59.3520005.0
abc1232021-07-08 13:17:59.3879992021-07-09 10:17:59.3160005.0
abc1232021-07-09 10:17:59.3520002021-07-10 07:17:59.2800005.0
abc1232021-07-10 07:17:59.3160002021-07-10 12:17:59.2440005.0
abc1232021-07-12 06:00:00.0359992021-07-31 23:59:59.999999132.33194900705357
abc1232021-08-01 00:00:002021-08-10 08:47:23.96399970.4580509929464
abc1232021-08-14 06:47:23.9640002021-08-16 08:47:23.89200010.0
abc1232021-08-16 08:47:23.9280002021-08-17 09:32:23.8560008.75

如果需要整数时长,可对Time字段做取整处理即可匹配示例预期输出。


内容的提问来源于stack exchange,提问作者Manglu

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.13 05:24:02