Python. Pandas. Get the time (difference) a value takes to get to another value

ksu1980 0
Viewed 27

Have the below dataset, and wanting to get the time (timediff) between values 2 and ~248. Basically, how long did it take to reach ~248 from value 2.

I have the below that gets the time difference, but now sure how to incorporate the condition => 2 & <248, so only the time difference between values 2 - 248 are calculated (i.e. 1 min is the result). The second part is to show which 'No.' is shown at the value ~248, in the dataset it's 2 (247.9400024). Value 247.9400024 is the max value in this dataset.

df.TimeStamp = pd.to_datetime(df.TimeStamp)
df['Diff] = df.groupby(['Value', 'No.'])['TimeStamp'].transform(lambda x: x.max() - x.min())

TimeStamp           Value         No.
18/08/2022 11:16:24 2             1
18/08/2022 11:17:07 1.940000057   1
18/08/2022 11:17:15 21.87999916   1
18/08/2022 11:17:20 20.62999916   2
18/08/2022 11:17:21 25.44000053   2
18/08/2022 11:17:22 142.9400024   2
18/08/2022 11:17:23 207.8099976   2
18/08/2022 11:17:24 247.9400024   2
18/08/2022 11:21:24 2.880000114   5
18/08/2022 11:21:25 7.940000057   5
18/08/2022 11:21:26 39.56000137   5
18/08/2022 11:21:27 211.1900024   5
18/08/2022 11:21:55 15.5          5
18/08/2022 11:21:56 0.189999998   5
18/08/2022 11:22:01 1.379999995   5
18/08/2022 11:22:02 8.130000114   5
18/08/2022 11:22:04 1.809999943   5
18/08/2022 11:22:19 2.380000114   5
18/08/2022 11:22:23 9.31000042    5
18/08/2022 11:22:24 5.25          5
18/08/2022 11:22:27 6.940000057   5
18/08/2022 11:22:28 16.69000053   2
18/08/2022 11:22:29 89.05999756   2
18/08/2022 11:22:31 203.1900024   2
18/08/2022 11:22:32 245.8099976   2
18/08/2022 11:23:06 196.5599976   5
18/08/2022 11:23:07 2.380000114   5
18/08/2022 11:23:08 22.05999947   5
18/08/2022 11:23:09 183.8099976   5
18/08/2022 11:24:28 246.1900024   5
18/08/2022 11:24:29 217.6900024   5
18/08/2022 11:24:30 183.3099976   5
18/08/2022 11:24:31 174.1300049   5
18/08/2022 11:24:32 158.25        5
18/08/2022 11:24:33 132.6300049   5
18/08/2022 11:24:34 69.44000244   5
18/08/2022 11:24:35 11.13000011   5
18/08/2022 11:24:36 1.75          5
18/08/2022 11:24:39 1.75          5
18/08/2022 11:25:00 1.75          5
18/08/2022 11:25:02 2.380000114   5
18/08/2022 11:25:05 7             5
18/08/2022 11:25:06 15.31000042   5
18/08/2022 11:25:10 12.88000011   2
18/08/2022 11:25:11 40.25         2
18/08/2022 11:25:12 165.5599976   2
18/08/2022 11:25:13 181           2
18/08/2022 11:25:14 238.6900024   2
18/08/2022 11:25:35 208.6900024   2

Hope it make sense.

Thanks

pandas python-3.8
1 Answers

After thinking about the problem, the solution was to find the first min and first max value in the 'Value' column and get their indexes. This essentially creates a min/max value pair. Once I have those, I append to a list, then drop those indexes from the main dataframe. The process is repeated until all the min/max values are found. The list is a bunch of dataframes, so I simply do contact.

Initially I was trying to do this in a couple of lines, my mistake. Or perhaps there is an alternative. But this approach works.

def get_time_diff(df_merge_res):

    for _ in range(100): # a limit never reached

        df_fst_min = df_merge_res.astype({'Value': float}).nsmallest(1, columns='Value')
        idx_min_mask = df_fst_min.index.tolist()
        df_merge_res = df_merge_res.drop(df_merge_res.index[0:idx_min_mask[-1]])
        df_merge_res.reset_index(drop=True, inplace=True)
        df_fst_max = df_merge_res.astype({'Value': float}).nlargest(1, columns='Value')
        idx_max_mask = df_fst_max.index.tolist()
        df_merge_res = df_merge_res.drop(df_merge_res.index[1:idx_max_mask[-1]])
        df_merge_res.reset_index(drop=True, inplace=True)
        t_df = df_merge_res[0:2]
        t_df['Diff Time'] = (t_df['TimeStamp'] - t_df['TimeStamp'].shift(1))
        max_press_times.append(t_df)
        df_merge_res = df_merge_res.iloc[2:]
        df_merge_res.reset_index(drop=True, inplace=True)

        if len(df_merge_res) == 0:
            break

The output is:

 TimeStamp              Value   No.  Diff Time
0 2022-09-08 10:12:49   152.7   2.0              NaT
1 2022-09-08 10:16:33   310.0   1.0  0 days 00:03:44
2 2022-09-09 00:44:36   160.9   5.0              NaT
3 2022-09-09 01:24:34   310.0   3.0  0 days 00:39:58
4 2022-09-09 05:53:30   220.5   1.0              NaT
5 2022-09-09 05:57:53   309.5   2.0  0 days 00:04:23
6 2022-09-09 05:59:54   263.8   1.0              NaT
7 2022-09-09 05:59:57   308.9   1.0  0 days 00:00:03
ksu19800
Related