I am trying to reindex my dataframe that has two datetime variables which define the duration. I want my dataframe to be indexed by one datetime variable incrementing by 1 minute with missing values/strings where there is no observation.
My data looks something like this
DMS = pandas.DataFrame({
'ID':[1,1,2,3],
'SentTime':['2016-01-22 14:47:05.486877', '2016-01-22 14:53:03.234377', '2016-01-22 14:45:09.434877','2016-01-22 14:48:05.486877'],
'EndTime':['2016-01-22 14:50:10.454347', '2016-01-22 14:57:45.456877', '2016-01-22 14:56:05.456877','2016-01-22 14:52:05.486877'],
'BinaryVariable1':[0, 1, 0, 0],
'BinaryVariable2':[0, 0, 0, 1],
'StringVariable':['ABC','DE','FG','XYZ']
})
I want this dataframe to look like
ID Time BinaryVariable1 BinaryVariable2 StringVariable
1 1/22/2016 14:45 NaN NaN NaN
1 1/22/2016 14:46 NaN NaN NaN
1 1/22/2016 14:47 0 0 ABC
1 1/22/2016 14:48 0 0 ABC
1 1/22/2016 14:49 0 0 ABC
1 1/22/2016 14:50 0 0 ABC
1 1/22/2016 14:51 NaN NaN NaN
1 1/22/2016 14:52 NaN NaN NaN
1 1/22/2016 14:53 1 0 DE
1 1/22/2016 14:54 1 0 DE
1 1/22/2016 14:55 1 0 DE
1 1/22/2016 14:56 1 0 DE
1 1/22/2016 14:57 1 0 DE
1 1/22/2016 14:58 NaN NaN NaN
1 1/22/2016 14:59 NaN NaN NaN
1 1/22/2016 15:00 NaN NaN NaN
2 1/22/2016 14:45 0 0 FG
2 1/22/2016 14:46 0 0 FG
2 1/22/2016 14:47 0 0 FG
2 1/22/2016 14:48 0 0 FG
2 1/22/2016 14:49 0 0 FG
2 1/22/2016 14:50 0 0 FG
2 1/22/2016 14:51 0 0 FG
2 1/22/2016 14:52 0 0 FG
2 1/22/2016 14:53 0 0 FG
2 1/22/2016 14:54 0 0 FG
2 1/22/2016 14:55 0 0 FG
2 1/22/2016 14:56 0 0 FG
2 1/22/2016 14:57 NaN NaN NaN
2 1/22/2016 14:58 NaN NaN NaN
2 1/22/2016 14:59 NaN NaN NaN
2 1/22/2016 15:00 NaN NaN NaN
3 1/22/2016 14:45 NaN NaN NaN
3 1/22/2016 14:46 NaN NaN NaN
3 1/22/2016 14:47 NaN NaN NaN
3 1/22/2016 14:48 0 1 XYZ
3 1/22/2016 14:49 0 1 XYZ
3 1/22/2016 14:50 0 1 XYZ
3 1/22/2016 14:51 0 1 XYZ
3 1/22/2016 14:52 0 1 XYZ
3 1/22/2016 14:53 NaN NaN NaN
3 1/22/2016 14:54 NaN NaN NaN
3 1/22/2016 14:55 NaN NaN NaN
3 1/22/2016 14:56 NaN NaN NaN
3 1/22/2016 14:57 NaN NaN NaN
3 1/22/2016 14:58 NaN NaN NaN
3 1/22/2016 14:59 NaN NaN NaN
3 1/22/2016 15:00 NaN NaN NaN
Any ideas? I can create index using pandas.date_range but I am having a challenge incorporating EndTime which is varying across observations.
You can always process it row by row: 1),Create an empty DataFrame res ; 2), pick one row of DMS and fill relevant row in res with row value; 3), loop until all rows are processed.
Here I solve it with some (maybe unnecessary) tricks. By taking advantage of apply method, you don't have to iterate DMS yourself:
DMS['SentTime'] = pd.to_datetime(DMS['SentTime']).dt.floor('60S')
DMS['EndTime'] = pd.to_datetime(DMS['EndTime']).dt.floor('60S')
dt_idx = pd.date_range(start=DMS['SentTime'].min(),end=DMS['EndTime'].max(),freq='60S')
# may you need '2016-01-22 15:00:00' as end insteand of DMS['EndTime'].max()
res = pd.DataFrame(index=pd.MultiIndex.from_product([DMS['ID'].unique(),dt_idx],names=['ID','TIME']),columns=DMS.columns)
def p(x):
_s = pd.IndexSlice[(x['ID'],pd.DatetimeIndex(start=x['SentTime'],end=x['EndTime'],freq='60S')),:]
res.loc[_s] = x.values
return '**'
DMS.apply(p,axis=1)
print(res)