I have a dataframe df :

I want to impute the columns with average of Sales for the respective columns at the respective levels:

Is there an inbuilt package I can leverage to achieve this for all the categorical columns that I have?

CodePudding user response：

try this:

avg_sale = df.groupby('Region').agg(avg_sale_region=('Sales', np.mean))
df.merge(avg_sale, on='Region')

This is for region, you can do the same for other columns such as city, ...

for col in columns_you_want: #columns_you_want=['City', 'Region', ...]
    avg_sale = df.groupby(col).agg(**{'avg_sale_by_' col: ('Sales', np.mean)})
    df = df.merge(avg_sale, on=col)

after that you can rename the columns:

df = df.drop(columns=columns_you_want)
df = df.rename(columns={'avg_sale_by_' col: col for col in columns_you_want})