ae_summary_tables.ipynb · wangjun_sh/myscripts

加入 Gitee

与超过 1200万开发者一起发现、参与优秀开源项目，私有仓库也完全免费：）

文件

该仓库未声明开源许可证文件（LICENSE），使用请关注具体项目描述及其代码上游依赖。

克隆/下载

ae_summary_tables.ipynb 64.02 KB

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AE Summary table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import xlsxwriter\n",
    "import pyreadstat\n",
    "\n",
    "myxptpath = \"C:\\\\Users\\\\Administrator\\\\Downloads\\\\cdiscpilot_update2\\\\SDTM\\\\\"\n",
    "dm = \"dm.xpt\"\n",
    "ex = \"ex.xpt\"\n",
    "ae = \"ae.xpt\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dmdf, dmmeta = pyreadstat.read_xport(myxptpath + dm)\n",
    "exdf, exmeta = pyreadstat.read_xport(myxptpath + ex)\n",
    "aedf, aemeta = pyreadstat.read_xport(myxptpath + ae)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'STUDYID': 'Study Identifier', 'DOMAIN': 'Domain Abbreviation', 'USUBJID': 'Unique Subject Identifier', 'SUBJID': 'Subject Identifier for the Study', 'RFSTDTC': 'Subject Reference Start Date/Time', 'RFENDTC': 'Subject Reference End Date/Time', 'RFXSTDTC': 'Date/Time of First Study Treatment', 'RFXENDTC': 'Date/Time of Last Study Treatment', 'RFICDTC': 'Date/Time of Informed Consent', 'RFPENDTC': 'Date/Time of End of Participation', 'DTHDTC': 'Date/Time of Death', 'DTHFL': 'Subject Death Flag', 'SITEID': 'Study Site Identifier', 'AGE': 'Age', 'AGEU': 'Age Units', 'SEX': 'Sex', 'RACE': 'Race', 'ETHNIC': 'Ethnicity', 'ARMCD': 'Planned Arm Code', 'ARM': 'Description of Planned Arm', 'ACTARMCD': 'Actual Arm Code', 'ACTARM': 'Description of Actual Arm', 'COUNTRY': 'Country', 'DMDTC': 'Date/Time of Collection', 'DMDY': 'Study Day of Collection'}\n"
     ]
    }
   ],
   "source": [
    "print(dmmeta.column_names_to_labels)\n",
    "#print(dmmeta.number_rows)\n",
    "#print(dmmeta.number_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STUDYID</th>\n",
       "      <th>DOMAIN</th>\n",
       "      <th>USUBJID</th>\n",
       "      <th>SUBJID</th>\n",
       "      <th>RFSTDTC</th>\n",
       "      <th>RFENDTC</th>\n",
       "      <th>RFXSTDTC</th>\n",
       "      <th>RFXENDTC</th>\n",
       "      <th>RFICDTC</th>\n",
       "      <th>RFPENDTC</th>\n",
       "      <th>...</th>\n",
       "      <th>SEX</th>\n",
       "      <th>RACE</th>\n",
       "      <th>ETHNIC</th>\n",
       "      <th>ARMCD</th>\n",
       "      <th>ARM</th>\n",
       "      <th>ACTARMCD</th>\n",
       "      <th>ACTARM</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>DMDTC</th>\n",
       "      <th>DMDY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>1015</td>\n",
       "      <td>2014-01-02</td>\n",
       "      <td>2014-07-02</td>\n",
       "      <td>2014-01-02</td>\n",
       "      <td>2014-07-02</td>\n",
       "      <td></td>\n",
       "      <td>2014-07-02T11:45</td>\n",
       "      <td>...</td>\n",
       "      <td>F</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HISPANIC OR LATINO</td>\n",
       "      <td>Pbo</td>\n",
       "      <td>Placebo</td>\n",
       "      <td>Pbo</td>\n",
       "      <td>Placebo</td>\n",
       "      <td>USA</td>\n",
       "      <td>2013-12-26</td>\n",
       "      <td>-7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>1023</td>\n",
       "      <td>2012-08-05</td>\n",
       "      <td>2012-09-02</td>\n",
       "      <td>2012-08-05</td>\n",
       "      <td>2012-09-01</td>\n",
       "      <td></td>\n",
       "      <td>2013-02-18</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HISPANIC OR LATINO</td>\n",
       "      <td>Pbo</td>\n",
       "      <td>Placebo</td>\n",
       "      <td>Pbo</td>\n",
       "      <td>Placebo</td>\n",
       "      <td>USA</td>\n",
       "      <td>2012-07-22</td>\n",
       "      <td>-14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-701-1028</td>\n",
       "      <td>1028</td>\n",
       "      <td>2013-07-19</td>\n",
       "      <td>2014-01-14</td>\n",
       "      <td>2013-07-19</td>\n",
       "      <td>2014-01-14</td>\n",
       "      <td></td>\n",
       "      <td>2014-01-14T11:10</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NOT HISPANIC OR LATINO</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>USA</td>\n",
       "      <td>2013-07-11</td>\n",
       "      <td>-8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-701-1033</td>\n",
       "      <td>1033</td>\n",
       "      <td>2014-03-18</td>\n",
       "      <td>2014-04-14</td>\n",
       "      <td>2014-03-18</td>\n",
       "      <td>2014-03-31</td>\n",
       "      <td></td>\n",
       "      <td>2014-09-15</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NOT HISPANIC OR LATINO</td>\n",
       "      <td>Xan_Lo</td>\n",
       "      <td>Xanomeline Low Dose</td>\n",
       "      <td>Xan_Lo</td>\n",
       "      <td>Xanomeline Low Dose</td>\n",
       "      <td>USA</td>\n",
       "      <td>2014-03-10</td>\n",
       "      <td>-8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-701-1034</td>\n",
       "      <td>1034</td>\n",
       "      <td>2014-07-01</td>\n",
       "      <td>2014-12-30</td>\n",
       "      <td>2014-07-01</td>\n",
       "      <td>2014-12-30</td>\n",
       "      <td></td>\n",
       "      <td>2014-12-30T09:50</td>\n",
       "      <td>...</td>\n",
       "      <td>F</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NOT HISPANIC OR LATINO</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>USA</td>\n",
       "      <td>2014-06-24</td>\n",
       "      <td>-7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        STUDYID DOMAIN      USUBJID SUBJID     RFSTDTC     RFENDTC  \\\n",
       "0  CDISCPILOT01     DM  01-701-1015   1015  2014-01-02  2014-07-02   \n",
       "1  CDISCPILOT01     DM  01-701-1023   1023  2012-08-05  2012-09-02   \n",
       "2  CDISCPILOT01     DM  01-701-1028   1028  2013-07-19  2014-01-14   \n",
       "3  CDISCPILOT01     DM  01-701-1033   1033  2014-03-18  2014-04-14   \n",
       "4  CDISCPILOT01     DM  01-701-1034   1034  2014-07-01  2014-12-30   \n",
       "\n",
       "     RFXSTDTC    RFXENDTC RFICDTC          RFPENDTC  ... SEX   RACE  \\\n",
       "0  2014-01-02  2014-07-02          2014-07-02T11:45  ...   F  WHITE   \n",
       "1  2012-08-05  2012-09-01                2013-02-18  ...   M  WHITE   \n",
       "2  2013-07-19  2014-01-14          2014-01-14T11:10  ...   M  WHITE   \n",
       "3  2014-03-18  2014-03-31                2014-09-15  ...   M  WHITE   \n",
       "4  2014-07-01  2014-12-30          2014-12-30T09:50  ...   F  WHITE   \n",
       "\n",
       "                   ETHNIC   ARMCD                   ARM ACTARMCD  \\\n",
       "0      HISPANIC OR LATINO     Pbo               Placebo      Pbo   \n",
       "1      HISPANIC OR LATINO     Pbo               Placebo      Pbo   \n",
       "2  NOT HISPANIC OR LATINO  Xan_Hi  Xanomeline High Dose   Xan_Hi   \n",
       "3  NOT HISPANIC OR LATINO  Xan_Lo   Xanomeline Low Dose   Xan_Lo   \n",
       "4  NOT HISPANIC OR LATINO  Xan_Hi  Xanomeline High Dose   Xan_Hi   \n",
       "\n",
       "                 ACTARM COUNTRY       DMDTC  DMDY  \n",
       "0               Placebo     USA  2013-12-26  -7.0  \n",
       "1               Placebo     USA  2012-07-22 -14.0  \n",
       "2  Xanomeline High Dose     USA  2013-07-11  -8.0  \n",
       "3   Xanomeline Low Dose     USA  2014-03-10  -8.0  \n",
       "4  Xanomeline High Dose     USA  2014-06-24  -7.0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dmdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STUDYID</th>\n",
       "      <th>DOMAIN</th>\n",
       "      <th>USUBJID</th>\n",
       "      <th>SUBJID</th>\n",
       "      <th>RFSTDTC</th>\n",
       "      <th>RFENDTC</th>\n",
       "      <th>RFXSTDTC</th>\n",
       "      <th>RFXENDTC</th>\n",
       "      <th>RFICDTC</th>\n",
       "      <th>RFPENDTC</th>\n",
       "      <th>...</th>\n",
       "      <th>SEX</th>\n",
       "      <th>RACE</th>\n",
       "      <th>ETHNIC</th>\n",
       "      <th>ARMCD</th>\n",
       "      <th>ARM</th>\n",
       "      <th>ACTARMCD</th>\n",
       "      <th>ACTARM</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>DMDTC</th>\n",
       "      <th>DMDY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>301</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-718-1254</td>\n",
       "      <td>1254</td>\n",
       "      <td>2013-07-10</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td>2013-07-10</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td></td>\n",
       "      <td>2014-01-09T11:10</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HISPANIC OR LATINO</td>\n",
       "      <td>Xan_Lo</td>\n",
       "      <td>Xanomeline Low Dose</td>\n",
       "      <td>Xan_Lo</td>\n",
       "      <td>Xanomeline Low Dose</td>\n",
       "      <td>USA</td>\n",
       "      <td>2013-06-28</td>\n",
       "      <td>-12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>302</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-718-1328</td>\n",
       "      <td>1328</td>\n",
       "      <td>2013-02-01</td>\n",
       "      <td>2013-05-01</td>\n",
       "      <td>2013-02-01</td>\n",
       "      <td>2013-04-18</td>\n",
       "      <td></td>\n",
       "      <td>2013-07-24</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NOT HISPANIC OR LATINO</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>USA</td>\n",
       "      <td>2013-01-28</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>303</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-718-1355</td>\n",
       "      <td>1355</td>\n",
       "      <td>2013-02-28</td>\n",
       "      <td>2013-08-29</td>\n",
       "      <td>2013-02-28</td>\n",
       "      <td>2013-08-29</td>\n",
       "      <td></td>\n",
       "      <td>2013-08-29T14:00</td>\n",
       "      <td>...</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NOT HISPANIC OR LATINO</td>\n",
       "      <td>Pbo</td>\n",
       "      <td>Placebo</td>\n",
       "      <td>Pbo</td>\n",
       "      <td>Placebo</td>\n",
       "      <td>USA</td>\n",
       "      <td>2013-02-12</td>\n",
       "      <td>-16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>304</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-718-1371</td>\n",
       "      <td>1371</td>\n",
       "      <td>2013-04-26</td>\n",
       "      <td>2013-08-08</td>\n",
       "      <td>2013-04-26</td>\n",
       "      <td>2013-08-01</td>\n",
       "      <td></td>\n",
       "      <td>2013-08-08</td>\n",
       "      <td>...</td>\n",
       "      <td>F</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NOT HISPANIC OR LATINO</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>USA</td>\n",
       "      <td>2013-04-11</td>\n",
       "      <td>-15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>305</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>DM</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>1427</td>\n",
       "      <td>2012-12-17</td>\n",
       "      <td>2013-02-18</td>\n",
       "      <td>2012-12-17</td>\n",
       "      <td>2013-02-11</td>\n",
       "      <td></td>\n",
       "      <td>2013-06-03</td>\n",
       "      <td>...</td>\n",
       "      <td>F</td>\n",
       "      <td>BLACK OR AFRICAN AMERICAN</td>\n",
       "      <td>NOT HISPANIC OR LATINO</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>Xan_Hi</td>\n",
       "      <td>Xanomeline High Dose</td>\n",
       "      <td>USA</td>\n",
       "      <td>2012-12-13</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          STUDYID DOMAIN      USUBJID SUBJID     RFSTDTC     RFENDTC  \\\n",
       "301  CDISCPILOT01     DM  01-718-1254   1254  2013-07-10  2014-01-09   \n",
       "302  CDISCPILOT01     DM  01-718-1328   1328  2013-02-01  2013-05-01   \n",
       "303  CDISCPILOT01     DM  01-718-1355   1355  2013-02-28  2013-08-29   \n",
       "304  CDISCPILOT01     DM  01-718-1371   1371  2013-04-26  2013-08-08   \n",
       "305  CDISCPILOT01     DM  01-718-1427   1427  2012-12-17  2013-02-18   \n",
       "\n",
       "       RFXSTDTC    RFXENDTC RFICDTC          RFPENDTC  ... SEX  \\\n",
       "301  2013-07-10  2014-01-09          2014-01-09T11:10  ...   M   \n",
       "302  2013-02-01  2013-04-18                2013-07-24  ...   M   \n",
       "303  2013-02-28  2013-08-29          2013-08-29T14:00  ...   M   \n",
       "304  2013-04-26  2013-08-01                2013-08-08  ...   F   \n",
       "305  2012-12-17  2013-02-11                2013-06-03  ...   F   \n",
       "\n",
       "                          RACE                  ETHNIC   ARMCD  \\\n",
       "301                      WHITE      HISPANIC OR LATINO  Xan_Lo   \n",
       "302                      WHITE  NOT HISPANIC OR LATINO  Xan_Hi   \n",
       "303                      WHITE  NOT HISPANIC OR LATINO     Pbo   \n",
       "304                      WHITE  NOT HISPANIC OR LATINO  Xan_Hi   \n",
       "305  BLACK OR AFRICAN AMERICAN  NOT HISPANIC OR LATINO  Xan_Hi   \n",
       "\n",
       "                      ARM ACTARMCD                ACTARM COUNTRY       DMDTC  \\\n",
       "301   Xanomeline Low Dose   Xan_Lo   Xanomeline Low Dose     USA  2013-06-28   \n",
       "302  Xanomeline High Dose   Xan_Hi  Xanomeline High Dose     USA  2013-01-28   \n",
       "303               Placebo      Pbo               Placebo     USA  2013-02-12   \n",
       "304  Xanomeline High Dose   Xan_Hi  Xanomeline High Dose     USA  2013-04-11   \n",
       "305  Xanomeline High Dose   Xan_Hi  Xanomeline High Dose     USA  2012-12-13   \n",
       "\n",
       "     DMDY  \n",
       "301 -12.0  \n",
       "302  -4.0  \n",
       "303 -16.0  \n",
       "304 -15.0  \n",
       "305  -4.0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dmdf.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating bool series True for NaN values  \n",
    "safety_flag = pd.notnull(dmdf[\"RFSTDTC\"])  \n",
    "dmdf = dmdf[safety_flag]\n",
    "dmdf.head()\n",
    "bigN = dmdf.shape[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STUDYID</th>\n",
       "      <th>DOMAIN</th>\n",
       "      <th>USUBJID</th>\n",
       "      <th>AESEQ</th>\n",
       "      <th>AESPID</th>\n",
       "      <th>AETERM</th>\n",
       "      <th>AELLT</th>\n",
       "      <th>AELLTCD</th>\n",
       "      <th>AEDECOD</th>\n",
       "      <th>AEPTCD</th>\n",
       "      <th>...</th>\n",
       "      <th>AESHOSP</th>\n",
       "      <th>AESLIFE</th>\n",
       "      <th>AESOD</th>\n",
       "      <th>EPOCH</th>\n",
       "      <th>AEDTC</th>\n",
       "      <th>AESTDTC</th>\n",
       "      <th>AEENDTC</th>\n",
       "      <th>AEDY</th>\n",
       "      <th>AESTDY</th>\n",
       "      <th>AEENDY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>AE</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>1.0</td>\n",
       "      <td>E07</td>\n",
       "      <td>APPLICATION SITE ERYTHEMA</td>\n",
       "      <td>APPLICATION SITE REDNESS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>APPLICATION SITE ERYTHEMA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>TREATMENT</td>\n",
       "      <td>2014-01-16</td>\n",
       "      <td>2014-01-03</td>\n",
       "      <td></td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>AE</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>2.0</td>\n",
       "      <td>E08</td>\n",
       "      <td>APPLICATION SITE PRURITUS</td>\n",
       "      <td>APPLICATION SITE ITCHING</td>\n",
       "      <td>NaN</td>\n",
       "      <td>APPLICATION SITE PRURITUS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>TREATMENT</td>\n",
       "      <td>2014-01-16</td>\n",
       "      <td>2014-01-03</td>\n",
       "      <td></td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>AE</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>3.0</td>\n",
       "      <td>E06</td>\n",
       "      <td>DIARRHOEA</td>\n",
       "      <td>DIARRHEA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DIARRHOEA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>TREATMENT</td>\n",
       "      <td>2014-01-16</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td>2014-01-11</td>\n",
       "      <td>15.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>AE</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>3.0</td>\n",
       "      <td>E10</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK SECOND DEGREE</td>\n",
       "      <td>AV BLOCK SECOND DEGREE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK SECOND DEGREE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>TREATMENT</td>\n",
       "      <td>2012-08-27</td>\n",
       "      <td>2012-08-26</td>\n",
       "      <td></td>\n",
       "      <td>23.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CDISCPILOT01</td>\n",
       "      <td>AE</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>2.0</td>\n",
       "      <td>E09</td>\n",
       "      <td>ERYTHEMA</td>\n",
       "      <td>LOCALIZED ERYTHEMA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ERYTHEMA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>TREATMENT</td>\n",
       "      <td>2012-08-27</td>\n",
       "      <td>2012-08-07</td>\n",
       "      <td></td>\n",
       "      <td>23.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        STUDYID DOMAIN      USUBJID  AESEQ AESPID  \\\n",
       "0  CDISCPILOT01     AE  01-701-1015    1.0    E07   \n",
       "1  CDISCPILOT01     AE  01-701-1015    2.0    E08   \n",
       "2  CDISCPILOT01     AE  01-701-1015    3.0    E06   \n",
       "3  CDISCPILOT01     AE  01-701-1023    3.0    E10   \n",
       "4  CDISCPILOT01     AE  01-701-1023    2.0    E09   \n",
       "\n",
       "                                 AETERM                     AELLT  AELLTCD  \\\n",
       "0             APPLICATION SITE ERYTHEMA  APPLICATION SITE REDNESS      NaN   \n",
       "1             APPLICATION SITE PRURITUS  APPLICATION SITE ITCHING      NaN   \n",
       "2                             DIARRHOEA                  DIARRHEA      NaN   \n",
       "3  ATRIOVENTRICULAR BLOCK SECOND DEGREE    AV BLOCK SECOND DEGREE      NaN   \n",
       "4                              ERYTHEMA        LOCALIZED ERYTHEMA      NaN   \n",
       "\n",
       "                                AEDECOD  AEPTCD  ... AESHOSP  AESLIFE AESOD  \\\n",
       "0             APPLICATION SITE ERYTHEMA     NaN  ...       N        N     N   \n",
       "1             APPLICATION SITE PRURITUS     NaN  ...       N        N     N   \n",
       "2                             DIARRHOEA     NaN  ...       N        N     N   \n",
       "3  ATRIOVENTRICULAR BLOCK SECOND DEGREE     NaN  ...       N        N     N   \n",
       "4                              ERYTHEMA     NaN  ...       N        N     N   \n",
       "\n",
       "       EPOCH       AEDTC     AESTDTC     AEENDTC  AEDY AESTDY AEENDY  \n",
       "0  TREATMENT  2014-01-16  2014-01-03              15.0    2.0    NaN  \n",
       "1  TREATMENT  2014-01-16  2014-01-03              15.0    2.0    NaN  \n",
       "2  TREATMENT  2014-01-16  2014-01-09  2014-01-11  15.0    8.0   10.0  \n",
       "3  TREATMENT  2012-08-27  2012-08-26              23.0   22.0    NaN  \n",
       "4  TREATMENT  2012-08-27  2012-08-07              23.0    3.0    NaN  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aedf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'STUDYID': 'Study Identifier',\n",
       " 'DOMAIN': 'Domain Abbreviation',\n",
       " 'USUBJID': 'Unique Subject Identifier',\n",
       " 'AESEQ': 'Sequence Number',\n",
       " 'AESPID': 'Sponsor-Defined Identifier',\n",
       " 'AETERM': 'Reported Term for the Adverse Event',\n",
       " 'AELLT': 'Lowest Level Term',\n",
       " 'AELLTCD': 'Lowest Level Term Code',\n",
       " 'AEDECOD': 'Dictionary-Derived Term',\n",
       " 'AEPTCD': 'Preferred Term Code',\n",
       " 'AEHLT': 'High Level Term',\n",
       " 'AEHLTCD': 'High Level Term Code',\n",
       " 'AEHLGT': 'High Level Group Term',\n",
       " 'AEHLGTCD': 'High Level Group Term Code',\n",
       " 'AEBODSYS': 'Body System or Organ Class',\n",
       " 'AEBDSYCD': 'Body System or Organ Class Code',\n",
       " 'AESOC': 'Primary System Organ Class',\n",
       " 'AESOCCD': 'Primary System Organ Class Code',\n",
       " 'AESEV': 'Severity/Intensity',\n",
       " 'AESER': 'Serious Event',\n",
       " 'AEACN': 'Action Taken with Study Treatment',\n",
       " 'AEREL': 'Causality',\n",
       " 'AEOUT': 'Outcome of Adverse Event',\n",
       " 'AESCAN': 'Involves Cancer',\n",
       " 'AESCONG': 'Congenital Anomaly or Birth Defect',\n",
       " 'AESDISAB': 'Persist or Signif Disability/Incapacity',\n",
       " 'AESDTH': 'Results in Death',\n",
       " 'AESHOSP': 'Requires or Prolongs Hospitalization',\n",
       " 'AESLIFE': 'Is Life Threatening',\n",
       " 'AESOD': 'Occurred with Overdose',\n",
       " 'EPOCH': 'Epoch',\n",
       " 'AEDTC': 'Date/Time of Collection',\n",
       " 'AESTDTC': 'Start Date/Time of Adverse Event',\n",
       " 'AEENDTC': 'End Date/Time of Adverse Event',\n",
       " 'AEDY': 'Study Day of Visit/Collection/Exam',\n",
       " 'AESTDY': 'Study Day of Start of Adverse Event',\n",
       " 'AEENDY': 'Study Day of End of Adverse Event'}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aemeta.column_names_to_labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>USUBJID</th>\n",
       "      <th>AESOCCD</th>\n",
       "      <th>AESOC</th>\n",
       "      <th>AEPTCD</th>\n",
       "      <th>AEDECOD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>APPLICATION SITE ERYTHEMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>APPLICATION SITE PRURITUS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DIARRHOEA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK SECOND DEGREE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SKIN AND SUBCUTANEOUS TISSUE DISORDERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ERYTHEMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>956</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NAUSEA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>957</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NAUSEA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>958</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NAUSEA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>959</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NAUSEA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>960</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NAUSEA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>961 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         USUBJID  AESOCCD                                              AESOC  \\\n",
       "0    01-701-1015      NaN  GENERAL DISORDERS AND ADMINISTRATION SITE COND...   \n",
       "1    01-701-1015      NaN  GENERAL DISORDERS AND ADMINISTRATION SITE COND...   \n",
       "2    01-701-1015      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "3    01-701-1023      NaN                                  CARDIAC DISORDERS   \n",
       "4    01-701-1023      NaN             SKIN AND SUBCUTANEOUS TISSUE DISORDERS   \n",
       "..           ...      ...                                                ...   \n",
       "956  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "957  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "958  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "959  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "960  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "\n",
       "     AEPTCD                               AEDECOD  \n",
       "0       NaN             APPLICATION SITE ERYTHEMA  \n",
       "1       NaN             APPLICATION SITE PRURITUS  \n",
       "2       NaN                             DIARRHOEA  \n",
       "3       NaN  ATRIOVENTRICULAR BLOCK SECOND DEGREE  \n",
       "4       NaN                              ERYTHEMA  \n",
       "..      ...                                   ...  \n",
       "956     NaN                                NAUSEA  \n",
       "957     NaN                                NAUSEA  \n",
       "958     NaN                                NAUSEA  \n",
       "959     NaN                                NAUSEA  \n",
       "960     NaN                                NAUSEA  \n",
       "\n",
       "[961 rows x 5 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get unique USUBJID, AESOC, AEDECOD, AESEV\n",
    "aedf1 = aedf[[\"USUBJID\",\"AESOCCD\",\"AESOC\",\"AEPTCD\",\"AEDECOD\"]]\n",
    "#aedf2 = aedf1.groupby([\"USUBJID\",\"AESOCCD\",\"AESOC\",\"AEPTCD\",\"AEDECOD\"])\n",
    "aedf2 = aedf1.groupby([\"USUBJID\",\"AESOC\",\"AEDECOD\"])\n",
    "aedf2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>USUBJID</th>\n",
       "      <th>AESOC</th>\n",
       "      <th>AEDECOD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>DIARRHOEA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "      <td>APPLICATION SITE ERYTHEMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "      <td>APPLICATION SITE PRURITUS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK SECOND DEGREE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>SKIN AND SUBCUTANEOUS TISSUE DISORDERS</td>\n",
       "      <td>ERYTHEMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>817</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "      <td>APPLICATION SITE PRURITUS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>818</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>METABOLISM AND NUTRITION DISORDERS</td>\n",
       "      <td>DECREASED APPETITE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>819</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>MUSCULOSKELETAL AND CONNECTIVE TISSUE DISORDERS</td>\n",
       "      <td>BACK PAIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>820</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NERVOUS SYSTEM DISORDERS</td>\n",
       "      <td>AMNESIA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>821</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NERVOUS SYSTEM DISORDERS</td>\n",
       "      <td>LETHARGY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>822 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         USUBJID                                              AESOC  \\\n",
       "0    01-701-1015                         GASTROINTESTINAL DISORDERS   \n",
       "1    01-701-1015  GENERAL DISORDERS AND ADMINISTRATION SITE COND...   \n",
       "2    01-701-1015  GENERAL DISORDERS AND ADMINISTRATION SITE COND...   \n",
       "3    01-701-1023                                  CARDIAC DISORDERS   \n",
       "4    01-701-1023             SKIN AND SUBCUTANEOUS TISSUE DISORDERS   \n",
       "..           ...                                                ...   \n",
       "817  01-718-1427  GENERAL DISORDERS AND ADMINISTRATION SITE COND...   \n",
       "818  01-718-1427                 METABOLISM AND NUTRITION DISORDERS   \n",
       "819  01-718-1427    MUSCULOSKELETAL AND CONNECTIVE TISSUE DISORDERS   \n",
       "820  01-718-1427                           NERVOUS SYSTEM DISORDERS   \n",
       "821  01-718-1427                           NERVOUS SYSTEM DISORDERS   \n",
       "\n",
       "                                  AEDECOD  \n",
       "0                               DIARRHOEA  \n",
       "1               APPLICATION SITE ERYTHEMA  \n",
       "2               APPLICATION SITE PRURITUS  \n",
       "3    ATRIOVENTRICULAR BLOCK SECOND DEGREE  \n",
       "4                                ERYTHEMA  \n",
       "..                                    ...  \n",
       "817             APPLICATION SITE PRURITUS  \n",
       "818                    DECREASED APPETITE  \n",
       "819                             BACK PAIN  \n",
       "820                               AMNESIA  \n",
       "821                              LETHARGY  \n",
       "\n",
       "[822 rows x 3 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aedf3 = aedf2.size().reset_index(name='count')\n",
    "aedf3 = aedf3.drop(['count'], axis=1)\n",
    "aedf3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AESOC</th>\n",
       "      <th>AEDECOD</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIAL FIBRILLATION</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIAL FLUTTER</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIAL HYPERTROPHY</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK FIRST DEGREE</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK SECOND DEGREE</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>237</td>\n",
       "      <td>VASCULAR DISORDERS</td>\n",
       "      <td>HOT FLUSH</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>238</td>\n",
       "      <td>VASCULAR DISORDERS</td>\n",
       "      <td>HYPERTENSION</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>239</td>\n",
       "      <td>VASCULAR DISORDERS</td>\n",
       "      <td>HYPOTENSION</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>240</td>\n",
       "      <td>VASCULAR DISORDERS</td>\n",
       "      <td>ORTHOSTATIC HYPOTENSION</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>241</td>\n",
       "      <td>VASCULAR DISORDERS</td>\n",
       "      <td>WOUND HAEMORRHAGE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>242 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  AESOC                               AEDECOD  count\n",
       "0     CARDIAC DISORDERS                   ATRIAL FIBRILLATION      5\n",
       "1     CARDIAC DISORDERS                        ATRIAL FLUTTER      2\n",
       "2     CARDIAC DISORDERS                    ATRIAL HYPERTROPHY      1\n",
       "3     CARDIAC DISORDERS   ATRIOVENTRICULAR BLOCK FIRST DEGREE      2\n",
       "4     CARDIAC DISORDERS  ATRIOVENTRICULAR BLOCK SECOND DEGREE      5\n",
       "..                  ...                                   ...    ...\n",
       "237  VASCULAR DISORDERS                             HOT FLUSH      1\n",
       "238  VASCULAR DISORDERS                          HYPERTENSION      3\n",
       "239  VASCULAR DISORDERS                           HYPOTENSION      3\n",
       "240  VASCULAR DISORDERS               ORTHOSTATIC HYPOTENSION      1\n",
       "241  VASCULAR DISORDERS                     WOUND HAEMORRHAGE      1\n",
       "\n",
       "[242 rows x 3 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#aedf_count = aedf3.groupby([\"AESOCCD\",\"AESOC\",\"AEPTCD\",\"AEDECOD\"])\n",
    "aedf_count = aedf3.groupby([\"AESOC\",\"AEDECOD\"])\n",
    "aedf_count2 = aedf_count.size().reset_index(name='count')\n",
    "aedf_count2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>USUBJID</th>\n",
       "      <th>AESOCCD</th>\n",
       "      <th>AESOC</th>\n",
       "      <th>AEPTCD</th>\n",
       "      <th>AEDECOD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "      <td>-999</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "      <td>-999</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>-999</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>-999</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SKIN AND SUBCUTANEOUS TISSUE DISORDERS</td>\n",
       "      <td>-999</td>\n",
       "      <td>SKIN AND SUBCUTANEOUS TISSUE DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>956</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>-999</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>957</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>-999</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>958</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>-999</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>959</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>-999</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>960</td>\n",
       "      <td>01-718-1427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>-999</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>941 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         USUBJID  AESOCCD                                              AESOC  \\\n",
       "0    01-701-1015      NaN  GENERAL DISORDERS AND ADMINISTRATION SITE COND...   \n",
       "1    01-701-1015      NaN  GENERAL DISORDERS AND ADMINISTRATION SITE COND...   \n",
       "2    01-701-1015      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "3    01-701-1023      NaN                                  CARDIAC DISORDERS   \n",
       "4    01-701-1023      NaN             SKIN AND SUBCUTANEOUS TISSUE DISORDERS   \n",
       "..           ...      ...                                                ...   \n",
       "956  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "957  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "958  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "959  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "960  01-718-1427      NaN                         GASTROINTESTINAL DISORDERS   \n",
       "\n",
       "     AEPTCD                                            AEDECOD  \n",
       "0      -999  GENERAL DISORDERS AND ADMINISTRATION SITE COND...  \n",
       "1      -999  GENERAL DISORDERS AND ADMINISTRATION SITE COND...  \n",
       "2      -999                         GASTROINTESTINAL DISORDERS  \n",
       "3      -999                                  CARDIAC DISORDERS  \n",
       "4      -999             SKIN AND SUBCUTANEOUS TISSUE DISORDERS  \n",
       "..      ...                                                ...  \n",
       "956    -999                         GASTROINTESTINAL DISORDERS  \n",
       "957    -999                         GASTROINTESTINAL DISORDERS  \n",
       "958    -999                         GASTROINTESTINAL DISORDERS  \n",
       "959    -999                         GASTROINTESTINAL DISORDERS  \n",
       "960    -999                         GASTROINTESTINAL DISORDERS  \n",
       "\n",
       "[941 rows x 5 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#SOC Only\n",
    "aesocdf = aedf1\n",
    "pd.set_option('mode.chained_assignment', None)\n",
    "aesocdf[\"AEPTCD\"] = -999\n",
    "aesocdf[\"AEDECOD\"] = aesocdf[\"AESOC\"]\n",
    "\n",
    "#aesocdf2 = aesocdf.groupby([\"USUBJID\",\"AESOCCD\",\"AESOC\",\"AEPTCD\",\"AEDECOD\"])\n",
    "aesocdf2 = aesocdf.groupby([\"USUBJID\",\"AESOC\",\"AEDECOD\"])\n",
    "aesocdf2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>USUBJID</th>\n",
       "      <th>AESOC</th>\n",
       "      <th>AEDECOD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>01-701-1015</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>01-701-1023</td>\n",
       "      <td>SKIN AND SUBCUTANEOUS TISSUE DISORDERS</td>\n",
       "      <td>SKIN AND SUBCUTANEOUS TISSUE DISORDERS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>01-701-1028</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "      <td>GENERAL DISORDERS AND ADMINISTRATION SITE COND...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       USUBJID                                              AESOC  \\\n",
       "0  01-701-1015                         GASTROINTESTINAL DISORDERS   \n",
       "1  01-701-1015  GENERAL DISORDERS AND ADMINISTRATION SITE COND...   \n",
       "2  01-701-1023                                  CARDIAC DISORDERS   \n",
       "3  01-701-1023             SKIN AND SUBCUTANEOUS TISSUE DISORDERS   \n",
       "4  01-701-1028  GENERAL DISORDERS AND ADMINISTRATION SITE COND...   \n",
       "\n",
       "                                             AEDECOD  \n",
       "0                         GASTROINTESTINAL DISORDERS  \n",
       "1  GENERAL DISORDERS AND ADMINISTRATION SITE COND...  \n",
       "2                                  CARDIAC DISORDERS  \n",
       "3             SKIN AND SUBCUTANEOUS TISSUE DISORDERS  \n",
       "4  GENERAL DISORDERS AND ADMINISTRATION SITE COND...  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aesocdf3 = aesocdf2.size().reset_index(name='count')\n",
    "aesocdf3 = aesocdf3.drop(['count'], axis=1)\n",
    "aesocdf3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AESOC</th>\n",
       "      <th>soc_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CONGENITAL, FAMILIAL AND GENETIC DISORDERS</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>EAR AND LABYRINTH DISORDERS</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>EYE DISORDERS</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>GASTROINTESTINAL DISORDERS</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        AESOC  soc_count\n",
       "0                           CARDIAC DISORDERS         44\n",
       "1  CONGENITAL, FAMILIAL AND GENETIC DISORDERS          3\n",
       "2                 EAR AND LABYRINTH DISORDERS          4\n",
       "3                               EYE DISORDERS          7\n",
       "4                  GASTROINTESTINAL DISORDERS         53"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#aedf_count = aedf3.groupby([\"AESOCCD\",\"AESOC\",\"AEPTCD\",\"AEDECOD\"])\n",
    "aesocdf_count = aesocdf3.groupby([\"AESOC\",\"AEDECOD\"])\n",
    "aesocdf_count2 = aesocdf_count.size().reset_index(name='count')\n",
    "aesocdf_count2 = aesocdf_count2.rename(columns={\"count\": \"soc_count\"})\n",
    "aesocdf_count2 = aesocdf_count2.drop(columns=[\"AEDECOD\"])\n",
    "aesocdf_count2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AESOC</th>\n",
       "      <th>AEDECOD</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIAL FIBRILLATION</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIAL FLUTTER</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIAL HYPERTROPHY</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK FIRST DEGREE</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK SECOND DEGREE</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               AESOC                               AEDECOD  count\n",
       "0  CARDIAC DISORDERS                   ATRIAL FIBRILLATION      5\n",
       "1  CARDIAC DISORDERS                        ATRIAL FLUTTER      2\n",
       "2  CARDIAC DISORDERS                    ATRIAL HYPERTROPHY      1\n",
       "3  CARDIAC DISORDERS   ATRIOVENTRICULAR BLOCK FIRST DEGREE      2\n",
       "4  CARDIAC DISORDERS  ATRIOVENTRICULAR BLOCK SECOND DEGREE      5"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aedf_count2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AESOC</th>\n",
       "      <th>AEDECOD</th>\n",
       "      <th>count</th>\n",
       "      <th>soc_count</th>\n",
       "      <th>bigN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIAL FIBRILLATION</td>\n",
       "      <td>5</td>\n",
       "      <td>44</td>\n",
       "      <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIAL FLUTTER</td>\n",
       "      <td>2</td>\n",
       "      <td>44</td>\n",
       "      <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIAL HYPERTROPHY</td>\n",
       "      <td>1</td>\n",
       "      <td>44</td>\n",
       "      <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK FIRST DEGREE</td>\n",
       "      <td>2</td>\n",
       "      <td>44</td>\n",
       "      <td>306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CARDIAC DISORDERS</td>\n",
       "      <td>ATRIOVENTRICULAR BLOCK SECOND DEGREE</td>\n",
       "      <td>5</td>\n",
       "      <td>44</td>\n",
       "      <td>306</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               AESOC                               AEDECOD  count  soc_count  \\\n",
       "0  CARDIAC DISORDERS                   ATRIAL FIBRILLATION      5         44   \n",
       "1  CARDIAC DISORDERS                        ATRIAL FLUTTER      2         44   \n",
       "2  CARDIAC DISORDERS                    ATRIAL HYPERTROPHY      1         44   \n",
       "3  CARDIAC DISORDERS   ATRIOVENTRICULAR BLOCK FIRST DEGREE      2         44   \n",
       "4  CARDIAC DISORDERS  ATRIOVENTRICULAR BLOCK SECOND DEGREE      5         44   \n",
       "\n",
       "   bigN  \n",
       "0   306  \n",
       "1   306  \n",
       "2   306  \n",
       "3   306  \n",
       "4   306  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aesocpt = pd.merge(aedf_count2, aesocdf_count2, on=\"AESOC\")\n",
    "aesocpt[\"bigN\"] = bigN\n",
    "aesocpt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(961, 37)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#aedf.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# export to excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a Pandas Excel writer using XlsxWriter as the engine.\n",
    "writer = pd.ExcelWriter('pandas_sdtm_excel.xlsx', engine='xlsxwriter')\n",
    "\n",
    "# Convert the dataframe to an XlsxWriter Excel object.\n",
    "dmdf.to_excel(writer, sheet_name='DM')\n",
    "exdf.to_excel(writer, sheet_name='EX')\n",
    "aedf.to_excel(writer, sheet_name='AE')\n",
    "\n",
    "aedf_count2.to_excel(writer, sheet_name='AESummary', startcol=1, startrow=2)\n",
    "aesocdf_count2.to_excel(writer, sheet_name='AESummary-SOC', startcol=1, startrow=2)\n",
    "aesocpt.to_excel(writer, sheet_name='AESummary-SOCPT', startcol=1, startrow=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the xlsxwriter objects from the dataframe writer object.\n",
    "workbook  = writer.book\n",
    "aesummarysheet = writer.sheets['AESummary']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "aesummarysheet.write('A1', 'Hello the world!!')\n",
    "aesummarysheet.write_comment('A1', 'This is a comment')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.save()"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}

一键复制编辑原始数据按行查看历史

提交于 2020-03-18 22:09 . added AE summary

马建仓 AI 助手

尝试更多

代码解读

代码找茬

代码优化

https://gitee.com/wangjun_sh/myscripts.git

git@gitee.com:wangjun_sh/myscripts.git

wangjun_sh

myscripts

master

wangjun_sh/myscripts

简介

发行版

贡献者

近期动态

wangjun_sh/myscripts .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

wangjun_sh/myscripts