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:
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?
Python: Tips of the Day
Find current directory and file's directory:
To get the full path to the directory a Python file is contained in, write this in that file:
import os dir_path = os.path.dirname(os.path.realpath(__file__))
(Note that the incantation above won't work if you've already used os.chdir() to change your current working directory, since the value of the __file__ constant is relative to the current working directory and is not changed by an os.chdir() call.)
To get the current working directory use
import os cwd = os.getcwd()
Documentation references for the modules, constants and functions used above:
- The os and os.path modules.
- The __file__ constant
- os.path.realpath(path) (returns "the canonical path of the specified filename, eliminating any symbolic links encountered in the path")
- os.path.dirname(path) (returns "the directory name of pathname path")
- os.getcwd() (returns "a string representing the current working directory")
- os.chdir(path) ("change the current working directory to path")
Ref: https://bit.ly/3fy0R6m
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework