Create new Z-Score column based off unique text values in an existing column

user_8452640 0
Viewed 158

System: O365

IDE: JupyterLab

Language: Python version 3.7.3

Library: pandas version 1.0.1

Data source: personally built

Http API Documentation: https://github.com/RTICWDT/open-data-maker/blob/master/API.md

Hello, I am wondering if anyone knows how to return a value utilizing a conditional setting within a column range. For instance, I would like to return z-scores based on like values within a range changing once the next group of values is seen.

Steps were taken:

  1. built the below function it seems to be halfway there but not quite so

Code:

# get data
df0 = pd.read_csv('data/erpservicedesk.csv')
df0.columns

# put z-score into a lamda
zscore = lambda x: (x - x.mean()) / x.std()

# build datafram with the important features
df1 = df0[['Incident ID*+', 'Res.Prod.Cat.TierII', 'Res.Op.Cat.TierIII', 'Mean-Time-Tckt-Close']]

df1.insert(4,'ZofMTTC',df1.groupby(['Res.Prod.Cat.TierII', 'Res.Op.Cat.TierIII'])['Mean-Time-Tckt-Close'].transform(zscore))

df2 = df1.sort_values(by=['Res.Prod.Cat.TierII'])
df2.head(100)

Problem

It appears my lambda function is not conditional based on new column values as it seems to take the 'Mean-Time-Tckt-Close' for the entire dataframe rather than each new instance of 'Res.Prod.Cat.TierII'.

Example

A B C
Bob Store 10
Bob Store 11
Bob Store 8
Alfred Store 12
Alfred Store 9

I need a new column D to reflect the Z-Score for Bob and Alfred based off their respective data.

pandas-groupby pandas transform statistics python-3.x
1 Answers

Working with your example, you can create dfs to store the mean and standard deviation using .groupby, then access these in a lambda function:

import pandas as pd

## recreate example df
df = pd.DataFrame({'A':['Bob']*3+['Alfred']*2, 'B':['Store']*5, 'C':[10,11,8,12,9]})

df_mean =  df.groupby('A').mean()
df_std =  df.groupby('A').std()

## apply the function along each row, using axis=1
df['D'] = df.apply(lambda x: (x['C'] - df_mean.loc[x['A']]) / df_std.loc[x['A']], axis=1)

Output:

>>> df
        A      B   C         D
0     Bob  Store  10  0.218218
1     Bob  Store  11  0.872872
2     Bob  Store   8 -1.091089
3  Alfred  Store  12  0.707107
4  Alfred  Store   9 -0.707107
user_53270680
Related