Pandas SQL Query: Display the name, salary and department number for those employees whose first name does not contain the letter 'M'

Pandas HR database Queries: Exercise-7 with Solution

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'.

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("Last name       First name      Salary    Department ID")

result = employees[employees['first_name'].str.find('M')==-1]
for index, row in result.iterrows():
    print(row['last_name'].ljust(15),row['first_name'].ljust(15),str(row['salary']).ljust(9),row['department_id'])

Sample Output:

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

Equivalent SQL Syntax:

SELECT first_name, last_name, salary,  department_id
  FROM employees
   WHERE first_name NOT LIKE '%M%';

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, last name, salary and department number for those employees whose first name starts with the letter ‘S’.
Next: Write a Pandas program to display the first name, last name, salary and department number in ascending order by department number.