Home Creating new DataFrame with regular datetime from a df with non regular datetime

Questions

Creating new DataFrame with regular datetime from a df with non regular datetime

March 2, 2022

I have a DataFrame like this

df
           dateEntry              dataReceived  
0   2021-12-22 15:00:34.359293        0 
1   2021-12-22 15:00:56.052554        1 
2   2021-12-22 15:02:12.408687        0 
3   2021-12-22 15:02:18.764644        1 
4   2021-12-22 15:03:26.959721        0 
5   2021-12-22 15:03:38.039307        1 
6   2021-12-22 15:05:59.347346        0 
7   2021-12-22 15:06:22.955319        1

dateEntry is type datetime64[ns].

dataReceived is always an alternation between 0 and 1. For exemple at the first row, it mean that the person is not moving (label 0) till the next row, so the person is not moving for 56-34 =22 secondes

I want to create an other dataframe but with regular timestep for exemple it start from 2021_12_22 15:00:40 with 15 secondes timestep.

To assign a value in the new DataFrame, I consider that the new datetime takes the value of the lower limit of the interval in which it is contained:

Desired OUTPUT

df_new

        dateEntry          dataReceived
0   2021-12-22 15:00:40         0
1   2021-12-22 15:00:55         0
2   2021-12-22 15:01:10         1
3   2021-12-22 15:01:25         1
4   2021-12-22 15:01:40         1
...
    2021-12-22 15:05:55         1
    2021-12-22 15:06:10         0

How can I get it?

>Solution :

IIUC, you need resample:

df['dateEntry'] = pd.to_datetime(df['dateEntry'])

df2 = (df.set_index('dateEntry')
         .resample('15s', origin='2021-12-22 15:00:40', closed='right')
         .ffill()
         .reset_index()
      )

output:

             dateEntry  dataReceived
0  2021-12-22 15:00:40             0
1  2021-12-22 15:00:55             0
2  2021-12-22 15:01:10             1
3  2021-12-22 15:01:25             1
4  2021-12-22 15:01:40             1
5  2021-12-22 15:01:55             1
...
20 2021-12-22 15:05:40             1
21 2021-12-22 15:05:55             1
22 2021-12-22 15:06:10             0
23 2021-12-22 15:06:25             1