Pandas SQL Query: Display employees who is either Sales Representative or Sales Man

Last update on May 28 2022 13:37:59 (UTC/GMT +8 hours)

Pandas HR database Queries: Exercise-24 with Solution

Write a Pandas program to display the first and last name and date of joining of the employees who is either Sales Representative or Sales Man.

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       Job ID          Hire Date")
result = employees[employees['job_id'].isin(['SA_REP', 'SA_MAN'])]
for index, row in result.iterrows():
    print(row['first_name'].ljust(15),row['last_name'].ljust(15),str(row['job_id']).ljust(15),str(row['hire_date']).ljust(10))

Sample Output:

First name      Last name       Job ID          Hire Date
John            Russell         SA_MAN          2004-10-01
Karen           Partners        SA_MAN          2005-01-05
Alberto         Errazuriz       SA_MAN          2005-03-10
Gerald          Cambrault       SA_MAN          2007-10-15
Eleni           Zlotkey         SA_MAN          2008-01-29
Peter           Tucker          SA_REP          2005-01-30
David           Bernstein       SA_REP          2005-03-24
Peter           Hall            SA_REP          2005-08-20
Christopher     Olsen           SA_REP          2006-03-30
Nanette         Cambrault       SA_REP          2006-12-09
Oliver          Tuvault         SA_REP          2007-11-23
Janette         King            SA_REP          2004-01-30
Patrick         Sully           SA_REP          2004-03-04
Allan           McEwen          SA_REP          2004-08-01
Lindsey         Smith           SA_REP          2005-03-10
Louise          Doran           SA_REP          2005-12-15
Sarath          Sewall          SA_REP          2006-11-03
Clara           Vishney         SA_REP          2005-11-11
Danielle        Greene          SA_REP          2007-03-19
Mattea          Marvins         SA_REP          2008-01-24
David           Lee             SA_REP          2008-02-23
Sundar          Ande            SA_REP          2008-03-24
Amit            Banda           SA_REP          2008-04-21
Lisa            Ozer            SA_REP          2005-03-11
Harrison        Bloom           SA_REP          2006-03-23
Tayler          Fox             SA_REP          2006-01-24
William         Smith           SA_REP          2007-02-23
Elizabeth       Bates           SA_REP          2007-03-24
Sundita         Kumar           SA_REP          2008-04-21
Ellen           Abel            SA_REP          2004-05-11
Alyssa          Hutton          SA_REP          2005-03-19
Jonathon        Taylor          SA_REP          2006-03-24
Jack            Livingston      SA_REP          2006-04-23
Kimberely       Grant           SA_REP          2007-05-24
Charles         Johnson         SA_REP          2008-01-04

Equivalent SQL Syntax:

SELECT first_name, last_name, hire_date
	FROM employees 
		WHERE job_id IN ('SA_REP', 'SA_MAN');

Click to view the table contain:

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 details of jobs in descending sequence on job title.

Python: Tips of the Day

Find current directory and file's directory:

To get the full path to the directory a Python file is contained in, write this in that file:

import os dir_path = os.path.dirname(os.path.realpath(__file__))

(Note that the incantation above won't work if you've already used os.chdir() to change your current working directory, since the value of the __file__ constant is relative to the current working directory and is not changed by an os.chdir() call.)

To get the current working directory use

import os cwd = os.getcwd()

Documentation references for the modules, constants and functions used above:

The os and os.path modules.

The __file__ constant

os.path.realpath(path) (returns "the canonical path of the specified filename, eliminating any symbolic links encountered in the path")

os.path.dirname(path) (returns "the directory name of pathname path")

os.getcwd() (returns "a string representing the current working directory")

os.chdir(path) ("change the current working directory to path")

Ref: https://bit.ly/3fy0R6m