Our MBA students in Data Journalism produced a series of tutorials as their final project in the Low Code course: Transforming data into news stories without programming, taught by Professor Adriano Belisário. This month you can check out some of their work and... Enjoy the adventures with the tutorials they've created. Today you can check out the tutorial made by Mariana Hallal.
Vaccination against Covid-19 in Brazil began with the groups most vulnerable to the disease or who are most exposed to the virus. The elderly, people living in long-term care facilities, healthcare professionals, and indigenous peoples were at the top of the list.
Recently, in May, people with comorbidities began to be included in the vaccination program. See how to discover the impact of this vaccination on the number of people hospitalized. To do this, we will explore data from the Ministry of Health's Sivep-Gripe system using SQL and Google Sheets.
Step by step
1 - Download the data from the Ministry of Health. For this analysis, we will only use the 2021 data. Click here. Click here to download the spreadsheet.To download, you need to click on “SRAG” > “Explore” > “Download”.
2 - If you've never used SQL, you need to download the program. We'll be using DB Browser for SQLite. Download it. here.
3 - In SQL, click on "New database", name this database "comorbidity" and save.
3 - A window will open asking you to create a table. Click cancel because we are going to import the data.
4 - Click on “File” > “Import” > “Table from CSV file”
5 - Select the file we downloaded from the Ministry of Health, configure the table as shown in the image, and click OK.
7 - The table name contains elements that could hinder analysis, such as the hyphen (-). Let's rename the table to eliminate this problem. Right-click on the table name and click "Modify Table".
8 - Rename the table to “srag2021” and click OK.
9 - Now let's move on to the analysis. Let's select the columns we will use. Click on "Execute SQL", type the command below and press the "play" button.
SELECT FATOR_RISC, substr (DT_INTERNA, 4, 10) AS mes_interna
FROM srag2021
WHERE CLASSI_FIN = 5
The SELECT function tells the program which columns we want. The FROM function tells us which table we want to pull those columns from. The WHERE function applies a filter. In this case, we only want the rows where the final classification is 5 (covid).
The term “substr” at the end of the SELECT function is used to remove characters from an entire column. Let's apply it to the DT_INTERNA column to keep only the month and year of hospitalization – this will make the analysis easier.
10 - This is the table we're going to use. Therefore, let's create a view to work with it more effectively. Click on "Save results view" > "Save as view". Name this view "fatorderisco".
11 - Press Enter twice after the last line and let's move on to another sentence. Our ultimate goal is to find out what percentage of those hospitalized had a risk factor each month. So we'll ask SQL to count how many people with and without risk factors were hospitalized each month.
SELECT *, count(FATOR_RISC) AS totalfator
FROM riskfactor
WHERE mes_interna LIKE '%2021%'
GROUP BY internal_month, RISK_FACTOR
With this statement, we filtered only the months of 2021. Although the spreadsheet theoretically only contains data from 2021, there are many typos in the "date" field that hinder the analysis.
After typing the sentence, select it and click "Execute".
12 - Let's save this result and transfer it to Google Sheets. Click the same "save view" button, choose "Export to CSV", click "save", and name it "fatorderisco".
13 - Create a new spreadsheet in Google Sheets. Click on “File” > “Import” > “Upload” > “Select a file from your device”. Uncheck the box that says “Convert text to numbers, dates, and formulas” and click on “Import data”.
14 - Click on “View” > “Freeze” > “1 line” to mark the first line as the header.
15 - Select the “totalfactor” column, click the “123” button, and select the “0” or “number” option.
16 - Let's create a pivot table. Click on "Data" > "PivotTable" > "Create". Click on "Add" next to "Rows" in the left-hand menu and select "internal_month".
Click “Add” next to “Values” and select “totalfactor”. In the “Summarize by” tab, select “SUM”.
Copy the result and paste only the values (CTRL + SHIFT + V) into cell F1 of the main tab.
17 - In cell D1 of the first tab, type “total_por_mes”. In cell D2, type the following formula: =VLOOKUP(B:B;F:G;2;FALSE).
18 - Double-click the small blue dot in the corner of cell D2 to apply this information to the entire column.
19 - In cell E1, type “percentage”. In cell E2, type the following formula: =C2/D2. Click the small blue square in the corner of the cell to apply the formula to the entire column. Select the entire column E, click on “123” and select percentage.
20 - Done! That's the result. In the RISK_FACTOR column, the number 1 means "yes, the patient has a risk factor" and the number 2 means "the patient does not have a risk factor". This information is in the data dictionary.
We observed that in January, 65,6% of deaths occurred among people with risk factors. In June, the percentage had already dropped to 52,37%. Always remember to talk to a specialist before drawing conclusions from a database.
Vaccination of people with comorbidities began in May in most states. Therefore, to date, most of these people have only received the first dose of the vaccine. In the coming weeks, people with risk factors are expected to represent an even smaller group among those hospitalized. To filter by state, you can place the desired state in the WHERE clause in the first statement in the SQL.
