This project was an introduction tp performing an Extract, Transform, Load (ETL). The data set Movie data originates from Wikipedia, Kaggle and implement a rating systsem on order to create a movie database from a clean dataset. The cleaning set incorporates cleaning rows and formating datatypes, preforming joins, and loading the cleaned dataset into a SQL database.
Our intention is to create an automated pipline that can ETL proccess we previously did manually. A synopsisv of the movie dataset comes from 1990 to 2018 and combining the information from 3 different resources.
- Data is extracted from the website in JSON and CSV formats.
-Data is transformed into Pandas data frames.
-JSON file requires extra step โ loading file first and then transforming into data frame.
- Function clean_movie combines scattered data of alternative languages into one column alt_titles.
- Its subfunction change_column_name organizes column names into consistent pattern.
- In the function extract_transform_load the transformation process of wiki movies data begins and includes:
- Function extract_transform_load gets new tasks for cleaning Kaggle data and includes:
- Changing datatypes, using methods pd.to_numeric, astype() and python comparison operators for Boolean types.
- Filling missing values and filtering unwanted columns.
- Merging data frames using pd_merge method.
- The function in its final step connects to the database by sqlalchemy library and to_sql method.
- Complete ETL process can be executed with a single function extract_transform_load call.
The final results created a movies table with 6,052 rows. We see a reduction by 17% from the original of 7,311 and a ratings table with 26,024,289 rows.
- Software: Python 3.7.9, Anaconda 4.9.2, Jupyter Notebooks 6.1.4, PostgreSQL 4.28
- Libraries: Pandas, SQLAlchemy, NumPy
- Files: Wikipedia Json, Movie Database Metadata, and MovieLens Ratings