Combined aggregate based on valid values

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

All the answers look equally complex, so I decided to run some benchmarks to help the OP make a decision.

I benchmarked each of the three solutions, each four times in succession, using IPython's %timeit magic. I used the OP's original 6x10 dataframe and a randomly-generated 100x10000 dataframe:

mozway's is by far the fastest.

Congrats, @mozway!

Benchmark details for OP's dataset

# mozway
2.15 ms ± 29 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
2.09 ms ± 17 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
2.1 ms ± 20.3 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
2.1 ms ± 16.4 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

# Asish M.
4.51 ms ± 18.7 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
4.49 ms ± 25.5 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
4.48 ms ± 20.7 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
4.51 ms ± 29.8 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

# BENY
13.5 ms ± 419 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
13.3 ms ± 700 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
13.3 ms ± 285 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
13.3 ms ± 163 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Benchmark details for 100x10000 dataset

# BENY
1.77 s ± 11.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
1.77 s ± 13.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
1.76 s ± 13.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
1.79 s ± 32.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

# Asish M.
2.21 s ± 17.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
2.2 s ± 9.42 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
2.19 s ± 14.6 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
2.2 s ± 24.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

# mozway
42.4 ms ± 1.41 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
43.9 ms ± 498 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
43 ms ± 1.06 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
42.2 ms ± 1.18 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

Code used to generate 100x10000 dataset

import random
import numpy as np
dct = {}
sz = 100
for x in range(sz):
    for y in range(sz):
        k = f'a{x}_l{y}'
        dct[k] = [random.choice(nums) for _ in range(sz)]
        dct[k].sort(key=lambda x: np.isnan(x)) # put nans at the end
dct['id'] = list(range(sz))
df = pd.DataFrame(dct)

You can drop the id column drop('id', axis=1) as well instead of set_index('id')

1. calculate the percentage for each column using (x == 5).sum() / x.notna().sum()
2. reset the index to get a column we can work with
3. generate the 'a' and 'l' columns by splitting the index
4. pivot

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