Skip to content

Data ETL & Analysis on the global and Mexican datasets of the COVID-19 pandemic.

License

Notifications You must be signed in to change notification settings

PhantomInsights/covid-19

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

COVID-19

This project contains scripts that collect and transform datasets of the COVID-19 pandemic for global and Mexican data. It also contains examples that explain the ETL and EDA process.

The following are the summaries of the included scripts:

  • step1_global.py - A Python script that downloads and merges datasets from the Johns Hopkins repository.

  • step1_mx.py - A Python script that downloads a Mexican CSC file and associated .xlsx catalog and merges them into a new CSV file.

  • step2_global.py - A Python script containing several functions to create plots and get insights from the global dataset.

  • step2_mx.py - A Python script containing several functions to create plots and get insights from the Mexican dataset.

Requirements

This project uses the following Python libraries

  • requests - For downloading PDF and CSV files.
  • openpyxl - For reading .xlsx files.
  • pandas - For performing data analysis.
  • NumPy - For fast matrix operations.
  • Matplotlib - For creating plots.
  • seaborn - Used to prettify Matplotlib plots.

ETL Process

Data is not always presented in the most optimal way, this is why we need to pass it through a transformation process.

I'm interested in both global and Mexican specific data (my country). Let's start with the global one.

Global Data

The university of Johns Hopkins provides various datasets that contain global data of the COVID-19 pandemic that are daily updated.

Our goal is to merge the time-series datasets into one CSV file.

The first thing to do is to define the CSV urls and their kind.

CSV_FILES = {
    "confirmed": "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv",
    "deaths": "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv",
    "recovered": "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv"
}

These CSV files have the same structure, the columns are the dates and the index are the countries/regions names.

In my experience it is better to have a datetime index than a string one. This is because pandas has great support for time-series data.

We have a small problem though, we don't know how many columns we will have since they add a new one each day.

What we will do is to first 'scout' one of the CSV files and create a skeleton dict that will then be filled with the real data.

# Initialize the skeleton dict.
data_dict = dict()

# This dictionary will hold all our available dates.
dates_dict = dict()

# This set will hold all the countries/regions we find.
countries = set()

# We will load the first CSV url.
file = list(CSV_FILES.values())[0]

with requests.get(file) as response:

    # Pass the response text into a csv.DictReader object.
    reader = csv.DictReader(response.text.splitlines())

    # Extract the header row and select from the fifth column onwards.
    fields = reader.fieldnames[4:]

    # Convert the header row dates to datetime objects.
    for field in fields:
        dates_dict[field] = "{:%Y-%m-%d}".format(
            datetime.strptime(field, "%m/%d/%y"))

    # Extract the countries/regions by iterating over all rows.
    for row in reader:
        countries.add(row["Country/Region"])

    # Convert the countries set to a list and sort it.
    countries = sorted(list(countries))

    # Combine every date with every country and fill it with zero values.
    for date in dates_dict.values():

        for country in countries:

            temp_key = "{}_{}".format(date, country)
            data_dict[temp_key] = [0, 0, 0]

Once this code is run we end up having a dict similar to this one.

{
    '2020-01-22_Afghanistan': [0, 0, 0],
    '2020-01-22_Albania': [0, 0, 0],
    '2020-01-22_Algeria': [0, 0, 0]
}

The underscore is added so we can later split back the key into its two original values.

Each country will have zero values for each date we find. The drawback is that we will end with several rows with zero values but that's really easy fo filter out with pandas.

Once we have our skeleton dict ready we can start filling it with real data.

We will load the 3 CSV files and check each row to see if it matches with our skeleton dict and then update the corresponding column.

# Iterate over our 3 urls.
for kind, url in CSV_FILES.items():

    with requests.get(url) as response:

        # Pass the response text into a csv.DictReader object.
        reader = csv.DictReader(response.text.splitlines())

        # Iterate over each row of the CSV file.
        for row in reader:

            # Iterate over our available dates.
            for k, v in dates_dict.items():

                # Construct the key for our look up.
                temp_key = "{}_{}".format(v, row["Country/Region"])

                # Update the corresponding value depending on the CSV data kind.
                if kind == "confirmed":
                    data_dict[temp_key][0] += int(row[k])
                elif kind == "deaths":
                    data_dict[temp_key][1] += int(row[k])
                elif kind == "recovered":
                    data_dict[temp_key][2] += int(row[k])

# Save our data to a CSV file.
with open("global_data.csv", "w", encoding="utf-8", newline="") as other_file:

    # Initialize the data list with the header row.
    data_list = data_list = [
        ["isodate", "country", "confirmed", "deaths", "recovered"]]

    # Iterate over our data dict and pass the values to the data list.
    for k, v in data_dict.items():
        isodate, country = k.split("_")
        data_list.append([isodate, country, v[0], v[1], v[2]])

    csv.writer(other_file).writerows(data_list)

Now we have our CSV file saved on our computer, ready to be analyzed.

Mexican Data

The Mexican government provides an encoded CSV file and its assorted catalog file to decode it. combining these two files gives us a new CSV file that contains all the information in a clean way.

We start by defining the urls and their respective file names.

DATA_URL = "http://187.191.75.115/gobmx/salud/datos_abiertos/datos_abiertos_covid19.zip"
DATA_FILE = "./data.zip"

CATALOG_URL = "http://187.191.75.115/gobmx/salud/datos_abiertos/diccionario_datos_covid19.zip"
CATALOG_FILE = "./catalog.zip"

Afterwards we use the requests library to download both.

with requests.get(DATA_URL) as response:

    with open(DATA_FILE, "wb") as temp_file:
        temp_file.write(response.content)

with requests.get(CATALOG_URL) as response:

    with open(CATALOG_FILE, "wb") as temp_file:
        temp_file.write(response.content)

Now that we have both files downloaded we can start combining them. The first thing to do is to convert each sheet from the catalog workbook into a dict.

We start by loading into memory the catalog file from the ZIP file.

with zipfile.ZipFile(CATALOG_FILE) as catalog_zip:
    print("Reading catalog file...")

    with catalog_zip.open(catalog_zip.namelist()[0]) as cat_file:
        print("Processing catalog file...")

        workbook = load_workbook(io.BytesIO(
            cat_file.read()), read_only=True)

Now we feed the dictionaries with the values from each sheet; since they all are very similar and there are too many I will only show you one of them.

# Load the specified sheet by name.
sheet = workbook["Catálogo ORIGEN"]

# Iterate over all the sheet's available rows.
for row in sheet.rows:
    ORIGEN_DICT[str(row[0].value)] = str(row[1].value).strip()

At this point we have 9 dictionaries containing all the data from the workbook. The next step is to read the original CSV file and replace the encoded values with the real ones.

# This list will hold our rows data.
data_list = list()

with zipfile.ZipFile(DATA_FILE) as data_zip:
    print("Reading CSV file...")

    with data_zip.open(data_zip.namelist()[0], "r") as csv_file:
        print("Procesing CSV file...")

        reader = csv.DictReader(
            io.TextIOWrapper(csv_file, encoding="latin-1"))

        # We start iterating over all the CSV rows.
        for row in reader:

We update the values from each row with the real ones using their corresponding dictionaries by passing the original value as the key. I will show you a few examples of how each column is updated.

row["ENTIDAD_UM"] = ENTIDADES_DICT[row["ENTIDAD_UM"]]
row["ORIGEN"] = ORIGEN_DICT[row["ORIGEN"]]
row["SECTOR"] = SECTOR_DICT[row["SECTOR"]]
row["SEXO"] = SEXO_DICT[row["SEXO"]]
row["TIPO_PACIENTE"] = TIPO_PACIENTE_DICT[row["TIPO_PACIENTE"]]
row["NACIONALIDAD"] = NACIONALIDAD_DICT[row["NACIONALIDAD"]]
row["RESULTADO"] = RESULTADO_DICT[row["RESULTADO"]]

After the row is updated we add it to our data_list.

data_list.append(row)

Once we finish processing all rows we simply save the data_list to a CSV file using a csv.DictWriter object.

with open("./mx_data.csv", "w", encoding="utf-8", newline="") as result_csv:
    writer = csv.DictWriter(result_csv, reader.fieldnames)
    writer.writeheader()
    writer.writerows(data_list)
    print("Dataset saved.")

And finally, we delete the ZIP files we downloaded and we end up only with the complete CSV file.

os.remove(DATA_FILE)
os.remove(CATALOG_FILE)

Data Analysis

Now we have 2 CSV files ready to be analyzed and plotted, global_data.csv and casos_confirmados.csv.

We are going to use pandas, NumPy, Matplotlib and seaborn. We will start by importing the required libraries and setting up the styles for our plots.

import matplotlib.dates as mdates
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
import numpy as np
import pandas as pd
import seaborn as sns


sns.set(style="ticks",
        rc={
            "figure.figsize": [15, 10],
            "text.color": "white",
            "legend.fontsize": "large",
            "xtick.labelsize": "x-large",
            "ytick.labelsize": "x-large",
            "axes.labelsize": "x-large",
            "axes.titlesize": "x-large",
            "axes.labelcolor": "white",
            "axes.edgecolor": "white",
            "xtick.color": "white",
            "ytick.color": "white",
            "axes.facecolor": "#111111",
            "figure.facecolor": "#232b2b"}
        )

These styles will apply an elegant dark gray palette to our plots.

Note: You will have different numbers on your results as I did this analysis on older datasets.

Global Data

We start by loading our dataset and specifying the first column as our index, this will turn it into a datetimeindex which is very handy when working with time-series data.

df = pd.read_csv("global_data.csv", parse_dates=["isodate"], index_col=0)

Let's take a look at our DataFrame using the head(), tail() and describe() methods.

df.head()
country confirmed deaths recovered
isodate
2020-01-22 Afghanistan 0 0 0
2020-01-22 Albania 0 0 0
2020-01-22 Algeria 0 0 0
2020-01-22 Andorra 0 0 0
2020-01-22 Angola 0 0 0
df.tail()
isodate country confirmed deaths recovered
2020-04-14 West Bank and Gaza 308 2 62
2020-04-14 Western Sahara 6 0 0
2020-04-14 Yemen 1 0 0
2020-04-14 Zambia 45 2 30
2020-04-14 Zimbabwe 17 3 0
df.describe()
confirmed deaths recovered
count 15540 15540 15540
mean 2007.55 105.364 489.394
std 16754 998.927 4630.27
min 0 0 0
25% 0 0 0
50% 1 0 0
75% 53 1 2
max 607670 25832 78200

We can observe the countries are alphabetically sorted and our datetimeindex worked correctly.

We can also observe all the first rows have zero values, as predicted from the ETL process. This caused an adverse effect on the describe() method, where the results are biased towards zero.

Let's fix this by removing all rows with zero values on their confirmed field.

df = df[df["confirmed"] > 0]
df.describe()
confirmed deaths recovered
count 7793 7793 7793
mean 4003.25 210.107 975.898
std 23490.1 1402.83 6502.32
min 1 0 0
25% 7 0 0
50% 52 1 2
75% 541 7 32
max 607670 25832 78200

This looks better and more accurate. On the next sections we will start producing interesting insights.

Top 10 Countries by Confirmed Cases, Deaths & Recoveries

To get the countries with the highest values we first need to group our DataFrame by the country field and select their max value which happens to be the latest one.

grouped_df = df.groupby("country").max()

Once grouped we use the sort_values() method on the field we are interested in and sort by descending order. From there we print the first 10 rows from the field we previously defined.

# Confirmed cases
print(grouped_df.sort_values("confirmed", ascending=False)["confirmed"][:10])
country confirmed
US 607670
Spain 172541
Italy 162488
France 137875
Germany 131359
United Kingdom 94845
China 83306
Iran 74877
Turkey 65111
Belgium 31119
# Deaths
print(grouped_df.sort_values("deaths", ascending=False)["deaths"][:10])
country deaths
US 25832
Italy 21067
Spain 18056
France 15748
United Kingdom 12129
Iran 4683
Belgium 4157
China 3345
Germany 3294
Netherlands 2955
# Recoveries
print(grouped_df.sort_values("recovered", ascending=False)["recovered"][:10])
country recovered
China 78200
Germany 68200
Spain 67504
Iran 48129
US 47763
Italy 37130
France 29098
Switzerland 13700
Canada 8210
Austria 7633

Daily Global Confirmed Cases, Deaths or Recoveries

Thanks fo the datetimeindex knowing the daily totals is really easy. We will only require to resample our DataFrame by 1 day intervals.

We will start by defining our field (confirmed, deaths or recovered) and resampling method.

field = "deaths"
resampled_df = df.resample("D").sum()

We add 2 new columns to know the daily field totals (difference) and their percent change (change).

resampled_df["difference"] = resampled_df[field].diff()
resampled_df["change"] = resampled_df["difference"].pct_change()

Now we drop NaN values, we do this so the next step doesn't crash the script.

resampled_df.dropna(inplace=True)

This step is optional, the purpose of it is to display the results in a more human readable way.

The difference column gets converted from float to int and the change column gets some string formatting, which includes adding a percent sign and rounding up the numbers to the second decimal.

resampled_df["difference"] = resampled_df["difference"].apply(int)

resampled_df["change"] = resampled_df["change"].apply(
    lambda x: str(np.round(x * 100, 2)) + "%")

And finally, we print the latest 10 rows.

print(resampled_df[[field, "difference", "change"]][-10:])
isodate deaths difference change
2020-04-05 69374 4768 -18.06%
2020-04-06 74565 5191 8.87%
2020-04-07 81865 7300 40.63%
2020-04-08 88338 6473 -11.33%
2020-04-09 95455 7117 9.95%
2020-04-10 102525 7070 -0.66%
2020-04-11 108503 5978 -15.45%
2020-04-12 114091 5588 -6.52%
2020-04-13 119482 5391 -3.53%
2020-04-14 125984 6502 20.61%

Daily Confirmed Cases, Deaths or Recoveries for any Country

Now we will know the daily confirmed cases, deaths or recoveries and their growth for any given country. We will use the US for this example.

We start by defining the country and which field we want (confirmed, deaths or recovered). Afterwards we filter our DataFrame so it only includes values of that country.

field = "deaths"
country = "US"
filtered_df = df[df["country"] == country].copy()

We add 2 new columns to know the daily field totals (difference) and their percent change (change).

filtered_df["difference"] = filtered_df[field].diff()
filtered_df["change"] = filtered_df["difference"].pct_change()

Now we drop NaN values, we do this so the next step doesn't crash the script.

filtered_df.dropna(inplace=True)

This step is optional, the purpose of it is to display the results in a more human readable way.

The difference column gets converted from float to int and the change column gets some string formatting, which includes adding a percent sign and rounding up the numbers to the second decimal.

filtered_df["difference"] = filtered_df["difference"].apply(int)

filtered_df["change"] = filtered_df["change"].apply(
    lambda x: str(np.round(x * 100, 2)) + "%")

And finally, we print the latest 10 rows.

print(filtered_df[[field, "difference", "change"]][-10:])
isodate deaths difference change
2020-04-05 9619 1212 -8.18%
2020-04-06 10783 1164 -3.96%
2020-04-07 12722 1939 66.58%
2020-04-08 14695 1973 1.75%
2020-04-09 16478 1783 -9.63%
2020-04-10 18586 2108 18.23%
2020-04-11 20463 1877 -10.96%
2020-04-12 22020 1557 -17.05%
2020-04-13 23529 1509 -3.08%
2020-04-14 25832 2303 52.62%

Feel free to try this example with other country names, such as Italy, Spain or Iran.

Days from 100 to 3,200 Confirmed Cases

This one is quite interesting, we will know how many days it took to reach from 100 to 3,200 confirmed cases.

For this exercise we will use custom bins for the exponential growth (100-199, 200-399, and so on).

We start by removing all rows lower than 100 confirmed cases.

df = df[df["confirmed"] >= 100]

We define our bins and their labels.

bins = [(100, 199), (200, 399), (400, 799), (800, 1599), (1600, 3200)]
labels = ["100-199", "200-399", "400-799", "800-1599", "1600-3200"]

We extract all the available countries in the dataset.

all_countries = sorted(df["country"].unique().tolist())

These lists will be filled with values in the next step.

valid_countries = list()
data_list = list()

We iterate over all the countries we have and create temporary DataFrames with them.

for country in all_countries:

    temp_df = df[df["country"] == country]

    # Only process countries if their confirmed cases are equal or greater than 3,200.
    if temp_df["confirmed"].max() >= 3200:
        temp_list = list()

        # We iterate over our bins and count how many days each one has.
        for item in bins:
            temp_list.append(temp_df[(temp_df["confirmed"] >= item[0]) & (
                temp_df["confirmed"] <= item[1])]["confirmed"].count())

        data_list.append(temp_list)
        valid_countries.append(country)

We create a final DataFrame with the results and add a new column with the total days from 100 to 3,200 confirmed cases.

final_df = pd.DataFrame(data_list, index=valid_countries, columns=labels)
final_df["total"] = final_df.sum(axis=1)
print(final_df)
100-199 200-399 400-799 800-1599 1600-3200 total
Australia 3 4 4 2 5 18
Austria 3 2 2 3 4 14
Belarus 3 2 3 3 4 15
Belgium 2 5 2 4 3 16
Brazil 3 3 2 2 4 14
Canada 4 1 3 4 2 14
Chile 1 3 4 3 6 17
China 0 0 2 2 2 6
Czechia 2 3 2 5 6 18
Denmark 0 1 2 11 8 22
Dominican Republic 1 4 3 7 9 24
Ecuador 2 1 2 5 6 16
France 3 3 1 3 3 13
Germany 3 1 3 3 2 12
India 6 3 4 5 4 22
Indonesia 3 3 5 6 8 25
Iran 1 2 1 2 2 8
Ireland 3 2 3 4 5 17
Israel 3 4 3 3 3 16
Italy 1 2 2 2 4 11
Japan 6 8 9 13 9 45
Korea, South 1 1 2 3 3 10
Luxembourg 1 2 3 4 14 24
Malaysia 5 1 4 5 10 25
Mexico 2 4 4 6 6 22
Netherlands 2 3 2 4 4 15
Norway 3 1 3 6 7 20
Pakistan 1 2 4 7 7 21
Panama 2 4 4 6 8 24
Peru 2 5 5 6 4 22
Philippines 4 5 4 4 5 22
Poland 3 3 4 4 6 20
Portugal 2 2 3 2 4 13
Qatar 0 4 17 4 8 33
Romania 4 4 3 4 6 21
Russia 3 3 3 4 3 16
Saudi Arabia 5 3 3 7 8 26
Serbia 3 5 4 4 7 23
Singapore 13 8 7 11 6 45
Spain 2 2 3 1 3 11
Sweden 2 3 2 7 8 22
Switzerland 1 4 3 2 4 14
Turkey 1 1 1 2 2 7
US 2 2 3 2 3 12
Ukraine 2 2 4 6 6 20
United Arab Emirates 6 3 5 4 5 23
United Kingdom 2 4 2 4

Daily Global Growth

Let's start the plots section with a straightforward one. We will plot the daily growth of confirmed cases, deaths and recoveries of all countries combined.

We will filter out rows with zero confirmed cases.

df = df[df["confirmed"] > 0]

Resample the data by 1 day intervals and sum the daily totals.

resampled_df = df.resample("D").sum()

Create 3 line plots on the same axis, one for each field.

fig, ax = plt.subplots()

ax.plot(resampled_df.index,
        resampled_df["confirmed"], label="Confirmed", color="gold")

ax.plot(resampled_df.index,
        resampled_df["deaths"], label="Deaths", color="lightblue")

ax.plot(resampled_df.index,
        resampled_df["recovered"], label="Recoveries", color="lime")

Customize our tickers.

ax.xaxis.set_major_locator(mdates.DayLocator(interval=7))
ax.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d"))
ax.yaxis.set_major_locator(ticker.MaxNLocator())
ax.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

Add final customizations.

plt.grid(linewidth=0.5)
plt.legend(loc=2)
plt.title("Daily Confirmed Cases, Deaths & Recoveries", pad=15)
plt.xlabel("Date (2020)", labelpad=15)
plt.ylabel("Cumulative Count", labelpad=15)

plt.show()

Daily Global Growth

Daily Growth for any Country

This plot is very similar to the previous one, the only difference is that it shows the daily growth only for one country, in this example it will be the US.

We filter out rows with zero confirmed cases and only select rows that belong to the country we defined.

country = "US"
df = df[(df["confirmed"] > 0) & (df["country"] == country)]

Create 3 line plots on the same axis, one for each field.

fig, ax = plt.subplots()

ax.plot(df.index, df["confirmed"], label="Confirmed", color="gold")
ax.plot(df.index, df["deaths"], label="Deaths", color="lightblue")
ax.plot(df.index, df["recovered"], label="Recoveries", color="lime")

Customize our tickers.

ax.xaxis.set_major_locator(mdates.DayLocator(interval=7))
ax.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d"))
ax.yaxis.set_major_locator(ticker.MaxNLocator())
ax.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

Add final customizations.

plt.grid(linewidth=0.5)
plt.legend(loc=2)
plt.title("Daily Confirmed Cases, Deaths & Recoveries", pad=15)
plt.xlabel("Date (2020)", labelpad=15)
plt.ylabel("Cumulative Count", labelpad=15)

plt.show()

Country Daily Growth

Daily Global Counts

This plot is simlar to the previous one, it will show us the daily counts of confirmed cases, deaths and recoveries for all the countries combined.

We filter out rows with zero confirmed cases.

df = df[df["confirmed"] > 0]

Resample the data by 1 day intervals and sum the daily totals.

resampled_df = df.resample("D").sum()

Add 3 new columns, one for each field counts.

resampled_df["confirmed_difference"] = resampled_df["confirmed"].diff()
resampled_df["deaths_difference"] = resampled_df["deaths"].diff()
resampled_df["recovered_difference"] = resampled_df["recovered"].diff()

Create 3 line plots on the same axis, one for each field counts.

fig, ax = plt.subplots()

ax.plot(resampled_df.index,
        resampled_df["confirmed_difference"], label="Confirmed", color="gold")

ax.plot(resampled_df.index,
        resampled_df["deaths_difference"], label="Deaths", color="lightblue")

ax.plot(resampled_df.index,
        resampled_df["recovered_difference"], label="Recoveries", color="lime")

Customize our tickers.

ax.xaxis.set_major_locator(mdates.DayLocator(interval=7))
ax.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d"))
ax.yaxis.set_major_locator(ticker.MaxNLocator())
ax.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

Add final customizations.

plt.grid(linewidth=0.5)
plt.legend(loc=2)
plt.title("Daily Confirmed Cases, Deaths & Recoveries", pad=15)
plt.xlabel("Date (2020)", labelpad=15)
plt.ylabel("Daily Count", labelpad=15)

plt.show()

Daily Global Count

Daily Counts for any Country

This plot is very similar to the previous one, the only difference is that it shows the daily counts only for one country, in this example it will be the US.

We filter out rows with zero confirmed cases and only select rows that belong to the country we defined.

country = "US"
df = df[(df["confirmed"] > 0) & (df["country"] == country)].copy()

Add 3 new columns, one for each field counts.

df["confirmed_difference"] = df["confirmed"].diff()
df["deaths_difference"] = df["deaths"].diff()
df["recovered_difference"] = df["recovered"].diff()

Create 3 line plots on the same axis, one for each field counts.

fig, ax = plt.subplots()

ax.plot(df.index, df["confirmed_difference"],
        label="Confirmed", color="gold")
ax.plot(df.index, df["deaths_difference"],
        label="Deaths", color="lightblue")
ax.plot(df.index, df["recovered_difference"],
        label="Recoveries", color="lime")

Customize our tickers.

ax.xaxis.set_major_locator(mdates.DayLocator(interval=7))
ax.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d"))
ax.yaxis.set_major_locator(ticker.MaxNLocator())
ax.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

Add final customizations.

plt.grid(linewidth=0.5)
plt.legend(loc=2)
plt.title("Daily Confirmed Cases, Deaths & Recoveries Counts", pad=15)
plt.xlabel("Date (2020)", labelpad=15)
plt.ylabel("Daily Count", labelpad=15)

plt.show()

Country Daily Counts

Daily Counts Comparison

This plot will compare the daily counts of the field we define between the countries we want.

We will start by defining a dictionary of countries, their labels and colors for their lines.

COUNTRIES = [
    ["US", "United States", "lightblue"],
    ["Italy", "Italy", "pink"],
    ["Spain", "Spain", "orange"],
    ["France", "France", "yellow"],
    ["United Kingdom", "United Kingdom", "lime"]
]

Then we will define the field and remove all 0 values from the DataFrame.

field = "deaths"
df = df[df[field] > 0]

Create a line plot for each country and add it to the same axis.

fig, ax = plt.subplots()

for country in COUNTRIES:
    temp_df = df[df["country"] == country[0]].copy()
    temp_df["difference"] = temp_df[field].diff()

    ax.plot(temp_df.index, temp_df["difference"],
            label=country[1], color=country[2])

Customize our tickers.

ax.xaxis.set_major_locator(mdates.DayLocator(interval=7))
ax.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d"))
ax.yaxis.set_major_locator(ticker.MaxNLocator())
ax.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

Add final customizations.

ax.grid(linewidth=0.5)
ax.legend(loc=2)
plt.title("Daily Comparison Between Countries", pad=15)
plt.xlabel("Date (2020)", labelpad=15)
plt.ylabel("Daily Count", labelpad=15)

plt.show()

Daily Comparison

If's fascinating how much insights we got from only 5 fields (date, country, confirmed, deaths and recoveries).

This is the end for the global data section, coming next is the Mexican dataset.

Mexican Data

We start by loading our dataset with no special parameters.

df = pd.read_csv("mx_data.csv")

We would normally use the head(), tail() and describe() methods to take a look at our DataFrame but since this one has several fields it breaks the Markdown.

Instead of that I will briefly describe what's inside this DataFrame:

  • We have 4 datetime fields which are: latest update, date of entry, date of first symptoms and date of death.

  • Several fields of preconditions, such as pregnancy, overweight, hypertension, diabetes.

  • State and municipality of residence.

  • Current status of the patient COVID-19 test (confirmed COVID-19, not confirmed COVID-19 and pending result).

  • Age and gender.

We are going to use some of these fields on the next sections.

Confirmed Cases by State

Mexico has 32 states and as of now all of them have confirmed cases.

To know how many tests each state has made we will use the value_counts() method on the ENTIDAD_RES field.

print(df["ENTIDAD_RES"].value_counts())
ENTIDAD_RES
CIUDAD DE MÉXICO 33657
MÉXICO 23349
NUEVO LEÓN 8418
JALISCO 7920
GUANAJUATO 7587
BAJA CALIFORNIA 5420
COAHUILA DE ZARAGOZA 5243
VERACRUZ DE IGNACIO DE LA LLAVE 4410
TABASCO 4336
SINALOA 4126
TAMAULIPAS 4099
PUEBLA 3766
MICHOACÁN DE OCAMPO 3280
AGUASCALIENTES 3086
YUCATÁN 2923
SAN LUIS POTOSÍ 2628
SONORA 2580
CHIHUAHUA 2294
QUINTANA ROO 2267
GUERRERO 2054
MORELOS 1955
HIDALGO 1716
TLAXCALA 1611
BAJA CALIFORNIA SUR 1558
CHIAPAS 1401
DURANGO 1269
QUERÉTARO 1268
OAXACA 1207
ZACATECAS 1057
NAYARIT 981
CAMPECHE 702
COLIMA 329

The state with most tests done is the capital of the country (Mexico City).

What we are really interested in are confirmed COVID-19 cases, we will make a simple filter and use the value_counts() method again.

print(df[df["RESULTADO"] == "Positivo SARS-CoV-2"]["ENTIDAD_RES"].value_counts())
ENTIDAD_RES
CIUDAD DE MÉXICO 10946
MÉXICO 6813
BAJA CALIFORNIA 2764
TABASCO 1976
SINALOA 1620
VERACRUZ DE IGNACIO DE LA LLAVE 1574
PUEBLA 1213
QUINTANA ROO 1177
YUCATÁN 924
MORELOS 915
TAMAULIPAS 799
CHIHUAHUA 768
NUEVO LEÓN 717
JALISCO 699
MICHOACÁN DE OCAMPO 678
GUERRERO 670
SONORA 642
HIDALGO 637
COAHUILA DE ZARAGOZA 616
GUANAJUATO 580
CHIAPAS 450
TLAXCALA 438
BAJA CALIFORNIA SUR 409
AGUASCALIENTES 398
SAN LUIS POTOSÍ 338
QUERÉTARO 315
OAXACA 291
NAYARIT 252
CAMPECHE 226
ZACATECAS 168
DURANGO 127
COLIMA 46

That was really easy, let's up our game and do some table pivoting and MultiIndex calculations.

We will start by only taking into account confirmed COVID-19 cases.

df = df[df["RESULTADO"] == "Positivo SARS-CoV-2"]

We will use the next value to calculate the percentages.

total_cases = len(df)

We will pivot the table, the gender will be our columns and the state wil be our index.

pivoted_df = df.pivot_table(
    index="ENTIDAD_RES", columns="SEXO", aggfunc="count")

We will add two new columns to this DataFrame, one for each gender percentage. This way we will know the total percentage of gender by state.

Note: These new columns can be added to any other column. We choose the first one (EDAD).

pivoted_df["EDAD", "female_percentage"] = np.round(
    pivoted_df["EDAD", "MUJER"] / total_cases * 100, 2)

pivoted_df["EDAD", "male_percentage"] = np.round(
    pivoted_df["EDAD", "HOMBRE"] / total_cases * 100, 2)

We rename the columns so they are human readable.

pivoted_df.rename(columns={"HOMBRE": "Male",
                            "MUJER": "Female",
                            "male_percentage": "Male %",
                            "female_percentage": "Female %"}, level=1, inplace=True)

print(pivoted_df["edad"])
ENTIDAD_RES Male Female Female % Male %
AGUASCALIENTES 198 200 0.5 0.49
BAJA CALIFORNIA 1530 1234 3.07 3.81
BAJA CALIFORNIA SUR 217 192 0.48 0.54
CAMPECHE 163 63 0.16 0.41
CHIAPAS 277 173 0.43 0.69
CHIHUAHUA 445 323 0.8 1.11
CIUDAD DE MÉXICO 6303 4643 11.55 15.68
COAHUILA DE ZARAGOZA 317 299 0.74 0.79
COLIMA 29 17 0.04 0.07
DURANGO 59 68 0.17 0.15
GUANAJUATO 313 267 0.66 0.78
GUERRERO 404 266 0.66 1.01
HIDALGO 390 247 0.61 0.97
JALISCO 423 276 0.69 1.05
MICHOACÁN DE OCAMPO 401 277 0.69 1
MORELOS 554 361 0.9 1.38
MÉXICO 4021 2792 6.95 10.01
NAYARIT 130 122 0.3 0.32
NUEVO LEÓN 415 302 0.75 1.03
OAXACA 171 120 0.3 0.43
PUEBLA 728 485 1.21 1.81
QUERÉTARO 167 148 0.37 0.42
QUINTANA ROO 728 449 1.12 1.81
SAN LUIS POTOSÍ 186 152 0.38 0.46
SINALOA 896 724 1.8 2.23
SONORA 379 263 0.65 0.94
TABASCO 1155 821 2.04 2.87
TAMAULIPAS 510 289 0.72 1.27
TLAXCALA 246 192 0.48 0.61
VERACRUZ DE IGNACIO DE LA LLAVE 998 576 1.43 2.48
YUCATÁN 537 387 0.96 1.34
ZACATECAS 97 71 0.18 0.24

And now we have a more complete and useful table of summaries.

Initial Symptoms Growth & Daily Counts

Let's start our plots section with a simple one. This plot will show us one aspect of the daily progression of the pandemic in Mexico.

We will plot the growth and counts when the patients had the initial symptoms of COVID-19.

We start by removing all the rows that are not COVID-19 positive.

df = df[df["RESULTADO"] == "Positivo SARS-CoV-2"]

We group our DataFrame by day of initial symptoms and aggregate them by number of ocurrences.

grouped_df = df.groupby("FECHA_SINTOMAS").count()

Convert the index to datetimeindex.

grouped_df.index = pd.to_datetime(grouped_df.index)

We add a new column that will hold the cumulative sum of the previous counts.

grouped_df["cumsum"] = grouped_df["SECTOR"].cumsum()

Note: We chose the 'SECTOR' column but any other would have worked the same.

We create 2 basic line plots with the previously created column.

fig, (ax1, ax2) = plt.subplots(2)

ax1.plot(grouped_df.index, grouped_df["cumsum"],
        label="Initial Symptoms Growth", color="lime")

ax2.plot(grouped_df.index, grouped_df["SECTOR"],
        label="Initial Symptoms Counts", color="gold")

Customize our tickers. The y-axis will be formatted with month and day.

ax1.xaxis.set_major_locator(ticker.MaxNLocator(15))
ax1.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d"))
ax1.yaxis.set_major_locator(ticker.MaxNLocator(10))
ax1.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

ax2.xaxis.set_major_locator(ticker.MaxNLocator(15))
ax2.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d"))
ax2.yaxis.set_major_locator(ticker.MaxNLocator(10))
ax2.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

Add final customizations.

ax1.grid(linewidth=0.5)
ax1.legend(loc=2)    
ax1.set_title("Initial Symptoms Growth & Daily Counts", pad=15)
ax1.set_ylabel("COVID-19 Positive Tests", labelpad=15)

ax2.grid(linewidth=0.5)
ax2.legend(loc=2)    
ax2.set_ylabel("COVID-19 Positive Tests", labelpad=15)

plt.show()

Mexico Symptoms Growth

There are 2 important things to note, the first one is that there were pepole with COVID-19 symptoms back in January and there's a bias on the counts in the last 2 weeks.

This bias gets corrected with the daily reports, it is a side effect of slow verification of data.

Deaths Growth & Daily Counts

This plot is almost the same as the previous one, the only difference is that we will plot only the deaths caused by COVID-19.

We start by filtering in only the deaths caused by COVID-19.

df = df[(df["RESULTADO"] == "Positivo SARS-CoV-2")
        & (df["FECHA_DEF"] != "9999-99-99")]

In this dataset the only way to know if someone has died is if their date of death is not 9999-99-99.

We group our DataFrame by day of death and aggregate them by number of ocurrences.

grouped_df = df.groupby("FECHA_DEF").count()

Convert the index to datetimeindex.

grouped_df.index = pd.to_datetime(grouped_df.index)

We add a new column that will hold the cumulative sum of the previous counts.

grouped_df["cumsum"] = grouped_df["SECTOR"].cumsum()

Note: We chose the 'SECTOR' column but any other would have worked the same.

We create 2 basic line plots with the previously created column.

fig, (ax1, ax2) = plt.subplots(2)

ax1.plot(grouped_df.index, grouped_df["cumsum"],
            label="Deaths Growth", color="lime")

ax2.plot(grouped_df.index, grouped_df["SECTOR"],
            label="Deaths Counts", color="gold")

Customize our tickers. The y-axis will be formatted with month and day.

ax1.xaxis.set_major_locator(ticker.MaxNLocator(15))
ax1.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d"))
ax1.yaxis.set_major_locator(ticker.MaxNLocator(10))
ax1.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

ax2.xaxis.set_major_locator(ticker.MaxNLocator(15))
ax2.xaxis.set_major_formatter(mdates.DateFormatter("%m-%d"))
ax2.yaxis.set_major_locator(ticker.MaxNLocator(10))
ax2.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

Add final customizations.

ax1.grid(linewidth=0.5)
ax1.legend(loc=2)
ax1.set_title("Deaths Growth & Daily Counts", pad=15)
ax1.set_ylabel("COVID-19 Deaths", labelpad=15)

ax2.grid(linewidth=0.5)
ax2.legend(loc=2)
ax2.set_ylabel("COVID-19 Deaths", labelpad=15)

plt.show()

Mexico Deaths Growth

We can also observe the same bias in the last 2 weeks as seen in the previous plot.

COVID-19 Test Results

Now we will know the distribution of the test results made in Mexico.

The RESULTADO field has 3 possible values. We create one column for each one.

# This one will be used to calculate tolals.
df["tests"] = 1

df["positive"] = df["RESULTADO"].apply(
    lambda x: 1 if x == "Positivo SARS-CoV-2" else 0)

df["not_positive"] = df["RESULTADO"].apply(
    lambda x: 1 if x == "No positivo SARS-CoV-2" else 0)

df["pending"] = df["RESULTADO"].apply(
    lambda x: 1 if x == "Resultado pendiente" else 0)

We group the DataFrame by the date of entry and aggregate them by sum.

df = df.groupby("FECHA_INGRESO").sum()

Convert the index to datetime.

df.index = pd.to_datetime(df.index)

These percentages will be used for the plots labels.

total = df["tests"].sum()
positive = round(df["positive"].sum() / total * 100, 2)
not_positive = round(df["not_positive"].sum() / total * 100, 2)
pending = round(df["pending"].sum() / total * 100, 2)

We create 3 vertical bar plots with the previously created columns. We will stack the positive and pending values over the not positive ones.

fix, ax = plt.subplots()

ax.bar(df.index, df["positive"], color="#ef6c00",
        label=f"SARS-CoV-2 Positive ({positive}%)", linewidth=0)

ax.bar(df.index, df["not_positive"], color="#42a5f5",
        label=f"SARS-CoV-2 Not Positive ({not_positive}%)", bottom=df["positive"] + df["pending"], linewidth=0)

ax.bar(df.index, df["pending"], color="#ffca28",
        label=f"Pending Result ({pending}%)", bottom=df["positive"], linewidth=0)

Customize our tickers. The y-axis will be formatted with month and day.

ax.xaxis.set_major_locator(ticker.MaxNLocator(15))
ax.xaxis.set_major_formatter(mdates.DateFormatter("%d-%m"))
ax.yaxis.set_major_locator(ticker.MaxNLocator(12))
ax.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

Add final customizations.

plt.title("COVID-19 Test Results", pad=15)
plt.legend(loc=2)
plt.grid(linewidth=0.5)
plt.ylabel("Number of Daily Results", labelpad=15)
plt.xlabel("2020", labelpad=15)

plt.show()

Mexico Tests

Age and Sex Distribution

Knowing the age groups is very important and for this exercise we will bin our data and then group it by gender. We will use custom bins that wlil hold values in steps of 10 (0-9, 10-19, 20-29 and so on).

On the 90-99 bin we will make an exception and define it as 90-120 since that age group has the least values of them all.

We start by only selecting rows that are COVID-19 positive.

df = df[df["RESULTADO"] == "Positivo SARS-CoV-2"]

Then we will create one DataFrame for each gender.

male_df = df[df["SEXO"] == "HOMBRE"]
female_df = df[df["SEXO"] == "MUJER"]

We then define 2 lists that will be used for our bins.

age_groups = list()
labels = list()

We start a loop from 0 to 100 with steps of 10. This will fill our previous 2 lists.

for i in range(0, 100, 10):

    # Our latest bin will be for ages >= 90.
    if i == 90:
        age_groups.append((i, i+30))
        labels.append("≥ 90")
    else:
        age_groups.append((i, i+9))
        labels.append("{}-{}".format(i, i+9))

We use the previous tuples to build our indexer and slice our DataFrames with it.

male_values = list()
female_values = list()

for start, end in age_groups:
    
    male_values.append(
        male_df[male_df["EDAD"].between(start, end)]["EDAD"].count())

    female_values.append(
        female_df[female_df["EDAD"].between(start, end)]["EDAD"].count())

We create 2 bar plots in the same axis, each plot will have the values for their respective DataFrame.

fig, ax = plt.subplots()

bars = ax.bar(
    [i - 0.225 for i in range(len(labels))], height=male_values,  width=0.45,  color="#1565c0", linewidth=0)

# This loop creates small texts with the absolute values above each bar (first set of bars).
for bar in bars:
    height = bar.get_height()

    plt.text(bar.get_x() + bar.get_width()/2.0, height * 1.01,
                "{:,}".format(height), ha="center", va="bottom")

bars2 = ax.bar(
    [i + 0.225 for i in range(len(labels))], height=female_values,  width=0.45,  color="#f06292", linewidth=0)

# This loop creates small texts with the absolute values above each bar (second set of bars).
for bar2 in bars2:
    height2 = bar2.get_height()

    plt.text(bar2.get_x() + bar2.get_width()/2.0, height2 * 1.01,
                "{:,}".format(height2), ha="center", va="bottom")

Customize our tickers.

ax.yaxis.set_major_locator(ticker.MaxNLocator())
ax.yaxis.set_major_formatter(ticker.StrMethodFormatter("{x:,.0f}"))

Add final customizations.

plt.grid(linewidth=0.5)
plt.legend(["Male", "Female"], loc=2)
plt.xticks(range(len(labels)), labels)
plt.title("Age and Sex Distribution", pad=15)
plt.xlabel("Age Range", labelpad=15)
plt.ylabel("Confirmed Cases", labelpad=15)

plt.show()

Age Distribution

We can observe that most cases fall within the 30-60 age range and men have more registered cases than women in all age groups.

And that's it for this dataset. We got some really interesting insights from some of the fields we have available.

Conclusion

Getting clean data is not always easy and can discourage people from doing their own analysis. That's why I wanted to shore these scripts with you so you can accelerate your workflow and get interesting insights.

I hope you have enjoyed the examples for tables and plots, you are always welcome to experiment and ask your questions in the issues tab.

Become a Patron!

Releases

No releases published

Packages

No packages published

Languages