Pandas SQL Query: Display name, salary and department number for those employees whose first name ends with specified letter
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?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics