Clinical Trial Study count Figure

[bivek2003]

It would be nice to generate this Histogram plot from paper.-,FIGURE%201,-Clinical%20Trial%20study) for the Clinical Trial study counts by year in the landing page of the web app. I can think of using seaborn for doing this task let me know your suggestions of any other tools @Jack-42 @Enerep

[Jack-42]

Hey @bivek2003

I think that could be nice, since the DB is static we can probably just generate the plot using seaborn (like you say) and the have the PNG file as a static asset.

[bivek2003]

Hey @Jack-42 , @Enerep ,

I just found out the figure uses the AACT(Aggregate Analysis of ClinicalTrials.gov) to plot the relationships. So here is a good question: whether to use TICTAC_DB or the AACT?

If you are curious about drug studies vs year data, then here are the results from TICTAC_DB:

tictac_db=> SELECT
  EXTRACT(YEAR FROM s.start_date)::int AS start_year,
  COUNT(DISTINCT s.study_id)  AS n_studies,
  COUNT(DISTINCT dtsd.study_id || '_' || dtsd.drug_id::text) AS n_drugs
FROM core.study s
LEFT JOIN core.disease_target_study_drug dtsd ON s.study_id = dtsd.study_id
WHERE s.start_date IS NOT NULL
  AND EXTRACT(YEAR FROM s.start_date) BETWEEN 1990 AND 2023
GROUP BY EXTRACT(YEAR FROM s.start_date)
ORDER BY start_year;
 start_year | n_studies | n_drugs
------------+-----------+---------
       1990 |        94 |      49
       1991 |       129 |      39
       1992 |       165 |      95
       1993 |       205 |     210
       1994 |       229 |     197
       1995 |       333 |     345
       1996 |       397 |     368
       1997 |       556 |     511
       1998 |       734 |     714
       1999 |       986 |     828
       2000 |      1344 |     999
       2001 |      1635 |    1088
       2002 |      2075 |    1169
       2003 |      2955 |    1481
       2004 |      3774 |    1790
       2005 |      4726 |    2114
       2006 |      5555 |    2318
       2007 |      6156 |    2508
       2008 |      6918 |    2516
       2009 |      7619 |    2450
       2010 |      7881 |    2412
       2011 |      8231 |    2199
       2012 |      8799 |    2342
       2013 |      8968 |    2479
       2014 |     10040 |    2796
       2015 |     11180 |    3298
       2016 |     11827 |    3290
       2017 |     11788 |    3119
       2018 |     11575 |    2997
       2019 |     11204 |    3130
       2020 |     10869 |    3357
       2021 |     10271 |    3343
       2022 |      8716 |    2843
       2023 |      8377 |    2797

Actually, in the figure, it's already labeled "AACT studies," but I overlooked it. Please let me know which approach would be good.

[Jack-42]

Hey @bivek2003

TICTAC uses data from the AACT so the results should be similar - it's a little surprising that the numbers don't seem to line up with what is shown in the paper (particularly for drugs).

I wouldn't too much about this - let's check with Dr. Yang and Dr. Abok for their thoughts next week.

[bivek2003]

agreed.