CODEWITHSUNDEEP
python-3.xpandas
stats_residue
stats_residue

Reputation: 175

How to merge timestamps that are only few seconds apart [Pandas]

I have this dataframe with shape 22341x3: 

    tID   DateTime
0   1     2020-04-04 10:15:40
1   2     2020-04-04 10:15:56
2   2     2020-04-04 11:07:11
3   3     2020-04-04 11:08:14
4   3     2020-04-04 11:18:46
5   4     2020-04-04 11:23:56
6   5     2020-04-04 11:24:14
7   6     2020-04-04 11:29:12
8   7     2020-04-04 11:29:23
9   8     2020-04-04 11:34:23

Now I have to create a column called merged_timestamp that merges all the timestamps that are only a few seconds apart and give them a new number: mtID

So for example: if we consider 2020-04-04 10:15:40 as a reference, the timestamps with few seconds apart can be from 40 seconds until 44 seconds. They can have hours and minutes with a big gap as compared to the reference, but their seconds should be only few seconds apart in order to get merged.

Any help would be appreciated.

EDIT: I tried doing dfd.resample('5s')[0:5] where dfd is my dataframe. It gives me this error TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'RangeIndex'

Upvotes: 1

Views: 370

Answers (1)

matman9
matman9

Reputation: 490

resample works on the index, so make the datetime your index;

df.index = pd.to_datetime(df['DateTime'])

then you can resample with;

df.resample('5s').count()

or some other aggregation, not sure what you are trying to do. And then you could drop the rows that your not interested in.

Upvotes: 1

Related Questions