Pandas SQL Query: Display the name, salary and manger id where manager ids are not null

Pandas HR database Queries: Exercise-11 with Solution

Write a Pandas program to display the first name, last name, salary and manger id where manager ids are not null.

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    Manager ID")
result = employees[employees['manager_id'].notnull()]
for index, row in result.iterrows():
    print(row['first_name'].ljust(15),row['last_name'].ljust(15),str(row['salary']).ljust(9),row['manager_id'])

Sample Output:

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

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 :

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 manger id where manager ids are null.
Next: Write a Pandas program to create and display a boolean series, where True for not null and False for null values or missing values in state_province column of locations file.