Add column from weekly multiindex to another daily df

I have a multiindex with Name and Date as indexes on a weekly timeframe. I'm attempting to change it to daily, ffill the missing values (as the dates don't overlap) and add it as a column to another df. The 1W df with the 'Value' column to be taken is:

df_wkly = df.groupby([pd.Grouper(level='Name'), pd.Grouper(level='Date', label='left', closed='left', freq='1D')]).sum()

                       Value
Name   Date
AA     2016-11-06  22.024915
       2016-11-13  25.453228
       2016-11-20  29.500000
BB     2016-10-30  29.056394
       2016-11-06  28.909892
       2016-11-13  27.444863

The frame I'm adding to has a timeframe of 1D.

                       Daily
Name   Date
AA     2016-11-01  22.024915
       2016-11-02  22.592978
       2016-11-03  22.881993
       2016-11-04  23.739071
       2016-11-07  25.453228
BB     2016-10-24  29.056394
       2016-10-25  29.105229
       2016-10-26  28.616886
       2016-10-27  28.568052
       2016-10-28  28.714554

I could do this without problems using resample but in my use, resample was very slow. I also do not want to add the dates from the 1W frame (first one) to the 1D (second one).

df['Value'] = df_wkly['Value'].groupby('Name').resample('1D', level='Date').sum().replace(0, np.nan).ffill()

Expected output:

                       Daily       Value
Name   Date
AA     2016-11-01  22.024915   22.024915
       2016-11-02  22.592978   22.024915
       2016-11-03  22.881993   22.024915
       2016-11-04  23.739071   22.024915
       2016-11-07  25.453228   25.453228
BB     2016-10-24  29.056394   29.056394
       2016-10-25  29.105229   29.056394
       2016-10-26  28.616886   29.056394
       2016-10-27  28.568052   29.056394
       2016-10-28  28.714554   29.056394

1 answer

  • answered 2019-12-15 03:58 misantroop

    Slightly different solution with concat.

    df['Value'] = pd.concat([df, df_wkly], sort=False).sort_index()['Value'].bfill()