<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
from sqlalchemy import create_engine
# load messages dataset
messages_df = pd.read_csv("../data/disaster_messages.csv")
messages_df.head(3)
messages_df.info()
# load categories dataset
categories_df = pd.read_csv("../data/disaster_categories.csv")

categories_df.head()
categories_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 26248 entries, 0 to 26247
Data columns (total 2 columns):
 #   Column      Non-Null Count  Dtype 
---  ------      --------------  ----- 
 0   id          26248 non-null  int64 
 1   categories  26248 non-null  object
dtypes: int64(1), object(1)
memory usage: 410.2+ KB<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>print("messages_df  : "+str(messages_df.shape))
print("categories_df: "+str(categories_df.shape))
messages_df.info()
# merge datasets
df = pd.merge(messages_df, categories_df.drop_duplicates(subset=['id']), how = 'left')
df.head()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 26248 entries, 0 to 26247
Data columns (total 5 columns):
 #   Column      Non-Null Count  Dtype 
---  ------      --------------  ----- 
 0   id          26248 non-null  int64 
 1   message     26248 non-null  object
 2   original    10184 non-null  object
 3   genre       26248 non-null  object
 4   categories  26248 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 = categories_df['categories'].str.split(';', expand=True) 
categories.head() 
categories_df.info()
# select the first row of the categories dataframe
row = categories.iloc[0]

# 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 = [colname[:-2] for colname in row.tolist()]
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 column in categories:
    # set each value to be the last character of the string, converted to int
    categories[column] = categories[column].apply(lambda val : int(val[-1]) ) 

categories.head()
# Check if there are columns with values that differ from [0,1]

for column in categories:
    unique_values_set = set(categories[column].unique())
    if unique_values_set.difference( set([0,1]) ):
        print(column +' : '+ str(unique_values_set))
# How many such rows:
categories['related'][categories['related']==2].sum()<jupyter_output><empty_output><jupyter_text>We see that there is only one column ('related') that has non-0/1 values, and only a small amount of those.The rows with bad values (2) will be dropped in the end of cleaning process  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.head()<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(subset='message').sum()
# drop duplicates
duplicated = df.duplicated(subset='message')
df = df[~duplicated]
# check number of duplicates
df.duplicated().sum()
df.info()
df['related'].unique()
df[df['related']==2]
# Drop rows with 'related' values of 2 (see analysis in section 4) 

drop_index = df[df['related']==2].index
df.drop(drop_index, inplace=True)

# Check that this column is OK now
df['related'].unique()
df.shape<jupyter_output><empty_output><jupyter_text>7. Distribution of categoriesLet's see, how our messages are distributed between all categories - Find Sum in each category column (from `category_colnames` list);- Sort in descending order;- Make a bar plot.<jupyter_code>df[category_colnames].sum().sort_values(ascending=False).plot.bar(title="Distribution of Categories");<jupyter_output><empty_output><jupyter_text>Apparently, for most categories we have a very unbalanced distribution with too few positive examples. `child_alone` category does not have any positive examples at all, so we just drop this column<jupyter_code>df.drop('child_alone', axis=1, inplace=True)<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>engine = create_engine('sqlite:///../data/DisasterResponse.db')
df.to_sql('messages_categories', engine, index=False, if_exists='replace')<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup