I have a df with this structure:
id a1_l1 a2_l1 a3_l1 a1_l2 a2_l2 a3_l2
1 1 5 3 1 2 3
2 1 5 3 1 2 3
3 2 5 3 5 5 3
4 5 5 3 5 5 3
5 5 5 2
6 5 5 2
7 5 5 2
8 2 5 2
9 3 5 1
10 3 5 1
I want to summarize in a table such that I get:
l1 l2
a1 0.4 0.5
a2 1 0.5
a3 0 0
In which what I'm doing is counting how may times 5 was present divided by the number of valid responses, so that for example:
a1, l1 is equal to .4 as I have 4 values of 5 divided by 10. and a2, l1 equals .5 as I have 2 values of 5 divided by 4 valid responses per column.
Thanks!
You can drop the id column drop('id', axis=1) as well instead of set_index('id')
(x == 5).sum() / x.notna().sum()tmp = df.set_index('id').apply(lambda x: x.eq(5).sum()/x.notna().sum()).reset_index()
tmp[['a', 'l']] = tmp['index'].str.split('_', expand=True)
>>>
index 0 a l
0 a1_l1 0.4 a1 l1
1 a2_l1 1.0 a2 l1
2 a3_l1 0.0 a3 l1
3 a1_l2 0.5 a1 l2
4 a2_l2 0.5 a2 l2
5 a3_l2 0.0 a3 l2
tmp.drop('index', axis=1).pivot(index='a', columns='l').droplevel(0, axis=1).rename_axis(None).rename_axis(None, axis=1)
>>>
l1 l2
a1 0.4 0.5
a2 1.0 0.5
a3 0.0 0.0
Try with pd.wide_to_long
s = pd.wide_to_long(df,['a1','a2','a3'],i='id',j = 'level',sep='_',suffix='\\w+')
out = s.eq(5).groupby(level=1).sum()
out = out.T.div(s.groupby(level=1).size())
out
level l1 l2
a1 0.4 0.2
a2 1.0 0.2
a3 0.0 0.0
You can reshape to have a dataframe with MultiIndex, then perform a simple division of the (sum of the truthy values equal to 5) by not na. Finally, unstack:
df2 = df.set_index('id')
df2.columns = df2.columns.str.split('_', expand = True)
df2 = (df2.eq(5).sum()/df2.notna().sum()).unstack()
output:
l1 l2
a1 0.4 0.5
a2 1.0 0.5
a3 0.0 0.0