'Want to compute rolling mean dynamically ( Loop ) & then sum by month

I am beginner for Python & needs your help.

Want to pickup the mean input values from df1 , apply it to df2 to compute rolling mean on the fly & then sum it up the rolling mean by month in df3 with expected format & then export it to excel.

So far I gathered below given codes , it is working fine but want to export "groupby sum" in desired format to excel. Mean calculation columns in df2 is not needed , presently it is populating in df2 for each value in df1-Master.

sample data in df1 & df2 are attached below , and df3 - expected out put format also attached below.



from datetime import date
import pandas as pd
import numpy as np
df1 = pd.read_excel('E:\Python\My Test.xlsx', 'Master')
df2 = pd.read_excel('E:\Python\My Test.xlsx', 'Data')
df3 = pd.read_excel('E:\Python\My Test.xlsx', 'Result')

for window1 in df1['Mean-1 input Value']:
    df2[f"Mean {window1}"] = df2['Number'].rolling(window=window1).mean()
for window2 in df1['Mean-2 input Value']:
    df2[f"Mean {window2}"] = df2['Number'].rolling(window=window2).mean()

df3 = df2.groupby('Month').sum()
with pd.ExcelWriter('E:\Python\My Test updated.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Master')
    df2.to_excel(writer, sheet_name='Data')
    df3.to_excel(writer, sheet_name='Result')

df1 ( Master sheet imported from excel )

Mean-1 input Value Mean-1 input Value
2 3
4 5
... ...

df2 ( Data sheet imported from excel )

Date Month Number
01-Jan-22 Jan-22 1
02-Jan-22 Jan-22 2
03-Jan-22 Jan-22 3
04-Jan-22 Jan-22 4
05-Jan-22 Jan-22 5
06-Jan-22 Jan-22 6
07-Jan-22 Jan-22 7
08-Jan-22 Jan-22 8
09-Jan-22 Jan-22 9
10-Jan-22 Jan-22 10
01-Feb-22 Feb-22 11
02-Feb-22 Feb-22 12
03-Feb-22 Feb-22 13
04-Feb-22 Feb-22 14
05-Feb-22 Feb-22 15
06-Feb-22 Feb-22 16
07-Feb-22 Feb-22 17
08-Feb-22 Feb-22 18
09-Feb-22 Feb-22 19
10-Feb-22 Feb-22 20
... ... ...

Expected output in df3 & want to export to Result sheet

Mean-1 input Value Mean-2 input Value Sum Item Jan-22 Feb-22 ...
2 3 Sum Mean-1 49.5 150 ...
2 3 Sum Mean-2 44 145 ...
4 5 Sum Mean-1 38.5 140 ...
2 3 Sum Mean-2 30.5 130 ...
... ... ... ... ... ...


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source