People Org Analysis

This analysis is on an organizational structure of a company (dataset in files section)

The data provided is a list of employees with their job family, management level, direct manager and management chain

On to the analysis.......

Load & Validate Data

The first step when working with any data set is to load the data and check the values, to ensure the data is complete and the correct data types.

import pandas as pd # The path to the CSV file file_path = "/work/work_sample_IA_dataset.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Display the data types for each column print(df.dtypes)

Exploratory Analysis

Next, I'm going to the do some exploratory data analysis.

One thing I saw in the data that I'm interested in studying is how many employees have a manager that's not in their job family.

import pandas as pd # The path to the CSV file file_path = "/work/work_sample_IA_dataset.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Merge the dataframe with itself based on 'Employee ID' and 'Manager' columns merged_df = pd.merge(df, df, left_on='Manager', right_on='Employee ID', suffixes=('_employee', '_manager')) # Filter out rows where job family of employee is the same as their manager's job family different_job_family = merged_df[merged_df['Job Family_employee'] != merged_df['Job Family_manager']] # Count the number of employees for each job family who have managers from a different job family different_job_family_count = different_job_family['Job Family_employee'].value_counts() # Count the total number of employees in each job family total_employee_count = df['Job Family'].value_counts() # Calculate the percentage and round it to a whole number percentage_diff_manager = ((different_job_family_count / total_employee_count) * 100).round(0) # Combine the count and percentage into a dataframe for better visualization result_df = pd.DataFrame({ 'Total Employees': total_employee_count, 'Employees with Different Job Family Manager': different_job_family_count, 'Percentage with Different Job Family Manager %': percentage_diff_manager }) print(result_df)

This is a surprising finding that the vast majority of employees have a manager that's in a different job family. This may indicate the employee is no longer employed with the company and the manager moved to a different family, or the manager family data isn't up-to-date, among other options.

Quantitative Analysis

First, I'll import the data and add it to a dataframe. Then I want to see the quantity of employees grouped by job family.

import pandas as pd # The path to the CSV file file_path = "/work/work_sample_IA_dataset.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Count the number of each unique ID in the 'Job Family' column and sort them job_family_counts = df['Job Family'].value_counts().sort_values(ascending=False) print(job_family_counts)

The results show a pretty even distribution of employees in each job family.

Span of Control by Job Family

To calculate span of control, we first have to count the number of employees and managers in each job family.

import pandas as pd # The path to the CSV file file_path = "/work/work_sample_IA_dataset.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Count the unique managers for each job family manager_counts = df.drop_duplicates(subset=['Job Family', 'Manager']).groupby('Job Family')['Manager'].count() # Count the total number of employees in each job family employee_counts = df['Job Family'].value_counts() # Combine the two counts into a single DataFrame summary = pd.concat([manager_counts, employee_counts], axis=1) summary.columns = ['Number of Managers', 'Number of Employees'] summary.sort_values(by='Number of Employees', ascending=False, inplace=True) print(summary)

import pandas as pd # The path to the CSV file file_path = "/work/work_sample_IA_dataset.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Count the number of employees reporting to each manager for each job family grouped = df.groupby(['Job Family', 'Manager']).size().reset_index(name='Direct Reports') # Calculate the average span of control for each job family (and round it to 2 decimal places) average_span = grouped.groupby('Job Family')['Direct Reports'].mean().round(2).sort_values(ascending=False) print(average_span)

The job family with the highest span of control is marketing.

Employees with most reports

To analyze this metric, we first have to count how many direct reports each manager has.

import pandas as pd # The path to the CSV file file_path = "/work/work_sample_IA_dataset.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Group by the manager column and count the number of direct reports direct_reports_count = df.groupby('Manager').size().reset_index(name='Number of Direct Reports') print(direct_reports_count)

Then comes the hard part. We need to count how many indirect reports each employee has.

We would need to use recursion here (not efficient) or iteration (more efficient).

Span of Control by Mgmt Level

To calculate the span of control for each management level, we need to:

a. count the total employees at each management level

b. count the total managers at each management level

c. divide employees by manager at each management level

import pandas as pd # The path to the CSV file file_path = "/work/work_sample_IA_dataset.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Count the total number of employees at each management level total_employees = df['Management Level'].value_counts() # Identify the managers managers = df['Manager'].dropna().unique() # Count the total number of managers at each management level df_managers = df[df['Employee ID'].isin(managers)] total_managers = df_managers['Management Level'].value_counts() # Calculate span of control for each management level span_of_control = total_employees / total_managers print(span_of_control)

Employees with Most Reports

import pandas as pd import plotly.graph_objects as go # The path to the CSV file file_path = "/work/work_sample_IA_dataset.csv" # Read data into a pandas dataframe df = pd.read_csv(file_path) # Count the total number of direct reports for each manager df['Direct Reports'] = df.groupby('Manager')['Employee ID'].transform('count') # Sort and take the top 5 employees for each job family with most direct reports top_employees = df.groupby('Job Family').apply(lambda x: x.nlargest(5, 'Direct Reports')).reset_index(drop=True) # Create dropdown menu for chart job_families = top_employees['Job Family'].unique() fig = go.Figure() for job_family in job_families: subset = top_employees[top_employees['Job Family'] == job_family] subset = subset.sort_values(by='Direct Reports') # Sort in ascending order for aesthetic fig.add_trace( go.Bar( x=subset['Direct Reports'], y=subset['Employee ID'].astype(str), # Convert Employee ID to string (in this case it's a better type) orientation='h', name=job_family, visible=(job_family == job_families[0]), hovertemplate='%{y}: %{x} direct reports<extra></extra>', # Hover should show number of direct reports ) ) # Add dropdown menu dropdown_buttons = [] for i, job_family in enumerate(job_families): visibility = [True if j == i else False for j in range(len(job_families))] dropdown_buttons.append( { "label": job_family, "method": "update", "args": [{"visible": visibility}] } ) fig.update_layout( title="Top 5 Employees by Direct Reports for Each Job Family", updatemenus=[ { "buttons": dropdown_buttons, "direction": "down", "showactive": True, "x": 1.15, "xanchor": "right", "y": 1.10, "yanchor": "top" } ] ) fig.show()