I have a long dataframe with multiple timeseries which are non overlapping.
import numpy as np import pandas as pd df = pd.DataFrame({'id':[1,1,1,1,1,2,2,2,2,2,2], 't':[0,1,2,3,4,2,3,4,5,6,7], 'price':[10,10.2,10.8,10.1,10.4,142.1,142.8,143.6,142.8,141.4,140.7]})
The df looks like this
Out[65]: id t price 0 1 0 10.0 1 1 1 10.2 2 1 2 10.8 3 1 3 10.1 4 1 4 10.4 5 2 2 142.1 6 2 3 142.8 7 2 4 143.6 8 2 5 142.8 9 2 6 141.4 10 2 7 140.7
For the time series with id 1, the missing timestamps are 5,6 and 7 and the time series #2 misses timestamps 0 and 1.
I would like to fill the missing dates for all the time series in the dataframe so all of them have all the dates filled with nan:
df_target = pd.DataFrame({'id':[1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2], 't':[0,1,2,3,4,5,6,7,0,1,2,3,4,5,6,7], 'price':[10,10.2,10.8,10.1,10.4,np.nan,np.nan,np.nan,np.nan,np.nan,142.1,142.8,143.6,142.8,141.4,140.7]}) Out[68]: id t price 0 1 0 10.0 1 1 1 10.2 2 1 2 10.8 3 1 3 10.1 4 1 4 10.4 5 1 5 NaN 6 1 6 NaN 7 1 7 NaN 8 2 0 NaN 9 2 1 NaN 10 2 2 142.1 11 2 3 142.8 12 2 4 143.6 13 2 5 142.8 14 2 6 141.4 15 2 7 140.7
The objective is to be able to then reshape this dataframe to a 3d array. Is there a simple way to fill missing dates for each time series? Thanks
Advertisement
Answer
Use Series.unstack
with DataFrame.stack
:
df1 = (df.set_index(['id','t'])['price'] .unstack() .stack(dropna=False) .reset_index(name='price')) print (df1) id t price 0 1 0 10.0 1 1 1 10.2 2 1 2 10.8 3 1 3 10.1 4 1 4 10.4 5 1 5 NaN 6 1 6 NaN 7 1 7 NaN 8 2 0 NaN 9 2 1 NaN 10 2 2 142.1 11 2 3 142.8 12 2 4 143.6 13 2 5 142.8 14 2 6 141.4 15 2 7 140.7