w3resource

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:

Employees Table

Departments Table

Countries Table

Job_History Table

Jobs Table

Locations Table

Regions Table

Python Code Editor:

Structure of HR database :

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?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://198.211.115.131/python-exercises/pandas/sql/python-pandas-hr-database-queries-exercise-8.php