|
|
* Plan/Todo [2025-01-06]
|
|
|
Goal is to update the main images with more details, i.e. adding means
|
|
|
etc.
|
|
|
|
|
|
- get aact_db back up
|
|
|
- attach it to a "research" network
|
|
|
- restart rocker, attaching it to the same research network.
|
|
|
|
|
|
** NOTES
|
|
|
|
|
|
aact_db-restored-2024-11-27 didn't successfully restore. It is missing
|
|
|
all the important stuff.
|
|
|
|
|
|
Figured out why the restore was failing. My code to restore had a faulty
|
|
|
check to see if the DB was up and ready. Fixed that now.
|
|
|
|
|
|
Waiting for restore (manually triggered) to start. Then I should have
|
|
|
access to the table as needed.
|
|
|
|
|
|
It seems like I'm missing some data within a schema, specifically the
|
|
|
Formularies and their associated views.
|
|
|
|
|
|
My options are:
|
|
|
|
|
|
- search around for documentation or other stuff
|
|
|
- try to rebuild
|
|
|
|
|
|
my suspision is that I forgot to back it up. I think it is probably
|
|
|
worth looking for. - So I've been looking through my copy of
|
|
|
ClinicalTrialsDataProcessing, and have not found anything referencing
|
|
|
it. The formularies data is required for my analysis though. If I
|
|
|
remember correctly, I manually uploaded the USP datasets in DBeaver,
|
|
|
then created any views etc.
|
|
|
|
|
|
I think that I'll have to recreate it. This is going to be hard because
|
|
|
I'm not sure what it did. At least I created mildly informative table
|
|
|
names.
|
|
|
|
|
|
The tables/views I've identified are: -
|
|
|
=Formularies.nct_to_brands_through_uspdc=
|
|
|
|
|
|
It looks like I need to - import usp-dc dataset - link those drugs to
|
|
|
usp data - create a view that links those automatically - back it up. -
|
|
|
double check the data I get from the request.
|
|
|
|
|
|
The links will be through RXCUIs, and grouped on =USP Class= In effect,
|
|
|
for a given RXCUI, I want to get the list of RXCUI's which have the same
|
|
|
USP-DC class, and then be able to link back to brands.
|
|
|
|
|
|
This should have the following links: - RXCUI -> USP-DC category/class
|
|
|
pair - USP-DC category/class pair -> RXCUIs - RXUCIs -> competitors
|
|
|
|
|
|
Do I want to combine the USP-DC and UPS-MMG datasets? No, there is
|
|
|
enough difference in them that I don't want to have to handle it that
|
|
|
way.
|
|
|
|
|
|
I've been working on this in scripts/ConfiguringFormularies.sql
|
|
|
|
|
|
So what I've managed to do so far is export tables, backup the data.
|
|
|
|
|
|
I've got a version that connects trials to brand names, but there may be
|
|
|
more details to the connection than I thought. I'd like to check if I
|
|
|
need to filter anything or check if there are other ingredients etc that
|
|
|
I need to include. */I probably need to write some descriptions of all
|
|
|
the tables and views to put everything together. An ai would probably be
|
|
|
helpful in doing this./*
|
|
|
|
|
|
|
|
|
** Code snippets
|
|
|
#+begin_example
|
|
|
podman run \
|
|
|
-e POSTGRES_PASSWORD="${POSTGRES_PASSWORD}" \
|
|
|
-e POSTGRES_USER="${POSTGRES_USER}" \
|
|
|
-e POSTGRES_DB="${POSTGRES_DB}" \
|
|
|
--name "${CONTAINER_NAME}" \
|
|
|
--detach \
|
|
|
--network research-network \
|
|
|
--shm-size=512mb \
|
|
|
--volume ./backup/:/backup/ \
|
|
|
-p 5432:5432\
|
|
|
postgres:14-alpine
|
|
|
#+end_example
|
|
|
|
|
|
#+begin_example
|
|
|
function check_postgres {
|
|
|
podman exec -i "${POSTGRES_DB}" psql -h localhost -U "${POSTGRES_USER}" -d "${POSTGRES_DB}" -c '\q' > /dev/null 2>&1
|
|
|
}
|
|
|
#+end_example
|
|
|
|
|
|
** Notes at end of day
|
|
|
- was reasonably productive in getting stuff unblocked for finishing
|
|
|
JMP, which i'll need to do before I leave town next week.
|
|
|
|
|
|
|
|
|
** What I've got to do tomorrow
|
|
|
I've got a version that connects trials to brand names, but there may be
|
|
|
more details to the connection than I thought. I'd like to check if I
|
|
|
need to filter anything or check if there are other ingredients etc that
|
|
|
I need to include. */I probably need to write some descriptions of all
|
|
|
the tables and views to put everything together. An ai would probably be
|
|
|
helpful in doing this./* At the end of it all, I should be able to get a
|
|
|
count of competing drugs per trial.
|
|
|
|
|
|
Once that is done, I can relink aact_db and rocker, then rerun my
|
|
|
analysis. Then I can adjust the images that I need for my JMP.
|
|
|
* [2025-01-07 Tue 12:01] notes
|
|
|
|
|
|
So what I've got to do is
|
|
|
|
|
|
** DONE Investigate what compounds are showing up in my current list
|
|
|
if that is what I want, then I'll be able proceed with redoing my images
|
|
|
if not, then I'll have to work on adjusting the views etc that I have.
|
|
|
|
|
|
I've looked through it and it seems to correct.
|
|
|
|
|
|
*** [[/mnt/will/large_data/Research_large_data/ClinicalTrialsDataProcessing/Scripts/ConfiguringFormularies.sql][ConfiguringFormularies.sql:81]] [2025-01-07 Tue 13:24]
|
|
|
I've tweaked these three views to make them clearer.
|
|
|
I also renamed the view of interest to ="Formularies".nct_to_brand_counts_through_uspdc= to better represent what it does.
|
|
|
|
|
|
|
|
|
** DONE Rerun the analysis
|
|
|
CLOSED: [2025-01-07 Tue 16:39]
|
|
|
So it looks like I'll need to
|
|
|
1. take a backup of aact_db
|
|
|
2. restore from backup, putting the container in the research network
|
|
|
3. then rerun the analysis.
|
|
|
|
|
|
Ok, I'm pushing the backup and can get started on restoring from backup.
|
|
|
Backup is restoring. As I recall, this takes 40 minutes.
|
|
|
|
|
|
had some mild tweaks to match the new results
|
|
|
it now runs
|
|
|
|
|
|
** DONE Add more details to images
|
|
|
CLOSED: [2025-01-13 Mon 10:26]
|
|
|
The details I want to add include:
|
|
|
|
|
|
- [x] sample sizes for breakdowns
|
|
|
- [x] box and whisker plot along the bottom of the large values
|
|
|
[[https://claude.ai/chat/0e6b6368-130e-4aa8-aa16-97b6c937bba4]] has details
|