formulary_01_make-md.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e8f8febe",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:14.523721Z",
     "start_time": "2023-01-18T10:50:14.519749Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0c1772ba",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:18.067021Z",
     "start_time": "2023-01-18T10:50:14.856468Z"
    }
   },
   "outputs": [],
   "source": [
    "# read drug basic data\n",
    "df1 = pd.read_excel('1.藥品基本檔.xlsx')  # 請先開EXCEL把全形逗號換成半形逗號！\n",
    "df2 = pd.read_excel('2.藥物諮詢.xlsx')  # 請先開EXCEL把全形逗號換成半形逗號！\n",
    "# exclude = pd.read_excel('exclude.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d3fa449f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:18.137768Z",
     "start_time": "2023-01-18T10:50:18.122539Z"
    }
   },
   "outputs": [],
   "source": [
    "# keep [Rx_o]\n",
    "df1['藥局內部溝通MEMO'] = df1['藥局內部溝通MEMO'].fillna('xx')\n",
    "df1_concate = df1[df1['藥局內部溝通MEMO'].str.contains('Rx_o')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "63bd0ae2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:18.203421Z",
     "start_time": "2023-01-18T10:50:18.195097Z"
    }
   },
   "outputs": [],
   "source": [
    "# exclude [Rx_x]\n",
    "df1 = df1[~df1['藥局內部溝通MEMO'].str.contains('Rx_x')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "cce17e20",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:18.266525Z",
     "start_time": "2023-01-18T10:50:18.258381Z"
    }
   },
   "outputs": [],
   "source": [
    "# drug selection\n",
    "df1 = df1[(df1['藥品狀態'] == '可用')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "5d0b2915",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:18.329359Z",
     "start_time": "2023-01-18T10:50:18.321351Z"
    }
   },
   "outputs": [],
   "source": [
    "# exclude DC comments\n",
    "exclude_list = ['廠商缺貨,可查類似藥', '停用', '廠商缺貨', '停用,可查類似藥']\n",
    "df1 = df1[~df1['DC註記'].isin(exclude_list)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "102107eb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:18.387446Z",
     "start_time": "2023-01-18T10:50:18.384453Z"
    }
   },
   "outputs": [],
   "source": [
    "# # exclude drugs in exclusion list\n",
    "# # 之後用 [Rx_x] 取代\n",
    "# exclude_list = exclude.iloc[:,0].tolist()\n",
    "# df1 = df1[~df1['藥品代碼'].isin(exclude_list)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "2eb8a53f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:18.451927Z",
     "start_time": "2023-01-18T10:50:18.439653Z"
    }
   },
   "outputs": [],
   "source": [
    "# exclude empty & weird category 2\n",
    "df1 = df1.dropna(subset=['藥理分類2'])\n",
    "exclude_list2 = ['MEDD', 'ZOTH', 'PHR']\n",
    "df1 = df1[~df1['藥理分類2'].isin(exclude_list2)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d137e74a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:18.537650Z",
     "start_time": "2023-01-18T10:50:18.506815Z"
    }
   },
   "outputs": [],
   "source": [
    "# concate [Rx_o] and drop duplicates\n",
    "df1 = pd.concat([df1, df1_concate], ignore_index=True, sort=False)\n",
    "df1 = df1.drop_duplicates(subset=['藥品代碼'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f845643b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:18.620999Z",
     "start_time": "2023-01-18T10:50:18.603746Z"
    }
   },
   "outputs": [],
   "source": [
    "df1['DC註記'] = df1['DC註記'].replace('停用', '')\n",
    "df1['DC註記'] = df1['DC註記'].replace('停用,可查類似藥', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "1dabf6f1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:23.578820Z",
     "start_time": "2023-01-18T10:50:23.566855Z"
    }
   },
   "outputs": [],
   "source": [
    "df1 = df1[['藥品代碼', '商品英文名稱', '商品學名', '藥理分類1', '藥理分類2', 'DC註記']]\n",
    "df2 = df2[['藥品代碼', '適應症', '用法用量', '肝功能異常(Y/N)', '腎功能異常(Y/N)', '禁忌', '副作用', '孕期用藥建議', '哺乳期用藥建議']]\n",
    "df = df1.merge(df2, on='藥品代碼', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "3ffd2370",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:24.668931Z",
     "start_time": "2023-01-18T10:50:24.654188Z"
    }
   },
   "outputs": [],
   "source": [
    "df = df.rename(columns={'藥品代碼': 'TAH Drug Code', '適應症': 'Indications', '用法用量': 'Dosing', '禁忌': 'Contraindications', '副作用': 'Adverse Effects', '肝功能異常(Y/N)': 'Hepatic Impairment', '腎功能異常(Y/N)': 'Renal Impairment', '孕期用藥建議': 'Pregnancy', '哺乳期用藥建議': 'Lactation'})\n",
    "df = df.replace(np.nan, 'No Data')\n",
    "df['DC註記'] = df['DC註記'].replace('No Data', '')\n",
    "df['DC註記'] = df['DC註記'].replace('臨採藥,請通知藥局外購', '臨採')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "6e537d9c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:25.202739Z",
     "start_time": "2023-01-18T10:50:25.158936Z"
    }
   },
   "outputs": [],
   "source": [
    "df = df.replace('無需調整劑量', 'Dose adjustment not necessary')\n",
    "df = df.replace('需調整劑量', 'Dose adjustment required')\n",
    "df = df.replace('Uknown 沒有資料', 'Unknown')\n",
    "df = df.replace('Unknown 沒有資料', 'Unknown')\n",
    "df = df.replace('Contraindicated 哺乳期使用禁忌', 'Contraindicated')\n",
    "df = df.replace('No (Limited) Human Data - Probably Compatible 無(很少)資料 - 可使用', 'No (Limited) Human Data - Probably Compatible')\n",
    "df = df.replace('No (Limited) Human Data – Potential Toxicity(Mother) 無(很少)資料 – 避免使用(母體)', 'No (Limited) Human Data – Potential Toxicity(Mother)')\n",
    "df = df.replace('No (Limited) Human Data - Potential Toxicity 無(很少)資料 - 避免使用', 'No (Limited) Human Data - Potential Toxicity')\n",
    "df = df.replace('Compatible 哺乳時可使用', 'Compatible')\n",
    "df = df.replace('Hold Breast Feeding 暫停哺乳', 'Hold Breast Feeding')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "cc705ca4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:25.890289Z",
     "start_time": "2023-01-18T10:50:25.885272Z"
    }
   },
   "outputs": [],
   "source": [
    "df['Pregnancy'] = df['Pregnancy'].str.title()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c05c1e30",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:26.714047Z",
     "start_time": "2023-01-18T10:50:26.702078Z"
    }
   },
   "outputs": [],
   "source": [
    "df['Pregnancy'] = df['Pregnancy'].str.replace('3 Rd', '3rd')\n",
    "df['Pregnancy'] = df['Pregnancy'].str.replace('2Nd', '2nd')\n",
    "df['Pregnancy'] = df['Pregnancy'].str.replace('1St', '1st')\n",
    "df['Pregnancy'] = df['Pregnancy'].str.replace('In', 'in')\n",
    "df['Pregnancy'] = df['Pregnancy'].str.replace('And', 'and')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7856addb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:27.815814Z",
     "start_time": "2023-01-18T10:50:27.447014Z"
    }
   },
   "outputs": [],
   "source": [
    "df.to_excel('formulary.xlsx', index=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "501f9c3b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:30.343189Z",
     "start_time": "2023-01-18T10:50:29.502395Z"
    }
   },
   "outputs": [],
   "source": [
    "# remove toc folder\n",
    "\n",
    "import os\n",
    "import sys\n",
    "import shutil\n",
    "\n",
    "# Get directory name\n",
    "mydir= 'C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc'\n",
    "\n",
    "try:\n",
    "    shutil.rmtree(mydir)\n",
    "except OSError as e:\n",
    "    print(\"Error: %s - %s.\" % (e.filename, e.strerror))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "1085732f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:30.980940Z",
     "start_time": "2023-01-18T10:50:30.378580Z"
    },
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "# make file directories\n",
    "import os\n",
    "\n",
    "try:\n",
    "    path = 'C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc'\n",
    "    # path = '/Users/shin/formulary/toc'\n",
    "    os.mkdir(path)\n",
    "    mdpath = path + '\\\\' + 'README.md'\n",
    "    with open(mdpath, 'w') as f:\n",
    "        pass\n",
    "except:\n",
    "    pass\n",
    "\n",
    "cat2_li = df['藥理分類2'].unique().tolist()\n",
    "for cat in cat2_li:\n",
    "    if cat[-3] == '-':\n",
    "        try:\n",
    "            path = 'C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc\\\\' + \\\n",
    "            cat[:3].lower() + '00-00\\\\'\n",
    "            # path = '/Users/shin/formulary/toc/' + cat[:3].lower() + '00-00/'\n",
    "            os.mkdir(path)\n",
    "            mdpath = path + 'README.md'\n",
    "            try:\n",
    "                with open(mdpath, 'w') as f:\n",
    "                    pass\n",
    "            except:\n",
    "                pass\n",
    "        except:\n",
    "            pass\n",
    "    else:\n",
    "        pass\n",
    "\n",
    "for cat in cat2_li:\n",
    "    if cat[-2:] == \"00\":\n",
    "        try:\n",
    "            path = 'C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc\\\\' + \\\n",
    "            cat[:3].lower() + '00-00\\\\' + cat.lower() + '\\\\'\n",
    "            # path = '/Users/shin/formulary/toc/' + cat[:3].lower() + '00-00/' + cat.lower() + '/'\n",
    "            os.mkdir(path)\n",
    "            mdpath = path + 'README.md'\n",
    "            try:\n",
    "                with open(mdpath, 'w') as f:\n",
    "                    pass\n",
    "            except:\n",
    "                pass\n",
    "        except:\n",
    "            pass\n",
    "    elif (cat[-2:] != \"00\") & (len(cat) == 8):\n",
    "        try:\n",
    "            path = 'C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc\\\\' + \\\n",
    "            cat[:3].lower() + '00-00\\\\' + cat[:6].lower() + '00\\\\'\n",
    "            os.mkdir(path)\n",
    "            mdpath = path + 'README.md'\n",
    "            try:\n",
    "                with open(mdpath, 'w') as f:\n",
    "                    pass\n",
    "            except:\n",
    "                pass\n",
    "        except:\n",
    "            pass\n",
    "        try:\n",
    "            path = 'C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc\\\\' + \\\n",
    "            cat[:3].lower() + '00-00\\\\' + cat[:6].lower() + '00\\\\' + cat.lower() + '\\\\'\n",
    "            # path = '/Users/shin/formulary/toc/' + cat[:3].lower() + '00-00/' + cat[:6].lower() + '00/'+ cat.lower() + '/'\n",
    "            os.mkdir(path)\n",
    "            mdpath = path + 'README.md'\n",
    "            try:\n",
    "                with open(mdpath, 'w') as f:\n",
    "                    pass\n",
    "            except:\n",
    "                pass\n",
    "        except:\n",
    "            pass\n",
    "    else:\n",
    "        try:\n",
    "            path = 'C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc\\\\' + cat.lower() + '\\\\'\n",
    "            # path = '/Users/shin/formulary/toc/' + cat.lower()\n",
    "            os.mkdir(path)\n",
    "            mdpath = path + 'README.md'\n",
    "            try:\n",
    "                with open(mdpath, 'w') as f:\n",
    "                    pass\n",
    "            except:\n",
    "                pass\n",
    "        except:\n",
    "            pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "5cb0ff9f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:31.033412Z",
     "start_time": "2023-01-18T10:50:31.017155Z"
    }
   },
   "outputs": [],
   "source": [
    "# add path to each drug (to save markdown files)\n",
    "toc1 = []\n",
    "toc2 = []\n",
    "toc3 = []\n",
    "for i in df['藥理分類2'].tolist():\n",
    "    if i[-2:] == '00':\n",
    "        toc1.append(i[:2].lower() + r'-00-00')\n",
    "        toc2.append(i.lower())\n",
    "        toc3.append(np.nan)\n",
    "    elif (i[-2:] != \"00\") & (len(i) == 8):\n",
    "        toc1.append(i[:2].lower() + r'-00-00')\n",
    "        toc2.append(i[:5].lower() + r'-00')\n",
    "        toc3.append(i.lower())\n",
    "    else:\n",
    "        toc1.append(i.lower())\n",
    "        toc2.append(np.nan)\n",
    "        toc3.append(np.nan)\n",
    "df['toc1'] = toc1\n",
    "df['toc2'] = toc2\n",
    "df['toc3'] = toc3\n",
    "\n",
    "df['drug_name'] = df['商品學名'].str.lower()\n",
    "li = df['drug_name'].tolist()\n",
    "new_li = []\n",
    "for i in li:\n",
    "    i = i.replace(' + ', '_and_')\n",
    "    i = i.replace(' & ', '_and_')\n",
    "    i = i.replace('+', '_and_')\n",
    "    i = i.replace('/', '-')\n",
    "    i = i.replace(' ', '_')\n",
    "    new_li.append(i)\n",
    "df['drug_name'] = new_li\n",
    "df['name_md'] = df['drug_name'] + '.md'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "37de4eb5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:33.662606Z",
     "start_time": "2023-01-18T10:50:33.599108Z"
    }
   },
   "outputs": [],
   "source": [
    "def combine_str(row):\n",
    "    if pd.isna(row.toc2) & pd.isna(row.toc3):\n",
    "        string = ['C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc',\n",
    "                  row['toc1'], row['name_md']]\n",
    "#         string = ['/Users/shin/formulary/toc', row['toc1'], row['name_md']]\n",
    "        return \"\\\\\".join(string)\n",
    "    elif pd.notna(row.toc3):\n",
    "        string = ['C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc',\n",
    "                  row['toc1'], row['toc2'], row['toc3'], row['name_md']]\n",
    "#         string = ['/Users/shin/formulary/toc', row['toc1'], row['toc2'], row['toc3'], row['name_md']]\n",
    "        return \"\\\\\".join(string)\n",
    "    else:\n",
    "        string = ['C:\\\\Users\\\\152551\\\\formulary-gitbook\\\\toc',\n",
    "                  row['toc1'], row['toc2'], row['name_md']]\n",
    "#         string = ['/Users/shin/formulary/toc', row['toc1'], row['toc2'], row['name_md']]\n",
    "        return \"\\\\\".join(string)\n",
    "\n",
    "\n",
    "df['url'] = df.apply(combine_str, axis=1)\n",
    "df = df.sort_values(by=['藥理分類2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "8590cf80",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:34.791618Z",
     "start_time": "2023-01-18T10:50:34.357053Z"
    }
   },
   "outputs": [],
   "source": [
    "df.to_excel('formulary2.xlsx', index=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "fca8e3e5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:35.088200Z",
     "start_time": "2023-01-18T10:50:35.084175Z"
    }
   },
   "outputs": [],
   "source": [
    "# write markdown files and save\n",
    "cat2_li = df['藥理分類2'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "41667a7f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-01-18T10:50:40.860322Z",
     "start_time": "2023-01-18T10:50:36.167127Z"
    }
   },
   "outputs": [],
   "source": [
    "# go through Category 2\n",
    "for cat2 in cat2_li:\n",
    "    df_cat2 = df[df['藥理分類2'] == cat2]\n",
    "    \n",
    "    # go through drug names under each Category 2\n",
    "    names = df_cat2['商品學名'].unique().tolist()\n",
    "    for name in names:\n",
    "        df_name = df_cat2[df_cat2['商品學名'] == name]\n",
    "        df_name = df_name.sort_values(by=['TAH Drug Code'], ascending=True).reset_index(drop=True)\n",
    "        # find drug codes with a same drug name, and go through codes with a same drug name\n",
    "        code_list = df_name['TAH Drug Code'].tolist()\n",
    "        df_name.set_index('TAH Drug Code', inplace=True)\n",
    "        # because drugs with a same drug name would be written in a same file, the saving path is the same\n",
    "        filepath = df_name.iloc[0]['url']\n",
    "        with open(filepath, \"w\", encoding='utf-8') as f:\n",
    "            title1 = '# ' + name + '\\n\\n'\n",
    "            f.write(title1)\n",
    "            \n",
    "            for code in code_list:\n",
    "                prodname = df_name.loc[code, '商品英文名稱']\n",
    "                title2 = '## ' + prodname + '\\n\\n'\n",
    "                f.write(title2)\n",
    "\n",
    "                note = df_name.loc[code, 'DC註記']\n",
    "                if len(note) == 2:\n",
    "                    title3 = '##### ' + note + '\\n\\n'\n",
    "                    f.write(title3)\n",
    "                else:\n",
    "                    pass\n",
    "\n",
    "                df_tb = df_name.loc[code,'Indications':'Lactation'].reset_index()\n",
    "                col_name = '[' + code + '](https://www.tahsda.org.tw/drugs/hissearch.php?drug_code=' + code + ')' \n",
    "                df_tb.columns = ['TAH Drug Code', col_name]\n",
    "                \n",
    "#                 search_name = name.replace(' ', '-').lower()\n",
    "#                 print(search_name)\n",
    "#                 keyword = search_name + '-drug-information'\n",
    "#                 print(keyword)\n",
    "#                 col_name_2 = '[' + UpToDate + '](https://www.uptodate.com/contents/' + keyword + ')' \n",
    "#                 df_tb.columns = ['More Info', col_name_2]\n",
    "                \n",
    "                content = df_tb.to_markdown(index=0)\n",
    "                f.write(content)\n",
    "\n",
    "                f.write('\\n\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d7f75de",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4926914",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.6"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}