Movies-ETL

Extract, Transform, Load

Purpose

1. Create an ETL pipeline from raw data to a SQL database.
2. Extract data from disparate sources using Python.
3. Clean and transform data using Pandas.
4. Use regular expressions (Regex) to parse data and to transform text into numbers.
5. Load data with PostgreSQL and verify in PgAdmin.

The project included extracting a large data set from Kaggle, then transforming the data into a usable dataset for a "hacking competition." Once the data was transformed and narrowed in scope for the hack-a-thon, the DataFrames were loaded into PostgresSQL.

Extracting

Wikipedia Movies JSON file, starting with 193 Columns:

Kaggle Movie Metadata, 24 columns

Kaggle Ratings data, 2602489 rows by 4 columns

Transforming

Wikipedia Data

Wikipedia Movies transformed, 22 columns

Wikipedia Movies, making the column names more succinct and uniform, 7033 rows of data.

Kaggle Data

Wikipedia Movies merged with Kaggle Movies data, all column names and row counts, 6052 rows.

Merged Movies with Kaggle ratings, all of the column names and row counts, 6052 rows.

Loading

Creating the Movie Database

Sending the data to PostgresSQL

Verifying the data in PgAdmin

Movies Query

Ratings Query

Summary

A JSON file and 2 Kaggle files were extracted, then transformed, and joined. A movies and ratings file were loaded into a database for the hack-a-thon event.