<jupyter_start><jupyter_text>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 Here all the dataset used for this project<jupyter_code>import pandas as pd
import numpy as np

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

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

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())<jupyter_output><empty_output><jupyter_text>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: 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   Creation de tables  Table dates<jupyter_code>min_date = covid_cases.document_date.min()
max_date = pd.to_datetime("2021-12-12")

date_table=pd.DataFrame(pd.date_range(min_date, max_date),columns=["document_date"])
date_table["date_Id"]=date_table.document_date.apply( lambda x : int(str(x).replace("-","")[0:8]))
date_table.tail()
date_table["year"]=date_table["document_date"].dt.year
date_table["month"]=date_table["document_date"].dt.month
date_table["quarter"]=date_table["document_date"].dt.quarter
date_table["quarter"]=date_table["quarter"].apply( lambda x: "Quarter" + str(x))
date_table["sem"]=date_table["document_date"].dt.isocalendar().week
date_table.set_index("date_Id", inplace=True)
date_table.head()<jupyter_output><empty_output><jupyter_text>Table mga<jupyter_code>mgaDF = []
for (x, y) in map_dict.items():
    mgaDF.append([x, y])
mga_table = pd.DataFrame(mgaDF, columns=["index", "mga_name"])
mga_table.set_index(keys="index", inplace=True,)

mga_table<jupyter_output><empty_output><jupyter_text>Data processing  covid_cases Dataset<jupyter_code>covid_cases.head()
covid_cases.drop(columns=["Unnamed: 0",], inplace=True)
covid_cases["date_Id"]=covid_cases.document_date.apply( lambda x : int(str(x).replace("-","")[0:8]))
covid_cases.drop(columns="document_date",inplace=True)
covid_cases.departement.unique()
covid_cases=covid_cases[-(covid_cases.departement=="Grand Total ")]
covid_cases=covid_cases[-(covid_cases.departement=="Grand Total")]
covid_cases=covid_cases[-(covid_cases.departement=="Guest")]
covid_cases1=pd.merge(covid_cases, departement,how="left", right_on="adm1_fr", left_on="departement")
covid_cases1.head()
covid_cases1 = covid_cases1[["cas_suspects","cas_confirmes","deces","taux_de_letalite","date_Id","adm1code"]]<jupyter_output><empty_output><jupyter_text>spa DataFrame<jupyter_code>spa.head()
spa["depart"] = spa.depart.apply(lambda x: "0"+str(x) if len(str(x)) == 1 else str(x))
spa["vilcom"] = spa.vilcom.apply(lambda x: "0"+str(x) if len(str(x)) == 1 else str(x))
spa["code"] = "HT"
spa["commune_id"] = spa["code"] + spa["depart"] + spa["vilcom"]
for_Commune_DF = spa[["vilcom", "vilcomn"]]
spa.drop(columns=["Unnamed: 0","index", "facil", "vilcom", "depart", "code", "departn", "vilcomn", "facdesc"], inplace=True)
spa.head()
facdesc0L = list(spa.facdesc_1.unique())
facdesc0L

facdesc0 = pd.DataFrame(facdesc0L, columns=["facdesc",])
facdesc0["index"] = facdesc0.index+1
facdesc0.set_index("index", inplace=True)
facdesc_table = facdesc0
facdesc_table

spa["facdesc_1"] = spa.facdesc_1.apply(lambda x: facdesc0L.index(x)+1)
spa.head()
spa.facdesc_1.value_counts()
spa.tail()<jupyter_output><empty_output><jupyter_text>departement DataFrame<jupyter_code>departement.head()
departement["IHSI_UNFPA_Total"]=departement["IHSI_UNFPA_2019_female"]+departement["IHSI_UNFPA_2019_male"]
departement.drop(inplace=True, columns=["adm0code", "adm0_en", "adm0_fr", "adm0_ht", "IHSI_UNFPA_2019_male", "IHSI_UNFPA_2019_female"])
departement.set_index("adm1code", inplace=True)
departement<jupyter_output><empty_output><jupyter_text>commune DataFrame<jupyter_code>commune.head()
commune["IHSI_UNFPA_2019"]=commune["IHSI_UNFPA_2019_female"]+commune["IHSI_UNFPA_2019_male"]
commune.drop(inplace=True, columns=["adm0code", "adm0_en", "adm0_fr", "adm0_ht", "adm1_en", "adm1_fr", "adm1_ht"])
commune.set_index(inplace=True, keys="adm2code")
commune.head()<jupyter_output><empty_output><jupyter_text>Data Remote<jupyter_code>from sqlalchemy import create_engine
from sqlalchemy import create_engine
user="postgres"
database="covid_19_final"
password="<PASSWORD>"
port="5432"
driver="postgresql://"
hostname="localhost"

connect_string=f"{driver}{user}:{password}@{hostname}:{port}/{database}"
connect_string
con=create_engine(connect_string)
con
"""
spa.to_sql(name="spa",con=con,if_exists="replace")
commune.to_sql(name="commune",con=con,if_exists="replace")
departement.to_sql(name="department",con=con,if_exists="replace")
covid_cases.to_sql(name="covid_cases",con=con,if_exists="replace")
date_table.to_sql(name="date_table",con=con,if_exists="replace")
mga_table.to_sql(name="mga_table",con=con,if_exists="replace")
facdesc_table.to_sql(name="facdesc_table", con=con,if_exists="replace")
"""



covid_cases.to_sql(name="covid_cases",con=con,if_exists="replace")<jupyter_output><empty_output><jupyter_text>QUESTIONS  Question 1: Create a relational database with this raw data set.<jupyter_code>import psycopg2
%load_ext sql
%sql postgresql://postgres:admin@localhost:5432/covid_19_final<jupyter_output><empty_output><jupyter_text>Question 2. Calculate the number of health facilities per commune.<jupyter_code># SQL statement to query from a PostgreSQL database table
sqlQ2 = %sql SELECT c."adm2_fr", count(spa."facdesc_1") as Quantity From commune as c inner join spa on c."adm2code" = spa."commune_id" group by c."adm2_fr" order by Quantity Desc
print(sqlQ2)<jupyter_output>* postgresql://postgres:***@localhost:5432/covid_19_final
127 rows affected.
+--------------------------------+----------+
|            adm2_fr             | quantity |
+--------------------------------+----------+
|             Delmas             |    77    |
|         Port-au-Prince         |    63    |
|       Croix-Des-Bouquets       |    41    |
|           Carrefour            |    37    |
|            Léogâne             |    28    |
|          Pétion-Ville          |    26    |
|          Cap-Haïtien           |    24    |
|             Jacmel             |    21    |
|           Dessalines           |    21    |
|          Port-de-Paix          |    20    |
|           <NAME>           |    19    |
|           <NAME>            |    19    |
|           Saint-Marc           |    19    |
|         <NAME>         |    16    |
|            Gonaïves            |    16    |
|           <NAME>           |    15    |
|            Arcahaie            |    15    |
| [...]<jupyter_text>Question 3. Calculate the number of health facilities by commune and by type of health facility.<jupyter_code>sqlQ3 = %sql SELECT c."adm2_fr", facdesc_table."facdesc" as Type_Health_Facilities, count(spa."facdesc_1") as Quantity From commune as c Inner join spa on c."adm2code" = spa."commune_id" Inner join facdesc_table On facdesc_table."index"=spa."facdesc_1" Group by c."adm2_fr", Type_Health_Facilities Order by adm2_fr, Quantity DESC
print(sqlQ3)<jupyter_output>* postgresql://postgres:***@localhost:5432/covid_19_final
322 rows affected.
+--------------------------------+--------------------------+----------+
|            adm2_fr             |  type_health_facilities  | quantity |
+--------------------------------+--------------------------+----------+
|            Abricots            | CENTRE DE SANTE SANS LIT |    2     |
|          <NAME>          | CENTRE DE SANTE SANS LIT |    4     |
|          <NAME>          |       DISPENSAIRE        |    3     |
|          <NAME>          |         HOPITAL          |    1     |
|         Anse-à-Foleur          |       DISPENSAIRE        |    3     |
|         Anse-à-Foleur          | CENTRE DE SANTE AVEC LIT |    1     |
|          Anse-à-Pître          | CENTRE DE SANTE AVEC LIT |    2     |
|          Anse-à-Pître          |       DISPENSAIRE        |    1     |
|         Anse à Galets          | CENTRE DE SANTE SANS LIT |    8     |
|         Anse à Galets          |       DISPE[...]<jupyter_text>Question 4. Calculate the number of health facilities by municipality and by department.<jupyter_code>sqlQ4= %sql SELECT department."adm1_fr", c."adm2_fr",  count(spa."facdesc_1") as Nbr_of_fascilities From commune as c Inner join spa on c."adm2code" = spa."commune_id" Inner join department On department."adm1code"=c."adm1code" Group by c."adm2_fr", department."adm1_fr" Order by adm1_fr, Nbr_of_fascilities DESC
print(sqlQ4)<jupyter_output>* postgresql://postgres:***@localhost:5432/covid_19_final
127 rows affected.
+-------------+--------------------------------+--------------------+
|   adm1_fr   |            adm2_fr             | nbr_of_fascilities |
+-------------+--------------------------------+--------------------+
|  Artibonite |           Dessalines           |         21         |
|  Artibonite |           Saint-Marc           |         19         |
|  Artibonite |            Gonaïves            |         16         |
|  Artibonite |           <NAME>           |         15         |
|  Artibonite | <NAME> l'Artibonite |         9          |
|  Artibonite |           Verrettes            |         9          |
|  Artibonite |   Saint-<NAME>'Attalaye   |         8          |
|  Artibonite |             Ennery             |         7          |
|  Artibonite |           <NAME>           |         7          |
|  Artibonite |            L'Estère            |         4          |
|  Artibonit[...]<jupyter_text>5-Calculate the number of sites by type (mga) and by department.<jupyter_code>sqlQ5 = %sql SELECT department."adm1_fr" as departement, mga_table."mga_name" as mga_type, count(spa."facdesc_1") as Number_of_Health_Facilities From department Inner join commune on commune."adm1code" = department."adm1code" Inner join spa On spa."commune_id"=commune."adm2code" inner join mga_table on mga_table."index"=spa."mga" group by departement, mga_type order by departement
print(sqlQ5)<jupyter_output>* postgresql://postgres:***@localhost:5432/covid_19_final
36 rows affected.
+-------------+---------------------+-----------------------------+
| departement |       mga_type      | number_of_health_facilities |
+-------------+---------------------+-----------------------------+
|  Artibonite |  private and public |              10             |
|  Artibonite |  private non profit |              17             |
|  Artibonite |        public       |              59             |
|  Artibonite | private with profit |              38             |
|    Centre   | private with profit |              9              |
|    Centre   |  private and public |              15             |
|    Centre   |        public       |              24             |
|    Centre   |  private non profit |              6              |
| Grande'Anse | private with profit |              4              |
| Grande'Anse |        public       |              24             |
| Grande'Anse |  private non profit |  [...]<jupyter_text>6-Calculate the number of sites with an ambulance by commune and by department (ambulance = 1.0)<jupyter_code>sqlQ6 = %sql SELECT department."adm1_fr" as departement, commune."adm2_fr", count(distinct commune."adm2_fr") as Number_of_commune_with_an_ambulance From department Inner join commune on commune."adm1code" = department."adm1code" Inner join spa On spa."commune_id"=commune."adm2code" where spa."ambulance" = 1.0 group by departement , adm2_fr order by departement
print(sqlQ6)<jupyter_output>* postgresql://postgres:***@localhost:5432/covid_19_final
80 rows affected.
+-------------+--------------------------------+-------------------------------------+
| departement |            adm2_fr             | number_of_commune_with_an_ambulance |
+-------------+--------------------------------+-------------------------------------+
|  Artibonite |           <NAME>           |                  1                  |
|  Artibonite |           Dessalines           |                  1                  |
|  Artibonite |             Ennery             |                  1                  |
|  Artibonite |            Gonaïves            |                  1                  |
|  Artibonite |         <NAME>          |                  1                  |
|  Artibonite |           <NAME>           |                  1                  |
|  Artibonite |            L'Estère            |                  1                  |
|  Artibonite | <NAME>'Artibonite |     [...]<jupyter_text>7-Calculate the number of hospitals per 10k inhabitants by department.<jupyter_code>sqlQ7=%sql SELECT department."adm1_fr" as departement, count ( facdesc_table."facdesc") as number_of_hospital, department."IHSI_UNFPA_Total" as total, round(round(count ( facdesc_table."facdesc")*10000,2)/department."IHSI_UNFPA_Total",2) From department Inner join commune on commune."adm1code" = department."adm1code" Inner join spa On spa."commune_id"=commune."adm2code" inner join facdesc_table on spa."facdesc_1"=facdesc_table."index" where facdesc_table."index" = 2 group by adm1_fr, total order by total desc 
print(sqlQ7)<jupyter_output>* postgresql://postgres:***@localhost:5432/covid_19_final
(psycopg2.errors.UndefinedColumn) column department.ihsi_unfpa_total does not exist
LINE 1: ...t ( facdesc_table.facdesc) as number_of_hospital, department...
                                                             ^

[SQL: SELECT department.adm1_fr as departement, count ( facdesc_table.facdesc) as number_of_hospital, department.IHSI_UNFPA_Total as total, round(round(count ( facdesc_table.facdesc)*10000,2)/department.IHSI_UNFPA_Total,2) From department Inner join commune on commune.adm1code = department.adm1code Inner join spa On spa.commune_id=commune.adm2code inner join facdesc_table on spa.facdesc_1=facdesc_table.index where facdesc_table.index = 2 group by adm1_fr, total order by total desc]
(Background on this error at: https://sqlalche.me/e/14/f405)
None<jupyter_text>8-Calculate the number of sites per 10k inhabitants per department<jupyter_code>sqlQ8 = %sql SELECT department."adm1_fr" as departement ,count ( facdesc_table."facdesc") as number_of_hospital, department."IHSI_UNFPA_Total" as total, round(round(count ( facdesc_table."facdesc")*10000,2)/department."IHSI_UNFPA_Total",2) From department Inner join commune on commune."adm1code" = department."adm1code" Inner join spa On spa."commune_id"=commune."adm2code" inner join facdesc_table on spa."facdesc_1"=facdesc_table."index" group by adm1_fr, total order by total desc
print(sqlQ8)<jupyter_output>* postgresql://postgres:***@localhost:5432/covid_19_final
(psycopg2.errors.UndefinedColumn) column department.ihsi_unfpa_total does not exist
LINE 1: ...t ( facdesc_table.facdesc) as number_of_hospital, department...
                                                             ^

[SQL: SELECT department.adm1_fr as departement ,count ( facdesc_table.facdesc) as number_of_hospital, department.IHSI_UNFPA_Total as total, round(round(count ( facdesc_table.facdesc)*10000,2)/department.IHSI_UNFPA_Total,2) From department Inner join commune on commune.adm1code = department.adm1code Inner join spa On spa.commune_id=commune.adm2code inner join facdesc_table on spa.facdesc_1=facdesc_table.index group by adm1_fr, total order by total desc]
(Background on this error at: https://sqlalche.me/e/14/f405)
None<jupyter_text>9-Calculate the number of beds per 1,000 inhabitants per department<jupyter_code>sqlQ9= %sql SELECT department."adm1_fr" as departement, count(spa."num_beds") as number_of_beds, department."IHSI_UNFPA_Total" as total, round(round(count ( spa."num_beds")*1000,2)/department."IHSI_UNFPA_Total",4) as number_of_beds_1000_inhabithants From department Inner join commune on commune."adm1code" = department."adm1code" Inner join spa On spa."commune_id"=commune."adm2code" inner join facdesc_table on spa."facdesc_1"=facdesc_table."index" group by adm1_fr, total order by total desc<jupyter_output>* postgresql://postgres:***@localhost:5432/covid_19_final
(psycopg2.errors.UndefinedColumn) column department.ihsi_unfpa_total does not exist
LINE 1: ...partement, count(spa.num_beds) as number_of_beds, department...
                                                             ^

[SQL: SELECT department.adm1_fr as departement, count(spa.num_beds) as number_of_beds, department.IHSI_UNFPA_Total as total, round(round(count ( spa.num_beds)*1000,2)/department.IHSI_UNFPA_Total,4) as number_of_beds_1000_inhabithants From department Inner join commune on commune.adm1code = department.adm1code Inner join spa On spa.commune_id=commune.adm2code inner join facdesc_table on spa.facdesc_1=facdesc_table.index group by adm1_fr, total order by total desc]
(Background on this error at: https://sqlalche.me/e/14/f405)
None<jupyter_text>10-How many communes have fewer dispensaries than hospitals<jupyter_code>sqlQ10= %sql WITH Hospital as (SELECT department."adm1_fr", commune."adm2_fr" as commune, count ( facdesc_table."facdesc") as hospital from department Inner join commune on commune."adm1code" = department."adm1code" Inner join spa On spa."commune_id"=commune."adm2code" inner join facdesc_table on spa."facdesc_1"=facdesc_table."index" where facdesc_table."index" = 2  group by adm1_fr, commune order by  hospital desc), Dispensaire as (SELECT department."adm1_fr", commune."adm2_fr" as commune, count ( facdesc_table."facdesc") as dispensaire from department Inner join commune on commune."adm1code" = department."adm1code" Inner join spa On spa."commune_id"=commune."adm2code" inner join facdesc_table on spa."facdesc_1"=facdesc_table."index" where facdesc_table."index" = 1 group by adm1_fr, commune order by  dispensaire desc) SELECT h."commune" as commune, Hospital, Dispensaire From Hospital as h inner join Dispensaire as d on h."commune"=d."commune" group by h."commune", Hospital, Dispensaire having Hospital > Dispensaire order by commune
print(sqlQ10)
sqlQ11= %sql SELECT round((sum(covid_cases."deces")/sum(covid_cases."cas_confirmes")),3) as taux_de_letalite, date_table."month" from covid_cases inner join date_table on covid_cases."date_Id"=date_table."date_Id" group by  month
sqlQ12= %sql SELECT round((sum(covid_cases."deces")/(sum(commune."IHSI_UNFPA_2019_male")+sum(commune."IHSI_UNFPA_2019_female"))),7) as taux_de_deces, date_table."month" from covid_cases inner join date_table on covid_cases."date_Id"=date_table."date_Id" inner join department on covid_cases."departement"=department."adm1_fr" inner join commune on department."adm1code"=commune."adm1code" group by month
print(sqlQ12)
sqlQ13= %sql SELECT round((sum(covid_cases."cas_suspects"+covid_cases."cas_confirmes")/(sum(commune."IHSI_UNFPA_2019_male")+sum(commune."IHSI_UNFPA_2019_female"))),8) as prevalence, date_table."month" from covid_cases inner join date_table on covid_cases."date_Id"=date_table."date_Id" inner join department on covid_cases."departement"=department."adm1_fr" inner join commune on department."adm1code"=commune."adm1code" group by month
print(sqlQ13)
sqlQ14= %sql SELECT department."adm1_fr", round((sum(covid_cases."cas_suspects"+covid_cases."cas_confirmes")/(sum(commune."IHSI_UNFPA_2019_male")+sum(commune."IHSI_UNFPA_2019_female"))),8) as Prevalence from covid_cases inner join date_table on covid_cases."date_Id"=date_table."date_Id" inner join department on covid_cases."departement"=department."adm1_fr" inner join commune on department."adm1code"=commune."adm1code" group by department."adm1_fr", month order by Prevalence Desc
print(sqlQ14)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup