HEART ATTACK: DO LOW TEMPERATURES INCREASE THE NUMBER OF CASES? ¶
TABLE OF CONTENTS¶
Complete project from inspection to analysis, using Google's ecosystem (Cloud Storage, BigQuery, Colab).
According to experts, the number of heart attacks increases when the temperature drops, more specifically below 14°C (57°F). How does this statement hold up by analyzing a sample from a particular city in Brazil?
In this project I want to show some skills regarding the analysis of public data, SQL and Cloud Computing.
Nowadays, every data professional should be comfortable with cloud solutions, specially the ones that are working on startups.
The idea of this project is to show decision making, usability of the cloud, how to integrate some modules and simulate a small data operation.
OBJECTIVE: Check if public statements are true by analysing public data, using cloud infrastructure.¶
Below, the link for the statements and datasets.
SOURCE 1: https://www.uol.com.br/vivabem/noticias/redacao/2019/07/07/inverno-aumenta-em-30-o-risco-de-infarto-saiba-como-se-proteger.htm
SOURCE 2: https://drauziovarella.uol.com.br/cardiovascular/numero-de-casos-de-infarto-e-avc-aumenta-no-frio/
DATASETS: http://dadosabertos.c3sl.ufpr.br/curitiba/SESPAMedicoUnidadesMunicipaisDeSaude/
2018-03-10_Sistema_E-SaudeMedicos-_Base_de_Dados.csv
2018-06-10_Sistema_E-SaudeMedicos-_Base_de_Dados.csv
2018-09-10_Sistema_E-SaudeMedicos-_Base_de_Dados.csv
IMPORTANT #1: Some info of the datasets are in Brazilian - Portuguese (Because I'm originally from Brazil and I decided to navigate our public datasets) but I'll translate all the important information throughout this page to English. Some of the images used in this project will also be Google Translated to English.
IMPORTANT #2: The idea of this project is not to affirm if public statements are true, or test the veracity of any scientific project.
USED TECHNOLOGIES¶
- SQL
- GCP (Google Cloud Platform)
2.1. Cloud Storage
2.2. BigQuery
2.3. Google Colab - Python
3.1. Pandas
3.2. Seaborn
3.3. Matplotlib.pyplot
Let's begin!
When navigating the web, it's very likely we will come across bold statements, such as the following:
Some of these statements can't be simply tested, but what if we decided to use public available data to test it out?
I've then decided to download and browse through the data of a Brazilian city (Since the posts were in Brazilian websites).
It's the city of Curitiba, which not only is very organized in terms of technology, as is also a city that can be very cold during some periods of the year.
Thing is, when I finished the download of such items, I've quickly realized that no-code / low-code applications like Excel or PowerBI would be a little tricky to use, since we start to get into gigabyte level of data, with MILLIONS of rows to analyze. (Even trying to open one of these files in Excel resulted in a miserable crash. It's time to use the real deal.)
Big datasets require big tools, and considering not everyone has a top notch machine at home or at their company, cloud solutions start to look very compelling.
2. DATA CLEANING ¶
Return to Index¶
With a portfolio project, that shall be no different.
I'm sure and convinced that there are different ways of cleaning data, changing encodings, all the good stuff.
But since we are talking about Portuguese and its special characters, I've decided to manually changed some stuff on Sublime Text.
Great, now with all the spaces, the "Ç" and the "Ã" and their cousins out of the way, let's upload these files to our cloud.
3. GOOGLE CLOUD STORAGE ¶
Return to Index¶
For starters, Cloud Storage is a badass version of standard clouds for uploading files, except it was designed for scabability in mind. It's super powerful and integrates like a breeze with BigQuery, which we will use in a moment.
BUT HEY, HELIO: Why don't you filter the dataset into a small version with only the occurrences of Heart Diseases and analyse it straight into Jupyter without uploading all that in cloud which could potentially generate costs?
- FIRST, I want to simulate a real life scenario where everything is available at the cloud of choice.
- SECOND, I want to show some SQL skills because that's what represents the day-to-day stuff of data professionals.
- THIRD, I know the file is 1GB only... But what if it was 70GB? Tough to work on a Jupyter without memory to load the datasets.
- FOURTH: Cloud is cool. 🤓
On a real life scenario, of course the best decision is always the one that generates the best result while consuming the least amount of resources.
Side note: It's possible to use a multi-cloud environment, but let's make it simple.
A little step-by-step ahead:
- Enter GCP Console
- Open a project (or enter an existing one, in my case it is named "Heart Attack-DS")
- Enter Cloud Storage on the left navigation bar
- Create and/or enter the bucket for your project
- Create a folder to make sure everything is neat
- Upload the 1GB of files to make it available to work within the cloud.
That's it, quite straight forward I'd say.
Now, let's make things a little more tangible.
4. GOOGLE BIGQUERY / SQL - ASSEMBLYING THE DATASETS ¶
Return to Index¶
We want to analyze the heart issues in general, specifically heart attacks, but of course the public available data is not only about heart diseases. There's a little bit of everything going on.
Another problem is that we don't have the weather info on these datasets, which require us to get another public dataset (Which Google kindly provides) and cross it with the temperature info for the particular days we are looking for.
So let's do it like this:
- I need to aggregate all the datasets into one.
- Then I will reduce their size, by filtering only the relevant data regarding heart problems
- Finally, I will join the weather data to the dataset, making it ready for analysis.
And for all the intents and purposes, it is time for some good old SQL, data's best friend. Let's do it.
4.1. BIGQUERY - Aggregating the 3 datasets into 1 ¶
Return to Index¶
We need to "tell" BigQuery how to manipulate the data.
Before, we need to understand how it manages data:
- First, we create a DATASET. The DATASET will store the TABLE.
- The TABLE the equivalent is what we are used to visualize on Pandas, Excel, etc etc
That being said, we need to request BigQuery to download the file from CloudStorage into a DATASET.
Then we WRITE A QUERY to the DATASET to summon a TABLE to be used afterwards.
Since we have 3 tables with the same variables (columns), we can create a table based on the first dataset and uploading its schema with it, and download the 2 other datasets into BigQuery while adding them to the created table, with the same schema as well.
Since this is not a tutorial on how to use BigQuery, I'll jump straight to the Queries. Feel free to ask me for more information.
4.2. SQL - Filtering by Heart Occurrences ¶
Return to Index¶
With this information I can execute my query.
The first query I executed was the following:
Well, let's describe what's going on here:
- I want only 3 variables for my analysis: When, How and How Much.
- I basically asked to extract the date, the ICD (Also asked the text version of it) and decided to count the occurrences.
- They come from de dataset I've assembled earlier.
- I filter only the ICD related to heart issues.
- I group the info to be able to have a count of issues per day
- I create a count column to help me to understand the amount and types of occurrences for each day.
Notice how each query process a determined amount of information.
That's why it's super important to write efficient queries, not only because of time, but also because the size of the files processed is paid for. Good queries not only save time, they also save money.
Now we save the query into a new dataset / table, good job so far.
4.3. SQL - Gathering weather data ¶
Return to Index¶
We have our Heart Medical data, but we are not done yet.
Remember that we want to check if the cases increase when it's cold?
Google supplies weather info inside BigQuery, it's called noaa_gsod (National Oceanic and Atmospheric Administration - Global Surface Summary of the Day), and it's available all the way from 1929.
We are going to Query data from GSOD 2018, for the city of Curitiba, Brazil.
Here comes the second query:
Well, let's describe what's going on here:
- I extracted Day, Month and Year of the dataset (Which I'll use as an index and also as a reference to the ICD tables)
- STN stands for Station, which is where NOAA reads information from. 838420 is the station for Curitiba.
- I also converted from Fahrenheit to Celsius, but because I'm more used to it. Bet some people didn't know this could be done with SQL, I was surprised when I first learned it.
Now I saved this into a new dataset / table, it's time for the "Gran SQL Finale".
4.4. SQL - JOINing weather data ¶
Return to Index¶
2 Tables to be merged into 1 while retaining all the information that we need. Time to JOIN.
Final query:
Let's describe the query:
- I select all the variables that I need, regardless of which table they belong.
- "oco" and "tem" are temporary variables that will be only used as an SQL command, they won't show up later.
- The "data" in front of f0 means that I'm renaming the column f0o to data, since that's the name we got from the first query when I used the "Extract Date" command
- Now pay attention on the EXTRACT:
- It is necessary for the join function to work because although both tables have date information, they are distributed differently.
- Example: I EXTRACTED the DAY from "oco" (my first table) and told my Query that it equals the "da" column of the table "tem"
- I had to CAST each variable AS INT64 because on my first table the information is stored as number and on the temperature table it is stores as a STRING.
- That resulted on the final table where I have all the heart occurrences plus the weather info on that particular day, which is exactly what I need for my analysis.
I could decide to do several things with this dataset, including delivering it on an Excel file for the business professional. But I think Python would allow me to run analysis faster and gather more information quickly.
Time to go to Colab :)
5. GOOGLE COLAB / PYTHON ¶
Return to Index¶
Here we are!
Now we are finally about to start the so called "analysis" using Google Colab and some Python libraries.
The idea of using a collab notebook is the integration with Google Cloud. While it's also possible to install the libraries on Jupyter, using native tools is always interesting. Plus, the idea for this project is to use Google's solution as much as possible.
Let's begin by:
- Loading the libraries we will use
- Authenticating our connection with BigQuery
- Saving our final query from BQ into a Pandas Dataframe.
# LIBRARIES
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline
from google.cloud import bigquery
# AUTHENTICATION
from google.colab import auth
auth.authenticate_user ()
print('Authenticated')
# ID of the Google Cloud project
project_id = 'heart-attack-ds'
# Set up the BigQuery client
client = bigquery.Client(project=project_id)
# Set up the query
query = """
SELECT *
FROM `heart-attack-ds.occurrences_results.heart_occurrences`
"""
# Run the query and retrieve the results
query_job = client.query(query)
results = query_job.result()
# Convert the results to a pandas dataframe
heart_occurrences = results.to_dataframe()
5.1. PYTHON - Quick browse through data ¶
Return to Index¶
While we in fact had to analyse multiple things to come to this point, after all, we NEED to know what we are doing on Queries, here is the part where we look at numbers, plots, cross things, understand variables and all the good stuff.
Many things happened for this moment to be possible, so the legend is in fact true: The actual analysis is only a small part of the whole process.
The only thing is: This part is a piece of cake now that we basically made our dataset / table as objective as possible.
# Print the dataframe to check if everything is fine
print(heart_occurrences)
# Print the head of the dataframe to get a slightly more organized view
heart_occurrences.head()
#Based on the temperature, I want to know the number of occcurences of each type of heart problem.
heart_occurrences.groupby(['temp_celsius','descricao_do_cid'])['data'].value_counts()
# Saving as a variable
value_per_temp = heart_occurrences.groupby(['temp_celsius','descricao_do_cid'])['data'].value_counts()
# I want to check the mean
value_per_temp.groupby(['temp_celsius', 'descricao_do_cid']).mean().reset_index()
# Saving as a variable
value_per_temp_mean = value_per_temp.groupby(['temp_celsius', 'descricao_do_cid']).mean().reset_index()
5.2. PYTHON - Plotting important info ¶
Return to Index¶
# I want to check how many types of ICD / CID are described in this dataset.
cids = value_per_temp_mean['descricao_do_cid'].unique()
for cid in cids:
print(cid)
#At last, let's see how the mean of each ICD corresponds to the temperature of when it happened.
for cid in cids:
plt.figure(figsize=(16,6))
plt.title(cid)
df_cid = value_per_temp_mean[value_per_temp_mean['descricao_do_cid'] == cid]
sns.barplot(x="temp_celsius", y="data", data=df_cid, color="coral")
6. CONCLUSION ¶
Return to Index¶
On a real life project, this is the part I'd write my conclusions. I usually put remarks throughout the plots so I don't lose information, then I just concatenate everything at the end.
But since this project basically had 1 plot session and really 1 question to be answered so to speak, we basically need to look at a few things.
- For the CITY OF CURITIBA in Brazil, IN 2018, within PUBLIC HOSPITALS, without considering other factors such as AGE and GENDER, is a clear NO.
Why? As we can clearly see with our plots, the temperature vs the mean of heart diseases occurrences is irrelevant when we plot considering all the cases within the dataset.
We also need to gather more information: In which city / country these websites done their research?
Did the research follow a good methodology in the first place?
We only have the data for public institutions, what about private ones?
What if the increase in number of cases is the combination of weather AND other factors, such as employment rate, human development index, or other?
Machine Learning would be excellent in that scenario in order to identify the most important variables. Maybe we could even check the possibility of uploading a model to production.
Plus, not many cities in Brazil stay below 14°C (57°F) for a long time during the year, so I wonder how such unbalanced scenario would contribute for a conclusion like the title of the website posts.
- NO.
Why? Because we didn't analyse the data for other years :)
- INCONCLUSIVE.
Why? Because I'd need to run more and more data, possibly from multiple years, plot more visualizations, study the plots for longer.
After a calmly analysed good chunk of data (we are probably talking about TERABYTES), then I'd be able to say for sure what is going on with that one.
If we have all the data needed: GREAT
If we have part of it: RUN IT AS A SAMPLE
If we barely have any data: It is just opinion.
And honestly, looking at the complexity of a GOOD analysis, I doubt such thing as analyzing big chunks of data was done.
We have to always question everything.
- I suspect there's more to it, like nutrition and stress.
- Let's not forget our "outliers", like people with cases within their families. They can take care of themselves but in the end, genetics play a big role.
- I'd ask for informations such as:
- Employment Rate;
- Average Income (Personal and family);
- Human Development Index;
- Family History (Simple yes or no is fine);
- I could consider gender (I'd guess there's a difference there);
- If possible some nutrition information based on country or state, if people from a particular area tend to consume a lot of trans fat or similar;
- I would consider age;
- Where the person was born;
- What type of work that person has, how heavy it is;
- Person's weight / BMI (Body Mass Index);
- If the person goes to the gym or exercise.
The list goes on! There are so many things that could in fact contribute for heart attacks, that it kinda feels silly to affirm that the weather in itself plays a major role in it.
7. ENDING & FINAL REMARKS ¶
Return to Index¶
Now that's the end.
THANK YOU for sticking around and I hope this project was useful to you.
Could I plug all the available datasets of heart diseases on a larger scale? Yes.
Is there room for improvement? Yes.
But I guess that already does the trick for an GCP portfolio project.
Hope talking to you soon!
CONTACT INFO:¶
Helio Ribeiro
helioribeiropro@gmail.com
+55 (11) 9 3932-8049