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:
  • Python list comprehensions.
  • apply() and map() methods in combination with lambda functions.
  • regular expressions or RegEx.

• 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