将pandas数据框日期范围扩展到单独的行 [英] Expand pandas dataframe date ranges to individual rows

问题描述

我必须根据开始日期和结束日期将熊猫数据框扩展为单独的行.

I have to expand a pandas dataframe based on start date and end date, into individual rows.

原始数据框如下

原始数据帧

我的最终数据帧应在各行的开始日期和结束日期之间的每一天重复一次.需要针对每个日期扩展结果，同时保留除'startdate'和'enddate'以外的其他列.

My final dataframe should be repeated for each day between start and end date of individual rows.The result needs to be expanded for each date while the other columns except 'startdate' and 'enddate' is preserved.

例如，应将起始日期= 20年1月1日和结束日期20年1月15日的第一行扩展为代表该系列中一个日期的15个独立行，如示例结果datafarame所示:

For instance , the first row with startdate = 01-Jan-20 and enddate 15-Jan-20 should be expanded as 15 seperate rows representing one date in the series as shown in the sample resultant datafarame here:

期望的结果数据帧

我尝试了itertuples的解决方案来遍历数据框并打破对单个日期的范围，但是当数据框的大小很大时，解决方案就很慢.

I tried with the solution of itertuples to iterate over the dataframe and break the ranges to individual dates, but the solution is slow when the size of the dataframe is large.

对此，任何最佳解决方案都将受到高度赞赏.

Any optimal solution on this is highly appreciated.

推荐答案

使用 pandas.date_range ，然后使用

Use pandas.date_range in a list comprehension ,then use DataFrame.explode (you need to be using at least pandas v 0.25.0 for the explode method):

# Minimal example setup
df = pd.DataFrame({
    'TRIPNAME': ['HIGHSEASON', 'LOWSEASON', 'MEDSEASON'],
    'TRIPCAT': ['H', 'L', 'M'],
    'STARTDATE' : ['01JAN20', '16SEP20', '29JAN20'],
    'ENDDATE': ['15JAN20', '30NOV20', '19JUL20'],
    'FARE': [345, 280, 250]
})


df['DATE'] = [pd.date_range(s, e, freq='d') for s, e in
              zip(pd.to_datetime(df['STARTDATE']), pd.to_datetime(df['ENDDATE']))]

df = df.explode('DATE').drop(['STARTDATE', 'ENDDATE'], axis=1)

print(df)

[出]

      TRIPNAME TRIPCAT  FARE       DATE
0   HIGHSEASON       H   345 2020-01-01
0   HIGHSEASON       H   345 2020-01-02
0   HIGHSEASON       H   345 2020-01-03
0   HIGHSEASON       H   345 2020-01-04
0   HIGHSEASON       H   345 2020-01-05
..         ...     ...   ...        ...
2    MEDSEASON       M   250 2020-07-15
2    MEDSEASON       M   250 2020-07-16
2    MEDSEASON       M   250 2020-07-17
2    MEDSEASON       M   250 2020-07-18
2    MEDSEASON       M   250 2020-07-19

这篇关于将pandas数据框日期范围扩展到单独的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！