PandaTricks_20220522_PO.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5\n"
     ]
    }
   ],
   "source": [
    "print(2+3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     Name  Age  StudentID    Topic_1_E1_C1 Topic_E1_C1\n",
      "0     Tom   20          1  DigestiveSystem       Italy\n",
      "1  Joseph   21          2           Botany     Germany\n",
      "2   Krish   19          3    Cell Bilology      France\n",
      "3    John   18          4  Cell-Physiology      Sweden\n",
      "4    Linn   20          5   Nervous-System      Poland\n"
     ]
    }
   ],
   "source": [
    "# Sample dataframe to be used in most of the functions below.\n",
    "import pandas as pd\n",
    "# assign data of lists.\n",
    "data =  {'Name': ['Tom', 'Joseph', 'Krish', 'John','Linn'], 'Age': [20, 21, 19, 18,20],'StudentID':[1,2,3,4,5],'Topic_1_E1_C1':['DigestiveSystem','Botany','Cell Bilology','Cell-Physiology','Nervous-System'],'Topic_E1_C1':['Italy','Germany','France','Sweden','Poland']}\n",
    "# Create DataFrame.\n",
    "testdf = pd.DataFrame(data)\n",
    "# Print the output.\n",
    "print(testdf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Function or order(arrange columns) in a particular Order from Left."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to order column in a particular order\n",
    "#Very Handy to get unique columns in the begining, esp in case of Wide Dataset.\n",
    "# REF : https://stackoverflow.com/questions/13148429/how-to-change-the-order-of-dataframe-columns\n",
    "def order(frame,var):\n",
    "    if type(var) is str:\n",
    "        var = [var] #let the command take a string or list\n",
    "    varlist =[w for w in frame.columns if w not in var]\n",
    "    frame = frame[var+varlist]\n",
    "    return frame "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   StudentID    Name  Age    Topic_1_E1_C1 Topic_E1_C1\n",
      "0          1     Tom   20  DigestiveSystem       Italy\n",
      "1          2  Joseph   21           Botany     Germany\n",
      "2          3   Krish   19    Cell Bilology      France\n",
      "3          4    John   18  Cell-Physiology      Sweden\n",
      "4          5    Linn   20   Nervous-System      Poland\n"
     ]
    }
   ],
   "source": [
    "testdf = order(testdf,['StudentID','Name'])\n",
    "print(testdf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Function to create a CSV files from a dataframe based on Chunk Size\n",
    "especially useful to create file for upload in another system where their is restrictions \n",
    "in the nos of records or file Sizes etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "from more_itertools import sliced\n",
    "import csv\n",
    "\n",
    "def createCSVsforImport(chunk_size, OutputLocation,dataframeInput):    \n",
    "    index_slices = sliced(range(len(dataframeInput)), chunk_size)    \n",
    "    i=0\n",
    "    for index_slice in index_slices:\n",
    "        chunk = dataframeInput.iloc[index_slice] # your dataframe chunk ready for use\n",
    "        chunk.to_csv(OutputLocation+'CSVFile_{id}.csv'.format(id = i), sep='|', encoding='utf-8-sig',index=False, line_terminator='\\n' , escapechar='\\\\', quoting=csv.QUOTE_ALL,chunksize=50)#,quoting=csv.QUOTE_NONE, escapechar='\\\\')\n",
    "        i = i+1  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "createCSVsforImport(2, \"D:\\\\GITCODE\\\\Outputs\\\\\",testdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"1_CSVOutputs.jpg\"/>"
      ],
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### TO show the image of the output generated by the function above.\n",
    "# import image module\n",
    "from IPython.display import Image\n",
    "  \n",
    "# get the image\n",
    "Image(url=\"1_CSVOutputs.jpg\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Code / Function to rename similar columns and combine/concatenate many columns into 1 , seperated by comma"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>StudentID</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Topic_1_E1_C1</th>\n",
       "      <th>Topic_E1_C1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Tom</td>\n",
       "      <td>20</td>\n",
       "      <td>DigestiveSystem</td>\n",
       "      <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>21</td>\n",
       "      <td>Botany</td>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Krish</td>\n",
       "      <td>19</td>\n",
       "      <td>Cell Bilology</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>John</td>\n",
       "      <td>18</td>\n",
       "      <td>Cell-Physiology</td>\n",
       "      <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Linn</td>\n",
       "      <td>20</td>\n",
       "      <td>Nervous-System</td>\n",
       "      <td>Poland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StudentID    Name  Age    Topic_1_E1_C1 Topic_E1_C1\n",
       "0          1     Tom   20  DigestiveSystem       Italy\n",
       "1          2  Joseph   21           Botany     Germany\n",
       "2          3   Krish   19    Cell Bilology      France\n",
       "3          4    John   18  Cell-Physiology      Sweden\n",
       "4          5    Linn   20   Nervous-System      Poland"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_concat = testdf.copy()\n",
    "df_concat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>StudentID</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Topic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Tom</td>\n",
       "      <td>20</td>\n",
       "      <td>DigestiveSystem</td>\n",
       "      <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>21</td>\n",
       "      <td>Botany</td>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Krish</td>\n",
       "      <td>19</td>\n",
       "      <td>Cell Bilology</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>John</td>\n",
       "      <td>18</td>\n",
       "      <td>Cell-Physiology</td>\n",
       "      <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Linn</td>\n",
       "      <td>20</td>\n",
       "      <td>Nervous-System</td>\n",
       "      <td>Poland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StudentID    Name  Age            Topic    Topic\n",
       "0          1     Tom   20  DigestiveSystem    Italy\n",
       "1          2  Joseph   21           Botany  Germany\n",
       "2          3   Krish   19    Cell Bilology   France\n",
       "3          4    John   18  Cell-Physiology   Sweden\n",
       "4          5    Linn   20   Nervous-System   Poland"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove suffix\n",
    "df_concat.columns = df_concat.columns.str.rstrip(\"_E1_C1\")\n",
    "df_concat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>Age</th>\n",
       "      <th>Name</th>\n",
       "      <th>StudentID</th>\n",
       "      <th>Topic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td>Tom</td>\n",
       "      <td>1</td>\n",
       "      <td>DigestiveSystem,Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>2</td>\n",
       "      <td>Botany,Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19</td>\n",
       "      <td>Krish</td>\n",
       "      <td>3</td>\n",
       "      <td>Cell Bilology,France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18</td>\n",
       "      <td>John</td>\n",
       "      <td>4</td>\n",
       "      <td>Cell-Physiology,Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20</td>\n",
       "      <td>Linn</td>\n",
       "      <td>5</td>\n",
       "      <td>Nervous-System,Poland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Age    Name StudentID                   Topic\n",
       "0  20     Tom         1   DigestiveSystem,Italy\n",
       "1  21  Joseph         2          Botany,Germany\n",
       "2  19   Krish         3    Cell Bilology,France\n",
       "3  18    John         4  Cell-Physiology,Sweden\n",
       "4  20    Linn         5   Nervous-System,Poland"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#combine-duplicate-columns-into-one\n",
    "# REF : https://stackoverflow.com/questions/37932391/pandas-dataframe-combine-duplicate-columns-into-one-separate-data-by-comma\n",
    "def cobinecols(y):\n",
    "        return ','.join(y[y.notnull()].astype(str))\n",
    "dfCombine = df_concat.groupby(level=0, axis=1).apply(lambda y: y.apply(cobinecols, axis=1))\n",
    "dfCombine.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>StudentID</th>\n",
       "      <th>TOPIC</th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Topic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1_E1_C1</td>\n",
       "      <td>Tom</td>\n",
       "      <td>20</td>\n",
       "      <td>DigestiveSystem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1_E1_C1</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>21</td>\n",
       "      <td>Botany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1_E1_C1</td>\n",
       "      <td>Krish</td>\n",
       "      <td>19</td>\n",
       "      <td>Cell Bilology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1_E1_C1</td>\n",
       "      <td>John</td>\n",
       "      <td>18</td>\n",
       "      <td>Cell-Physiology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1_E1_C1</td>\n",
       "      <td>Linn</td>\n",
       "      <td>20</td>\n",
       "      <td>Nervous-System</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StudentID    TOPIC    Name  Age            Topic\n",
       "0          1  1_E1_C1     Tom   20  DigestiveSystem\n",
       "1          2  1_E1_C1  Joseph   21           Botany\n",
       "2          3  1_E1_C1   Krish   19    Cell Bilology\n",
       "3          4  1_E1_C1    John   18  Cell-Physiology\n",
       "4          5  1_E1_C1    Linn   20   Nervous-System"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##Convert DF wide to Long\n",
    "dfw2L = pd.wide_to_long(testdf, stubnames=['Topic'], i=['StudentID'], j='TOPIC', suffix='\\\\w+' , sep='_').reset_index()\n",
    "dfw2L.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Demonstate conversion of Long to wide with some custom logic\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(12, 4)\n",
      "   patientnummer       date               Item  score\n",
      "0            001  6/30/2015           Fracture      1\n",
      "1            001  6/30/2015            Fatigue      0\n",
      "2            001  6/30/2015        Weight loss      1\n",
      "3            001  6/30/2015  Pain in extremity      1\n",
      "4            001  6/30/2016            Fatigue      1\n",
      "5            002  3/22/2018            Fatigue      0\n",
      "6            002  2/11/2019  Pain in extremity      1\n",
      "7            002  2/11/2019            Fatigue      1\n",
      "8            003  6/30/2015        Weight loss      1\n",
      "9            004   9/8/2017            Fatigue      0\n",
      "10           004   9/8/2017        Weight loss      1\n",
      "11           004  2/19/2018            Fatigue      1\n"
     ]
    }
   ],
   "source": [
    "# Sample dataframe to be used for this transformation\n",
    "import pandas as pd\n",
    "# assign data of lists.\n",
    "data =  {'patientnummer': ['001', '001', '001', '001','001', '002', '002','002','003','004','004','004'], \n",
    "'date': ['6/30/2015','6/30/2015','6/30/2015','6/30/2015','6/30/2016','3/22/2018','2/11/2019','2/11/2019','6/30/2015','9/8/2017','9/8/2017','2/19/2018'],\n",
    "'Item':['Fracture','Fatigue','Weight loss','Pain in extremity','Fatigue','Fatigue','Pain in extremity','Fatigue','Weight loss','Fatigue','Weight loss','Fatigue'],'score':[1,0,1,1,1,0,1,1,1,0,1,1]}\n",
    "# Create DataFrame.\n",
    "patient_df = pd.DataFrame(data)\n",
    "\n",
    "print(patient_df.shape)\n",
    "# Print the output.\n",
    "print(patient_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11, 4)\n"
     ]
    },
    {
     "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>patientnummer</th>\n",
       "      <th>date</th>\n",
       "      <th>Item</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Weight loss</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Pain in extremity</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2016</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>002</td>\n",
       "      <td>3/22/2018</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patientnummer       date               Item  score\n",
       "1           001  6/30/2015            Fatigue      0\n",
       "2           001  6/30/2015        Weight loss      1\n",
       "3           001  6/30/2015  Pain in extremity      1\n",
       "4           001  6/30/2016            Fatigue      1\n",
       "5           002  3/22/2018            Fatigue      0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patient_df_filtered = patient_df[patient_df['Item'].isin(['Chest wall pain'\n",
    "                                                    ,'Dermatitis radiation'\n",
    "                                                    ,'Edema limbs'\n",
    "                                                    ,'Esophagitis'\n",
    "                                                    ,'Fatigue'\n",
    "                                                    ,'Lymphedema breast/chest'\n",
    "                                                    ,'Pain breast'\n",
    "                                                    ,'Pain in extremity'\n",
    "                                                    ,'Weight loss'])]\n",
    "\n",
    "print(patient_df_filtered.shape)\n",
    "patient_df_filtered.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add a column for rank based on unique combination of patient Id +Date as differnt patients fill same questionaiire at different dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11, 5)\n"
     ]
    },
    {
     "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>patientnummer</th>\n",
       "      <th>date</th>\n",
       "      <th>Item</th>\n",
       "      <th>score</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Weight loss</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Pain in extremity</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2016</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>002</td>\n",
       "      <td>3/22/2018</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patientnummer       date               Item  score  rank\n",
       "1           001  6/30/2015            Fatigue      0   1.0\n",
       "2           001  6/30/2015        Weight loss      1   1.0\n",
       "3           001  6/30/2015  Pain in extremity      1   1.0\n",
       "4           001  6/30/2016            Fatigue      1   2.0\n",
       "5           002  3/22/2018            Fatigue      0   2.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df=patient_df_filtered.sort_values(by=['patientnummer', 'date','Item'])\n",
    "new_df=new_df.assign(rank=new_df['date']!=new_df['date'].shift())\n",
    "new_df=new_df.assign(rank=new_df.groupby(['patientnummer'])['rank'].cumsum()).sort_index()\n",
    "print(new_df.shape)\n",
    "new_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now pivot this table based on patientId and rank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Item</th>\n",
       "      <th>Fatigue</th>\n",
       "      <th>Pain in extremity</th>\n",
       "      <th>Weight loss</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>patientnummer</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">001</th>\n",
       "      <th>1.0</th>\n",
       "      <th>6/30/2015</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.0</th>\n",
       "      <th>6/30/2016</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">002</th>\n",
       "      <th>1.0</th>\n",
       "      <th>2/11/2019</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.0</th>\n",
       "      <th>3/22/2018</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>003</th>\n",
       "      <th>1.0</th>\n",
       "      <th>6/30/2015</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">004</th>\n",
       "      <th>1.0</th>\n",
       "      <th>2/19/2018</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.0</th>\n",
       "      <th>9/8/2017</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               score                              \n",
       "Item                         Fatigue Pain in extremity Weight loss\n",
       "patientnummer rank date                                           \n",
       "001           1.0  6/30/2015     0.0               1.0         1.0\n",
       "              2.0  6/30/2016     1.0               NaN         NaN\n",
       "002           1.0  2/11/2019     1.0               1.0         NaN\n",
       "              2.0  3/22/2018     0.0               NaN         NaN\n",
       "003           1.0  6/30/2015     NaN               NaN         1.0\n",
       "004           1.0  2/19/2018     1.0               NaN         NaN\n",
       "              2.0  9/8/2017      0.0               NaN         1.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = new_df.pivot_table(index=['patientnummer','rank','date' ],columns=['Item'],\n",
    "                     values=['score'], aggfunc='first')\n",
    "result.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now further flatten this resultset to have 1 row per patient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>patientnummer</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>('score', 'Fatigue')</th>\n",
       "      <th>('score', 'Pain in extremity')</th>\n",
       "      <th>('score', 'Weight loss')</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6/30/2016</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>002</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2/11/2019</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>002</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3/22/2018</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>003</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>004</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2/19/2018</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>004</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9/8/2017</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patientnummer  rank       date  ('score', 'Fatigue')  \\\n",
       "0           001   1.0  6/30/2015                   0.0   \n",
       "1           001   2.0  6/30/2016                   1.0   \n",
       "2           002   1.0  2/11/2019                   1.0   \n",
       "3           002   2.0  3/22/2018                   0.0   \n",
       "4           003   1.0  6/30/2015                   NaN   \n",
       "5           004   1.0  2/19/2018                   1.0   \n",
       "6           004   2.0   9/8/2017                   0.0   \n",
       "\n",
       "   ('score', 'Pain in extremity')  ('score', 'Weight loss')  \n",
       "0                             1.0                       1.0  \n",
       "1                             NaN                       NaN  \n",
       "2                             1.0                       NaN  \n",
       "3                             NaN                       NaN  \n",
       "4                             NaN                       1.0  \n",
       "5                             NaN                       NaN  \n",
       "6                             NaN                       1.0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot_flat = pd.DataFrame(result.to_records())\n",
    "pivot_flat.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>M1_date</th>\n",
       "      <th>M2_date</th>\n",
       "      <th>M1_('score', 'Fatigue')</th>\n",
       "      <th>M2_('score', 'Fatigue')</th>\n",
       "      <th>M1_('score', 'Pain in extremity')</th>\n",
       "      <th>M2_('score', 'Pain in extremity')</th>\n",
       "      <th>M1_('score', 'Weight loss')</th>\n",
       "      <th>M2_('score', 'Weight loss')</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>patientnummer</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>001</th>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>6/30/2016</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002</th>\n",
       "      <td>2/11/2019</td>\n",
       "      <td>3/22/2018</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>003</th>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>004</th>\n",
       "      <td>2/19/2018</td>\n",
       "      <td>9/8/2017</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 M1_date    M2_date  M1_('score', 'Fatigue')  \\\n",
       "patientnummer                                                  \n",
       "001            6/30/2015  6/30/2016                      0.0   \n",
       "002            2/11/2019  3/22/2018                      1.0   \n",
       "003            6/30/2015        NaN                      NaN   \n",
       "004            2/19/2018   9/8/2017                      1.0   \n",
       "\n",
       "               M2_('score', 'Fatigue')  M1_('score', 'Pain in extremity')  \\\n",
       "patientnummer                                                               \n",
       "001                                1.0                                1.0   \n",
       "002                                0.0                                1.0   \n",
       "003                                NaN                                NaN   \n",
       "004                                0.0                                NaN   \n",
       "\n",
       "               M2_('score', 'Pain in extremity')  M1_('score', 'Weight loss')  \\\n",
       "patientnummer                                                                   \n",
       "001                                          NaN                          1.0   \n",
       "002                                          NaN                          NaN   \n",
       "003                                          NaN                          1.0   \n",
       "004                                          NaN                          NaN   \n",
       "\n",
       "               M2_('score', 'Weight loss')  \n",
       "patientnummer                               \n",
       "001                                    NaN  \n",
       "002                                    NaN  \n",
       "003                                    NaN  \n",
       "004                                    1.0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot_flat_unstack = pivot_flat.set_index(['patientnummer', 'rank']).unstack()\n",
    "pivot_flat_unstack.columns = ['M'+str(int(c[1])) +'_'+c[0] for c in pivot_flat_unstack.columns]\n",
    "pivot_flat_unstack.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compare the differences in 2 dataframes based on 2 columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(12, 2)\n",
      "(11, 2)\n"
     ]
    }
   ],
   "source": [
    "# Sample dataframe to be used for this transformation\n",
    "import pandas as pd\n",
    "# assign data of lists.\n",
    "data =  {'patientnummer': ['001', '001', '001', '001','001', '002', '002','002','003','004','004','004'], \n",
    "'ResearchNumber': [1,2,3,4,4,6,6,7,8,9,9,10]}\n",
    "# Create DataFrame.\n",
    "patient_df_sunday = pd.DataFrame(data)\n",
    "print(patient_df_sunday.shape)\n",
    "\n",
    "# assign data of lists.\n",
    "data2 =  {'patientnummer': ['001', '001', '001', '001','005',  '002', '002','002','003','004','004'], \n",
    "'ResearchNumber': [1,2,3,4,11, 6,12,7,8,9,9]}\n",
    "# Create DataFrame.\n",
    "# Create DataFrame.\n",
    "patient_df_monday = pd.DataFrame(data2)\n",
    "\n",
    "print(patient_df_monday.shape)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>patientnummer</th>\n",
       "      <th>ResearchNumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patientnummer  ResearchNumber\n",
       "0           001               1\n",
       "1           001               2\n",
       "2           001               3\n",
       "3           001               4\n",
       "4           001               4"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patient_df_sunday.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>patientnummer</th>\n",
       "      <th>ResearchNumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>005</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patientnummer  ResearchNumber\n",
       "0           001               1\n",
       "1           001               2\n",
       "2           001               3\n",
       "3           001               4\n",
       "4           005              11"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patient_df_monday.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sort in an ascending order\n",
    "patient_df_sunday.sort_values(by=['patientnummer','ResearchNumber'], inplace=True)\n",
    "# sort in an ascending order\n",
    "patient_df_monday.sort_values(by=['patientnummer','ResearchNumber'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "patient_df_sunday['VER'] = 'OLD'\n",
    "patient_df_monday['VER'] = 'NEW'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print the difference between the 2 dataframes based on 2 columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>patientnummer</th>\n",
       "      <th>ResearchNumber</th>\n",
       "      <th>VER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>004</td>\n",
       "      <td>10</td>\n",
       "      <td>OLD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>002</td>\n",
       "      <td>12</td>\n",
       "      <td>NEW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>005</td>\n",
       "      <td>11</td>\n",
       "      <td>NEW</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   patientnummer  ResearchNumber  VER\n",
       "11           004              10  OLD\n",
       "18           002              12  NEW\n",
       "22           005              11  NEW"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([patient_df_sunday, patient_df_monday], ignore_index=True).drop_duplicates(subset=['patientnummer', 'ResearchNumber'], keep=False).head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10-11-2020\n",
      "20-07-2021\n",
      "11-10-2020\n",
      "06-07-2021\n",
      "09-04-2013\n",
      "11-02-2021\n",
      "30-06-2015\n"
     ]
    }
   ],
   "source": [
    "# Change date from python format of yy-mm-dd to dd-mm-yyyy for spss\n",
    "from datetime import datetime as dt\n",
    "\n",
    "def reformatDate(x):\n",
    "    \n",
    "    try:\n",
    "        if x is pd.NaT :\n",
    "            return x\n",
    "        if x is np.nan:\n",
    "            return x\n",
    "        if len(str(x))== 0:\n",
    "            return x\n",
    "            \n",
    "        original_date = x #str(x)\n",
    "        sep = str(x)[2]\n",
    "        if sep == '/': \n",
    "            #%y is a 2-digit year, %Y is 4-digit year.\n",
    "            #Check if the year is 2 digit or 4 digit\n",
    "            if len(x.split(\"/\")[-1])==2:\n",
    "                return datetime.datetime.strptime(original_date, '%d/%m/%y').strftime('%d-%m-%Y')\n",
    "            else :\n",
    "                return datetime.datetime.strptime(original_date, '%d/%m/%Y').strftime('%d-%m-%Y')\n",
    "        elif sep == '-':#date in right format, return as it is \n",
    "            return original_date\n",
    "        else:\n",
    "            sep = str(x)[4]\n",
    "            if sep == '-' :\n",
    "                return pd.to_datetime(original_date, format='%Y-%m-%d %H:%M:%S').strftime('%d-%m-%Y')\n",
    "            elif '/' in str(x): # for m/dd/yyy format date\n",
    "                return pd.to_datetime(original_date, dayfirst=False).strftime('%d-%m-%Y')              \n",
    "            else:\n",
    "                return original_date\n",
    "            \n",
    "        #original_date = datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S')\n",
    "        #formatted_date = original_date.strftime(\"%d-%m-%Y\")\n",
    "        #return formatted_date\n",
    "        #original_date = pd.to_datetime(x, '%Y-%m-%d %H:%M:%S')\n",
    "        #formatted_date = original_date.dt.strftime('%d-%m-%Y')\n",
    "        #return formatted_date\n",
    "    except Exception as e: \n",
    "        print(x)\n",
    "        print(e)        \n",
    "        return original_date\n",
    "    \n",
    "#dtm = lambda x: dt.strptime(str(x), \"%d-%m-%Y\")\n",
    "#dfCSV[\"incdat\"] = dfCSV[\"incdat\"].apply(dtm) dont to conversion now as date substraction wont work later\n",
    "print(reformatDate(pd.to_datetime('2020-11-10')))\n",
    "print(reformatDate(pd.to_datetime('2021-07-20 22:00:00')))\n",
    "print(reformatDate(pd.to_datetime('10/11/2020')))\n",
    "print(reformatDate(pd.to_datetime('07-06-2021')))\n",
    "print(reformatDate(pd.to_datetime('04/09/2013')))\n",
    "print(reformatDate('11/02/21'))#%y is a 2-digit year, %Y is 4-digit year.\n",
    "print(reformatDate('6/30/2015'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>patientnummer</th>\n",
       "      <th>date</th>\n",
       "      <th>Item</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Fracture</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Weight loss</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Pain in extremity</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2016</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patientnummer       date               Item  score\n",
       "0           001  6/30/2015           Fracture      1\n",
       "1           001  6/30/2015            Fatigue      0\n",
       "2           001  6/30/2015        Weight loss      1\n",
       "3           001  6/30/2015  Pain in extremity      1\n",
       "4           001  6/30/2016            Fatigue      1"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data =  {'patientnummer': ['001', '001', '001', '001','001', '002', '002','002','003','004','004','004'], \n",
    "'date': ['6/30/2015','6/30/2015','6/30/2015','6/30/2015','6/30/2016','3/22/2018','2/11/2019','2/11/2019','6/30/2015','9/8/2017','9/8/2017','2/19/2018'],\n",
    "'Item':['Fracture','Fatigue','Weight loss','Pain in extremity','Fatigue','Fatigue','Pain in extremity','Fatigue','Weight loss','Fatigue','Weight loss','Fatigue'],'score':[1,0,1,1,1,0,1,1,1,0,1,1]}\n",
    "# Create DataFrame.\n",
    "patient_df = pd.DataFrame(data)\n",
    "patient_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>patientnummer</th>\n",
       "      <th>date</th>\n",
       "      <th>Item</th>\n",
       "      <th>score</th>\n",
       "      <th>date_reformed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Fracture</td>\n",
       "      <td>1</td>\n",
       "      <td>30-06-2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>0</td>\n",
       "      <td>30-06-2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Weight loss</td>\n",
       "      <td>1</td>\n",
       "      <td>30-06-2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2015</td>\n",
       "      <td>Pain in extremity</td>\n",
       "      <td>1</td>\n",
       "      <td>30-06-2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001</td>\n",
       "      <td>6/30/2016</td>\n",
       "      <td>Fatigue</td>\n",
       "      <td>1</td>\n",
       "      <td>30-06-2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  patientnummer       date               Item  score date_reformed\n",
       "0           001  6/30/2015           Fracture      1    30-06-2015\n",
       "1           001  6/30/2015            Fatigue      0    30-06-2015\n",
       "2           001  6/30/2015        Weight loss      1    30-06-2015\n",
       "3           001  6/30/2015  Pain in extremity      1    30-06-2015\n",
       "4           001  6/30/2016            Fatigue      1    30-06-2016"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "patient_df[\"date_reformed\"] = patient_df[\"date\"].apply(reformatDate)\n",
    "patient_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "ad2bdc8ecc057115af97d19610ffacc2b4e99fae6737bb82f5d7fb13d2f2c186"
  },
  "kernelspec": {
   "display_name": "Python 3.6.5 ('base')",
   "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.6.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}