An interactive web-based dashboard to track COVID-19 in Colombia. Case study: five main cities

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.

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) O n 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).
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).
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).
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).
As we implement these tools to protect human lives, we can ingest extensive data from their sources and display results in interactive dashboards and near real-time. These online dashboards have become a critical source of information during the Covid-19 outbreak (5).
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).

Selection of the software to use and the variables to incorporate
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), and is characterized by the ability to generate visualization data and implement control elements on the user interface dashboard simply and efficiently (8).
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 dashboard 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), 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 implementation of the central dashboard, and other specific dashboards for some departments within the web platform. After the implementation process, the dynamic tool is updated 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), 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 buttons. 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 represents 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 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. 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.  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 ♦