Pandas - Get the value from the previous datetime minute for specific rows

user_15693450 0
Viewed 89

Get the value from the previous datetime minute per user

Original Dataframe

I have a Dataframe structured like this:

Original Dataframe

    User    Datetime    Hits
0   A   2021-03-10 15:25:26 10
1   A   2021-03-10 15:25:52 10
2   A   2021-03-10 15:26:09 12
3   A   2021-03-10 15:26:12 12
4   B   2021-03-10 15:25:32 6
5   A   2021-03-10 15:27:39 15

Goal

My goal is to obtain the difference (Delta) per User between the current 'Hits' value and the value from last minute.
For every specific datetime minute, the 'Hits' value per user is the same, as this value is the result from a value_counts() operation for this specific minute.
Hence, multiple similar 'Hits' values per user can be present per minute.

Methods tried

I have succeeded using .apply in combination with a rather complex function, but the problem is that this method is too slow for the size of the original dataframe.
More specifically I am looking for a faster method, ideally without custom functions.

Target Dataframe

My target Dataframe will look like this:

Target Dataframe

User    Datetime    Hits    Delta
0   A   2021-03-10 15:25:26 10  <NA>
1   A   2021-03-10 15:25:52 10  <NA>
2   A   2021-03-10 15:26:09 12  2
3   A   2021-03-10 15:26:12 12  2
4   B   2021-03-10 15:25:32 6   <NA>
5   A   2021-03-10 15:27:39 15  3

Code for Dataframe

d = {'User': ['A', 'A', 'A', 'A', 'B','A'], 
     'Datetime': ['2021-03-10 15:25:26', '2021-03-10 15:25:52', '2021-03-10 15:26:09', '2021-03-10 15:26:12', '2021-03-10 15:25:32', '2021-03-10 15:27:39'], 
     'Hits': [10,10,12,12,6,15],
    'Delta': pd.NA}
df = pd.DataFrame(data=d)
df
pandas-groupby pandas datetime python
1 Answers

Let's do this in a few small steps.

First we create a copy of your dataframe, called df1 - we then floor the Datetime column to remove any seconds from the column so we can measure the changes in minutes.

We then apply a series of conditional checks to apply your delta calculation.

import pandas as pd

df1 = df.copy() 
## might be better to only copy the columns you're interested in for memory.
df1['Datetime'] = pd.to_datetime(df1['Datetime'])
df1['Datetime'] = df1['Datetime'].dt.floor('T')

con = df1.sort_values(['User','Datetime'])\
         .groupby('User')['Datetime'].diff() >= pd.Timedelta('1 min')


df.loc[con,'delta'] = df.groupby('User')['Hits'].diff()
df['delta'] = df.groupby([df1['User'], df1['Datetime']])['delta'].ffill()

print(df)
  User             Datetime  Hits  delta
0    A  2021-03-10-15:25:26    10    NaN
1    A  2021-03-10-15:25:52    10    NaN
2    A  2021-03-10-15:26:09    12    2.0
3    A  2021-03-10-15:26:12    12    2.0
4    B  2021-03-10-15:25:32     6    NaN
5    A  2021-03-10-15:27:39    15    3.0
user_93751020
Related