In [1]:

    

%load_ext sql
import pandas as pd
import json
from __future__ import division


    

In [3]:

    

# sql connection parameters come from connect.py
have_connect = !ls connect.py 2>/dev/null
if len(have_connect) == 0:
    !mv ../../connect.py .
from connect import *

get_ipython().magic('sql mysql://' + mysqlusername + ':' + mysqlpassword + '@' + mysqlserver + ':3306/' + mysqldbname)
clinical_study = %sql select nct_id, overall_status, left(phase, 7) phase, enrollment, \
                        case when start_date != '' then year(start_date_dt) \
                             when completion_date != '' then year(completion_date_dt) \
                             else year(verification_date_dt) end study_year \
                      from clinical_study \
                      where study_type = 'Interventional'


    

    




123600 rows affected.

In [16]:

    

summary = [(i[4],i[1]) for i in clinical_study]
years = set([i[0] for i in summary])
statuses = set([i[1] for i in summary])
outjson = {str(int(y)): {s: summary.count((y,s)) for s in statuses if summary.count((y,s)) > 0} for y in years}


    

In [17]:

    

outjson


    

    
Out[17]:





{'1931': {'Completed': 1},
 '1960': {'Completed': 1},
 '1965': {'Completed': 1},
 '1966': {'Completed': 2},
 '1971': {'Active, not recruiting': 2, 'Completed': 2},
 '1972': {'Completed': 3},
 '1973': {'Completed': 3},
 '1974': {'Completed': 2},
 '1975': {'Completed': 1},
 '1976': {'Completed': 7},
 '1977': {'Completed': 8},
 '1978': {'Completed': 9, 'Recruiting': 1},
 '1979': {'Active, not recruiting': 3, 'Completed': 2},
 '1980': {'Completed': 4},
 '1981': {'Completed': 5},
 '1982': {'Active, not recruiting': 1,
  'Completed': 7,
  'Recruiting': 1,
  'Terminated': 1},
 '1983': {'Active, not recruiting': 1, 'Completed': 19},
 '1984': {'Completed': 16, 'Withdrawn': 1},
 '1985': {'Active, not recruiting': 1, 'Completed': 16, 'Suspended': 1},
 '1986': {'Active, not recruiting': 2, 'Completed': 19},
 '1987': {'Active, not recruiting': 3, 'Completed': 25, 'Terminated': 1},
 '1988': {'Active, not recruiting': 5,
  'Completed': 29,
  'Recruiting': 1,
  'Terminated': 2},
 '1989': {'Active, not recruiting': 6,
  'Completed': 83,
  'Recruiting': 3,
  'Terminated': 4},
 '1990': {'Active, not recruiting': 6,
  'Completed': 109,
  'Terminated': 3,
  'Withdrawn': 1},
 '1991': {'Active, not recruiting': 17,
  'Completed': 112,
  'Recruiting': 1,
  'Suspended': 1},
 '1992': {'Active, not recruiting': 13,
  'Completed': 160,
  'Recruiting': 2,
  'Terminated': 1},
 '1993': {'Active, not recruiting': 33,
  'Completed': 186,
  'Recruiting': 4,
  'Terminated': 2,
  'Withdrawn': 1},
 '1994': {'Active, not recruiting': 33,
  'Completed': 230,
  'Recruiting': 2,
  'Terminated': 4,
  'Withdrawn': 1},
 '1995': {'Active, not recruiting': 49,
  'Completed': 306,
  'Recruiting': 10,
  'Suspended': 2,
  'Terminated': 16},
 '1996': {'Active, not recruiting': 73,
  'Completed': 442,
  'Recruiting': 14,
  'Terminated': 21,
  'Withdrawn': 9},
 '1997': {'Active, not recruiting': 101,
  'Completed': 569,
  'Enrolling by invitation': 1,
  'Recruiting': 26,
  'Suspended': 3,
  'Terminated': 27,
  'Withdrawn': 4},
 '1998': {'Active, not recruiting': 188,
  'Completed': 789,
  'Recruiting': 22,
  'Suspended': 3,
  'Terminated': 39,
  'Withdrawn': 11},
 '1999': {'Active, not recruiting': 185,
  'Completed': 1073,
  'Recruiting': 38,
  'Suspended': 6,
  'Terminated': 68,
  'Withdrawn': 16},
 '2000': {'Active, not recruiting': 251,
  'Completed': 1395,
  'Enrolling by invitation': 2,
  'Not yet recruiting': 1,
  'Recruiting': 52,
  'Suspended': 2,
  'Terminated': 118,
  'Withdrawn': 11},
 '2001': {'Active, not recruiting': 264,
  'Completed': 1824,
  'Not yet recruiting': 2,
  'Recruiting': 93,
  'Suspended': 6,
  'Terminated': 148,
  'Withdrawn': 11},
 '2002': {'Active, not recruiting': 323,
  'Completed': 2800,
  'Enrolling by invitation': 1,
  'Not yet recruiting': 1,
  'Recruiting': 120,
  'Suspended': 11,
  'Terminated': 231,
  'Withdrawn': 23},
 '2003': {'Active, not recruiting': 447,
  'Completed': 3719,
  'Enrolling by invitation': 6,
  'Not yet recruiting': 2,
  'Recruiting': 219,
  'Suspended': 16,
  'Terminated': 329,
  'Withdrawn': 28},
 '2004': {'Active, not recruiting': 643,
  'Completed': 4734,
  'Enrolling by invitation': 6,
  'Not yet recruiting': 4,
  'Recruiting': 350,
  'Suspended': 30,
  'Terminated': 454,
  'Withdrawn': 41},
 '2005': {'Active, not recruiting': 767,
  'Completed': 5534,
  'Enrolling by invitation': 11,
  'Not yet recruiting': 36,
  'Recruiting': 629,
  'Suspended': 38,
  'Terminated': 657,
  'Withdrawn': 83},
 '2006': {'Active, not recruiting': 1001,
  'Completed': 6251,
  'Enrolling by invitation': 27,
  'Not yet recruiting': 86,
  'Recruiting': 856,
  'Suspended': 61,
  'Terminated': 853,
  'Withdrawn': 173},
 '2007': {'Active, not recruiting': 1242,
  'Completed': 6499,
  'Enrolling by invitation': 45,
  'Not yet recruiting': 134,
  'Recruiting': 1243,
  'Suspended': 57,
  'Terminated': 982,
  'Withdrawn': 192},
 '2008': {'Active, not recruiting': 1501,
  'Completed': 6680,
  'Enrolling by invitation': 105,
  'Not yet recruiting': 169,
  'Recruiting': 1803,
  'Suspended': 82,
  'Terminated': 987,
  'Withdrawn': 263},
 '2009': {'Active, not recruiting': 1764,
  'Completed': 6302,
  'Enrolling by invitation': 180,
  'Not yet recruiting': 294,
  'Recruiting': 2593,
  'Suspended': 85,
  'Terminated': 824,
  'Withdrawn': 290},
 '2010': {'Active, not recruiting': 1836,
  'Completed': 5351,
  'Enrolling by invitation': 218,
  'Not yet recruiting': 331,
  'Recruiting': 3807,
  'Suspended': 89,
  'Terminated': 682,
  'Withdrawn': 266},
 '2011': {'Active, not recruiting': 1676,
  'Completed': 4030,
  'Enrolling by invitation': 287,
  'Not yet recruiting': 594,
  'Recruiting': 5462,
  'Suspended': 87,
  'Terminated': 436,
  'Withdrawn': 240},
 '2012': {'Active, not recruiting': 1252,
  'Completed': 2176,
  'Enrolling by invitation': 324,
  'Not yet recruiting': 982,
  'Recruiting': 7494,
  'Suspended': 81,
  'Terminated': 181,
  'Withdrawn': 280},
 '2013': {'Active, not recruiting': 382,
  'Completed': 368,
  'Enrolling by invitation': 202,
  'Not yet recruiting': 3260,
  'Recruiting': 5384,
  'Suspended': 32,
  'Terminated': 23,
  'Withdrawn': 114},
 '2014': {'Active, not recruiting': 1,
  'Not yet recruiting': 195,
  'Suspended': 9,
  'Withdrawn': 10},
 '2015': {'Not yet recruiting': 9,
  'Recruiting': 1,
  'Suspended': 1,
  'Withdrawn': 1},
 '2016': {'Not yet recruiting': 7},
 '2017': {'Not yet recruiting': 2},
 '2018': {'Not yet recruiting': 1},
 '2021': {'Terminated': 1}}

In [18]:

    

statuses


    

    
Out[18]:





{'Active, not recruiting',
 'Completed',
 'Enrolling by invitation',
 'Not yet recruiting',
 'Recruiting',
 'Suspended',
 'Terminated',
 'Withdrawn'}

In [ ]:

    




    

In [ ]: