Rolling sum across subsets

user_14487374 0
Viewed 48

I have a data set with values ('ee') of interest by quarter, and multiple fields which indicate the amount of ee in various subsets of the population, as follows:

test=pd.DataFrame(data={'cyq':['2018Q1']*3+['2018Q2']*3+['2018Q3']*3+['2018Q4']*3,
                   'species':['canine','canine','feline']*4,
                   'group':['a','b','a']*4,
                    'ee':range(12)})

I'm trying to get a rolling sum of ee over the quarters, specific to each unique subset of the other fields, in this case species, and group. In my actual data set, I have six identifying fields in total.

The following:

test.groupby(['cyq','species','group']).ee.rolling(window=2).sum()

is producing all NaNs. Other solutions which I've found end up rolling the sum within the each quarter, or only on one identifying field. My goal is to take a rolling sum that recognizes canine a, canine b, and feline a as distinct and rolls their ee values by quarter:

enter image description here

Thank you so much for the help. I feel like there's a simple solution, but variations on this question I've found here aren't working on my dataset.

EDIT: I've pieced together the following solution, but sure this can't be the most effective or efficient way to achieve the goal:

test=pd.DataFrame(data={'cyq':['2018Q1']*3+['2018Q2']*3+['2018Q3']*3+['2018Q4']*3,
                   'species':['canine','canine','feline']*4,
                   'group':['a','b','a']*4,
                    'ee':range(12)})
test['cyq']=test.cyq.apply(lambda x: pd.to_datetime(x))
test=test.groupby(['group','species','cyq']).sum().reset_index(level=2)
test=test.groupby(level=[i for i in range(test.index.nlevels)]).rolling('100D',min_periods=2,on='cyq').sum()
test.droplevel([i for i in range(int(test.index.nlevels/2))]).reset_index()
pandas python-3.x python
2 Answers

EDIT2 Again, regarding to the comments, here a possible solution? Please try it on your real data and validate it.

test['cyq']= pd.to_datetime(test['cyq'])
test=test.set_index('cyq').groupby(['species','group']).rolling('100D',min_periods=2).sum()

EDIT Based on your comment at the other answer I think I know what you are searching for. The mistake was you included the quarters in your groupby which you actually don't want to.

test=pd.DataFrame(data={'cyq':['2018Q1']*3+['2018Q2']*3+['2018Q3']*3+['2018Q4']*3,
                   'species':['canine','canine','feline']*4,
                   'group':['a','b','a']*4,
                    'ee':range(12)})

res = test.groupby(['species', 'group'], as_index = False)['ee'].rolling(window=2).sum().join(test['cyq'])
print(res)

   species group    ee     cyq
0   canine     a   NaN  2018Q1
3   canine     a   3.0  2018Q2
6   canine     a   9.0  2018Q3
9   canine     a  15.0  2018Q4
1   canine     b   NaN  2018Q1
4   canine     b   5.0  2018Q2
7   canine     b  11.0  2018Q3
10  canine     b  17.0  2018Q4
2   feline     a   NaN  2018Q1
5   feline     a   7.0  2018Q2
8   feline     a  13.0  2018Q3
11  feline     a  19.0  2018Q4

You don't get any results because in your provided example after the groupby each group is exactly 1 row. Your rolling window is 2. Look at the documentation for pd.rolling, quote from min_periods.

min_periods : int, default None

Minimum number of observations in window required to have a value; otherwise, result is np.nan. For a window that is specified by an integer, min_periods will default to the size of the window.

Since you never have a window of two all values return NaN. If your real data is bigger and each group has more values, your code will work. You could set min_periods to 1 so you will get a return value if there is at least one value.

user_155213920

I don't think you got anything from your first code because there is nothing that would come back based on that grouping. However, I was able to get pretty close to what you were looking for by removing the grouping.

test=pd.DataFrame(data={'cyq':['2018Q1']*3+['2018Q2']*3+['2018Q3']*3+['2018Q4']*3,
                   'species':['canine','canine','feline']*4,
                   'group':['a','b','a']*4,
                    'ee':range(12)})
test['ee'] = test['ee'].rolling(window = 2).sum()
test
user_177504310
Related