ABSTRACT

Objective

Design and implement an interactive web-based dashboard to track COVID-19 in Colombia

Methods

A tool was designed and implemented to analyze the data of Covid-19 positive cases in Colombia and published by the Instituto Nacional de Salud. The tool is based on the implementation of business intelligence methods with which you can understand the behavior of the pandemic in Colombia and generate structured data for decision-making by government levels. The tool displays, on a single screen, information on the number of cases, patient status, age ranges, city, location department, and gender. This information can be dynamically filtered and focus analyzes on the national, departmental, or municipal order. Additionally, methods are implemented for trend analysis, both on a linear and semi-log scale, as well as for calculating the case fatality rate in each of the municipalities.

Results

A web-based data analysis dashboard is implemented for semi-continuous monitoring of the COVID-19 pandemic in Colombia. With the use of the tool, a situational analysis is carried out for five of the most important cities in Colombia.

Conclusions

The application is effective, flexible, and easy to use. The situational analysis reflects that public policies for the control of the disease have been favorable for Medellín, but for Cartagena, Bogotá, Barranquilla, and Cali, complementary measures are required.

Key Words:
Pandemics; Colombia; coronavirus infections (source: MeSH, NLM)

RESUMEN

Objetivo

Diseñar e implementar una herramienta dinámica web para el seguimiento de la COVID-19 en Colombia.

Métodos

Se diseña e implementa una herramienta para el análisis de los datos de los casos positivos de COVID-19 en Colombia y publicados por el Instituto Nacional de Salud. La herramienta se basa en la implementación de métodos de inteligencia empresarial con los que se pueda entender el comportamiento de la pandemia en Colombia y generar datos estructurados para la toma de decisiones por parte de los estamentos gubernamentales. La herramienta despliega, en una sola pantalla, información sobre número de casos, estado de los pacientes, rangos de edad, ciudad y departamento de ubicación y género. Esta información puede ser filtrada de forma dinámica y enfocar los análisis en el orden nacional, departamental o municipal. Adicionalmente, se implementan métodos para el análisis de tendencia, tanto en escala lineal como en escala semilogarítmica, así como para el cálculo de la tasa de letalidad en cada uno de los municipios.

Resultados

Se implementó una herramienta de análisis de datos basado en la web para el seguimiento semicontinuo de la pandemia de la COVID-19 en Colombia. A partir de la herramienta se realiza un análisis situacional para cinco de las ciudades más importantes de Colombia.

Conclusiones

El aplicativo es eficaz, flexible y de fácil manejo. El análisis situacional refleja que las políticas públicas para el control de la enfermedad han sido efectivas para Medellín, pero, para Cartagena, Bogotá, Barranquilla y Cali, se requieren medidas complementarias.

Palabras Clave:
Pandemia; Colombia; infecciones por coronavirus (fuente. DeCs, BIREME)

On December 31, 2019, health authorities in Wuhan City, Hubei Province, China, report an epidemic outbreak of pneumonia whose causes were not entirely clear. After a rigorous follow-up, and accompaniment by the World Health Organization, it is established that the unusual outbreak is caused by the SARS-CoV-2 virus, with characteristics of rapid spread and potential danger of death, especially in people over 60 years of age or with underlying diseases. From that date, until May 23, 2020, the virus outbreak has spread rapidly until it became a global pandemic that has spread to 188 regions and countries of the world, affecting about 5,468 million people and causing more than 344 deaths, thus being considered the worst health crisis since the Influenza epidemic in 1918 ¹1. Dong E, Du H, Gardner L. An interactive web-based dashboard to track COVID-19 in real time. Lancet Infect Dis [Internet]. 2020; 20(5):533-4. DOI:10.1016/S1473-3099(20)30120-1.
https://doi.org/10.1016/S1473-3099(20)30... .

After the confirmation of the first case in Colombia, on March 6, the Colombian government implemented different restrictive measures for mobility within the national territory aimed at controlling the spread of the disease, and whose effectiveness is evaluated through the contagion rate, R0, or average rate of new infections. The aim is to reduce the potential number of patients who present complications, require hospitalization, or even referral to Intensive Care Units (ICU), whose capacity in the country is quite limited, especially in small and intermediate populations ²2. González-Jaramillo, Valentina González-Jaramillo N, Gómez-Restrepo C, Palacio-Acosta C, Gómez-López A, Franco O. Proyecciones de impacto de la pandemia COVID-19 en la población colombiana, según medidas de mitigación. Datos preliminares de modelos epidemiológicos para el periodo del 18 de marzo al 18 de abril de 2020. Rev Salud Pública (Bogotá). 2020; 22(2):1-6. DOI:10.15446/rsap.v22n2.85789.
https://doi.org/10.15446/rsap.v22n2.8578... .

According to the behavior of the pandemic in the various countries, measures have been taken in Colombia to mitigate and curb its impact. In particular, it seeks to slow down its transmission speed and prepare the health system for case care, avoiding its collapse, and reducing the social and economic impact of the pandemic ³3. Manrique-Abril FG, Agudelo-Calderon CA, González-Chordá VM, Gutiérrez-Lesmes O, Téllez-Piñerez CF, Herrera-Amaya G. Modelo SIR de la pandemia de Covid-19 en Colombia/SIR model of the COVID-19 pandemic in Colombia. Rev Salud Pública (Bogotá). 2020; 22(2):1-9. DOI:10.15446/rsap.v22.85977.
https://doi.org/10.15446/rsap.v22.85977... .

Throughout the history of humanity, social isolation has been proven to be the best strategy to face the advance of an epidemic while learning from it and obtaining an effective vaccine or medication to control the associated symptoms. However, decisions that restrict mobility also have a high social, and economic impact, so precise information must be available to decide the optimal time to enter and exit a quarantine.

Interactive visualizations of data are needed to contextualize this information. These tools involve not only the public health and research communities but also the general public whose cooperation is essential to effective response efforts ⁴4. Berry I, Soucy JPR, Tuite A, Fisman D. Open access epidemiologic data and an interactive dashboard to monitor the COVID-19 outbreak in Canad. Cmaj. 2020; 192(15):E420. DOI:10.1503/cmaj.75262.
https://doi.org/10.1503/cmaj.75262... .

As we implement these tools to protect human lives, we can ingest extensive data from their sources and dis-play results in interactive dashboards and near real-time. These online dashboards have become a critical source of information during the Covid-19 outbreak ⁵5. Kamel Boulos MN, Geraghty EM. Geographical tracking and mapping of coronavirus disease COVID-19/severe acute respiratory syndrome coronavirus 2 (SARS-CoV-2) epidemic and associated events around the world: How 21st century GIS technologies are supporting the global fight against outbreaks and epidemics. Int J Health Geogr [Internet]. 2020; 19(1):1-12. DOI:10.1186/s12942-020-00202-8.
https://doi.org/10.1186/s12942-020-00202... .

The objective of this study is to develop a dynamic tool for the updated report of the situation of Covid-19 in Colombia from the data provided by the Instituto Nacional de Salud (INS), where important characteristics are reflected to understand the behavior of the virus in Colombian society and visualize the effectiveness of public policies. The tool provides a graphical interface that summarizes data streams into information that is easier to read and interpret ⁶6. Kamadjeu R, Gathenji C. Designing and implementing an electronic dashboard for disease outbreaks response - Case study of the 20132014 Somalia Polio outbreak response dashboard. Pan Afr Med J. 2017;27(Supp 3):22. DOI:10.11604/pamj.supp.2017.27.3.11062.
https://doi.org/10.11604/pamj.supp.2017.... .

METHODS

Selection of the software to use and the variables to incorpórate

For data manipulation, the Microsoft Power BI tool was selected, which is a business intelligence and analytics platform that leaves all the techniques and tools of Business Intelligence available ⁷7. Lachev T, Price E. Applied Microsoft Power BI (3rd Edition): Bring Your Data to Life! 3rd ed. Prologika Press; 2018., and is characterized by the ability to generate visualization data and implement control elements on the user interface dashboard simply and efficiently ⁸8. Knight D, Pearson M, Quintana, M. Powell B. Microsoft Power BI Complete Reference. Birmingham: Packt Publishing; 2018..

The information reported by the INS within the Coronavirus portal in Colombia was reviewed, and it was decided to select all the variables included in the structured database, including case identification, location city, department, state of attention, age, sex and date of a web report. The variable of the number of national tests, and by the department, taken from the test section of the INS portal was also included.

Dashboard design and construction

This process began with the purification of the information from the INS databases. The data was exported as excel CSV files, and then a cleaning and unification process was carried out to import it into PowerBi. Once the data was imported, the corresponding connections were made between the variables of each of the databases.

In accordance with the previous review of applications in the market, it was decided that all the information should be presented in a unified way in a single dash-board and that dynamically allow the user to have the complete panorama of the situation in the country without requiring advance through tabs, additional pages, or separate graphics.

The graphs were designed and constructed using relationships between variables with the help of DAX programming commands ⁹9. Russo M, Ferrari A. The Definitive Guide to DAX: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel [Internet]. Pearson Education (Business Skills); 2019 [cited 2020 May 22]. Available from: Available from: https://bit.ly/3cncnkn .
https://bit.ly/3cncnkn... , for correct visualization of the data, both numerical and geo-referenced.

Also, a drop-down panel for department selection and a filter reset button is incorporated, thus generating a secure mechanism to change the display of the variables to be analyzed. Table 1 summarizes each of the display options available to the application, indicating the possible selections, and the filter criteria.

Functional testing and implementation on a web platform

Operation tests of the control panel were carried out, with special care in activating filters. Additionally, the web platform https://covid-colombia.com was created for the imple-mentation of the central dashboard, and other specific dashboards for some departments within the web platform. After the implementation process, the dynamic tool is up-dated daily with the latest report delivered from the INS.

Presentation of a case study with the five principal cities of the country

A comparative situational analysis was performed with the information of the five cities with the largest population in Colombia, these being Bogotá D.C, Medellín, Cali, Barranquilla, and Cartagena de Indias ¹⁰10. Portafolio. Estas son las ciudades más pobladas del país. Portafolio; 2019 [cited 2020 May 22]. Available from: Available from: https://bit.ly/3hUwJTp .
https://bit.ly/3hUwJTp... , with graphs obtained from the dynamic tool that was implemented.

RESULTS

This section presents each of the graphs created by the tool for a specific case of analysis at the departmental level. The application is divided into seven displays, a drop-down panel, and a filter reset button, as shown in Figure 1.

Chart A presents the number of positive cases recorded daily (green boxes), and a curve (red line) of accumulated values with representation on a linear scale, or on a semi-log scale, at the national, departmental, or municipal level, depending on the selection made on the graph but-tons. To facilitate the reading of the graphs, you can select any bar in the chart, which opens a small window that gives more detailed information than it represents.

Charts B, C and D indicate the age ranges, sex, and the status of the patients (at home, recovered, deceased, in the ICU, or in hospital) of each case. Graph E presents on a map the geographical location of the cases at the national level, and if the departmental filter is applied, the map focuses on that position, and presents the distribution of cases in the cities or municipalities of said department.

Visualizations F and G show the number of cases and total tests at the national or departmental level, according to the selected filter. Each of the visualizations can be filtered according to the drop-down panel H, which lists each of the departments and special districts where positive cases are registered. Panel activation is shown in Figure 2.

Comparative analysis of the five main cities in Colombia

An analysis of the situation related to Covid-19 was carried out in the five main cities of the country: Bogotá D.C., Medellín, Cali, Barranquilla, and Cartagena de Indias.

Number of cases and percentage of the national total

For May 23, there is an official report of 12.434 positive cases in the five main cities of the country. This value re-presents 63,3% of all cases in the country. Bogotá DC leads the distribution of positive cases in the five cities with 56,1% of cases, followed by Cartagena, Cali, Barranquilla, and Medellín, with percentages of 15,1%, 14,7%, 10,7% and 3,5%, respectively, as evidenced in Figure 3. Various factors can influence more than 60% of infections to occur in just five cities, such as, for example, a higher population density, a higher degree importation of cases due to the presence of international airports with a high flow of foreigners, greater social inequality, or non-compliance with quarantine. Determining the factors with the highest incidence is highly relevant to understand the spread of the virus. Still, they are beyond the scope of this document, and the discussion is left open for future research.

Semi-logarithmic scale curves

In Figure 4, the accumulated value of the number of infections in each of the cities under analysis is presented in a semi-log presentation. This presentation allows studying the behavior of the pandemic during the isolation measures. Thus, for example, during the first days of virus exposure in Colombian society, the five capitals present relatively similar growth slopes. After the start of the quarantine, decreed on March 24, until April 7, the slope seems to have a slight decrease, although it is less noticeable in the city of Bogotá. As of this date, different behaviors began to be perceived in the five capitals. While in the city of Bogotá, the slope does not show significant changes, it is possible to notice pronounced increases in the cities of Cali and Cartagena de Indias. Although the reasons may be diverse, it may be due to the increase in tests and the possibility of carrying out analyzes in a higher number of specialized centers. It is also worth noting the coincidence of these increases with the Easter season, and especially susceptible time in Colombian society for the agglomeration of people, either for religious fervor or for vacations. In both cases, it infers a quarantine violation. Further analysis, where these variables are correlated, is proposed for future work.

It is also interesting to note that, as of April 26, the ci-ties of the Atlantic Coast, Barranquilla, and Cartagena de Indias, show pronounced increases in the number of cases, while Cali and Bogotá retain monotonous growth. It is also interesting to note that, as of April 26, the cities of the Atlantic Coast, Barranquilla, and Cartagena de Indias, show pronounced increases in the number of cases, while Cali and Bogotá retain monotonous growth. At the end of this study, Medellín shows itself as the one with the best behavior, showing low growth figures in the number of infections. The preceding is summarized like that, even though the isolation measure is of the national order, the behavior of the pandemic follows different dynamics in each region, where social, economic and cultural factors influence compliance with the measure, just as the state of the health system directly influences the ability to find, contain, and recover infected people.

Number of deaths and case fatality rate

Figures 5 and 6 show the number of deaths and the case fatality rate for each of the five main cities in Colombia.

When comparing both graphs it is possible to notice a critical behavior in the city of Cali, and in Cartagena de Indias, since they present increases in the number of cases, and, additionally, higher case fatality rates, and where if the trend continues it could mean far more deaths than those observed in the city of Bogotá. On the other hand, the excellent performance of the health system in the city of Medellín should be highlighted, where few deaths have occurred concerning the number of infections.

DISCUSSION

A dynamic tool for the consultation of the information of the Covid-19 positives in Colombia is presented. The application has benefits regarding the alternatives found in the national market, since it dynamically displays, on a single dashboard, all the information available in the databases provided by the National Institute of Health, and is discriminated against by the information geographically to the national, departmental, and municipal order.

It is a tool that, through an agile, intuitive, flexible, and easy-to-use web platform, allows information to be viewed on any device (desktop, laptop, or mobile), regardless of its specifications or operating system.

The tool includes linear and logarithmic growth curves at the national and departmental levels. These resources are not fully available in other platforms found in the network, in which it is limited only to the national order. However, as described in the case study, local information can be extracted so that regional leaders can assess the effectiveness of public policies in each of their territories, and determine the need to adopt additional special measures, or, on the contrary, dismantle the restrictive measures to mobility. Under both perspectives, there is an important support for decision-making support ♦

REFERENCES

Publication Dates

History