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.