Pandas SQL Query: Display the first name, job id, salary and department for those employees not working in the departments 50,30 and 80
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:
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.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics