python - 具有自定义分位数的 Groupby 数据框并将分位数标签添加到新列

Question

import pandas as pd
import numpy as np

a = list("ABC") * 4
value = np.random.randint(-5, 5, 12)
df = pd.DataFrame({"A": a, "value": value})
print(df)

    A  value
0   A     -5
1   B      1
2   C      0
3   A      0
4   B     -4
5   C     -1
6   A      2
7   B      4
8   C     -5
9   A      0
10  B      1
11  C     -1

我的目标是创建第三列，其中包含基于自定义分位数范围的分位数标签。这些范围是根据第一列的 GroupBy 对象计算的。我的用例是计算每个组的底部 10% 和顶部 10% 值(即十分位数)，然后相应地在新列中标记它们:'bottom_decile'、'mid_deciles'、'top_decile'

期望的结果:

    A  value              C
0   A     -5  bottom_decile
1   B      1    mid_deciles
2   C      0     top_decile
3   A      0    mid_deciles
4   B     -4  bottom_decile
5   C     -1    mid_deciles
6   A      2     top_decile
7   B      4     top_decile
8   C     -5  bottom_decile
9   A      0    mid_deciles
10  B      1    mid_deciles
11  C     -1    mid_deciles

这是我的尝试:

df['C'] = df.groupby(['A'])['value'].transform(lambda x: pd.qcut(x, [0, 0.1, 0.9, 1], labels=['bottom_decile', 'mid_deciles', 'top_decile']))

但是，结果会引发错误:

ValueError: Bin edges must be unique: array([-5. , -3.8,  2. ,  2. ]).
You can drop duplicate edges by setting the 'duplicates' kwarg

Answer 1

您可以删除重复的边:

df['C'] = df.groupby('A')['value'].transform(pd.qcut,
                                   q=[0, 0.1, 0.9, 1],
                                   labels=['bottom_decile', 'mid_deciles', 'top_decile'],
                                   duplicates='drop')

print(df.head())

输出:

   A  value              C
0  A     -5  bottom_decile
1  B      1    mid_deciles
2  C      0     top_decile
3  A      0    mid_deciles
4  B     -4  bottom_decile