{ "cells": [ { "cell_type": "markdown", "id": "a69e27c1", "metadata": {}, "source": [ "This notebook contains all the code used to create, analyze, and visualize the data for [the article on mentions for causes of death in the media](https://ourworldindata.org/does-the-news-reflect-what-we-die-from).\n", "\n", "You can run this notebook on [Google Colab](https://colab.research.google.com/github/owid/etl/blob/master/docs/analyses/media_deaths/media_deaths_analysis.ipynb), or download the files: [this notebook](analyses/media_deaths/media_deaths_analysis.ipynb) | [replication folder](https://catalog.owid.io/analyses/media-deaths-analysis-data.zip) (including notebook, input data and results).\n", "\n", "More details can be found in [the methodology document](https://docs.owid.io/projects/etl/analyses/media_deaths/methodology/)." ] }, { "cell_type": "markdown", "id": "4530449d", "metadata": {}, "source": [ "## Setup\n", "### Requirements and library import\n", "\n", "There are some requirements you need to install to run this notebook. They are general data science libraries and the mediacloud library." ] }, { "cell_type": "code", "execution_count": null, "id": "01d97fa0", "metadata": { "tags": [ "collapsed" ] }, "outputs": [], "source": [ "# Install dependencies\n", "%pip install -q pandas==2.2.2 matplotlib>=3.9.1.post1 mediacloud>=4.4.0 plotly==6.3.0 kaleido==1.1.0\n", "# Fetch adjacent modules and files if running in Colab\n", "import urllib.request, os, sys\n", "if \"google.colab\" in sys.modules:\n", " for file in [\"query_generation.py\", \"data/media_deaths_results.csv\"]:\n", " os.makedirs(os.path.dirname(file) or \".\", exist_ok=True)\n", " urllib.request.urlretrieve(f\"https://raw.githubusercontent.com/owid/etl/master/docs/analyses/media_deaths/{file}\", file)" ] }, { "cell_type": "markdown", "id": "6566cb9d", "metadata": {}, "source": [ "Import necessary libraries." ] }, { "cell_type": "code", "execution_count": 1, "id": "7a6beeed", "metadata": {}, "outputs": [], "source": [ "import datetime as dt\n", "import pandas as pd\n", "import plotly.graph_objects as go\n", "import time\n", "import matplotlib.pyplot as plt\n", "import mediacloud.api" ] }, { "cell_type": "markdown", "id": "1cec4fcb", "metadata": {}, "source": [ "We also use the queries generated in a separate file. \n", "\n", "We don't include the full query generation in this notebook for easier browsing, but you can see the query generation file [here](https://github.com/owid/etl/blob/master/docs/analyses/media_deaths/query_generation.py) and the final queries in the methodology document [here](methodology/#queries-for-each-cause-of-death). " ] }, { "cell_type": "code", "execution_count": null, "id": "21b69f8a", "metadata": {}, "outputs": [], "source": [ "from query_generation import create_full_queries, create_queries, create_single_keyword_queries # ty: ignore" ] }, { "cell_type": "markdown", "id": "8f959d1e", "metadata": {}, "source": [ "### Set overall variables for analysis\n", "\n", "There are some parameters you need to set before the analysis can be run:\n", "- VERBOSE: If True, the script will output some information on its progress and intermediary results\n", "- RERUN_QUERIES: If True, the Media Cloud API will be queried to get the article counts (mentions) for each cause of death. You will need to set a Media Cloud API key below. If False, this script will use the saved results in ./data/media_death_mentions.csv . \n", "- OVERWRITE: If True, this overwrites the saved results with your own results. Only choose this is you know what you are doing!\n", "- MC_API_TOKEN: This needs to be set so you can access the Media Cloud API. To get an API token you have to create a free account on Media Cloud [here](https://search.mediacloud.org/sign-up) and create an [API token](https://github.com/mediacloud/api-tutorial-notebooks/blob/main/MC01%20-%20setup.ipynb). This API token should be pasted below, so you can access the database." ] }, { "cell_type": "code", "execution_count": 2, "id": "e218a319", "metadata": {}, "outputs": [], "source": [ "YEAR = 2023\n", "VERBOSE = True\n", "\n", "RERUN_QUERIES = False # whether you want to rerun the queries or not - rerunning the queries can take ~30 minutes.\n", "#If false, the notebook uses the results found in ./data/media_deaths_mentions\n", "\n", "USE_SAVED_RESULTS = False # Whether to use final results for all plots. If TRUE this overrides all analysis and uses the saved results in ./data/media_deaths_results.csv\n", "\n", "MC_API_TOKEN = \"\" #paste your API TOKEN here e.g. \"46971fa7d873b615238234a777c2d867fbbb444b\" - this is NOT a valid token. If RERUN_QUERIES is false you don't need this.\n", "\n", "OVERWRITE = True # whether you want to overwrite existing files or not\n", "\n", "# These are the causes of death we are using for the 2023 version.\n", "# They are based on the 12 leading causes of death in the US for 2023, plus drug overdoses, homicides, and terrorism\n", "CAUSES_OF_DEATH = [\n", " \"heart disease\",\n", " \"cancer\",\n", " \"accidents\",\n", " \"stroke\",\n", " \"respiratory\",\n", " \"alzheimers\",\n", " \"diabetes\",\n", " \"kidney\",\n", " \"liver\",\n", " \"covid\",\n", " \"suicide\",\n", " \"influenza\",\n", " \"drug overdose\",\n", " \"homicide\",\n", " \"terrorism\",\n", "]\n", "\n", "TERRORISM_DEATHS_2023 = 16 # We get the terrorism deaths for the USA from the Global Terrorism Index\n", "\n", "# colours used for charts\n", "FIXED_COLOURS = {\n", " \"heart disease\": \"#1f77b4\", # Blue\n", " \"cancer\": \"#ff7f0e\", # Orange\n", " \"accidents\": \"#2ca02c\", # Green\n", " \"stroke\": \"#d62728\", # Red\n", " \"respiratory\": \"#9467bd\", # Purple\n", " \"alzheimers\": \"#8c564b\", # Brown\n", " \"diabetes\": \"#e377c2\", # Pink\n", " \"kidney\": \"#7f7f7f\", # Gray\n", " \"liver\": \"#bcbd22\", # Olive\n", " \"covid\": \"#17becf\", # Teal\n", " \"suicide\": \"#aec7e8\", # Light blue\n", " \"influenza\": \"#ffbb78\", # Light orange\n", " \"drug overdose\": \"#98df8a\", # Light green\n", " \"homicide\": \"#ff9896\", # Light red\n", " \"terrorism\": \"#c5b0d5\", # Light purple\n", " \"war\": \"#c49c94\", # Light brown\n", " \"hiv\": \"#f7b6d2\", # Light pink\n", " \"malaria\": \"#c7c7c7\", # Light gray\n", " \"tb\": \"#dbdb8d\", # Light olive\n", " \"diarrhea\": \"#9edae5\", # Light teal\n", " }" ] }, { "cell_type": "markdown", "id": "3c3bbd48", "metadata": {}, "source": [ "## Data processing\n", "### Get deaths for each cause of death\n", "\n", "This data comes from two files, both downloaded from the CDC Wonder database \"[Underlying Cause of Death Data](https://wonder.cdc.gov/deaths-by-underlying-cause.html)\" and hosted on the Our World in Data R2 storage.\n", "\n", "- The leading causes file is the result when selecting \"Group results by\" - \"15 leading causes of death\" and selecting the year 2023\n", "- The external factors file is the result when selecting \"Group result by\" - \"Cause of death\", selecting the year 2023 and only selecting cause of death \"V01-Y89 (External causes of mobidity and mortality)\"" ] }, { "cell_type": "code", "execution_count": 96, "id": "8a2dceb2", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Notes15 Leading Causes of Death15 Leading Causes of Death CodeDeathsPopulationCrude Rate
0NaN#Diseases of heart (I00-I09,I11,I13,I20-I51)GR113-054680981.0334914895.0203.3
1NaN#Malignant neoplasms (C00-C97)GR113-019613352.0334914895.0183.1
2NaN#Accidents (unintentional injuries) (V01-X59,Y...GR113-112222698.0334914895.066.5
3NaN#Cerebrovascular diseases (I60-I69)GR113-070162639.0334914895.048.6
4NaN#Chronic lower respiratory diseases (J40-J47)GR113-082145357.0334914895.043.4
\n", "
" ], "text/plain": [ " Notes 15 Leading Causes of Death \\\n", "0 NaN #Diseases of heart (I00-I09,I11,I13,I20-I51) \n", "1 NaN #Malignant neoplasms (C00-C97) \n", "2 NaN #Accidents (unintentional injuries) (V01-X59,Y... \n", "3 NaN #Cerebrovascular diseases (I60-I69) \n", "4 NaN #Chronic lower respiratory diseases (J40-J47) \n", "\n", " 15 Leading Causes of Death Code Deaths Population Crude Rate \n", "0 GR113-054 680981.0 334914895.0 203.3 \n", "1 GR113-019 613352.0 334914895.0 183.1 \n", "2 GR113-112 222698.0 334914895.0 66.5 \n", "3 GR113-070 162639.0 334914895.0 48.6 \n", "4 GR113-082 145357.0 334914895.0 43.4 " ] }, "execution_count": 96, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 15 leading causes of death\n", "# this is fetching the file from OWIDs data storage - it is the same file you can download from the CDC\n", "leading_causes_df = pd.read_csv(\"https://snapshots.owid.io/6f/b0139e189d66756d94f84fafab7c3c\", sep='\\t')\n", "leading_causes_df.head(5)" ] }, { "cell_type": "code", "execution_count": 97, "id": "b12f34cf", "metadata": {}, "outputs": [], "source": [ "# We map the names the CDC uses to our key words used in this analysis\n", "CAUSES_MAP = {\n", " \"#Diseases of heart (I00-I09,I11,I13,I20-I51)\": \"heart disease\",\n", " \"#Malignant neoplasms (C00-C97)\": \"cancer\",\n", " \"#Accidents (unintentional injuries) (V01-X59,Y85-Y86)\": \"accidents\",\n", " \"#Cerebrovascular diseases (I60-I69)\": \"stroke\",\n", " \"#Chronic lower respiratory diseases (J40-J47)\": \"respiratory\",\n", " \"#Alzheimer disease (G30)\": \"alzheimers\",\n", " \"#Diabetes mellitus (E10-E14)\": \"diabetes\",\n", " \"#Nephritis, nephrotic syndrome and nephrosis (N00-N07,N17-N19,N25-N27)\": \"kidney\",\n", " \"#Chronic liver disease and cirrhosis (K70,K73-K74)\": \"liver\",\n", " \"#COVID-19 (U07.1)\": \"covid\",\n", " \"#Intentional self-harm (suicide) (*U03,X60-X84,Y87.0)\": \"suicide\",\n", " \"#Influenza and pneumonia (J09-J18)\": \"influenza\",\n", " \"#Essential hypertension and hypertensive renal disease (I10,I12,I15)\": \"hypertension\",\n", " \"#Septicemia (A40-A41)\": \"septicemia\",\n", " \"#Parkinson disease (G20-G21)\": \"parkinson\",\n", "}\n", "\n", "leading_causes_df[\"cause\"] = leading_causes_df[\"15 Leading Causes of Death\"].map(CAUSES_MAP)\n", "leading_causes_df = leading_causes_df.drop(columns=[\"Notes\", \"Population\", \"15 Leading Causes of Death\", \"15 Leading Causes of Death Code\", \"Crude Rate\"], errors=\"raise\")\n", "leading_causes_df = leading_causes_df.dropna(subset=[\"cause\", \"Deaths\"], how=\"all\")\n", "leading_causes_df[\"year\"] = 2023" ] }, { "cell_type": "code", "execution_count": 98, "id": "21e4d251", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
NotesICD Sub-ChapterICD Sub-Chapter CodeCause of deathCause of death CodeDeathsPopulationCrude Rate
0NaNTransport accidentsV01-V99Pedestrian injured in collision with pedal cyc...V01.00334914895Unreliable
1NaNTransport accidentsV01-V99Pedestrian injured in collision with pedal cyc...V01.1Suppressed334914895Suppressed
2NaNTransport accidentsV01-V99Unspecified whether traffic or nontraffic acci...V01.9Suppressed334914895Suppressed
3NaNTransport accidentsV01-V99Pedestrian injured in collision with two- or t...V02.0Suppressed334914895Suppressed
4NaNTransport accidentsV01-V99Pedestrian injured in collision with two- or t...V02.1513349148950
\n", "
" ], "text/plain": [ " Notes ICD Sub-Chapter ICD Sub-Chapter Code \\\n", "0 NaN Transport accidents V01-V99 \n", "1 NaN Transport accidents V01-V99 \n", "2 NaN Transport accidents V01-V99 \n", "3 NaN Transport accidents V01-V99 \n", "4 NaN Transport accidents V01-V99 \n", "\n", " Cause of death Cause of death Code \\\n", "0 Pedestrian injured in collision with pedal cyc... V01.0 \n", "1 Pedestrian injured in collision with pedal cyc... V01.1 \n", "2 Unspecified whether traffic or nontraffic acci... V01.9 \n", "3 Pedestrian injured in collision with two- or t... V02.0 \n", "4 Pedestrian injured in collision with two- or t... V02.1 \n", "\n", " Deaths Population Crude Rate \n", "0 0 334914895 Unreliable \n", "1 Suppressed 334914895 Suppressed \n", "2 Suppressed 334914895 Suppressed \n", "3 Suppressed 334914895 Suppressed \n", "4 51 334914895 0 " ] }, "execution_count": 98, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# external causes (for homicide and drug overdoses)\n", "# this is fetching the file from OWIDs data storage - it is the same file you can download from the CDC\n", "external_causes_df = pd.read_csv(\"https://snapshots.owid.io/27/cb223d374b691fbd451c1985d0cf31\")\n", "external_causes_df.head(5)" ] }, { "cell_type": "code", "execution_count": 99, "id": "ca7b53bd", "metadata": {}, "outputs": [], "source": [ "# replace Suppressed (causes with less than 10 deaths this year)/ Unreliable with pd.NA\n", "external_causes_df = external_causes_df.replace(\"Suppressed\", pd.NA)\n", "external_causes_df = external_causes_df.replace(\"Unreliable\", pd.NA)\n", "\n", "external_causes_df[\"Deaths\"] = external_causes_df[\"Deaths\"].astype(\"Int64\")\n", "external_causes_df = external_causes_df.drop(columns=[\"Notes\", \"Population\", \"ICD Sub-Chapter Code\"], errors=\"raise\")\n", "\n", "external_causes_df[\"year\"] = 2023" ] }, { "cell_type": "code", "execution_count": 100, "id": "ce4c110f", "metadata": {}, "outputs": [], "source": [ "# Combine both dataframes and adding terrorism deaths\n", "def create_tb_death(tb_leading_causes, tb_ext_causes):\n", " drug_od_deaths = tb_ext_causes[tb_ext_causes[\"Cause of death Code\"] == \"X42\"][\"Deaths\"].iloc[0]\n", " ext_causes_gb = tb_ext_causes[[\"Deaths\", \"ICD Sub-Chapter\"]].groupby(\"ICD Sub-Chapter\").sum().reset_index()\n", " homicide_deaths = ext_causes_gb[ext_causes_gb[\"ICD Sub-Chapter\"] == \"Assault\"][\"Deaths\"].iloc[0]\n", "\n", " terrorism_deaths = TERRORISM_DEATHS_2023\n", "\n", " deaths = [\n", " {\"cause\": \"drug overdose\", \"year\": 2023, \"deaths\": drug_od_deaths},\n", " {\"cause\": \"homicide\", \"year\": 2023, \"deaths\": homicide_deaths},\n", " {\"cause\": \"terrorism\", \"year\": 2023, \"deaths\": terrorism_deaths},\n", " ]\n", "\n", " tb_leading_causes.columns = [col.lower() for col in tb_leading_causes.columns]\n", "\n", " tb_deaths = pd.concat([tb_leading_causes, pd.DataFrame(deaths)])\n", "\n", " # subtract drug overdose deaths from accidents\n", " acc_deaths = tb_deaths[tb_deaths[\"cause\"] == \"accidents\"][\"deaths\"].iloc[0]\n", " drug_od_deaths = tb_deaths[tb_deaths[\"cause\"] == \"drug overdose\"][\"deaths\"].iloc[0]\n", " tb_deaths.loc[tb_deaths[\"cause\"] == \"accidents\", \"deaths\"] = acc_deaths - drug_od_deaths\n", "\n", " return tb_deaths\n", "\n", "death_df = create_tb_death(leading_causes_df, external_causes_df)" ] }, { "cell_type": "markdown", "id": "27530e15", "metadata": {}, "source": [ "### Get media mentions from Media Cloud\n", "To get the number of articles that mention each cause of death for three major newspapers (the New York Times, the Washington Post and Fox News) we use the open-source database [Media Cloud](https://www.mediacloud.org/). \n", "\n", "Before you can run this code you need to create a free account on Media Cloud [here](https://search.mediacloud.org/sign-up) and create an [API token](https://github.com/mediacloud/api-tutorial-notebooks/blob/main/MC01%20-%20setup.ipynb). This API token should be pasted in the script variables above, so this code can run." ] }, { "cell_type": "code", "execution_count": 101, "id": "11a19499", "metadata": {}, "outputs": [], "source": [ "from dotenv import load_dotenv\n", "import os\n", "\n", "load_dotenv()\n", "\n", "MC_API_TOKEN = os.getenv(\"MC_API_TOKEN\") #paste your API TOKEN here e.g. \"46971fa7d873b615238234a777c2d867fbbb444b\" - this is NOT a valid token\n", "# Initialize search API\n", "search_api = mediacloud.api.SearchApi(MC_API_TOKEN)\n", "\n", "# source IDs for each newspaper\n", "NYT_ID = 1\n", "WAPO_ID = 2\n", "FOX_ID = 1092\n", "US_COLLECTION_ID = 34412234\n", "\n", "# create queries - see query_generation.py\n", "QUERIES = create_queries()\n", "STR_QUERIES = create_full_queries()\n", "SINGLE_QUERIES = create_single_keyword_queries()" ] }, { "cell_type": "code", "execution_count": 102, "id": "56edf212", "metadata": {}, "outputs": [], "source": [ "def get_start_end(year):\n", " return (dt.date(year, 1, 1), dt.date(year, 12, 31))\n", "\n", "# helper function to use Media Cloud API\n", "def query_results(query, source_ids, year, collection_ids=None):\n", " start_date, end_date = get_start_end(year)\n", " if collection_ids:\n", " results = search_api.story_count(\n", " query=query, start_date=start_date, end_date=end_date, collection_ids=collection_ids\n", " )\n", " else:\n", " results = search_api.story_count(query=query, start_date=start_date, end_date=end_date, source_ids=source_ids)\n", " return results[\"relevant\"]\n", "\n", "# function to get mentions from a specific source\n", "def get_mentions_from_source(\n", " source_ids: list,\n", " source_name: str,\n", " queries: dict,\n", " year=YEAR,\n", " collection_ids=None,\n", "):\n", " \"\"\"\n", " Get mentions of causes of death from a specific source.\n", " Args:\n", " source_ids (list): List of source IDs to query.\n", " source_name (str): Name of the source.\n", " queries (dict): Dictionary of queries to run.\n", " year (int): Year to query for.\n", " collection_ids (list): List of collection IDs to query.\n", " Returns:\n", " pd.DataFrame: DataFrame containing the results of the queries.\"\"\"\n", " query_count = []\n", " for name, query in queries.items():\n", " time.sleep(30) # wait for 30 seconds to avoid hitting API rate limits\n", " start_time = time.time()\n", " cnt = query_results(query, source_ids, collection_ids=collection_ids, year=year)\n", " if VERBOSE:\n", " print(f\"Querying: {source_name} for CoD {name}\")\n", " print(f\"Query: {query}\")\n", " print(f\"Count: {cnt} mentions for {name} in the {source_name} in {year} - retrieved in {time.time() - start_time:.2f} seconds\")\n", " print(\"-\" * 40)\n", " query_count.append(\n", " {\n", " \"cause\": name,\n", " \"mentions\": cnt,\n", " \"source\": source_name,\n", " \"year\": year,\n", " }\n", " )\n", " return pd.DataFrame(query_count)" ] }, { "cell_type": "code", "execution_count": 103, "id": "80c29c5d", "metadata": {}, "outputs": [], "source": [ "if RERUN_QUERIES:\n", " assert MC_API_TOKEN is not None, \"Get API key from https://www.mediacloud.org/ in order to access this data\"\n", " source_ids = [NYT_ID, WAPO_ID, FOX_ID]\n", " sources = [\"The New York Times\", \"The Washington Post\", \"Fox News\"]\n", "\n", " mentions_ls = []\n", " single_mentions_ls = []\n", "\n", " queries_in_use = {q: q_str for q, q_str in STR_QUERIES.items() if q in CAUSES_OF_DEATH}\n", " single_queries_in_use = {q: q_str for q, q_str in SINGLE_QUERIES.items() if q in CAUSES_OF_DEATH}\n", "\n", " for s_id, s_name in zip(source_ids, sources):\n", " mentions = get_mentions_from_source([s_id], s_name, queries_in_use, year=YEAR)\n", " mentions_ls.append(mentions.copy(deep=True))\n", " # get single mention counts as well\n", " single_mentions = get_mentions_from_source([s_id], s_name, single_queries_in_use, year=YEAR)\n", " single_mentions_ls.append(single_mentions.copy(deep=True))\n", "\n", " # add mentions for US collection\n", " collection_mentions = get_mentions_from_source(\n", " source_ids=[],\n", " source_name=\"US Collection\",\n", " queries=queries_in_use,\n", " year=YEAR,\n", " collection_ids=[US_COLLECTION_ID],\n", " )\n", " mentions_ls.append(collection_mentions.copy(deep=True))\n", "\n", " ### add single mentions for US collection\n", " collection_single_mentions = get_mentions_from_source(\n", " source_ids=[],\n", " source_name=\"US Collection\",\n", " queries=single_queries_in_use,\n", " year=YEAR,\n", " collection_ids=[US_COLLECTION_ID],\n", " )\n", " single_mentions_ls.append(collection_single_mentions.copy(deep=True))\n", "\n", " # concatenate all single mentions into a single DataFrame\n", " single_mentions_df = pd.concat(single_mentions_ls, ignore_index=True)\n", " single_mentions_df = single_mentions_df.rename(columns={\"mentions\": \"single_mentions\"})\n", "\n", " # concatenate all mentions into a single DataFrame\n", " mentions_df = pd.concat(mentions_ls, ignore_index=True)\n", " mentions_df = mentions_df.merge(single_mentions_df, on=[\"cause\", \"source\", \"year\"], how=\"left\")\n", " mentions_df = mentions_df[[\"year\", \"source\", \"cause\", \"mentions\", \"single_mentions\"]]\n", "\n", " if OVERWRITE:\n", " mentions_df.to_csv(\"./data/media_deaths_mentions.csv\", index=False)\n", "\n", "# to save time, you can use the cached version\n", "else:\n", " mentions_df = pd.read_csv(\"./data/media_deaths_mentions.csv\")\n" ] }, { "cell_type": "markdown", "id": "e832019f", "metadata": {}, "source": [ "## Analysis" ] }, { "cell_type": "code", "execution_count": 104, "id": "297318fb", "metadata": {}, "outputs": [], "source": [ "# these are some helper functions for the analysis:\n", "def add_shares(tb, columns=None):\n", " \"\"\"Add shares for each row relative to total of a columns to DataFrame.\"\"\"\n", " if columns is None:\n", " columns = [\"mentions\", \"deaths\"]\n", "\n", " for col in columns:\n", " total = tb[col].sum()\n", " if total == 0:\n", " tb.loc[:, f\"{col}_share\"] = 0\n", " else:\n", " tb.loc[:, f\"{col}_share\"] = round(\n", " (tb[col] / total) * 100, 3\n", " ) # convert to percentage, round to three decimal places\n", "\n", " return tb\n" ] }, { "cell_type": "code", "execution_count": 105, "id": "97179ea8", "metadata": { "tags": [ "collapsed" ] }, "outputs": [], "source": [ "def plot_media_deaths_matplotlib(media_deaths_df, columns=None, bar_labels=None, title=None, absolute=False, save_path=None, fixed_colors=FIXED_COLOURS):\n", " \"\"\"This function plots the final results. Colors are fixed for each cause of death.\n", " media_deaths_df: DataFrame containing media deaths data\n", " columns: List of columns to plot (default: [\"deaths_share\", \"mentions_share\"])\n", " bar_labels: List of labels for the bars (default: [\"Deaths\", \"Mentions\"])\n", " title: Title of the plot (default: \"Media Mentions of Causes of Death in 2023\")\n", " \"\"\"\n", "\n", " if columns is None:\n", " columns = [\"deaths_share\", \"mentions_share\"]\n", " bar_labels = [\"Deaths\", \"Mentions\"]\n", " if bar_labels is None:\n", " bar_labels = columns # fallback to original column names\n", " if title is None:\n", " title = f\"Media Mentions of Causes of Death in {YEAR}\"\n", "\n", " mm_plot = media_deaths_df[[\"cause\"] + columns].transpose()\n", " mm_plot.columns = mm_plot.iloc[0]\n", " mm_plot = mm_plot.drop(mm_plot.index[0]) # drop the first row which is the cause names\n", " mm_plot.index = bar_labels\n", " # maximum sum of values for each row\n", " max_val = mm_plot.sum(axis=1).max()\n", "\n", " ordered_cols = [cause for cause in CAUSES_OF_DEATH if cause in mm_plot.columns]\n", "\n", " # Ensure the causes in mm_plot.columns match the fixed color order\n", " color_order = [fixed_colors[cause] for cause in ordered_cols]\n", "\n", " # Plot with fixed colors and cause order\n", " mm_plot = mm_plot[ordered_cols] # Reorder the columns in desired order\n", " ax = mm_plot.plot(kind=\"bar\", stacked=True, color=color_order)\n", "\n", " ax.set_xticklabels(ax.get_xticklabels(), rotation=0)\n", "\n", " if absolute:\n", " plt.ylabel(\"Count\")\n", " else:\n", " plt.ylabel(\"Share\")\n", " plt.title(title, loc=\"center\")\n", " plt.legend(title=\"Cause of death\", bbox_to_anchor=(1.05, 1), loc=\"upper left\")\n", " plt.tight_layout()\n", "\n", " for i, row in enumerate(mm_plot.values):\n", " cumulative = 0\n", " for j, value in enumerate(row):\n", " if value > (max_val * 0.02):\n", " if absolute:\n", " seg_label = f\"{int(value)}\"\n", " else:\n", " seg_label = f\"{round(value, 1)}%\"\n", " ax.text(\n", " x=i, # bar index (x position)\n", " y=cumulative + value / 2, # vertical position in the middle of the bar segment\n", " s=seg_label, # label with one decimal place as percent, alternatively rounded to whole number\n", " ha=\"center\",\n", " va=\"center\",\n", " fontsize=8,\n", " )\n", " cumulative += value\n", "\n", " if save_path:\n", " plt.savefig(save_path, dpi=300, bbox_inches='tight')\n", " plt.show()" ] }, { "cell_type": "code", "execution_count": 106, "id": "0d8df5cf", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
deathscauseyear
0680981.0heart disease2023
1613352.0cancer2023
2181146.0accidents2023
3162639.0stroke2023
4145357.0respiratory2023
\n", "
" ], "text/plain": [ " deaths cause year\n", "0 680981.0 heart disease 2023\n", "1 613352.0 cancer 2023\n", "2 181146.0 accidents 2023\n", "3 162639.0 stroke 2023\n", "4 145357.0 respiratory 2023" ] }, "execution_count": 106, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# printing death and media mentions dataframes\n", "death_df.head()" ] }, { "cell_type": "code", "execution_count": 107, "id": "840e1f3b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
yearsourcecausementionssingle_mentions
02023The New York Timesheart disease4361028
12023The New York Timescancer6252163
22023The New York Timesaccidents14895242
32023The New York Timesstroke119789
42023The New York Timesrespiratory212392
\n", "
" ], "text/plain": [ " year source cause mentions single_mentions\n", "0 2023 The New York Times heart disease 436 1028\n", "1 2023 The New York Times cancer 625 2163\n", "2 2023 The New York Times accidents 1489 5242\n", "3 2023 The New York Times stroke 119 789\n", "4 2023 The New York Times respiratory 212 392" ] }, "execution_count": 107, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mentions_df.head()" ] }, { "cell_type": "code", "execution_count": 108, "id": "5cc26834", "metadata": {}, "outputs": [], "source": [ "# analysis\n", "# copy existing df into new dataframes to make rerunning cells easier\n", "tb_mentions = mentions_df.copy(deep=True)\n", "tb_deaths = death_df.copy(deep=True)\n", "\n", "# filter only on causes of death we are interested in\n", "tb_mentions = tb_mentions[tb_mentions[\"cause\"].isin(CAUSES_OF_DEATH)] #type:ignore\n", "\n", "tb_mentions= pd.merge(left=tb_mentions, right=tb_deaths, on=[\"cause\", \"year\"], how=\"left\")\n", "\n", "sources = tb_mentions[\"source\"].unique().tolist() #type:ignore\n", "\n", "# add shares to media mentions table\n", "tb_mentions.loc[:, \"mentions_share\"] = 0.0\n", "tb_mentions.loc[:, \"deaths_share\"] = 0.0\n", "tb_mentions.loc[:, \"single_mentions_share\"] = 0.0\n", "for source in sources:\n", " tb_s = tb_mentions[tb_mentions[\"source\"] == source]\n", " tb_s = add_shares(tb_s, columns=[\"mentions\", \"deaths\", \"single_mentions\"])\n", " tb_mentions.update(tb_s)\n", "\n", "# pivot table\n", "tb_mentions = tb_mentions.pivot(\n", " index=[\"cause\", \"year\", \"deaths\", \"deaths_share\"], columns=\"source\", values=[\"mentions\", \"mentions_share\", \"single_mentions\", \"single_mentions_share\"]\n", " ).reset_index()\n", "tb_mentions.columns = [\n", " \"cause\",\n", " \"year\",\n", " \"deaths\",\n", " \"deaths_share\",\n", " \"fox_mentions\",\n", " \"nyt_mentions\",\n", " \"wapo_mentions\",\n", " \"us_mentions\",\n", " \"fox_share\",\n", " \"nyt_share\",\n", " \"wapo_share\",\n", " \"us_share\",\n", " \"fox_single_mentions\",\n", " \"nyt_single_mentions\",\n", " \"wapo_single_mentions\",\n", " \"us_single_mentions\",\n", " \"fox_single_share\",\n", " \"nyt_single_share\",\n", " \"wapo_single_share\",\n", " \"us_single_share\",\n", " ]\n" ] }, { "cell_type": "code", "execution_count": 109, "id": "5e9c5552", "metadata": {}, "outputs": [], "source": [ "# save results\n", "if OVERWRITE:\n", " tb_mentions.to_csv(\"./data/media_deaths_results.csv\", index=False)" ] }, { "cell_type": "markdown", "id": "5969975c", "metadata": {}, "source": [ "## Results" ] }, { "cell_type": "code", "execution_count": null, "id": "2f01ff1d", "metadata": {}, "outputs": [], "source": [ "# If USE_SAVED_RESULTS is True, load the saved results\n", "if USE_SAVED_RESULTS:\n", " media_deaths_df = pd.read_csv(\"./data/media_deaths_results.csv\")\n", "else:\n", " media_deaths_df = tb_mentions.copy()" ] }, { "cell_type": "markdown", "id": "fdd867d2", "metadata": {}, "source": [ "### Results by source" ] }, { "cell_type": "code", "execution_count": null, "id": "b5aec699", "metadata": { "tags": [ "collapsed" ] }, "outputs": [], "source": [ "plot_media_deaths_matplotlib(media_deaths_df, columns=[\"deaths_share\", \"nyt_share\", \"wapo_share\", \"fox_share\", \"us_share\"], bar_labels=[\"Deaths\", \"NYT\", \"WaPo\", \"Fox\", \"US Collection\"], title=f\"Media Mentions of Causes of Death in {YEAR} - by Source\")" ] }, { "cell_type": "code", "execution_count": 4, "id": "bf6199ef", "metadata": { "tags": [ "collapsed" ] }, "outputs": [], "source": [ "def plot_media_deaths(media_deaths_df, columns=None, bar_labels=None, title=None, absolute=False, save_path=None, fixed_colors=FIXED_COLOURS):\n", " \"\"\"\n", " Interactive stacked bars comparing e.g. deaths vs media mentions.\n", " columns: list of 2 metrics to compare (e.g. ['deaths_share','us_share'])\n", " bar_labels: labels for those bars (e.g. ['Deaths','Mentions'])\n", " absolute: if True, treat values as counts; else as percentages (auto-scales 0-1 to 0-100)\n", " Requires globals YEAR and CAUSES_OF_DEATH (ordering).\n", " \"\"\"\n", "\n", " if columns is None:\n", " if {\"deaths_share\",\"us_share\"}.issubset(media_deaths_df.columns):\n", " columns = [\"deaths_share\",\"us_share\"]\n", " else:\n", " # pick first non-deaths *_share as the mention proxy\n", " shares = [c for c in media_deaths_df.columns if c.endswith(\"_share\")]\n", " if \"deaths_share\" in shares and len(shares) > 1:\n", " columns = [\"deaths_share\", next(c for c in shares if c != \"deaths_share\")]\n", " else:\n", " raise ValueError(\"Please pass columns=[...]; available share columns: \"\n", " + \", \".join(shares) if shares else \"none\")\n", "\n", " if bar_labels is None:\n", " # Nice names if they match common columns\n", " alias = {\"deaths_share\":\"Deaths\",\"us_share\":\"Mentions\",\"nyt_share\":\"NYT\",\"wapo_share\":\"WaPo\",\"fox_share\":\"Fox\"}\n", " bar_labels = [alias.get(c, c) for c in columns]\n", "\n", " if title is None:\n", " title = f\"Media Mentions of Causes of Death in {YEAR}\"\n", "\n", " # Validate columns\n", " missing = [c for c in columns if c not in media_deaths_df.columns]\n", " if missing:\n", " raise KeyError(f\"Columns not found: {missing}. Available: {list(media_deaths_df.columns)}\")\n", "\n", " # Build 2xN matrix: rows are bars, cols are causes\n", " mm_plot = (media_deaths_df[[\"cause\"] + columns]\n", " .set_index(\"cause\")\n", " .T) # index = selected columns, columns = causes\n", "\n", " # Order causes by canonical list\n", " ordered_cols = [c for c in CAUSES_OF_DEATH if c in mm_plot.columns]\n", " if not ordered_cols:\n", " ordered_cols = list(mm_plot.columns)\n", " mm_plot = mm_plot[ordered_cols]\n", " mm_plot.index = bar_labels # nicer row labels\n", "\n", " # If percentage mode and values look like 0–1, scale to 0–100\n", " if not absolute and mm_plot.to_numpy(dtype=float).max(initial=0) <= 1.0:\n", " mm_plot = mm_plot * 100.0\n", "\n", " max_val = mm_plot.sum(axis=1).max()\n", "\n", " fig = go.Figure()\n", " for cause in ordered_cols:\n", " vals = [float(mm_plot.loc[row, cause]) for row in bar_labels]\n", " # label only if segment >= 2% of largest row total\n", " texts = []\n", " for v in vals:\n", " if v > (max_val * 0.02):\n", " texts.append(f\"{int(v):,}\" if absolute else f\"{v:.1f}%\")\n", " else:\n", " texts.append(\"\")\n", " fig.add_trace(go.Bar(\n", " name=cause,\n", " x=bar_labels,\n", " y=vals,\n", " marker_color=fixed_colors.get(cause, \"#999\"),\n", " text=texts,\n", " textposition=\"inside\",\n", " insidetextanchor=\"middle\",\n", " hovertemplate=(\n", " \"Group=%{x}
Cause=%{fullData.name}
\"\n", " + (\"Value=%{y:,d}\" if absolute else \"Value=%{y:.1f}%\")\n", " + \"\"\n", " ),\n", " ))\n", "\n", " fig.update_layout(\n", " barmode=\"stack\",\n", " title=title,\n", " yaxis_title=(\"Count\" if absolute else \"Share\"),\n", " legend_title_text=\"Cause of death\",\n", " margin=dict(l=40, r=160, t=60, b=40),\n", " )\n", "\n", " if save_path:\n", " fig.write_image(save_path, scale=1, width=1000, height=600)\n", "\n", " fig.show()" ] }, { "cell_type": "code", "execution_count": 8, "id": "9307a9b0", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "plot_media_deaths(\n", " media_deaths_df,\n", " columns=[\"deaths_share\", \"nyt_share\",\"wapo_share\", \"fox_share\", \"us_share\"],\n", " bar_labels=[\"Deaths\", \"NYT\",\"WaPo\", \"Fox\", \"US Collection\"],\n", " absolute=False,\n", " title=\"Media mentions of causes of death in 2023\",\n", " save_path=\"data/media_deaths_by_source.png\"\n", ")" ] }, { "cell_type": "markdown", "id": "2f3a4336", "metadata": {}, "source": [ "### Single vs multiple mentions" ] }, { "cell_type": "code", "execution_count": null, "id": "ffa00425", "metadata": { "tags": [ "collapsed" ] }, "outputs": [], "source": [ "# plotting difference in single vs multiple mentions (absolute number of articles):\n", "plot_media_deaths_matplotlib(media_deaths_df, columns=[\"nyt_single_mentions\", \"nyt_mentions\"], bar_labels=[\"Single Mentions - NYT\", \"Multiple mentions - NYT\"], title=f\"Comparison: Articles mentioning CoD once vs multiple times\", absolute=True)" ] }, { "cell_type": "code", "execution_count": 9, "id": "7090753d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "plot_media_deaths(\n", " media_deaths_df,\n", " columns=[\"nyt_single_mentions\", \"nyt_mentions\"],\n", " bar_labels=[\"Single Mentions - NYT\", \"Multiple mentions - NYT\"],\n", " title=\"Comparison: articles mentioning the cause of death once vs multiple times\",\n", " absolute=True,\n", " save_path=\"data/media_deaths_nyt_single_vs_multiple_absolute.png\",\n", " )" ] }, { "cell_type": "code", "execution_count": null, "id": "9b7330ec", "metadata": { "tags": [ "collapsed" ] }, "outputs": [], "source": [ "# single vs multiple mentions in relative terms:\n", "plot_media_deaths_matplotlib(media_deaths_df, columns=[\"deaths_share\", \"nyt_single_share\", \"nyt_share\"], bar_labels=[\"Deaths\", \"Single Mentions\\nNYT\", \"Multiple mentions\\nNYT\"], title=f\"Comparison: Share of articles mentioning CoD once vs multiple times\")" ] }, { "cell_type": "code", "execution_count": 10, "id": "cb9fde22", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "plot_media_deaths(\n", " media_deaths_df,\n", " columns=[\"deaths_share\", \"nyt_single_share\",\"nyt_share\"],\n", " bar_labels=[\"Deaths\", \"Single Mentions\\nNYT\",\"Multiple mentions\\nNYT\"],\n", " title=\"Comparison: share of articles mentioning the cause of death once vs multiple times\",\n", " absolute=False,\n", " save_path=\"data/media_deaths_nyt_single_vs_multiple_relative.png\",\n", ")" ] } ], "metadata": { "kernelspec": { "display_name": "etl-py3.11", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.11.3" } }, "nbformat": 4, "nbformat_minor": 5 }