I do have the below code where it gives me the sum of Sales and Profit based on Country and State.
import pandas as pd
Order = pd.read_excel (r"C:\Users\Sample.xls", sheet_name='Orders')
Return = pd.read_excel (r"C:\Users\Sample.xls", sheet_name='Returns')
Order.merge(Return, on='Order ID', how='inner')\
.groupby(['Country', 'State']).agg(Sales=("Sales", sum),Profit=("Profit", sum)).reset_index()
OUTPUT
My Requirement is I want to calculate Average Sales and formulae for Average Sales is Average Sales=sum of sales/count of states. For example for state Arizona I have 22 rows so the Average Sales if 6015.773/22 = 273.444227272727.
REQUIRED OUTPUT
