Pandas .resample or .asfreq to fill in missing datetime entries

user_9655107 0
Viewed 1717

I have a data frame that looks like this:

import pandas as pd
d = {'TransactionId' : pd.Series([633025, 634560],index = ['2018-01-07 22:30:00', '2018-01-08 19:00:00']),
 'Value' : pd.Series([677.06, 677.44], index = ['2018-01-07 22:30:00', '2018-01-08 19:00:00'])}
df = pd.DataFrame(d)
df.head()

Once TransactionId 633025 finishes, values cease recording until the next Transaction begins. I would like to add in a datetime index value every 15 minutes between the two occurrences, with TransactionId of 'NaN', and forward filling the the Value column. I have tried using .resample and .asfreq without any luck; using either with a period of '15min' doesn't modify the dataframe accordingly. How can I do this?

pandas python-3.x datetime
1 Answers

First, make your index a datetime and then you can just use .resample and then .ffill

import pandas as pd

df.index = pd.to_datetime(df.index)
df = df.resample('15min').asfreq()
df['Value'] = df['Value'].ffill()

Output:

                     TransactionId   Value
2018-01-07 22:30:00       633025.0  677.06
2018-01-07 22:45:00            NaN  677.06
2018-01-07 23:00:00            NaN  677.06
2018-01-07 23:15:00            NaN  677.06
2018-01-07 23:30:00            NaN  677.06 
...
2018-01-08 18:00:00            NaN  677.06
2018-01-08 18:15:00            NaN  677.06
2018-01-08 18:30:00            NaN  677.06
2018-01-08 18:45:00            NaN  677.06
2018-01-08 19:00:00       634560.0  677.44
user_43333590
Related