Python/Pandas - Remove all columns from dataframe where > 50% of rows have the value 0

Question

I'm working with Python & Pandas. I would like to remove every column from my data frame where more than 50% of rows have the value 0 in that particular column.

Here's an example:

import pandas as pd



# defining a dataframe

data = [['Alex',10, 173, 0, 4000],['Bob',12, 0, 0, 4000], ['Clarke',13, 0, 0, 0]]

# naming the columns

df = pd.DataFrame(data,columns=['Name','Age', 'Height', 'Score', 'Income'])



# printing the dataframe

print(df)

I managed to make a table that shows me how many rows have the value 0 for each column and the percentage. But I have the feeling I'm going down the wrong track. Can someone help?

# make a new dataframe and count the number of values = zero per column

zeroValues = df.eq(0).sum(axis=0)

zeroValues = zeroValues.to_frame()



# name the column

zeroValues.columns = ["# of zero values"]



# add a column that calculates the  percentage of values = zero

zeroValues["zeroValues %"] = ((zeroValues["# of zero values"] * 100) / 

len(df.index))



# print the result

print(zeroValues)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Use DataFrame.mean for get percentage of 0 values first and then filter with loc - need all values less or equal with 0.5:

zeroValues = df.eq(0).mean()
print (zeroValues)
Name      0.000000
Age       0.000000
Height    0.666667
Score     1.000000
Income    0.333333
dtype: float64

print (zeroValues <= 0.5)
Name       True
Age        True
Height    False
Score     False
Income     True
dtype: bool

df = df.loc[:, zeroValues <= 0.5]
print (df)
     Name  Age  Income
0    Alex   10    4000
1     Bob   12    4000
2  Clarke   13       0

One row solution:

df = df.loc[:, df.eq(0).mean().le(.5)]
print (df)
     Name  Age  Income
0    Alex   10    4000
1     Bob   12    4000
2  Clarke   13       0

Python/Pandas - Remove all columns from dataframe where > 50% of rows have the value 0

Sign up