CODEWITHSUNDEEP
pythonpandasdataframe
Adler Müller
Adler Müller

Reputation: 248

Reset timestamp in dataframe and count up the time

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  |

Upvotes: 1

Views: 229

Answers (1)

jezrael
jezrael

Reputation: 862471

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

Upvotes: 1

Related Questions