<jupyter_start><jupyter_text>ETL Pipeline PreparationFollow the instructions below to help you create your ETL pipeline. 1. Import libraries and load datasets.- Import Python libraries- Load `messages.csv` into a dataframe and inspect the first few lines.- Load `categories.csv` into a dataframe and inspect the first few lines.<jupyter_code># import libraries
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
# loading messages dataset
messages = pd.read_csv('messages.csv')
messages.head(20)
# Some checks about messages dataset
messages.shape
# About the genre column data
messages.genre.value_counts()
# what's a message?
messages.iloc[13188].message
# loading categories dataset
categories = pd.read_csv('categories.csv')
categories.head()
# How about categories shape?
categories.shape<jupyter_output><empty_output><jupyter_text>2. Merge datasets.- Merge the messages and categories datasets using the common id- Assign this combined dataset to `df`, which will be cleaned in the following steps<jupyter_code># merging both datasets
df = messages.merge(categories, on='id', how='left')
df.head()
# The new shape
df.shape

# Well it change the number of rows, how is that?
# Let's see for unique id numbers
np.sum(np.unique(df.id, return_counts=True)[1])
# Don't get it, let me check the info, and clean it later.
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 26386 entries, 0 to 26385
Data columns (total 5 columns):
id            26386 non-null int64
message       26386 non-null object
original      10246 non-null object
genre         26386 non-null object
categories    26386 non-null object
dtypes: int64(1), object(4)
memory usage: 1.2+ MB<jupyter_text>3. Split `categories` into separate category columns.- Split the values in the `categories` column on the `;` character so that each value becomes a separate column. You'll find [this method](https://pandas.pydata.org/pandas-docs/version/0.23/generated/pandas.Series.str.split.html) very helpful! Make sure to set `expand=True`.- Use the first row of categories dataframe to create column names for the categories data.- Rename columns of `categories` with new column names.<jupyter_code># create a dataframe of the 36 individual category columns
categories = pd.DataFrame(columns=['' for x in range(36)])
categories.head()
# select the first row of the categories dataframe
row = df.loc[0, 'categories']

# use this row to extract a list of new column names for categories.
# one way is to apply a lambda function that takes everything 
# up to the second to last character of each string with slicing
category_colnames = [x[:-2] for x in row.split(';')]
print(category_colnames)
# rename the columns of `categories`
categories.columns = category_colnames
categories.head()<jupyter_output><empty_output><jupyter_text>4. Convert category values to just numbers 0 or 1.- Iterate through the category columns in df to keep only the last character of each string (the 1 or 0). For example, `related-0` becomes `0`, `related-1` becomes `1`. Convert the string to a numeric value.- You can perform [normal string actions on Pandas Series](https://pandas.pydata.org/pandas-docs/stable/text.htmlindexing-with-str), like indexing, by including `.str` after the Series. You may need to first convert the Series to be of type string, which you can do with `astype(str)`.<jupyter_code>for number, column in enumerate(categories):
    # set each value to be the last character of the string
    categories[column] = df.categories.astype(str).apply(lambda x: x.split(';')[number][-1])
    print(number,column)
    # convert column from string to numeric
    categories[column] = categories[column].apply(int)
categories.head()
# Let's do a numerical check
# for noise in some data ('related' column this time)
categories.describe()
# To fix the numerical problems, let's garantee just 0 and 1 in the data.
categories = (categories>0).astype(int)
# Duplicate the 'related' column to avoid eliminate those rows with 0 values
# the duplicated new column will have '1' where 'related' is '0'
# that warantees some issues fixed in the ML step because we are avoiding
# rows full of '0's.
categories['not_related'] = categories['related'].apply(lambda x: [1,0][x])
# For ML training purposes, let's erase the columns with '0' values, this
# could bring issues in some ML algorithms.
for col in categories.columns:
    if categories[col].sum() == 0:
        categories.drop(col, axis=1, inplace=True)
# What we have at the moment
pd.set_option('display.max_colwidth', -1)
pd.set_option('display.max_columns', None)
categories.tail()<jupyter_output><empty_output><jupyter_text>5. Replace `categories` column in `df` with new category columns.- Drop the categories column from the df dataframe since it is no longer needed.- Concatenate df and categories data frames.<jupyter_code># drop the original categories column from `df`

df.drop('categories', axis=1, inplace=True)
df.head()
# concatenate the original dataframe with the new `categories` dataframe
df = pd.concat([df, categories], axis = 1)
df.tail()<jupyter_output><empty_output><jupyter_text>6. Remove duplicates.- Check how many duplicates are in this dataset.- Drop the duplicates.- Confirm duplicates were removed.<jupyter_code># check number of duplicates
df.duplicated('id').value_counts()
# drop duplicates
df_dropped = df.drop_duplicates()
# check number of duplicates
df_dropped.duplicated('id').value_counts()
# Let's understand what is happening with the duplicated data
df_dropped[df_dropped.duplicated('id', keep=False)]
# They are pretty similar, Let's drop the rows with the same 'id'
# Keeping the last one.
df_dropped = df_dropped.drop_duplicates('id', keep='last')
# Showing results
df_dropped.duplicated('id').value_counts()
# Final dataset
df_dropped.shape
# Numerical check of the final dataset
df_dropped.describe()
# More checks of the final dataset
df_dropped.head()<jupyter_output><empty_output><jupyter_text>6.1. Doing something with imbalances.- Checking first the how imbalanced is the data.- Oversampling the small data.- Undersampling the biggest one.<jupyter_code># Let's check how imbalanced is the data in relation to
# 'related' column data
100*df_dropped[categories.columns].sum()/len(df)
# Checking how imbalaced the data is
df_dropped[categories.columns].sum()
# Checking the proportion of rows with only one feature value
# To analyze if we will clean these rows or not
db = df_dropped[categories.columns]
for col in categories.columns:
    d_base = db[db[col] == 1]
    print(col, (d_base.sum(axis=1)==1).sum())
# An approach to fix a little bit the imbalanced data, doing Oversampling
# for a group of features with less data
# Just a bit, too much oversampling push our model to overfit
def Oversampling_Imbalanced_Data(data, target_col, factor=2, top_data=0.05, replace=True):
    '''
    This function return the new dataset taking a sample of the existing data.
    
    Function parameters:
    
        Required:
            
            data : The original dataset to be oversampled.
            
            target_col : list ; 
                the columns of the featured data or categories to be checked.
            
        Optional:
            
            factor : float; default = 2 (2x), example: 0.0 (0x) - 10.0 (10x)
                the factor that multiplies the number of rows to be repeated.
                
            top_data : float; default = 0.05 (5%), example: 0.0 (0%) - 0.1 (10%)
                the percentage of data represented that will include the features to 
                be repeated.
                
            replace : True or False;
                This determine if wants to repeat some of the data already sampled. 
                If you don't want to repeat data more than once then the factor can't
                be more than 1.
                
        Return:
        
            new_data : the new dataset oversampled.
        
    '''

    new_data = data.copy()
    # Let's walk through every feature or category
    for col in target_col:
        # taking a sub dataframe where all rows with this column is '1'
        d_base = data[data[col] == 1]
        # If there is data and the number of rows is below the top_data value:
        if (len(d_base) > 0) and ((len(d_base)/len(data))<top_data):
            # Let's take the samples and add them to the new dataframe
            d_samples = d_base.sample(n = int(factor*len(d_base)), replace=replace)
            new_data = pd.concat([new_data, d_samples], ignore_index=True)
            
    # Returning the new oversampled dataset.
    return new_data
# Now lets define a function to make undersampling to the biggest
# features or categories represented.
# In this case we are only taking the related and not_related columns,
# deleting only the rows where there is only a '1' in their features, and
# '0' in the rest of the categories.

def Undersampling_Imbalanced_Related_Data(data, target_col, factor=0.5):
    '''
    This function return the new dataset removing a sample of the existing data
    of features than only have values in their columns alone, and '0' in the others.
    
    Function parameters:
    
        Required:
            
            data : The original dataset to be undersampled.
            
            target_col : list ;
                the columns of the featured data or categories to be checked.
            
        Optional:
            
            factor : float; default = 0.5 (50%), example: 0.0 (0%) - 1.0 (100%)
                the factor that represent the percentage been elminated from the
                existing data.
                
        Return:
        
            new_data : the new dataset undersampled.
        
    '''

    new_data = data.copy()
    # calculating the number of rows to delete in the rows that only have one value.
    # In this case they are only the related and not_related columns.
    l = int(len(new_data[new_data[target_col].sum(axis=1) == 1]) * factor)
    # Taking a sample of that data.
    d_samples = new_data[new_data[target_col].sum(axis=1) == 1].sample(n = l, replace=False)
    # Finally we create new_data dataset from the data without these rows, and the
    # sample selected.
    new_data = pd.concat([new_data[new_data[target_col].sum(axis=1) > 1],d_samples], ignore_index=True)
    
    return new_data
df_dropped.shape
# Let's do oversampling of the data that is below the 3,5% of the representation,
# and just add 70% more of that data without repeating.
dfx = Oversampling_Imbalanced_Data(df_dropped, categories.columns, factor=0.7, top_data=0.035, replace=False)
dfx.shape
# And finally some undersampling of the related features.
# Let's reduce it 60%
dfx = Undersampling_Imbalanced_Related_Data(dfx, categories.columns, 0.4)
dfx.shape<jupyter_output><empty_output><jupyter_text>7. Save the clean dataset into an sqlite database.You can do this with pandas [`to_sql` method](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html) combined with the SQLAlchemy library. Remember to import SQLAlchemy's `create_engine` in the first cell of this notebook to use it below.<jupyter_code># Saving the dataset
engine = create_engine('sqlite:///DisasterResponse.db')
dfx.to_sql('DisasterResponse', engine, index=False, if_exists='replace')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08737183419507492, 'fraction_numerical': 0.014810270791341687, 'mean_word_length': 3.6090468497576738, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 36, 'https://': 3, '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': '59852', 'license': 'MIT', 'n_tokens_mistral': 3395, 'n_tokens_neox': 3226, 'n_words': 1288, 'path': 'Notebook files/ETL Pipeline Preparation.ipynb', 'repo_name': 'CarlosArocha/disaster_response_pipeline_project', 'size': '11411'}