CODEWITHSUNDEEP
pythonpandaspython-datetime
reddi hari
reddi hari

Reputation: 193

How to number timestamps that comes under particular duration of time in dataframe

If we can divide time of a day from 00:00:00 hrs to 23:59:00 into 15 min blocks we will have 96 blocks. we can number them from 0 to 95.
I want to add a "timeblock" column to the dataframe, where i can number each row with a timeblock number that time stamp sits in as shown below. 

    tagdatetime            tagvalue     timeblock
    2020-01-01 00:00:00    47.874423    0
    2020-01-01 00:01:00    14.913561    0
    2020-01-01 00:02:00    56.368034    0
    2020-01-01 00:03:00    16.555687    0
    2020-01-01 00:04:00    42.138176    0
    ... ... ...
    2020-01-01 00:13:00    47.874423    0
    2020-01-01 00:14:00    14.913561    0
    2020-01-01 00:15:00    56.368034    0
    2020-01-01 00:16:00    16.555687    1
    2020-01-01 00:17:00    42.138176    1
    ... ... ...
    2020-01-01 23:55:00    18.550685    95
    2020-01-01 23:56:00    51.219147    95
    2020-01-01 23:57:00    15.098951    95
    2020-01-01 23:58:00    37.863191    95
    2020-01-01 23:59:00    51.380950    95

Upvotes: 0

Views: 29

Answers (1)

r-beginners
r-beginners

Reputation: 35115

I think there's a better way to do it, but I think it's possible below.

 import pandas as pd
 import numpy as np

 tindex = pd.date_range('2020-01-01 00:00:00', '2020-01-01 23:59:00', freq='min')
 tvalue = np.random.randint(1,50, (1440,))
 df = pd.DataFrame({'tagdatetime':tindex, 'tagvalue':tvalue})

 min15 = pd.date_range('2020-01-01 00:00:00', '2020-01-01 23:59:00', freq='15min')
 tblock = np.arange(96)
 df2 = pd.DataFrame({'min15':min15, 'timeblock':tblock})

 df3 = pd.merge(df, df2, left_on='tagdatetime', right_on='min15', how='outer')
 df3.ffill(axis=0, inplace=True)
 df3 = df3.drop('min15', axis=1)

 df3.iloc[10:20,]

 tagdatetime    tagvalue    timeblock
 10 2020-01-01 00:10:00 20  0.0
 11 2020-01-01 00:11:00 25  0.0
 12 2020-01-01 00:12:00 42  0.0
 13 2020-01-01 00:13:00 45  0.0
 14 2020-01-01 00:14:00 11  0.0
 15 2020-01-01 00:15:00 15  1.0
 16 2020-01-01 00:16:00 38  1.0
 17 2020-01-01 00:17:00 23  1.0
 18 2020-01-01 00:18:00 5   1.0
 19 2020-01-01 00:19:00 32  1.0

Upvotes: 1

Related Questions