python - 在 Pandas (Python) 中与 groupby 和 where 合并

Question

我有两个这样的表:

Customr Issue   Date_Issue
1   1   01/01/2019
1   2   03/06/2019
1   3   04/07/2019
1   4   13/09/2019
2   5   01/02/2019
2   6   16/03/2019
2   7   20/08/2019
2   8   30/08/2019
2   9   01/09/2019
3   10  01/02/2019
3   11  03/02/2019
3   12  05/03/2019
3   13  20/04/2019
3   14  25/04/2019
3   15  13/05/2019
3   16  20/05/2019
3   17  25/05/2019
3   18  01/06/2019
3   19  03/07/2019
3   20  20/08/2019

Customr Date_Survey df_Score
1   06/04/2019  10
2   10/06/2019  9
3   01/08/2019  3

并且我需要获取调查日期前三个月内每个客户的问题数量。

但我无法在 Pandas 中获取此查询。

#first table
index_survey = [0,1,2]
Customer_Survey = pd.Series([1,2,3],index= index_survey)
Date_Survey = pd.Series(["06/04/2019","10/06/2019","01/08/2019"])
df_Score=[10, 9, 3]
df_survey = pd.DataFrame(Customer_Survey,columns = ["Customer_Survey"])
df_survey["Date_Survey"] =Date_Survey
df_survey["df_Score"] =df_Score

#And second table
index_survey = [0,1,2]
Customer_Survey = pd.Series([1,2,3],index= index_survey)
Date_Survey = pd.Series(["06/04/2019","10/06/2019","01/08/2019"])
df_Score=[10, 9, 3]
df_survey = pd.DataFrame(Customer_Survey,columns = ["Customer_Survey"])
df_survey["Date_Survey"] =Date_Survey
df_survey["df_Score"] =df_Score

期待结果

Custr   Date_Survey Score   Count_issues
1   06/04/2019  10  0
2   10/06/2019  9   1
3   01/08/2019  3   5

Answer 1

使用:

#convert columns to datetimes
df1['Date_Issue'] = pd.to_datetime(df1['Date_Issue'], dayfirst=True)
df2['Date_Survey'] = pd.to_datetime(df2['Date_Survey'], dayfirst=True)
#create datetimes for 3 months before 
df2['Date1'] = df2['Date_Survey'] - pd.offsets.DateOffset(months=3)

#merge together
df = df1.merge(df2, on='Customr')
#filter by between, select only Customr and get counts
s = df.loc[df['Date_Issue'].between(df['Date1'], df['Date_Survey']), 'Customr'].value_counts()

#map to new column and replace NaNs to 0
df2['Count_issues'] = df2['Customr'].map(s).fillna(0, downcast='int')
print (df2)
   Customr Date_Survey  df_Score      Date1  Count_issues
0        1  2019-04-06        10 2019-01-06             0
1        2  2019-06-10         9 2019-03-10             1
2        3  2019-08-01         3 2019-05-01             5