1. Outlet exclusions and finding top outlets.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Last updated: July 5, 2023\n",
    "\n",
    "Last run: July 5, 2023\n",
    "\n",
    "**Outlet filtering and identification of top outlets**\n",
    "\n",
    "## Unreviewed science in the news: The evolution of preprint media coverage from 2014-2021\n",
    "\n",
    "Juan Pablo Alperin\n",
    "\n",
    "**Related Publication:**\n",
    "Fleerackers, A., Shores, K., Chtena, N. & Alperin, J.P. (2023). Unreviewed science in the news: The evolution of preprint media coverage from 2014-2021. *bioarxiv*. \n",
    "\n",
    "**Related Dataset:**\n",
    "Alperin, J. P., Fleerackers, A., & Shores, K. (2023). Data for: Unreviewed science in the news [Data set]. Harvard Dataverse. https://doi.org/10.7910/DVN/ZHQUFD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: the unfiltered data used by this script is not available in the accompanying dataset because it includes unfiltered data from the Web of Science that cannot be shared due to contractual obligations. The original data was sourced from the OST, housed at UQAM."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Altmetric news mentions of items indexed in Web of Science \n",
    "\n",
    "`news_of_wos_with_doi_mention_details.csv` was exported from the database housed at the OST (UQAM)\n",
    "\n",
    "```\n",
    "SELECT \n",
    "      n.Altmetric_ID, n.[Order], alt.DOI, alt.Arxiv_ID, alt.First_Seen_On, alt.PubDate, alt.Title as alt_title, a.Annee_Bibliographique as wos_year, a.Titre as wos_title, n.Author_Name, n.Author_Url, n.Posted_On, n.Title, n.Url as moreover_url\n",
    "  FROM [WoS].[pex].[Article]  a INNER JOIN [WoS].[dbo].[Identifier] i ON (a.OST_BK = i.OST_BK AND i.Type = 'doi')\n",
    "  JOIN [Altmetrics_2021Jun3].[dbo].[Document] alt ON (i.Identifier = alt.DOI )\n",
    "  JOIN [Altmetrics_2021Jun3].[dbo].[News] n ON (alt.Altmetric_ID = n.Altmetric_ID)\n",
    "  WHERE n.Posted_On >= '2014'\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "input_file = 'data/news_of_wos_with_doi_mention_details.csv'\n",
    "columns = ['Altmetric_ID', 'Order', 'DOI', 'Arxiv_ID', 'First_Seen_On', 'PubDate', 'alt_title', 'wos_year', 'wos_title', 'Author_Name', 'Author_Url', 'Posted_On', 'Title', 'moreover_url']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/juan/.pyenv/versions/myenv3.8/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3146: DtypeWarning: Columns (3) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(input_file, sep=';', names=columns)\n",
    "df.columns = [x.lower() for x in df.columns]\n",
    "df.rename(columns = {'author_name': 'outlet', 'author_url': 'outlet_url'}, inplace=True)\n",
    "df['posted_on'] = pd.to_datetime(df.posted_on)\n",
    "df['posted_on_year'] = df.posted_on.map(lambda x: x.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Number of WoS mentions: 6,053,470'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"Number of WoS mentions: {:,}\".format(df.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique outlets with at least 100 stories each year (prior to exclusions): 128\n"
     ]
    }
   ],
   "source": [
    "df2 = df.groupby(['outlet', 'posted_on_year']).size().unstack()\n",
    "\n",
    "# Identify outlets that have at least 100 stories EVERY year between 2014 and 2020 (not 2021)\n",
    "df2 = df2[df2[list(range(2014, 2021))].apply(lambda x: x >= 100).all(axis=1)]\n",
    "\n",
    "print(\"Number of unique outlets with at least 100 stories each year (prior to exclusions): %s\" % len(df2.index.unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mentions prior to exclusion: 6,053,470\n",
      "Excluding from 34 outlets:\n",
      "Pansci: 1,542\n",
      "RT Network: 7,280\n",
      "Photonics.com: 2,455\n",
      "Helsingin Sanomat: 3,929\n",
      "Futura-Sciences: 8,234\n",
      "Sciences et Avenir: 7,355\n",
      "Top Santé: 3,401\n",
      "L'Express: 1,010\n",
      "Der Tagesspiegel: 1,793\n",
      "Deutsches Ärzteblatt: 7,994\n",
      "Der Standard: 3,423\n",
      "Spektrum: 13,442\n",
      "SPIEGEL ONLINE: 5,764\n",
      "Innovations Report: 14,208\n",
      "Neue Züricher Zeitung (NZZ): 2,837\n",
      "Informationsdienst Wissenschaft: 24,740\n",
      "Jura Forum: 7,746\n",
      "Die Welt: 4,311\n",
      "Ingeniøren: 1,592\n",
      "Ad Hoc News: 1,595\n",
      "LABO Online: 2,575\n",
      "Corriere della Sera: 1,691\n",
      "Le Scienze: 5,042\n",
      "Galileonet: 4,306\n",
      "El País: 6,644\n",
      "Agencia SINC: 7,281\n",
      "NCYT - Noticias de la Ciencia y la Technologia: 5,082\n",
      "MSN: 101,829\n",
      "MIT Technology Review: 2,355\n",
      "Bioportfolio: 38,023\n",
      "Health Canal: 18,283\n",
      "Mother Nature Network: 3,525\n",
      "NPR: 5,073\n",
      "Health24: 4,582\n",
      "\n",
      "Total exclusions: 330,942\n",
      "Remaining mentions: 5,722,528\n"
     ]
    }
   ],
   "source": [
    "exclude = pd.read_csv('data/excluded_outlets.csv', encoding='utf8')\n",
    "\n",
    "print('Mentions prior to exclusion: {:,}'.format(df.shape[0]))\n",
    "print('Excluding from {} outlets:'.format(exclude.shape[0]))\n",
    "t = 0\n",
    "for o in exclude.Outlet:\n",
    "    t += df[df['outlet'] == o].shape[0]\n",
    "    print('{}: {:,}'.format(o, df[df['outlet'] == o].shape[0]))\n",
    "        \n",
    "print()\n",
    "print('Total exclusions: {:,}'.format(t))\n",
    "del t\n",
    "df = df[~df.outlet.isin(exclude.Outlet)]\n",
    "print('Remaining mentions: {:,}'.format(df.shape[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique outlets with at least 100 stories each year: 94\n"
     ]
    }
   ],
   "source": [
    "df2 = df.groupby(['outlet', 'posted_on_year']).size().unstack()\n",
    "\n",
    "# Identify outlets that have at least 100 stories EVERY year between 2014 and 2020 (not 2021)\n",
    "df2 = df2[df2[list(range(2014, 2021))].apply(lambda x: x >= 100).all(axis=1)]\n",
    "df2.to_csv('data/top outlets/outlets.csv')\n",
    "df = df[df.outlet.isin(df2.index)]\n",
    "\n",
    "print(\"Number of unique outlets with at least 100 stories each year: %s\" % len(df.outlet.unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data/top outlets/wos_with_doi_mention_details.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'We downloaded all of the mentions of WoS research from our 94 outlets, resulting in 1,657,202 mentions of 466,138 distinct research outputs in 1,113,270 distinct news stories'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"We downloaded all of the mentions of WoS research from our {:,} outlets, resulting in {:,} mentions of {:,} distinct research outputs in {:,} distinct news stories\".format(df.outlet.unique().shape[0], df.shape[0], df.altmetric_id.unique().shape[0], df[['outlet', 'moreover_url']].drop_duplicates().shape[0])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Altmetric news mentions of items published in preprint servers\n",
    "\n",
    "`news_of_preprints_mention_details.csv` was exported from the database housed at the OST (UQAM)\n",
    "\n",
    "```\n",
    "  SELECT \n",
    "      n.Altmetric_ID, n.[Order], alt.DOI, alt.Arxiv_ID, j.Journal, j.Altmetric_JID, alt.First_Seen_On, alt.PubDate, alt.Title as alt_title, n.Author_Name, n.Author_Url, n.Posted_On, n.Title, n.Url as moreover_url\n",
    "  FROM\n",
    "  [Altmetrics_2021Jun3].[dbo].[Document] alt \n",
    "  JOIN [Altmetrics_2021Jun3].[dbo].[News] n ON (alt.Altmetric_ID = n.Altmetric_ID)\n",
    "  LEFT JOIN [Altmetrics_2021Jun3].[dbo].[Journal] as j ON (alt.Altmetric_ID = j.Altmetric_ID)\n",
    "\n",
    "  WHERE n.Posted_On >= '2014'\n",
    "  AND (j.Journal IN ('arXiv', 'bioRxiv', 'ChemRxiv', 'medRxiv', 'Social Science Research Network (SSRN)')\n",
    "  OR j.Altmetric_JID IN ('arxiv', 'biorxiv', 'chemrxiv', 'medrxiv', 'ssrn'))\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "input_file = 'data/news_of_preprints_mention_details.csv'\n",
    "columns = ['Altmetric_ID', 'Order', 'DOI', 'Arxiv_ID', 'Journal', 'Altmetric_JID', 'First_Seen_On', 'PubDate', 'alt_title', 'Author_Name', 'Author_Url', 'Posted_On', 'Title', 'moreover_url']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'173,802'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(input_file, sep=';', names=columns)\n",
    "\"{:,}\".format(df.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = [x.lower() for x in df.columns]\n",
    "df.rename(columns = {'author_name': 'outlet', 'author_url': 'outlet_url'}, inplace=True)\n",
    "df['posted_on'] = pd.to_datetime(df.posted_on)\n",
    "df['posted_on_year'] = df.posted_on.map(lambda x: x.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['server'] = df.apply(lambda row: row['altmetric_jid'] if pd.isna(row['journal']) else row['journal'], axis=1)\n",
    "df['server'] = df.apply(lambda row: 'ssrn' if 'SSRN' in row['server'] else row['server'], axis=1)\n",
    "df['server'] = df.server.str.lower()\n",
    "\n",
    "# remove chemrxiv \n",
    "df = df[df.server != 'chemrxiv']\n",
    "del df['journal']\n",
    "del df['altmetric_jid']\n",
    "\n",
    "df = df[df.outlet.isin(df2.index)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'A total of 40,039 mentions of 15,041 preprints across 31,258 news stories.'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The last number might not be exactly right, because there are 2,413 some moreover_urls that are blank\n",
    "\"A total of {:,} mentions of {:,} preprints across {:,} news stories.\".format(df.shape[0], df.altmetric_id.unique().shape[0], df[['outlet', 'moreover_url']].drop_duplicates().shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data/top outlets/preprints_mention_details.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}