Pandas SQL Query: Create a boolean series, where True for not null and False for null values or missing values in specified column of locations file

Pandas HR database Queries: Exercise-12 with Solution

Write a Pandas program to create and display a boolean series, where True for not null and False for null values or missing values in state_province column of locations file.

LOCATIONS.csv

Sample Solution :

Python Code :

import pandas as pd
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
employees = pd.read_csv(r"EMPLOYEES.csv")
departments = pd.read_csv(r"DEPARTMENTS.csv")
job_history = pd.read_csv(r"JOB_HISTORY.csv")
jobs = pd.read_csv(r"JOBS.csv")
countries = pd.read_csv(r"COUNTRIES.csv")
regions = pd.read_csv(r"REGIONS.csv")
locations = pd.read_csv(r"LOCATIONS.csv")
print("Original data / State Province")
print(locations.state_province)
print("\n\n   State Province(Not null / Null Series")
print(locations.state_province.notnull())

Sample Output:

Original data / State Province
0                   NaN
1                   NaN
2      Tokyo Prefecture
3                   NaN
4                 Texas
5            California
6            New Jersey
7            Washington
8               Ontario
9                 Yukon
10                  NaN
11          Maharashtra
12      New South Wales
13                  NaN
14                  NaN
15               Oxford
16           Manchester
17              Bavaria
18            Sao Paulo
19               Geneve
20                   BE
21              Utrecht
22    Distrito Federal,
Name: state_province, dtype: object


   State Province(Not null / Null Series
0     False
1     False
2      True
3     False
4      True
5      True
6      True
7      True
8      True
9      True
10    False
11     True
12     True
13    False
14    False
15     True
16     True
17     True
18     True
19     True
20     True
21     True
22     True
Name: state_province, dtype: bool

Click to view the table contain:

Employees Table

Departments Table

Countries Table

Job_History Table

Jobs Table

Locations Table

Regions Table

Python Code Editor:

Structure of HR database :

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a Pandas program to display the first name, last name, salary and manger id where manager ids are not null.
Next: Write a Pandas program to create a boolean series selecting rows with one or more nulls from locations file.