Reset timestamp in dataframe and count up the time

user_15870842 0
Viewed 93

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  |
pandas dataframe python
1 Answers

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()
user_29010020
Related