我有一个数据框,我需要在同一天内配对连续事件,然后从前者中减去后者。每个都有时间戳和日期。
time date event score
0 2022-03-07 06:45:00+00:00 2022-03-07 light 80.066667
1 2022-03-07 18:12:00+00:00 2022-03-07 dark 79.857667
2 2022-03-30 06:25:00+00:00 2022-03-30 light 107.060833
3 2022-03-30 13:38:00+00:00 2022-03-30 dark 105.324000
4 2022-03-30 13:40:00+00:00 2022-03-30 dark 105.239750
5 2022-03-30 15:47:00+00:00 2022-03-30 light 106.863143
6 2022-04-01 06:25:00+00:00 2022-04-01 light 101.271867
我曾尝试使用
df = df.pivot(index='time', columns='event', values='score')
event light dark
time
2022-03-07 06:45:00+00:00 80.066667 NaN
2022-03-07 18:12:00+00:00 NaN 79.857667
2022-03-30 06:25:00+00:00 107.060833 NaN
2022-03-30 13:38:00+00:00 NaN 105.324000
2022-03-30 13:40:00+00:00 NaN 105.239750
2022-03-30 15:47:00+00:00 106.863143 NaN
2022-04-01 06:25:00+00:00 101.271867 NaN
然而,由于事件发生在不同的时间,扩展数据帧具有NAN。理想情况下,我会这样结束,我会保留两个事件中第一次出现的时间(亮或暗),对齐事件(注意:对应的暗与2022-04-01尚未出现的光相匹配),当光先出现时,我从光中减去暗,当暗先出现时,我从暗中减去光。
event light dark diff
time
2022-03-07 06:45:00+00:00 80.066667 79.857667 -0.208999
2022-03-30 06:25:00+00:00 107.060833 105.324000 -1.7368
2022-03-30 13:40:00+00:00 106.863143 105.239750 -1.6233
2022-04-01 06:25:00+00:00 101.271867 NaN NaN