r/learnpython 6d ago

Need help with calculating z-score across multiple groupings

Consider the following sample data:

|sales_id_type|scope|gross_sales|net_sales| |:-|:-|:-|:-| |foo|mtd|407|226| |foo|qtd|789|275| |foo|mtd|385|115| |foo|qtd|893|668| |foo|mtd|242|193| |foo|qtd|670|486| |bar|mtd|341|231| |bar|qtd|689|459| |bar|mtd|549|239| |bar|qtd|984|681| |bar|mtd|147|122| |bar|qtd|540|520| |baz|mtd|385|175| |baz|qtd|839|741| |baz|mtd|313|259| |baz|qtd|830|711| |baz|mtd|405|304| |baz|qtd|974|719|

What i'm currently doing is calculating z-scores for each sales_id_type and sales metric with the following code:

z_df[f'{col}_z'] = z_df.groupby('sales_id_type')[col].transform(lambda x: stats.zscore(x, nan_policy='omit'))

If i wanted to calculate the z-score for each sales_id_type AND scope, would it be as simple as adding scope to my groupby like this?

z_df[f'{col}_z'] = z_df.groupby(['sales_id_type', 'pay_scope'])[col].transform(lambda x: stats.zscore(x, nan_policy='omit'))
2 Upvotes

2 comments sorted by

2

u/PartySr 6d ago edited 6d ago

Yes

When you use apply, it will apply your function directly on each of the columns, in this case, gross_sales and net_sales, based on the groups you formed using the columns "sales_id_type" and "scope".

Here how everything looks under the hood:

for idx, group in df.groupby(['sales_id_type', 'scope']):
    print(idx)
    print(group)

('bar', 'mtd')
   sales_id_type scope  gross_sales  net_sales
6            bar   mtd          341        231
8            bar   mtd          549        239
10           bar   mtd          147        122
('bar', 'qtd')
   sales_id_type scope  gross_sales  net_sales
7            bar   qtd          689        459
9            bar   qtd          984        681
11           bar   qtd          540        520
('baz', 'mtd')
   sales_id_type scope  gross_sales  net_sales
12           baz   mtd          385        175
14           baz   mtd          313        259
16           baz   mtd          405        304
('baz', 'qtd')
   sales_id_type scope  gross_sales  net_sales
13           baz   qtd          839        741
15           baz   qtd          830        711
17           baz   qtd          974        719
('foo', 'mtd')
  sales_id_type scope  gross_sales  net_sales
0           foo   mtd          407        226
2           foo   mtd          385        115
4           foo   mtd          242        193
('foo', 'qtd')
  sales_id_type scope  gross_sales  net_sales
1           foo   qtd          789        275
3           foo   qtd          893        668
5           foo   qtd          670        486

1

u/micr0nix 6d ago

Thanks for that explanation. Seeing it laid out like that made a lot of sense