\\n\"+\n", " \"BokehJS does not appear to have successfully loaded. If loading BokehJS from CDN, this \\n\"+\n", " \"may be due to a slow or bad network connection. Possible fixes:\\n\"+\n", " \"
\\n\"+\n", " \"\\n\"+\n",
" \"from bokeh.resources import INLINE\\n\"+\n",
" \"output_notebook(resources=INLINE)\\n\"+\n",
" \"\\n\"+\n",
" \"\\n\"+\n \"BokehJS does not appear to have successfully loaded. If loading BokehJS from CDN, this \\n\"+\n \"may be due to a slow or bad network connection. Possible fixes:\\n\"+\n \"
\\n\"+\n \"\\n\"+\n \"from bokeh.resources import INLINE\\n\"+\n \"output_notebook(resources=INLINE)\\n\"+\n \"\\n\"+\n \"| \n", " | REPORT_ID | \n", "REPORT_NO | \n", "VERSION_NO | \n", "DATRECEIVED | \n", "DATINTRECEIVED | \n", "MAH_NO | \n", "REPORT_TYPE_CODE | \n", "REPORT_TYPE_ENG | \n", "REPORT_TYPE_FR | \n", "GENDER_CODE | \n", "... | \n", "HOSP_REQUIRED | \n", "OTHER_MEDICALLY_IMP_COND | \n", "REPORTER_TYPE_ENG | \n", "REPORTER_TYPE_FR | \n", "SOURCE_CODE | \n", "SOURCE_ENG | \n", "SOURCE_FR | \n", "E2B_IMP_SAFETYREPORT_ID | \n", "AUTHORITY_NUMB | \n", "COMPANY_NUMB | \n", "
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | \n", "1 | \n", "000000001 | \n", "0 | \n", "05-JUN-73 | \n", "05-JUN-73 | \n", "NaN | \n", "7.0 | \n", "Spontaneous | \n", "Déclaration spontanée | \n", "2.0 | \n", "... | \n", "NaN | \n", "NaN | \n", "Physician | \n", "Physicien | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "
| 1 | \n", "2 | \n", "000000002 | \n", "0 | \n", "05-JUN-73 | \n", "05-JUN-73 | \n", "NaN | \n", "7.0 | \n", "Spontaneous | \n", "Déclaration spontanée | \n", "2.0 | \n", "... | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "3.0 | \n", "Hospital | \n", "Hôpital | \n", "NaN | \n", "NaN | \n", "NaN | \n", "
| 2 | \n", "3 | \n", "000000003 | \n", "0 | \n", "05-JUN-73 | \n", "05-JUN-73 | \n", "NaN | \n", "7.0 | \n", "Spontaneous | \n", "Déclaration spontanée | \n", "1.0 | \n", "... | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "3.0 | \n", "Hospital | \n", "Hôpital | \n", "NaN | \n", "NaN | \n", "NaN | \n", "
3 rows × 42 columns
\n", "CategoricalColumnTransformer from our Vectorizers library to construct a new pandas Series with the index being our objects and the values being the corresponding list of categorical values associated with each object. Notionaly, we've decided to say that two objects are similar if they match on many of these categorical values. \n",
"\n",
"Since we're combining categorical values from multiple columns we should also append the column name to each categorical value in order to be sure to not confuse things. We'll set include_column_name=True in order to have our categorical descriptors take the form of the string f'{column_name}:{value}'."
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 57.3 s, sys: 540 ms, total: 57.8 s\n",
"Wall time: 58.1 s\n"
]
},
{
"data": {
"text/plain": [
"1019799"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%time\n",
"report_list = CategoricalColumnTransformer(object_column_name='REPORT_ID',\n",
" descriptor_column_name=['GENDER_ENG','OUTCOME_ENG'],\n",
" include_column_name=True).fit_transform(data)\n",
"len(report_list)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Since we had one REPORT_ID for every row in our data frame we now a series of the same length with a list of at most two descriptors assicated with each."
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"REPORT_ID\n",
"1 [GENDER_ENG:Female, OUTCOME_ENG:Unknown]\n",
"2 [GENDER_ENG:Female, OUTCOME_ENG:Unknown]\n",
"3 [GENDER_ENG:Male, OUTCOME_ENG:Unknown]\n",
"dtype: object"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"report_list.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"2 967530\n",
"1 52267\n",
"0 2\n",
"Name: count, dtype: int64"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"report_list.apply(len).value_counts()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We implicitely filter all categorical values that are Null. As we see here, this can result in smaller or empty lists. Around 46,000 records only had one of our fields present and 2 of our records had neither field present."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Enrich with reactions\n",
"\n",
"In order to find out what the reports we are interested in are actually about we need to enrich this data with the adverse reaction being reported. This information comes from the reactions table which contains just over 3 million reactions associated with our reports. REPORT_ID is also contained in this table so it can also be used to describe our objects of interest, but now we will describe our reports as a list of the categorical values contained within the PT_NAME_ENG column. This is the Adverse Reaction Term column. "
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(3866373, 10)"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"data = pd.read_csv(f'{file_path}/reactions.txt', sep='$', header=None, low_memory=False)\n",
"column_names = 'REACTION_ID,REPORT_ID,DURATION,DURATION_UNIT_ENG,DURATION_UNIT_FR,PT_NAME_ENG,PT_NAME_FR,SOC_NAME_ENG,SOC_NAME_FR,MEDDRA_VERSION'.split(',')\n",
"data.columns = column_names\n",
"data.shape"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Of note here, we have more rows than objects of interest. That is because a single report can be responsible for multiple reactions. That is going to necessitate grouping up the rows with the same REPORT_ID we can do this with a pandas groupby manually but this is exactly what our CategoricalColumnTransformer is doing under the hood. As such, just as before, we simply specify our object_column_name and the columns from this table that we'd like to use as descriptors and apply our transformer."
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 37.1 s, sys: 1.27 s, total: 38.4 s\n",
"Wall time: 39.1 s\n"
]
},
{
"data": {
"text/plain": [
"1019212"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%time\n",
"reaction_list = CategoricalColumnTransformer(object_column_name='REPORT_ID',\n",
" descriptor_column_name='PT_NAME_ENG',\n",
" include_column_name=True).fit_transform(data)\n",
"len(reaction_list)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now, just as before, we have a series with an index that is REPORT_ID and values that are lists of descriptors (strings)."
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"REPORT_ID\n",
"1 [PT_NAME_ENG:Pruritus, PT_NAME_ENG:Oedema]\n",
"2 [PT_NAME_ENG:Urticaria]\n",
"3 [PT_NAME_ENG:Erythema, PT_NAME_ENG:Periorbital...\n",
"dtype: object"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"reaction_list.head(3)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Drug ingredient data"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Lastly, we will include some categorical columns to describe the drug being reported on. This will give us the oportunity to see how we might make use a pair of linked tables to enrich our data further. It will also give us enough descriptors to induce an interesting notion of similarity on our data. Similarity measures based on very few categorical values will end up with a tremendous number of records at exactly the same distance making for an uninteresting metric space that is also difficult to work with.\n",
"\n",
"To do this we will enrich our reports with the name of the drug involved in the report and the active ingredients which make up that drug. We've included the active ingredients in this notion to because there are a wide variety of drugs with different names that contain the same drug. For example, there are over three hundred different drugs who's only active ingredient is listed as cannabis. We'd like our notion of distance to treat reports about these drugs as being similar. \n",
"\n",
"As before, we load the table that contains our active ingredients for each drug we know about."
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(354757, 5)"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"input_file = f'{file_path}/drug_product_ingredients.txt'\n",
"data = pd.read_csv(input_file, sep='$', header=None, low_memory=False)\n",
"column_names = 'DRUG_PRODUCT_INGREDIENT_ID,DRUG_PRODUCT_ID,DRUGNAME,ACTIVE_INGREDIENT_ID,ACTIVE_INGREDIENT_NAME'.split(\",\")\n",
"data.columns = column_names\n",
"data.shape"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The first thing we notice is that this table has no concept of reports or a REPORT_ID. In order to get this information we will have to load a another table which contains this linking information. Since we only want to use this table to link our drug ids with our report ids we will only load the two columns of interest from our file."
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(4280493, 2)"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"input_file = f'{file_path}/report_drug.txt'\n",
"drug2report_joining = pd.read_csv(input_file, sep='$', header=None, low_memory=False, usecols=[1,2])\n",
"drug2report_joining.columns = 'REPORT_ID,DRUG_PRODUCT_ID'.split(',')\n",
"drug2report_joining.shape"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now we make use of pandas excellent merge function to perform an inner join between this data. This will create a new DataFrame that contains both our ingredient lists as well as the reports which their corresponding drugs were contained in. \n",
"\n",
"It should be noted that this is a much larger table due to the fact any given drug may have many reports associated with it."
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(21606832, 6)"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"merged_data = data.merge(drug2report_joining, how='inner', on='DRUG_PRODUCT_ID')\n",
"merged_data.shape"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Lastly, we deploy our CategoricalColumnTransformer in order to reduce this data to an pandas Series containing lists of our categorical column values of interest and indexed by our column of interest, REPORT_ID. We've included unique_values=True in this transformer to ensure that any report that potentially contains multiple drugs with overlapping ingredients is only represented by the unique set of ingredients across all the drugs involved."
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 2min 41s, sys: 5.55 s, total: 2min 46s\n",
"Wall time: 2min 48s\n"
]
},
{
"data": {
"text/plain": [
"1016353"
]
},
"execution_count": 23,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%time\n",
"report_drug_ingredient_list = CategoricalColumnTransformer(object_column_name='REPORT_ID',\n",
" descriptor_column_name=['DRUGNAME','ACTIVE_INGREDIENT_NAME'],\n",
" include_column_name=True, unique_values=True).fit_transform(merged_data)\n",
"len(report_drug_ingredient_list)"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"REPORT_ID\n",
"1 [DRUGNAME:GANTRISIN, ACTIVE_INGREDIENT_NAME:su...\n",
"2 [DRUGNAME:PENBRITIN, ACTIVE_INGREDIENT_NAME:am...\n",
"3 [DRUGNAME:NEOSPORIN OINTMENT, ACTIVE_INGREDIEN...\n",
"dtype: object"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"report_drug_ingredient_list.head(3)"
]
},
{
"cell_type": "code",
"execution_count": 25,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"2 101065\n",
"3 97990\n",
"4 54916\n",
"5 56476\n",
"6 33446\n",
" ... \n",
"727 1\n",
"796 1\n",
"817 1\n",
"903 1\n",
"962 1\n",
"Name: count, Length: 446, dtype: int64"
]
},
"execution_count": 25,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"report_drug_ingredient_list.apply(len).value_counts().sort_index()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We see that no report contains less than two tokens in our new representation. One from its DRUGNAME and one from its ACTIVE_INGREDIENT. In other words all drugs have both a name and at least one active ingredient. On the other end of the spectrum there is a drug that has been reported on that contain closer to 814 active ingredients. \n",
"\n",
"That last statement should be taken with a grain of salt since in this particular data set various synonyms for drug ingredients are sometimes recorded multiple times for a given drug. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Combine our series\n",
"\n",
"Now we have three pandas Series each indexed by REPORT_ID and containing a list of strings representing our categorical values.\n",
"\n",
"Since these series all share an index we can use pandas concat to join them together. For those familiar with database terminology this can be done via either an outer or inner join. An outer join says that if an object is present in either series index it will be included in our results. An inner join requires the index to be present in all of our series.\n",
"\n",
"Outer joins may introduce NaN into our newly joined_df if some of our index values aren't present in all series. Since pandas doesn't allow us to fillna with lists we would have to do this manually ourselves via the commented code included below. In this case we are are performing an inner join since we only care about reports that have both symptoms and drugs associated with them."
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"1015830 records joined\n"
]
},
{
"data": {
"text/html": [
"| \n", " | 0 | \n", "1 | \n", "2 | \n", "
|---|---|---|---|
| REPORT_ID | \n", "\n", " | \n", " | \n", " |
| 1 | \n", "[GENDER_ENG:Female, OUTCOME_ENG:Unknown] | \n", "[PT_NAME_ENG:Pruritus, PT_NAME_ENG:Oedema] | \n", "[DRUGNAME:GANTRISIN, ACTIVE_INGREDIENT_NAME:su... | \n", "
| 2 | \n", "[GENDER_ENG:Female, OUTCOME_ENG:Unknown] | \n", "[PT_NAME_ENG:Urticaria] | \n", "[DRUGNAME:PENBRITIN, ACTIVE_INGREDIENT_NAME:am... | \n", "
| 3 | \n", "[GENDER_ENG:Male, OUTCOME_ENG:Unknown] | \n", "[PT_NAME_ENG:Erythema, PT_NAME_ENG:Periorbital... | \n", "[DRUGNAME:NEOSPORIN OINTMENT, ACTIVE_INGREDIEN... | \n", "
REPORT_ID."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We'd now like to combine all the lists in the columns of this data frame into one big list per REPORT_ID. To do this we will take advantage of the fact that python lists have overloaded the addition opperator with concatination. Adding two lists together just merges them in exactly the way we'd like. As such, what we'd like to do with our dataframe is just sum up all the values (i.e. lists) in every row. Adding two Series (i.e. columns) together just performs elementwise addition on all their elements."
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {},
"outputs": [],
"source": [
"joined_report = joined_df[0] + joined_df[1] + joined_df[2]"
]
},
{
"cell_type": "code",
"execution_count": 28,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"REPORT_ID\n",
"1 [GENDER_ENG:Female, OUTCOME_ENG:Unknown, PT_NA...\n",
"2 [GENDER_ENG:Female, OUTCOME_ENG:Unknown, PT_NA...\n",
"3 [GENDER_ENG:Male, OUTCOME_ENG:Unknown, PT_NAME...\n",
"dtype: object"
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"joined_report.head(3)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Here is a quick summary of the distribution of how many reports have however many tokens. You see that many of our reports have between 4 and 20 tokens while one of them has as many as 825 tokens associated with it. As we saw earlier this large list is mostly made up of active drug ingredients."
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"4 5133\n",
"5 49422\n",
"6 62134\n",
"7 57920\n",
"8 56035\n",
" ... \n",
"736 1\n",
"802 1\n",
"827 1\n",
"952 1\n",
"1021 1\n",
"Name: count, Length: 462, dtype: int64"
]
},
"execution_count": 29,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"joined_report.apply(len).value_counts().sort_index()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now we have a series with one entry for each REPORT_ID and a value that is a list of all the categorical descriptors we've derived from multiple columns across multiple tables. \n",
"\n",
"In the above analysis we have constructed our reprsentation via joining multiple seperate CategoricalColumnTransformer operations. Alternatively, we could have done this by building one large table containing all the columns of interest to us and then applying our CategoricalColumnTransformer to that single larger table. It is generally preferable to reduce the size of our tables via grouping and filtering before joining them together which is why we went with the approach we have here. That said, depending on your data base and compute access you could easily take a different approach. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Ngram Vectorizer\n",
"\n",
"In order to quickly and efficiently compare variable length sequences of categorical tokens we will convert into a sparse matrix format. This is a commonly practice in natural language processing where documents are often described as a 'bag of words'. In essence we have a single column for each unique categorical value in our list. That column has a one in it if that categorical value is present in our list. The sparse representation simply means that we don't encode any of the multitude of zeroes that would otherwise be contained in this matrix. \n",
"\n",
"We will do this with our NgramVectorizer. This is the simplest use case for our Ngram vectorizer with the default value of N=1. "
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {},
"outputs": [],
"source": [
"record_id_labels = list(joined_report.index)"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"ngram is now a