<jupyter_start><jupyter_text>World Happiness API Project Data Preparation Workflow:1. Import Dataset from Local Machine2. Manipulate Dataset3. Save Data into SQLite Database File ETL Workflow:![ETL](https://www.slideteam.net/media/catalog/product/cache/960x720/t/h/three_components_of_etl_process_flow_model_slide01.jpg)<jupyter_code># Import Dependencies
import pandas as pd
import random
import os
import csv
import requests
import json
import numpy as np
# Import data from csv file

happypath = os.path.join("..", "data", "happy.csv")
ginipath = os.path.join("..", "data", "gini.csv")
gdppath = os.path.join("..", "data", "pop.csv")
geopath = os.path.join("..", "data", "geo.csv")
happipath = os.path.join("..", "data", "happiness.csv")

happy_df = pd.read_csv(happypath)
gini_df = pd.read_csv(ginipath)
gdp_df = pd.read_csv(gdppath)
geo_df = pd.read_csv(geopath, encoding='latin-1')
happi_df = pd.read_csv(happipath)

happy_df.index
# Extract the most recent year data for each country (higher performance)
happiness_df = happy_df.loc[happy_df.groupby(['Country name'])['Year'].idxmax()].reset_index(drop=True)
happiness_df
# Create a loop to extract the most recent year data for each country (control workflow)
country = []
year = []
happiness = []

for i in happy_df.index:
    if i < happy_df.shape[0]-1:
        if happy_df["Year"][i] < happy_df["Year"][i+1]:
            continue
            # print(happy_df["Year"][i])
        else:
            country.append(happy_df["Country name"][i])
            year.append(happy_df["Year"][i])
            happiness.append(happy_df["Life Ladder"][i])
    else:
        break   
# Create a new dataframe to hold the data from the loop

# happiness_df = pd.DataFrame(list(zip(country, year, happiness)),
#                           columns=["country", "year", "happiness"])
happiness_df["Year"] = happiness_df["Year"].astype(str)
happiness_df = happiness_df.rename(index=str, columns={"Country name":"country_name", 
                                                       "Life Ladder":"happiness",
                                                      "Year":"year"})
happiness_df = happiness_df.round({"happiness": 3})
happiness_df
# Extract the most recent year data for each country (higher performance)
gini_index_df = gini_df.loc[gini_df.groupby(['Country Name'])['Year'].idxmax()].reset_index(drop=True)
gini_index_df
# Create a loop to extract the most recent year data for each country
country = []
country_code = []
year = []
gini = []

for i in gini_df.index:
    if i < gini_df.shape[0]-1:
        if gini_df["Year"][i] < gini_df["Year"][i+1]:
            continue
            # print(gini_df["Year"][i])
        else:
            country.append(gini_df["Country Name"][i])
            country_code.append(gini_df["Country Code"][i])
            year.append(gini_df["Year"][i])
            gini.append(gini_df["Value"][i])
    else:
        break
# Create a new dataframe to hold the data from the loop
dict = {"country_name":country, "code":country_code, "gyear":year, "gini":gini}
gini_index_df = pd.DataFrame(dict)
# Rename the columns 
gini_index_df = gini_index_df.rename(index=str, columns={"Country Name":"country_name", 
                                                        "Country Code":"code",
                                                        "Year":"gyear",
                                                        "Value":"gini"})
gini_index_df["gyear"] = gini_index_df["gyear"].astype(str)
gini_index_df
# Check column names in gdp_df
list(gdp_df.columns.values)
# Extract information from GDP dataframe
gdp_reduced_df = gdp_df[['country_name', 'region_id', 'gdp', 'population_population_number_of_people']]
demo_df = gdp_reduced_df.dropna()
# Loop through the data and extract the data for the project
country = []
region_id = []
gdp = []
pop = []

for i in gdp_df.index:
    if i < gdp_df.shape[0]-1:
        mask = gdp_df['gdp'].isna()
        if mask[i] == True:
            continue
            # print(gdp_df["country_name"][i])
        else:
            country.append(gdp_df["country_name"][i])
            region_id.append(gdp_df["region_id"][i])
            gdp.append(gdp_df["gdp"][i])
            pop.append(gdp_df["population_population_number_of_people"][i])
    else:
        break
# Create a new dataframe to hold the data
pop = {"country_name":country, "region_id":region_id, 
       "gdp":gdp, "population":pop}
demo_df = pd.DataFrame(pop)
# Rename the columns
demo_df = demo_df.rename(index=str, columns={"population_population_number_of_people":"population"})
demo_df.head()
geo_df = geo_df.rename(index=str, columns={"name":"country_name"})
geo_df.head()
print(gini_index_df["gyear"].dtype)
print(happiness_df["year"].dtype)
# Merging the dataframes
joined_df = pd.merge(demo_df, geo_df, on='country_name', how='inner')
joined_df = pd.merge(joined_df, gini_index_df, on="country_name", how="inner")
joined_df = pd.merge(joined_df, happiness_df, on="country_name", how="inner")
joined_df["gdpPerCap"] = round(joined_df["gdp"] / joined_df["population"], 2)
joined_df.head()
# print(joined_df["gdpPerCap"].min())
# Export the dataframe to CSV file
joined_df.to_csv("../outputs/joined_data.csv", index=False, header=True)
joined_df.groupby('region_id')['country_name'].nunique()
joined_df.groupby('region_id')[['gini', 'happiness']].mean()
joined_df.groupby("region_id")[["population", "gdp"]].sum()
happi_df.head()
list(happi_df)
happy_df = pd.merge(happiness_df, geo_df, on='country_name', how='inner')
print(len(happy_df))
print(happy_df.head())
# Export the dataframe and save it as a CSV file for later use
happy_df.to_csv("../outputs/happy.csv", index=False, header=True)
gini_df = pd.merge(gini_index_df, geo_df, on='country_name', how='inner')
print(len(gini_df))
# Save data as CSV file for later use
gini_df.to_csv("../outputs/gini.csv", index=False, header=True)
demo_df = pd.merge(demo_df, geo_df, on='country_name', how='inner')
print(len(demo_df))
# Save it as CSV file for later use
demo_df.to_csv("../outputs/demographic.csv", index=False, header=True)
# Creating SQLite Database
from sqlalchemy import create_engine, Column, Integer, String, Float, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
Base = declarative_base()
# Define the happiness table
class Happiness(Base):
    __tablename__ = "happiness"
    id =Column(Integer, primary_key=True)
    country = Column(String)
    year = Column(Text)
    happiness = Column(Float)
    lat = Column(Float)
    lon = Column(Float)
# Table only exists in python and not in the actual database yet
Base.metadata.tables
# Create the database engine
engine = create_engine("sqlite:///../db/data.sqlite")

# This is where the tables was created in the database
Base.metadata.create_all(engine)
# The ORM's "handle" to the database is the Session
session = Session(engine)
# Adding the data to the session

for i in happy_df.index:
    session.add(Happiness(
        country = happy_df["country_name"][i], 
        year = happy_df["year"][i],
        happiness = happy_df["happiness"][i],
        lat = happy_df["latitude"][i],
        lon = happy_df["longitude"][i]
    ))
# Data has not been added to the database yet
engine.execute("SELECT * FROM Happiness").fetchall()
# Use the new attribute to see the queue of data ready to go into the database
session.new
# Commit flushes whatever remaining changes remain to the database and commits the transaction
session.commit()
# Confirm nothing new to add
session.new
# Query the database to check the data
session.query(Happiness.country, Happiness.lat, Happiness.happiness).all()
# Create a class for gini
class Gini(Base):
    __tablename__ = "gini"
    id =Column(Integer, primary_key=True)
    country = Column(String)
    country_code = Column(String)
    year = Column(Text)
    gini = Column(Float)
    lat = Column(Float)
    lon = Column(Float)
Base.metadata.tables
engine = create_engine("sqlite:///../db/data.sqlite")
Base.metadata.create_all(engine)
session = Session(engine)
for i in gini_df.index:
    session.add(Gini(
        country = gini_df["country_name"][i], 
        country_code = gini_df["code"][i],
        year = gini_df["gyear"][i],
        gini = gini_df["gini"][i],
        lat = gini_df["latitude"][i],
        lon = gini_df["longitude"][i]
    ))
engine.execute("SELECT * FROM Gini").fetchall()
session.new
session.commit()
session.new
session.query(Gini.country, Gini.country_code, Gini.year, Gini.gini).all()
# Create a class for demographic
class Demographic(Base):
    __tablename__ = "demographic"
    id =Column(Integer, primary_key=True)
    country = Column(String)
    region_id = Column(String)
    year = Column(Text)
    gdp = Column(Float)
    population = Column(Float)
    lat = Column(Float)
    lon = Column(Float)
Base.metadata.tables
engine = create_engine("sqlite:///../db/data.sqlite")
Base.metadata.create_all(engine)
session = Session(engine)
demo_df
for i in demo_df.index:
    session.add(Demographic(
        country = demo_df["country_name"][i], 
        region_id = demo_df["region_id"][i],
        year = "2018",
        gdp = demo_df["gdp"][i],
        population = demo_df["population"][i],
    ))
engine.execute("SELECT * FROM Demographic").fetchall()
session.new
session.commit()
session.new
session.query(Demographic.country, Demographic.region_id, Demographic.lat, Demographic.gdp, Demographic.population).all()
# Define the happiness table
class Geo(Base):
    __tablename__ = "geo"
    id =Column(Integer, primary_key=True)
    country = Column(String)
    lat = Column(Float)
    lon = Column(Float)
    init = Column(String)
Base.metadata.tables
engine = create_engine("sqlite:///../db/data.sqlite")
Base.metadata.create_all(engine)
session = Session(engine)
for i in geo_df.index:
    session.add(Geo(
        country = geo_df["country_name"][i], 
        lat = geo_df["latitude"][i],
        lon = geo_df["longitude"][i],
        init = geo_df["country"][i]
    ))

engine.execute("SELECT * FROM Geo").fetchall()
session.new
session.commit()
session.new
session.query(Geo.country, Geo.lat, Geo.lon, Geo.init).all()
# Define the joined table
class Join(Base):
    __tablename__ = "join"
    id =Column(Integer, primary_key=True)
    country = Column(String)
    country_code = Column(String)
    region_id = Column(String)
    gdp = Column(Float)
    population = Column(Float)
    lat = Column(Float)
    lon = Column(Float)
    gini = Column(Float)
    happiness = Column(Float)
    gdpPerCap = Column(Float)
Base.metadata.tables
engine = create_engine("sqlite:///../db/data.sqlite")
Base.metadata.create_all(engine)
session = Session(engine)
for i in joined_df.index:
    session.add(Join(
        country = joined_df["country_name"][i],
        country_code = joined_df["code"][i],
        region_id = joined_df["region_id"][i],
        gdp = joined_df["gdp"][i],
        population = joined_df["population"][i],
        lat = joined_df["latitude"][i],
        lon = joined_df["longitude"][i],
        gini = joined_df["gini"][i],
        happiness = joined_df["happiness"][i],
        gdpPerCap = joined_df["gdpPerCap"][i]
    ))
engine.execute("SELECT * FROM Geo").fetchall()
session.new
session.commit()
session.new
session.query(Join.country, Join.lat, Join.lon, Join.gdpPerCap).all()
# Define the joined table
class Explain(Base):
    __tablename__ = "explain"
    id =Column(Integer, primary_key=True)
    country = Column(String)
    happiness = Column(Float)
    gdppercap = Column(Float)
    social = Column(Float)
    health = Column(Float)
    freedom = Column(Float)
    generosity = Column(Float)
    corruption = Column(Float)
    residual = Column(Float)
Base.metadata.tables
engine = create_engine("sqlite:///../db/data.sqlite")
Base.metadata.create_all(engine)
session = Session(engine)
for i in happi_df.index:
    session.add(Explain(
        country = happi_df["Country"][i],
        happiness = happi_df["Happiness score"][i],
        gdppercap = happi_df["Explained by: GDP per capita"][i],
        social = happi_df["Explained by: Social support"][i],
        health = happi_df["Explained by: Healthy life expectancy"][i],
        freedom = happi_df["Explained by: Freedom to make life choices"][i],
        generosity = happi_df["Explained by: Generosity"][i],
        corruption = happi_df["Explained by: Perceptions of corruption"][i],
        residual = happi_df["Dystopia (1.88) + residual"][i],
    ))
engine.execute("SELECT * FROM Explain").fetchall()
session.new
session.commit()
session.new
session.query(Explain.country, Explain.gdppercap, Explain.social, Explain.health).all()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14042284632376145, 'fraction_numerical': 0.0022877879457242033, 'mean_word_length': 3.807356844899507, 'pattern_counts': {'":': 18, '<': 10, '<?xml version=': 0, '>': 5, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '223197', 'license': 'MIT', 'n_tokens_mistral': 4301, 'n_tokens_neox': 4171, 'n_words': 921, 'path': 'jupyternotebook/.ipynb_checkpoints/World_Happiness_Data_Preparation-checkpoint.ipynb', 'repo_name': 'NormanLo4319/World_Happiness_Database_API', 'size': '12676'}