# [SOLVED] how to write a function to find Zero value count and % of zero for all the columns and export into excel in python

## Issue

I have one data set which has 780 columns and 87529 rows it contains lots of zero values.
I am using the below code, but I am getting a 780*2 line as result, which is really difficult to read and understand,so i wanted to export this result into excel,can anyone help me to construct the code.

``````for column_name in df.columns:
column = df[column_name]
count = (column == 0).sum()
percent_zero = (column ==0 ).sum()/87529*100
print('Count of zeros in column ', column_name, ' is : ', count)
``````

## Solution

Try this one. (You have to use your own `df`)

``````import pandas as pd

df = pd.DataFrame([
{'col1': 0, 'col2': 0},
{'col1': 1, 'col2': 0},
{'col1': 1, 'col2': 1},
])

temp = 'Count of zeros in column "{col}" is : {n_zeros} (Percentage: {percent_zero:.1f}%)'
n_rows = len(df)
seeds = []

for col, ser in df.iteritems():
n_zeros = (ser == 0).sum()
percent_zero = n_zeros / n_rows * 100
print(temp.format(col=col, n_zeros=n_zeros, percent_zero=percent_zero))
seeds.append({'column_name': col, 'number_of_zero': n_zeros, 'percent_of_zero': percent_zero})

df_out = pd.DataFrame(seeds)
df_out.to_excel('out.xlsx', index=False)
``````

If you got an error related to export, try this command:

``````pip install openpyxl
``````