<jupyter_start><jupyter_text>ETL Pipeline PreparationIn this notebook, the extract, transform, load process is executed. 1. Extract    - Extracting the data from disaster_messages.csv and disaster_categories.csv and explore the data2. Transform    - Merging the two datasets together and transforming the category columns for each of the disaster response type3. Load    - Load the data into the database (use sqlalchemy)<jupyter_code>import pandas as pd
import numpy as np
from sqlalchemy import create_engine
messages = pd.read_csv('disaster_messages.csv')
messages.head()
categories = pd.read_csv('disaster_categories.csv')
categories.head()<jupyter_output><empty_output><jupyter_text>Make sure to check that the datasets are merged correctly.<jupyter_code>df = pd.merge(messages, categories, on = 'id')
df.head()<jupyter_output><empty_output><jupyter_text>Now that the merged dataframe is correct, it is time to split the categories into individual columns and have their respective values stored within that column.<jupyter_code>categories = df['categories'].str.split(';', expand=True)
categories.head()<jupyter_output><empty_output><jupyter_text>First, let's extract the column names and store it as the category_colnames to be used later,<jupyter_code>row = categories.iloc[0,:]
category_colnames = row.apply(lambda x: x[:-2]).tolist()
category_colnames
categories.columns = category_colnames
categories.head()<jupyter_output><empty_output><jupyter_text>After assigning the appropriate column names, it is essential to extract the classification types (0 OR 1) in each column. The following cell demonstrates the for loop in extracting the final element of the string in each column and assigning the type as integers.<jupyter_code>for column in categories:
    categories[column] = categories[column].str.get(-1)
    categories[column] = categories[column].astype(int)
    
categories.head()
df = df.drop('categories', axis = 1)
df.head()<jupyter_output><empty_output><jupyter_text>After dropping the categories column from df dataframe, we can concat the original df dataframe with the new categories dataframe including the correct classification information.<jupyter_code>df = pd.concat([df, categories], axis = 1)
df.head()
df[df.duplicated() == True]<jupyter_output><empty_output><jupyter_text>The cell above checks whether the dataframe contains any duplicates. As can be seena above, there are roughly 170 rows that are categorized to be duplicates.<jupyter_code>df = df.drop_duplicates()
df[df.duplicated() == True]<jupyter_output><empty_output><jupyter_text>After dropping the duplicates, all of the dataframe entries are unique and ready to be load into the database, 'DisasterResponse.db'.<jupyter_code>engine = create_engine('sqlite:///DisasterResponse.db')
df.to_sql('disaster_cleaned', engine, index = False, if_exists = 'replace')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08980582524271845, 'fraction_numerical': 0.004507628294036061, 'mean_word_length': 6.734584450402145, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '334297', 'license': 'FTL', 'n_tokens_mistral': 841, 'n_tokens_neox': 810, 'n_words': 276, 'path': 'data/ETL_pipeline.ipynb', 'repo_name': 'prabowst/Udacity-disaster-response', 'size': '2884'}