Label every nth seconds of a pandas dataframe by an incremental value

Edward Steven 0
Viewed 25

I have a dataframe include ID and time

ID time
1 2022-07-31 23:59:53
2 2022-07-31 23:59:54
3 2022-07-31 23:59:55
4 2022-07-31 00:00:00
5 2022-07-31 00:00:01
6 2022-07-31 00:00:02
7 2022-07-31 00:00:07
8 2022-07-31 00:00:10
9 2022-07-31 00:00:13

So, I need to label every 10 seconds based on the column "Time" by incremental value AS BELOW.

ID time DESIRED_VALUE
1 2022-07-31 23:59:53 A
2 2022-07-31 23:59:54 A
3 2022-07-31 23:59:55 A
4 2022-08-01 00:00:00 B
5 2022-08-01 00:00:01 B
6 2022-08-01 00:00:02 B
7 2022-08-01 00:00:07 B
8 2022-08-01 00:00:10 C
9 2022-08-01 00:00:13 C
time time-series dataframe pandas python
1 Answers

There are probably faster ways to do this but for me, the most intuitive method is to floor time into 10s-frequency column, encode the datetime into numbers and substitute the numbers into letters using map().

df['DESIRED_VALUE'] = (
    pd.Series(
        pd.to_datetime(df['time']) # convert the column to datetime (if it's not already)
        .dt.floor('10s')           # floor divide datetime with frequency of 10sec
        .factorize()[0],           # encode datetime into numbers
        index=df.index)            # construct a Series
    .map(dict(enumerate('ABC'))    # map letters to encoded numbers
        )
)

res

New England cottontail0
Related