根据当前数据帧中的约束生成数据帧列

Question

我有一个包含以下列的数据帧：

Date_2      Date_1     is_B
02/08/2019  01/09/2019  1
02/08/2019  01/09/2019  1
02/08/2019  01/09/2019  0
02/08/2019  01/09/2019  0
.   .   .
.   .   .
.   .   .
31/08/2019  01/09/2019  0
31/08/2019  01/09/2019  0
31/08/2019  01/09/2019  0
31/08/2019  01/09/2019  0
31/08/2019  01/09/2019  0
31/08/2019  01/09/2019  1
31/08/2019  01/09/2019  1

我想生成另一个数据帧df2，以便输出如下所示：

Date_1  Total_count Total(is_b = 1) num_2   num_3   num_5   num_20
01/09/2019  493      147            26      30      32       59
Total_Count = total entries for Date_1 in the dataframe 
Total(is_b = 1) = total entries for Date_1 where is_b = 1
num_2 = total entries for Date_1 for 2 days where Date_2 = (Date_1 - 1 to Date_1 - 2){Both included as well}
num_3 = total entries for Date_1 for 3 days where Date_2 = (Date_1 - 3 to Date_1 - 5){Both included as well}
num_5 = total entries for Date_1 for 5 days where Date_2 = (Date_1 - 6 to Date_1 - 10){Both included as well}
num_20 = total entries for Date_1 for 20 days where Date_2 = (Date_1 - 11 to Date_1 - 30){Both included as well}

我能够使用轻松生成前2列

df.groupby('Date_1')['Date_1'].count()
df.loc[df.isBooked == 1].groupby('Date_1')['Date_1'].count()

我不知道如何计算其他列：

我确实试过这个：

df.loc[(df.isBooked == 1) & (df.Booking_Date = Flight_Date - 1) & (df.Booking_Date = Flight_Date - 2)].groupby('Flight_Date')['Flight_Date'].count().reset_index(name='num_2')

但这完全是一个无效的语法。

有人能帮我生成列num_2、num_3、num_5、num_20吗。

Answer 1

答案有两部分。

日期解析

从示例中可以看出，日期是而不是解析的-它们是字符串。必须对它们进行解析才能执行日期操作。

import pandas as pd
def dateparse(d):
return pd.datetime.strptime(d, '%d/%m/%Y')
for c in ['Date_1', 'Date_2']:
df[c] = df[c].map(dateparse)

如果您打印df，它应该是这样的(通知日期格式(：

Date_2     Date_1  is_B
0  2019-08-02 2019-09-01     1
1  2019-08-02 2019-09-01     1
2  2019-08-02 2019-09-01     0
3  2019-08-02 2019-09-01     0

现在，列具有dtype: datetime64[ns]。

统计数据的计算

我们将计算几个以Date_1为索引的级数，然后将它们合并。

total_count = df.groupby('Date_1')['Date_1'].count().rename('Total_Count')
total_count_is_b = df[df.is_B == 1].groupby('Date_1')['Date_1'] 
.count().rename('Total(is_b = 1)')

要获得num_2，请执行以下操作：

from datetime import timedelta
num_2_df = df[
(df.is_B == 1) &
df.Date_2.between(
df.Date_1 - timedelta(days=2),
df.Date_1 - timedelta(days=1)
)
].groupby('Date_1')['Date_2'].count().rename('num_2')
# notice argument order of `pandas.Series.between`

其他num_3、num_5、num_20可以类似地计算：

num_3_df = df[
(df.is_B == 1) &
df.Date_2.between(df.Date_1 - timedelta(days=5), df.Date_1 - timedelta(days=3))
].groupby('Date_1')['Date_2'].count().rename('num_3')
num_5_df = df[
(df.is_B == 1) &
df.Date_2.between(df.Date_1 - timedelta(days=10), df.Date_1 - timedelta(days=6))
].groupby('Date_1')['Date_2'].count().rename('num_5')
num_20_df = df[
(df.is_B == 1) &
df.Date_2.between(df.Date_1 - timedelta(days=30), df.Date_1 - timedelta(days=11))
].groupby('Date_1')['Date_2'].count().rename('num_20')

最后，所有列合并到一个表中：

result_df = pd.concat(
[total_count, total_count_is_b, num_2_df, num_3_df, num_5_df, num_20_df],
axis=1
).fillna(0).astype(int)
result_df = result_df.reset_index()