The victims of the COVID-19

Mix.install([
  {:req, "~> 0.4"},
  {:explorer, "~> 0.8"},
  {:kino_vega_lite, "~> 0.1"}
])

Intro

This notebook is based on the work of Judite Cypreste and is originally available at https://github.com/juditecypreste/as-vitimas-do-coronavirus (in Portuguese).

Dependencies

We need Req in order to download the CSV files and Explorer that will be handy to transform it to a dataset. Finally VegaLite helps to render Brazilian cities provided by IBGE and Kino renders the table at the end.

Initial dataframe

File.mkdir_p("covid-19")
filename = "covid-19/covid_full.csv"

# Only downloads the file if it doesn't exist yet.
unless File.exists?(filename) do
  # This file contains all data related to cases in all cities from the country
  csv = Req.get!("https://data.brasil.io/dataset/covid19/caso_full.csv.gz").body

  # Req gives us an umcompressed file :)
  File.write!(filename, csv)
end

Now we need to build the dataframe based on the CSV file that was downloaded.

require Explorer.DataFrame, as: DF
alias Explorer.Series

important_columns = [
  "city",
  "city_ibge_code",
  "date",
  "last_available_deaths",
  "estimated_population",
  "is_last",
  "place_type",
  "state"
]

df =
  DF.from_csv!(filename,
    columns: important_columns,
    dtypes: [{"city_ibge_code", :category}, {"last_available_deaths", :integer}]
  )

Filters

The CSV have cumulative data, so we need to select the last rows for each city. Additionally we need to select only a few columns and drop the ones that have nil values.

df =
  df
  |> DF.filter(is_last == true and place_type == "city")
  |> DF.discard(["is_last", "place_type"])
  |> DF.drop_nil()

Analyzing the dataframe

# Since the lines are the last one for each city, we only arrange by the last available deaths.

df = DF.sort_by(df, desc: last_available_deaths)

Building the map

Originally the study was showing cities that had at least one death. Unfortunatelly almost all cities had deaths registered since the beginning of the pandemic. So I decided to show the deaths per 100k rate in this study.

# First we rename some columns, and then we calculate the rate.
df =
  df
  |> DF.rename(%{"city_ibge_code" => "id", "last_available_deaths" => "deaths"})
  |> DF.discard([:date])
  |> DF.mutate(deaths_per_100k: deaths * 100_000 / estimated_population)

deaths_per_city = DF.to_rows(df)

And finally the map is built with VegaLite.

We are fetching the TopoJSON data from this awesome service from IBGE: API de malhas geográficas V3.

alias VegaLite, as: Vl

Vl.new(width: 700, height: 600)
|> Vl.data_from_url(
  "https://servicodados.ibge.gov.br/api/v3/malhas/paises/BR?intrarregiao=municipio&qualidade=minima&formato=application/json",
  format: [type: :topojson, feature: "BRMU"]
)
|> Vl.transform(
  lookup: "properties.codarea",
  from: [
    data: [values: deaths_per_city],
    key: "id",
    fields: ["deaths_per_100k", "city"]
  ]
)
|> Vl.projection(type: :mercator)
|> Vl.mark(:geoshape)
|> Vl.encode_field(:color, "deaths_per_100k", type: :quantitative, title: "Deaths per 100k")
|> Vl.encode(:tooltip, [
  [field: "city", type: :nominal, title: "City"],
  [field: "deaths_per_100k", type: :quantitative, title: "Rate"]
])
|> Vl.config(view: [stroke: nil])

The full list of cities and the final numbers are in the following table.

Kino.DataTable.new(deaths_per_city)

Maps for each region

Brazil is divided in 5 regions. The following maps shows the same data, but focused in each region.

defmodule MapHelper do
  @features %{"N" => "GR1MU", "NE" => "GR2MU", "SE" => "GR3MU", "S" => "GR4MU", "CO" => "GR5MU"}
  # It uses https://servicodados.ibge.gov.br/api/docs/malhas?versao=3#api-Malhas-regioesIdGet
  def draw_region(region_code, deaths_per_city) when region_code in ~w(N NE SE S CO) do
    Vl.new(width: 700, height: 600)
    |> Vl.data_from_url(
      "https://servicodados.ibge.gov.br/api/v3/malhas/regioes/#{region_code}?intrarregiao=municipio&qualidade=minima&formato=application/json",
      format: [type: :topojson, feature: Map.fetch!(@features, region_code)]
    )
    |> Vl.transform(
      lookup: "properties.codarea",
      from: [
        data: [values: deaths_per_city],
        key: "id",
        fields: ["deaths_per_100k", "city"]
      ]
    )
    |> Vl.projection(type: :mercator)
    |> Vl.mark(:geoshape)
    |> Vl.encode_field(:color, "deaths_per_100k", type: :quantitative, title: "Deaths per 100k")
    |> Vl.encode(:tooltip, [
      [field: "city", type: :nominal, title: "City"],
      [field: "deaths_per_100k", type: :quantitative, title: "Rate"]
    ])
    |> Vl.config(view: [stroke: nil])
  end
end

# North
MapHelper.draw_region("N", deaths_per_city)

# Northeast
MapHelper.draw_region("NE", deaths_per_city)

# Southeast
MapHelper.draw_region("SE", deaths_per_city)

# South
MapHelper.draw_region("S", deaths_per_city)

# Central-West
MapHelper.draw_region("CO", deaths_per_city)