Pandas: Find all columns where all entries present, rows and columns has a NaN and drop rows with any NaNs
25. Complete Data Filtering and NaN Drop
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
For more Practice: Solve these Related Problems:
- Write a Pandas program to identify columns with all non-missing values and then drop any rows that contain NaNs in the dataset.
- Write a Pandas program to filter out columns that have any missing values, then remove rows with NaNs from the remaining data.
- Write a Pandas program to check for NaNs across the dataset, then drop rows with any missing values and verify the resulting shape.
- Write a Pandas program to select columns with complete data and then remove any rows that contain at least one NaN value.
Go to:
PREV : Year-wise Non-zero Analysis.
NEXT :
Alternate Column Selection.
Python Code Editor:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.