Pandas SQL Query: Display name, salary and department number for those employees whose first name ends with specified letter
Pandas HR database Queries: Exercise-16 with Solution
Write a Pandas program to display the first name, last name, salary and department number for those employees whose first name ends with the letter 'd' or 'n' or 's' and also arrange the result in descending order by department id.
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 Last name Salary Department ID")
result = employees[employees['first_name'].str[-1].isin(['s','d','n'])]
result = result.sort_values('department_id', ascending=True)
for index, row in result.iterrows():
print(row['first_name'].ljust(15),row['last_name'].ljust(15),str(row['salary']).ljust(9),row['department_id'])
Sample Output:
First name Last name Salary Department ID Den Raphaely 11000 30.0 Karen Colmenares 2500 30.0 Susan Mavris 6500 40.0 Jason Mallin 3300 50.0 Alexis Bull 4100 50.0 Kevin Feeney 3000 50.0 Curtis Davies 3100 50.0 John Seo 2700 50.0 Stephen Stiles 3200 50.0 Winston Taylor 3200 50.0 James Marlow 2500 50.0 Steven Markle 2200 50.0 James Landry 2400 50.0 Kevin Mourgos 5800 50.0 Donald OConnell 2600 50.0 Douglas Grant 2600 50.0 Girard Geoni 2800 50.0 Jean Fleaur 3100 50.0 David Austin 4800 60.0 Hermann Baer 10000 70.0 Charles Johnson 6200 80.0 Jonathon Taylor 8600 80.0 Gerald Cambrault 11000 80.0 Harrison Bloom 10000 80.0 David Lee 6800 80.0 Allan McEwen 9000 80.0 David Bernstein 9500 80.0 Karen Partners 13500 80.0 John Russell 14000 80.0 Ellen Abel 11000 80.0 Steven King 24000 90.0 Luis Popp 6900 100.0 John Chen 8200 100.0
Equivalent SQL Syntax:
SELECT first_name, last_name, salary, department_id FROM employees WHERE first_name LIKE '%D%' OR first_name LIKE '%S%' OR first_name LIKE '%N%' ORDER BY salary DESC;
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 name, last name, salary and department number for those employees whose first name ends with the letter 'm'.
Next: Write a Pandas program to display the first name, last name, salary and department number for employees who works either in department 70 or 90.
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