+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Use of & operator, groupby in python (/thread-24936.html)
Use of & operator, groupby in python - abhi1693r - Mar-11-2020
ID AMOUNT DATE AVG_AMOUNT
FACL01 10 20201503
FACL02 20 20201503
FACL03 30 20201503
FACL01 40 20201502
FACL01 50 20201503
FACL06 60 20201502
FACL07 70 20201501
FACL08 80 20201503
FACL09 20 20201503
FACL01 10 20201501
FACL11 30 20201503
FACL12 10 20201502
Assumptions: Today is March 15 2020 (20201503). This data is for last 3 months.
import pandas as pd
df= pd.DataFrame({ 'ID': [FACL01, FACL02, FACL03, FACL01, FACL04, FACL06, FACL07, FACL08, FACL09, FACL01, FACL11, FACL12], 'AMOUNT': [10, 20, 30, 40, 50, 60, 70, 80, 20, 10, 30, 10], 'DATE': [20201503, 20201503, 20201503, 20201502, 20201503, 20201502, 20201501, 20201503, 20201503, 20201501, 20201503, 20201502]}) M1=[20201501, 20201502, 20201503]
#My try:
df["AVG_AMOUNT"] = df[df["DATE"].isin(M1)].groupby('ID')['AMOUNT'].mean()So every month this list gets updated with an ID number and corresponding amount etc. For eg, FACL01 came in Jan, Feb and March. Goal is to fill current month's ID (say,FACL01 for 20201503) with mean of amount for last 3 months for that ID ie. ((10+40+80)/3). (PS:I know the last part makes it a bit complex)