w3resource

Pandas SQL Query: Display the name, salary and department number in descending order by first name

Pandas HR database Queries: Exercise-9 with Solution

Write a Pandas program to display the first name, last name, salary and department number in descending order by first name.

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

Equivalent SQL Syntax:

SELECT first_name, last_name, salary,  department_id
  FROM employees
   ORDER BY first_name;

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 name, last name, salary and department number in ascending order by department number.
Next: Write a Pandas program to display the first name, last name, salary and manger id where manager ids are null.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.