Reset timestamp in dataframe and count up the time-CodePudding

So my timestamp in dataframe looks like something like this:

    In [18]: df['time'].head(1)
    Out[18]: 2021-10-05 12:00:00.000000 00:00

    In [19]: df['time'].tail(1)
    Out[19]: 2021-10-07 12:00:00.000000 00:00

I want a new column (time_new). There the first timestamp has to be resetted to 0 so starting point is 0. then it only counts up the hours, seconds and milliseconds. So that at the end there are 48 hours in this case. The following table for illustration.

| index     | time                              | time_new      |
|-------    |---------------------------------- |----------     |
| 0         | 2021-10-05 12:00:00.000000 00:00  | 00:00:00.000  |
| 1         | 2021-10-05 13:00:00.000000 00:00  | 01:00:00.000  |
| 2         | 2021-10-05 13:00:00.001000 00:00  | 01:00:00.001  |
| 3         | 2021-10-06 02:00:00.000000 00:00  | 14:00:00.000  |
| 4         | 2021-10-07 12:00:00.000000 00:00  | 48:00:00.000  |

CodePudding user response：

For timedeltas subtract minimal value of column:

df['time_new'] = df['time'].sub(df['time'].min())

Or first value of column:

df['time_new'] = df['time'].sub(df['time'].iat[0])

If format is important use custom function:

def format_timedelta(x):
    ts = x.total_seconds()

    hours, remainder = divmod(ts, 3600)
    minutes, seconds = divmod(remainder, 60)
    return ('{:02d}:{:02d}:{:.3f}').format(int(hours), int(minutes), seconds) 

df['time_new'] = df['time'].sub(df['time'].min()).apply(format_timedelta)
print (df)
                              time     time_new
0        2021-10-05 12:00:00 00:00  00:00:0.000
1        2021-10-05 13:00:00 00:00  01:00:0.000
2 2021-10-05 13:00:00.001000 00:00  01:00:0.001
3        2021-10-06 02:00:00 00:00  14:00:0.000
4        2021-10-07 12:00:00 00:00  48:00:0.000

For mean:

avg = df.loc[df['time_new'] <= pd.Timedelta('01:00:00'), 'vibration'].mean()