Examine unreported trials on ClinicalTrials.gov.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Finding trials registered on ClinicalTrials.gov that do not have reported results\n",
    "\n",
    "Reporting of clinical trial results became mandatory for many trials in 2008. However [this paper](http://www.bmj.com/content/352/bmj.i637) and [this investigation](https://www.statnews.com/2015/12/13/clinical-trials-investigation/) both find that substantial numbers of clinical trials have not reported results, even for those trials where the FDAAA has made reporting mandatory.\n",
    "\n",
    "This notebook examines how many trials on ClinicalTrials.gov have had their results publicly reported. We have a broader definition of a trial that should report its results than the FDAAA. We count a trial as eligible for our analysis if:\n",
    "\n",
    "- it has overall status of 'Completed'\n",
    "- it has a study type of 'Interventional' \n",
    "- its completion date was after 1 Jan 2006, but is more than 24 months ago\n",
    "- it is phase 2 or later (or its phase is N/A, ie it's a trial of a device or a behavioural intervention)\n",
    "- it has no results disposition date (i.e. no application to delay results has been filed).\n",
    "\n",
    "We then classify it as overdue if it has no summary results attached on ClinicalTrials.gov, and no results on PubMed that are linked by NCT ID (see below). \n",
    "\n",
    "This is substantially broader than FDAAA, which covers only US-based trials of FDA-approved drugs. However, we think all trials should report their results, not just US-based trials, or FDA-approved drugs. In addition, FDAAA requires results to be reported within 12 months of completion, and we allow 24 months.\n",
    "\n",
    "ClinicalTrials.gov supplies notes on [how to find studies with results](https://clinicaltrials.gov/ct2/help/how-find/find-study-results) and [results in general](https://clinicaltrials.gov/ct2/about-site/results)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import csv\n",
    "from datetime import datetime\n",
    "from dateutil.relativedelta import relativedelta\n",
    "import glob\n",
    "from pprint import pprint\n",
    "from slugify import slugify\n",
    "\n",
    "import sqlite3\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import utils"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create summary results file\n",
    "\n",
    "The raw XML trial summaries from ClinicalTrials.gov are supplied as a [single very large zip file](https://clinicaltrials.gov/search?studyxml=true), containing more than 200,000  XML files. This section assumes that that these have already been downloaded and unzipped in the `search_result` directory.\n",
    "\n",
    "Extract the fields of interest from the XML summaries, and save them to a CSV file, which we'll use as our source data for the rest of this exercise. ClinicalTrials.gov supplies [field definitions](https://prsinfo.clinicaltrials.gov/definitions.html). \n",
    "\n",
    "Toggle `REGENERATE_SUMMARY` to `False` for the purposes of development."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done\n"
     ]
    }
   ],
   "source": [
    "fname = './data/trials.csv'\n",
    "REGENERATE_SUMMARY = False # False\n",
    "if REGENERATE_SUMMARY:\n",
    "    files = glob.glob('./search_result/*.xml')\n",
    "    print len(files), 'files found'\n",
    "    fieldnames = ['nct_id', 'title', 'overall_status', \n",
    "                  'study_type', 'completion_date',\n",
    "                  'lead_sponsor', 'lead_sponsor_class',\n",
    "                  'collaborator', 'collaborator_class', \n",
    "                  'phase', 'locations', 'has_drug_intervention', 'drugs', \n",
    "                  'disposition_date', 'results_date', \n",
    "                  'enrollment']\n",
    "    trials = csv.DictWriter(open(fname, 'wb'), fieldnames=fieldnames)\n",
    "    trials.writeheader()\n",
    "    for i, f in enumerate(files):\n",
    "        if i % 50000 == 0:\n",
    "            print i, f\n",
    "        text = open(f, 'r').read()\n",
    "        data = utils.extract_ctgov_xml(text)\n",
    "        trials.writerow(data)\n",
    "print 'done'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data for analysis\n",
    "\n",
    "Load into Pandas, normalising the date and phase fields. NB: If this produces a weird EOF error (which it does intermittently), delete the last line of the file manually. We will have to live with one missing trial. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dtype = {'has_drug_intervention': bool, \n",
    "         'phase': str} \n",
    "converters = {'enrollment': lambda x: x and int(x) or 0} \n",
    "datefields = ['completion_date', 'results_date', 'disposition_date']\n",
    "df = pd.read_csv(fname,\n",
    "                 parse_dates=datefields, \n",
    "                 infer_datetime_format=True,\n",
    "                 keep_default_na=False,\n",
    "                 na_values=None,\n",
    "                 converters=converters,\n",
    "                 dtype=dtype)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['phase_normalised'] = df.phase.apply(utils.normalise_phase)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nct_id</th>\n",
       "      <th>title</th>\n",
       "      <th>overall_status</th>\n",
       "      <th>study_type</th>\n",
       "      <th>completion_date</th>\n",
       "      <th>lead_sponsor</th>\n",
       "      <th>lead_sponsor_class</th>\n",
       "      <th>collaborator</th>\n",
       "      <th>collaborator_class</th>\n",
       "      <th>phase</th>\n",
       "      <th>locations</th>\n",
       "      <th>has_drug_intervention</th>\n",
       "      <th>drugs</th>\n",
       "      <th>disposition_date</th>\n",
       "      <th>results_date</th>\n",
       "      <th>enrollment</th>\n",
       "      <th>phase_normalised</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>240369</th>\n",
       "      <td>NCT00683956</td>\n",
       "      <td>Evaluation of a Closed-Loop Control System for...</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Interventional</td>\n",
       "      <td>2009-07-01</td>\n",
       "      <td>Stanford University</td>\n",
       "      <td>Other</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>United States</td>\n",
       "      <td>True</td>\n",
       "      <td>Propofol;</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240370</th>\n",
       "      <td>NCT01540734</td>\n",
       "      <td>Pharmacokinetic Study Investigating the Extent...</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Interventional</td>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>GlaxoSmithKline</td>\n",
       "      <td>Industry</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>United States</td>\n",
       "      <td>True</td>\n",
       "      <td>Marketed paracetamol; Experimental paracetamol...</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240371</th>\n",
       "      <td>NCT02691429</td>\n",
       "      <td>Applicability of the Acai Fruit (Euterpe Olera...</td>\n",
       "      <td>Recruiting</td>\n",
       "      <td>Interventional</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>Federal University of São Paulo</td>\n",
       "      <td>Other</td>\n",
       "      <td>Rafael R. Caiado Cristiane S. Peris Acácio Alv...</td>\n",
       "      <td>Other Other Other Other Other Other Other Othe...</td>\n",
       "      <td>N/A</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>24</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240372</th>\n",
       "      <td>NCT02947750</td>\n",
       "      <td>Neurovascular Transduction During Exercise in ...</td>\n",
       "      <td>Recruiting</td>\n",
       "      <td>Interventional</td>\n",
       "      <td>2021-11-01</td>\n",
       "      <td>Emory University</td>\n",
       "      <td>Other</td>\n",
       "      <td>National Heart, Lung, and Blood Institute (NHLBI)</td>\n",
       "      <td>NIH</td>\n",
       "      <td>2</td>\n",
       "      <td>United States</td>\n",
       "      <td>True</td>\n",
       "      <td>6R-BH4;</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>150</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240373</th>\n",
       "      <td>NCT00005693</td>\n",
       "      <td>Computer Assisted Instruction Weight Managemen...</td>\n",
       "      <td>Completed</td>\n",
       "      <td>Observational</td>\n",
       "      <td>NaT</td>\n",
       "      <td>National Heart, Lung, and Blood Institute (NHLBI)</td>\n",
       "      <td>NIH</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>N/A</td>\n",
       "      <td></td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             nct_id                                              title  \\\n",
       "240369  NCT00683956  Evaluation of a Closed-Loop Control System for...   \n",
       "240370  NCT01540734  Pharmacokinetic Study Investigating the Extent...   \n",
       "240371  NCT02691429  Applicability of the Acai Fruit (Euterpe Olera...   \n",
       "240372  NCT02947750  Neurovascular Transduction During Exercise in ...   \n",
       "240373  NCT00005693  Computer Assisted Instruction Weight Managemen...   \n",
       "\n",
       "       overall_status      study_type completion_date  \\\n",
       "240369      Completed  Interventional      2009-07-01   \n",
       "240370      Completed  Interventional      2009-12-01   \n",
       "240371     Recruiting  Interventional      2016-11-01   \n",
       "240372     Recruiting  Interventional      2021-11-01   \n",
       "240373      Completed   Observational             NaT   \n",
       "\n",
       "                                             lead_sponsor lead_sponsor_class  \\\n",
       "240369                                Stanford University              Other   \n",
       "240370                                    GlaxoSmithKline           Industry   \n",
       "240371                    Federal University of São Paulo              Other   \n",
       "240372                                   Emory University              Other   \n",
       "240373  National Heart, Lung, and Blood Institute (NHLBI)                NIH   \n",
       "\n",
       "                                             collaborator  \\\n",
       "240369                                                      \n",
       "240370                                                      \n",
       "240371  Rafael R. Caiado Cristiane S. Peris Acácio Alv...   \n",
       "240372  National Heart, Lung, and Blood Institute (NHLBI)   \n",
       "240373                                                      \n",
       "\n",
       "                                       collaborator_class phase  \\\n",
       "240369                                                        1   \n",
       "240370                                                        1   \n",
       "240371  Other Other Other Other Other Other Other Othe...   N/A   \n",
       "240372                                                NIH     2   \n",
       "240373                                                      N/A   \n",
       "\n",
       "            locations has_drug_intervention  \\\n",
       "240369  United States                  True   \n",
       "240370  United States                  True   \n",
       "240371         Brazil                 False   \n",
       "240372  United States                  True   \n",
       "240373                                False   \n",
       "\n",
       "                                                    drugs disposition_date  \\\n",
       "240369                                         Propofol;               NaT   \n",
       "240370  Marketed paracetamol; Experimental paracetamol...              NaT   \n",
       "240371                                                                 NaT   \n",
       "240372                                           6R-BH4;               NaT   \n",
       "240373                                                                 NaT   \n",
       "\n",
       "       results_date  enrollment  phase_normalised  \n",
       "240369          NaT          35                 1  \n",
       "240370          NaT          28                 1  \n",
       "240371          NaT          24                 5  \n",
       "240372          NaT         150                 2  \n",
       "240373          NaT           0                 5  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate whether trials are completed\n",
    "\n",
    "The criteria for counting a trial as completed are defined above. Print some summary stats about completed trials. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cutoff date 2015-05-03 11:15:15.882637\n",
      "240374 total trials found\n",
      "3621 trials have dispositions filed\n",
      "54301 are completed and due results, by our definition\n",
      "16101 trials due results have submitted results on clinicaltrials.gov\n",
      "38200 trials due results have not submitted results on clinicaltrials.gov\n"
     ]
    }
   ],
   "source": [
    "startdate = datetime.strptime('01 January 2006', '%d %B %Y')\n",
    "cutoff = datetime.now() - relativedelta(years=2)\n",
    "print 'Cutoff date', cutoff\n",
    "\n",
    "df['is_completed'] = (df.overall_status == 'Completed') & \\\n",
    "    (df.study_type.str.startswith('Interventional')) & \\\n",
    "    (df.completion_date >= startdate) & \\\n",
    "    (df.completion_date <= cutoff) & \\\n",
    "    (df.phase_normalised >= 2) & \\\n",
    "    (df.disposition_date.isnull())\n",
    "df['is_overdue'] = (df.is_completed & \\\n",
    "                    df.results_date.isnull())\n",
    "df_completed = df[df.is_completed] \n",
    "df_overdue = df[df.is_completed & df.results_date.isnull()]\n",
    "\n",
    "print len(df), 'total trials found'\n",
    "print len(df[~df.disposition_date.isnull()]), 'trials have dispositions filed'\n",
    "print len(df_completed), 'are completed and due results, by our definition'\n",
    "print len(df[df.is_completed & ~df.results_date.isnull()]), \\\n",
    "    'trials due results have submitted results on clinicaltrials.gov'\n",
    "print len(df_overdue), \\\n",
    "    'trials due results have not submitted results on clinicaltrials.gov'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check for results on PubMed\n",
    "\n",
    "If trials have reported their results on PubMed, and if it's possible [to find them on PubMed using a linked NCT ID](https://www.nlm.nih.gov/bsd/policy/clin_trials.html), then we count those trials as having submitted results. \n",
    "\n",
    "So, for all trials that we regard as completed and due results, and that haven't already reported results on clinicaltrials.gov, we search PubMed, looking for the NCT ID either as a Secondary Source ID, or in the title/abstract. We look for anything published between the completion date and now, that doesn't have the words \"study protocol\" in the title, and that is [classified as results of a trial (using the \"therapy\" clinical keyword, broad version)](https://www.ncbi.nlm.nih.gov/books/NBK3827/#pubmedhelp.Clinical_Queries_Filters). \n",
    "\n",
    "At the time of writing, about 9,000 of the 34,000 trials have results on PubMed. An example of an NCT ID with results on PubMed: NCT02460380. (TODO: Update this). \n",
    "\n",
    "**Note 1**: we know from [the BMJ paper](http://www.bmj.com/content/352/bmj.i637) that there are trials that do have results on PubMed, but that aren't linked using the NCT ID. The BMJ authors found these using a manual search. Some examples: `NCT00002762: 19487378`, `NCT00002879: 18470909`, `NCT00003134: 19066728`, `NCT00003596: 18430910`. We regard these as invalid, because you can only find results via an exhaustive manual search. We only count results as published for our purposes if they are either (i) submitted on ClinicalTrials.gov or (ii) retrievable on PubMed using the NCT ID. See more on this below.\n",
    "\n",
    "**Note 2**: we know there are some trials that have results PMIDs directly in ClinicalTrials.gov, in the `results_reference` field of the XML. After discussion with Jess here, and Annice at ClinicalTrials.gov, I decided that these results are too often meaningless to be useful - lots of the time they aren't truly results, but are studies from years ago.\n",
    "\n",
    "**Note 3**: we also experimented with retrieving the results using the narrow version of the \"therapy\" clinical keyword, and using no clinical keyword at all. We evaluated these by using multiple PubMed matches as surrogate measures for false identification. At the time of writing on 2016/10/24, we examined 34677 trial registry IDs: the PubMed broad keyword yielded 7815 matches with 1706 multiple matches; the PubMed narrow keyword yielded 6448 matches with 1238 multiple matches, and using no keyword yielded 7981 matches with 1860 multiple matches. We chose the broad keyword for our final results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 NCT00135811\n",
      "10000 NCT00891358\n",
      "20000 NCT02243930\n",
      "30000 NCT01196546\n",
      "done\n"
     ]
    }
   ],
   "source": [
    "# Store results locally.\n",
    "conn = sqlite3.connect('./data/trials-abstract.db')\n",
    "cur = conn.cursor()  \n",
    "c = \"CREATE TABLE IF NOT EXISTS trials(nct_id TEXT PRIMARY KEY, \"\n",
    "c += \"pubmed_results INT, pubmed_results_broad INT, pubmed_results_narrow INT)\"\n",
    "cur.execute(c)\n",
    "conn.commit()\n",
    "\n",
    "REGENERATE_PUBMED_LINKS = False\n",
    "count = 0\n",
    "df['pubmed_results'] = False\n",
    "for i, row in df_overdue.iterrows():\n",
    "    if count % 10000 == 0:\n",
    "        print count, row.nct_id\n",
    "    count += 1\n",
    "    # First, check for results stored in the local db.\n",
    "    c = \"SELECT nct_id, pubmed_results, pubmed_results_broad, \"\n",
    "    c += \"pubmed_results_narrow FROM trials WHERE nct_id='%s'\" % row.nct_id\n",
    "    cur.execute(c)\n",
    "    data = cur.fetchone()\n",
    "    has_results = False\n",
    "    if data and (not REGENERATE_PUBMED_LINKS):\n",
    "        has_results = bool(int(data[2]))\n",
    "    else:\n",
    "        # No local results, or we want to regenerate them: check PubMed.\n",
    "        broad_results = \\\n",
    "            utils.get_pubmed_linked_articles(row.nct_id, \n",
    "                                             row.completion_date, 'broad')\n",
    "        # Used in the past (see note 3 above).\n",
    "        simple_results = \\\n",
    "            utils.get_pubmed_linked_articles(row.nct_id, \n",
    "                                             row.completion_date, '')\n",
    "        narrow_results = \\\n",
    "            utils.get_pubmed_linked_articles(row.nct_id, \n",
    "                                             row.completion_date, 'narrow')\n",
    "        c = \"INSERT OR REPLACE INTO trials VALUES('%s', %s, %s, %s)\" % \\\n",
    "            (row.nct_id, len(simple_results), len(broad_results), len(narrow_results))\n",
    "        cur.execute(c)\n",
    "        conn.commit()\n",
    "        has_results = broad_results > 0\n",
    "    df.set_value(i, 'pubmed_results', has_results) \n",
    "    \n",
    "cur.close()\n",
    "conn.close()\n",
    "print 'done' "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate final overdue count\n",
    "\n",
    "Now we have looked for PubMed results, we can calculate the final overdue count, and print some summary statistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "How many of the unreported trials were found on PubMed:\n",
      "False    27794\n",
      "True     10406\n",
      "Name: pubmed_results, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Reset dataframes now we have the results from PubMed.\n",
    "df['is_overdue'] = (df.is_completed & df.results_date.isnull() & ~df.pubmed_results)\n",
    "print 'How many of the unreported trials were found on PubMed:'\n",
    "print df[df.is_completed & df.results_date.isnull()].pubmed_results.value_counts()\n",
    "df_completed = df[df.is_completed]\n",
    "df_overdue = df[df.is_overdue]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "54301 trials should have published results\n",
      "27794 trials have not published results\n",
      "48.81% of completed trials have published results\n",
      "30570515 total patients are enrolled in overdue trials\n"
     ]
    }
   ],
   "source": [
    "# Print summary stats for the entire dataset. \n",
    "print len(df_completed), 'trials should have published results'\n",
    "print len(df_overdue), 'trials have not published results'\n",
    "percent_submitted = (1 - (len(df_overdue) / float(len(df_completed)))) * 100\n",
    "print '%s%% of completed trials have published results' % \\\n",
    "    '{:,.2f}'.format(percent_submitted)\n",
    "print int(df_overdue.enrollment.sum()), 'total patients are enrolled in overdue trials'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "29377 trials by major sponsors should have published results\n",
      "13266 trials by major sponsors have not published results\n",
      "54.84% of completed trials by major sponsors have published results\n",
      "5602426 total patients are enrolled in overdue trials\n"
     ]
    }
   ],
   "source": [
    "# Print summary stats for major trial sponsors only.\n",
    "NUM_TRIALS = 30\n",
    "df_major = df_completed[\n",
    "    df_completed.groupby('lead_sponsor').nct_id.transform(len) >= NUM_TRIALS]\n",
    "print len(df_major), 'trials by major sponsors should have published results'\n",
    "print len(df_major[df_major.is_overdue]), 'trials by major sponsors have not published results'\n",
    "percent_submitted = (1 - (len(df_major[df_major.is_overdue]) / float(len(df_major)))) * 100\n",
    "print '%s%% of completed trials by major sponsors have published results' % \\\n",
    "    '{:,.2f}'.format(percent_submitted)\n",
    "print int(df_major[df_major.is_overdue].enrollment.sum()), 'total patients are enrolled in overdue trials'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>is_overdue</th>\n",
       "      <th>is_completed</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lead_sponsor_class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Industry</th>\n",
       "      <td>6280.0</td>\n",
       "      <td>16997.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NIH</th>\n",
       "      <td>421.0</td>\n",
       "      <td>1185.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other</th>\n",
       "      <td>20778.0</td>\n",
       "      <td>35267.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>U.S. Fed</th>\n",
       "      <td>315.0</td>\n",
       "      <td>852.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    is_overdue  is_completed\n",
       "lead_sponsor_class                          \n",
       "Industry                6280.0       16997.0\n",
       "NIH                      421.0        1185.0\n",
       "Other                  20778.0       35267.0\n",
       "U.S. Fed                 315.0         852.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_completed.groupby('lead_sponsor_class').sum()[['is_overdue', 'is_completed']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>is_overdue</th>\n",
       "      <th>is_completed</th>\n",
       "      <th>percent_overdue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lead_sponsor_class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Industry</th>\n",
       "      <td>6280.0</td>\n",
       "      <td>16997.0</td>\n",
       "      <td>36.947697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NIH</th>\n",
       "      <td>421.0</td>\n",
       "      <td>1185.0</td>\n",
       "      <td>35.527426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other</th>\n",
       "      <td>20778.0</td>\n",
       "      <td>35267.0</td>\n",
       "      <td>58.916267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>U.S. Fed</th>\n",
       "      <td>315.0</td>\n",
       "      <td>852.0</td>\n",
       "      <td>36.971831</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    is_overdue  is_completed  percent_overdue\n",
       "lead_sponsor_class                                           \n",
       "Industry                6280.0       16997.0        36.947697\n",
       "NIH                      421.0        1185.0        35.527426\n",
       "Other                  20778.0       35267.0        58.916267\n",
       "U.S. Fed                 315.0         852.0        36.971831"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate publication rates by sector (raw data)\n",
    "df_by_sector = df_completed.groupby('lead_sponsor_class').sum()[['is_overdue', 'is_completed']]\n",
    "df_by_sector['percent_overdue'] = df_by_sector.is_overdue / df_by_sector.is_completed * 100\n",
    "df_by_sector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>is_overdue</th>\n",
       "      <th>is_completed</th>\n",
       "      <th>percent_overdue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lead_sponsor_class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Industry</th>\n",
       "      <td>2474.0</td>\n",
       "      <td>9511.0</td>\n",
       "      <td>26.011986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NIH</th>\n",
       "      <td>345.0</td>\n",
       "      <td>1030.0</td>\n",
       "      <td>33.495146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other</th>\n",
       "      <td>10330.0</td>\n",
       "      <td>18334.0</td>\n",
       "      <td>56.343406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>U.S. Fed</th>\n",
       "      <td>117.0</td>\n",
       "      <td>502.0</td>\n",
       "      <td>23.306773</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    is_overdue  is_completed  percent_overdue\n",
       "lead_sponsor_class                                           \n",
       "Industry                2474.0        9511.0        26.011986\n",
       "NIH                      345.0        1030.0        33.495146\n",
       "Other                  10330.0       18334.0        56.343406\n",
       "U.S. Fed                 117.0         502.0        23.306773"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate publication rates by sector (major sponsors only)\n",
    "df_major_gp = df_major.groupby('lead_sponsor_class').sum()[['is_overdue', 'is_completed']]\n",
    "df_major_gp['percent_overdue'] = df_major_gp.is_overdue / df_major_gp.is_completed * 100\n",
    "df_major_gp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write to CSV\n",
    "\n",
    "Output final results to a CSV file, which we will use in the interactive. We reshape the data so it has a row for each sponsor, and two columns for each year: one column for the number of overdue results, and one for the total trials. \n",
    "\n",
    "Also, write all the raw data to a single CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/ipykernel/__main__.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  if __name__ == '__main__':\n",
      "/usr/local/lib/python2.7/dist-packages/ipykernel/__main__.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  from ipykernel import kernelapp as app\n"
     ]
    }
   ],
   "source": [
    "df_completed['year_completed'] = df_completed['completion_date'].dt.year.dropna().astype(int)\n",
    "df_completed['year_completed'] = df_completed.year_completed.astype(int)\n",
    "\n",
    "# Drop all sponsors with fewer than N completed trials.\n",
    "df_final = df_completed[\n",
    "    df_completed.groupby('lead_sponsor').nct_id.transform(len) >= NUM_TRIALS]\n",
    "\n",
    "# Now reshape the data: a row for each sponsor, columns by year:\n",
    "# lead_sponsor,2008_overdue,2008_total,2009_overdue,2009_total...\n",
    "df_temp = df_final.set_index(['lead_sponsor', 'lead_sponsor_class', 'year_completed']) \n",
    "gb = df_temp.groupby(level=[0, 1, 2]).is_overdue\n",
    "df2 = gb.agg({'overdue': 'sum', 'total': 'count'}) \\\n",
    "          .unstack().swaplevel(0, 1, 1).sort_index(1)\n",
    "df2.columns = df2.columns.to_series().apply(lambda x: '{}_{}'.format(*x))\n",
    "df3 = df2.reset_index()\n",
    "df3['lead_sponsor_slug'] = df3.lead_sponsor.apply(slugify)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "323 sponsors found with cutoff point at 30 trials\n"
     ]
    }
   ],
   "source": [
    "\n",
    "df3.to_csv('./data/completed.csv', index=None)\n",
    "print len(df3), 'sponsors found with cutoff point at %s trials' % NUM_TRIALS\n",
    "\n",
    "# Write the raw output to a full spreadsheet. \n",
    "df.to_csv('./data/all.csv', index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## For reference: Compare our results with BMJ authors\n",
    "\n",
    "TODO: Make this a separate notebook?\n",
    "\n",
    "A [2016 BMJ paper](http://www.bmj.com/content/352/bmj.i637) found that around 65% of papers reprted results. \"Overall, 2892 of the 4347 clinical trials (66.5%) had been published or reported results as of July 2014.\" \n",
    "\n",
    "Excellently, the BMJ authors [publish their raw data on DataDryad](http://datadryad.org/resource/doi:10.5061/dryad.6n018) so we can compare our results with theirs, to get an idea of the difference between our automated strategy and their partially manual strategy. (However, in their reported data it looks to me like the matched PMID rate is 59.9% of all NCT IDs.)\n",
    "\n",
    "The BMJ authors were looking at a much smaller set of papers than us, because they focussed on academic medical centres. Their set is slightly different, because they include pre-Phase-2 trials, and 'Terminated' as well as 'Completed' trials. They also used a manual search strategy which involved searching Scopus and manually comparing results. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/openpyxl/reader/worksheet.py:322: UserWarning: Data Validation extension is not supported and will be removed\n",
      "  warn(msg)\n",
      "/usr/local/lib/python2.7/dist-packages/openpyxl/workbook/names/named_range.py:125: UserWarning: Discarded range with reserved name\n",
      "  warnings.warn(\"Discarded range with reserved name\")\n"
     ]
    }
   ],
   "source": [
    "from openpyxl import load_workbook\n",
    "import sys\n",
    "bmj_results = load_workbook(filename = './data/chen-bmj.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4347 rows found in total\n",
      "2452 of those rows have a PMID\n",
      "56.4067172763 per cent of their NCT IDs have a PMID, including duplicates\n",
      "\n",
      "4092 *unique* NCT IDs found in all rows\n",
      "2295 of these have PMIDs\n",
      "56.0850439883 per cent of unique NCT IDs have a PMID\n"
     ]
    }
   ],
   "source": [
    "nct_ids = {}\n",
    "count = 0\n",
    "has_pmid = 0\n",
    "\n",
    "# The Excel data has multiple worksheets, sigh. \n",
    "# And NCT IDs can occur more than once with different results, sigh.\n",
    "# We only care about where there's at least one result.\n",
    "# Fiddle about and reshape the data so that we know whether \n",
    "# each NCT ID has a result. \n",
    "for sheet in bmj_results.worksheets:\n",
    "    for i, row in enumerate(sheet.rows):\n",
    "        if i == 0:\n",
    "            continue\n",
    "        if row[0].value:\n",
    "            count += 1\n",
    "            if isinstance(row[6].value, long):\n",
    "                val = str(row[6].value)\n",
    "            else:\n",
    "                val = row[6].value\n",
    "            if val:\n",
    "                has_pmid += 1\n",
    "            # Always set val if it exists.\n",
    "            # Otherwise, only set val if there's no current value\n",
    "            # for this NCT ID.\n",
    "            if val:\n",
    "                nct_ids[row[0].value] = val\n",
    "            else:\n",
    "                if not row[0].value in nct_ids:\n",
    "                    nct_ids[row[0].value] = val\n",
    "\n",
    "print count, 'rows found in total'\n",
    "print has_pmid, 'of those rows have a PMID'\n",
    "print has_pmid / float(count) * 100, 'per cent of their NCT IDs have a PMID, including duplicates'\n",
    "print\n",
    "unique_nct_ids = len(nct_ids.keys())\n",
    "print unique_nct_ids, '*unique* NCT IDs found in all rows'\n",
    "pmids_found = sum(1 for x in nct_ids.values() if x)\n",
    "print pmids_found, 'of these have PMIDs'\n",
    "print pmids_found / float(unique_nct_ids) * 100, 'per cent of unique NCT IDs have a PMID'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compare with our data\n",
    "\n",
    "Now examine: \n",
    "\n",
    "* of the NCT IDs for which BMJ authors find PubMed results, how many we also find PubMed results for\n",
    "* of the same dataset, how many *only BMJ* find results for\n",
    "* of the NCT IDs for which BMJ authors do not find PubMed results, how many we do find PubMed results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4092 NCT IDs in the full BMJ dataset\n",
      "2541 NCT IDs are in both the BMJ dataset and ours\n",
      "1164 we both find results for\n",
      "461 only they find results for\n",
      "409 only we find results for\n",
      "507 neither of us find results for\n"
     ]
    }
   ],
   "source": [
    "df_bmj = pd.Series(nct_ids).to_frame(name='pmid')\n",
    "df_bmj['pubmed_results'] = ~df_bmj.pmid.isnull()\n",
    "df_bmj.index.name = 'nct_id'\n",
    "df_bmj.reset_index(inplace=True)\n",
    "print len(df_bmj), 'NCT IDs in the full BMJ dataset'\n",
    "# df_bmj.head(20)\n",
    "\n",
    "merged_results = \\\n",
    "    pd.merge(df_bmj, df_completed, #[['nct_id', 'pubmed_results']], \n",
    "             on='nct_id', how='inner', suffixes=('_bmj', '_ours'))\n",
    "    \n",
    "# NB I tried this first with a left join: but 1521 out of the 4500 papers \n",
    "# don't appear in our dataset, because the BMJ authors' inclusion criteria are \n",
    "# different from ours. To get a sample after a left join...\n",
    "# merged_results[merged_results.we_have_results.isnull()].head()\n",
    "\n",
    "merged_results['we_have_results'] = ~merged_results.is_overdue\n",
    "merged_results.we_have_results.value_counts(dropna=False)\n",
    "# merged_results.head()\n",
    "print len(merged_results), 'NCT IDs are in both the BMJ dataset and ours'\n",
    "\n",
    "papers_both_find_pm_results = \\\n",
    "    merged_results[merged_results.pubmed_results_bmj & merged_results.we_have_results]\n",
    "papers_both_find_pm_results.head()\n",
    "print len(papers_both_find_pm_results), 'we both find results for'\n",
    "papers_only_they_find_results = \\\n",
    "    merged_results[merged_results.pubmed_results_bmj & ~merged_results.we_have_results]\n",
    "print len(papers_only_they_find_results), 'only they find results for'  \n",
    "papers_only_we_find_results = \\\n",
    "    merged_results[~merged_results.pubmed_results_bmj & merged_results.we_have_results]\n",
    "print len(papers_only_we_find_results), 'only we find results for'\n",
    "noone_finds_results = \\\n",
    "    merged_results[~merged_results.pubmed_results_bmj & ~merged_results.we_have_results]\n",
    "print len(noone_finds_results), 'neither of us find results for'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nct_id</th>\n",
       "      <th>title</th>\n",
       "      <th>pubmed_results_bmj</th>\n",
       "      <th>pmid</th>\n",
       "      <th>we_have_results</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>758</th>\n",
       "      <td>NCT00324506</td>\n",
       "      <td>Safety and Efficacy of Cellcept and Avonex as ...</td>\n",
       "      <td>True</td>\n",
       "      <td>21180632</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>NCT00218491</td>\n",
       "      <td>Effectiveness of N-Acetylcysteine (NAC) in Tre...</td>\n",
       "      <td>True</td>\n",
       "      <td>23952889</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2436</th>\n",
       "      <td>NCT01220310</td>\n",
       "      <td>Doctors and Web-based Self-management Support ...</td>\n",
       "      <td>True</td>\n",
       "      <td>23031610</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1323</th>\n",
       "      <td>NCT00529841</td>\n",
       "      <td>Research Study for Children With Salt Wasting ...</td>\n",
       "      <td>True</td>\n",
       "      <td>16817818</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>332</th>\n",
       "      <td>NCT00145197</td>\n",
       "      <td>Reducing Underuse of Early-Stage Breast Cancer...</td>\n",
       "      <td>True</td>\n",
       "      <td>19033569</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>NCT00027703</td>\n",
       "      <td>Combination Chemotherapy With or Without Bevac...</td>\n",
       "      <td>True</td>\n",
       "      <td>22665541</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1755</th>\n",
       "      <td>NCT00662103</td>\n",
       "      <td>Aerobic Exercise, Resistance Exercise, or Flex...</td>\n",
       "      <td>True</td>\n",
       "      <td>20048529</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1719</th>\n",
       "      <td>NCT00646204</td>\n",
       "      <td>Namenda (Memantine) for Non-Motor Symptoms in ...</td>\n",
       "      <td>True</td>\n",
       "      <td>21193343</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1309</th>\n",
       "      <td>NCT00524966</td>\n",
       "      <td>Randomized Controlled Trial of Bipolar Versus ...</td>\n",
       "      <td>True</td>\n",
       "      <td>21072688</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>NCT00054418</td>\n",
       "      <td>Risedronate in Preventing Bone Loss in Premeno...</td>\n",
       "      <td>True</td>\n",
       "      <td>19075260</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           nct_id                                              title  \\\n",
       "758   NCT00324506  Safety and Efficacy of Cellcept and Avonex as ...   \n",
       "500   NCT00218491  Effectiveness of N-Acetylcysteine (NAC) in Tre...   \n",
       "2436  NCT01220310  Doctors and Web-based Self-management Support ...   \n",
       "1323  NCT00529841  Research Study for Children With Salt Wasting ...   \n",
       "332   NCT00145197  Reducing Underuse of Early-Stage Breast Cancer...   \n",
       "42    NCT00027703  Combination Chemotherapy With or Without Bevac...   \n",
       "1755  NCT00662103  Aerobic Exercise, Resistance Exercise, or Flex...   \n",
       "1719  NCT00646204  Namenda (Memantine) for Non-Motor Symptoms in ...   \n",
       "1309  NCT00524966  Randomized Controlled Trial of Bipolar Versus ...   \n",
       "93    NCT00054418  Risedronate in Preventing Bone Loss in Premeno...   \n",
       "\n",
       "     pubmed_results_bmj      pmid we_have_results  \n",
       "758                True  21180632           False  \n",
       "500                True  23952889           False  \n",
       "2436               True  23031610           False  \n",
       "1323               True  16817818           False  \n",
       "332                True  19033569           False  \n",
       "42                 True  22665541           False  \n",
       "1755               True  20048529           False  \n",
       "1719               True  21193343           False  \n",
       "1309               True  21072688           False  \n",
       "93                 True  19075260           False  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Examine a sample of the papers only they find results for.\n",
    "cols = ['nct_id', 'title', 'pubmed_results_bmj', 'pmid', 'we_have_results']\n",
    "papers_only_they_find_results.sample(10)[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "409 papers for which only we find results\n",
      "45 of those we find on PubMed, the rest on ClinicalTrials.gov\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nct_id</th>\n",
       "      <th>title</th>\n",
       "      <th>completion_date</th>\n",
       "      <th>pubmed_results_bmj</th>\n",
       "      <th>pmid</th>\n",
       "      <th>we_have_results</th>\n",
       "      <th>results_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1475</th>\n",
       "      <td>NCT00580528</td>\n",
       "      <td>Reducing Blood Pressure in Prehypertensive Old...</td>\n",
       "      <td>2010-05-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2195</th>\n",
       "      <td>NCT00925262</td>\n",
       "      <td>Controlled Trial of Mental Health Intervention...</td>\n",
       "      <td>2010-02-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>NCT00135668</td>\n",
       "      <td>Dose-Response Study of Sodium Nitroprusside in...</td>\n",
       "      <td>2008-03-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2514</th>\n",
       "      <td>NCT01668303</td>\n",
       "      <td>Family-Based Juvenile Drug Court Services</td>\n",
       "      <td>2009-11-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1197</th>\n",
       "      <td>NCT00480441</td>\n",
       "      <td>Effectiveness Study of Dronabinol and BRENDA f...</td>\n",
       "      <td>2009-06-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>NCT00133068</td>\n",
       "      <td>Collaboration to Reduce Disparities in Hyperte...</td>\n",
       "      <td>2008-08-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1932</th>\n",
       "      <td>NCT00739479</td>\n",
       "      <td>CCRC: Effects of Partially Hydrolyzed Whey Pep...</td>\n",
       "      <td>2010-08-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>NCT00079547</td>\n",
       "      <td>The Safety and Effectiveness of Low and High C...</td>\n",
       "      <td>2008-03-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460</th>\n",
       "      <td>NCT00206232</td>\n",
       "      <td>Novel Treatment for Diastolic Heart Failure in...</td>\n",
       "      <td>2010-07-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>NCT00276744</td>\n",
       "      <td>Individualized Drug Treatment Selection Proces...</td>\n",
       "      <td>2010-04-01</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           nct_id                                              title  \\\n",
       "1475  NCT00580528  Reducing Blood Pressure in Prehypertensive Old...   \n",
       "2195  NCT00925262  Controlled Trial of Mental Health Intervention...   \n",
       "315   NCT00135668  Dose-Response Study of Sodium Nitroprusside in...   \n",
       "2514  NCT01668303          Family-Based Juvenile Drug Court Services   \n",
       "1197  NCT00480441  Effectiveness Study of Dronabinol and BRENDA f...   \n",
       "306   NCT00133068  Collaboration to Reduce Disparities in Hyperte...   \n",
       "1932  NCT00739479  CCRC: Effects of Partially Hydrolyzed Whey Pep...   \n",
       "151   NCT00079547  The Safety and Effectiveness of Low and High C...   \n",
       "460   NCT00206232  Novel Treatment for Diastolic Heart Failure in...   \n",
       "645   NCT00276744  Individualized Drug Treatment Selection Proces...   \n",
       "\n",
       "     completion_date pubmed_results_bmj  pmid we_have_results results_date  \n",
       "1475      2010-05-01              False  None            True          NaT  \n",
       "2195      2010-02-01              False  None            True          NaT  \n",
       "315       2008-03-01              False  None            True          NaT  \n",
       "2514      2009-11-01              False  None            True          NaT  \n",
       "1197      2009-06-01              False  None            True          NaT  \n",
       "306       2008-08-01              False  None            True          NaT  \n",
       "1932      2010-08-01              False  None            True          NaT  \n",
       "151       2008-03-01              False  None            True          NaT  \n",
       "460       2010-07-01              False  None            True          NaT  \n",
       "645       2010-04-01              False  None            True          NaT  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Papers only we find results for. If the `results_date` field exists, it \n",
    "# means that the results are published on ClinicalTrials.gov. Otherwise \n",
    "# we found results on PubMed but they did not - perhaps because\n",
    "# it's been a couple of years since they did their search.\n",
    "# We find 43 papers on PubMed that the BMJ authors don't: \n",
    "print len(papers_only_we_find_results), 'papers for which only we find results'\n",
    "print len(papers_only_we_find_results[papers_only_we_find_results.results_date.isnull()]),\\\n",
    "    'of those we find on PubMed, the rest on ClinicalTrials.gov'\n",
    "cols = ['nct_id', 'title', 'completion_date', 'pubmed_results_bmj', \n",
    "        'pmid', 'we_have_results', 'results_date']\n",
    "# papers_only_we_find_results.sample(20)[cols]\n",
    "papers_only_we_find_results[papers_only_we_find_results.results_date.isnull()].sample(10)[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}