如果日期介于两个日期之间，则查找每个ID的值之和

Question

data={'id':[1,1,1,1,2,2,2,2],
      'date1':[datetime.date(2016,1,1),datetime.date(2016,1,2),datetime.date(2016,1,2),datetime.date(2016,1,4),
               datetime.date(2016,1,2),datetime.date(2016,1,4),datetime.date(2016,1,3),datetime.date(2016,1,1)],
      'date2':[datetime.date(2016,1,5),datetime.date(2016,1,3),datetime.date(2016,1,5),datetime.date(2016,1,5),
               datetime.date(2016,1,4),datetime.date(2016,1,5),datetime.date(2016,1,4),datetime.date(2016,1,1)],
      'score1':[5,7,3,2,9,3,8,3],
      'score2':[1,3,0,5,2,20,7,7]}
df=pd.DataFrame.from_dict(data)

数据帧df如下所示：

   id       date1       date2  score1  score2
0   1  2016-01-01  2016-01-05       5       1
1   1  2016-01-02  2016-01-03       7       3
2   1  2016-01-02  2016-01-05       3       0
3   1  2016-01-04  2016-01-05       2       5
4   2  2016-01-02  2016-01-04       9       2
5   2  2016-01-04  2016-01-05       3      20
6   2  2016-01-03  2016-01-04       8       7
7   2  2016-01-01  2016-01-01       3       7

另一个包含ID usetdate的数据帧UF

   id      usetdate  
0   1  2016-01-01    
1   1  2016-01-03    
2   2  2016-01-04    
3   2  2016-01-02    

如果我通过的日期（usetdate）介于date1和date2之间，我想根据UF的每个ID找到score1和score2的总和

   id      usetdate  score1  score2
0   1  2016-01-01      5       1
1   1  2016-01-03     17       9
2   2  2016-01-04     20       29
3   2  2016-01-02     9        2

Answer 1

import datetime
import pandas as pd

data={'id':[1,1,1,1,2,2,2,2],
      'date1':[datetime.date(2016,1,1),datetime.date(2016,1,2),datetime.date(2016,1,2),datetime.date(2016,1,4),
               datetime.date(2016,1,2),datetime.date(2016,1,4),datetime.date(2016,1,3),datetime.date(2016,1,1)],
      'date2':[datetime.date(2016,1,5),datetime.date(2016,1,3),datetime.date(2016,1,5),datetime.date(2016,1,5),
               datetime.date(2016,1,4),datetime.date(2016,1,5),datetime.date(2016,1,4),datetime.date(2016,1,1)],
      'score1':[5,7,3,2,9,3,8,3],
      'score2':[1,3,0,5,2,20,7,7]}
df=pd.DataFrame.from_dict(data)

data={'id':[1,1,2,2],
      'date':[datetime.date(2016,1,1),datetime.date(2016,1,2),datetime.date(2016,1,2),datetime.date(2016,1,4)]}

df1=pd.DataFrame.from_dict(data)

data1=[]
x=[]
def agg(df,df1):
    for i in range(1,2):
        x=list(df1.id)
        y=list(df1.date)
        data1= df[df.id==x[i]]
        data2=data1[data1.date1 > y[i]] 
        data3=data2[data1.date2 < y[i]]
        data4=data3.groupby(['id']).agg({"score1":sum})
        x.append(data4)
    return data4

agg(df,df1)

请试试这个