Pandas: print column name with missing values
df.isnull().any() generates a boolean array (True if the column has a missing value, False otherwise). You can use it to index into df.columns:
df.columns[df.isnull().any()]will return a list of the columns which have missing values.
df = pd.DataFrame({'A': [1, 2, 3], 'B': [1, 2, np.nan], 'C': [4, 5, 6], 'D': [np.nan, np.nan, np.nan]})dfOut: A B C D0 1 1.0 4 NaN1 2 2.0 5 NaN2 3 NaN 6 NaNdf.columns[df.isnull().any()]Out: Index(['B', 'D'], dtype='object')df.columns[df.isnull().any()].tolist() # to get a list instead of an Index objectOut: ['B', 'D']
Oneliner -
[col for col in df.columns if df[col].isnull().any()]
import numpy as npimport pandas as pdraw_data = {'first_name': ['Jason', np.nan, 'Tina', 'Jake', 'Amy'], 'last_name': ['Miller', np.nan, np.nan, 'Milner', 'Cooze'], 'age': [22, np.nan, 23, 24, 25], 'sex': ['m', np.nan, 'f', 'm', 'f'], 'Test1_Score': [4, np.nan, 0, 0, 0], 'Test2_Score': [25, np.nan, np.nan, 0, 0]}results = pd.DataFrame(raw_data, columns = ['first_name', 'last_name', 'age', 'sex', 'Test1_Score', 'Test2_Score'])results ''' first_name last_name age sex Test1_Score Test2_Score0 Jason Miller 22.0 m 4.0 25.01 NaN NaN NaN NaN NaN NaN2 Tina NaN 23.0 f 0.0 NaN3 Jake Milner 24.0 m 0.0 0.04 Amy Cooze 25.0 f 0.0 0.0'''You can use following function, which will give you output in Dataframe
- Zero Values
- Missing Values
- % of Total Values
- Total Zero Missing Values
- % Total Zero Missing Values
- Data Type
Just copy and paste following function and call it by passing your pandas Dataframe
def missing_zero_values_table(df): zero_val = (df == 0.00).astype(int).sum(axis=0) mis_val = df.isnull().sum() mis_val_percent = 100 * df.isnull().sum() / len(df) mz_table = pd.concat([zero_val, mis_val, mis_val_percent], axis=1) mz_table = mz_table.rename( columns = {0 : 'Zero Values', 1 : 'Missing Values', 2 : '% of Total Values'}) mz_table['Total Zero Missing Values'] = mz_table['Zero Values'] + mz_table['Missing Values'] mz_table['% Total Zero Missing Values'] = 100 * mz_table['Total Zero Missing Values'] / len(df) mz_table['Data Type'] = df.dtypes mz_table = mz_table[ mz_table.iloc[:,1] != 0].sort_values( '% of Total Values', ascending=False).round(1) print ("Your selected dataframe has " + str(df.shape[1]) + " columns and " + str(df.shape[0]) + " Rows.\n" "There are " + str(mz_table.shape[0]) + " columns that have missing values.")# mz_table.to_excel('D:/sampledata/missing_and_zero_values.xlsx', freeze_panes=(1,0), index = False) return mz_tablemissing_zero_values_table(results)Output
Your selected dataframe has 6 columns and 5 Rows.There are 6 columns that have missing values. Zero Values Missing Values % of Total Values Total Zero Missing Values % Total Zero Missing Values Data Typelast_name 0 2 40.0 2 40.0 objectTest2_Score 2 2 40.0 4 80.0 float64first_name 0 1 20.0 1 20.0 objectage 0 1 20.0 1 20.0 float64sex 0 1 20.0 1 20.0 objectTest1_Score 3 1 20.0 4 80.0 float64If you want to keep it simple then you can use following function to get missing values in %
def missing(dff): print (round((dff.isnull().sum() * 100/ len(dff)),2).sort_values(ascending=False))missing(results)'''Test2_Score 40.0last_name 40.0Test1_Score 20.0sex 20.0age 20.0first_name 20.0dtype: float64'''