Repository files navigation

• Objective:

  • AnalyzeCOVID-19 mortality data in Chile from 2020 to 2024, providing critical insights into the pandemic's impact on the population.

• Project Division:

  • First Part: Work withGoogle Sheets for initial data exploration and analysis.
  • Second Part: UsePostgreSQL for advanced analysis and data storage.

• Methodology:

  • Google Sheets:

    • UsePython for data processing and analysis.
    • Collaboratively explore data usingGoogle Sheets.

  • PostgreSQL:

    • Migrate data to aPostgreSQL database for more detailed analysis and advanced queries.

• Key Objectives:

  • Data Cleaning and Organization: Prepare the mortality dataset for meaningful analysis.
  • Exploration of Trends and Patterns: Investigate mortality rates by region, age, and time.
  • Data Visualization: Create effective visualizations to communicate key findings.

The project leveragesPython for data processing and analysis andGoogle Sheets for collaborative data exploration.

• Cleaning andorganizing the mortality dataset for meaningful analysis.
• Exploring trends and patterns, such as mortality rates by region, age, or time.
• Visualizing data to communicate insights effectively.
• Building reproducible workflows for dataanalysis andreporting.

• Googlesheets Database

The project leveragesPython for data processing and analysis andPostgres for collaborative data exploration.

Ensure you have the following installed:

• PostgreSQL (Download)
• Python 3.x (Download)
• DBeaver (optional, for database visualization:Download)
• Required Python Libraries:

  pip install pandas scikit-learn psycopg2 matplotlib seaborn numpy reportlab fpdf googleapiclient google

1. In this case, we are going to useDBeaver, so the first thing we need to do is create a new connection.

• As shown in the picture, we need to configure the following settings:
  • Host: localhost
  • Database: postgres
  • Port: 5432
  • Username: postgres
  • Password:YOUR PASSWORD

2. After setting the parameters, we should test the connection to ensure it works before applying the configuration.

- AÑO (INT) - Year of the death date | Number- FECHA_DEF (DATE) - Date of the death date - SEXO_NOMBRE (VARCHAR(10)) - Gloss identifying biological sex- EDAD_CANT (INT) - Numerical record of the patient's age at admission- COMUNA (VARCHAR(100)) - Gloss of the commune of residence of the deceased, according to the political-administrative division updated in 2019- NOMBRE_REGION (VARCHAR(100)) - Gloss of the region of residence- LUGAR_DEFUNCION (VARCHAR(100)) - Describes the place where the death occurs- PERIODO (VARCHAR(7)) - Period of the death date- RANGO_ETARIO (VARCHAR(20)) - Age range of the deceased

• Since the data we have is quite large, we need to insert it through the terminal to avoid issues when using the editor.

psql -U postgres -d mi_base_de_datos -h localhost -p 5432 -f /home/usuario/Chile-COVID-Mortality-2020-2024/postgres/tables/add_into_tables.sql

select cd."AÑO" as "year",cd."NOMBRE_REGION" as "region",count(cd."EDAD_CANT") as "quantity",sum(case when cd."SEXO_NOMBRE" = 'Mujer' then 1 else 0 end) as "Women",sum(case when cd."SEXO_NOMBRE" = 'Hombre' then 1 else 0 end) as "Men"from covid_chile.covid_data cdgroup by "year", "region"order by "year";

The data used in this project, including COVID-19 mortality records in Chile from 2020 to 2024, is provided by the Chilean government’s open data platform:

• Defunciones por COVID19

Special thanks toPamela Suarez(deis@minsal.cl) for creating and curating the dataset. I acknowledge and appreciate her work in making this information publicly accessible.

Thanks to theMinisterio de Salud de Chile and related governmental agencies for making this data publicly available.