[FIXED] Find different percentile for every group in data frame

Issue

I have the date frame with the following structure:

df = pd.DataFrame({'GROUP_ID': np.random.randint(1, 7, size=100),
                     'VALUES': np.random.randint(0, 50, size=100)})
df['THRESHOLD'] = df['GROUP_ID']*5
df = df[['GROUP_ID','VALUES','THRESHOLD']]
df.sort_values(by='GROUP_ID', inplace=True)

(this one is just for example)

A column THRESHOLD is actually a percentile (in %) for every group. And I need to add a column 'PERCENTILE' in which there should be a numerical value of percentile for values in each group.

I was trying to use groupby and apply, but I don't get how to pass values of THRESHOLD column to parameter q in quantile\percentile function.

Solution

Create dictionary and map treshold with x.name for GROUP_ID passed to function transform for new column with quantile, only necessary treshold between 0 and 1:

np.random.seed(152)
df = pd.DataFrame({'GROUP_ID': np.random.randint(1, 7, size=100),
                     'VALUES': np.random.randint(0, 50, size=100)})
df['THRESHOLD'] = df['GROUP_ID'] / 15
df = df[['GROUP_ID','VALUES','THRESHOLD']]
df.sort_values(by='GROUP_ID', inplace=True)

d = dict(zip(df['GROUP_ID'], df['THRESHOLD']))
df['new'] = df.groupby('GROUP_ID')['VALUES'].transform(lambda x: x.quantile(d[x.name]))
print (df.head(20))
    GROUP_ID  VALUES  THRESHOLD       new
23         1      17   0.066667  7.733333
53         1       9   0.066667  7.733333
39         1      43   0.066667  7.733333
57         1      15   0.066667  7.733333
36         1      47   0.066667  7.733333
59         1      17   0.066667  7.733333
28         1       4   0.066667  7.733333
63         1      33   0.066667  7.733333
18         1      12   0.066667  7.733333
12         1      27   0.066667  7.733333
47         1      43   0.066667  7.733333
81         1      45   0.066667  7.733333
91         1      45   0.066667  7.733333
5          1       8   0.066667  7.733333
83         1      26   0.066667  7.733333
61         2      39   0.133333  4.200000
95         2      33   0.133333  4.200000
44         2      22   0.133333  4.200000
42         2      34   0.133333  4.200000
41         2      48   0.133333  4.200000

Answered By - jezrael