{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-15T09:53:01.873561Z",
     "start_time": "2021-03-15T09:53:01.869097Z"
    }
   },
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.114125Z",
     "start_time": "2021-04-05T16:55:08.016250Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load Data\n",
    "\n",
    "We will work with data from the Audit table. For sake of simplicity, the rows from the SQL table have been exported in an Excel file. Also, only a subset of the audit has been exported."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.314873Z",
     "start_time": "2021-04-05T16:55:10.117939Z"
    }
   },
   "outputs": [],
   "source": [
    "# Load Audit Excel File\n",
    "audit = pd.concat(pd.read_excel('AuditTableData.xlsx', sheet_name=None), ignore_index=True)\n",
    "\n",
    "# Set Datetime column\n",
    "audit.CreatedOn = pd.to_datetime(audit.CreatedOn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.403917Z",
     "start_time": "2021-04-05T16:55:10.321131Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape: (10, 15)\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>CallingUserId</th>\n",
       "      <th>UserId</th>\n",
       "      <th>CreatedOn</th>\n",
       "      <th>TransactionId</th>\n",
       "      <th>ChangeData</th>\n",
       "      <th>Action</th>\n",
       "      <th>Operation</th>\n",
       "      <th>ObjectId</th>\n",
       "      <th>AuditId</th>\n",
       "      <th>AttributeMask</th>\n",
       "      <th>ObjectTypeCode</th>\n",
       "      <th>ObjectIdName</th>\n",
       "      <th>UserAdditionalInfo</th>\n",
       "      <th>RegardingObjectId</th>\n",
       "      <th>RegardingObjectIdName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>52C11204-6A97-E611-80EF-005056955350</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>BC254F36-401F-EB11-814A-005056953107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "      <td>65486436-401F-EB11-8140-005056956E2F</td>\n",
       "      <td>,10647,10059,10529,10106,10088,131,10239,10732...</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>52C11204-6A97-E611-80EF-005056955350</td>\n",
       "      <td>2020-11-05 08:24:27</td>\n",
       "      <td>6E6D4551-401F-EB11-814A-005056953107</td>\n",
       "      <td>drs_drs_xx_preferences_contact~drs_xx_preferen...</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "      <td>57554051-401F-EB11-8140-005056956E2F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>52C11204-6A97-E611-80EF-005056955350</td>\n",
       "      <td>2020-11-05 08:25:00</td>\n",
       "      <td>081BEC64-401F-EB11-814A-005056953107</td>\n",
       "      <td>drs_drs_xx_preferences_contact~drs_xx_preferen...</td>\n",
       "      <td>34</td>\n",
       "      <td>2</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "      <td>2401E764-401F-EB11-8140-005056956E2F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>F048EF85-06BC-E811-8106-005056953107</td>\n",
       "      <td>2020-11-18 07:36:45</td>\n",
       "      <td>EB74CDCE-7029-EB11-814C-005056953107</td>\n",
       "      <td>~804170001</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "      <td>A1A9B9CE-7029-EB11-8143-005056956E2F</td>\n",
       "      <td>,10740,10205,</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>F048EF85-06BC-E811-8106-005056953107</td>\n",
       "      <td>2020-11-18 07:36:52</td>\n",
       "      <td>EC74CDCE-7029-EB11-814C-005056953107</td>\n",
       "      <td>~~804170001</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "      <td>A2A9B9CE-7029-EB11-8143-005056956E2F</td>\n",
       "      <td>,10661,10662,10205,</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CallingUserId                                UserId           CreatedOn  \\\n",
       "0            NaN  52C11204-6A97-E611-80EF-005056955350 2020-11-05 08:23:42   \n",
       "1            NaN  52C11204-6A97-E611-80EF-005056955350 2020-11-05 08:24:27   \n",
       "2            NaN  52C11204-6A97-E611-80EF-005056955350 2020-11-05 08:25:00   \n",
       "3            NaN  F048EF85-06BC-E811-8106-005056953107 2020-11-18 07:36:45   \n",
       "4            NaN  F048EF85-06BC-E811-8106-005056953107 2020-11-18 07:36:52   \n",
       "\n",
       "                          TransactionId  \\\n",
       "0  BC254F36-401F-EB11-814A-005056953107   \n",
       "1  6E6D4551-401F-EB11-814A-005056953107   \n",
       "2  081BEC64-401F-EB11-814A-005056953107   \n",
       "3  EB74CDCE-7029-EB11-814C-005056953107   \n",
       "4  EC74CDCE-7029-EB11-814C-005056953107   \n",
       "\n",
       "                                          ChangeData  Action  Operation  \\\n",
       "0                                                NaN       1          1   \n",
       "1  drs_drs_xx_preferences_contact~drs_xx_preferen...      33          2   \n",
       "2  drs_drs_xx_preferences_contact~drs_xx_preferen...      34          2   \n",
       "3                                         ~804170001       2          2   \n",
       "4                                        ~~804170001       2          2   \n",
       "\n",
       "                               ObjectId                               AuditId  \\\n",
       "0  BB254F36-401F-EB11-814A-005056953107  65486436-401F-EB11-8140-005056956E2F   \n",
       "1  BB254F36-401F-EB11-814A-005056953107  57554051-401F-EB11-8140-005056956E2F   \n",
       "2  BB254F36-401F-EB11-814A-005056953107  2401E764-401F-EB11-8140-005056956E2F   \n",
       "3  BB254F36-401F-EB11-814A-005056953107  A1A9B9CE-7029-EB11-8143-005056956E2F   \n",
       "4  BB254F36-401F-EB11-814A-005056953107  A2A9B9CE-7029-EB11-8143-005056956E2F   \n",
       "\n",
       "                                       AttributeMask  ObjectTypeCode  \\\n",
       "0  ,10647,10059,10529,10106,10088,131,10239,10732...               2   \n",
       "1                                                NaN               2   \n",
       "2                                                NaN               2   \n",
       "3                                      ,10740,10205,               2   \n",
       "4                                ,10661,10662,10205,               2   \n",
       "\n",
       "   ObjectIdName  UserAdditionalInfo  RegardingObjectId  RegardingObjectIdName  \n",
       "0           NaN                 NaN                NaN                    NaN  \n",
       "1           NaN                 NaN                NaN                    NaN  \n",
       "2           NaN                 NaN                NaN                    NaN  \n",
       "3           NaN                 NaN                NaN                    NaN  \n",
       "4           NaN                 NaN                NaN                    NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print Data\n",
    "print(\"Shape: \" + str(audit.shape))\n",
    "audit.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deal with the *AttributeMaskColumn* and *ChangeData* columns\n",
    "\n",
    "The first thing to do is to work with the *AttributeMasCustom* and the *ChangeData* columns. As explaine in Part I, these two columns contain the attributes and old values before the change.\n",
    "\n",
    "Therefore, we will start by creating a new column on the dataset, a column that will combine a field and its old value.\n",
    "\n",
    "> For the 'Create' event, there is no old value. Therefore the value \"OnCreation\" will be added\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.411879Z",
     "start_time": "2021-04-05T16:55:10.406919Z"
    }
   },
   "outputs": [],
   "source": [
    "# Remove the first and last comma in the mask column\n",
    "audit[\"AttributeMaskCustom\"] = audit.AttributeMask.str[1:-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.428258Z",
     "start_time": "2021-04-05T16:55:10.416223Z"
    }
   },
   "outputs": [],
   "source": [
    "# Split the items to have \"Field###Value\"\n",
    "audit[\"A\"] = audit[\"AttributeMaskCustom\"].str.split(',').fillna('')\n",
    "audit[\"B\"] = audit[\"ChangeData\"].str.split('~').fillna(' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.439301Z",
     "start_time": "2021-04-05T16:55:10.431504Z"
    }
   },
   "outputs": [],
   "source": [
    "# will create a new column with: Field###Old_Value!!!Date???\n",
    "def mergelines(data,i):\n",
    "    v = \"\"\n",
    "    for j in range(i):\n",
    "        v_1 = data.A[j]\n",
    "        v_2 = data.B[j] if data.B[0] != \" \" else \"OnCreation\"\n",
    "        v_3 = str(data.CreatedOn)\n",
    "        v += v_1 + \"###\"+ v_2 + \"!!!\" + v_3 + \"???\"\n",
    "    return v[:-3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.477936Z",
     "start_time": "2021-04-05T16:55:10.442843Z"
    }
   },
   "outputs": [],
   "source": [
    "audit[\"values_merges\"] = audit.apply(lambda x : mergelines(x,len(x.A)),axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging line per field and per event\n",
    "\n",
    "Let's create a new dataframe with only two columns:\n",
    "\n",
    "* The first column will contains the reference to a field, it's old value, and the date and time when the change occured. \n",
    "* The second column will contain the GUID of the AuditId, in order to retrieve which changes have been saved together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.519883Z",
     "start_time": "2021-04-05T16:55:10.481849Z"
    }
   },
   "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>v</th>\n",
       "      <th>i</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10647###OnCreation!!!2020-11-05 08:23:42</td>\n",
       "      <td>65486436-401F-EB11-8140-005056956E2F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10059###OnCreation!!!2020-11-05 08:23:42</td>\n",
       "      <td>65486436-401F-EB11-8140-005056956E2F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10529###OnCreation!!!2020-11-05 08:23:42</td>\n",
       "      <td>65486436-401F-EB11-8140-005056956E2F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10106###OnCreation!!!2020-11-05 08:23:42</td>\n",
       "      <td>65486436-401F-EB11-8140-005056956E2F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10088###OnCreation!!!2020-11-05 08:23:42</td>\n",
       "      <td>65486436-401F-EB11-8140-005056956E2F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>10205###804170001!!!2021-03-25 15:55:12</td>\n",
       "      <td>0639DB7A-828D-EB11-8149-005056956E2F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>42###!!!2021-03-31 10:05:11</td>\n",
       "      <td>86F7E593-0892-EB11-8149-005056956E2F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>10205###804170001!!!2021-03-31 10:05:11</td>\n",
       "      <td>86F7E593-0892-EB11-8149-005056956E2F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>42###danny@gmail.com!!!2021-03-31 10:05:16</td>\n",
       "      <td>87F7E593-0892-EB11-8149-005056956E2F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>10205###804170001!!!2021-03-31 10:05:16</td>\n",
       "      <td>87F7E593-0892-EB11-8149-005056956E2F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>248 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              v  \\\n",
       "0      10647###OnCreation!!!2020-11-05 08:23:42   \n",
       "1      10059###OnCreation!!!2020-11-05 08:23:42   \n",
       "2      10529###OnCreation!!!2020-11-05 08:23:42   \n",
       "3      10106###OnCreation!!!2020-11-05 08:23:42   \n",
       "4      10088###OnCreation!!!2020-11-05 08:23:42   \n",
       "..                                          ...   \n",
       "243     10205###804170001!!!2021-03-25 15:55:12   \n",
       "244                 42###!!!2021-03-31 10:05:11   \n",
       "245     10205###804170001!!!2021-03-31 10:05:11   \n",
       "246  42###danny@gmail.com!!!2021-03-31 10:05:16   \n",
       "247     10205###804170001!!!2021-03-31 10:05:16   \n",
       "\n",
       "                                        i  \n",
       "0    65486436-401F-EB11-8140-005056956E2F  \n",
       "1    65486436-401F-EB11-8140-005056956E2F  \n",
       "2    65486436-401F-EB11-8140-005056956E2F  \n",
       "3    65486436-401F-EB11-8140-005056956E2F  \n",
       "4    65486436-401F-EB11-8140-005056956E2F  \n",
       "..                                    ...  \n",
       "243  0639DB7A-828D-EB11-8149-005056956E2F  \n",
       "244  86F7E593-0892-EB11-8149-005056956E2F  \n",
       "245  86F7E593-0892-EB11-8149-005056956E2F  \n",
       "246  87F7E593-0892-EB11-8149-005056956E2F  \n",
       "247  87F7E593-0892-EB11-8149-005056956E2F  \n",
       "\n",
       "[248 rows x 2 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_cc = pd.concat([pd.Series(row['AuditId'], row['values_merges'].split('???')) for _, row in audit.iterrows()]).reset_index()\n",
    "new_cc.columns = [\"v\",\"i\"]\n",
    "new_cc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.538893Z",
     "start_time": "2021-04-05T16:55:10.525225Z"
    }
   },
   "outputs": [],
   "source": [
    "# Now, we can merge back this new information within the entire dataframe\n",
    "all_data = new_cc.join(audit.set_index('AuditId'), on='i')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the moment, there are multiple fields per line. Now we make use of the '###' separator to spli each line and have only one field per line."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.576702Z",
     "start_time": "2021-04-05T16:55:10.542883Z"
    }
   },
   "outputs": [],
   "source": [
    "all_data[['v1','v1_1']] = all_data.v.str.split(\"###\",expand=True,)\n",
    "all_data[['Old Value','v3']] = all_data.v1_1.str.split(\"!!!\",expand=True,)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a result we now have a table where for each line there is only one field"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:10.616586Z",
     "start_time": "2021-04-05T16:55:10.579357Z"
    }
   },
   "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>v1</th>\n",
       "      <th>v3</th>\n",
       "      <th>CreatedOn</th>\n",
       "      <th>Old Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10647</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>OnCreation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10059</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>OnCreation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10529</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>OnCreation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10106</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>OnCreation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10088</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>2020-11-05 08:23:42</td>\n",
       "      <td>OnCreation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>10205</td>\n",
       "      <td>2021-03-25 15:55:12</td>\n",
       "      <td>2021-03-25 15:55:12</td>\n",
       "      <td>804170001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>42</td>\n",
       "      <td>2021-03-31 10:05:11</td>\n",
       "      <td>2021-03-31 10:05:11</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>10205</td>\n",
       "      <td>2021-03-31 10:05:11</td>\n",
       "      <td>2021-03-31 10:05:11</td>\n",
       "      <td>804170001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>42</td>\n",
       "      <td>2021-03-31 10:05:16</td>\n",
       "      <td>2021-03-31 10:05:16</td>\n",
       "      <td>danny@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>10205</td>\n",
       "      <td>2021-03-31 10:05:16</td>\n",
       "      <td>2021-03-31 10:05:16</td>\n",
       "      <td>804170001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>248 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        v1                   v3           CreatedOn        Old Value\n",
       "0    10647  2020-11-05 08:23:42 2020-11-05 08:23:42       OnCreation\n",
       "1    10059  2020-11-05 08:23:42 2020-11-05 08:23:42       OnCreation\n",
       "2    10529  2020-11-05 08:23:42 2020-11-05 08:23:42       OnCreation\n",
       "3    10106  2020-11-05 08:23:42 2020-11-05 08:23:42       OnCreation\n",
       "4    10088  2020-11-05 08:23:42 2020-11-05 08:23:42       OnCreation\n",
       "..     ...                  ...                 ...              ...\n",
       "243  10205  2021-03-25 15:55:12 2021-03-25 15:55:12        804170001\n",
       "244     42  2021-03-31 10:05:11 2021-03-31 10:05:11                 \n",
       "245  10205  2021-03-31 10:05:11 2021-03-31 10:05:11        804170001\n",
       "246     42  2021-03-31 10:05:16 2021-03-31 10:05:16  danny@gmail.com\n",
       "247  10205  2021-03-31 10:05:16 2021-03-31 10:05:16        804170001\n",
       "\n",
       "[248 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data[[\"v1\",\"v3\",\"CreatedOn\",\"Old Value\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fields mapping\n",
    "\n",
    "Inside the AttributeMask column, each integer corresponds to the ColumnNumber metadata property of attributes that have been updated. Those information are stored in the MetadataSchema.Attribute SQL table.\n",
    "\n",
    "Again, for sake of simplicity here, this data will not be retrieve directly in SQL, but in an Excel file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-16T20:56:30.862703Z",
     "start_time": "2021-03-16T20:56:30.851689Z"
    }
   },
   "source": [
    "```sql\n",
    "SELECT ar.name,ar.ColumnNumber \n",
    "FROM MetadataSchema.Attribute ar INNER JOIN \n",
    "  MetadataSchema.Entity en ON ar.EntityId = en.EntityId\n",
    "WHERE en.ObjectTypeCode=2\n",
    "``` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.045240Z",
     "start_time": "2021-04-05T16:55:10.622123Z"
    }
   },
   "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>name</th>\n",
       "      <th>ColumnNumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>accountid</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>accountiddsc</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>accountidname</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>accountidyominame</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>accountrolecode</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>accountrolecode</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>accountrolecodename</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>address1_addressid</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>address1_addresstypecode</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>address1_addresstypecode</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       name  ColumnNumber\n",
       "0                 accountid            14\n",
       "1              accountiddsc            86\n",
       "2             accountidname            85\n",
       "3         accountidyominame           219\n",
       "4           accountrolecode            56\n",
       "5           accountrolecode            56\n",
       "6       accountrolecodename           158\n",
       "7        address1_addressid            89\n",
       "8  address1_addresstypecode            90\n",
       "9  address1_addresstypecode            90"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contact_attributeMask = pd.read_excel(\"Contact_AttributeMask.xlsx\")\n",
    "contact_attributeMask.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can map the AttribueMask ColumnNumber to their field's technical names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.067903Z",
     "start_time": "2021-04-05T16:55:11.049384Z"
    }
   },
   "outputs": [],
   "source": [
    "attributeMaskeDict = pd.Series(contact_attributeMask.name.values,index=contact_attributeMask.ColumnNumber.apply(str)).to_dict()\n",
    "all_data[\"Field\"] = all_data.v1.map(attributeMaskeDict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add the 'Event' column via 'Action' mapping\n",
    "\n",
    "Inside the Event column, each integer corresponds to the an unique event. It's an integer representing what kind of action has been performed on the record (e.g: Create, Update, Deactivate, Add Member,  ...).\n",
    "\n",
    "Again, for sake of simplicity here, this data will not be retrieve directly in SQL, but in an Excel file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT Value as Action, AttributeValue as ActionValue\n",
    "FROM StringMap \n",
    "WHERE AttributeName='action' and LangId = '1033'\n",
    "``` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.115707Z",
     "start_time": "2021-04-05T16:55:11.070895Z"
    }
   },
   "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>Action</th>\n",
       "      <th>ActionValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Create</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Update</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Delete</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Activate</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Deactivate</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cascade</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Merge</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Assign</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Share</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Action  ActionValue\n",
       "0     Unknown            0\n",
       "1      Create            1\n",
       "2      Update            2\n",
       "3      Delete            3\n",
       "4    Activate            4\n",
       "5  Deactivate            5\n",
       "6     Cascade           11\n",
       "7       Merge           12\n",
       "8      Assign           13\n",
       "9       Share           14"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "action_mapping = pd.read_excel(\"action_mapping.xlsx\")\n",
    "action_mapping.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.132211Z",
     "start_time": "2021-04-05T16:55:11.120218Z"
    }
   },
   "outputs": [],
   "source": [
    "actionMaskDict = pd.Series(action_mapping.Action.values,index=action_mapping.ActionValue).to_dict()\n",
    "all_data[\"Event\"] = all_data.Action.map(actionMaskDict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Users mapping\n",
    "\n",
    "Similar to the fields mapping, we can map the name of the user that changed the data.\n",
    "\n",
    "To change relatively to the Excel file, the data is load via a JSON file that has been retrieved with the Web API via URL `/api/data/v8.2/systemusers?$select=fullname,systemuserid`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.147479Z",
     "start_time": "2021-04-05T16:55:11.136223Z"
    }
   },
   "outputs": [],
   "source": [
    "# Opening JSON file \n",
    "with open('systemusers.json', encoding=\"utf8\") as json_file: \n",
    "    systemusers = json.load(json_file) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.162891Z",
     "start_time": "2021-04-05T16:55:11.151783Z"
    }
   },
   "outputs": [],
   "source": [
    "# From the JSON file, create a dictionary GUID -> Fullname\n",
    "df = pd.DataFrame.from_dict(systemusers['value'])\n",
    "df.index = df.systemuserid.str.upper()\n",
    "users = df[\"fullname\"].to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.178480Z",
     "start_time": "2021-04-05T16:55:11.167922Z"
    }
   },
   "outputs": [],
   "source": [
    "# Map it to the data\n",
    "all_data[\"User\"] = all_data.UserId.map(users)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add current values\n",
    "\n",
    "As explained in the Part I, a major drawback of the way Dynamics 365 deals with the audit is that it only stored the previous value, but not the new one.\n",
    "\n",
    "To complete our dataset, we will retrieve via the Web API all fields of a given contact and add it to the *AuditTable* rows. It will enable us to have the complete history of the audit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.194714Z",
     "start_time": "2021-04-05T16:55:11.181162Z"
    }
   },
   "outputs": [],
   "source": [
    "# Opening JSON file \n",
    "with open('contacts(BB254F36-401F-EB11-814A-005056953107).json', encoding=\"utf8\") as json_file: \n",
    "    contact = json.load(json_file) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.210379Z",
     "start_time": "2021-04-05T16:55:11.195711Z"
    }
   },
   "outputs": [],
   "source": [
    "df_contact = pd.DataFrame(contact.items(), columns=['Field','Old Value'])\n",
    "df_contact[\"User\"] = None\n",
    "df_contact[\"Event\"] = \"CURRENT_VALUE\"\n",
    "df_contact[\"ObjectId\"] = contact['contactid'].upper()\n",
    "df_contact[\"CreatedOn\"] = pd.Timestamp.today()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.235830Z",
     "start_time": "2021-04-05T16:55:11.214790Z"
    }
   },
   "outputs": [],
   "source": [
    "all_data = all_data.append(df_contact)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OptionSet Mappings\n",
    "\n",
    "In the AuditTable rows, option set are represented with the value of the option set, not with the name. In the code below we will map each option set value to it's label."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT Value as OptionName, AttributeValue as OptionValue, AttributeName as FieldName\n",
    "FROM StringMap \n",
    "WHERE ObjectTypeCode = 1\n",
    "``` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.388079Z",
     "start_time": "2021-04-05T16:55:11.238189Z"
    }
   },
   "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>OptionName</th>\n",
       "      <th>OptionValue</th>\n",
       "      <th>FieldName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Analysé, sans autocertification</td>\n",
       "      <td>3</td>\n",
       "      <td>drs_statutautocertification</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Typologie à clarifier</td>\n",
       "      <td>804170002</td>\n",
       "      <td>drs_statutdyn365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Client existant à clarifier (Typologie)</td>\n",
       "      <td>7</td>\n",
       "      <td>drs_contexteprocessdyn365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Client existant à autocertifier (ADE)</td>\n",
       "      <td>8</td>\n",
       "      <td>drs_contexteprocessdyn365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Changement de circonstance à autocertifier (ADE)</td>\n",
       "      <td>9</td>\n",
       "      <td>drs_contexteprocessdyn365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Non</td>\n",
       "      <td>0</td>\n",
       "      <td>drs_technical_isemailsent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Oui</td>\n",
       "      <td>1</td>\n",
       "      <td>drs_technical_isemailsent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Non</td>\n",
       "      <td>0</td>\n",
       "      <td>drs_technical_isresidencefiscalemailsent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Oui</td>\n",
       "      <td>1</td>\n",
       "      <td>drs_technical_isresidencefiscalemailsent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Non</td>\n",
       "      <td>0</td>\n",
       "      <td>drs_technical_istaskcreatedafter67days</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         OptionName  OptionValue  \\\n",
       "0                   Analysé, sans autocertification            3   \n",
       "1                             Typologie à clarifier    804170002   \n",
       "2           Client existant à clarifier (Typologie)            7   \n",
       "3             Client existant à autocertifier (ADE)            8   \n",
       "4  Changement de circonstance à autocertifier (ADE)            9   \n",
       "5                                               Non            0   \n",
       "6                                               Oui            1   \n",
       "7                                               Non            0   \n",
       "8                                               Oui            1   \n",
       "9                                               Non            0   \n",
       "\n",
       "                                  FieldName  \n",
       "0               drs_statutautocertification  \n",
       "1                             drs_statutdyn365  \n",
       "2                    drs_contexteprocessdyn365  \n",
       "3                    drs_contexteprocessdyn365  \n",
       "4                    drs_contexteprocessdyn365  \n",
       "5                 drs_technical_isemailsent  \n",
       "6                 drs_technical_isemailsent  \n",
       "7  drs_technical_isresidencefiscalemailsent  \n",
       "8  drs_technical_isresidencefiscalemailsent  \n",
       "9    drs_technical_istaskcreatedafter67days  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "optionset_mapping = pd.read_excel(\"Contact_OptionSetsMapping.xlsx\")\n",
    "optionset_mapping.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.481708Z",
     "start_time": "2021-04-05T16:55:11.394951Z"
    }
   },
   "outputs": [],
   "source": [
    "# Create an unique value merging the technical name of a field and its option set value.\n",
    "optionset_mapping['MapValue'] = optionset_mapping.FieldName + \"###\" + optionset_mapping.OptionValue.astype(str)\n",
    "\n",
    "# Transform it into a Dictionary\n",
    "optionSetMaskDict = pd.Series(optionset_mapping.OptionName.values,index=optionset_mapping.MapValue).to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.500466Z",
     "start_time": "2021-04-05T16:55:11.485290Z"
    }
   },
   "outputs": [],
   "source": [
    "# Create the same unique value by merging field technical name and its option set value. \n",
    "all_data['OptionSetMapping'] = all_data.Field.astype(str) + \"###\" + all_data['Old Value'].astype(str)\n",
    "\n",
    "# Mapping\n",
    "all_data[\"OldValueOptionSetName\"] = all_data.OptionSetMapping.map(optionSetMaskDict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.515681Z",
     "start_time": "2021-04-05T16:55:11.503460Z"
    }
   },
   "outputs": [],
   "source": [
    "# If the field is an Option Set, display the label into 'Old Value' column\n",
    "all_data['Old Value'] = np.where(all_data.OldValueOptionSetName.isnull(), all_data['Old Value'], all_data.OldValueOptionSetName)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Final data\n",
    "\n",
    "After all this transformations, the data looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:11.565849Z",
     "start_time": "2021-04-05T16:55:11.520695Z"
    },
    "scrolled": false
   },
   "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>CreatedOn</th>\n",
       "      <th>User</th>\n",
       "      <th>Event</th>\n",
       "      <th>Field</th>\n",
       "      <th>Old Value</th>\n",
       "      <th>ObjectId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-11-05 08:23:42.000000</td>\n",
       "      <td>Lloyd Sebag</td>\n",
       "      <td>Create</td>\n",
       "      <td>dada_webregistration</td>\n",
       "      <td>OnCreation</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-11-05 08:23:42.000000</td>\n",
       "      <td>Lloyd Sebag</td>\n",
       "      <td>Create</td>\n",
       "      <td>drs_donationaudernierdesvivants</td>\n",
       "      <td>OnCreation</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-11-05 08:23:42.000000</td>\n",
       "      <td>Lloyd Sebag</td>\n",
       "      <td>Create</td>\n",
       "      <td>drs_nepasanonymiser</td>\n",
       "      <td>OnCreation</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-11-05 08:23:42.000000</td>\n",
       "      <td>Lloyd Sebag</td>\n",
       "      <td>Create</td>\n",
       "      <td>drs_isepargnecadeau</td>\n",
       "      <td>OnCreation</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-11-05 08:23:42.000000</td>\n",
       "      <td>Lloyd Sebag</td>\n",
       "      <td>Create</td>\n",
       "      <td>drs_imprimercourrierinitial</td>\n",
       "      <td>OnCreation</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>617</th>\n",
       "      <td>2021-04-05 18:55:11.203439</td>\n",
       "      <td>None</td>\n",
       "      <td>CURRENT_VALUE</td>\n",
       "      <td>drs_xxcomplment</td>\n",
       "      <td>None</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>618</th>\n",
       "      <td>2021-04-05 18:55:11.203439</td>\n",
       "      <td>None</td>\n",
       "      <td>CURRENT_VALUE</td>\n",
       "      <td>drs_dyn365statutcode</td>\n",
       "      <td>None</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>619</th>\n",
       "      <td>2021-04-05 18:55:11.203439</td>\n",
       "      <td>None</td>\n",
       "      <td>CURRENT_VALUE</td>\n",
       "      <td>address1_latitude</td>\n",
       "      <td>None</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>620</th>\n",
       "      <td>2021-04-05 18:55:11.203439</td>\n",
       "      <td>None</td>\n",
       "      <td>CURRENT_VALUE</td>\n",
       "      <td>drs_dyn365ventescumules</td>\n",
       "      <td>None</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>621</th>\n",
       "      <td>2021-04-05 18:55:11.203439</td>\n",
       "      <td>None</td>\n",
       "      <td>CURRENT_VALUE</td>\n",
       "      <td>drs_origin</td>\n",
       "      <td>None</td>\n",
       "      <td>BB254F36-401F-EB11-814A-005056953107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>870 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     CreatedOn                 User          Event  \\\n",
       "0   2020-11-05 08:23:42.000000  Lloyd Sebag         Create   \n",
       "1   2020-11-05 08:23:42.000000  Lloyd Sebag         Create   \n",
       "2   2020-11-05 08:23:42.000000  Lloyd Sebag         Create   \n",
       "3   2020-11-05 08:23:42.000000  Lloyd Sebag         Create   \n",
       "4   2020-11-05 08:23:42.000000  Lloyd Sebag         Create   \n",
       "..                         ...                  ...            ...   \n",
       "617 2021-04-05 18:55:11.203439                 None  CURRENT_VALUE   \n",
       "618 2021-04-05 18:55:11.203439                 None  CURRENT_VALUE   \n",
       "619 2021-04-05 18:55:11.203439                 None  CURRENT_VALUE   \n",
       "620 2021-04-05 18:55:11.203439                 None  CURRENT_VALUE   \n",
       "621 2021-04-05 18:55:11.203439                 None  CURRENT_VALUE   \n",
       "\n",
       "                               Field   Old Value  \\\n",
       "0                dada_webregistration  OnCreation   \n",
       "1    drs_donationaudernierdesvivants  OnCreation   \n",
       "2                drs_nepasanonymiser  OnCreation   \n",
       "3                drs_isepargnecadeau  OnCreation   \n",
       "4        drs_imprimercourrierinitial  OnCreation   \n",
       "..                               ...         ...   \n",
       "617                  drs_xxcomplment        None   \n",
       "618                drs_dyn365statutcode        None   \n",
       "619                address1_latitude        None   \n",
       "620             drs_dyn365ventescumules        None   \n",
       "621                       drs_origin        None   \n",
       "\n",
       "                                 ObjectId  \n",
       "0    BB254F36-401F-EB11-814A-005056953107  \n",
       "1    BB254F36-401F-EB11-814A-005056953107  \n",
       "2    BB254F36-401F-EB11-814A-005056953107  \n",
       "3    BB254F36-401F-EB11-814A-005056953107  \n",
       "4    BB254F36-401F-EB11-814A-005056953107  \n",
       "..                                    ...  \n",
       "617  BB254F36-401F-EB11-814A-005056953107  \n",
       "618  BB254F36-401F-EB11-814A-005056953107  \n",
       "619  BB254F36-401F-EB11-814A-005056953107  \n",
       "620  BB254F36-401F-EB11-814A-005056953107  \n",
       "621  BB254F36-401F-EB11-814A-005056953107  \n",
       "\n",
       "[870 rows x 6 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data[[\"CreatedOn\",\"User\",\"Event\",\"Field\",\"Old Value\",\"ObjectId\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, each rows contains the event, the data, the user who performed the action, which field is concerned by the change and the previous field value."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add Display Name\n",
    "\n",
    "One final data mapping we can do is to use the display name of every field instead of the technical name."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT ar.name,ar.ColumnNumber,ll.Label\n",
    "FROM MetadataSchema.Attribute ar INNER JOIN \n",
    "  MetadataSchema.Entity en ON ar.EntityId = en.EntityId INNER JOIN\n",
    "  LocalizedLabelView ll    ON ll.ObjectId = ar.AttributeId\n",
    "WHERE en.ObjectTypeCode=2  and ll.Label != '' and LanguageId = 1036\n",
    "``` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:12.118141Z",
     "start_time": "2021-04-05T16:55:11.570088Z"
    }
   },
   "outputs": [],
   "source": [
    "contact_DisplayName = pd.read_excel(\"Contact_AttributeMask_WithDisplayName.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:12.144865Z",
     "start_time": "2021-04-05T16:55:12.123288Z"
    }
   },
   "outputs": [],
   "source": [
    "# Mapping\n",
    "displayNameMaskeDict = pd.Series(contact_DisplayName.Label.values,index=contact_DisplayName.ColumnNumber.apply(str)).to_dict()\n",
    "all_data[\"Field Display Name\"] = all_data.v1.map(displayNameMaskeDict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retrieving the new value\n",
    "\n",
    "Now that we have all our data ready, we can make the final step : add the 'New Value' column !"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:12.168658Z",
     "start_time": "2021-04-05T16:55:12.150267Z"
    }
   },
   "outputs": [],
   "source": [
    "# Use only the columns of interest\n",
    "final_data = all_data[[\"CreatedOn\",\"User\",\"Event\",\"Field Display Name\",\"Old Value\",\"ObjectId\"]].sort_values('CreatedOn',ascending=False)\n",
    "\n",
    "# Create the 'New Value' columns thanks to the shift method !\n",
    "final_data[\"New Value\"] = final_data.groupby(['ObjectId','Field Display Name'])['Old Value'].shift()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:12.185196Z",
     "start_time": "2021-04-05T16:55:12.173571Z"
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Remove the current value rows, since we only use it for the 'New Value' column of last change per field\n",
    "final_data = final_data[final_data['Event'] != \"CURRENT_VALUE\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-04-05T16:55:12.218668Z",
     "start_time": "2021-04-05T16:55:12.189739Z"
    },
    "scrolled": false
   },
   "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>CreatedOn</th>\n",
       "      <th>User</th>\n",
       "      <th>Event</th>\n",
       "      <th>Field Display Name</th>\n",
       "      <th>Old Value</th>\n",
       "      <th>New Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>2021-03-31 10:05:16</td>\n",
       "      <td>Danny Rodrigues Alves</td>\n",
       "      <td>Update</td>\n",
       "      <td>Statut MEC</td>\n",
       "      <td>Non auto-déclaré</td>\n",
       "      <td>Non auto-déclaré</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>2021-03-31 10:05:16</td>\n",
       "      <td>Danny Rodrigues Alves</td>\n",
       "      <td>Update</td>\n",
       "      <td>Email DadaMail</td>\n",
       "      <td>danny@gmail.com</td>\n",
       "      <td>danny@hotmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>2021-03-31 10:05:11</td>\n",
       "      <td>Danny Rodrigues Alves</td>\n",
       "      <td>Update</td>\n",
       "      <td>Statut MEC</td>\n",
       "      <td>Non auto-déclaré</td>\n",
       "      <td>Non auto-déclaré</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>2021-03-31 10:05:11</td>\n",
       "      <td>Danny Rodrigues Alves</td>\n",
       "      <td>Update</td>\n",
       "      <td>Email DadaMail</td>\n",
       "      <td></td>\n",
       "      <td>danny@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>2021-03-25 15:55:12</td>\n",
       "      <td>Danny Rodrigues Alves</td>\n",
       "      <td>Update</td>\n",
       "      <td>Statut MEC</td>\n",
       "      <td>Non auto-déclaré</td>\n",
       "      <td>Non auto-déclaré</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              CreatedOn                   User   Event Field Display Name  \\\n",
       "247 2021-03-31 10:05:16  Danny Rodrigues Alves  Update         Statut MEC   \n",
       "246 2021-03-31 10:05:16  Danny Rodrigues Alves  Update      Email DadaMail   \n",
       "245 2021-03-31 10:05:11  Danny Rodrigues Alves  Update         Statut MEC   \n",
       "244 2021-03-31 10:05:11  Danny Rodrigues Alves  Update      Email DadaMail   \n",
       "243 2021-03-25 15:55:12  Danny Rodrigues Alves  Update         Statut MEC   \n",
       "\n",
       "            Old Value          New Value  \n",
       "247  Non auto-déclaré   Non auto-déclaré  \n",
       "246   danny@gmail.com  danny@hotmail.com  \n",
       "245  Non auto-déclaré   Non auto-déclaré  \n",
       "244                      danny@gmail.com  \n",
       "243  Non auto-déclaré   Non auto-déclaré  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we compare it to the Audit from Dynamics 365, we can see that it's identical !\n",
    "\n",
    "![AuditFromDynamics.png](AuditFromDynamics.png)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}