python - Moving average based on dates and not rows

Question

    CargoTons   DateOrigin  DateDestination Origin  Destination
0   72875.0 2020-01-01  2020-01-08  Snohvit Dragon
1   77126.0 2020-01-01  2020-01-16  Cameron (Liqu.) Grain
2   0       2020-01-02          
3   67500.0 2020-01-03  2020-01-18  Sabine Pass South Hook
4   93843.0 2020-01-04  2020-01-23  Ras Laffan  South Hook
5   76239.0 2020-01-05  2020-01-14  Yamal       Grain
6   71749.0 2020-01-05  2020-01-23  Sabine Pass Dragon
7   75353.0 2020-01-06  2020-01-22  Sabine Pass South Hook
8   71749.0 2020-01-07  2020-01-21  Sabine Pass South Hook
9   0       2020-01-08          
10  96925.0 2020-01-09  2020-01-25  Ras Laffan  South Hook
11  65013.0 2020-01-10  2020-01-22  Snohvit     Grain
12  76505.0 2020-01-10  2020-01-19  Yamal       Dragon
13  0       2020-01-11          
14  0       2020-01-12          
15  0       2020-01-13          
16  0       2020-01-14          
17  0       2020-01-15  

    

Above is a snapshot of the data available.

I would like to have a moving average column which give the MA based on dates and not rows-i.e the days where I have multiple entries for the same date should just have one value as the MA.

I tried using pd.rolling() but this obviously gives the lookback on the rows rather than dates

Answer 1

We don't know what your window is for the moving average, so I selected 2, which will leave the first day's MA value as NaN obviously.

The basic logic is groupby date, sum the cargo tons, and do the MA on that with a 2 day period. Use a left join to introduce that back to the original dataframe.

import pandas as pd
pd.merge(df,
         df.groupby('DateOrigin')['CargoTons'].sum().rolling(2).mean().reset_index(name='Cargo MA'),
         on='DateOrigin',
         how='left')