Python 条件求和使用日期和条件

Question

我必须数据帧并且我正在使用 pandas。我想根据可变日期和列中的值进行累积和

我想向 df2 添加第二列，显示日期，以了解 df2 中的 date2 之后 AVG 列的总和大于 100 的那一天。

例如，df1 和 df2 是我开始的数据帧，df3 是我想要的数据帧，df3['date100'] 是 avg 总和大于 100 的日期:

df1 = pd.DataFrame({'date1': ['1/1/2014', '2/1/2014', '3/1/2014','1/1/2014', '2/1/2014', '3/1/2014','1/1/2014', '2/1/2014', '3/1/2014'],
 'Place':['A','A','A','B','B','B','C','C','C'],'AVG': [62,14,47,25,74,60,78,27,41]})

df2 = pd.DataFrame({'date2': ['1/1/2014', '2/1/2014'], 'Place':['A','C'])})

*Something*
df3 = pd.DataFrame({'date2': ['1/1/2014', '2/1/2014'], 'Place':['A','C'], 'date100': ['3/1/2014', '2/1/2014'], 'sum': [123, 105]})

我找到了一些答案，但大多数都使用 groupby 并且 df2 没有组。

Answer 1

由于您的示例非常基本，因此如果您有需要我处理的边缘情况，请询问。该解决方案意味着:

解决方案:

#   For this solution your DataFrame needs to be sorted by date.
limit = 100
df = pd.DataFrame({
    'date1': ['1/1/2014', '2/1/2014', '3/1/2014','1/1/2014',
              '2/1/2014', '3/1/2014','1/1/2014', '2/1/2014', '3/1/2014'], 
    'Place':['A','A','A','B','B','B','C','C','C'],
    'AVG': [62,14,47,25,74,60,78,27,41]})

df2 = pd.DataFrame({'date2': ['1/1/2014', '2/1/2014'], 'Place':['A','C']})

result = []
for row in df2.to_dict('records'):
    #   For each date, I want to select the date that comes AFTER this one.
    #   Then, I take the .cumsum(), because it's the agg you wish to do.
    #   Filter by your limit and take the first occurrence.
    #   Converting this to a dict, appending it to a list, makes it easy
    #   to rebuild a DataFrame later.
    ndf = df.loc[ (df['date1'] >= row['date2']) & (df['Place'] == row['Place']) ]\
            .sort_values(by='date1')
    ndf['avgsum'] = ndf['AVG'].cumsum()
    final_df = ndf.loc[ ndf['avgsum'] >= limit ]

    #   Error handling, in case there is not avgsum above the threshold.
    try:
        final_df = final_df.iloc[0][['date1', 'avgsum']].rename({'date1' : 'date100'})
        result.append( final_df.to_dict() )
    except IndexError:
        continue

df3 = pd.DataFrame(result)

final_df = pd.concat([df2, df3], axis=1, sort=False)
print(final_df)
#       date2 Place  avgsum   date100
# 0  1/1/2014     A   123.0  3/1/2014
# 1  2/1/2014     C     NaN       NaN