Counting number of empty cells in pandas data frame on the row level and create a column accordingly

Question

I have a data frame with empty cells like this:

  Col1       Col2      Col3       Col4        Col5       
   A                    B                       C
                                    G            
   E           R                                P
                        J                          
   C           K                                T

I want to create an extra column with the number of empty cells in each row, so the expected output is like this:

 ID     Col1       Col2      Col3       Col4        Col5      No_Of_Empty     
 1       A                    B                       C           2
 2                                        G                       3
 3       E           R                                P           2
 4                            J                                   3
 5       C           K                                T           3

Here is what I tried:

df['No_Of_Des'] = df.iloc[:,1::].apply(lambda x: sum(x==' '), axis = 1)

The output I am getting is not likethe expected one, I am not sure what is wrong here?

Answer 1

Source DF:

In [168]: df
Out[168]:
  Col1 Col2 Col3 Col4 Col5
0    A         B         C
1                   G
2    E    R              P
3              J
4    C    K              T

Demo:

In [170]: df.iloc[:, 1:].eq("")
Out[170]:
    Col2   Col3   Col4   Col5
0   True  False   True  False
1   True   True  False   True
2  False   True   True  False
3   True  False   True   True
4  False   True   True  False

In [171]: df.iloc[:, 1:].eq("").sum(axis=1)
Out[171]:
0    2
1    3
2    2
3    3
4    2
dtype: int64

In [172]: df['No_Of_Empty'] = df.iloc[:, 1:].eq("").sum(axis=1)

In [173]: df
Out[173]:
  Col1 Col2 Col3 Col4 Col5  No_Of_Empty
0    A         B         C            2
1                   G                 3
2    E    R              P            2
3              J                      3
4    C    K              T            2