API_Drug_Data_Extract.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import math\n",
    "import os\n",
    "import re\n",
    "import random\n",
    "\n",
    "import urllib, json\n",
    "import plotly.express as px"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def json_to_df_results(url):\n",
    "    \"\"\" get flat pandas table from 'results' in json url\"\"\"\n",
    "    \n",
    "    response = urllib.request.urlopen(url)\n",
    "    data = json.loads(response.read())\n",
    "\n",
    "    #normalize json into flat table\n",
    "    df = pd.json_normalize(data['results'])\n",
    "    \n",
    "    return df\n",
    "\n",
    "def json_to_df_meta(url):\n",
    "    \"\"\" get flat pandas table from 'results' in json url\"\"\"\n",
    "    \n",
    "    response = urllib.request.urlopen(url)\n",
    "    data = json.loads(response.read())\n",
    "\n",
    "    #normalize json into flat table\n",
    "    df = pd.json_normalize(data['meta'])\n",
    "    return df\n",
    "\n",
    "def get_histogram(df,title):\n",
    "    \"\"\" plot data in df as histogram (where x is 'term', y is 'count'), title must be string \"\"\"\n",
    "    \n",
    "    fig = px.histogram(df, x='term',y='count', title=title)\n",
    "    fig.update_layout(xaxis_title=None)\n",
    "    \n",
    "    return fig\n",
    "\n",
    "def get_pie(df,title):\n",
    "    \"\"\" plot data in df as pie chart (where x is 'term', y is 'count'), title must be string \"\"\"\n",
    "    fig = px.pie(df, values='count', names='term', title = title)\n",
    "    return fig\n",
    "\n",
    "def get_line(df, title):\n",
    "    \"\"\" plot data in df as pie chart (where x is 'term', y is 'count'), title must be string \"\"\"\n",
    "    fig = px.line(df, y='count', x='term', title=title)\n",
    "    return fig\n",
    "\n",
    "def get_api_data(query,df_template):\n",
    "    \"\"\"Scrape data and construct df from API. Valid for reprter's type, country, seriousness of event and indication.\n",
    "    \n",
    "    Input:\n",
    "    \n",
    "    query -- API query (string)\n",
    "\n",
    "    \n",
    "    df_empty_template -- df, mandatory cols term, count, year_begin, year_end, event, drug_name, signal\n",
    "    \n",
    "    \n",
    "    Returns: df with reporters type/country/seriousness of reaction per drug, all events, all years\n",
    "    \"\"\"\n",
    "\n",
    "    \n",
    "    try:\n",
    "        df_json = json_to_df_results(query)\n",
    "\n",
    "        df_json['year_begin'] = year_begin\n",
    "        df_json['year_end'] = year_end\n",
    "\n",
    "        df_json['event'] = event\n",
    "        df_json['drug_name'] = drug_name\n",
    "        df_json['signal'] = signal\n",
    "    \n",
    "        df_template = df_template.append(df_json)\n",
    "\n",
    "        print(year_begin,year_end,event,drug_name,signal)\n",
    "\n",
    "    except Exception as exception:\n",
    "        print(exception)\n",
    "        pass\n",
    "\n",
    "    return df_template\n",
    "    \n",
    "def merge_with_reference(df,df_reference):\n",
    "    \n",
    "    \"\"\"Replaces + with space in event name in df. Merges with reference df on term, requires df_reference with cols term, value and df with term, event.\n",
    "        Drops 'term' col and replaces 'value' col name with 'term'to follow api format.\n",
    "        Valid for reporter's type, seriousness of event and indication.\n",
    "    \"\"\"\n",
    "    \n",
    "    df['event']= df['event'].str.replace('+', ' ')\n",
    "    df = df.merge(df_reference,how='left',on='term')\n",
    "    df.drop(columns='term', inplace=True)\n",
    "    df.rename(columns={'value':'term'},inplace=True)\n",
    "    \n",
    "    return df\n",
    "\n",
    "\n",
    "def merge_with_country_code(df, country_codes):\n",
    "    \n",
    "    \"\"\"Replaces + with space in event name in df. Merges with country_codes df on term, requires country_codes with cols term, value and df with term, event.\n",
    "        Copies vals of country where where country - not code - reported. Drops 'term' col and replaces 'value' col name with 'term'to follow api format. \n",
    "        Valid for reporter's region.\n",
    "    \"\"\"\n",
    "    df['event']= df['event'].str.replace('+', ' ')\n",
    "    df = df.merge(country_codes,how='left',on='term')\n",
    "\n",
    "    df['value'] = df['value'].fillna(df['term'])\n",
    "    df.drop(columns='term', inplace=True)\n",
    "    df.rename(columns={'value':'term'},inplace=True)\n",
    "    \n",
    "    return df\n",
    "\n",
    "def get_api_data_age_sex(query, df_template):\n",
    "    \"\"\"Scrape data and construct df from API. Valid for age+sex group.\n",
    "    \n",
    "    Input:\n",
    "    \n",
    "    query -- API query (string)\n",
    "\n",
    "    df_empty_template -- df, mandatory cols term, count, year_begin, year_end, event, drug_name, signal\n",
    "    \n",
    "    \n",
    "    Returns: df with age group of reaction per drug, sex, all events, all years\n",
    "    \"\"\"\n",
    "\n",
    "    try:\n",
    "        df_json = json_to_df_results(query)\n",
    "        df_json['sex'] = sex\n",
    "        df_json['year_begin'] = year_begin\n",
    "        df_json['year_end'] = year_end\n",
    "        df_json['event'] = event\n",
    "        df_json['drug_name'] = drug_name\n",
    "\n",
    "        df_template = df_template.append(df_json)\n",
    "\n",
    "        print(year_begin,year_end,sex,event,drug_name)\n",
    "\n",
    "    except Exception as exception:\n",
    "        print(exception)\n",
    "        pass\n",
    "\n",
    "    return df_template\n",
    "\n",
    "def convert_sex(df, sex_code, sex_term):\n",
    "    \"\"\"\n",
    "    Converts sex code to sex term, requires df with 'sex' col\n",
    "    \n",
    "    Input: \n",
    "    df -- pd dataframe, 'sex' col must be present\n",
    "    sex_code (str) -- code for each sex\n",
    "    sex_term (str) -- full term for each sex\n",
    "    \n",
    "    Returns:\n",
    "    df -- modified pd dataframe\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    sex = df['sex'] == sex_code\n",
    "    \n",
    "    df.loc[sex,'sex'] = sex_term\n",
    "    \n",
    "    return df\n",
    "\n",
    "\n",
    "def convert_age(df,age_format):\n",
    "    \n",
    "    \"\"\" Converts age (in months or days) to years. Requires df with 'term' (age). Rounds up converted age. \n",
    "    \n",
    "    Input:\n",
    "    df -- pd dataframe, col 'term' mandatory\n",
    "    age_format (str) -- vals 'days' or 'months'\n",
    "    \n",
    "    Returns:\n",
    "    df -- modified pd dataframe\n",
    "    \"\"\"\n",
    "    if age_format == 'days':\n",
    "        #where age in days, convert to years (assumed age <= 100yrs)\n",
    "        df['term'].where(df['term'] < 1200, (df['term']/12/31), inplace=True) #keep as is where age <1200, otherwise convert days-->years\n",
    "    \n",
    "    if age_format == 'months':\n",
    "        #where age in months, convert to years (assumed age <= 100 yrs)\n",
    "        df['term'].where(df['term'] < 100 , (df['term']/12), inplace=True)  #keep as is where age <100, otherwise convert months-->years\n",
    "\n",
    "    df['term'] = df['term'].astype(float).round(0)\n",
    "    \n",
    "    return df\n",
    "\n",
    "def merge_and_clean(df,df_reference, col_to_merge_on):\n",
    "    \"\"\"\n",
    "    Merges with reference df to get signal per event. Replaces 'term' with 'age'\n",
    "    \n",
    "    Input:\n",
    "    df -- pd dataframe. col 'term' required\n",
    "    df_reference -- pd dataframe to merge with\n",
    "    col_to_merge_on (str) -- col name to merge on\n",
    "    \n",
    "    Returns:\n",
    "    \n",
    "    df -- modified pd dataframe\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    df[col_to_merge_on]= df[col_to_merge_on].str.replace('+', ' ')\n",
    "    df = df.merge(df_reference,how='left', on='event')\n",
    "    df.rename(columns={'term':'age'},inplace=True)\n",
    "    \n",
    "    return df\n",
    "\n",
    "def get_api_data_signal_yearly(query,df_template):\n",
    "    \"\"\"Scrape data and construct df from API. Valid for signal yearly \n",
    "    \n",
    "    Input:\n",
    "    \n",
    "    query -- API query (string)\n",
    "\n",
    "    \n",
    "    df_empty_template -- df, mandatory cols event, drug_name, signal\n",
    "    \n",
    "    \n",
    "    Returns: df with drug, events, signal yearly\n",
    "    \"\"\"\n",
    "\n",
    "    \n",
    "    try:\n",
    "        df_json = json_to_df_results(query)\n",
    "        \n",
    "        df_json['time'] = pd.to_datetime(df_json['time'], format='%Y%m%d')\n",
    "        df_json = df_json.groupby(df_json['time'].dt.year)['count'].agg(['sum']) #counts get aggregated, sum is calculated\n",
    "\n",
    "        df_json['event'] = event\n",
    "        df_json['drug_name'] = drug_name\n",
    "        df_json['signal'] = signal\n",
    "    \n",
    "        df_template = df_template.append(df_json)\n",
    "\n",
    "        print(event,drug_name,signal)\n",
    "\n",
    "    except Exception as exception:\n",
    "        print(exception)\n",
    "        pass\n",
    "\n",
    "    return df_template\n",
    "\n",
    "def rename_sum(df,col_to_rename_sum, col_to_rename_index):\n",
    "    \n",
    "    \"\"\" Final processing after count aggregation by sum. Renames to col_to_rename_sum. Drops old index, renames it by col_to_rename_index\n",
    "    \n",
    "        \n",
    "    Input:\n",
    "    df -- pd dataframe\n",
    "    col_to_rename_sum (str) -- col name, will replace 'sum'\n",
    "    col_to_rename_index (str) -- col name, will replace old index\n",
    "    \n",
    "    Returns:\n",
    "    \n",
    "    df -- modified pd dataframe\n",
    "    \n",
    "    \n",
    "    \"\"\"\n",
    "    df.rename(columns={'sum':col_to_rename_sum}, inplace=True) \n",
    "    df.reset_index(inplace=True)\n",
    "    df.rename(columns={'index':col_to_rename_index}, inplace=True) \n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dirs\n",
    "# get required dirs for API querying output\n",
    "demo = os.path.join(os.getcwd(), 'demo')\n",
    "reporter_dir = os.path.join('demo', 'reporter_type')\n",
    "region = os.path.join('demo', 'region')\n",
    "serious = os.path.join('demo', 'serious')\n",
    "indication = os.path.join('demo', 'indication')\n",
    "sex_age = os.path.join('demo', 'sex_age')\n",
    "signal_yearly = os.path.join('demo', 'signal_yearly')\n",
    "\n",
    "#create dirs if not existing\n",
    "dirs = [demo,reporter_dir,region,serious,indication,sex_age, signal_yearly]\n",
    "for directory in dirs:\n",
    "    if not os.path.exists(directory):\n",
    "        os.makedirs(directory)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read list of events + signal + drug_name\n",
    "events = pd.read_excel('events_signals.xlsx')\n",
    "#remove whitespace\n",
    "events['event'] = events['event'].str.strip()\n",
    "#get df excerpt \n",
    "events2 = events[['event', 'signal']]\n",
    "\n",
    "#replace space in composit event names with + (needed for api querying)\n",
    "events['event'] = events['event'].str.replace(' ','+')\n",
    "#needed for api querying\n",
    "events['drug_name'] = events['drug_name'].str.upper()\n",
    "\n",
    "#as in FDA db\n",
    "sex_list = ['1','2']\n",
    "\n",
    "#year range to query for \n",
    "years = pd.read_excel('years.xlsx')\n",
    "\n",
    "#master df with necessary cols\n",
    "df_master = pd.read_excel('df_empty_template.xlsx')\n",
    "df_demo = pd.read_excel('demo.xlsx')\n",
    "\n",
    "#df with reporter type vals & terms\n",
    "reporter_type = pd.read_excel('reporter_type.xlsx')\n",
    "#df with country codes\n",
    "country_codes = pd.read_excel('country_codes.xlsx')\n",
    "country_codes['value'] = country_codes['value'].str.upper()\n",
    "#df with serious anotation\n",
    "serious_vals = pd.read_excel('serious.xlsx')\n",
    "\n",
    "#df for signal by year\n",
    "df_signal_template = pd.read_excel('signal_df.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#string \"root\" for openFDA API querying\n",
    "query_reporter = 'https://api.fda.gov/drug/event.json?search=(receivedate:([{0}+TO+{1}]))+AND+(patient.reaction.reactionmeddrapt.exact:(%22{2}%22))+AND+(patient.drug.openfda.generic_name:%22{3}%22)&count=primarysource.qualification'\n",
    "query_country = 'https://api.fda.gov/drug/event.json?search=(receivedate:([{0}+TO+{1}]))+AND+(patient.reaction.reactionmeddrapt.exact:(%22{2}%22))+AND+(patient.drug.openfda.generic_name:%22{3}%22)&count=primarysource.reportercountry.exact'\n",
    "query_serious = 'https://api.fda.gov/drug/event.json?search=(receivedate:([{0}+TO+{1}]))+AND+(patient.reaction.reactionmeddrapt.exact:(%22{2}%22))+AND+(patient.drug.openfda.generic_name:%22{3}%22)&count=serious'\n",
    "query_indi = 'https://api.fda.gov/drug/event.json?search=(receivedate:([{0}+TO+{1}]))+AND+(patient.reaction.reactionmeddrapt.exact:(%22{2}%22))+AND+(patient.drug.openfda.generic_name:%22{3}%22)&count=patient.drug.drugindication.exact'\n",
    "query_age_sex = 'https://api.fda.gov/drug/event.json?search=(receivedate:([{0}+TO+{1}])+AND+patient.patientsex:{2})+AND+(patient.reaction.reactionmeddrapt.exact:(%22{3}%22))+AND+(patient.drug.openfda.generic_name:%22{4}%22)&count=patient.patientonsetage'\n",
    "query_signal = 'https://api.fda.gov/drug/event.json?search=(patient.reaction.reactionmeddrapt.exact:(%22{0}%22))+AND+(patient.drug.openfda.generic_name:%22{1}%22)&count=receivedate'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reporter type \n",
    "df_reporter = pd.DataFrame(columns=df_master.columns)\n",
    "for event,drug_name,signal in zip(events.event, events.drug_name, events.signal):\n",
    "\n",
    "    for year_begin, year_end in zip(years.year_begin,years.year_end):\n",
    "        \n",
    "        query = query_reporter.format(year_begin,year_end,event,drug_name)\n",
    " \n",
    "        df_reporter = get_api_data(query, df_reporter)\n",
    "        \n",
    "df_reporter = merge_with_reference(df_reporter,reporter_type)\n",
    "df_reporter.to_excel(os.path.join(reporter_dir, '{0}_reporter_type.xlsx'.format(drug_name)), index=False) #if more than one drug in events, filename has last drug prefix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reporter's region\n",
    "df_country = pd.DataFrame(columns=df_master.columns)\n",
    "\n",
    "for event,drug_name,signal in zip(events.event, events.drug_name, events.signal):\n",
    "\n",
    "    for year_begin, year_end in zip(years.year_begin,years.year_end):\n",
    "        \n",
    "        query = query_country.format(year_begin,year_end,event,drug_name)\n",
    " \n",
    "        df_country = get_api_data(query, df_country)\n",
    "        \n",
    "df_country = merge_with_country_code(df_country, country_codes)\n",
    "df_country.to_excel(os.path.join(region,'{0}_reporter_country.xlsx'.format(drug_name)), index=False) #if more than one drug in events, filename has last drug prefix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#serious\n",
    "df_serious = pd.DataFrame(columns=df_master.columns)\n",
    "\n",
    "for event,drug_name,signal in zip(events.event, events.drug_name, events.signal):\n",
    "\n",
    "    for year_begin, year_end in zip(years.year_begin,years.year_end):\n",
    "        \n",
    "        query = query_serious.format(year_begin,year_end,event,drug_name)\n",
    " \n",
    "        df_serious = get_api_data(query, df_serious)\n",
    "        \n",
    "df_serious = merge_with_reference(df_serious,serious_vals)\n",
    "df_serious.to_excel(os.path.join(serious,'{0}_reaction_seriousness.xlsx'.format(drug_name)), index=False) #if more than one drug in events, filename has last drug prefix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "#indication\n",
    "df_indication = pd.DataFrame(columns=df_master.columns)\n",
    "\n",
    "for event,drug_name,signal in zip(events.event, events.drug_name, events.signal):\n",
    "\n",
    "    for year_begin, year_end in zip(years.year_begin,years.year_end):\n",
    "        \n",
    "        query = query_indi.format(year_begin,year_end,event,drug_name)\n",
    " \n",
    "        df_indication = get_api_data(query, df_indication)\n",
    "        \n",
    "df_indication['event']= df_indication['event'].str.replace('+', ' ')\n",
    "df_indication.to_excel(os.path.join(indication,'{0}_indication.xlsx'.format(drug_name)), index=False) #if more than one drug in events, filename has last drug prefix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#age_sex group\n",
    "demo_df = pd.DataFrame(columns=df_demo.columns)\n",
    "for event,drug_name in zip(events.event, events.drug_name): #no 'signal' here, later on merged to get 'signal'\n",
    "    for sex in sex_list:\n",
    "\n",
    "        for year_begin, year_end in zip(years.year_begin,years.year_end):\n",
    "\n",
    "            query = query_age_sex.format(year_begin,year_end,sex,event,drug_name)\n",
    "\n",
    "            demo_df = get_api_data_age_sex(query, demo_df)\n",
    "            \n",
    "            \n",
    "#convert age in months/days to years \n",
    "#attention: run days conversion always first!\n",
    "demo_df = convert_age(demo_df, 'days')\n",
    "demo_df = convert_age(demo_df, 'months')\n",
    "\n",
    "#convert each sex codes to sex term\n",
    "demo_df = convert_sex(demo_df, '2', 'female')\n",
    "demo_df = convert_sex(demo_df, '1', 'male')\n",
    "\n",
    "#merge to get 'signal', rename 'term' col\n",
    "demo_df = merge_and_clean(demo_df,events2,'event')\n",
    "demo_df.to_excel(os.path.join(sex_age,'{0}_Age_Sex.xlsx'.format(drug_name)), index=False) #if more than one drug in events, filename has last drug prefix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#events,drugs,signals yearly\n",
    "signal_df = pd.DataFrame(columns=df_signal_template.columns)\n",
    "\n",
    "for event,drug_name,signal in zip(events.event, events.drug_name, events.signal):\n",
    "\n",
    "    query = query_signal.format(event,drug_name)\n",
    "    signal_df = get_api_data_signal_yearly(query, signal_df)\n",
    "    \n",
    "#final clean up \n",
    "signal_df = rename_sum(signal_df,'count', 'year') #sum = count (follow fda api naming convention), old index = year\n",
    "signal_df.to_excel(os.path.join(signal_yearly,'{0}_signal_yearly.xlsx'.format(drug_name)), index=False) #if more than one drug in events, filename has last drug prefix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check 100 most common drugs, all our five included there\n",
    "# most_common_drugs = json_to_df_results('https://api.fda.gov/drug/event.json?&count=patient.drug.openfda.generic_name.exact')\n",
    "# most_common_drugs.to_excel('most_common_drugs.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}