Data Analyst well versed  in SQL, Python, Tableau.

Welcome to my portfolio website

layout

By default, the <title> tag for this page is determined by the title field (in the Content group). You can override the tag value here.

metaTitle

media

bottomSections

By default, the description tag for this post is taken from the Excerpt field. You can override the tag value here.

metaDescription

By default, the "og:image" meta tag set for social sharing this post points to the Featured Image field (in the Content group). You can override that image here.

socialImage

markdown_content

If enabled, the title suffix defined in the site configuration is appended to the title tag of this page.

addTitleSuffix

To add or override any meta tag for this page, add entries to this list. Entries defined here take precedence over any other defaults.

metaTags

title

colors

date

author

excerpt

featuredImage

Divider

## **Project Overview and Purpose**

*   Perform Exploratory Data analysis to figure out which variables have an impact on employee retention.
*   Provide data-driven suggestions and insights for the HR department, based on my understanding of the data
*   Built a Predictive Model that can predict whether or not an employee will leave the company and help identify factors that contribute to leaving

## **Dataset**

In this dataset, there are 14,999 rows, 10 columns, and these variables:

![](/images/Screen%20Shot%202023-04-19%20at%203.13.43%20PM.png)

## **Technology Stack:**

*   Python Packages: Scikit-Learn, NLTK, Pandas, SciPy, Seaborn  (load, explore, extract, and organize information)
*   Jupyter Notebook (Exploratory Data Analysis)

## **Perform the Project**

## Step 1. Imports and load dataset

![](/images/Screen%20Shot%202023-04-19%20at%203.19.57%20PM.png)

![](/images/Screen%20Shot%202023-04-19%20at%203.31.52%20PM.png)

## Step 2. Data Exploration: Initial EDA and Data Cleaning

*   Understand variables

*   Clean the dataset (missing data, redundant data, outliers)

**Gather basic information about the data**

![](/images/Screen%20Shot%202023-04-19%20at%203.38.18%20PM.png)

**Gather descriptive statistics about the data**

****![](/images/Screen%20Shot%202023-04-19%20at%203.38.32%20PM.png)

**Rename columns**
As a data cleaning step, we need to rename some columns.  The column names were standardized - all in snake_case, correct column names that were misspelled, also column names were made more concise.

![](/images/Screen%20Shot%202023-04-19%20at%203.47.56%20PM.png)

**Check for any missing values in the data**

****![](/images/Screen%20Shot%202023-04-19%20at%204.07.39%20PM.png)

![](/images/Screen%20Shot%202023-04-19%20at%204.08.56%20PM.png)

**Check for outliers in the data**

![](/images/Screen%20Shot%202023-04-19%20at%204.10.27%20PM.png)

![](/images/Screen%20Shot%202023-04-19%20at%204.11.17%20PM.png)

## Step 3. Continue EDA: Analyze Relationships Between Variables

Now, I started examining the variables and creating plots to visualize relationships between variables in the data, comparing employees who stayed versus those who left

### **Number of projects and average monthly hours**

**First,** I created a stacked boxplot (visualizing distributions within data) showing `average_monthly_hours` distributions for `number_project`,  comparing the distributions of employees who stayed versus those who left.

Also, I plotted a stacked histogram to visualize the distribution of `number_project` those employees who stayed and those who left, because, although box plots are very useful in visualizing distributions within data, they can be deceiving without the context of how big the sample sizes that they represent are.

![](/images/Screen%20Shot%202023-04-19%20at%204.21.54%20PM.png)

It might be natural that people who work on more projects would also work longer hours. This appears to be the case here, with the mean hours of each group (stayed and left) increasing with the number of projects worked. However, a few things stand out from this plot:

1.  There are **two groups of employees** who left the company: **(A)** those who worked considerably less than their peers with the same number of projects, and **(B)** those who worked much more. Of those in group A, it's possible that they were fired. It's also possible that this group includes employees who had already given their notice and were assigned fewer hours because they were already on their way out the door. For those in group B, it's reasonable to infer that they probably quit. The folks in group B likely contributed a lot to the projects they worked on; they might have been the largest contributors to their projects.

2.  Everyone with **7 projects** left the company, and the interquartile ranges of this group and those who left with six projects was ~255–295 hours/week—much more than any other group.

3.  The **optimal number of projects** for employees to work on seems to be **3–4**. The ratio of left/stayed is very small for these cohorts.

4.  If we assume a work week of 40 hours and two weeks of vacation per year, then the average number of working hours per month of employees working Monday–Friday `= 50 weeks * 40 hours per week / 12 months = 166.67 hours per month`. This means that, aside from the employees who worked on two projects, every group—even those who didn't leave the company—worked considerably more hours than this. **It seems that employees here are overworked.**

As the next step, I needed to confirm that all employees with 7 projects left:

![](/images/Screen%20Shot%202023-04-19%20at%204.26.38%20PM.png)

This confirms that all employees with 7 projects did leave.

### **Average monthly hours versus the satisfaction levels**

![](/images/Screen%20Shot%202023-04-19%20at%204.33.04%20PM.png)

The scatterplot above shows that there was a **sizeable group of employees who worked ~240–315 hours per month**. 315 hours per month is over 75 hours per week for a whole year. **It's likely this is related to their satisfaction levels being close to zero.**

The plot also shows **another group of people who left**, those who had more **normal working hours**. Even so, their **satisfaction** was only around **0.4**. It's difficult to speculate about why they might have left. It's possible they felt pressured to work more, considering so many of their peers worked more. And that pressure could have lowered their satisfaction levels.

Finally, there is a **group who worked ~210–280 hours** per month, and they had **satisfaction** levels ranging **~0.7–0.9.**

### **Satisfaction levels and tenure**

It might be interesting to visualize satisfaction levels by tenure:

![](/images/Screen%20Shot%202023-04-19%20at%204.43.35%20PM.png)

**My observations from this plot:**

*   Employees who **left** fall into two general categories: **dissatisfied** employees with **shorter tenures** and very **satisfied** employees with **medium-length tenures**.

*   **Four-year** employees who left seem to have an unusually **low** satisfaction level. It's worth investigating changes to company policy that might have affected people specifically at the four-year mark, if possible.

*   The **longest-tenured** employees **didn't leave**. Their satisfaction levels aligned with those of newer employees who stayed.

*   The histogram shows that there are relatively few longer-tenured employees. It's possible that they're the higher-ranking, higher-paid employees.

### **Mean and median satisfaction scores of employees who left and those who didn't**

![](/images/Screen%20Shot%202023-04-19%20at%204.47.56%20PM.png)

As expected, the mean and median satisfaction scores of employees who **left** are **lower** than those of employees who stayed. Interestingly, among employees who stayed, the mean satisfaction score appears to be slightly below the median score. This indicates that satisfaction levels among those who stayed might be skewed to the left.

### **Salary levels and different tenures**

Let's examine salary levels for different tenures:

![](/images/Screen%20Shot%202023-04-19%20at%204.50.03%20PM.png)

The plots above show that **long-tenured** employees were **not disproportionately comprised** of **higher-paid** employees.

### **Average monthly hours and  evaluation scores**

Next, I explored whether there's a correlation between working long hours and receiving high evaluation scores. I created a scatterplot of `average_monthly_hours` vs `last_evaluation:`

``![](/images/Screen%20Shot%202023-04-19%20at%204.53.19%20PM.png)

**The following observations can be made from the scatterplot above:**

*   The scatterplot indicates two groups of employees who left: **overworked** employees who performed very **well** and employees who worked slightly **under** the nominal monthly average of 166.67 hours with **lower** evaluation scores.

*   There seems to be a **correlation** between **hours** worked and **evaluation** **score**.

*   There isn't a **high percentage** of employees in the **upper left** quadrant of this plot, but working long hours doesn't guarantee a good evaluation score.

*   Most of the employees in this company work well over **167** hours per month.

### **Average monthly hours and promotion**

Next, let's examine whether employees who worked very long hours were promoted in the last five years:

![](/images/Screen%20Shot%202023-04-19%20at%204.57.27%20PM.png)

The plot above shows the following:

*   very few employees who were promoted in the last five years left

*   very few employees who worked the most hours were promoted

*   all of the employees who left were working the longest hours

### **Distribution of employees who left across departments**

Next, I  inspected how the employees who left are distributed across departments:

![](/images/Screen%20Shot%202023-04-19%20at%205.01.18%20PM.png)

![](/images/Screen%20Shot%202023-04-19%20at%205.01.36%20PM.png)

There doesn't seem to be any department that differs significantly in its proportion of employees who left to those who stayed.



### **Correlation heatmap between variables in the dataset**

Lastly, I  checked for strong correlations between variables in the data:

![](/images/Screen%20Shot%202023-04-19%20at%205.04.36%20PM.png)

The correlation heatmap confirms that the **number of projects**, **monthly hours**, and **evaluation scores** all have some positive correlation with each other, and whether an employee leaves is negatively correlated with their **satisfaction level**.



### **Insights**

It appears that employees are leaving the company as a result of **poor management**. Leaving is tied to longer **working hours**, **many projects**, and generally **lower satisfaction levels**. 

It can be ungratifying to work long hours and not receive promotions or good evaluation scores. There's a sizeable group of employees at this company who are probably burned out. 

It also appears that if an employee has spent more than six years at the company, they tend not to leave.





Employee Retention. Part 1: Exploratory Data Analysis

NYC Citi Bike Share Program Analysis. Part 2: The findings report

## Project background

Since 2013, the **Citi Bike Program** has implemented a robust infrastructure for collecting data on the program's utilization. Through the team's efforts, each month bike data is collected, organized, and made public on the ["Citi Bike Data"](https://ride.citibikenyc.com/system-data) webpage.

This project involved extracting a few **NYC Citi Bike datasets** and using to visualize and determine answers to company leadership questions.

The data was extracted from the website, transformed in a **Jupyter Notebook** using **Python** and **Pandas** then cleaned in a **BigQuery** using **SQL** queries and pushed out to a combined csv to then load into **Tableau**.

Each of the visualizations created contains interactive features for users to explore the different trends related to short-term customers and annual Citi Bike members, peak hours and days of the week, and the breakdown of bike types and seasons (winter and summer).

A report of findings you can read [here](/blog/python-dashboard).

<hr>

**Some of the questions answered include:**

*   How many trips have been recorded total during the chosen period?

*   By what percentage has total ridership grown?

*   How has the proportion of short-term customers and annual members changed?

*   What are the peak hours in which bikes are used during summer months?

*   What are the peak hours in which bikes are used during winter months?

*   What are the top 10 stations in the city for starting a journey/ ending a journey?

*   What are the bottom 10 stations in the city for starting a journey/ ending a journey?

*   What is the bikes type breakdown of all participants, short-term customers and annual members?

*   How does the average trip duration change?

<hr>

**Datasets Downloaded:** (3 months of Winter 2021-2022 & 3 months of Summer 2022)

*   202112-citibike-tripdata.csv
*   202201-citibike-tripdata.csv
*   202202-citibike-tripdata.csv
*   202206-citibike-tripdata.csv
*   202207-citibike-tripdata.csv
*   202208-citibike-tripdata.csv

**Note:** The datasets used are not included in this folder due to storage space, but can be downloaded from this [website](https://ride.citibikenyc.com/system-data) directly.

<hr>

## Performing analysis

### Transforming dataset with Python and Pandas

*   Use Pandas to calculate the difference between two dates (Started time and Ended time).
    Check the format of the data:
    `df.info()`

![](/images/001.png)

*   Convert the dates to datetime format:

```
df['started_at'] = pd.to_datetime(df['started_at'], errors='coerce')
df['ended_at'] = pd.to_datetime(df['ended_at'], errors='coerce')
```

*   Calculate the difference between two dates as a timedelta and creating a new column 'tripduration':
    `df['tripduration'] = df['ended_at'] - df['started_at']`

*   Calculate the difference between two dates in seconds and creating a new column 'tripduration_seconds':
    `df['tripduration_seconds'] = (df['ended_at'] - df['started_at']).dt.total_seconds()`

![](/images/002.png)

*   Export the Dataframe as a new CSV file

Repeate for all 6 files.

### Cleaning the Data with SQL

*   Upload 6 CSV files into a BigQuery tables



*   Create a new column "route":

`CONCAT(start_station_name, " - ", end_station_name) AS route`

![](/images/004.png)

*   Cleaning tables with SQL queries

Check and remove negative trip duration:

![](/images/005.png)

![](/images/006.png)

Check and remove NULL data:

![](/images/007.png)

![](/images/008.png)

*   Save new tables and export as new CSV files

### Aggregate datasets into one CSV file

Jupyter Notebook was used to merge multiple datasets into one file.

`df_winter_summer = pd.concat([df_202112, df_202201, df_202202, df_202206, df_202207, df_202208], ignore_index=True)`

As a result, one CSV file was received: `[4016535 rows x 16 columns]>`

Then the new Dataframe was exported as new CSV file for further analysis in Tableau.

### Create visualizations, design dashboards and create a Tableau story

**Glimpse of Visualizations:**

#### Popular routes and stations

![](/images/009.png)

***

#### Map of bike stations

![](/images/010.png)

***

#### Peak ridership times dashboard

![](/images/011.png)

***

#### Outliers

![](/images/012.png)

***

#### Peak trips hours during the day

## ![](/images/013.png)

#### User type breakdown analysis

![](/images/014.png)

Welcome to my portfolio website

Recent Projects

Employee Retention. Part 1: Exploratory Data Analysis

NYC Citi Bike Share Program Analysis. Part 2: The findings report

NYC Citi Bike Share Program Analysis. Part 1: Performing analysis

Data exploration of a climate database using SQLite and SQLAlchemy

Andrew Kotovets

Welcome to my portfolio website

Recent Projects

Employee Retention. Part 1: Exploratory Data Analysis

NYC Citi Bike Share Program Analysis. Part 2: The findings report

NYC Citi Bike Share Program Analysis. Part 1: Performing analysis

Data exploration of a climate database using SQLite and SQLAlchemy