To display the none cell in the excel sheet by python

Question

I'm dealing a problem with Excel sheet and python. I have successfully retrieved the specific columns and rows in Excel by pandas. Now I want to display only the rows and columns which has the "none" or "empty" value. Sample image of excel sheet

In above image, I need the rows and columns whose values is none. For example in "estfix" column has several none value so I need to check the column value if it is none i need to print it's corresponding row and column. Hope you understand.

Code I tried:

import pandas as pd
wb= pd.ExcelFile(r"pathname details.xlsx")
sheet_1=pd.read_excel(r"pathname details.xlsx",sheetname=0)

c=sheet_1[["bugid","sev","estfix","manager","director"]]
print(c)

I'm using python 3.6. Thanks in advance!

I expecting output like this:

Here Nan is consider as a None.

jezrael jezrael · Accepted Answer · 2017-10-16T11:23:13

Use isnull with any for check at least one True:

a = df[df.isnull().any(axis=1)]

For columns with rows:

b = df.loc[df.isnull().any(axis=1), df.isnull().any()]

Sample:

df = pd.DataFrame({'A':list('abcdef'),
                   'B':[4,np.nan,4,5,5,4],
                   'C':[7,8,9,4,2,3],
                   'D':[1,3,np.nan,7,1,0],
                   'E':[5,3,6,9,2,4],
                   'F':list('aaabbb')})

print (df)
   A    B  C    D  E  F
0  a  4.0  7  1.0  5  a
1  b  NaN  8  3.0  3  a
2  c  4.0  9  NaN  6  a
3  d  5.0  4  7.0  9  b
4  e  5.0  2  1.0  2  b
5  f  4.0  3  0.0  4  b

a = df[df.isnull().any(1)]
print (a)
   A    B  C    D  E  F
1  b  NaN  8  3.0  3  a
2  c  4.0  9  NaN  6  a

b = df.loc[df.isnull().any(axis=1), df.isnull().any()]
print (b)
     B    D
1  NaN  3.0
2  4.0  NaN

Detail:

print (df.isnull())
       A      B      C      D      E      F
0  False  False  False  False  False  False
1  False   True  False  False  False  False
2  False  False  False   True  False  False
3  False  False  False  False  False  False
4  False  False  False  False  False  False
5  False  False  False  False  False  False

print (df.isnull().any(axis=1))
0    False
1     True
2     True
3    False
4    False
5    False
dtype: bool

print (df.isnull().any())
A    False
B     True
C    False
D     True
E    False
F    False
dtype: bool

To display the none cell in the excel sheet by python

1 Answers