Assignment 4

a. Merging (10 pts)

Your job is to integrate these data sets so that we have data with both COVID-19 cases and population. Use a merge that ensures that every row of the COVID-19 dataset remains, regardless of whether we have data about its population. Note that the COVID-19 dataset also has names for “areas” that are not necessarily countries (e.g. the ‘Diamond Princess’, a cruise ship which had a number of cases). You may use displayName from the COVID-19 dataset here to match (country also works to match “Czech Republic” but then fails on “China”). Name the merged data frame merged.

b. Cleanup (10 pts)

[Updated: 3/29: 25 instead of 17.] You should find 25 “areas” that do not have matched population data. You can find these by checking for null entries in the population part of the merged data. If merged is the data frame, merged[merged['Country Code'].isnull()] will show these rows. Some of these are due to different names, and others do not exist in the dataset (due to the nature of the area or decisions by the World Bank). Let’s try to fix those countries that have data. Use any means to find the country codes the World Bank uses for the countries. You can also find these codes here on Wikipedia.

Copy the code below into your notebook and update the translate dictionary with by replacing the appropriate country codes with the three-letter codes. Note that there are seven “areas” including the first three for which you will not find matching rows in the population data, and those may remain None. Then, run the code. (Make sure your merged dataset is named merged or modified the code accordingly.)

translate = {'Jersey': None,
             'Guernsey': None,
             'Kyrgyzstan': None,
             'China (mainland)': 'CHN',
             'Saint Vincent and the Grenadines': 'VCT',
             'Saint Lucia': 'LCA',
             'Congo': 'COG',
             'Cape Verde': 'CPV',
             'Bahamas': 'BHS',
             'Côte d’Ivoire': 'CIV',
             'Iran': 'IRN',
             'South Korea': None,
             'Diamond Princess': None,
             'Czechia': None,
             'Hong Kong SAR': None,
             'Egypt': None,
             'Russia': None,
             'Taiwan': None,
             'Slovakia': None,
             'Brunei': None,
             'Palestinian Authority': None,
             'Venezuela': None,
             'Macao SAR': None,
             'Ivory Coast': None,
             'Congo (DRC)': None,
             'Congo': None,
             'Vatican City': None}

covid_df['countryCode'] = merged['Country Code']
covid_df.loc[:,'countryCode'].update(covid_df['displayName'].map(translate))

Finally, merge the covid_df and pop_df again but using the countryCode and Country Code columns this time. Again, we want to preserve all rows from the COVID-19 data frame regardless of whether they have matching population data. If you call your final merged data frame merged, you can find the 25 countries that were missing data via:

fields = ['displayName',
          'Population ages 0-14, total',
          'Population ages 15-64, total',
          'Population ages 65 and above, total',
          'Population, total']
merged[merged['displayName'].isin(translate.keys())][fields]

Your numbers for the 25 areas that were missing population data should look like:

c. Analysis (10 pts)

Now, compute the fraction of the population that has tested positive for COVID-19. Note that this does not necessarily indicate the total fraction of people who have or have had COVID-19 because some countries test different numbers of people via different criteria. Sort these values in a descending order, ignoring NaN or inf values. Show the five countries that have the highest fractions of their populations that have confirmed cases.

Finally, plot the fraction of people over 65 versus the fraction testing positive. First compute the fraction of people over 65. Then, use pandas’ plot.scatter method to do this. Try setting the optional argument ylim to (0,0.0015) to better see the data.

a. Merging (10 pts)

Load the Excel data (cckp_historical_data_0.xls). Note that this is an Excel file, and will require the xlrd python module to work. You can install it via conda. Remember, you must pass an argument to read_excel indicating which sheet you wish to load. We want the temperature data by country. Now, using the same merged data frame from Part 1, merge in the new temperature data. Merge the temperature data with the merged data from part 1. The temperature data includes the country code making it easier to merge with our data. You must determine which column this is and merge appropriately.

b. Imputation (15 pts)

Despite an easier merge, we still have 24 rows without temperature data. We cannot fix this in a similar manner as Part a because the dataset doesn’t have the data. However, we can use the temperatures of countries that are close to the specified country instead. To do this, we need to calculate the closest countries. The COVID-19 dataset gives us locations in the form of latitude (lat) and longitude (long). We can use Haversine Distance to calculate distances. However, we don’t want to substitute temperatures when the nearest country is over 500 kilometers away (perhaps this threshold should be closer). Using scikit-learn (conda install scikit-learn if it isn’t already installed), we can do this efficiently. The below code assumes your data frame (with merged temperatures) is called merged_temp. Then, it computes the closest country (by distance) unless it is further than 500km away, in which case this is -1.

from sklearn.neighbors import DistanceMetric
rlatlon = np.radians(merged_temp[['lat', 'long']].values)
haversine = DistanceMetric.get_metric('haversine')
dist = 6371 * haversine.pairwise(rlatlon) # dist in km
np.fill_diagonal(dist, 1e9) # make sure we don't select point itself
min_dist = dist.argmin(axis=0) # find index of minimum along rows
min_dist_vals = dist.min(axis=0) # find minimum value along rows
closest_idxs = np.where(min_dist_vals < 500, min_dist, -1)

Now use closest_idxs to update the March temperature (Mar_temp) for those countries for which we have a close match. Note that because we are using raw numpy, the indices are numeric and may not match the index of your merged data frame. You should either call reset_index to return the labels to numeric identifiers that will match up closest_idxs or carefully use iloc calls. Also, make sure you only change the countries that are missing temperature data. Some countries will still lack temperature data because we don’t have another country close enough with data. After adding the March temperature data, the 24 missing countries should now show the following:

c. Analysis (10 pts)

Plot the March temperature versus the fraction tested positive, again using the plot.scatter method.

a. Matching (10 pts)

First, let’s load each data source (OWID, JHU, Wiki). Now, we need to attempt to line up different countries. This can be a complex procedure because we need to resolve names that can be different. For example, “DR Congo”, “Congo (Kinshasa)”, and “Democratic Republic of Congo” are all the same country described differently. Wikipedia’s redirection capabilities help here as typing any of the three strings into the search box will direct you to the same page. We can extract the ISO 3661-2 code for each country or region to better compare the datasets. Note that this two-character code is different from the three-character code that the World Bank uses. Download and use the file country-codes.csv to add a new column to each dataset that indicates its country code. Some country names may not translate. That is ok.

b. Grouping (10 pts)

In the JHU data, you will notice that the data is broken down into provinces and states for some countries. We will be comparing across countries so we need to sum all of the Confirmed, Deaths, and Recovered numbers for each Country/Region and Date. Use pandas’ groupby method to sum these three fields by country and date.

c. Merging (10 pts)

Using the country codes we added to each dataset in Part a, we want to merge the three datasets together. We need to do this both by country code and date because we don’t want to merge the data from different dates. Merging three datasets will require two merge calls. Finally, set the index to be a MultiIndex that includes both the country code and date.

d. Fusion (15 pts)

Determine which fields of each dataset link together. For example, the total_cases field in the Our World in Data dataset matches the Confirmed field in the Johns Hopkins data and the cases field in the Wikipedia data.

To fuse the values without any knowledge about the underlying sources of the data, we can try to use an average or take the maximum or minimum. We will do this first for the number of cases. Select only the three columns related to each dataset’s total number of cases. Then, run the agg method on the rows with the aggregation functions “min”, “max”, and “mean”. You should obtain a result like:

Calculate the same results for deaths and recoveries, but note that the Our World in Data dataset does not have recoveries so there will only be two columns.

e. Analysis (10 pts)

First, find the date and country for which the difference between the minimum number of cases and the maximum number of cases is largest.

Second, plot a line graph of the mean number of cases for China (CN), South Korea (KR), the United States (US), and Italy (IL), based on the three datasets.