Insert 15 min datetime in hourly intervals

user_11945555 0
Viewed 576

I have a dataframe with 3 columns of which the first column is a datetime.

It looks like this

Datetime            Level1   Level2
2016-02-24 01:00    12       15
2016-02-24 02:00    14       13
2016-02-24 03:00    8        12

Now I would like to add 15 min interval values between the hourly values. However, Level1 and Level2 gets the same values as its previous hour. It should look like this:

Datetime            Level1   Level2
2016-02-24 01:00    12       15
2016-02-24 01:15    12       15
2016-02-24 01:30    12       15
2016-02-24 01:45    12       15
2016-02-24 02:00    14       13
2016-02-24 02:15    14       13
2016-02-24 02:30    14       13
2016-02-24 02:45    14       13
2016-02-24 03:00    8        12
2016-02-24 03:15    8        12
2016-02-24 03:30    8        12
2016-02-24 03:45    8        12

I can't think of a way how to do this properly.

pandas datetime python
2 Answers

Set the datetime as index (after converting to datetime), and use the asfreq method, with a forward fill, to fill the null values with previous values :

#thanks to @a_guest for the cleaned sample data

df = pd.DataFrame(
    data=[['2016-02-24 01:00', 12, 15],
          ['2016-02-24 02:00', 14, 13],
          ['2016-02-24 03:00',  8, 12]],
    columns=['Datetime', 'Level1', 'Level2']
)
df['Datetime'] = pd.to_datetime(df['Datetime'])

df = df.set_index('Datetime')

df.asfreq('15min',method='pad')

                 Level1 Level2
Datetime        
2016-02-24 01:00:00 12  15
2016-02-24 01:15:00 12  15
2016-02-24 01:30:00 12  15
2016-02-24 01:45:00 12  15
2016-02-24 02:00:00 14  13
2016-02-24 02:15:00 14  13
2016-02-24 02:30:00 14  13
2016-02-24 02:45:00 14  13
2016-02-24 03:00:00 8   12

NB : your question says 15 minutes intervals between the times, and your latest time is 3pm, not 4pm, as shared in your data.

user_71757130

You can create copies of the original data frame where you increment the Datetime column by 15 minutes each and then use pd.concat followed by sort_values to get the final result:

from datetime import timedelta
import pandas as pd

df = pd.DataFrame(
    data=[['2016-02-24 01:00', 12, 15],
          ['2016-02-24 02:00', 14, 13],
          ['2016-02-24 03:00',  8, 12]],
    columns=['Datetime', 'Level1', 'Level2']
)
df['Datetime'] = pd.to_datetime(df['Datetime'])

intervals = [df]
while len(intervals) < 4:
    new = intervals[-1].copy()
    new['Datetime'] += timedelta(minutes=15)
    intervals.append(new)

result = pd.concat(intervals).sort_values(by='Datetime')
user_37672390
Related