Pandas SQL Query: Display the first name, job id, salary and department for those employees not working in the departments 50,30 and 80

Pandas HR database Queries: Exercise-20 with Solution

Write a Pandas program to display the first name, job id, salary and department for those employees not working in the departments 50,30 and 80.

EMPLOYEES.csv

Sample Solution :

Python Code :

import pandas as pd
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("First name      Job ID       Salary    Department ID")
result = employees[~employees['department_id'].isin([50, 30, 80])]
for index, row in result.iterrows():
    print(row['first_name'].ljust(15),row['job_id'].ljust(12),str(row['salary']).ljust(9),row['department_id'])

Sample Output:

First name      Job ID       Salary    Department ID
Steven          AD_PRES      24000     90.0
Neena           AD_VP        17000     90.0
Lex             AD_VP        17000     90.0
Alexander       IT_PROG      9000      60.0
Bruce           IT_PROG      6000      60.0
David           IT_PROG      4800      60.0
Valli           IT_PROG      4800      60.0
Diana           IT_PROG      4200      60.0
Nancy           FI_MGR       12000     100.0
Daniel          FI_ACCOUNT   9000      100.0
John            FI_ACCOUNT   8200      100.0
Ismael          FI_ACCOUNT   7700      100.0
Jose Manuel     FI_ACCOUNT   7800      100.0
Luis            FI_ACCOUNT   6900      100.0
Kimberely       SA_REP       7000      nan
Jennifer        AD_ASST      4400      10.0
Michael         MK_MAN       13000     20.0
Pat             MK_REP       6000      20.0
Susan           HR_REP       6500      40.0
Hermann         PR_REP       10000     70.0
Shelley         AC_MGR       12000     110.0
William         AC_ACCOUNT   8300      110.0

Equivalent SQL Syntax:

SELECT employee_id, first_name, job_id, department_id
 FROM employees
  WHERE department_id NOT IN (50, 30, 80);

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, last name, salary and department number for those employees who holds a letter n as a 3rd character in their first name.
Next: Write a Pandas program to display the ID for those employees who did two or more jobs in the past.