'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 |
|---|
