Pandas SQL Query: Display the name, salary and department number in ascending order by department number
Pandas HR database Queries: Exercise-8 with Solution
Write a Pandas program to display the first name, last name, salary and department number in ascending order by department number.
DEPARTMENTS.csv
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.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 Jennifer Whalen 4400 10.0 Michael Hartstein 13000 20.0 Pat Fay 6000 20.0 Karen Colmenares 2500 30.0 Guy Himuro 2600 30.0 Shelli Baida 2900 30.0 Alexander Khoo 3100 30.0 Den Raphaely 11000 30.0 Sigal Tobias 2800 30.0 Susan Mavris 6500 40.0 Curtis Davies 3100 50.0 Randall Perkins 2500 50.0 Peter Vargas 2500 50.0 Britney Everett 3900 50.0 Sarah Bell 4000 50.0 Trenna Rajs 3500 50.0 Joshua Patel 2500 50.0 John Seo 2700 50.0 Samuel McCain 3200 50.0 Stephen Stiles 3200 50.0 Randall Matos 2600 50.0 Timothy Gates 2900 50.0 Vance Jones 2800 50.0 Kelly Chung 3800 50.0 Renske Ladwig 3600 50.0 Alana Walsh 3100 50.0 Anthony Cabrio 3000 50.0 Julia Dellinger 3400 50.0 Alexis Bull 4100 50.0 Nandita Sarchand 4200 50.0 Girard Geoni 2800 50.0 Martha Sullivan 2500 50.0 Jean Fleaur 3100 50.0 Winston Taylor 3200 50.0 Jennifer Dilly 3600 50.0 Hazel Philtanker 2200 50.0 Michael Rogers 2900 50.0 Donald OConnell 2600 50.0 Douglas Grant 2600 50.0 Ki Gee 2400 50.0 Kevin Feeney 3000 50.0 Matthew Weiss 8000 50.0 Payam Kaufling 7900 50.0 Shanta Vollman 6500 50.0 Kevin Mourgos 5800 50.0 Julia Nayer 3200 50.0 Adam Fripp 8200 50.0 James Landry 2400 50.0 Steven Markle 2200 50.0 Laura Bissot 3300 50.0 Mozhe Atkinson 2800 50.0 James Marlow 2500 50.0 TJ Olson 2100 50.0 Jason Mallin 3300 50.0 Irene Mikkilineni 2700 50.0 Alexander Hunold 9000 60.0 Bruce Ernst 6000 60.0 Diana Lorentz 4200 60.0 Valli Pataballa 4800 60.0 David Austin 4800 60.0 Hermann Baer 10000 70.0 Sundita Kumar 6100 80.0 Elizabeth Bates 7300 80.0 Ellen Abel 11000 80.0 William Smith 7400 80.0 Jonathon Taylor 8600 80.0 Jack Livingston 8400 80.0 Charles Johnson 6200 80.0 Alyssa Hutton 8800 80.0 Tayler Fox 9600 80.0 Peter Hall 9000 80.0 Lisa Ozer 11500 80.0 John Russell 14000 80.0 Karen Partners 13500 80.0 Gerald Cambrault 11000 80.0 Eleni Zlotkey 10500 80.0 Peter Tucker 10000 80.0 David Bernstein 9500 80.0 Christopher Olsen 8000 80.0 Nanette Cambrault 7500 80.0 Oliver Tuvault 7000 80.0 Janette King 10000 80.0 Patrick Sully 9500 80.0 Allan McEwen 9000 80.0 Lindsey Smith 8000 80.0 Louise Doran 7500 80.0 Sarath Sewall 7000 80.0 Clara Vishney 10500 80.0 Danielle Greene 9500 80.0 Mattea Marvins 7200 80.0 David Lee 6800 80.0 Sundar Ande 6400 80.0 Amit Banda 6200 80.0 Harrison Bloom 10000 80.0 Alberto Errazuriz 12000 80.0 Steven King 24000 90.0 Lex De Haan 17000 90.0 Neena Kochhar 17000 90.0 Luis Popp 6900 100.0 Jose Manuel Urman 7800 100.0 Ismael Sciarra 7700 100.0 John Chen 8200 100.0 Daniel Faviet 9000 100.0 Nancy Greenberg 12000 100.0 Shelley Higgins 12000 110.0 William Gietz 8300 110.0 Kimberely Grant 7000 nan
Equivalent SQL Syntax:
SELECT first_name, last_name, salary, department_id FROM employees ORDER BY department_id;
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 whose first name does not contain the letter ‘M’.
Next: Write a Pandas program to display the first name, last name, salary and department number in descending order by first name.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics