Суммируйте столбец Pandas DataFrame под диапазонами другого DataFrame
У меня есть два DataFrames DF1 и DF2, и я хочу агрегировать значения одного столбца в DF1 по диапазонам дат столбца в DF2. Вот мой воспроизводимый пример:
DF1 варьируется от 6/14/2013 до 7/13/2013 и отсортирован по убыванию во времени. Его столбцы, которые необходимо агрегировать, - это a и b. Обратите внимание, что для одного и того же date может быть несколько записей.
list1 = [{'a': 5, 'date': '7/13/2013', 'b': 13},
{'a': 4, 'date': '7/12/2013', 'b': 14},
{'a': 7, 'date': '7/12/2013', 'b': 12},
{'a': 2, 'date': '7/10/2013', 'b': 18},
{'a': 9, 'date': '7/7/2013', 'b': 17},
{'a': 6, 'date': '7/5/2013', 'b': 20},
{'a': 8, 'date': '6/30/2013', 'b': 12},
{'a': 5, 'date': '6/29/2013', 'b': 13},
{'a': 3, 'date': '6/25/2013', 'b': 13},
{'a': 4, 'date': '6/23/2013', 'b': 10},
{'a': 1, 'date': '6/22/2013', 'b': 16},
{'a': 6, 'date': '6/20/2013', 'b': 19},
{'a': 7, 'date': '6/18/2013', 'b': 12},
{'a': 9, 'date': '6/16/2013', 'b': 15}]
DF1 = pd.DataFrame(list1)
DF2 содержит разделители недельных дат, для которых следует объединить столбцы DF1a и b.
list2 = [{'datesep': '6/22/2013', 'c': 32},
{'datesep': '6/29/2013', 'c': 23},
{'datesep': '7/6/2013', 'c': 44},
{'datesep': '7/13/2013', 'c': 18},
{'datesep': '7/20/2013', 'c': 51}]
DF2 = pd.DataFrame(list2)
Я хочу оставить DF1.c как есть и агрегировать DF1.a и DF1.b, чтобы значения суммировались на разделителе DF2.datesep чуть выше их DF1.date. То есть значения DF1.a и DF1.b от 6/16/2013 до 6/22/2013 (оба включительно) должны быть агрегированы в ближайшем разделителе следующей даты, которым является строка DF2.datesep=6/22/2013. От 7/7/2013 до 7/13/2013 (оба включительно) следует агрегировать по ближайшему разделителю следующей даты, который представляет собой строку DF2.datesep=7/13/2013 и т. д. Поэтому результат должен выглядеть так (порядок столбцов не имеет значения):
c date a_sum b_sum
0 32 6/22/2013 23 62
1 23 6/29/2013 12 36
2 44 7/6/2013 14 32
3 18 7/13/2013 27 74
4 51 7/20/2013 - -
Я сделал это с циклом на list1 и list2, но есть ли решение Pandas / Numpy, которое использует DF1 и DF2? Спасибо!
Ответы 1
Сначала вам нужно преобразовать строки даты в фактическую дату. Затем вы можете использовать лямбда для вычисления a_sum и b_sum для каждой строки. Наконец, объедините сумму df с DF2:
DF1.date = pd.to_datetime(DF1.date)
DF2['end'] = pd.to_datetime(DF2.datesep)
DF2['start'] = DF2.end.shift(1).fillna(pd.to_datetime('1970-01-01'))
sums = DF2.apply(lambda x: DF1.loc[DF1.date.gt(x.start) & DF1.date.le(x.end)][['a','b']].sum(), axis=1)
sums.columns=['a_sum','b_sum']
pd.concat([DF2[['c','datesep']],sums],1)
c datesep a_sum b_sum
0 32 6/22/2013 23 62
1 23 6/29/2013 12 36
2 44 7/6/2013 14 32
3 18 7/13/2013 27 74
4 51 7/20/2013 0 0