<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

# load messages dataset
messages = pd.read_csv('messages.csv')
messages.head()
#function to read csv from categories.csv inorder to clean data  : 
def read_categories(path_csv='categories.csv'):
    #helper function to manage column list 
    def column_update(l,entry) :
        if entry in l:
            pass
        else:
            l.append(entry)
    #reading the given file 
    fp= open(path_csv,'r')
    columns=[] # list to store the column data
    while(True):
        a=fp.readline()
        if a =='':
            print(columns)
            break
            fp.close()
            yield None
        else:
            a=a.replace('\n','')
            items=a.split(',')
            result=[]
            for item in items:
                splits=item.split(';')
                if item=='categories' :
                    continue
                if item=='id':
                    column_update(columns,item)
                    continue
                elif len(splits) >2 : 
                    for vals in splits:
                        tmp=vals.split('-')
                        column_update(columns,tmp[0])
                        result.append(int(tmp[-1]))
                else:
                    result.append(int(splits[0]))
            if len(result)>=1:
                yield result
            else:
                continue
a=read_categories()
#printing columns for dataframe 
while(next(a)):
    continue
#storing columns data 
columns=['id', 'related', 'request', 'offer', 'aid_related', 'medical_help', 'medical_products', 'search_and_rescue', 'security', 'military', 'child_alone', 'water', 'food', 'shelter', 'clothing', 'money', 'missing_people', 'refugees', 'death', 'other_aid', 'infrastructure_related', 'transport', 'buildings', 'electricity', 'tools', 'hospitals', 'shops', 'aid_centers', 'other_infrastructure', 'weather_related', 'floods', 'storm', 'fire', 'earthquake', 'cold', 'other_weather', 'direct_report']
# load categories dataset
categories = pd.DataFrame(read_categories(), columns=columns)
categories['id']=categories['id'].apply(lambda x : int(x)) # it will help to merge with the id column in messages dataframe
categories.head()
print(len(messages))
print(len(categories))<jupyter_output>26248
26248<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>messages=messages[~messages.duplicated()]
categories=categories[~categories.duplicated()]
print(len(messages))
print(len(categories))
set(messages['id'])-set(categories['id'])
set(categories['id'])-set(messages['id'])
categories.isna().any().any()
messages.isna().any()
for i in categories[categories['id'].duplicated()]['id']:
    print(categories[categories['id']==i])
    
# merge datasets
df = messages.merge(categories ,on = 'id', how='left')
print(len(df))
df.head()
cat_column=['related', 'request', 'offer', 'aid_related', 'medical_help', 'medical_products', 'search_and_rescue', 'security', 'military', 'child_alone', 'water', 'food', 'shelter', 'clothing', 'money', 'missing_people', 'refugees', 'death', 'other_aid', 'infrastructure_related', 'transport', 'buildings', 'electricity', 'tools', 'hospitals', 'shops', 'aid_centers', 'other_infrastructure', 'weather_related', 'floods', 'storm', 'fire', 'earthquake', 'cold', 'other_weather', 'direct_report']<jupyter_output><empty_output><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>def series_int(x):
    z=x.apply(lambda a : int(a))
    return z 
    
#categories[cat_column].apply(lambda x : series_int(x) ,axis=1)
# create a dataframe of the 36 individual category columns
categories = 
categories.head()
# select the first row of the categories dataframe
row = 

# 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 = 
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
    categories[column] = 
    
    # convert column from string to numeric
    categories[column] = 
categories.head()<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.head()
# concatenate the original dataframe with the new `categories` dataframe
df = 
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

# drop duplicates

# check number of duplicates<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>from sqlalchemy import create_engine
engine = create_engine('sqlite:///TrialDatabaseName.db')
df.to_sql('messagelabel', engine, index=False)<jupyter_output><empty_output><jupyter_text>8. Use this notebook to complete `etl_pipeline.py`Use the template file attached in the Resources folder to write a script that runs the steps above to create a database based on new datasets specified by the user. Alternatively, you can complete `etl_pipeline.py` in the classroom on the `Project Workspace IDE` coming later.<jupyter_code>#An example command to run etl process
#python process_data.py disaster_messages.csv disaster_categories.csv DisasterResponse.db
%%writefile etl_pipeline.py
import pandas as pd
import numpy as np
import sys
from sqlalchemy import create_engine


def get_file_names():
    '''Capture file name arguments passed to command line 
    and transfer to relevant functions as their required arguments'''
    files=list()
    for arg in sys.argv:
        files.append(arg)
    if len(files)!=4:
        raise ValueError("Provide three filenames to the pipeline\
        in sequence messages.csv,\
        categories.csv , databasse.db")
    if files[1].split('.')[-1]!='csv' :
        raise ValueError (f"Provide .CSV filename at {files[1]} ")
    if files[2].split('.')[-1]!='csv' :
        raise ValueError (f"Provide .CSV filename at {files[2]} ")
    if files[3].split('.')[-1]!='db' :
        raise ValueError (f"Provide .db extension at {files[3]} ")
    return files

#helper function to manage column list 
def column_update(l,entry) :
    if entry in l:
        pass
    else:
        l.append(entry)

columns=[] # list to store the column data

def read_categories(path_csv):
    '''This function is to read the categories'
    matrix-data from the categories.csv '''
    #reading the given file 
    fp= open(path_csv,'r')
    while(True):
        a=fp.readline()
        if a =='':
            fp.close()
            break
            yield None
        else:
            a=a.replace('\n','')
            items=a.split(',')
            result=[]
            for item in items:
                splits=item.split(';')
                if item=='categories' :
                    continue
                if item=='id':
                    column_update(columns,item)
                    continue
                elif len(splits) >2 : 
                    for vals in splits:
                        tmp=vals.split('-')
                        column_update(columns,tmp[0])
                        result.append(int(tmp[-1]))
                else:
                    result.append(int(splits[0]))
            if len(result)>=1:
                yield result
            else:
                continue

                
def create_merged_dataframe(csv_message,csv_categories):
    '''After extracting and cleaning the two csv file data,
    it creates single merged dataframe'''
    messages = pd.read_csv(csv_message)
    categories = pd.DataFrame(read_categories(csv_categories)
                              ,columns=columns)
    #removing duplicates
    messages=messages[~messages.duplicated()]
    categories=categories[~categories.duplicated()]
    df = messages.merge(categories, on = 'id', how='left')
    return df

def save_to_db(df,fileName, tableName):
    engine = create_engine(f'sqlite:///{fileName}')
    conn=engine.connect()
    engine.execute(f"DROP TABLE IF EXISTS {tableName}")
    df.to_sql(tableName, conn, index=False)
    return True

    
if __name__ =="__main__":
    _,messages,categories,db=get_file_names()
    df=create_merged_dataframe(messages,categories)
    tableName='categorizedMessages'
    a=save_to_db(df,db,tableName=tableName)
    if a : 
        print(f"Data has been saved into the {db} within \
the table '{tableName} '")

    
    
    
    
#testing the file 
from etl_pipeline import get_file_names,read_categories,create_merged_dataframe,save_to_db
df=create_merged_dataframe('messages.csv','categories.csv')
save_to_db(df,'dataMessages.db',"categorizedMessages")
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine(f'sqlite:///dataMessages.db')
conn=engine.connect()
data=pd.read_sql('select * from categorizedMessages',engine)
conn.close()
data
!python etl_pipeline.py messages.csv categories.csv LabelledMessages.db
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('sqlite:///DisasterResponse.db')
conn=engine.connect()
data=pd.read_sql_table('categorizedMessages',conn)

tmp=list(data.columns)
for i in ['id','message','genre','original','related']:
  tmp.remove(i)
data[data['related']==2][tmp].sum().sum()

data[~(data['related']==2)]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1155215842960132, 'fraction_numerical': 0.00469035003908625, 'mean_word_length': 3.4906396255850236, 'pattern_counts': {'":': 1, '<': 32, '<?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': '472182', 'license': 'MIT', 'n_tokens_mistral': 3399, 'n_tokens_neox': 3278, 'n_words': 1000, 'path': 'Analysis & Data Exploration/ETL Pipeline Preparation.ipynb', 'repo_name': 'rindhane/MessageLabelling', 'size': '11513'}