4
$\begingroup$

I can't work out how to get the moving annual sum from this data:

>                          revenue
> txdate   2014-01-31     2014-02-28      2014-03-31     2014-04-30     ....
> user_id
> 1            0              10             165             0
> 2          265             265             200           250
> 3          770             985            1235           900
> ....

Previously I would have tried something like this and adjusted until it worked:

df.groupby(level='practice_id').apply(lambda x: pd.rolling_sum(x, 12))

but it's deprecated and I'm not getting my head around the 0.18 changes to rolling despite reading the docs, and I'm not sure that the shape of the data is helpful (it's close to what needs to be inserted in a db table). The original data format is as follows:

> txdate          user_id        tx_amount
> 2014-01-01         2               5
> 2014-01-02         2               5
> 2014-01-02         3              30
> 2014-01-03         3              15
> 2014-01-02         2              10

I reshaped with the following cmd:

> df.set_index('txdate').groupby('user_id').resample('M').agg({'revenue': np.sum})

I'm thinking I might need to reverse the order of operations.

$\endgroup$

1 Answer 1

2
$\begingroup$

If anyone else comes looking, this was my solution:

# find last column
last_column = df.shape[1]-1

# grab the previous 11 columns (also works if there aren't that many in the df)
wanted = df.iloc[:, (last_column-11):last_column]

# calculate the rounded moving annual total 
mat_calc = round(wanted.sum(axis=1)/len(wanted.columns), 2)

Probably not the most pandastic solution, but it works well.

$\endgroup$
1
  • 1
    $\begingroup$ Welcome to DS stack exchange! Thanks for checking back and posting your solution. I guess in this case you want the actual rolling average over a full year, but I would typically use an exponentially weighted moving average for this type of problem. Check out http://statsmodels.sourceforge.net/ and there might be a way to apply a flat function rather than a weight in ARIMA or EWMA. $\endgroup$
    – AN6U5
    Commented Aug 9, 2016 at 1:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.