dataframe replace (numeric) categorical values by their frequency of label = 1

Question

Here is my dataframe:

data = [['a1','b1',0], ['a2','b3',0], ['a1','b2',1], ['a1','b1',1], ['a2','b3',0]]
df = pd.DataFrame(data=data, columns = ['A','B','label'])

Except for 'label' column, each col is categorical value (string). I want to replace (numeric) values by their frequency of label = 1, e.g.：

n(a1) =  count(A == 'a1' & label = 1)/count(A == 'a1')

I used a very stupid way by iterating columns to create a dictionary, then replace df through dictionary. Is there any more simply way?

dic = {}
for col, value in df.iteritems():
    if col != 'label':
        for cat in value.unique():
            count = df[value == cat].shape[0]
            positive = df[(value == cat) & (df['label'] == 1)].shape[0]
            dic[cat] = (positive) / count
df.replace(dic, inplace=True)

My question is to make the code concise since I naively iterate cols and values. I believe pandas has a lot of convenient functions to achieve this.

Answer 1

Avoid for-loops and avoid unique() in this case. Fundamentally you're doing a grouped count, so use Pandas built-in grouping support which is vectorised. Since your numerator is filtering on label, after grouping you need to join (merge) and fillna on missing values that had no label=1.

Don't construct a dic manually. Once you have a replacement frame with a proper index based on the original values, you can just to_dict().

import pandas as pd

df = pd.DataFrame({
    'A':     ('a1', 'a2', 'a1', 'a1', 'a2'),
    'B':     ('b1', 'b3', 'b2', 'b1', 'b3'),
    'label': (   0,    0,    1,    1,    0),
})


def make_counts(col: str) -> pd.Series:
    grouped = df.groupby([col, 'label'])[col].count()
    positive = grouped.loc[:, 1].groupby(level=col).sum().rename('positive')
    count = grouped.groupby(level=col).sum().rename('count_')
    fractions = pd.merge(
        positive, count, how='right', left_index=True, right_index=True,
    )
    replacement = (fractions.positive.fillna(0) / fractions.count_).to_dict()
    return df[col].replace(replacement)


for col in ('A', 'B'):
    df[col] = make_counts(col)

print(df)
'''
          A    B  label
0  0.666667  0.5      0
1  0.000000  0.0      0
2  0.666667  1.0      1
3  0.666667  0.5      1
4  0.000000  0.0      0
'''

Assuming that "whatever you're actually doing" still uses only 0 or 1 for your labels, you should actually re-interpret this as a grouped mean rather than a grouped count:

import pandas as pd

df = pd.DataFrame({
    'A':     ('a1', 'a2', 'a1', 'a1', 'a2'),
    'B':     ('b1', 'b3', 'b2', 'b1', 'b3'),
    'label': (   0,    0,    1,    1,    0),
})


def make_counts(col: str) -> pd.Series:
    return df.groupby(col).label.transform('mean')


for col in ('A', 'B'):
    df[col] = make_counts(col)