Clinical Trials Loading - Revision history

Enkhjargal at 22:40, 12 December 2017

2017-12-12T22:40:30Z

← Older revision		Revision as of 22:40, 12 December 2017
Line 1:		Line 1:
	1. Relevant Files:		Look at the /nfs/db/trials/HOWTO.Enkhee


			1. Relevant Files:
	/nfs/db/trials/important --> clinical trials raw data		/nfs/db/trials/important --> clinical trials raw data
	/nfs/home/teague/work/Projects/trials/extract.py --> script to clean the raw data		/nfs/home/teague/work/Projects/trials/extract.py --> script to clean the raw data

Enkhjargal at 17:34, 28 September 2017

2017-09-28T17:34:41Z

← Older revision		Revision as of 17:34, 28 September 2017
Line 149:		Line 149:
	insert into clinical2.ctphase select * from clinical1.ctphase;		insert into clinical2.ctphase select * from clinical1.ctphase;
	insert into clinical2.ct2condclass select * from clinical1.ct2condclass;		insert into clinical2.ct2condclass select * from clinical1.ct2condclass;



	4. Matching the name between subname.name and intervention.name		4. Matching the name between subname.name and intervention.name

			create temp table tempsubname
			as select sub_id_fk as sub_id_fk, who_name as name
			from catalog_item join catalog on (cat_id_fk=cat_id and short_name='chembl20')
			join chembl20.molecule_dictionary as md on supplier_code=md.chembl_id
			join chembl20.molecule_atc_classification as mac on md.molregno=mac.molregno
			join chembl20.atc_classification as ac on mac.level5=ac.level5;

	~~create temp table subname~~		insert into tempsubname
	~~as select sub_id_fk as sub_id_fk, who_name as name~~		select cs.sub_id_fk, sy.synonym
	~~from catalog_item join catalog on (cat_id_fk=cat_id and short_name='chembl20')~~		from catalog_substance as cs join synonym as sy on cs.cat_content_fk = sy.cat_content_fk
	~~join chembl20.molecule_dictionary as md on supplier_code=md.chembl_id~~		where not exists (select 1 from tempsubname as sn where sn.sub_id_fk = cs.sub_id_fk and sn.name = sy.synonym);
	~~join chembl20.molecule_atc_classification as mac on md.molregno=mac.molregno~~
	~~join chembl20.atc_classification as ac on mac.level5=ac.level5;~~		alter table tempsubname add column q tsquery;

	insert into ~~subname~~
	select cs.sub_id_fk, sy.synonym
	from catalog_substance as cs join synonym as s on cs.cat_content_fk = s.cat_content_fk
	where not exists (select 1 from ~~subname~~ as sn where sn.sub_id_fk = cs.sub_id_fk and sn.name = sy.synonym);

	~~alter table subname add column~~ q ~~tsquery~~;		update tempsubname as s
			set q=plainto_tsquery(t.name)
			from tempsubname as t
			where s.sub_id_fk=t.sub_id_fk and s.name=t.name;

	~~update table subname as s~~
	~~set q=plainto_tsquery(t.who_name)~~
	~~from subname as t~~
	~~where s.sub_id_fk=t.sub_id_fk and s.who_name=t.who_name;~~

	alter table clinical2.ct2int add column terms tsvector;		alter table clinical2.ct2int add column terms tsvector;
	update table clinical2.ct2int set term=to_tsvector('english', name)		update table clinical2.ct2int set term=to_tsvector('english', name)

			insert into clinical2.ct2subint (sub_id_fk, ct2int_fk)
			select distinct sub.sub_id_fk, int.ct2int_id
			from clinical2.ct2int as int join tempsubname as sub on
			int.terms@@sub.q
			where not exists (select 1 from clinical2.ct2int as int join tempsubname as sub on int.terms@@sub.q);

	~~insert into clinical2.ct2subint (sub_id_fk, ct2int_fk)~~
	~~select distinct sub.sub_id_fk, int.ct2int_id~~
	~~from clinical2.ct2int as int join subname as sub on~~
	~~int.terms@@sub.query;~~

	5. Warehousing		5. Warehousing

Enkhjargal at 16:23, 7 September 2017

2017-09-07T16:23:36Z

← Older revision		Revision as of 16:23, 7 September 2017
Line 181:		Line 181:
	from clinical2.ct2int as int join subname as sub on		from clinical2.ct2int as int join subname as sub on
	int.terms@@sub.query;		int.terms@@sub.query;

			5. Warehousing

			update clinical2.ct2int as ct2int set num_trials = (select
			count(*) from clinical2.ct2toint as ct2toint where
			ct2int.ct2int_id = ct2toint.ct2int_fk);

			update clinical2.ct2int as ct2int set num_substances = (select
			count(*) from clinical2.ct2subint as ct2subint where
			ct2int.ct2int_id = ct2subint.ct2int_fk);

			update clinical2.ct2condition as ct2cond set num_trials = (select
			count(*) from clinical2.ct2tocond as ct2tocond where
			ct2cond.ct2condition_id = ct2tocond.ct2condition_fk);

			update clinical2.ct2condition as ct2cond set num_substances = (select
			count(distinct(ct2subint.sub_id_fk)) from clinical2.ct2tocond as ct2tocond, clinical2.ct2toint as ct2toint, clinical2.ct2subint as ct2subint where
			ct2cond.ct2condition_id = ct2tocond.ct2condition_fk and ct2tocond.ct2_fk = ct2toint.ct2_fk and ct2toint.ct2int_fk=ct2subint.ct2int_fk);

Enkhjargal at 20:18, 24 August 2017

2017-08-24T20:18:50Z

Show changes

Enkhjargal at 19:58, 24 August 2017

2017-08-24T19:58:29Z

← Older revision		Revision as of 19:58, 24 August 2017
Line 6:		Line 6:
	2. SQL queries to create a new clinical trial schema and table are located in zinc code:		2. SQL queries to create a new clinical trial schema and table are located in zinc code:

	SQL_statement/clinical_trial		zinc/SQL_statement/clinical_trial.sql

	3. To load the data ~~from raw data:~~		3. Load raw data to the database tables:
			If you want to delete all the existing data, then use --wipe
			Otherwise it will load data incrementally to the existing data.

			source /nfs/soft/www/apps/zinc15/envs/dev/bin/activate
			python /nfs/soft/www/apps/zinc15/envs/dev/bin/zinc-manage utils clinical-trials load_ct /nfs/db/trials/important/studies.txt
			python /nfs/soft/www/apps/zinc15/envs/dev/bin/zinc-manage utils clinical-trials load_condition /nfs/db/trials/important/browse_conditions.txt
			python /nfs/soft/www/apps/zinc15/envs/dev/bin/zinc-manage utils clinical-trials load_intervention /nfs/db/trials/important/interventions.txt

Enkhjargal at 19:03, 24 August 2017

2017-08-24T19:03:23Z

← Older revision		Revision as of 19:03, 24 August 2017
Line 4:		Line 4:
	http://wiki.docking.org/index.php/Creating_clinical_name_mappings --> how to create name_mappings		http://wiki.docking.org/index.php/Creating_clinical_name_mappings --> how to create name_mappings

	2.		2. SQL queries to create a new clinical trial schema and table are located in zinc code:

			SQL_statement/clinical_trial

			3. To load the data from raw data:

Enkhjargal: Created page with " 1. Relevant Files: /nfs/db/trials/important --> clinical trials raw data /nfs/home/teague/work/Projects/trials/extract.py --> script to clean the raw data http:/..."

2017-08-17T19:04:07Z

Created page with " 1. Relevant Files: /nfs/db/trials/important --> clinical trials raw data /nfs/home/teague/work/Projects/trials/extract.py --> script to clean the raw data http:/..."

New page

1. Relevant Files:
/nfs/db/trials/important --> clinical trials raw data
/nfs/home/teague/work/Projects/trials/extract.py --> script to clean the raw data
http://wiki.docking.org/index.php/Creating_clinical_name_mappings --> how to create name_mappings

2.