Pandas - 将具有开始和结束日期的数据框转换为每日数据 [英] Pandas - Convert dataframe with start and end date to daily data

问题描述

每个 ID 有一个记录，包括开始日期和结束日期

I have one record per ID with start date and end date

id  age state   start_date  end_date
123 18  CA     2/17/2019    5/4/2019
223 24  AZ     1/17/2019    3/4/2019

我想为开始日和结束日之间的每一天创建一条记录，以便将每日活动数据加入其中.目标输出看起来像这样

I want to create a record for each day between the start and end day, so I can join daily activity data to it. The target output would look something like this

id  age state   start_date
123 18  CA      2/17/2019
123 18  CA      2/18/2019
123 18  CA      2/19/2019
123 18  CA      2/20/2019
123 18  CA      2/21/2019
            …
123 18  CA      5/2/2019
123 18  CA      5/3/2019
123 18  CA      5/4/2019

当然，对数据集中的所有 id 及其各自的开始日期执行此操作.非常感谢任何帮助 - 谢谢！

And of course do this for all ids and their respective start dates in the dataset. Any help is much appreciated - thanks!

推荐答案

melt, GroupBy, resample &填充

首先我们melt (unpivot) 你的两个日期列合二为一.然后我们resample 按天计算:

melt, GroupBy, resample & ffill

First we melt (unpivot) your two date columns to one. Then we resample on day basis:

melt = df.melt(id_vars=['id', 'age', 'state'], value_name='date').drop('variable', axis=1)
melt['date'] = pd.to_datetime(melt['date'])

melt = melt.groupby('id').apply(lambda x: x.set_index('date').resample('d').first())
           .ffill()
           .reset_index(level=1)
           .reset_index(drop=True)

输出

          date     id   age state
0   2019-02-17  123.0  18.0    CA
1   2019-02-18  123.0  18.0    CA
2   2019-02-19  123.0  18.0    CA
3   2019-02-20  123.0  18.0    CA
4   2019-02-21  123.0  18.0    CA
..         ...    ...   ...   ...
119 2019-02-28  223.0  24.0    AZ
120 2019-03-01  223.0  24.0    AZ
121 2019-03-02  223.0  24.0    AZ
122 2019-03-03  223.0  24.0    AZ
123 2019-03-04  223.0  24.0    AZ

[124 rows x 4 columns]

编辑:

我不得不在一个项目中重新审视这个问题，看起来像使用 DataFrame.apply 和 pd.date_range 和 DataFrame.explode 是快了近 3 倍:

I had to revisit this problem in a project, and looks like using DataFrame.apply with pd.date_range and DataFrame.explode is almost 3x faster:

df["date"] = df.apply(
    lambda x: pd.date_range(x["start_date"], x["end_date"]), axis=1
)
df = (
    df.explode("date", ignore_index=True)
    .drop(columns=["start_date", "end_date"])
)

输出

      id  age state       date
0    123   18    CA 2019-02-17
1    123   18    CA 2019-02-18
2    123   18    CA 2019-02-19
3    123   18    CA 2019-02-20
4    123   18    CA 2019-02-21
..   ...  ...   ...        ...
119  223   24    AZ 2019-02-28
120  223   24    AZ 2019-03-01
121  223   24    AZ 2019-03-02
122  223   24    AZ 2019-03-03
123  223   24    AZ 2019-03-04

[124 rows x 4 columns]

这篇关于Pandas - 将具有开始和结束日期的数据框转换为每日数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！