Pandas SQL Query: Display the name, salary and department number for those employees who holds a letter n as a 3rd character in their first name

Pandas HR database Queries: Exercise-19 with Solution

Write a Pandas program to display the first, last name, salary and department number for those employees who holds a letter n as a 3rd character in their first name.

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[employees['first_name'].str[2:3]=='n']
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
Nancy           Greenberg       12000     100.0
Daniel          Faviet          9000      100.0
Den             Raphaely        11000     30.0
Renske          Ladwig          3600      50.0
Randall         Matos           2600      50.0
Nanette         Cambrault       7500      80.0
Janette         King            10000     80.0
Lindsey         Smith           8000      80.0
Danielle        Greene          9500      80.0
Sundar          Ande            6400      80.0
Sundita         Kumar           6100      80.0
Jonathon        Taylor          8600      80.0
Winston         Taylor          3200      50.0
Nandita         Sarchand        4200      50.0
Jennifer        Dilly           3600      50.0
Randall         Perkins         2500      50.0
Vance           Jones           2800      50.0
Donald          OConnell        2600      50.0
Jennifer        Whalen          4400      10.0

Equivalent SQL Syntax:

SELECT first_name,last_name, department_id
 FROM employees
  WHERE first_name LIKE '__n%';

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 department number for those employees whose managers are hold the ID 120, 103 or 145.
Next: Write a Pandas program to display the first name, job id, salary and department for those employees not working in the departments 50,30 and 80.