python - 填写与 pandas 中特定属性相关的缺失日期 [英] python - Fill in missing dates with respect to a specific attribute in pandas

问题描述

我的数据如下所示:

id, date, target
1,2016-10-24,22
1,2016-10-25,31
1,2016-10-27,44
1,2016-10-28,12
2,2016-10-21,22
2,2016-10-22,31
2,2016-10-25,44
2,2016-10-27,12

我想在 id 中填写缺失的日期.比如id=1的日期范围是2016-10-24~2016-10-28，缺少2016-10-26.而且，id=2的日期范围是2016-10-21~2016-10-27，缺少2016-10-23、2016-10-24和2016-10-26.我想补缺的日期，目标值填为0.

I want to fill in missing dates among id. For example, the date range of id=1 is 2016-10-24 ~ 2016-10-28, and 2016-10-26 is missing. Moreover, the date range of id=2 is 2016-10-21 ~ 2016-10-27, and 2016-10-23, 2016-10-24 and 2016-10-26 are missing. I want to fill in the missing dates and fill in the target value as 0.

因此，我希望我的数据如下:

Therefore, I want my data to be as below:

id, date, target
1,2016-10-24,22
1,2016-10-25,31
1,2016-10-26,0
1,2016-10-27,44
1,2016-10-28,12
2,2016-10-21,22
2,2016-10-22,31
2,2016-10-23,0
2,2016-10-24,0
2,2016-10-25,44
2,2016-10-26,0
2,2016-10-27,12

有人可以帮我吗?

提前致谢.

推荐答案

您可以使用 groupby 和 resample - 然后是问题 fillna - 所以需要 asfreq 首先:

You can use groupby with resample - then is problem fillna - so need asfreq first:

#if necessary convert to datetime
df.date = pd.to_datetime(df.date)
df = df.set_index('date')
df = df.groupby('id').resample('d')['target'].asfreq().fillna(0).astype(int).reset_index()
print (df)
    id       date  target
0    1 2016-10-24      22
1    1 2016-10-25      31
2    1 2016-10-26       0
3    1 2016-10-27      44
4    1 2016-10-28      12
5    2 2016-10-21      22
6    2 2016-10-22      31
7    2 2016-10-23       0
8    2 2016-10-24       0
9    2 2016-10-25      44
10   2 2016-10-26       0
11   2 2016-10-27      12

这篇关于python - 填写与 pandas 中特定属性相关的缺失日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！