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**

*   Built a Predictive Model that can predict whether or not an employee will leave the company and help identify factors that contribute to leaving

## **Logistic Regression model assumptions**

*   Outcome variable is categorical

*   Observations are independent of each other

*   No severe multicollinearity among X variables

*   No extreme outliers

*   Linear relationship between each X variable and the logit of the outcome variable

*   Sufficiently large sample size

## **During the project, I tried to answer these questions:**

*   Do you notice anything odd?

*   Which independent variables did you choose for the model and why?

*   Is each of the assumptions met?

*   How well does your model fit the data?

*   Can you improve the model? Is there anything you would change about the model?

## **Model Building (Logistic Regression)**

Fit a model that predicts the outcome variable using two or more independent variables.

### Identify the type of prediction task

The goal is to predict whether an employee leaves the company, which is a categorical outcome variable. So this task involves classification. More specifically, this involves binary classification, since the outcome variable `left` can be either 1 (indicating employee left) or 0 (indicating employee didn't leave).

### Identify the types of models most appropriate for this task

Since the variable we want to predict (whether an employee leaves the company) is categorical, we need to build a **Logistic Regression model**. The binomial logistic regression suits our task because it involves binary classification.

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

Next, I created a heatmap to visualize how correlated variables are and consider which variables are interesting for examining correlations between them.

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

Next, I created a stacked bart plot to visualize number of employees across departments, comparing those who left with those who didn't:

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

Since logistic regression is quite sensitive to outliers, it would be a good idea at this stage to remove the outliers in the `tenure` column that were identified earlier (Part 1: EDA of this dataset).

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

Next, I isolated the outcome variable, which is the variable we want our model to predict:

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

Next, I selected the features we want to use in our model. On this step we also need to consider which variables will help us predict the outcome variable, `left`

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

Next, we need to split the data into a training set and a testing set:

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

Then construct a logistic regression model and fit it to the training dataset:

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

After that,  we need to test the logistic regression model (we have to use the model to make predictions on the test set):

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

## **Results**

Next, I created a confusion matrix to visualize the results of the logistic regression model:

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

The upper-left quadrant displays the number of true negatives.

The upper-right quadrant displays the number of false positives.

The bottom-left quadrant displays the number of false negatives.

The bottom-right quadrant displays the number of true positives.

True negatives: The number of people who did not leave that the model accurately predicted did not leave.

False positives: The number of people who did not leave the model inaccurately predicted as leaving.

False negatives: The number of people who left that the model inaccurately predicted did not leave

True positives: The number of people who left the model accurately predicted as leaving

A perfect model would yield all true negatives and true positives, and no false negatives or false positives.

## **Model Evaluation**

Next, I created a classification report that includes precision, recall, f1-score, and accuracy metrics to evaluate the performance of the logistic regression model.

In this step, we have to check the class balance in the data - check the value counts in the `left` column. Since this is a binary classification task, the class balance informs the way we interpret accuracy metrics.

***

There is an approximately 83%-17% split. So the data is not perfectly balanced, but it is not too imbalanced. If it was more severely imbalanced, you might want to resample the data to make it more balanced. In this case, we can use this data without modifying the class balance and continue evaluating the model.

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

*Our evaluation metrics:*

*   **AUC** is the area under the ROC curve; it's also considered the probability that the model ranks a random positive example more highly than a random negative example.

*   **Precision** measures the proportion of data points predicted as True that are actually True, in other words, the proportion of positive predictions that are true positives.

*   **Recall** measures the proportion of data points that are predicted as True, out of all the data points that are actually True. In other words, it measures the proportion of positives that are correctly classified.

*   **Accuracy** measures the proportion of data points that are correctly classified.

*   **F1-score** is an aggregation of precision and recall.

## **Results**

The classification report shows that the logistic regression model achieved a precision of 80%, recall of 83%, f1-score of 80% (all weighted averages), and accuracy of 83%, on the test set.

## **Conclusion and Recommendations**

The models and the feature importances extracted from the models confirm that employees at the company are overworked.

To retain employees, the following recommendations could be presented to the stakeholders:

*   Cap the number of projects that employees can work on.

*   Consider promoting employees who have been with the company for at least four years, or conduct a further investigation about why four-year tenured employees are so dissatisfied.

*   Either reward employees for working longer hours, or don't require them to do so.

*   If employees aren't familiar with the company's overtime pay policies, inform them about this. If the expectations around workload and time off aren't explicit, make them clear.

*   Hold company-wide and within-team discussions to understand and address the company work culture, across the board and in specific contexts.

*   High evaluation scores should not be reserved for employees who work 200+ hours per month. Consider a proportionate scale for rewarding employees who contribute more/put in more effort.



Employee Retention. Part 2: Predictive Modeling

## **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

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

Data exploration of a climate database using SQLite and SQLAlchemy

## Project goal

Analyze Amazon reviews written by members of the paid Amazon Vine program to determine if having a paid Vine review makes a difference in the percentage of 5-star reviews.

## Tools,  Dataset , Project Description

The Extract, Transform, and Load (ETL) process was used on the Amazon Shoes Department dataset.
PgAdmin was utilized to connect to Amazon Web Service (AWS),
PySpark and PostgreSQL were used against the Dataset to create four separate DataFrames to match the table schema in PgAdmin.
The transformed data was then uploaded into AWS RDS.

This project focused on practicing the ETL (Extract, Transform, and Load) process in the cloud. The Amazon Reviews dataset with reviews of a specific product was selected from an AWS resource (approximately 50 datasets).

The ETL process was performed to extract the datasets, transform the data to match SQL schemas, connect and loaded into an AWS RDS instance, and then load into pgAdmin.

Then, PySpark, Pandas, and SQL were used to determine if there was any bias toward favorable reviews from Vine members in the datasets.

Finally, a summary of the analysis was written.

## Topics Explored

*   Big data and the challenges associated with it.
*   Hadoop and the main elements of its ecosystem.
*   How MapReduce processes data.
*   Spark and how it processes data.
*   How NLP collects and analyzes text data.
*   How to use AWS Simple Storage Service (S3) and relational databases for basic cloud storage.
*   Analysis of an Amazon customer review.

## Project implementation

### Cloud Storage with S3 on AWS

#### AWS's Simple Storage Service

S3 is Amazon's cloud file storage service that uses key-value pairs. Files are stored on multiple servers and have a high rate of availability of more than 99.9%. To store files, S3 uses buckets, which are similar to folders or directories on your computer. Buckets can contain additional folders and files. Each bucket must have a unique name across all of AWS.

One of S3's perks is its fine-grained control over files. Each file or bucket can have different read and write permissions, which helps regulate what can be done with each file.

S3 is also very scalable—you are not limited to the memory of one computer. As data flows in, more and more can be stored, as opposed to a local computer that is limited by available memory. Additionally, it offers availability—several team members can access massive amounts of data from one central location.

#### PySpark and S3 Stored Data

Since PySpark is a big data tool, it has many ways of reading files from data storage so that we can manipulate them. We have decided to use S3 as our data storage, so we'll use PySpark for all data processing.

Using PySpark is how we've been reading our data into Google Colab. The format for reading in from S3 is the S3 link, followed by your bucket name, folder by each folder, and then the filename, as follows:

For US East (default region)

template\_url = "https://<bucket-name>.s3.amazonaws.com/<folder-name>/<file-name>"

example\_url = "https://dataviz-curriculum.s3.amazonaws.com/data-folder/data.csv"
For other regions

template\_url = "https://<bucket-name.s3-<region>.amazonaws.com/<folder-name>/<file-name>"

example\_url =" https://dataviz-curriculum.s3-us-west-1.amazonaws.com/data-folder/data.csv"

#### PySpark ETL (Extract, Transform, and Load)

Our goal is to get this raw data from S3 into an RDS database.

We already have three tables set up in the RDS database and would like to get the raw data from S3 into the database. Create a new database in pgAdmin called "my\_data\_class\_db." We'll have it represent the  database by first running the following schema in pgAdmin for our RDS:

```
-- Create Active User Table
CREATE TABLE active_user (
 id INT PRIMARY KEY NOT NULL,
 first_name TEXT,
 last_name TEXT,
 username TEXT
);

CREATE TABLE billing_info (
 billing_id INT PRIMARY KEY NOT NULL,
 street_address TEXT,
 state TEXT,
 username TEXT
);

CREATE TABLE payment_info (
 billing_id INT PRIMARY KEY NOT NULL,
 cc_encrypted TEXT
);
```

**NOTE**
Table creation is not part of the ETL process. We're creating the tables to represent a pre-established database you need for the raw data. In a real-life situation, databases will already have a well-defined schema and tables for you, as the engineer, to process data into.

Start with creating a new notebook, and installing Spark:

```
import os
# Find the latest version of spark 3.0  from http://www-us.apache.org/dist/spark/ and enter as the spark version
# For example:
# spark_version = 'spark-3.0.2'
spark_version = 'spark-3.<enter version>'
os.environ['SPARK_VERSION']=spark_version

# Install Spark
!apt-get update
!apt-get install openjdk-11-jdk-headless -qq > /dev/null
!wget -q http://www-us.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop2.7.tgz
!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz
!pip install -q findspark

# Set Environment Variables
import os
os.environ["JAVA_HOME"] = "/usr/lib/jvm/java-11-openjdk-amd64"
os.environ["SPARK_HOME"] = f"/content/{spark_version}-bin-hadoop2.7"

# Start a SparkSession
import findspark
findspark.init()
```

We use Spark to write directly to our Postgres database. But in order to do so, there are a few more lines of code we need.

First, enter the following code to download a Postgres driver that will allow Spark to interact with Postgres:

```
!wget https://jdbc.postgresql.org/download/postgresql-42.2.16.jar
```

You should get a message containing the words "HTTP request sent, awaiting response… 200 OK," indicating that your request was processed without a problem.
Then, start a Spark session with an additional option that adds the driver to Spark:

```
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("CloudETL").config("spark.driver.extraClassPath","/content/postgresql-42.2.16.jar").getOrCreate()
```

We have performed the first two steps of the ETL process before with PySpark, so let's quickly review those.

**Extract**
We can connect to data storage, then extract that data into a DataFrame. We'll do this on two datasets, and be sure to replace the bucket name with one of your own.

We'll start by importing SparkFiles from PySpark into our notebook. This will allow Spark to add a file to our Spark project.

Next, the file is read in with the read method and combined with the csv() method, which pulls in our CSV stored in SparkFiles and infers the schema. SparkFiles.get() will have Spark retrieve the specified file, since we are dealing with a CSV.  The "," is the chosen separator, and we will have Spark determine the head for us. Enter the following code:

```
# Read in data from S3 Buckets
from pyspark import SparkFiles
url ="https://YOUR-BUCKET-NAME.s3.amazonaws.com/user_data.csv"
spark.sparkContext.addFile(url)
user_data_df = spark.read.csv(SparkFiles.get("user_data.csv"), sep=",", header=True, inferSchema=True)
```

Finally, an action is called to show the first 10 runs and confirm our data extraction by entering the following code:

```
# Show DataFrame
user_data_df.show()
```

Repeat a similar process to load in the other data. Enter the code:

```
url ="https://YOUR-BUCKET-NAME.s3.amazonaws.com/user_payment.csv"
spark.sparkContext.addFile(url)
user_payment_df = spark.read.csv(SparkFiles.get("user_payment.csv"), sep=",", header=True, inferSchema=True)

# Show DataFrame
user_payment_df.show()
```

**Transform**
Now that the raw data stored in S3 is available in a PySpark DataFrame, we can perform our transformations.

First, join the two tables:

![](/images/01_Join-Two-DataFrames.png)

Next, drop any rows with null or "not a number" (NaN) values:

![](/images/02_Drop-Null-Values.png)

Filter for active users:

![](/images/03_Load-Sql-Function.png)

Next, select columns to create three different DataFrames that match what is in the AWS RDS database. Create a DataFrame to match the active\_user table:

![](/images/04_Create-User-Dataframe-Active-User-Table.png)

Next, create a DataFrame to match the billing\_info table:

![](/images/05-Create-User-DataFrame-Match-Billing-Info-Table.png)

Finally, create a DataFrame to match the payment\_info table:

![](/images/06-Create-User-DataFrame-Match-Payment.png)

Once our data has been transformed to fit the tables in our database, we're ready to move on to the "Load" step.

**Load**

The final step is to get our transformed raw data into the database. PySpark can easily connect to a database to load the DataFrames into the table. First, we'll do some configuration to allow the connection with the following code:

```
# Configure settings for RDS
mode = "append"
jdbc_url="jdbc:postgresql://<connection string>:5432/<database-name>"
config = {"user":"postgres",
          "password": "<password>",
          "driver":"org.postgresql.Driver"}
```

We'll need to provide a username and password, and also supply the AWS server name where `<connection string>` is located in the code above. To find it in PgAdmin, right-click AWS in the Server directory listing on the left side of PgAmin, and then select Properties in the drop-down menu. Select the Connection tab in the window that opens, and then select the address in the Host name/address field. Copy that address and paste it in place of `<connection string>`.

Let's further break down what's happening here:

*   `mode` is what we want to do with the DataFrame in the table, such as `overwrite` or `append`. We'll append it to the current table because every time we run this ETL process, we'll want more data added to our database without removing any.
*   The `jdbc_url` is the connection string to our database.



*   Replace `<connection string>` with the endpoint connection url found from the AWS RDS console.
*   Replace `<database name>` with the name of your database, you wish to connect to.



*   A dictionary of configuration that includes the `user`, `password`, and `driver` to what type of database is being used.



*   The `user` field is the username for your database, which should be `postgres` if you followed with the creation of the RDS instance. Otherwise, enter the one you created.
*   The `password` would be the password you created when making the RDS instance.

**NOTE**

> If you forget anything like the name of the database or user name you can check on pgAdmin for these values. Be sure that you are entering the name of the database and not the name of your server in the connection string.

The cleaned DataFrames can then be written directly to our database by using the `.write.jdbc` method that takes in the parameters we set:

The connection string stored in `jdbc_url` is passed to the URL argument.
The corresponding name of the table we are writing the DataFrame to.
The mode we're using, which is "append."
The connection configuration we set up passed to the properties.
The code is as follows:

```
# Write DataFrame to active_user table in RDS
clean_user_df.write.jdbc(url=jdbc_url, table='active_user', mode=mode, properties=config)

# Write dataframe to billing_info table in RDS
clean_billing_df.write.jdbc(url=jdbc_url, table='billing_info', mode=mode, properties=config)

# Write dataframe to payment_info table in RDS
clean_payment_df.write.jdbc(url=jdbc_url, table='payment_info', mode=mode, properties=config)

```

Double-check our work and running queries in pgAdmin on our database to confirm that the load did exactly what we wanted:

```
-- Query database to check successful upload
SELECT * FROM active_user;
SELECT * FROM billing_info;
SELECT * FROM payment_info;
```

## Perform ETL on Amazon Product Reviews

Using the cloud ETL process,  create an AWS RDS database with tables in pgAdmin, pick a dataset from the [Amazon Review datasets](https://s3.amazonaws.com/amazon-reviews-pds/tsv/index.txt), and extract the dataset into a DataFrame.&#x20;

Then, transform the DataFrame into four separate DataFrames that match the table schema in pgAdmin.&#x20;

Then,  upload the transformed data into the appropriate tables and run queries in pgAdmin to confirm that the data has been uploaded.

**Steps:**

*   From the following [Amazon Review datasets](https://s3.amazonaws.com/amazon-reviews-pds/tsv/index.txt), pick a dataset that you would like to analyze. All the datasets have the same schemata, as shown in this image:

![](/images/07_1.png)

*   Create a new database with Amazon RDS.

*   In pgAdmin, create a new database in your Amazon RDS server that you just create.



*   Use the SQL queries :

![](/images/schema_sql.png)

*   In pgAdmin, run a new query to create the tables for your new database using the SQL code from the previous step.



*   After you run the query, we will have the following four tables in the database: customers\_table, products\_table, review\_id\_table, and vine\_table.



*   Download the `Amazon_Reviews_ETL_starter_code.ipynb` file, then upload the file as a Google Colab Notebook, and rename it `Amazon_Reviews_ETL`.

**NOTE**

> If you try to open the `Amazon_Reviews_ETL_starter_code.ipynb` with jupyter notebook it will give you an error.

7.  First **extract** one of the review datasets, then create a new DataFrame.
8.  Next, follow the steps below to **transform** the dataset into four DataFrames that will match the schema in the pgAdmin tables:

**NOTE**

> Some datasets have a large number of rows, which will affect the time it takes to complete the following steps.

**The customers\_table DataFrame**
To create the `customers_table`, use the code in the `Amazon_Reviews_ETL_starter_code.ipynb` file and follow the steps below to aggregate the reviews by `customer_id`.

*   Use the `groupby()` function on the customer\_id column of the DataFrame you created in Step 6.
*   Count all the customer ids using the `agg()` function by chaining it to the `groupby()` function. After you use this function, a new column will be created, `count(customer_id)`.
*   Rename the `count(customer_id)` column using the `withColumnRenamed()` function so it matches the schema for the `customers_table` in pgAdmin.
*   The final `customers_table` DataFrame:

![](/images/08_2.png)

**The products\_table DataFrame**
To create the `products_table`, use the `select()` function to select the `product_id` and `product_title`, then drop duplicates with the `drop_duplicates()` function to retrieve only unique `product_ids`. Refer to the code snippet provided in the `Amazon_Reviews_ETL_starter_code.ipynb` file for assistance.

The final `products_table` DataFrame:

![](/images/09_3.png)

**The review\_id\_table DataFrame**
To create the `review_id_table`, use the `select()` function to select the columns that are in the `review_id_table` in [pgAdmin](https://docs.github.com/en/github/authenticating-to-github/removing-sensitive-data-from-a-repository), and convert the review\_date column to a date using the code snippet provided in the `Amazon_Reviews_ETL_starter_code.ipynb` file.

The final `review_id_table` DataFrame:

![](/images/10_4.png)

**The vine\_table DataFrame**
To create the `vine_table`, use the `select()` function to select only the columns that are in the `vine_table` in [pgAdmin](https://docs.github.com/en/github/authenticating-to-github/removing-sensitive-data-from-a-repository).

The final `vine_table` DataFrame:

![](/images/11_5.png)

**Load the DataFrames into pgAdmin**

1.  Make the connection to your AWS RDS instance.
2.  Load the DataFrames that correspond to tables in pgAdmin.
3.  In pgAdmin, run a query to check that the tables have been populated.

Then, export the `Amazon_Reviews_ETL` Google Colab Notebook as an ipynb file.

### RESULTS:

**Helpful Reviews (All) with 5 Star:**

For all reviews and "helpful" reviews, **around half of the ratings are 5 stars**, which indicates that the Vine programs tend to give 5 Stars over any other rating.

![](/images/12.png)

**Percentage of Vine Reviews are 5-star:**

For all the Vine Reviews, we found almost the same, a little lower ratings than 5 Star.

![](/images/13.png)

**Percentage of Non-Vine Reviews are 5-star:**
In General, the non-Vine reviews is higher of 5 Stars on non-Vine reviews than 5 Star Vine.

![](/images/14.png)

**Vine Review vs. Non-Vine Review**:
For the entire Furniture product review file, the majority has a small Amazon Vine review:

![](/images/15.png)

Now, applying the same analysis over smaller dataset, with "helpful" reviews, we faound an average percentage from the Vine program:

![](/images/16.png)

**5 Star Reviews Vine vs Non-Vine:** For the entire review dataset, we found a small 5 Star reviews from Vine reviews, **around 0.3%**

![](/images/17.png)

By Filtering the "helpful" reviews only, we saw and found a light difference; **a lower 1%** of the 5 Star review from Vine.

![](/images/18.png)

### **SUMMARY**

1.  The majority of reviews for Furniture products are almost nothing or lower results from Vine participants: **99.6% are Non-Vine**.
2.  And overall all 5 Star reviews are also the same as the Furniture, all are from Vine participants: **99.7% of all 5-star reviews are non-Vine**.
3.  But we need to highlight that not all of the 5 Star reviews are coming from Vine participants.

In addition, we found that much data isn't Vine reviews over specific products, that we could minimize the resluts and create a different dataset on just Vine products.

The analysis gave us that **1/4 are Vine Reviews**

***

![](/images/19.png)

Specific Product provide an average of **57% 5 Star reviews**

***

![](/images/20.png)

For the majority of Vine Reviews, the analysis provide a **49% of 5 Star reviews**

***

![](/images/21.png)

And for the majority of the non-Vine Reviews, the analysis provide a **60% of 5 Star reviews**

***

![](/images/22.png)

Analyzing Big Data using PySpark,  PgAdmin and Amazon Web Services

## **Project Overview and Purpose**

*   To build a database using PostgreSQL and create queries to discover insights about the employees.
*   Connect the SQL database to Pandas to create graphs and visualize the data.

## **Results**

### Create an ERD

![](/images/ERD_Hr_db2.png)

### Create Tables in Postgres SQL

![](/images/001_create_tables.png)

![](/images/002_create_tables.png)

### Queries Examples for Analyzing the Database

![](/images/003_Queries.png)

![](/images/004_Queries.png)

![](/images/005_Queries.png)

### Create a connection between Postgres and Python

![](/images/006_hrdb.png)

![](/images/007_hrdb.png)

### Create Graphs Salary Count

![](/images/008_hrdb.png)

![](/images/salary_count_hr02.png)

### Create Histogram

![](/images/009_hrdb.png)&#x20;

![](/images/histogram_hr01-d9127292.png)

### Create queries

![](/images/010_hrdb.png)

![](/images/011_hrdb.png)

### Create an Average Salaries Graph

![](/images/012_hrdb.png)

![](/images/average_salary_hr03.png)


Analyzing Employee Database Part 2: Connect and visualize data with Python

## **Project Overview and Purpose**

The company was worried about its employees who were going to go to the retirement and needed to know:

1.  Who will retire in the next few years and who will be eligible for the retirement package?

2.  How many positions will the company need to fill?

The company used Excel to work with data and decided to upgrade methods and use SQL instead. So, we had 6 CSV files with the data at the beginning, and we needed to build a new employee database with SQL.

**Projects Goals:**

Create ERD, perform data modeling, and complete analysis on an employee database using SQL techniques.

**Projects Tasks:**

*   Design entity relationship diagrams (ERDs) that will apply to the data.

*   Create an SQL database in PgAdmin.

*   Import data into a database: Import and export CSV datasets into PgAdmin.

*   Create new tables, using different joins.

*   Cleaning the data.

*   Create queries that use data to answer questions.

*   Perform analysis of the employee database.

## **Results**

### **Part 1: Create an ERD Diagram**

An entity relationship diagram was created using quick database diagrams to gather and organize the key elements from various data tables. The data was imported using Postgres and the pgAdmin interface. SQL queries were written to create data tables by joining primary keys from different data sets together and establishing foreign keys.
SQL queries were created to generate a list of retiring employees by title and a list of employees eligible for mentorship.

<div style="text-align: left">![](/images/EmployeeDB-fb39649f.png)</div>

## **Part 2: The Number of Retiring Employees by Title**

### Create a Retirement Titles table

A Retirement Titles table was made that holds all the titles of employees who were born between January 1, 1952, and December 31, 1955.

**Code:**

<div style="text-align: left">![](/images/Creating%20retirement_titles.png)</div>

**Retirement** **Table:**

<div style="text-align: left">![](/images/retirement_titles.png)</div>

The Retirement Titles table was exported as `retirement_titles.csv`

### Remove duplicates

There were many duplicate entries in the Retirement Titles table because some employees had multiple titles in the database.

Using the distinct on SQL script, the duplicates were removed leaving the most recent job title to create a unique list of retiring employees.

**Code:**

<div style="text-align: left">![](/images/remove_duplicate.png)</div>

**Unique Retirement Table:**

<div style="text-align: left">![](/images/unique_titles.png)</div>

The Unique Titles table was exported as `unique_titles.csv`

### Create a Retiring Titles table

A query was written and executed to create a Retiring Titles table that contains the number of titles filled by employees who are retiring.

Then a Retiring Titles table was created.

**Code:**

<div style="text-align: left">![](/images/retiring_titles_query.png)</div>

**Retiring Titles Table:**

<div style="text-align: left">![](/images/retiring_titles.png)</div>

The Retiring Titles table is exported as `retiring_titles.csv`

## **Part 3: The Employees Eligible for the Mentorship Program**

Using the ERD as a reference, a mentorship-eligibility table was created that holds the current employees who were born between January 1,  1965 and December 31, 1965.

### Create a Mentorship-eligibility table

**Code:**

![](/images/query_mentor_list.png)

**Table:**

![](/images/mentorship_eligibilty.png)

The Mentorship Eligibility table is exported and saved as `mentorship_eligibilty.csv`

## **Part 4: A written report on the employee database analysis**

The purpose of this analysis was to determine the number of retiring employees per title and identify employees who are eligible to participate in a mentorship program. The report helped management prepare for the “silver tsunami” as many current employees reach retirement age.

### Results:

1.  From the finding of the eligible retirees, a high percentage of the workforce could retire at any given time.

2.  From the job titles of the eligible retirees, the breakdown is below:

> 29,414 Senior Engineer
> 28,254 Senior Staff
> 14,222 Engineer
> 12,243 Staff
> 4,502 Technique Leader
> 1,761 Assistant Engineer
> 2 Manager

### Summary:

#### How many roles will need to be filled as the "silver tsunami" begins to make an impact?

**90,398** vacancies are in urgent need to be filled out as soon as the workforce starts retiring at any given time.

#### Are there enough qualified, retirement-ready employees in the departments to mentor the next generation of employees?

There are **1,549** employees eligible for the mentorship program. **90,398** vacancies will need to be filled. So the gap is significant. It looks like the company does not have enough retirees to mentor the next generation of employees. As a next step, it would be useful to have a query that indicates a list of employees who will be retired for each following year. It could help the company to prepare better for the "silver tsunami".


Analyzing Employee Database Part 1: Build and analyze database with PostgreSQL

### Project Goal

Create an ETL pipeline from raw data to a SQL database. Extract data from several sources using Python. Clean and transform data using Pandas. Load data with PostgreSQL and verify in PgAdmin.

### Step 1: Write an ETL Function to Read Three Data Files

**The Wiki\_movies\_df DataFrame:**

![](/images/wiki_movies_df.png)

**The Kaggle\_metadata DataFrame:**

![](/images/kaggle_metadata.png)

**The Ratings DataFrame:**

![](/images/ratings.png)

**File:**

[ETL\_function\_test](https://github.com/Akotovets1/Movies-ETL-2-/blob/main/ETL_function_test.ipynb)

### Step 2: Extract and Transform the Wikipedia Data

![](/images/D2_wiki_movies_df.png)

![](/images/D2_wiki_movies_df_columns.png)

The cleaned Wikipedia data is converted to a Pandas DataFrame, and the DataFrame is displayed in the [ETL\_clean\_wiki\_movies](https://github.com/Akotovets1/Movies-ETL-2-/blob/main/ETL_clean_wiki_movies.ipynb) file.

### Step 3: Extract and Transform the Kaggle data

**DataFrames:**

![](/images/D3.1_wiki_movies-df92d531.png)

![](/images/D3.2_movies_with_ratings-d7624121.png)

![](/images/D3.3_movies_df.png)

**File:**

[ETL\_clean\_kaggle\_data](https://github.com/Akotovets1/Movies-ETL-2-/blob/main/ETL_clean_kaggle_data.ipynb)

### Step 4: Create the Movie Database

**File:**

[ETL\_create\_database](https://github.com/Akotovets1/Movies-ETL-2-/blob/main/ETL_create_database.ipynb)

**The elapsed time to add the data to the database:**

![](/images/D4_elapsed_time.png)

**The movies table in the SQL database:**

![](/images/movies_query.png)

**The rating table in the SQL database:**

![](/images/ratings_query.png)
