Stacking daily data dataframe to get hourly output

Question

I have a dataframe which looks as:

df.ix[1:3]

          Val   endDay        startDay                             
1        2.20   1996-04-01    1996-03-31
2        5.15   1997-04-05    1997-04-01

The startDay however starts at hour 9 am and continues until 8 am on end day.

I am looking for the following output:

startDay     Hour   Val
1996-03-31   9     2.20 
1996-03-31   10    2.20 
                     ........
1996-03-31   24    2.20 
1996-04-01   1     2.20 
                     ........
1996-04-01   7     2.20 
1996-04-01   8     2.20
1997-04-01   9     5.15 
1997-04-01   10    5.15 
                     ........
1997-04-01   24    5.15 
1997-04-05   1     5.15 
                     ........
1997-04-05   7     5.15 
1997-04-05   8     5.15

I just used ..... to represent the continuation of hours 11 through 23 and 2 though 6. I am not sure how to do this stacking pythonically.

I am not sure how I can use merge because Hour column needs to be defined, not sure which option in merge will include that? — Zanam, Apr 18 '19 at 17:31

BENY · Accepted Answer · 2019-04-18T17:40:41.103

Just doing with unnesting after create list of datetime

df['day']=[pd.date_range(x+' 09:00:00',y+' 08:00:00',freq='H') for x , y in zip(df.startDay,df.endDay)]
yourdf=unnesting(df,['day']).drop_duplicates('day')
yourdf
Out[909]: 
                  day   Val      endDay    startDay
1 1996-03-31 09:00:00  2.20  1996-04-01  1996-03-31
1 1996-03-31 10:00:00  2.20  1996-04-01  1996-03-31
1 1996-03-31 11:00:00  2.20  1996-04-01  1996-03-31
1 1996-03-31 12:00:00  2.20  1996-04-01  1996-03-31
...

Notice here I did not split the two columns with date and hour , that can be done with yourdf.day.dt.hour; yourdf.dt.date

def unnesting(df, explode):
    idx = df.index.repeat(df[explode[0]].str.len())
    df1 = pd.concat([
        pd.DataFrame({x: np.concatenate(df[x].values)}) for x in explode], axis=1)
    df1.index = idx

    return df1.join(df.drop(explode, 1), how='left')

@Zanam aah, that is one of function write by me no worry right now you know it :-) — BENY, Apr 18 '19 at 17:41

Stacking daily data dataframe to get hourly output

1 Answers1