I asked (and answered) a question here Pandas ffill resampled data grouped by column where I wanted to know how to ffill a date range for each unique entry for a column (my assets column).
My solution requires that the asset "id" is a column. However, the data makes more sense to me as a multiindex. Furthermore I would like more fields in the multiindex. Is the only way of filling forward to drop the non-date fields from the multiiindex before ffilling?
A modified version of my example (to work on a df with multiindex) here:
from datetime import datetime, timedelta
import pytz
some_time = datetime(2018,4,2,20,20,42)
start_date = datetime(some_time.year,some_time.month,some_time.day).astimezone(pytz.timezone('Europe/London'))
end_date = start_date + timedelta(days=1)
start_date = start_date + timedelta(hours=some_time.hour,minutes=(0 if some_time.minute < 30 else 30 ))
df = pd.DataFrame(['A','B'],columns=['asset_id'])
df2=df.copy()
df['datetime'] = start_date
df2['datetime'] = end_date
df['some_property']=0
df.loc[df['asset_id']=='B','some_property']=2
df = df.append(df2).set_index(['asset_id','datetime'])
With what is arguably my crazy solution here:
df = df.reset_index()
df = df.set_index('datetime').groupby('asset_id').resample('30T').ffill().drop('asset_id',axis=1)
df = df.reset_index().set_index(['asset_id','datetime'])
Can I avoid all that re-indexing?
