<reponame>thomas-hoellen/NLP-disaster-tweet-pipeline
import pandas as pd
from sqlalchemy import create_engine

# --- Load Data ---
# load twitter messages dataset
messages = pd.read_csv('messages.csv')
# load categories/labels dataset
categories = pd.read_csv('categories.csv')

# --- Merge Data ---
df = pd.merge(messages, categories, left_on='id', right_on='id', how='left')

# --- Preprocess Data ---
# split categories into single columns
categories = df['categories'].str.split(';', expand=True)
# rename columns in categories to make data clearer
row = categories.iloc[0]
category_colnames = []
slice_col_names = lambda s: s[:-2]
for i in range(len(row)):
    category_colnames.append(slice_col_names(row[i]))
categories.columns = category_colnames

# rename values from string to int for later usage as labels in ML pipeline
for column in categories:
    # set each value to be the last character of the string
    categories[column] = categories[column].str[-1]
    # convert column from string to numeric
    categories[column] = pd.to_numeric(categories[column])
categories.head()

# Add pre processed categories data frame to merged df
df = df.drop(['categories'], axis=1)
df = pd.concat([df, categories], axis=1)

# Remove duplicates
df = df.drop_duplicates(subset=['message'], keep=False)

# Save to SQL
engine = create_engine('sqlite:///DisasterTweetsDatabase.db')
df.to_sql('DisasterTweets', engine, index=False)