Elegant resample for groups in Pandas

Viewed 4918

For a given pandas data frame called full_df which looks like

  index   id   timestamp    data  
 ------- ---- ------------ ------ 
      1    1   2017-01-01   10.0  
      2    1   2017-02-01   11.0  
      3    1   2017-04-01   13.0  
      4    2   2017-02-01    1.0  
      5    2   2017-03-01    2.0  
      6    2   2017-05-01    9.0  

The start and end dates (and the time delta between start and end) are varying.

But I need a id wise resampled version (added rows marked with *)

  index   id   timestamp    data       
 ------- ---- ------------ ------ ---- 
      1    1   2017-01-01   10.0       
      2    1   2017-02-01   11.0       
      3    1   2017-03-01    NaN   *   
      4    1   2017-04-01   13.0       
      5    2   2017-02-01    1.0       
      6    2   2017-03-01    2.0       
      7    2   2017-04-01    NaN   *   
      8    2   2017-05-01    9.0  

Because the dataset is very large I was wondering if there is more efficient way of doing so than

  1. Do full_df.groupby('id')
  2. Do for each group df

    df.index = pd.DatetimeIndex(df['timestamp'])
    all_days = pd.date_range(df.index.min(), df.index.max(), freq='MS')
    df = df.reindex(all_days)
    
  3. Combine all groups again with a new index

That's time consuming and not very elegant. Any ideas?

2 Answers
Related