Table of Contents
The outline of your notebook will show up here. You can include headings in any text cell by starting a line with #
, ##
, ###
, etc., depending on the desired title hierarchy.
You just got hired as the first and only data practitioner at a small business experiencing exponential growth. The company needs more structured processes, guidelines, and standards. Your first mission is to structure the human resources data. The data is currently scattered across teams and files and comes in various formats: Excel files, CSVs, JSON files...
You'll work with the following data in the datasets
folder:
- Office addresses are currently saved in
office_addresses.csv
. If the value for office isNaN
, then the employee is remote. - Employee addresses are saved on the first tab of
employee_information.xlsx
. - Employee emergency contacts are saved on the second tab of
employee_information.xlsx
; this tab is calledemergency_contacts
. However, this sheet was edited at some point, and the headers were removed! The HR manager let you know that they should be:employee_id
,last_name
,first_name
,emergency_contact
,emergency_contact_number
, andrelationship
. - Employee roles, teams, and salaries have been exported from the company's human resources management system into a JSON file titled
employee_roles.json
. Here are the first few lines of that file:
{"A2R5H9": { "title": "CEO", "monthly_salary": "$4500", "team": "Leadership" }, ... }
import pandas as pd
import os
os.listdir()
df_address = pd.read_csv('datasets/office_addresses.csv')
df_address
df_empl = pd.read_excel("datasets/employee_information.xlsx", sheet_name = 0)
df_empl
df_contact = pd.read_excel("datasets/employee_information.xlsx", sheet_name = 'emergency_contacts', header = None, names = ['employee_id', 'last_name', 'first_name', 'emergency_contact', 'emergency_contact_number', 'relationship'])
df_contact
df_role= pd.read_json('datasets/employee_roles.json', orient='index')
df_role
print("Shape of df_address:", df_address.shape)
print("Shape of df_empl:", df_empl.shape)
print("Shape of df_contact:", df_contact.shape)
print("Shape of df_role:", df_role.shape)
df1 = pd.merge(df_empl, df_address, left_on='employee_country', right_on='office_country', how='left')
df2 = pd.merge(df1, df_contact, on="employee_id", how='left')
df3 = pd.merge(df2, df_role, left_on="employee_id", right_index=True, how='left')
df3
df3.loc[:, df3.columns.str.startswith('office')] = df3.loc[:, df3.columns.str.startswith('office')].fillna('Remote')
df3
df3.set_index('employee_id', inplace=True)
employees_final = df3[['employee_first_name', 'employee_last_name', 'employee_country', 'employee_city', 'employee_street', 'employee_street_number', 'emergency_contact', 'emergency_contact_number', 'relationship', 'monthly_salary', 'team', 'title', 'office', 'office_country', 'office_city', 'office_street', 'office_street_number']]
employees_final.columns = ['first_name', 'last_name', 'employee_country', 'employee_city', 'employee_street', 'employee_street_number', 'emergency_contact', 'emergency_contact_number', 'relationship', 'monthly_salary', 'team', 'title', 'office', 'office_country', 'office_city', 'office_street', 'office_street_number']
employees_final