英文:
Rolling average base on two columns in Pandas
问题
我想要根据周来获得每个月的滚动总和。以下是我的数据框（df）的样子：
我想要根据每个颜色和月份获取每个月的滚动总和，基于该月的周。对于红色和二月份，第一周是30，第二周是50（30+20）。对于三月份的红色，它应该是15, 25, 45, 65。
我尝试使用分组和滚动窗口，包括使用lambda函数，但没有成功。
任何帮助将不胜感激。提前感谢！
以下是要获取的结果：
number color mon 0 rolling30 red Feb 1 3020 red Feb 2 5015 red Mar 1 1510 red Mar 2 2520 red Mar 3 4520 red Mar 4 655 blue Feb 1 515 blue Feb 2 2020 blue Mar 1 2010 blue Mar 2 3010 blue Mar 3 4030 blue Mar 4 7020 Green Feb 1 2015 Green Mar 1 1510 Green Mar 2 25
希望这能帮助你。
英文:
I want to get a rolling sum for each month base on weeks.
Here is what my df looks like
df = pd.DataFrame([1, 2, 1, 2, 3, 4, 1, 2, 1, 2, 3, 4, 1, 1, 2], index=pd.MultiIndex.from_arrays([[ 30, 20,15, 10, 20, 20,5,15,20,10,10, 30, 20,15, 10], ['red','red','red','red', 'red', 'red', 'blue', 'blue', 'blue', 'blue', 'blue', 'blue','Green', 'Green', 'Green'],['Feb', 'Feb', 'Mar', 'Mar', 'Mar', 'Mar', 'Feb', 'Feb', 'Mar', 'Mar', 'Mar', 'Mar', 'Feb', 'Mar', 'Mar']], names=['number', 'color', 'Mon'])).reset_index()dfnumber color mon 030 red Feb 120 red Feb 215 red Mar 110 red Mar 220 red Mar 320 red Mar 45 blue Feb 115 blue Feb 220 blue Mar 110 blue Mar 210 blue Mar 330 blue Mar 420 Green Feb 115 Green Mar 110 Green Mar 2
I want to get the rolling sum for each month for each color base on that month's week.
For the color red and mon Feb 1st week 30 and 2nd week 50 (30+20). For-Mar it should be for red 15,25,45,65
number color mon 0 rolling30 red Feb 1 3020 red Feb 2 5015 red Mar 1 1510 red Mar 2 2520 red Mar 3 4520 red Mar 4 655 blue Feb 1 515 blue Feb 2 2020 blue Mar 1 2010 blue Mar 2 3010 blue Mar 3 4030 blue Mar 4 7020 Green Feb 1 2015 Green Mar 1 1510 Green Mar 2 25
I'm trying to use group by with rolling window with and without lambda but it didn't work out
df.groupby(by=['color','Mon']).rolling(window=2).sum()# also below commanddf.groupby(by=['color','Mon']).apply(lambda x: x.rolling(2).sum())
Any help would be greatly appreciated.
Thanks in advance!!
答案1
得分: 1
我认为你想要使用 .cumsum() 函数：
df['rolling'] = df.groupby(['color', 'Mon'])['number'].cumsum()print(df)
打印结果：
number color Mon 0 rolling0 30 red Feb 1 301 20 red Feb 2 502 15 red Mar 1 153 10 red Mar 2 254 20 red Mar 3 455 20 red Mar 4 656 5 blue Feb 1 57 15 blue Feb 2 208 20 blue Mar 1 209 10 blue Mar 2 3010 10 blue Mar 3 4011 30 blue Mar 4 7012 20 Green Feb 1 2013 15 Green Mar 1 1514 10 Green Mar 2 25
英文:
I think you want .cumsum():
df['rolling'] = df.groupby(['color', 'Mon'])['number'].cumsum()print(df)
Prints:
number color Mon 0 rolling0 30 red Feb 1 301 20 red Feb 2 502 15 red Mar 1 153 10 red Mar 2 254 20 red Mar 3 455 20 red Mar 4 656 5 blue Feb 1 57 15 blue Feb 2 208 20 blue Mar 1 209 10 blue Mar 2 3010 10 blue Mar 3 4011 30 blue Mar 4 7012 20 Green Feb 1 2013 15 Green Mar 1 1514 10 Green Mar 2 25
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论