比较两个数据帧，根据给定条件进行计数并更新现有计数列

Question

我正在学习熊猫并陷入一个问题，我正在用示例示例解释下面的问题。假设有 3 个 DF：DF1、DF2 和 DF3。我想根据DF3 ID和日期列将DF3与DF1以及Df2进行比较，其中日期位于开始日期和结束日期之间，更新DF1和DF2中的计数。

d = {'ID':['51','51','51','52'], 'Count' : ['2', '2', '1', '2'], 
 'StartDate' : pd.to_datetime(['2018-09-01', '2018-07-01', '2018-08-01', '2018-08-01']),
 'EndDate' : pd.to_datetime(['2018-09-30', '2018-07-31', '2018-08-31', '2018-08-31'])}
df1= pd.DataFrame(data=d)
Count   EndDate        ID       StartDate
2       2018-09-30      51      2018-09-01
2       2018-07-31      51      2018-07-01
1       2018-08-31      51      2018-08-01
2       2018-08-31      52      2018-08-01
d = {'ID':['51','52'], 'Count' : ['5', '2'], 
 'StartDate' : pd.to_datetime(['2018-07-01', '2018-07-01']),
 'EndDate' : pd.to_datetime(['2018-09-30', '2018-09-30'])}
df2= pd.DataFrame(data=d)
Count    EndDate    ID  StartDate
5       2018-09-30  51  2018-07-01
2       2018-09-30  52  2018-07-01
d = {'ID':['51','51','51','51','52'], 'Count' : ['1', '1', '1', '1','2'], 
 'Date' : pd.to_datetime(['2018-09-01', '2018-09-14', '2018-08-14', '2018-07-27','2018-08-13'])}
df3= pd.DataFrame(data=d)
Count   Date    ID
1       2018-09-01  51
1       2018-09-14  51
1       2018-08-14  51
1       2018-07-27  51
2       2018-08-13  52
The expected output is DF1 and Df2 with updated count-
DF1 - 
Count   EndDate ID  StartDate
2       2018-09-30  51  2018-09-01
1       2018-07-31  51  2018-07-01
1       2018-08-31  51  2018-08-01
2       2018-08-31  52  2018-08-01
DF2-
Count   EndDate ID  StartDate
4       2018-09-30  51  2018-07-01
2       2018-09-30  52  2018-07-01

据观察，DF1 计数在开始日期-"2018-07-01"和结束日期 - "2018-07-31"更新为 1，因为 DF3 中只有 1 个第 07 个月的条目。同样，DF2 中的总体（季度）计数从 5 更改为 4。请帮助我解决这种情况。提前谢谢。

Answer 1

注意：示例代码中的Count列被指定为字符串 - 我已将它们转换为整数。

我将从我认为解决聚合到不同时间段问题的最佳方法开始：将 resample 函数与日期索引一起使用：

data = df3.set_index('Date')
quarterly = data.resample('Q').sum()
monthly = data.resample('M').sum()

这导致

>>> quarterly
            Count
Date             
2018-09-30      6
>>> monthly
            Count
Date             
2018-07-31      1
2018-08-31      3
2018-09-30      2

如果新数据可用，您甚至可以非常轻松地更新它：

newdata = pd.DataFrame({'Date': [pd.to_datetime('2019-01-03')], 'Count': [2]}).set_index('Date')
quarterly.add(newdata.resample('Q').sum(), fill_value=0)

如果您真的想按照问题中的方式执行此操作，在给定的日期范围内，此函数以简单的方式执行您想要的操作：

def updatecount(rangedf, countdf):
    for rowid, row in rangedf.iterrows():
        count = countdf.query('ID == @row.ID and @row.StartDate <= Date <= @row.EndDate').Count.sum()
        rangedf.loc[rowid, "Count"] += count

你会把它称为

updatecount(df1, df3)
updatecount(df2, df3)