Pandas SQL Query: Display employees who is either Sales Representative or Sales Man
24. Employee Name & DOJ for Sales Reps or Sales Men
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:
For more Practice: Solve these Related Problems:
- Write a Pandas program to display the first and last name along with the date of joining for employees whose job title is either 'Sales Representative' or 'Sales Man' using a case-insensitive filter.
- Write a Pandas program to filter EMPLOYEES.csv for records with job titles containing 'Sales' and then display the required fields sorted by date of joining.
- Write a Pandas program to display employee names and joining dates for sales positions and compute the tenure for each employee.
- Write a Pandas program to display the first and last names and joining dates for sales employees and then export the filtered data to a new CSV file.
Go to:
Previous: Write a Pandas program to display the details of jobs in descending sequence on job title.
Python Code Editor:
Structure of HR database :
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?