EEA & UK deaths: DIC and CVST

gistfile1.txt

test("EEA_UK_deaths") {

    val df = spark.read.option("header", true)
      .csv("/Users/elena/Downloads/Morticd10_part*")

    df.createTempView("data")

    val code2country = spark.read.option("header", true)
      .csv("/Users/elena/Downloads/WHO_country_codes.csv")

    val filteredCounties = spark.read.option("header", true).option("sep", ";")
      .csv("/Users/elena/Downloads/EEA_UK_country_list.csv")

    code2country.createTempView("code")
    filteredCounties.createTempView("filtered")

    val res = spark.sql(
      """select d.year, d.cause, c.name, count(*)
        |from data d
        |join code c on c.country = d.country
        |where c.name in (select f.name from filtered f)
        |  and c.cause in ('D65', 'I676')
        |group by d.year, d.cause, c.name
        |""".stripMargin)

    res.coalesce(1).write.option("header", true).csv("/Users/elena/Downloads/output")

  }

Source

EEA countries: https://ec.europa.eu/eurostat/statistics-explained/index.php/Glossary:European_Economic_Area_(EEA)
ICD-10: https://icd.who.int/browse10/2019/en#
WHO mortality data (country codes): https://www.who.int/data/data-collection-tools/who-mortality-database