Calculate the percentage of each row which is above or below average, for specific category

user_16433326 0
Viewed 139

I was wondering how I could calculate whether each row was above or below average, for every category via Python? I have a csv file called test.csv. For example, category 2, I have two values. First I need to calculate the mean of that category and then whether each value is above or below avg percentage. I don't know how to do the latter.

import pandas as pd
import numpy as np

#loading the data into data frame
X = pd.read_csv('test.csv')

The two columns of interest are the Category and Totals column:

Category Totals estimates
2        2777   043
4        1003   06
4        3473   065
4        2638   017
1        2855   04
0        2196   03
0        2630   91
2        2714   39
3        2472   0.51
0        1090   0.12
pandas python
2 Answers

Use groupby.transform to compute the grouped means:

means = df.groupby('Category')['Totals'].transform('mean')

Then use np.select to check if they are above/below/equal the grouped means:

conditions = {
    'above': df['Totals'] > means,
    'below': df['Totals'] < means,
}

df['vs_mean'] = np.select(conditions.values(), conditions.keys(), default='equal')

#    Category  Totals  estimates vs_mean
# 0         2    2777      43.00   above
# 1         4    1003       6.00   below
# 2         4    3473      65.00   above
# 3         4    2638      17.00   above
# 4         1    2855       4.00   equal
# 5         0    2196       3.00   above
# 6         0    2630      91.00   above
# 7         2    2714      39.00   below
# 8         3    2472       0.51   equal
# 9         0    1090       0.12   below
user_131383640 
# get mean of the Totals column 
mean = df['Totals'].mean()

# create a new column 'Labels' containing a default value = "above"
df['Labels'] = "above"

# update the column 'Labels' where Totals is below the mean.
df.loc[df['Totals']< mean, 'Labels'] = "below"
user_167479600
Related