Pandas : Percentage of nan for each value of a column

user_13833498 0
Viewed 748

Objective : Get missing values as percentage for each column of a df and for each client

My df is about created tickets :

          id                type  ...      priority          Client
0     56 113            Incident  ...          Low           client1
1     56 267             Demande  ...          High          client1
2     56 294            Incident  ...          Nan           NaN
3     56 197             Demande  ...          Low           client3
4     56 143             Demande  ...          Nan           client4

first attempt :

df.notna().sum()/len(agg_global)*100
Out[29]:                       
id                       97.053453   
type                     76.415869   
priority                 82.626625    
client                   84.596443

That's very useful but i would like to add more details to my output with 'Client' dimension in column like this :

Output i would like to create :

                           Client1   Client2     Client3      NaN
id                      100.000000   100.000000  100.000000   66.990424
type                     76.415869   66.990424   76.415869    43.761970
status                  100.000000   100.000000  66.990424    76.415869
category                66.990424   43.761970   76.415869     43.761970
entity                   43.761970   100.000000  76.415869    76.415869
source_demande           84.596443   100.000000  76.415869    43.761970

I tried to use "groupby" but i could not obtain the desire output ... :

                   id       type  ...      priority         Client
client                            ...                             
True        97.053453  76.415869  ...      29.98632       29.98632

Any Advice will be appreciated. Thank you for your attention !

pandas-groupby pandas python
2 Answers

You can remove column Client for not testing percentage of missing values, test them by DataFrame.isna, aggregate mean by Client with replace NaNs for avoid lost them, and last transpose by DataFrame.T:

print (df)
       id      type priority   Client
0     NaN  Incident      Low  client1
1     NaN       NaN     High  client1
2  56 294  Incident      Nan      NaN
3  56 197       NaN      Low  client3
4     NaN   Demande      NaN  client4


df = (df.drop('Client', 1)
        .isna()
        .groupby(df['Client'].fillna('NaN'))
        .mean()
        .rename_axis(None)
        .T)
print (df)
          NaN  client1  client3  client4
id        0.0      1.0      0.0      1.0
type      0.0      0.5      1.0      0.0
priority  0.0      0.0      0.0      1.0
user_29010020

As far as I see it's possible to do using brute-force. I would try to use the isna function and summation to estimate the number of NaNs in each row or column, then I'd try to estimate percentages.

user_131979410
Related