Pandas: Find all columns where all entries present, rows and columns has a NaN and drop rows with any NaNs
Pandas Filter: Exercise-25 with Solution
Write a Pandas program to filter all columns where all entries present, check which rows and columns has a NaN and finally drop rows with any NaNs from world alcohol consumption dataset.
Test Data:
Year WHO region Country Beverage Types Display Value 0 1986 Western Pacific Viet Nam Wine 0.00 1 1986 Americas Uruguay Other 0.50 2 1985 Africa Cte d'Ivoire Wine 1.62 3 1986 Americas Colombia Beer 4.27 4 1987 Americas Saint Kitts and Nevis Beer 1.98
Sample Solution:
Python Code :
import pandas as pd
# World alcohol consumption data
w_a_con = pd.read_csv('world_alcohol.csv')
print("World alcohol consumption sample data:")
print(w_a_con.head())
print("\nFind all columns which all entries present:")
print(w_a_con.loc[:, w_a_con.notnull().all()])
print("\nRows and columns has a NaN:")
print(w_a_con.loc[:,w_a_con.isnull().any()])
print("\nDrop rows with any NaNs:")
print(w_a_con.dropna(how='any'))
Sample Output:
World alcohol consumption sample data: Year WHO region ... Beverage Types Display Value 0 1986 Western Pacific ... Wine 0.00 1 1986 Americas ... Other 0.50 2 1985 Africa ... Wine 1.62 3 1986 Americas ... Beer 4.27 4 1987 Americas ... Beer 1.98 [5 rows x 5 columns] Find all columns which all entries present: Year ... Beverage Types 0 1986 ... Wine 1 1986 ... Other 2 1985 ... Wine 3 1986 ... Beer 4 1987 ... Beer 5 1987 ... Other 6 1987 ... Wine 7 1985 ... Spirits 8 1986 ... Spirits 9 1984 ... Other 10 1987 ... Wine 11 1989 ... Beer 12 1985 ... Beer 13 1984 ... Other 14 1985 ... Spirits 15 1987 ... Wine 16 1984 ... Wine 17 1989 ... Beer 18 1984 ... Spirits 19 1984 ... Beer 20 1986 ... Wine 21 1989 ... Spirits 22 1984 ... Spirits 23 1984 ... Beer 24 1985 ... Other 25 1984 ... Other 26 1985 ... Wine 27 1984 ... Beer 28 1987 ... Beer 29 1986 ... Other .. ... ... ... 70 1986 ... Spirits 71 1985 ... Other 72 1987 ... Other 73 1986 ... Other 74 1986 ... Spirits 75 1989 ... Other 76 1985 ... Beer 77 1985 ... Spirits 78 1989 ... Other 79 1989 ... Other 80 1985 ... Other 81 1985 ... Wine 82 1987 ... Spirits 83 1986 ... Other 84 1986 ... Other 85 1985 ... Wine 86 1986 ... Wine 87 1989 ... Wine 88 1987 ... Beer 89 1986 ... Wine 90 1989 ... Wine 91 1989 ... Beer 92 1986 ... Spirits 93 1987 ... Other 94 1985 ... Spirits 95 1984 ... Other 96 1985 ... Wine 97 1984 ... Wine 98 1984 ... Wine 99 1985 ... Wine [100 rows x 4 columns] Rows and columns has a NaN: Display Value 0 0.00 1 0.50 2 1.62 3 4.27 4 1.98 5 0.00 6 0.13 7 0.39 8 1.55 9 6.10 10 0.20 11 0.62 12 0.00 13 0.00 14 0.05 15 0.07 16 0.06 17 2.23 18 1.62 19 1.08 20 0.00 21 4.51 22 2.67 23 0.44 24 NaN 25 0.00 26 1.36 27 2.22 28 0.11 29 NaN .. ... 70 1.02 71 0.57 72 0.00 73 0.01 74 2.06 75 0.00 76 0.02 77 0.01 78 0.00 79 2.09 80 0.84 81 2.54 82 2.25 83 NaN 84 0.00 85 0.01 86 1.83 87 0.01 88 0.42 89 0.70 90 0.01 91 4.43 92 0.00 93 NaN 94 3.06 95 0.00 96 7.38 97 0.00 98 0.00 99 0.00 [100 rows x 1 columns] Drop rows with any NaNs: Year WHO region ... Beverage Types Display Value 0 1986 Western Pacific ... Wine 0.00 1 1986 Americas ... Other 0.50 2 1985 Africa ... Wine 1.62 3 1986 Americas ... Beer 4.27 4 1987 Americas ... Beer 1.98 5 1987 Americas ... Other 0.00 6 1987 Africa ... Wine 0.13 7 1985 Africa ... Spirits 0.39 8 1986 Americas ... Spirits 1.55 9 1984 Africa ... Other 6.10 10 1987 Africa ... Wine 0.20 11 1989 Americas ... Beer 0.62 12 1985 Western Pacific ... Beer 0.00 13 1984 Eastern Mediterranean ... Other 0.00 14 1985 Western Pacific ... Spirits 0.05 15 1987 Africa ... Wine 0.07 16 1984 Americas ... Wine 0.06 17 1989 Africa ... Beer 2.23 18 1984 Europe ... Spirits 1.62 19 1984 Africa ... Beer 1.08 20 1986 South-East Asia ... Wine 0.00 21 1989 Americas ... Spirits 4.51 22 1984 Europe ... Spirits 2.67 23 1984 Europe ... Beer 0.44 25 1984 Eastern Mediterranean ... Other 0.00 26 1985 Europe ... Wine 1.36 27 1984 Eastern Mediterranean ... Beer 2.22 28 1987 Western Pacific ... Beer 0.11 30 1986 Africa ... Other 4.48 31 1986 Western Pacific ... Wine 0.00 .. ... ... ... ... ... 68 1989 Africa ... Beer 0.12 69 1986 Africa ... Spirits 0.42 70 1986 Africa ... Spirits 1.02 71 1985 Africa ... Other 0.57 72 1987 Africa ... Other 0.00 73 1986 Eastern Mediterranean ... Other 0.01 74 1986 Americas ... Spirits 2.06 75 1989 Eastern Mediterranean ... Other 0.00 76 1985 Africa ... Beer 0.02 77 1985 Africa ... Spirits 0.01 78 1989 Americas ... Other 0.00 79 1989 Europe ... Other 2.09 80 1985 Africa ... Other 0.84 81 1985 Europe ... Wine 2.54 82 1987 Europe ... Spirits 2.25 84 1986 South-East Asia ... Other 0.00 85 1985 Africa ... Wine 0.01 86 1986 Americas ... Wine 1.83 87 1989 Eastern Mediterranean ... Wine 0.01 88 1987 Eastern Mediterranean ... Beer 0.42 89 1986 Eastern Mediterranean ... Wine 0.70 90 1989 Africa ... Wine 0.01 91 1989 Europe ... Beer 4.43 92 1986 Africa ... Spirits 0.00 94 1985 Europe ... Spirits 3.06 95 1984 Africa ... Other 0.00 96 1985 Europe ... Wine 7.38 97 1984 South-East Asia ... Wine 0.00 98 1984 Africa ... Wine 0.00 99 1985 South-East Asia ... Wine 0.00 [95 rows x 5 columns]
Click to download world_alcohol.csv
Python Code Editor:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a Pandas program to find which years have all non-zero values and which years have any non-zero values.
Next: Write a Pandas program to filter all records starting from the 'Year' column, access every other column from world alcohol consumption dataset.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/python-exercises/pandas/filter/pandas-filter-exercise-25.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics