# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ## Haiti Health Data Analysis

# You are an analyst working at a health research company in Haiti.  The government has asked your company to analyze data related to covid-19.  To that end, they have provided you with access to health data.
#

# Your goal as a Data Manager is to provide
# *  Relational Database to manage all the data collection
# * Use SQL Language to answers some query provided from the Board
# * Build a dashboard with Power BI to illustrate all the activites of covid
#

# ### Install Requirements

# !pip3 install sqlalchemy
# !pip3 install psycopg2-binary

import os
import pandas as pd
import numpy as np

# MySql dependencies
import sqlalchemy as sqla
import psycopg2

# +
# MySql Engine connection

username = "bootcamp_aa"
password = "<PASSWORD>"
hostname = "localhost"
port = "5432"
db = "health_research_company"

connect_str = f"postgresql+psycopg2://{username}:{password}@{hostname}:{port}/{db}"

engine = sqla.create_engine(connect_str)

engine.connect
# -

# ### Here all the dataset used for this project

covid_cases = pd.read_csv("./datasets/mspp_covid19_cases.csv")
display(covid_cases)
covid_cases.info()

spa = pd.read_csv("./datasets/spa.csv")
display(spa.head(5))
display(spa.info())
map_dict = {1: 'public',2: 'private non profit',3: 'private with profit',4 : 'private and public'}
map_dict

# #### SPA - Data Cleaning

spa[['mga']].value_counts()

# Replace MGA values with the map Dict
spa = spa.replace({"mga": map_dict})

spa[['mga']] = spa.mga.apply(str)

spa[['mga']].dtypes

spa.head(3)

departement = pd.read_excel("./datasets/hti_adminboundaries_tabulardata.xlsx",sheet_name=1)
commune  = pd.read_excel("./datasets/hti_adminboundaries_tabulardata.xlsx",sheet_name=2)
display(departement.head(5))
display(departement.info())
display(commune.head(5))
display(commune.info())
display(commune.shape)

# # Questions

# * Question 1: Create a relational database with this raw data set.
# * Question 2. Calculate the number of health facilities per commune. 
# * Question 3. Calculate the number of health facilities by commune and by type of health facility.
# * Question 4. Calculate the number of health facilities by municipality and by department.
# * Question 5: Calculate the number of sites by type (mga) and by department.
# * Question 6: Calculate the number of sites with an ambulance by commune and by department (ambulance = 1.0).
# * Question 7. Calculate the number of hospitals per 10k inhabitants by department.
# * Question 8. Calculate the number of sites per 10k inhabitants per department * Question 9.
# * Question 9: Calculate the number of beb per 1,000 inhabitants per department.
# * Question 10.How many communes have fewer dispensaries than hospitals?
# * Question 11 How many  Letality rate per month
# * Question 12 How many Death rate per month 
# * Question 13 How many Prevalence per month 
# * Question 14 How many Prevalence by department
# * Question 15 What is the variation of the prevalence per week
# * Question 16. Build a Power BI dashboard to understand the health structures in Hait
#
#
#
#
#
#

# ### Your Goal as  a Data Management is to 

# Export Data to Mysql tables
covid_cases.to_sql(
    "covid_cases",
    engine,
    index=False,
    if_exists="append"
)
spa.to_sql(
    "spa",
    engine,
    index=False,
    if_exists="append"
)
departement.to_sql(
    "departement",
    engine,
    index=False,
    if_exists="append"
)
departement.to_sql(
    "departement",
    engine,
    index=False,
    if_exists="append"
)
commune.to_sql(
    "commune",
    engine,
    index=False,
    if_exists="append"
)











StarcoderdataJupyterScriptsDedupFiltered