<commit_before>------------------------------
-- map all unique case drug drugnames to rxnorm Vocabulary concept_ids
--
-- we will include non-standard and standard codes so we pick up brand names as well as ingredients etc
-- and roll-up to standard codes when we produce the statistics in a later process.
--
-- we map using the following precedence order.
--
-- regex drug name mapping
-- active ingredient drug name mapping (only current FAERS data has active ingredient)
-- nda drug_name mapping
-- manual usagi drug name mapping
--
-- Note. We map all drug roles including concomitant drugs
--
-- LTS COMPUTING LLC
------------------------------

-- temporarily create an index on the cdmv5 schema concept table to improve performance of all the mapping lookups
-- we will then drop it at the end of this script
set search_path = cdmv5;
drop index if exists vocab_concept_name_ix;
create index vocab_concept_name_ix on cdmv5.concept(vocabulary_id, standard_concept, upper(concept_name), concept_id);
analyze verbose cdmv5.concept;

set search_path = faers;

-- build a mapping table to generate a cleaned up version of the drugname for exact match joins to the concept table concept_name column 
-- for RxNorm concepts only 
-- NOTE we join to unique_all_case because we only need to map drugs for unique cases 
-- ie. where there are multiple versions of cases we only process the case with the latest (max) caseversion)

drop table if exists drug_regex_mapping;
create table drug_regex_mapping as
select distinct drug_name_original, drug_name_clean, concept_id, update_method
from (
	select distinct drugname as drug_name_original, upper(drugname) as drug_name_clean, cast(null as integer) as concept_id, null as update_method
	from drug a
	inner join unique_all_case b on a.primaryid = b.primaryid
	where b.isr is null
	union all
	select distinct drugname as drug_name_original, upper(drugname) as drug_name_clean, cast(null as integer) as concept_id, null as update_method
	from drug_legacy a
	inner join unique_all_case b on a.isr = b.isr
	where b.isr is not null
) aa

-- remove the word tablet or "(tablet)" or the plural forms from drug name
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '(.*)(\W|^)\(TABLETS?\)|TABLETS?(\W|$)', '\1\2', 'gi') 
where concept_id is null
and drug_name_clean ~*  '.*TABLET.*'

-- remove the word capsule or (capsule) or the plural forms from drug name
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '(.*)(\W|^)\(CAPSULES?\)|CAPSULES?(\W|$)', '\1\2', 'gi')
where concept_id is null
and drug_name_clean ~*  '.*CAPSULE.*'

-- remove the drug strength in MG or MG/MG or MG\MG or MG / MG and their plural forms
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '\(*(\y\d*\.*\d*\ *MG\,*\ *\/*\\*\ *\d*\.*\d*\ *(M2|ML)*\ *\,*\+*\ *\y)\)*', '\3', 'gi')
where concept_id is null
and drug_name_clean ~*  '\(*(\y\d*\.*\d*\ *MG\,*\ *\/*\\*\ *\d*\.*\d*\ *(M2|ML)*\ *\,*\+*\ *\y)\)*'

-- remove the drug strength in MILLIGRAMS or MILLIGRAMS/MILLILITERS or MILLIGRAMS\MILLIGRAM and their plural forms
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '\(*(\y\d*\.*\d*\ *MILLIGRAMS?\,*\ *\/*\\*\ *\d*\.*\d*\ *(M2|MILLILITERS?)*\ *\,*\+*\ *\y)\)*', '\3', 'gi')
where concept_id is null
and drug_name_clean ~*  '\(*(\y\d*\.*\d*\ *MILLIGRAMS?\,*\ *\/*\\*\ *\d*\.*\d*\ *(M2|MILLILITERS?)*\ *\,*\+*\ *\y)\)*'

-- create an index on the mapping table to improve performance
drop index if exists drug_name_clean_ix;
create index drug_name_clean_ix on drug_regex_mapping(drug_name_clean);

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex upper' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean;

-- remove trailing spaces or period characters
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '[ \.]$', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex trailing space or period chars' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove multiple occurrences of white space '
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '(\S) +', '\1 ', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove multiple white space' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove trailing spaces
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, ' +$', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove trailing spaces' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove leading spaces
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '^ +', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove leading spaces' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove single quotes and double quotes'
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '[''""]', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove single quotes' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove '^*$?' chars
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '[\*\^\$\?]', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove ^*$? punctuation chars' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- change \ to / char
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '\\', '/', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex change forward slash to back slash' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove spaces before closing parenthesis char
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, ' +\)', ')', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove spaces before closing parenthesis' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove (UNKNOWN)
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '( *unknown *)', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove (unknown)' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove BLINDED
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, ' *blinded *', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove blinded' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'<commit_msg>additional regex drug mappings<commit_after>------------------------------
-- map all unique case drug drugnames to rxnorm Vocabulary concept_ids
--
-- we will include non-standard and standard codes so we pick up brand names as well as ingredients etc
-- and roll-up to standard codes when we produce the statistics in a later process.
--
-- we map using the following precedence order.
--
-- regex drug name mapping
-- active ingredient drug name mapping (only current FAERS data has active ingredient)
-- nda drug_name mapping
-- manual usagi drug name mapping
--
-- Note. We map all drug roles including concomitant drugs
--
-- LTS COMPUTING LLC
------------------------------

-- temporarily create an index on the cdmv5 schema concept table to improve performance of all the mapping lookups
-- we will then drop it at the end of this script
set search_path = cdmv5;
drop index if exists vocab_concept_name_ix;
create index vocab_concept_name_ix on cdmv5.concept(vocabulary_id, standard_concept, upper(concept_name), concept_id);
analyze verbose cdmv5.concept;

set search_path = faers;

-- build a mapping table to generate a cleaned up version of the drugname for exact match joins to the concept table concept_name column 
-- for RxNorm concepts only 
-- NOTE we join to unique_all_case because we only need to map drugs for unique cases 
-- ie. where there are multiple versions of cases we only process the case with the latest (max) caseversion)

drop table if exists drug_regex_mapping;
create table drug_regex_mapping as
select distinct drug_name_original, drug_name_clean, concept_id, update_method
from (
	select distinct drugname as drug_name_original, upper(drugname) as drug_name_clean, cast(null as integer) as concept_id, null as update_method
	from drug a
	inner join unique_all_case b on a.primaryid = b.primaryid
	where b.isr is null
	union all
	select distinct drugname as drug_name_original, upper(drugname) as drug_name_clean, cast(null as integer) as concept_id, null as update_method
	from drug_legacy a
	inner join unique_all_case b on a.isr = b.isr
	where b.isr is not null
) aa

-- create an index on the mapping table to improve performance
drop index if exists drug_name_clean_ix;
create index drug_name_clean_ix on drug_regex_mapping(drug_name_clean);

-- remove the word tablet or "(tablet)" or the plural forms from drug name
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '(.*)(\W|^)\(TABLETS?\)|TABLETS?(\W|$)', '\1\2', 'gi') 
where concept_id is null
and drug_name_clean ~*  '.*TABLET.*'

-- remove the word capsule or (capsule) or the plural forms from drug name
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '(.*)(\W|^)\(CAPSULES?\)|CAPSULES?(\W|$)', '\1\2', 'gi')
where concept_id is null
and drug_name_clean ~*  '.*CAPSULE.*'

-- remove the drug strength in MG or MG/MG or MG\MG or MG / MG and their plural forms
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '\(*(\y\d*\.*\d*\ *MG\,*\ *\/*\\*\ *\d*\.*\d*\ *(M2|ML)*\ *\,*\+*\ *\y)\)*', '\3', 'gi')
where concept_id is null
and drug_name_clean ~*  '\(*(\y\d*\.*\d*\ *MG\,*\ *\/*\\*\ *\d*\.*\d*\ *(M2|ML)*\ *\,*\+*\ *\y)\)*'

-- remove the drug strength in MILLIGRAMS or MILLIGRAMS/MILLILITERS or MILLIGRAMS\MILLIGRAM and their plural forms
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '\(*(\y\d*\.*\d*\ *MILLIGRAMS?\,*\ *\/*\\*\ *\d*\.*\d*\ *(M2|MILLILITERS?)*\ *\,*\+*\ *\y)\)*', '\3', 'gi')
where concept_id is null
and drug_name_clean ~*  '\(*(\y\d*\.*\d*\ *MILLIGRAMS?\,*\ *\/*\\*\ *\d*\.*\d*\ *(M2|MILLILITERS?)*\ *\,*\+*\ *\y)\)*'

-- remove HYDROCHLORIDE and HCL
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '(\y\ *(HCL|HYDROCHLORIDE)\y)', '\3', 'gi') 
where concept_id is null
and drug_name_clean ~*  '\(*(\y\ *(HCL|HYDROCHLORIDE)\ *\y)\)*'

-- remove FORMULATION, GENERIC, NOS
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '\(\y(FORMULATION|GENERIC|NOS)\y\)|\y(FORMULATION|GENERIC|NOS)\y', '\3', 'gi')  
where concept_id is null
and drug_name_clean ~*  '\y(FORMULATION|GENERIC|NOS)\y'

-- lookup RxNorm concept name using words from last set of parentheses in the drug name (typically this is the ingredient name(s) for a branded drug
UPDATE drug_regex_mapping a
SET update_method = 'regex ingredient name in parentheses' , concept_id = b.concept_id, drug_name_clean = upper(b.concept_name)
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = regexp_replace(a.drug_name_clean, '.* \((.*)\)', '\1', 'gi')
AND a.concept_id is null
and drug_name_clean ~*  '.* \((.*)\)';

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex upper' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean;

-- remove trailing spaces or period or , characters
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '[ \.\,]$', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex trailing space or period chars' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove multiple occurrences of white space '
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '(\S) +', '\1 ', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove multiple white space' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove trailing spaces
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, ' +$', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove trailing spaces' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove leading spaces
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '^ +', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove leading spaces' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove single quotes and double quotes'
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '[''""]', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove single quotes' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove '^*$?' chars
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '[\*\^\$\?]', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove ^*$? punctuation chars' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- change \ to / char
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '\\', '/', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex change forward slash to back slash' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove spaces before closing parenthesis char
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, ' +\)', ')', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove spaces before closing parenthesis' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove UNKNOWN or UNK 
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, '\(\y(UNKNOWN|UNK)\y\)|\y(UNKNOWN|UNK)\y', '', 'gi')  
where concept_id is null
and drug_name_clean ~*  '\y(UNKNOWN|UNK)\y'

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove (unknown)' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
AND upper(b.concept_name) = a.drug_name_clean
and a.concept_id is null;

-- remove BLINDED
update drug_regex_mapping
set drug_name_clean = regexp_replace(drug_name_clean, ' *blinded *', '', 'gi')
where concept_id is null;

-- find exact mapping
UPDATE drug_regex_mapping a
SET update_method = 'regex remove blinded' , concept_id = b.concept_id
FROM cdmv5.concept b
WHERE b.vocabulary_id = 'RxNorm'
{'fraction_non_alphanumeric': 0.12143358278765201, 'fraction_numerical': 0.003274087932647334, 'mean_word_length': 4.560793237971391, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5385014', 'n_tokens_mistral': 5937, 'n_tokens_neox': 5662, 'n_words': 1831}