# How to calculate frequency of a category with respect to the value in another column?

I was trying to calculate the frequency of disease_present (yes) when smoking status is y (yes) for each group (A, B, C, D)

 The formula is = Number of samples with disease_present (yes) / No of samples with smoking status 'y' * 100

df

Sample_code Group   smoking disease_present
1   A   y   yes
2   B   n
3   C   n   yes
4   D   y
5   B   n   yes
6   A   y   yes
7   C   n
8   D   y
9   A   n   yes
10  B   y
11  D   n   yes
12  C   n
13  A   y   yes
14  B   y   yes


I tried this

df.groupby("Group").count()


This gave the same number in every category

1. Pandas by default turns empty cells into NaN
2. I tried to replace NaN with empty strings with df1 = df.fillna(""). I still got the same results

I am not able to reproduce the issue.

However I think this is a crosstab problem:

>>> new_df = df.fillna("no")
>>> new_df
# prints:
Sample_code Group smoking disease_present
0             1     A       y             yes
1             2     B       n              no
2             3     C       n             yes
3             4     D       y              no
4             5     B       n             yes
5             6     A       y             yes
6             7     C       n              no
7             8     D       y              no
8             9     A       n             yes
9            10     B       y              no
10           11     D       n             yes
11           12     C       n              no
12           13     A       y             yes
13           14     B       y             yes

>>> pd.crosstab([new_df.disease_present, new_df.smoking], new_df.Group)
# prints
Group                    A  B  C  D
disease_present smoking
no              n        0  1  2  0
y        0  1  0  2
yes             n        1  1  1  1
y        3  1  0  0