using RollingFunctions
df9 = DataFrame(region = ["US","US","US","US","EU","EU","EU","EU"],
year = [2010,2011,2012,2013,2010,2011,2012,2013],
value = [3,3,2,2,2,2,1,1])
df9 = @linq df9 |>
groupby(:region) |>
transform(SumValue = ["missing"; rolling(sum, :value, 2)])
I think that works!
I see your point in having to indicate it “missing” or null starting value… the rolling function isnt smart enough to basically help the user out with that.
hmm… will be painful on a large dataset.
It shouldn’t be that painful. There are easy ways to write a small helper function that won’t use up as much memory. But no need to re-write for optimization until you actually see a bottleneck.
in a large dataset where you might not know the exact amount, or have like last 30 periods rolls, this would become impractical, or am i missing some easy fix?
No. They do not function the same way. Using the value missing will have much better performance than the string "missing". And there are lots and lots of utilities to make working with missing simple compared to the string "missing".
participants in this thread
Please let me know of questions you may have on the use of RollingFunctions.jl.
This package supports two kinds of applicative windows sliding over sequential data.
the sequential data (index ordered) contains (N) values, here indexed 1:N.
the operational window size (W) is given and W <= N.
The familiar kind rolls over data and with each advance applies a function to the subsequence spanned by the window. We call this approach a rolling function.
a rolling max-of-n or mean-of-n taken over a data sequence.
a windowed volatility calculation applied over a time series.
vanilla rolling functions take
a sequence of N (length) values and a window span W (count of indicies).
and return
a sequence of N - W + 1 elements (calculated summary values)
We offer a second kind of applicative window; one that preserves the length of the given data sequence in the length of the value sequence that results. We call this approach a running function.
for most of the data, the corresponding rolling function applies
to obtain the remaining values, a tapering version of the function is applied.
vanilla running functions take
a sequence of N (length) values and a window span W (count of indicies).
and return
a sequence of N elements (calculated summary values)
I’m not sure I understand what you’re trying to do, but if it’s “in every row, put the sum of the value column for the two rows above, except when there aren’t two above, in which case put missing”, you can combine RollingFunctions with ShiftedArrays like this:
julia> using DataFrames, ShiftedArrays, RollingFunctions
julia> df = DataFrame(region = ["US","US","US","US","EU","EU","EU","EU"],
year = [2010,2011,2012,2013,2010,2011,2012,2013],
value = [3,3,2,2,2,2,1,1]);
julia> transform(groupby(df, :region),
:value => (x -> running(sum, lag(x, 1), 2)) => :cumval)
8×4 DataFrame
Row │ region year value cumval
│ String Int64 Int64 Float64?
─────┼─────────────────────────────────
1 │ US 2010 3 missing
2 │ US 2011 3 missing
3 │ US 2012 2 6.0
4 │ US 2013 2 5.0
5 │ EU 2010 2 missing
6 │ EU 2011 2 missing
7 │ EU 2012 1 4.0
8 │ EU 2013 1 3.0
So EU in 2013 is 3 (sum of 2012 and 2011), in 2012 is 4 (sum of 2010 and 2011) while in 2011 it’s missing as there’s no 2009 observation.
using ShiftedArrays
function runsh(arr, sh)
if sh>0
d=cumsum(arr).-ShiftedArray(cumsum(arr),sh,default=0)
ShiftedArray(collect(ShiftedArray(d,1-sh)),sh)
else
return arr
end
end
transform(groupby(df, :region),
:value => (x -> runsh(x, 2)) => :cumval)
PS
without the collect function between the two nested shiftdearray the result is not as expected.
Can anyone explain why?
PPS
note tha runsh (arr, sh) also works for sh = 0 and keeps the data type of arr
and
it works even if sh is greater than the length of some of the groups
hey @JeffreySarnoff - qq (i hope) on the RollingFunctions package. I am running into an issue when using Dataframes & Rollingfunction, in similar way that was done in this thread. Specifically I get an error:
"
nested task error:
Bad window span (12) for length 10.
"
when doing this:
"
transform(Trailing12 = running(prod, (:Growth .+1), 12).-1)
"
the odd thing about it is that when I pass a df to it with sample data, it runs just fine, then I pass the full dataset which is about 100x larger, I get the Bad Window span error. Cant quite figure out why that would be happening, as the data set is the same, just more of it… ie. 200k rows vs 2k rows. Hoping it’s something simple happening here?
seems like whenever the df is over 8200 x 8, or so, it starts to show that error message… hm…
hmm…not super sure how to share a failing example…data is private, but cant replicate this error with a random filled df.
doesnt seem to be a pure size issue, as I did a quick test on that right now, doing this:
and it worked just fine.
yet with my actual data it seems to result in that odd error message the moment i go over 8200 rows in the dataframe being passed to it.
is the error message itself indicative of anything potentially?
the 12 in there, there are ID categories that only have up to 10 rows, so it gets broken the moment it tries to do 12 passes where only 10 rows exist for the groupby category.
Hmmm… is there a simple way to tell it to ignore those? alternatively I guess i can filter out original df to ensure those dont occur.
so below fails same way as I have error with my dataset, I think it’s the way running function is trying to use the Groupby, where i will only be 1 row for all of the dataframe, and trying to ask it do running 2 prior crashes the process.
if i understand linq action correctly here, it loops through the df, by given group, and allows the transform function to run with it’s specified action (prod or sum in this case).
the error also doesnt fully make sense to me, as what makes the window size here?
are Julia arrays known to have odd issues?
in my working data example, I have the code working completely fine with a data set I load into a df, then I add another 200 rows to the df with an additional ID, and I start getting the Bad window span errors.
I know it’s not a data issue at this point, I’ve taken that new ID & associated data set, and run it through the linq transform code by itself, and it works fine. So i am completely stumped by Julia array all of a sudden thinks window span is smaller than the vector - as it’s not, I’ve manually looked through the data, and obviously it works fine on it’s own with the same code.
