Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas GroupBy and Calculate Z-Score [duplicate]

Tags:

python

pandas

So I have a dataframe that looks like this:

pd.DataFrame([[1, 10, 14], [1, 12, 14], [1, 20, 12], [1, 25, 12], [2, 18, 12], [2, 30, 14], [2, 4, 12], [2, 10, 14]], columns = ['A', 'B', 'C'])

    A   B   C
0   1   10  14
1   1   12  14
2   1   20  12
3   1   25  12
4   2   18  12
5   2   30  14
6   2   4   12
7   2   10  14

My goal is to get the z-scores of column B, relative to their groups by column A and C. I know I can calculate the mean and standard deviation of each group

test.groupby(['A', 'C']).mean()    
        B
A   C   
1   12  22.5
    14  11.0
2   12  11.0
    14  20.0

test.groupby(['A', 'C']).std()
        B
A   C   
1   12  3.535534
    14  1.414214
2   12  9.899495
    14  14.142136

Now for every item in column B I want to calculate it's z-score based off of these means and standard deviations. So the first result would be (10 - 11) / 1.41. I feel like there has to be a way to do this without too much complexity but I've been stuck on how to proceed. Let me know if anyone can point me in the right direction or if I need to clarify anything!

like image 694
JSolomonCulp Avatar asked Feb 27 '19 14:02

JSolomonCulp


1 Answers

Do with transform

Mean=test.groupby(['A', 'C']).B.transform('mean')    
Std=test.groupby(['A', 'C']).B.transform('std')

Then

(test.B - Mean) / Std

One function zscore from scipy

from scipy.stats import zscore
test.groupby(['A', 'C']).B.transform(lambda x : zscore(x,ddof=1))
Out[140]: 
0   -0.707107
1    0.707107
2   -0.707107
3    0.707107
4    0.707107
5    0.707107
6   -0.707107
7   -0.707107
Name: B, dtype: float64

Ok Show my number tie out hehe

(test.B - Mean) / Std ==test.groupby(['A', 'C']).B.transform(lambda x : zscore(x,ddof=1))
Out[148]: 
0    True
1    True
2    True
3    True
4    True
5    True
6    True
7    True
Name: B, dtype: bool
like image 128
BENY Avatar answered Nov 15 '22 11:11

BENY