w3resource

Pandas SQL Query: Display the ID for those employees who did two or more jobs in the past


Write a Pandas program to display the ID for those employees who did two or more jobs in the past.

JOB_HISTORY.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")
result = job_history.groupby(['employee_id']) 
print(result.filter(lambda x: len(x) > 1).groupby('employee_id').size().sort_values(ascending=False))

Sample Output:

employee_id
200    2
176    2
101    2
dtype: int64

Equivalent SQL Syntax:

SELECT employee_id 
	FROM job_history 
		GROUP BY employee_id 
			HAVING COUNT(*) >=2;

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 :

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, job id, salary and department for those employees not working in the departments 50,30 and 80.
Next: Write a Pandas program to calculate minimum, maximum and mean salary from employees file.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.