pandas: subtract row of column from another next row of another column-CodePudding

I have a datetime columns on that basis I calculate min_time and max_time. so from current min_time of the row want to subtract from previous row max_time and want to save into another column. How to do that?

data = pd.DataFrame()
data['datetime'] = 18-6-22 8:22:22, 18-6-22 8:22:23, 18-6-22 8:22:24, 18-6-22 8:22:25, 18-6-22 8:22:26, 18-6-22 11:22:27
data['min_time'] = 18-6-22 8:22:22, 18-6-22 8:22:23, 18-6-22 8:22:24, 18-6-22 8:22:25, 18-6-22 8:22:26, 18-6-22 11:22:27
data['max_time'] = 18-6-22 8:22:22, 18-6-22 8:22:23, 18-6-22 8:22:24, 18-6-22 8:22:25, 18-6-22 8:22:26, 18-6-22 11:22:27
data['t_diff_time'] = 0, 0, 0, 0, 0, 0 Day 3:00:1

Is there any function to do this?

expected output :

CodePudding user response：

You can use a combination of sub() and shift(). Of course, the first value will be null because for the first min there is no previous max. Try with:

df['diff'] = df['min_time'].sub(df['max_time'].shift(1))

Or, equally in result:

df['diff'] = df['min_time'] - df['max_time'].shift(1)

Returning:

           datetime            min_time            max_time            diff
0   18-6-22 8:22:22 2022-06-18 08:22:22 2022-06-18 08:22:22             NaT
1   18-6-22 8:22:23 2022-06-18 08:22:23 2022-06-18 08:22:23 0 days 00:00:01
2   18-6-22 8:22:24 2022-06-18 08:22:24 2022-06-18 08:22:24 0 days 00:00:01
3   18-6-22 8:22:25 2022-06-18 08:22:25 2022-06-18 08:22:25 0 days 00:00:01
4   18-6-22 8:22:26 2022-06-18 08:22:26 2022-06-18 08:22:26 0 days 00:00:01
5  18-6-22 11:22:27 2022-06-18 11:22:27 2022-06-18 11:22:27 0 days 03:00:01

Optionally, consider adding fillna() to exactly match your desired output:

data['diff'] = data['min_time'].sub(data['max_time'].shift(1)).fillna(pd.to_timedelta(0))

Outputs:

           datetime            min_time            max_time            diff
0   18-6-22 8:22:22 2022-06-18 08:22:22 2022-06-18 08:22:22 0 days 00:00:00
1   18-6-22 8:22:23 2022-06-18 08:22:23 2022-06-18 08:22:23 0 days 00:00:01
2   18-6-22 8:22:24 2022-06-18 08:22:24 2022-06-18 08:22:24 0 days 00:00:01
3   18-6-22 8:22:25 2022-06-18 08:22:25 2022-06-18 08:22:25 0 days 00:00:01
4   18-6-22 8:22:26 2022-06-18 08:22:26 2022-06-18 08:22:26 0 days 00:00:01
5  18-6-22 11:22:27 2022-06-18 11:22:27 2022-06-18 11:22:27 0 days 03:00:01

CodePudding user response：

You can use shift() function. You can do something like this:

shifted_max = data["max_time"].shift(1)
data["t_diff_time"] = data["min_time"] - shifted_max