Pandas SQL Query: Display name, salary and department number for those employees whose first name ends with specified letter
16. Employees with First Name Ending in d, n, or s (Desc)
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:
For more Practice: Solve these Related Problems:
- Write a Pandas program to display the specified fields for employees whose first name ends with 'd', 'n', or 's' using regex, and then sort by department id in descending order.
- Write a Pandas program to filter employees by first name ending with the specified letters and then order the results by department id descending, followed by salary.
- Write a Pandas program to display employees with first names ending in 'd', 'n', or 's', excluding records with null department id, sorted descending by department id.
- Write a Pandas program to display the required fields for employees with the specified ending letters in their first name and count the number of such employees per department.
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?