In March 2020, the first suspect of Covid-19 appeared in Depok, a city in West Java, Indonesia. The number of new suspects was growing ever since then in the nearest areas like DKI Jakarta, Banten, and other provinces across Indonesia. Many stakeholders take part in this pandemic, including central to local government, medical personnel, and even data scientists.
In this case, data scientists help assessed the situations in places on specific areas about availability, quantity, and distribution mapping of health infrastructures like the testing centers and authorized hospitals for patients treatment. In this project, I would like to conduct a simple analysis to determine strategic locations for masks and medical resources for COVID-19 treatment in West Java, using active cases as of 6 December 2020 as an indicator, as well as defining the red zone areas to give intense recovery for future "new normal" condition.
Data
Several factors would be the consideration in determining the masks and medical resources:
- COVID-19 cases on each city/regency
- The total population of West Java as of 2020
- Distribution and Population Density of West Java
- Hospitals for COVID-19 treatment
The following data sources are also needed to extract/generate the required information:
- Processed COVID-19 positive case data collection in West Java as of 6 December 2020.
- New datasets from hospital data that contains the city/regency, along with their latitudes and longitudes.
First, import all necessary libraries in Python.
After Folium is installed and Libraries are imported, add a Foursquare developer account, and have the credentials ready.
Read and show all data used
First, we need to read and show data of COVID-19 cases per city/regency to expose the data of all the cases whether it is active cases, under quarantine, or dead patients.
Read and show data COVID-19 cases per city/regency.
Read and show the top 5 data rows from COVID-19 cases per city/regency.
df_cases.head()
Read and show the bottom 5 data rows from COVID-19 cases per city/regency.
df_cases.tail()
After that, read and show the total population data in West Java as of 2020.
Read and show the top 5 data rows of population density in each city/regency.
df_population.head()
Rank the data from least to most populated.
Read and show the top 5 most populated cities/regencies in West Java.
df_most_population.tail()
Now let’s look at the hospital data that provide treatment for COVID-19 patients in West Java. Construct a Pandas data frame for subsequent data analysis.
Read and show the top 5 data rows from Hospital data providing treatment for COVID-19 patients.
df_hospital.head()
This part sums up the data mining and data exploration methods. Next, the METHODOLOGY section will be described as the process of how to conduct a ‘visual’ approach to better understand all the data using Data Science and data analytics tool kits.
Methodology
First, create a new dataset of only ACTIVE CASES and QUARANTINE from the COVID-19 Case table on 6 December 2020. For further analysis, we only use ACTIVE CASES as an indicator since we only want to see the existing number of patients under medical treatment.
df_cases.columns
Remove/drop irrelevant columns for this analysis.
df_cases.tail()
Check if there are any missing or null values.
Based on these data mining, preparation, and exploration, there are 9.847 COVID-19 active cases in West Java as of 6 December 2020, distributed across 27 cities/regencies in the province out of over 48.683.861 population of West Java.
Depok City has the highest number of active cases with 1.730 people currently under hospital treatment. Just like any other area, each city/regency has many neighborhoods that can be used to pinpoint the location of the new proposed COVID-19 testing center along with further analysis of the neighborhood using the FourSquare API and Folium map visualization technique.
Latitude and Longitude of West Java and Its Cities
To support the map visual analysis, I’m using the free service tools provided by Open Cage Geocode (https://opencagedata.com/)to get the latitude and longitude of cities/regencies, venues, and surrounding neighborhoods. Open an account and download the required dependencies for the analysis, and kindly refer to their website for further details.
We can as well use the API service from OpenCage Geocoder to obtain the latitude and longitude of all cities/regencies in West Java.
df_cases.head(10)
Latitude and Longitude of Hospital
To see the locations of hospitals in the province, I also need to get the latitude and longitude of all COVID-19 testing centers in West Java that I obtained from West Java Provincial Government’s official website: pikobar.jabarprov.go.id/contact.
After identifying the location, the next step is to visualize the map of the whole province, which in this case is West Java to present the data to the stakeholders using a visualization approach.
First, download all the required dependencies earlier in the report and use the FOLIUM API service.
The map shows the territory area of West Java, with the border shown on the purple line. But it does not show the territory area of each city/regency within the province. In this scenario, I want to propose strategic locations (i.e. neighborhood) for further investigation within the West Java area.
Results
The chart below shows the population density of West Java in 2020, based on city/regency.
Using the graph above, Kab. Bogor (Bogor Regency) has the highest population with 5.840.907 people living in the area. Followed by Bandung Regency, Bekasi Regency, and Bekasi City respectively.
To understand and estimate the territories or areas that are within the administrative area of West Java, let’s plot all the districts that have been retrieved from https://pikobar.jabarprov.go.id/contact along with their latitude and longitude values.
The map above shows that all of the cities/regencies are within the territorial area of the province. To address the business problem, we need to display the extent and the distribution of medical devices for treatment of COVID-19 positive case-patients in West Java based on the number that has been obtained from the government site.
From this map, it can be seen that most of the regions in West Java are now in the "RED" zone, with a radius of 6 that most likely represent the relative extent of COVID-19 distribution in West Java. But several cities are not in the red zone, there may be no active cases or no updated data available.
On the government’s official website, these data are shown in a slider format so people can see the daily COVID-19 cases update from March. There is also information displayed in polygon format, as well as numbers of active cases plotting on each city/regency.
We also need to see the hospital location to compare the red zone and hospital capacity in the city/regency, to see how well the accessibility and distribution of the medical resources.
The hospitals are quite sparsely distributed within each other especially in densely populated areas like Bandung. Let’s see how strategic these hospitals are in accommodating the extent of positive cases patients in the city/regency by combining maps of COVID-19 cases and hospitals in West Java.
From the distribution of COVID-19 cases and hospitals located across the province, it can be seen that almost all hospitals require a lot of medical resources for COVID-19 treatment. As the number of cases high in Bandung both the city and regency, Cimahi City, and Sumedang Regency, the number of hospitals in these areas are also high.
Discussion
This section will analyze locations in the red zone based on the hospital located in the middle of the red zone. Since Depok City has the highest number of active cases, we will then determine the surrounding venues and neighborhood of RSUD Kota Depok (Depok Hospital).
Let’s begin by trying to get the top 100 venues that are within the Depok Hospital neighborhood and are within a radius of 500 meters of our candidate COVID-19 testing center using FOURSQUARE API. First, let’s create the GET request URL. After that, name that URL, url.
After that, get the URL for the API in the Depok Hospital neighborhood.
Make a request using the REQUEST library, and name the query results for Depok Hospital area, results.
Next, we will use the above function (get_category_type) to extract information from the JSON file related to venues in the Depok Hospital neighborhood.
After that, get the URL for the API in the Depok Hospital neighborhood, then make a request using the REQUEST library, and name the query results for the Depok Hospital area, results.
Using the results generated by the FOURSQUARE API, the business site around Depok Hospital are identified, see which businesses are in the red zone.
The next set of challenges that we need to tackle is to gain slightly more insights (profile) into the Depok Hospital area. To simplify the analysis, we can use the Euclidian (distance-based) clustering technique which is part of the unsupervised machine learning technique. In particular, we will use the K-means clustering.
First, we need to decide the best K-value for our analysis by utilizing the K-means clustering algorithm.
The X-axis shows the various number of K-values that we can use for our clustering analysis. From this elbow curve, it can be seen that the curve starts flattening out at K=3, then more flattened at K=6. Therefore, we will use a K=3 to cluster neighborhoods surrounding our proposed Covid-19 testing center.
To visualize the clustering of the neighborhood, we need to create a custom function called "regioncolors" that would help us distinguish the facilities using color.
We have assigned cluster labels to all of the neighborhood venues, as well as unique colors to each cluster. Next, we can then visualize the clustering analysis to a Folium map and see how all of these venues are geographically distributed within the 500-meter radius of the surrounding facilities.
Finally, compiled a map of these business locations with a map of the distribution of COVID-19 cases in Depok City.
The result of the analysis is the location of the business which is in the Depok Hospital neighborhood and is within a radius of 500 meters. Then, we also get the most congested cluster if businesses apply normal conditions in the red zone, potentially increasing cases of contracting the COVID-19 virus within the area.
Results and Discussion
This project aims to provide information for local people who must be alerted to go out of the house from the distribution of the COVID-19 case in West Java. It also aims to provide information on areas that are most needed for plenty of mask distribution, according to population density in the area.
Furthermore, it provides information on which cities need the most medical resources for COVID-19 treatment, including medical equipment and personnel. The analysis also provides information on the business neighborhood which shall implement COVID-19 health protocol with a high discipline in a "new normal" situation.
Conclusion
This project aimed to identify which areas have the most cases and see the distribution of hospitals and medical resources across the province. This would help mask distributors to understand potential distribution areas according to population density in West Java’s cities/regencies. Furthermore, it will also help the distribution of medical devices for COVID-19 test devices to hospitals that are estimated to have a large number of patients or even help to analyze which hospitals need additional medical equipment and personnel.
The analysis will surely provide awareness for business owners who run the business in the red zone areas to adjust their service by enhancing health protocol for its customers. Therefore, hopefully, the number of active cases in a particular area can be declining over time.
