Análises: Mato Grosso do Sul

Informações Gerais

> Lembre-se de ativar o runtime para Mix Standalone com o caminho para /data

Objetivo deste book consiste em apresentar gráficos e tabelas sobre os resultados alcançados com as bases do e-SUS VE, SIPNI e SIVEP do estado de Mato Grosso do Sul.

Municípios de Fronteira

1. 5000906 Antônio João
2. 5001243 Aral Moreira
3. 5002100 Bela Vista
4. 5002803 Caracol
5. 5003157 Coronel Sapucaia
6. 5003207 Corumbá
7. 5004809 Japorã
8. 5005202 Ladário
9. 5005681 Mundo Novo
10. 5006358 Paranhos
11. 5006606 Ponta Porã
12. 5006903 Porto Murtinho
13. 5007703 Sete Quedas

Indicador de Efetividade

$$ EV = \frac{INV-IV}{INV}\ \times 100 $$

• $INV$: Incidência entre os não vacinados
• $IV$: Incidência entre os vacinados

Taxa de Incidência

$$ \frac{C}{P} \times 100000 $$

• $C$: Casos sintomáticos ESUS-VE + SIVEP
• $P$: População residente na faixa etária (ou geral)

Gráficos e Tabelas

Epicurva de taxa de incidência 1

Filtrar:

• Entre 15 e 39 anos

Temporalidade:

• Semana epidemiológica

Gerar um por:

• Por localidade
• Somatório de fronteira
• Somatório de não fronteira

Epicurva de taxa de incidência 2

Temporalidade:

• Semana epidemiológica

Gerar um por:

• Por localidade
• Somatório de fronteira
• Somatório de não fronteira

Epicurva em Gráfico de Área Agrupada

Filtrar:

• Do estado

Temporalidade:

• Semana epidemiológica

Dimensões:

• Vacinados
• Não vacinados

Gerar um por:

• Casos somados
• Internações
• Óbitos

Distribuição em Gráfico de Barra

Filtrar:

• Do estado

Dimensões:

• Vacinados
• Não vacinados
• Por faixa etária

Gerar um por:

• Casos somados
• Internações
• Óbitos

Tabela Indicador de efetividade (Município)

Linhas:

• Município

Colunas:

• casos
• internações
• óbitos

Tabela Indicador de efetividade por faixa etária (Estado)

Linhas:

• Faixa etária
• Todas as idades
• 15 a 39 anos

Colunas:

• casos
• internações
• óbitos

NÃO PRECISA Tabela Indicador de efetividade por vacina (Estado)

Linhas:

• Vacina

Colunas:

• casos
• internações
• óbitos

Identificação dos caminhos

results_dir = Path.expand("sandbox/results", __DIR__)

put_path = fn {map, context}, suffix ->
  desired_file =
    if suffix == :na do
      "#{context}.csv"
    else
      "#{context}_#{suffix}.csv"
    end

  results_dir
  |> File.ls!()
  |> Enum.find(&(&1 =~ desired_file))
  |> tap(
    &if(
      is_nil(&1),
      do: raise(~s(Arquivo "*#{desired_file}" não encontrado))
    )
  )
  |> Path.expand(results_dir)
  |> then(&{Map.put(map, suffix, &1), context})
end

paths = %{
  esus_ve: %{
    cases:
      {%{}, "esus_ve_cases"}
      |> put_path.(:na)
      |> put_path.(:no_vaccine)
      |> put_path.(:partial_vaccine)
      |> put_path.(:full_vaccine)
      |> put_path.(:guarded)
      |> then(&elem(&1, 0))
  },
  sipni:
    {%{}, "sipni"}
    |> put_path.(:partial_vaccine)
    |> put_path.(:full_vaccine),
  sivep: %{
    cases:
      {%{}, "sivep_cases"}
      |> put_path.(:na)
      |> put_path.(:no_vaccine)
      |> put_path.(:partial_vaccine)
      |> put_path.(:full_vaccine)
      |> put_path.(:guarded)
      |> then(&elem(&1, 0)),
    hospitalizations:
      {%{}, "sivep_hospitalizations"}
      |> put_path.(:na)
      |> put_path.(:no_vaccine)
      |> put_path.(:partial_vaccine)
      |> put_path.(:full_vaccine)
      |> put_path.(:guarded)
      |> then(&elem(&1, 0)),
    deaths:
      {%{}, "sivep_deaths"}
      |> put_path.(:na)
      |> put_path.(:no_vaccine)
      |> put_path.(:partial_vaccine)
      |> put_path.(:full_vaccine)
      |> put_path.(:guarded)
      |> then(&elem(&1, 0))
  }
}

Definição de funções e variáveis

defmodule MS do
  def create_ets(ets_table) do
    :ets.new(ets_table, [:set, :public, :named_table])
  rescue
    _error -> :ets.delete_all_objects(ets_table)
  end

  def extract_and_join_csvs(csv_path1, csv_path2, filter, parser, merger) do
    data1 = parse_csv(csv_path1, filter, parser)
    data2 = parse_csv(csv_path2, filter, parser)

    {result, data2} =
      Enum.reduce(data1, {[], data2}, fn item1, {result, data2} ->
        {item2, data2} = pop_in_list(data2, MS.Filter.same_location_and_date_function(item1))

        if is_nil(item2) do
          {[item1 | result], data2}
        else
          {[merger.(item1, item2) | result], data2}
        end
      end)

    data2 ++ result
  end

  def parse_csv(csv_path, filter, parser) do
    csv_path
    |> File.read!()
    |> NimbleCSV.RFC4180.parse_string()
    |> parse_lines(filter, parser)
  end

  def parse_lines(data, filter, parser) do
    if is_list(parser) do
      [root_parser | parsers] = parser

      data
      |> Enum.map(fn line ->
        data = root_parser.(line, filter)

        unless is_nil(data) do
          Enum.reduce(parsers, data, & &1.(&2))
        end
      end)
      |> Enum.reject(&is_nil/1)
    else
      data
      |> Enum.map(&parser.(&1, filter))
      |> Enum.reject(&is_nil/1)
    end
  end

  def pop_in_list(list, acc \\ [], fun) do
    if Enum.any?(list) do
      [item | list] = list

      if fun.(item) do
        {item, acc ++ list}
      else
        pop_in_list(list, [item | acc], fun)
      end
    else
      {nil, acc}
    end
  end
end

defmodule MS.Filter do
  def state(map), do: map.location == 50
  def before(map, date), do: Date.compare(map.date, date) == :lt

  def same_location_and_date_function(map) do
    &(&1.location == map.location and Date.compare(&1.date, map.date) == :eq)
  end
end

defmodule MS.Merger do
  def consolidation_merge(data1, data2, key1, key2, default1, default2) do
    {result, data2} =
      Enum.reduce(data1, {[], data2}, fn item1, {result, data2} ->
        {item2, data2} = MS.pop_in_list(data2, MS.Filter.same_location_and_date_function(item1))

        item1 = Map.put(item1, key2, if(is_nil(item2), do: default2, else: item2[key2]))

        {[item1 | result], data2}
      end)

    data2
    |> Enum.map(&Map.put(&1, key1, default1))
    |> Kernel.++(result)
    |> Enum.map(&Map.put(Map.take(&1, [:date, key1, key2]), :date, to_string(&1.date)))
  end

  def sum_function(key), do: fn m1, m2 -> Map.put(m1, key, m1[key] + m2[key]) end
end

defmodule MS.Locations do
  @ets :cities
  @csv "sandbox/input/ms_cities_names.csv"
  @boundary_cities [
    5_000_906,
    5_001_243,
    5_002_100,
    5_002_803,
    5_003_157,
    5_003_207,
    5_004_809,
    5_005_202,
    5_005_681,
    5_006_358,
    5_006_606,
    5_006_903,
    5_007_703
  ]

  def init(path \\ @csv) do
    MS.create_ets(@ets)

    path
    |> Path.expand(__DIR__)
    |> File.read!()
    |> NimbleCSV.RFC4180.parse_string()
    |> Enum.map(fn [k, v] -> {String.to_integer(k), v} end)
    |> then(&[{50, "Mato Grosso do Sul"} | &1])
    |> then(&:ets.insert(@ets, &1))

    :ok
  end

  def name(id), do: :ets.lookup_element(:cities, id, 2)

  def is_boundary_city?(id), do: id in @boundary_cities
end

defmodule MS.Parser do
  @keys ~w(a15_29 a30_39 a40_49 a50_59 a60_69 a70_79 a80_plus)a

  def age_groups(age_groups) do
    age_groups
    |> Enum.map(&String.to_integer/1)
    |> then(&Enum.zip(@keys, &1))
  end

  def consolidation([location, date | age_groups], filter) do
    item = %{
      location: String.to_integer(location),
      date: Date.from_iso8601!(date),
      age_groups: age_groups(age_groups)
    }

    if is_nil(filter) do
      item
    else
      if filter.(item) do
        item
      else
        nil
      end
    end
  end

  def sum_age_groups_function(key) do
    &Map.put(&1, key, Enum.reduce(&1.age_groups, 0, fn {_k, v}, acc -> acc + v end))
  end

  def sum_age_groups_function(key, take_amount) do
    &Map.put(
      &1,
      key,
      Enum.reduce(
        Enum.take(&1.age_groups, take_amount),
        0,
        fn {_k, v}, acc -> acc + v end
      )
    )
  end
end

defmodule MS.Populations do
  @ets :populations
  @csv "sandbox/input/ms_population.csv"

  def init(path \\ @csv) do
    MS.create_ets(@ets)

    path
    |> Path.expand(__DIR__)
    |> File.read!()
    |> NimbleCSV.RFC4180.parse_string()
    |> Enum.map(fn [k, v] -> {String.to_integer(k), String.to_integer(v)} end)
    |> then(&:ets.insert(@ets, &1))

    :ok
  end

  def get(id), do: :ets.lookup_element(@ets, id, 2)
end

defmodule MS.PopulationsPerAgeGroup do
  @ets :populations_per_age
  @csv "sandbox/input/ms_population_per_age.csv"

  def init(path \\ @csv) do
    MS.create_ets(@ets)

    path
    |> Path.expand(__DIR__)
    |> File.read!()
    |> NimbleCSV.RFC4180.parse_string()
    |> Enum.map(fn list -> Enum.map(list, &String.to_integer/1) |> to_record() end)
    |> then(&:ets.insert(@ets, &1))

    :ok
  end

  defp to_record([
         l,
         a4,
         a9,
         a14,
         a19,
         a24,
         a29,
         a34,
         a39,
         a44,
         a49,
         a54,
         a59,
         a64,
         a69,
         a74,
         a79,
         a80m
       ]) do
    {
      l,
      a4 + a9 + a14 + a19 + a24 + a29 + a34 + a39,
      a4 + a9 + a14 + a19 + a24 + a29,
      a34 + a39,
      a44 + a49,
      a54 + a59,
      a64 + a69,
      a74 + a79,
      a80m
    }
  end

  def get(id, index), do: :ets.lookup_element(@ets, id, index + 2)
  def less_than_30(id), do: :ets.lookup_element(@ets, id, 2)
end

:ok

MS.Locations.init()
MS.Populations.init()
MS.PopulationsPerAgeGroup.init()

Epicurva de taxa de incidência 1

defmodule EpicurveIncidenceRate1 do
  @title "Epicurva de taxa de incidência"
  @label "Taxa de incidência"

  def plot(paths) do
    %{na: cases1} = paths.esus_ve.cases
    %{na: cases2} = paths.sivep.cases

    :cases
    |> prepare(cases1, cases2)
    |> Enum.map(&parse/1)
    |> epicurves()
  end

  defp prepare(key, csv_path1, csv_path2) do
    today = Date.utc_today()

    MS.extract_and_join_csvs(
      csv_path1,
      csv_path2,
      &MS.Filter.before(&1, today),
      [&MS.Parser.consolidation/2, MS.Parser.sum_age_groups_function(key, 2)],
      MS.Merger.sum_function(key)
    )
  end

  defp parse(%{cases: cases, location: location} = item) do
    population = MS.PopulationsPerAgeGroup.less_than_30(location)

    %{
      date: to_string(item.date),
      location: location,
      cases: cases,
      population: population,
      value: Float.round(cases / population * 100_000, 1)
    }
  end

  def epicurves(data) do
    [title: @title]
    |> VegaLite.new()
    |> VegaLite.concat(
      data
      |> Enum.group_by(& &1.location)
      |> Enum.sort(&amp;(elem(&amp;1, 0) <= elem(&amp;2, 0)))
      |> Enum.map(&amp;epicurve/1)
      |> append_boundaries(data)
    )
  end

  defp epicurve({location, data}) do
    title = if(is_integer(location), do: MS.Locations.name(location), else: location)

    [title: title, height: 150, width: 150]
    |> VegaLite.new()
    |> VegaLite.data_from_values(data)
    |> VegaLite.mark(:line, tooltip: true, point: true)
    |> VegaLite.encode_field(:x, "date", type: :temporal, time_unit: :yearweek, title: "Data")
    |> VegaLite.encode_field(:y, "value", type: :quantitative, aggregate: :mean, title: @label)
  end

  defp append_boundaries([state | cities], data) do
    {boundary, non_boundary} =
      Enum.reduce(data, {[], []}, fn item, {boundary, non_boundary} ->
        if MS.Locations.is_boundary_city?(item.location) do
          {[item | boundary], non_boundary}
        else
          {boundary, [item | non_boundary]}
        end
      end)

    boundary =
      boundary
      |> Enum.group_by(&amp; &amp;1.date)
      |> Enum.map(&amp;boundary_sum/1)
      |> boundary_epicurve("Fronteira")

    non_boundary =
      non_boundary
      |> Enum.group_by(&amp; &amp;1.date)
      |> Enum.map(&amp;boundary_sum/1)
      |> boundary_epicurve("Não-fronteira")

    [state, boundary, non_boundary | cities]
  end

  defp boundary_sum({date, items}) do
    {cases, population} =
      Enum.reduce(items, {0, 0}, fn item, {c, p} -> {c + item.cases, p + item.population} end)

    %{
      date: date,
      value: Float.round(cases / population * 100_000, 1)
    }
  end

  defp boundary_epicurve(data, title), do: epicurve({title, data})
end

EpicurveIncidenceRate1.plot(paths)

Epicurva de taxa de incidência 2

defmodule EpicurveIncidenceRate2 do
  @title "Epicurva de taxa de incidência"
  @label "Taxa de incidência"

  def plot(paths) do
    %{na: cases1} = paths.esus_ve.cases
    %{na: cases2} = paths.sivep.cases

    :cases
    |> prepare(cases1, cases2)
    |> Enum.map(&parse/1)
    |> epicurves()
  end

  defp prepare(key, csv_path1, csv_path2) do
    today = Date.utc_today()

    MS.extract_and_join_csvs(
      csv_path1,
      csv_path2,
      &MS.Filter.before(&1, today),
      [&MS.Parser.consolidation/2, MS.Parser.sum_age_groups_function(key)],
      MS.Merger.sum_function(key)
    )
  end

  defp parse(%{cases: cases, location: location} = item) do
    population = MS.Populations.get(location)

    %{
      date: to_string(item.date),
      location: location,
      cases: cases,
      population: population,
      value: Float.round(cases / population * 100_000, 1)
    }
  end

  def epicurves(data) do
    [title: @title]
    |> VegaLite.new()
    |> VegaLite.concat(
      data
      |> Enum.group_by(& &1.location)
      |> Enum.sort(&amp;(elem(&amp;1, 0) <= elem(&amp;2, 0)))
      |> Enum.map(&amp;epicurve/1)
      |> append_boundaries(data)
    )
  end

  defp epicurve({location, data}) do
    title = if(is_integer(location), do: MS.Locations.name(location), else: location)

    [title: title, height: 150, width: 150]
    |> VegaLite.new()
    |> VegaLite.data_from_values(data)
    |> VegaLite.mark(:line, tooltip: true, point: true)
    |> VegaLite.encode_field(:x, "date", type: :temporal, time_unit: :yearweek, title: "Data")
    |> VegaLite.encode_field(:y, "value", type: :quantitative, aggregate: :mean, title: @label)
  end

  defp append_boundaries([state | cities], data) do
    {boundary, non_boundary} =
      Enum.reduce(data, {[], []}, fn item, {boundary, non_boundary} ->
        if MS.Locations.is_boundary_city?(item.location) do
          {[item | boundary], non_boundary}
        else
          {boundary, [item | non_boundary]}
        end
      end)

    boundary =
      boundary
      |> Enum.group_by(&amp; &amp;1.date)
      |> Enum.map(&amp;boundary_sum/1)
      |> boundary_epicurve("Fronteira")

    non_boundary =
      non_boundary
      |> Enum.group_by(&amp; &amp;1.date)
      |> Enum.map(&amp;boundary_sum/1)
      |> boundary_epicurve("Não-fronteira")

    [state, boundary, non_boundary | cities]
  end

  defp boundary_sum({date, items}) do
    {cases, population} =
      Enum.reduce(items, {0, 0}, fn item, {c, p} -> {c + item.cases, p + item.population} end)

    %{
      date: date,
      value: Float.round(cases / population * 100_000, 1)
    }
  end

  defp boundary_epicurve(data, title), do: epicurve({title, data})
end

EpicurveIncidenceRate2.plot(paths)

Epicurva em Gráfico de Área Agrupada: Casos

defmodule CasesVaccineNoVaccine do
  @title "Casos entre vacinados e não vacinados"
  @label "Casos"

  def plot(paths) do
    %{guarded: guarded1, no_vaccine: no_vaccine1} = paths.esus_ve.cases
    %{guarded: guarded2, no_vaccine: no_vaccine2} = paths.sivep.cases

    guarded =
      :guarded
      |> prepare(guarded1, guarded2)
      |> Enum.map(&%{date: to_string(&1.date), key: "Vacinado", value: &1.guarded})

    no_vaccine =
      :no_vaccine
      |> prepare(no_vaccine1, no_vaccine2)
      |> Enum.map(&%{date: to_string(&1.date), key: "Não vacinado", value: &1.no_vaccine})

    stacked_area(guarded ++ no_vaccine)
  end

  defp prepare(key, csv_path1, csv_path2) do
    today = Date.utc_today()

    MS.extract_and_join_csvs(
      csv_path1,
      csv_path2,
      fn map -> MS.Filter.state(map) and MS.Filter.before(map, today) end,
      [&MS.Parser.consolidation/2, MS.Parser.sum_age_groups_function(key)],
      MS.Merger.sum_function(key)
    )
  end

  def stacked_area(data) do
    [title: @title, width: 600, height: 400]
    |> VegaLite.new()
    |> VegaLite.data_from_values(data)
    |> VegaLite.mark(:area, tooltip: true)
    |> VegaLite.encode_field(:x, "date", type: :temporal, time_unit: :yearweek, title: "Data")
    |> VegaLite.encode_field(:y, "value", type: :quantitative, aggregate: :mean, title: @label)
    |> VegaLite.encode_field(:color, "key", type: :nominal, title: "Tipo")
  end
end

CasesVaccineNoVaccine.plot(paths)

Epicurva em Gráfico de Área Agrupada: Internações

defmodule HospitalizationsVaccineNoVaccine do
  @title "Internações entre vacinados e não vacinados"
  @label "Internações"

  def plot(paths) do
    %{guarded: guarded, no_vaccine: no_vaccine} = paths.sivep.hospitalizations

    guarded =
      :guarded
      |> prepare(guarded)
      |> Enum.map(&%{date: to_string(&1.date), key: "Vacinado", value: &1.guarded})

    no_vaccine =
      :no_vaccine
      |> prepare(no_vaccine)
      |> Enum.map(&%{date: to_string(&1.date), key: "Não vacinado", value: &1.no_vaccine})

    stacked_area(guarded ++ no_vaccine)
  end

  defp prepare(key, csv_path) do
    today = Date.utc_today()

    MS.parse_csv(
      csv_path,
      fn map -> MS.Filter.state(map) and MS.Filter.before(map, today) end,
      parser(key)
    )
  end

  defp stacked_area(data) do
    [title: @title, width: 600, height: 400]
    |> VegaLite.new()
    |> VegaLite.data_from_values(data)
    |> VegaLite.mark(:area, tooltip: true)
    |> VegaLite.encode_field(:x, "date", type: :temporal, time_unit: :yearweek, title: "Data")
    |> VegaLite.encode_field(:y, "value", type: :quantitative, aggregate: :mean, title: @label)
    |> VegaLite.encode_field(:color, "key", type: :nominal, title: "Tipo")
  end

  defp parser(key), do: [&MS.Parser.consolidation/2, MS.Parser.sum_age_groups_function(key)]
end

HospitalizationsVaccineNoVaccine.plot(paths)

Epicurva em Gráfico de Área Agrupada: Óbitos

defmodule DeathsVaccineNoVaccine do
  @title "Óbitos entre vacinados e não vacinados"
  @label "Óbitos"

  def plot(paths) do
    %{guarded: guarded, no_vaccine: no_vaccine} = paths.sivep.deaths

    guarded =
      :guarded
      |> prepare(guarded)
      |> Enum.map(&%{date: to_string(&1.date), key: "Vacinado", value: &1.guarded})

    no_vaccine =
      :no_vaccine
      |> prepare(no_vaccine)
      |> Enum.map(&%{date: to_string(&1.date), key: "Não vacinado", value: &1.no_vaccine})

    stacked_area(guarded ++ no_vaccine)
  end

  defp prepare(key, csv_path) do
    today = Date.utc_today()

    MS.parse_csv(
      csv_path,
      fn map -> MS.Filter.state(map) and MS.Filter.before(map, today) end,
      parser(key)
    )
  end

  defp stacked_area(data) do
    [title: @title, width: 600, height: 400]
    |> VegaLite.new()
    |> VegaLite.data_from_values(data)
    |> VegaLite.mark(:area, tooltip: true)
    |> VegaLite.encode_field(:x, "date", type: :temporal, time_unit: :yearweek, title: "Data")
    |> VegaLite.encode_field(:y, "value", type: :quantitative, aggregate: :mean, title: @label)
    |> VegaLite.encode_field(:color, "key", type: :nominal, title: "Tipo")
  end

  defp parser(key), do: [&MS.Parser.consolidation/2, MS.Parser.sum_age_groups_function(key)]
end

DeathsVaccineNoVaccine.plot(paths)

Distribuição em Gráfico de Barra: Casos

defmodule CasesVaccineNoVaccineBar do
  @title "Casos entre vacinados e não vacinados"
  @label "Casos"

  def plot(paths) do
    %{guarded: guarded1, no_vaccine: no_vaccine1} = paths.esus_ve.cases
    %{guarded: guarded2, no_vaccine: no_vaccine2} = paths.sivep.cases

    guarded =
      guarded1
      |> prepare(guarded2)
      |> Enum.flat_map(&flat_map/1)
      |> sum_age_groups("Vacinado")

    no_vaccine =
      no_vaccine1
      |> prepare(no_vaccine2)
      |> Enum.flat_map(&flat_map/1)
      |> sum_age_groups("Não-vacinado")

    grouped_bar(guarded ++ no_vaccine)
  end

  defp prepare(csv_path1, csv_path2) do
    today = Date.utc_today()

    MS.extract_and_join_csvs(
      csv_path1,
      csv_path2,
      fn map -> MS.Filter.state(map) and MS.Filter.before(map, today) end,
      &MS.Parser.consolidation/2,
      &merge/2
    )
  end

  defp merge(i1, i2) do
    i1.age_groups
    |> Enum.zip(i2.age_groups)
    |> Enum.map(fn {{age_group, v1}, {_, v2}} -> {age_group, v1 + v2} end)
    |> then(&Map.put(i1, :age_groups, &1))
  end

  defp flat_map(item) do
    Enum.map(item.age_groups, fn {age_group, value} ->
      %{age_group: age_group, value: value}
    end)
  end

  defp sum_age_groups(data, key) do
    data
    |> Enum.group_by(& &1.age_group)
    |> Enum.map(fn {age_group, items} ->
      %{key: key, age_group: age_group, value: Enum.reduce(items, 0, &(&1.value + &2))}
    end)
    |> Enum.sort(&amp;(&amp;1.age_group <= &amp;2.age_group))
  end

  def grouped_bar(data) do
    [title: @title, width: 100, height: 300]
    |> VegaLite.new()
    |> VegaLite.data_from_values(data)
    |> VegaLite.mark(:bar, tooltip: true)
    |> VegaLite.encode_field(:column, "age_group", title: "Faixa etária")
    |> VegaLite.encode_field(:x, "key", title: "Tipo")
    |> VegaLite.encode_field(:color, "key")
    |> VegaLite.encode_field(:y, "value", type: :quantitative, title: @label)
  end
end

CasesVaccineNoVaccineBar.plot(paths)

Distribuição em Gráfico de Barra: Internações

defmodule HospitalizationsVaccineNoVaccineBar do
  @title "Internações entre vacinados e não vacinados"
  @label "Internações"

  def plot(paths) do
    %{guarded: guarded, no_vaccine: no_vaccine} = paths.sivep.hospitalizations

    guarded =
      guarded
      |> prepare()
      |> Enum.flat_map(&flat_map/1)
      |> sum_age_groups("Vacinado")

    no_vaccine =
      no_vaccine
      |> prepare()
      |> Enum.flat_map(&flat_map/1)
      |> sum_age_groups("Não-vacinado")

    grouped_bar(guarded ++ no_vaccine)
  end

  defp prepare(csv_path) do
    today = Date.utc_today()

    MS.parse_csv(
      csv_path,
      fn map -> MS.Filter.state(map) and MS.Filter.before(map, today) end,
      &MS.Parser.consolidation/2
    )
  end

  defp flat_map(item) do
    Enum.map(item.age_groups, fn {age_group, value} ->
      %{age_group: age_group, value: value}
    end)
  end

  defp sum_age_groups(data, key) do
    data
    |> Enum.group_by(& &1.age_group)
    |> Enum.map(fn {age_group, items} ->
      %{key: key, age_group: age_group, value: Enum.reduce(items, 0, &(&1.value + &2))}
    end)
    |> Enum.sort(&amp;(&amp;1.age_group <= &amp;2.age_group))
  end

  def grouped_bar(data) do
    [title: @title, width: 100, height: 300]
    |> VegaLite.new()
    |> VegaLite.data_from_values(data)
    |> VegaLite.mark(:bar, tooltip: true)
    |> VegaLite.encode_field(:column, "age_group", title: "Faixa etária")
    |> VegaLite.encode_field(:x, "key", title: "Tipo")
    |> VegaLite.encode_field(:color, "key")
    |> VegaLite.encode_field(:y, "value", type: :quantitative, title: @label)
  end
end

HospitalizationsVaccineNoVaccineBar.plot(paths)

Distribuição em Gráfico de Barra: Óbitos

defmodule DeathsVaccineNoVaccineBar do
  @title "Óbitos entre vacinados e não vacinados"
  @label "Óbitos"

  def plot(paths) do
    %{guarded: guarded, no_vaccine: no_vaccine} = paths.sivep.deaths

    guarded =
      guarded
      |> prepare()
      |> Enum.flat_map(&flat_map/1)
      |> sum_age_groups("Vacinado")

    no_vaccine =
      no_vaccine
      |> prepare()
      |> Enum.flat_map(&flat_map/1)
      |> sum_age_groups("Não-vacinado")

    grouped_bar(guarded ++ no_vaccine)
  end

  defp prepare(csv_path) do
    today = Date.utc_today()

    MS.parse_csv(
      csv_path,
      fn map -> MS.Filter.state(map) and MS.Filter.before(map, today) end,
      &MS.Parser.consolidation/2
    )
  end

  defp flat_map(item) do
    Enum.map(item.age_groups, fn {age_group, value} ->
      %{age_group: age_group, value: value}
    end)
  end

  defp sum_age_groups(data, key) do
    data
    |> Enum.group_by(& &1.age_group)
    |> Enum.map(fn {age_group, items} ->
      %{key: key, age_group: age_group, value: Enum.reduce(items, 0, &(&1.value + &2))}
    end)
    |> Enum.sort(&amp;(&amp;1.age_group <= &amp;2.age_group))
  end

  def grouped_bar(data) do
    [title: @title, width: 100, height: 300]
    |> VegaLite.new()
    |> VegaLite.data_from_values(data)
    |> VegaLite.mark(:bar, tooltip: true)
    |> VegaLite.encode_field(:column, "age_group", title: "Faixa etária")
    |> VegaLite.encode_field(:x, "key", title: "Tipo")
    |> VegaLite.encode_field(:color, "key")
    |> VegaLite.encode_field(:y, "value", type: :quantitative, title: @label)
  end
end

DeathsVaccineNoVaccineBar.plot(paths)

Tabela indicador de efetividade: Municípios

defmodule EffectivenessIndicatorTableCities do
  def show(paths) do
    %{guarded: guarded_c1, no_vaccine: no_vaccine_c1} = paths.esus_ve.cases
    %{guarded: guarded_c2, no_vaccine: no_vaccine_c2} = paths.sivep.cases
    %{guarded: guarded_d, no_vaccine: no_vaccine_d} = paths.sivep.deaths
    %{guarded: guarded_h, no_vaccine: no_vaccine_h} = paths.sivep.hospitalizations

    guarded_c1
    |> prepare_cases(guarded_c2, no_vaccine_c1, no_vaccine_c2)
    |> prepare_deaths(guarded_d, no_vaccine_d)
    |> prepare_hospitalizations(guarded_h, no_vaccine_h)
    |> Enum.map(&Map.put(&1, :location, MS.Locations.name(&1.location)))
    |> Enum.sort(&amp;(&amp;1.location <= &amp;2.location))
    |> Kino.DataTable.new(keys: [:location, :ev_cases, :ev_hospitalizations, :ev_deaths])
  end

  defp prepare_cases(guarded1, guarded2, no_vaccine1, no_vaccine2) do
    guarded = merge(:guarded, guarded1, guarded2)
    no_vaccine = merge(:no_vaccine, no_vaccine1, no_vaccine2)

    Enum.group_by(guarded ++ no_vaccine, &amp; &amp;1.location)
    |> Enum.map(fn
      {location, [i1, i2]} -> %{location: location, ev_cases: ev(Map.merge(i1, i2))}
      {location, _items} -> %{location: location, ev_cases: nil}
    end)
  end

  defp merge(key, csv_path1, csv_path2) do
    MS.extract_and_join_csvs(
      csv_path1,
      csv_path2,
      fn map -> not MS.Filter.state(map) end,
      [&amp;MS.Parser.consolidation/2, MS.Parser.sum_age_groups_function(key)],
      MS.Merger.sum_function(key)
    )
    |> Enum.group_by(&amp; &amp;1.location)
    |> Enum.map(fn {location, items} ->
      %{:location => location, key => Enum.reduce(items, 0, &amp;(&amp;1[key] + &amp;2))}
    end)
  end

  defp prepare_deaths(data, guarded, no_vaccine) do
    Enum.group_by(csv(:guarded, guarded) ++ csv(:no_vaccine, no_vaccine), &amp; &amp;1.location)
    |> Enum.map(fn
      {location, [i1, i2]} -> %{location: location, ev_deaths: ev(Map.merge(i1, i2))}
      {location, _items} -> %{location: location, ev_deaths: nil}
    end)
    |> Kernel.++(data)
    |> Enum.group_by(&amp; &amp;1.location)
    |> Enum.map(fn {_location, items} -> Enum.reduce(items, %{}, &amp;Map.merge(&amp;2, &amp;1)) end)
  end

  defp prepare_hospitalizations(data, guarded, no_vaccine) do
    Enum.group_by(csv(:guarded, guarded) ++ csv(:no_vaccine, no_vaccine), &amp; &amp;1.location)
    |> Enum.map(fn
      {location, [i1, i2]} -> %{location: location, ev_hospitalizations: ev(Map.merge(i1, i2))}
      {location, _items} -> %{location: location, ev_hospitalizations: nil}
    end)
    |> Kernel.++(data)
    |> Enum.group_by(&amp; &amp;1.location)
    |> Enum.map(fn {_location, items} -> Enum.reduce(items, %{}, &amp;Map.merge(&amp;2, &amp;1)) end)
  end

  defp csv(key, csv_path) do
    MS.parse_csv(
      csv_path,
      fn map -> not MS.Filter.state(map) end,
      [&amp;MS.Parser.consolidation/2, MS.Parser.sum_age_groups_function(key)]
    )
    |> Enum.group_by(&amp; &amp;1.location)
    |> Enum.map(fn {location, items} ->
      %{:location => location, key => Enum.reduce(items, 0, &amp;(&amp;1[key] + &amp;2))}
    end)
  end

  defp ev(%{no_vaccine: no_vaccine, guarded: guarded}) do
    Float.round((no_vaccine - guarded) / no_vaccine * 100, 1)
  end
end

EffectivenessIndicatorTableCities.show(paths)

Tabela indicador de efetividade: Estado

defmodule EffectivenessIndicatorTableState do
  def show(paths) do
    %{guarded: guarded_c1, no_vaccine: no_vaccine_c1} = paths.esus_ve.cases
    %{guarded: guarded_c2, no_vaccine: no_vaccine_c2} = paths.sivep.cases
    %{guarded: guarded_d, no_vaccine: no_vaccine_d} = paths.sivep.deaths
    %{guarded: guarded_h, no_vaccine: no_vaccine_h} = paths.sivep.hospitalizations

    guarded_c1
    |> prepare_cases(guarded_c2, no_vaccine_c1, no_vaccine_c2)
    |> prepare_deaths(guarded_d, no_vaccine_d)
    |> prepare_hospitalizations(guarded_h, no_vaccine_h)
    |> Enum.sort(&amp;(&amp;1.age_group <= &amp;2.age_group))
    |> Kino.DataTable.new(keys: [:age_group, :ev_cases, :ev_hospitalizations, :ev_deaths])
  end

  defp prepare_cases(guarded1, guarded2, no_vaccine1, no_vaccine2) do
    guarded = merge(:guarded, guarded1, guarded2)
    no_vaccine = merge(:no_vaccine, no_vaccine1, no_vaccine2)

    Enum.group_by(guarded ++ no_vaccine, &amp; &amp;1.age_group)
    |> Enum.map(fn
      {age_group, [i1, i2]} -> %{age_group: age_group, ev_cases: ev(Map.merge(i1, i2))}
      {age_group, _items} -> %{age_group: age_group, ev_cases: nil}
    end)
  end

  defp merge(key, csv_path1, csv_path2) do
    d1 = csv(key, csv_path1)
    d2 = csv(key, csv_path2)

    d1
    |> Kernel.++(d2)
    |> Enum.group_by(&amp; &amp;1.age_group)
    |> Enum.map(fn {age_group, items} ->
      %{
        :age_group => age_group,
        key => Enum.reduce(items, 0, &amp;(&amp;1[key] + &amp;2))
      }
    end)
  end

  defp prepare_deaths(data, guarded, no_vaccine) do
    Enum.group_by(csv(:guarded, guarded) ++ csv(:no_vaccine, no_vaccine), &amp; &amp;1.age_group)
    |> Enum.map(fn
      {age_group, [i1, i2]} -> %{age_group: age_group, ev_deaths: ev(Map.merge(i1, i2))}
      {age_group, _items} -> %{age_group: age_group, ev_deaths: nil}
    end)
    |> Kernel.++(data)
    |> Enum.group_by(&amp; &amp;1.age_group)
    |> Enum.map(fn {_age_group, items} -> Enum.reduce(items, %{}, &amp;Map.merge(&amp;2, &amp;1)) end)
  end

  defp prepare_hospitalizations(data, guarded, no_vaccine) do
    Enum.group_by(csv(:guarded, guarded) ++ csv(:no_vaccine, no_vaccine), &amp; &amp;1.age_group)
    |> Enum.map(fn
      {age_group, [i1, i2]} -> %{age_group: age_group, ev_hospitalizations: ev(Map.merge(i1, i2))}
      {age_group, _items} -> %{age_group: age_group, ev_hospitalizations: nil}
    end)
    |> Kernel.++(data)
    |> Enum.group_by(&amp; &amp;1.age_group)
    |> Enum.map(fn {_age_group, items} -> Enum.reduce(items, %{}, &amp;Map.merge(&amp;2, &amp;1)) end)
  end

  defp csv(key, csv_path) do
    MS.parse_csv(
      csv_path,
      &amp;MS.Filter.state/1,
      &amp;MS.Parser.consolidation/2
    )
    |> Enum.flat_map(&amp;flat_map(&amp;1, key))
    |> Enum.group_by(&amp; &amp;1.age_group)
    |> Enum.map(fn {age_group, items} ->
      %{:age_group => age_group, key => Enum.reduce(items, 0, &amp;(&amp;1[key] + &amp;2))}
    end)
  end

  defp flat_map(%{age_groups: age_groups}, key) do
    data = Enum.map(age_groups, fn {k, v} -> %{:age_group => k, key => v} end)
    all = %{:age_group => :all, key => Enum.reduce(data, 0, &amp;(&amp;1[key] + &amp;2))}

    a15_39 = %{
      :age_group => :a15_39,
      key =>
        Enum.filter(data, &amp;(&amp;1.age_group in ~w[a15_29 a30_39]a))
        |> Enum.reduce(0, &amp;(&amp;1[key] + &amp;2))
    }

    [all, a15_39 | data]
  end

  defp ev(%{no_vaccine: no_vaccine, guarded: guarded}) do
    Float.round((no_vaccine - guarded) / no_vaccine * 100, 1)
  end
end

EffectivenessIndicatorTableState.show(paths)