如何计算数据帧中一组分组行中先前的差异 [英] How to calculate difference on previous within set of grouped rows in a dataframe
问题描述
我正在寻求有关 Pandas 中此同时分组/行对行差异问题的帮助.问题与此处所述的 R 完全相同:如何计算两者之间的时间差日期时间，对于每个组(学生合同)?
我有这样的数据:
# USER_ID CONTRACT_REF SUBMISSION_DATE1 1 A 20/6 01:002 1 A 20/6 02:003 1 乙 20/6 03:004 4 A 20/6 04:005 5 A 20/6 05:006 5 乙 20/6 06:007 7 A 20/6 07:008 7 乙 20/6 08:009 7 乙 20/6 09:3010 7 乙 20/6 10:00我想为每个唯一的 USER_ID - CONTRACT_REF 对计算与上次提交的时间差.
注意:每个 USER_ID - CONTRACT_REF 对在第一次出现时必须为零(或空值).
所以输出应该如下所示:
# USER_ID CONTRACT_REF SUBMISSION_DATE TIME_DIFFERENCE1 1 A 20/6 01:00 02 1 A 20/6 02:00 13 1 乙 20/6 03:00 04 4 A 20/6 04:00 05 5 A 20/6 05:00 06 5 乙 20/6 06:00 07 7 A 20/6 07:00 08 7 A 20/6 08:00 19 7 A 20/6 09:30 1.510 7 乙 20/6 10:00 0我目前正在从 R 转向 Pandas，虽然我发现语法令人耳目一新，但当涉及到数据帧上的复杂函数时，我有点困惑.
预先感谢您提供任何提示！
[注意:您的数据似乎与您想要的输出不匹配；第二个中没有 CONTRACT_REF C ，即使在您的输出中，我也不明白为什么 5, B 行是 1 而不是 0.我是假设这些是你的错误.由于您没有发表评论，我将使用输出中的数据，因为它会引出一个更有趣的列.]
我可能会做类似的事情
df["SUBMISSION_DATE"] = pd.to_datetime(df["SUBMISSION_DATE"],dayfirst=True)gs = df.groupby(["USER_ID", "CONTRACT_REF"])["SUBMISSION_DATE"]df["TIME_DIFF"] = gs.diff().fillna(0)/pd.datetools.timedelta(hours=1)产生
<预><代码>>>>df# USER_ID CONTRACT_REF SUBMISSION_DATE TIME_DIFF0 1 1 A 2014-06-20 01:00:00 0.01 2 1 A 2014-06-20 02:00:00 1.02 3 1 B 2014-06-20 03:00:00 0.03 4 4 A 2014-06-20 04:00:00 0.04 5 5 A 2014-06-20 05:00:00 0.05 6 5 B 2014-06-20 06:00:00 0.06 7 7 A 2014-06-20 07:00:00 0.07 8 7 A 2014-06-20 08:00:00 1.08 9 7 A 2014-06-20 09:30:00 1.59 10 7 B 2014-06-20 10:00:00 0.0[10 行 x 5 列]<小时>
一些解释:从像
这样的数据帧开始<预><代码>>>>df# USER_ID CONTRACT_REF SUBMISSION_DATE0 1 1 A 20/6 01:001 2 1 A 20/6 02:002 3 1 乙 20/6 03:003 4 4 A 20/6 04:004 5 5 A 20/6 05:005 6 5 乙 20/6 06:006 7 7 A 20/6 07:007 8 7 A 20/6 08:008 9 7 A 20/6 09:309 10 7 乙 20/6 10:00[10 行 x 4 列]我们希望将 SUBMISSION_DATE 列从字符串转换为实际日期对象:
然后我们可以按USER_ID和CONTRACT_REF进行分组，并选择SUBMISSION_DATE列:
然后我们可以取每组的差异:
<预><代码>>>>gs.diff()0 钠盐1 01:00:002 钠盐3 钠盐4 钠盐5 钠盐6 钠盐7 01:00:008 01:30:009 钠盐数据类型:timedelta64[ns]NaT，Not-a-Time，是 NaN 的时间等价物.我们可以用 0 填充这些:
并且由于您希望以小时为单位来衡量事物，我们可以除以 1 小时的时间增量:
<预><代码>>>>gs.diff().fillna(0)/pd.datetools.timedelta(hours=1)0 0.01 1.02 0.03 0.04 0.05 0.06 0.07 1.08 1.59 0.0数据类型:float64将此分配给框架:
<预><代码>>>>df["TIME_DIFF"] = gs.diff().fillna(0)/pd.datetools.timedelta(hours=1)我们完成了:
<预><代码>>>>df# USER_ID CONTRACT_REF SUBMISSION_DATE TIME_DIFF0 1 1 A 2014-06-20 01:00:00 0.01 2 1 A 2014-06-20 02:00:00 1.02 3 1 B 2014-06-20 03:00:00 0.03 4 4 A 2014-06-20 04:00:00 0.04 5 5 A 2014-06-20 05:00:00 0.05 6 5 B 2014-06-20 06:00:00 0.06 7 7 A 2014-06-20 07:00:00 0.07 8 7 A 2014-06-20 08:00:00 1.08 9 7 A 2014-06-20 09:30:00 1.59 10 7 B 2014-06-20 10:00:00 0.0[10 行 x 5 列]I'm looking for help with this simultaneous group-by / row-on-row difference problem in Pandas. The problem is exactly as stated here for R: How to calculate time difference between datetimes, for each group (student-contract)?
I have data like this:
# USER_ID CONTRACT_REF SUBMISSION_DATE
1 1 A 20/6 01:00
2 1 A 20/6 02:00
3 1 B 20/6 03:00
4 4 A 20/6 04:00
5 5 A 20/6 05:00
6 5 B 20/6 06:00
7 7 A 20/6 07:00
8 7 B 20/6 08:00
9 7 B 20/6 09:30
10 7 B 20/6 10:00
I want to calculate the time difference from the previous submission for each unique USER_ID - CONTRACT_REF pair.
Note: each USER_ID - CONTRACT_REF pair has to have a zero (or null) for its first appearance.
So the output should look as follows:
# USER_ID CONTRACT_REF SUBMISSION_DATE TIME_DIFFERENCE
1 1 A 20/6 01:00 0
2 1 A 20/6 02:00 1
3 1 B 20/6 03:00 0
4 4 A 20/6 04:00 0
5 5 A 20/6 05:00 0
6 5 B 20/6 06:00 0
7 7 A 20/6 07:00 0
8 7 A 20/6 08:00 1
9 7 A 20/6 09:30 1.5
10 7 B 20/6 10:00 0
I'm currently moving to Pandas from R, and while I find the syntax refreshing, I'm a bit stumped when it comes to complex functions on dataframes.
Thanks in advance for any tips!
[Note: your data doesn't seem to match your desired output; there are no CONTRACT_REF Cs in the second, and even in your output, I don't see why the 5, B row is 1 and not 0. I'm assuming that these are mistakes on your part. Since you didn't comment, I'm going to use the data from the output, because it leads to a more interesting column.]
I might do something like
df["SUBMISSION_DATE"] = pd.to_datetime(df["SUBMISSION_DATE"],dayfirst=True)
gs = df.groupby(["USER_ID", "CONTRACT_REF"])["SUBMISSION_DATE"]
df["TIME_DIFF"] = gs.diff().fillna(0) / pd.datetools.timedelta(hours=1)
which produces
>>> df
# USER_ID CONTRACT_REF SUBMISSION_DATE TIME_DIFF
0 1 1 A 2014-06-20 01:00:00 0.0
1 2 1 A 2014-06-20 02:00:00 1.0
2 3 1 B 2014-06-20 03:00:00 0.0
3 4 4 A 2014-06-20 04:00:00 0.0
4 5 5 A 2014-06-20 05:00:00 0.0
5 6 5 B 2014-06-20 06:00:00 0.0
6 7 7 A 2014-06-20 07:00:00 0.0
7 8 7 A 2014-06-20 08:00:00 1.0
8 9 7 A 2014-06-20 09:30:00 1.5
9 10 7 B 2014-06-20 10:00:00 0.0
[10 rows x 5 columns]
Some explanation: starting from a dataframe like
>>> df
# USER_ID CONTRACT_REF SUBMISSION_DATE
0 1 1 A 20/6 01:00
1 2 1 A 20/6 02:00
2 3 1 B 20/6 03:00
3 4 4 A 20/6 04:00
4 5 5 A 20/6 05:00
5 6 5 B 20/6 06:00
6 7 7 A 20/6 07:00
7 8 7 A 20/6 08:00
8 9 7 A 20/6 09:30
9 10 7 B 20/6 10:00
[10 rows x 4 columns]
We want to turn the SUBMISSION_DATE column from strings to real date objects:
>>> df["SUBMISSION_DATE"] = pd.to_datetime(df["SUBMISSION_DATE"],dayfirst=True)
>>> df
# USER_ID CONTRACT_REF SUBMISSION_DATE
0 1 1 A 2014-06-20 01:00:00
1 2 1 A 2014-06-20 02:00:00
2 3 1 B 2014-06-20 03:00:00
3 4 4 A 2014-06-20 04:00:00
4 5 5 A 2014-06-20 05:00:00
5 6 5 B 2014-06-20 06:00:00
6 7 7 A 2014-06-20 07:00:00
7 8 7 A 2014-06-20 08:00:00
8 9 7 A 2014-06-20 09:30:00
9 10 7 B 2014-06-20 10:00:00
[10 rows x 4 columns]
Then we can group by USER_ID and CONTRACT_REF, and select the SUBMISSION_DATE column:
>>> gs = df.groupby(["USER_ID", "CONTRACT_REF"])["SUBMISSION_DATE"]
>>> gs
<pandas.core.groupby.SeriesGroupBy object at 0xa7af08c>
Then we can take the difference of each group:
>>> gs.diff()
0 NaT
1 01:00:00
2 NaT
3 NaT
4 NaT
5 NaT
6 NaT
7 01:00:00
8 01:30:00
9 NaT
dtype: timedelta64[ns]
NaT, Not-a-Time, is the temporal equivalent of NaN. We can fill these with 0:
>>> gs.diff().fillna(0)
0 00:00:00
1 01:00:00
2 00:00:00
3 00:00:00
4 00:00:00
5 00:00:00
6 00:00:00
7 01:00:00
8 01:30:00
9 00:00:00
dtype: timedelta64[ns]
And since you want things to be measured in hours, we can divide by a timedelta of 1 hour:
>>> gs.diff().fillna(0) / pd.datetools.timedelta(hours=1)
0 0.0
1 1.0
2 0.0
3 0.0
4 0.0
5 0.0
6 0.0
7 1.0
8 1.5
9 0.0
dtype: float64
Assign this to the frame:
>>> df["TIME_DIFF"] = gs.diff().fillna(0) / pd.datetools.timedelta(hours=1)
And we're done:
>>> df
# USER_ID CONTRACT_REF SUBMISSION_DATE TIME_DIFF
0 1 1 A 2014-06-20 01:00:00 0.0
1 2 1 A 2014-06-20 02:00:00 1.0
2 3 1 B 2014-06-20 03:00:00 0.0
3 4 4 A 2014-06-20 04:00:00 0.0
4 5 5 A 2014-06-20 05:00:00 0.0
5 6 5 B 2014-06-20 06:00:00 0.0
6 7 7 A 2014-06-20 07:00:00 0.0
7 8 7 A 2014-06-20 08:00:00 1.0
8 9 7 A 2014-06-20 09:30:00 1.5
9 10 7 B 2014-06-20 10:00:00 0.0
[10 rows x 5 columns]
这篇关于如何计算数据帧中一组分组行中先前的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
