Dataframe: I have a datetime index, but it is missing the 17:00 each day. How would I add additional yyyy-mm-dd 17:00 row to every day?
Say if I have:
price
2017-01-01 16:55 1.2
2017-01-01 17:05 2.3
2017-01-01 17:10 3.4
.
.
.
2019-01-01 16:55 23
2019-01-01 17:05 29
2019-01-01 17:10 20
I want to add rows at 17:00 with NaNs so that I have
2017-01-01 16:55 1.2
2017-01-01 17:00 NaN
2017-01-01 17:05 2.3
2017-01-01 17:10 3.4
Use reindex:
from io import StringIO
# sample data
s = """date_time,price
2017-01-01 16:55,1.2
2017-01-01 17:05,2.3
2017-01-01 17:10,3.4"""
df = pd.read_csv(StringIO(s))
df['date_time'] = pd.to_datetime(df['date_time'])
df = df.set_index('date_time')
# create a date range with the index min and max and set to whatever freq you would like
new_idx = pd.date_range(df.index.min(), df.index.max(), freq='5T')
df.reindex(new_idx)
price
2017-01-01 16:55:00 1.2
2017-01-01 17:00:00 NaN
2017-01-01 17:05:00 2.3
2017-01-01 17:10:00 3.4
Since you have different date , you will need groupby here, and I am using resample
df.groupby(df.index.date).apply(lambda x : x.resample('5 min').mean()).reset_index(level=0,drop=True)
Out[13]:
price
date_time
2017-01-01 16:55:00 1.2
2017-01-01 17:00:00 NaN
2017-01-01 17:05:00 2.3
2017-01-01 17:10:00 3.4
