pre-processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd\n",
    "import os\n",
    "import datetime\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "#os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['Google cluster-usage traces format schema 2014-11-17 external.pdf',\n",
       " 'machine_events.csv',\n",
       " 'part-01_job_events.csv',\n",
       " 'part-01_task_events.csv',\n",
       " 'part-01_task_usage.csv',\n",
       " 'part-02_job_events.csv',\n",
       " 'part-02_task_events.csv',\n",
       " 'part-02_task_usage.csv',\n",
       " 'part-03_job_events.csv',\n",
       " 'part-03_task_events.csv',\n",
       " 'part-03_task_usage.csv',\n",
       " 'part-04_job_events.csv',\n",
       " 'part-04_task_events.csv',\n",
       " 'part-04_task_usage.csv',\n",
       " 'part-05_job_events.csv',\n",
       " 'part-05_task_events.csv',\n",
       " 'part-05_task_usage.csv',\n",
       " 'part-06_job_events.csv',\n",
       " 'part-06_task_events.csv',\n",
       " 'part-06_task_usage.csv',\n",
       " 'part-07_job_events.csv',\n",
       " 'part-07_task_events.csv',\n",
       " 'part-07_task_usage.csv']"
      ]
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "source": [
    "os.listdir('../Google-Data-411')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_events = [] # hold all data\n",
    "task_events = []\n",
    "task_usage = []\n",
    "\n",
    "os.chdir('../Google-Data-411') # move to data directory\n",
    "\n",
    "for file in os.listdir('../Google-Data-411'): # for each file name in the data directory\n",
    "    if 'job' in file: # if file name has job in it\n",
    "        job_events.append(pd.read_csv(file, header=None)) # append to job events list\n",
    "    elif 'task_events' in file:\n",
    "        task_events.append(pd.read_csv(file, header=None))\n",
    "    elif 'task_usage' in file:\n",
    "        task_usage.append(pd.read_csv(file, header=None))\n",
    "\n",
    "job_events = pd.concat(job_events) # concatenate all this data to be one big dataframe\n",
    "task_events = pd.concat(task_events) \n",
    "task_usage = pd.concat(task_usage)\n",
    "\n",
    "os.chdir('../pre-processing') # move back to working directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "machine_events = pd.read_csv('../Google-Data-411/machine_events.csv', header=None)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Job events dimensions:  (21161, 8)\nTask events dimensions:  (1244596, 13)\nTask usage dimensions:  (17944928, 20)\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "# Output size of these dataframes\n",
    "#\n",
    "\n",
    "print('Job events dimensions: ', job_events.shape)\n",
    "print('Task events dimensions: ', task_events.shape)\n",
    "print('Task usage dimensions: ', task_usage.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Job Events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "          time       jobID  eventType  \\\n",
       "0  20646876178  6252924356          0   \n",
       "1  20647976107  6252809803          3   \n",
       "2  20648680067  6252924356          1   \n",
       "3  20648680072  6252924415          0   \n",
       "4  20648703418  6252897088          4   \n",
       "\n",
       "                                       userName  schedClass  \\\n",
       "0  r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=           2   \n",
       "1  fVUqjeDqXrzcJObPpFT8U1FwH8uBfyRXJQa4zRlwpb4=           1   \n",
       "2  r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=           2   \n",
       "3  r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=           0   \n",
       "4  r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=           2   \n",
       "\n",
       "                                        jobName  \\\n",
       "0  JPCEeQVfAM84156WazlpF2mbNCIH6JNAEdWKADGMW6M=   \n",
       "1  7cLW+6pt2CHJM01krasXKfXt1ArqbEMuwVseGSpclus=   \n",
       "2  JPCEeQVfAM84156WazlpF2mbNCIH6JNAEdWKADGMW6M=   \n",
       "3  /9RMVijX6saTjLFPA6Npk+8JAvFpdgBL7M1O0FMFBf0=   \n",
       "4  pmE/ir4WZoTI/I8Zx1FxhEWDHH5S8IiMxASLJjUfjLA=   \n",
       "\n",
       "                                 logicalJobName  \n",
       "0  G/9E4AW9fSviXbmdFO5BBcjVd49zuI1AIU5gHQJLm+8=  \n",
       "1  kzI7CKSyq7J04F06rtsufIYDPvFxRDHISFXAf2Qav7o=  \n",
       "2  G/9E4AW9fSviXbmdFO5BBcjVd49zuI1AIU5gHQJLm+8=  \n",
       "3  QdrSScSXyIpoqR4skT9QFLlVShlxNDTFRBhIWZRq2SM=  \n",
       "4  G/9E4AW9fSviXbmdFO5BBcjVd49zuI1AIU5gHQJLm+8=  "
      ],
      "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>time</th>\n      <th>jobID</th>\n      <th>eventType</th>\n      <th>userName</th>\n      <th>schedClass</th>\n      <th>jobName</th>\n      <th>logicalJobName</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>20646876178</td>\n      <td>6252924356</td>\n      <td>0</td>\n      <td>r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=</td>\n      <td>2</td>\n      <td>JPCEeQVfAM84156WazlpF2mbNCIH6JNAEdWKADGMW6M=</td>\n      <td>G/9E4AW9fSviXbmdFO5BBcjVd49zuI1AIU5gHQJLm+8=</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>20647976107</td>\n      <td>6252809803</td>\n      <td>3</td>\n      <td>fVUqjeDqXrzcJObPpFT8U1FwH8uBfyRXJQa4zRlwpb4=</td>\n      <td>1</td>\n      <td>7cLW+6pt2CHJM01krasXKfXt1ArqbEMuwVseGSpclus=</td>\n      <td>kzI7CKSyq7J04F06rtsufIYDPvFxRDHISFXAf2Qav7o=</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>20648680067</td>\n      <td>6252924356</td>\n      <td>1</td>\n      <td>r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=</td>\n      <td>2</td>\n      <td>JPCEeQVfAM84156WazlpF2mbNCIH6JNAEdWKADGMW6M=</td>\n      <td>G/9E4AW9fSviXbmdFO5BBcjVd49zuI1AIU5gHQJLm+8=</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>20648680072</td>\n      <td>6252924415</td>\n      <td>0</td>\n      <td>r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=</td>\n      <td>0</td>\n      <td>/9RMVijX6saTjLFPA6Npk+8JAvFpdgBL7M1O0FMFBf0=</td>\n      <td>QdrSScSXyIpoqR4skT9QFLlVShlxNDTFRBhIWZRq2SM=</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>20648703418</td>\n      <td>6252897088</td>\n      <td>4</td>\n      <td>r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=</td>\n      <td>2</td>\n      <td>pmE/ir4WZoTI/I8Zx1FxhEWDHH5S8IiMxASLJjUfjLA=</td>\n      <td>G/9E4AW9fSviXbmdFO5BBcjVd49zuI1AIU5gHQJLm+8=</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "#\n",
    "# Remove NaN column\n",
    "#\n",
    "\n",
    "job_events = job_events.drop(columns=1)\n",
    "job_events.columns = ['time', 'jobID', 'eventType', 'userName', 'schedClass', 'jobName', 'logicalJobName'] # set column names appropriately\n",
    "job_events.head() # preview data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Percentage of unique jobIDs events:  34.68645149095033\n"
     ]
    }
   ],
   "source": [
    "print('Percentage of unique jobIDs events: ', 100*len(job_events['jobID'].unique())/job_events.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "# Unique Names:  119\n# Unique Job Names:  3500\n# Unique Logical Job Names:  2498\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                        time  jobID  eventType  userName  schedClass  jobName  \\\n",
       "0 2011-05-01 05:44:06.876178      0          0         0           2        0   \n",
       "1 2011-05-01 05:44:07.976107      1          3         1           1        1   \n",
       "2 2011-05-01 05:44:08.680067      0          1         0           2        0   \n",
       "3 2011-05-01 05:44:08.680072      2          0         0           0        2   \n",
       "4 2011-05-01 05:44:08.703418      3          4         0           2        3   \n",
       "\n",
       "   logicalJobName  \n",
       "0               0  \n",
       "1               1  \n",
       "2               0  \n",
       "3               2  \n",
       "4               0  "
      ],
      "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>time</th>\n      <th>jobID</th>\n      <th>eventType</th>\n      <th>userName</th>\n      <th>schedClass</th>\n      <th>jobName</th>\n      <th>logicalJobName</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:06.876178</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011-05-01 05:44:07.976107</td>\n      <td>1</td>\n      <td>3</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:08.680067</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:08.680072</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:08.703418</td>\n      <td>3</td>\n      <td>4</td>\n      <td>0</td>\n      <td>2</td>\n      <td>3</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "source": [
    "#\n",
    "# Factorise userName as numbers, jobName as numbers, logicalJobName as numbers\n",
    "#\n",
    "print('# Unique Names: ', len(job_events['userName'].unique()))\n",
    "print('# Unique Job Names: ', len(job_events['jobName'].unique()))\n",
    "print('# Unique Logical Job Names: ', len(job_events['logicalJobName'].unique()))\n",
    "\n",
    "factorised_names, original_names = pd.factorize(job_events['userName'])\n",
    "factorised_job_names, original_job_names = pd.factorize(job_events['jobName'])\n",
    "factorised_logical_job_names, original_logical_job_names = pd.factorize(job_events['logicalJobName'])\n",
    "factorised_job_ids, original_job_ids = pd.factorize(job_events['jobID'])\n",
    "\n",
    "job_events['userName'] = factorised_names\n",
    "job_events['jobName'] = factorised_job_names\n",
    "job_events['logicalJobName'] = factorised_logical_job_names\n",
    "job_events['jobID'] = factorised_job_ids\n",
    "job_events['time'] = pd.to_datetime(job_events['time'], unit='us', origin='2011-05-01')\n",
    "job_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                        time  jobID  eventType  userName  schedClass  jobName  \\\n",
       "0 2011-05-01 05:44:06.876178      0          0         0           2        0   \n",
       "1 2011-05-01 05:44:07.976107      1          3         1           1        1   \n",
       "2 2011-05-01 05:44:08.680067      0          1         0           2        0   \n",
       "3 2011-05-01 05:44:08.680072      2          0         0           0        2   \n",
       "4 2011-05-01 05:44:08.703418      3          4         0           2        3   \n",
       "\n",
       "   logicalJobName primary_job_id  \n",
       "0               0          87100  \n",
       "1               1          70113  \n",
       "2               0          76001  \n",
       "3               2          27020  \n",
       "4               0          81434  "
      ],
      "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>time</th>\n      <th>jobID</th>\n      <th>eventType</th>\n      <th>userName</th>\n      <th>schedClass</th>\n      <th>jobName</th>\n      <th>logicalJobName</th>\n      <th>primary_job_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:06.876178</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>87100</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011-05-01 05:44:07.976107</td>\n      <td>1</td>\n      <td>3</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>70113</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:08.680067</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>76001</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:08.680072</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>2</td>\n      <td>27020</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:08.703418</td>\n      <td>3</td>\n      <td>4</td>\n      <td>0</td>\n      <td>2</td>\n      <td>3</td>\n      <td>0</td>\n      <td>81434</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "# Creating key column\n",
    "\n",
    "timestamps_je = list(job_events['time'].astype(str)) # timestamps\n",
    "jobid_je = list(job_events['jobID'].astype(str))\n",
    "eventtype_je = list(job_events['eventType'].astype(str))\n",
    "\n",
    "millisecs = []\n",
    "for i in range(len(timestamps_je)): \n",
    "    ms = timestamps_je[i][::-1][0:3] # extract final three digits of time\n",
    "    ji = jobid_je[i]\n",
    "    et = eventtype_je[i]\n",
    "    millisecs.append(ms+ji+et) # concatenate jobid, eventtype, time\n",
    "    #millisecs.append(str(list(job_events['time'])[i]).split(' ')[1].split('.')[1])\n",
    "\n",
    "job_events['primary_job_id'] = millisecs\n",
    "job_events.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task Events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "            0   1           2   3   4   5   \\\n",
       "0  20646168368 NaN   515042969  17 NaN   5   \n",
       "1  20646168371 NaN   515042969  17 NaN   0   \n",
       "2  20646876180 NaN  6252924356   0 NaN   0   \n",
       "3  20646899409 NaN   515042969   2 NaN   5   \n",
       "4  20646899412 NaN   515042969   2 NaN   0   \n",
       "\n",
       "                                             6   7   8        9        10  \\\n",
       "0  /fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=   2   0  0.01562  0.01553   \n",
       "1  /fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=   2   0  0.01562  0.01553   \n",
       "2  r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=   2   8  0.06873  0.04773   \n",
       "3  /fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=   2   0  0.01562  0.01553   \n",
       "4  /fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=   2   0  0.01562  0.01553   \n",
       "\n",
       "         11  12  \n",
       "0  0.000215   0  \n",
       "1  0.000215   0  \n",
       "2  0.000038   0  \n",
       "3  0.000215   0  \n",
       "4  0.000215   0  "
      ],
      "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>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n      <th>7</th>\n      <th>8</th>\n      <th>9</th>\n      <th>10</th>\n      <th>11</th>\n      <th>12</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>20646168368</td>\n      <td>NaN</td>\n      <td>515042969</td>\n      <td>17</td>\n      <td>NaN</td>\n      <td>5</td>\n      <td>/fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>20646168371</td>\n      <td>NaN</td>\n      <td>515042969</td>\n      <td>17</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>/fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>20646876180</td>\n      <td>NaN</td>\n      <td>6252924356</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=</td>\n      <td>2</td>\n      <td>8</td>\n      <td>0.06873</td>\n      <td>0.04773</td>\n      <td>0.000038</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>20646899409</td>\n      <td>NaN</td>\n      <td>515042969</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>5</td>\n      <td>/fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>20646899412</td>\n      <td>NaN</td>\n      <td>515042969</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>/fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "task_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "          time       jobID  taskIndex  machineID  eventType  \\\n",
       "0  20646168368   515042969         17        NaN          5   \n",
       "1  20646168371   515042969         17        NaN          0   \n",
       "2  20646876180  6252924356          0        NaN          0   \n",
       "3  20646899409   515042969          2        NaN          5   \n",
       "4  20646899412   515042969          2        NaN          0   \n",
       "\n",
       "                                       userName  schedulingClass  priority  \\\n",
       "0  /fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=                2         0   \n",
       "1  /fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=                2         0   \n",
       "2  r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=                2         8   \n",
       "3  /fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=                2         0   \n",
       "4  /fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=                2         0   \n",
       "\n",
       "       CPU      RAM      Disk  machineConstraint  \n",
       "0  0.01562  0.01553  0.000215                  0  \n",
       "1  0.01562  0.01553  0.000215                  0  \n",
       "2  0.06873  0.04773  0.000038                  0  \n",
       "3  0.01562  0.01553  0.000215                  0  \n",
       "4  0.01562  0.01553  0.000215                  0  "
      ],
      "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>time</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>eventType</th>\n      <th>userName</th>\n      <th>schedulingClass</th>\n      <th>priority</th>\n      <th>CPU</th>\n      <th>RAM</th>\n      <th>Disk</th>\n      <th>machineConstraint</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>20646168368</td>\n      <td>515042969</td>\n      <td>17</td>\n      <td>NaN</td>\n      <td>5</td>\n      <td>/fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>20646168371</td>\n      <td>515042969</td>\n      <td>17</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>/fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>20646876180</td>\n      <td>6252924356</td>\n      <td>0</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>r/Al6kYJOwZITr6wi4pAlEwyGv5TM2EkJ8woA5hszeA=</td>\n      <td>2</td>\n      <td>8</td>\n      <td>0.06873</td>\n      <td>0.04773</td>\n      <td>0.000038</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>20646899409</td>\n      <td>515042969</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>5</td>\n      <td>/fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>20646899412</td>\n      <td>515042969</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>/fk1fVcVxZ6iM6gHZzqbIyq56m5zrmHfpdcZ/zzkq4c=</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "#\n",
    "# Remove first missing column\n",
    "#\n",
    "\n",
    "task_events = task_events.drop(columns=1)\n",
    "task_events.columns = ['time', 'jobID', 'taskIndex', 'machineID', 'eventType', 'userName', 'schedulingClass', 'priority', 'CPU', 'RAM', 'Disk', 'machineConstraint'] # set column names appropriately\n",
    "task_events.head() # preview data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Percentage of NaN machine IDs:  37.01112650209385\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "# Find number of NaN machine IDs\n",
    "#\n",
    "print('Percentage of NaN machine IDs: ', 100*(np.sum(task_events['machineID'].isna())/len(task_events)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Consider either removing these observations or making them obvious during analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "          time  jobID  taskIndex  machineID  eventType  userName  \\\n",
       "0  20646168368      0         17         -1          5         0   \n",
       "1  20646168371      0         17         -1          0         0   \n",
       "2  20646876180      1          0         -1          0         1   \n",
       "3  20646899409      0          2         -1          5         0   \n",
       "4  20646899412      0          2         -1          0         0   \n",
       "\n",
       "   schedulingClass  priority      CPU      RAM      Disk  machineConstraint  \n",
       "0                2         0  0.01562  0.01553  0.000215                  0  \n",
       "1                2         0  0.01562  0.01553  0.000215                  0  \n",
       "2                2         8  0.06873  0.04773  0.000038                  0  \n",
       "3                2         0  0.01562  0.01553  0.000215                  0  \n",
       "4                2         0  0.01562  0.01553  0.000215                  0  "
      ],
      "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>time</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>eventType</th>\n      <th>userName</th>\n      <th>schedulingClass</th>\n      <th>priority</th>\n      <th>CPU</th>\n      <th>RAM</th>\n      <th>Disk</th>\n      <th>machineConstraint</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>20646168368</td>\n      <td>0</td>\n      <td>17</td>\n      <td>-1</td>\n      <td>5</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>20646168371</td>\n      <td>0</td>\n      <td>17</td>\n      <td>-1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>20646876180</td>\n      <td>1</td>\n      <td>0</td>\n      <td>-1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>2</td>\n      <td>8</td>\n      <td>0.06873</td>\n      <td>0.04773</td>\n      <td>0.000038</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>20646899409</td>\n      <td>0</td>\n      <td>2</td>\n      <td>-1</td>\n      <td>5</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>20646899412</td>\n      <td>0</td>\n      <td>2</td>\n      <td>-1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.01553</td>\n      <td>0.000215</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "#\n",
    "# Factorise all machine IDs (make note of NaN ID), userName, jobID\n",
    "#\n",
    "factorised_machine_ids, original_machine_ids = pd.factorize(task_events['machineID'])\n",
    "task_events['machineID'] = factorised_machine_ids\n",
    "factorised_usernames, original_usernames = pd.factorize(task_events['userName'])\n",
    "task_events['userName'] = factorised_usernames\n",
    "factorised_jobid_task, original_jobid_task = pd.factorize(task_events['jobID'])\n",
    "task_events['jobID'] = factorised_jobid_task\n",
    "task_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Create separate dataframe without NaN\n",
    "#\n",
    "\n",
    "nan_values = (task_events['machineID'] != -1).values  # boolean vector for NaNs in machine ID column\n",
    "task_events_NA = task_events\n",
    "task_events_clean = task_events.iloc[nan_values].reset_index()\n",
    "task_events_clean = task_events_clean.reset_index().drop(columns=['index', 'level_0'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Find unique pairs of taskIndex and jobID (only in task_events_clean)\n",
    "# Quickest way to find unique pairs is to find sum of taskIndex and jobID\n",
    "# This way takes less than 10 seconds compared to 30 minutes any other way\n",
    "# Number generated is almost certainly going to be unique to that specific process\n",
    "\n",
    "task_events['processID'] = (task_events['jobID']+0.00001*task_events['taskIndex'])\n",
    "task_events_clean['processID'] = (task_events_clean['jobID']+0.00001*task_events_clean['taskIndex'])\n",
    "\n",
    "task_combos_full, task_combo_unique_full = pd.factorize(task_events['processID'])\n",
    "task_combos, task_combo_unique = pd.factorize(task_events_clean['processID'])\n",
    "task_events_clean['processID'] = task_combos\n",
    "task_events['processID'] = task_combos_full"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "# Unique pairs in our method  266605\n# Unique pairs using pandas function  266605\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "# Check number of unique pairs is the same\n",
    "#\n",
    "print('# Unique pairs in our method ', len(task_events_clean['processID'].unique()))\n",
    "print('# Unique pairs using pandas function ', len(task_events_clean[['jobID', 'taskIndex']].drop_duplicates().to_numpy()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                        time  jobID  taskIndex  machineID  eventType  \\\n",
       "0 2011-05-01 05:44:06.899535      2       1115          0          3   \n",
       "1 2011-05-01 05:44:06.899762      3          2          1          4   \n",
       "2 2011-05-01 05:44:06.899918      3          8          2          4   \n",
       "3 2011-05-01 05:44:07.630135      0         17          3          1   \n",
       "4 2011-05-01 05:44:07.975760      3          6          4          4   \n",
       "\n",
       "   userName  schedulingClass  priority      CPU       RAM      Disk  \\\n",
       "0         2                0         0  0.06873  0.011930  0.000115   \n",
       "1         1                0         2  0.06873  0.008774  0.000038   \n",
       "2         1                0         2  0.06873  0.008774  0.000038   \n",
       "3         0                2         0  0.01562  0.015530  0.000215   \n",
       "4         1                0         2  0.06873  0.008774  0.000038   \n",
       "\n",
       "   machineConstraint  processID  \n",
       "0                  0          0  \n",
       "1                  0          1  \n",
       "2                  0          2  \n",
       "3                  0          3  \n",
       "4                  0          4  "
      ],
      "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>time</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>eventType</th>\n      <th>userName</th>\n      <th>schedulingClass</th>\n      <th>priority</th>\n      <th>CPU</th>\n      <th>RAM</th>\n      <th>Disk</th>\n      <th>machineConstraint</th>\n      <th>processID</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:06.899535</td>\n      <td>2</td>\n      <td>1115</td>\n      <td>0</td>\n      <td>3</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.06873</td>\n      <td>0.011930</td>\n      <td>0.000115</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011-05-01 05:44:06.899762</td>\n      <td>3</td>\n      <td>2</td>\n      <td>1</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:06.899918</td>\n      <td>3</td>\n      <td>8</td>\n      <td>2</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:07.630135</td>\n      <td>0</td>\n      <td>17</td>\n      <td>3</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.015530</td>\n      <td>0.000215</td>\n      <td>0</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:07.975760</td>\n      <td>3</td>\n      <td>6</td>\n      <td>4</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>4</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "source": [
    "#\n",
    "# Convert time to timestamp\n",
    "#\n",
    "task_events['time'] = pd.to_datetime(task_events['time'], unit='us', origin='2011-05-01')\n",
    "task_events_clean['time'] = pd.to_datetime(task_events_clean['time'], unit='us', origin='2011-05-01')\n",
    "\n",
    "task_events_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                        time  jobID  taskIndex  machineID  eventType  \\\n",
       "0 2011-05-01 05:44:06.899535      2       1115          0          3   \n",
       "1 2011-05-01 05:44:06.899762      3          2          1          4   \n",
       "2 2011-05-01 05:44:06.899918      3          8          2          4   \n",
       "3 2011-05-01 05:44:07.630135      0         17          3          1   \n",
       "4 2011-05-01 05:44:07.975760      3          6          4          4   \n",
       "\n",
       "   userName  schedulingClass  priority      CPU       RAM      Disk  \\\n",
       "0         2                0         0  0.06873  0.011930  0.000115   \n",
       "1         1                0         2  0.06873  0.008774  0.000038   \n",
       "2         1                0         2  0.06873  0.008774  0.000038   \n",
       "3         0                2         0  0.01562  0.015530  0.000215   \n",
       "4         1                0         2  0.06873  0.008774  0.000038   \n",
       "\n",
       "   machineConstraint  processID primary_job_id  \n",
       "0                  0          0          53523  \n",
       "1                  0          1          26734  \n",
       "2                  0          2          81934  \n",
       "3                  0          3          53101  \n",
       "4                  0          4          06734  "
      ],
      "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>time</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>eventType</th>\n      <th>userName</th>\n      <th>schedulingClass</th>\n      <th>priority</th>\n      <th>CPU</th>\n      <th>RAM</th>\n      <th>Disk</th>\n      <th>machineConstraint</th>\n      <th>processID</th>\n      <th>primary_job_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:06.899535</td>\n      <td>2</td>\n      <td>1115</td>\n      <td>0</td>\n      <td>3</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.06873</td>\n      <td>0.011930</td>\n      <td>0.000115</td>\n      <td>0</td>\n      <td>0</td>\n      <td>53523</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011-05-01 05:44:06.899762</td>\n      <td>3</td>\n      <td>2</td>\n      <td>1</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>1</td>\n      <td>26734</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:06.899918</td>\n      <td>3</td>\n      <td>8</td>\n      <td>2</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>2</td>\n      <td>81934</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:07.630135</td>\n      <td>0</td>\n      <td>17</td>\n      <td>3</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.015530</td>\n      <td>0.000215</td>\n      <td>0</td>\n      <td>3</td>\n      <td>53101</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:07.975760</td>\n      <td>3</td>\n      <td>6</td>\n      <td>4</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>4</td>\n      <td>06734</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "# Creating key column\n",
    "\n",
    "timestamps_te = list(task_events_clean['time'].astype(str)) # timestamps\n",
    "jobid_te = list(task_events_clean['jobID'].astype(str))\n",
    "eventtype_te = list(task_events_clean['eventType'].astype(str))\n",
    "\n",
    "millisecs = []\n",
    "for i in range(len(timestamps_te)): \n",
    "    ms = timestamps_te[i][::-1][0:3] # extract final three digits of time\n",
    "    ji = jobid_te[i]\n",
    "    et = eventtype_te[i]\n",
    "    millisecs.append(ms+ji+et) # concatenate jobid, eventtype, time\n",
    "    #millisecs.append(str(list(job_events['time'])[i]).split(' ')[1].split('.')[1])\n",
    "\n",
    "task_events_clean['primary_job_id'] = millisecs\n",
    "task_events_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                        time  jobID  taskIndex  machineID  eventType  \\\n",
       "0 2011-05-01 05:44:06.899535      2       1115          0          3   \n",
       "1 2011-05-01 05:44:06.899762      3          2          1          4   \n",
       "2 2011-05-01 05:44:06.899918      3          8          2          4   \n",
       "3 2011-05-01 05:44:07.630135      0         17          3          1   \n",
       "4 2011-05-01 05:44:07.975760      3          6          4          4   \n",
       "\n",
       "   userName  schedulingClass  priority      CPU       RAM      Disk  \\\n",
       "0         2                0         0  0.06873  0.011930  0.000115   \n",
       "1         1                0         2  0.06873  0.008774  0.000038   \n",
       "2         1                0         2  0.06873  0.008774  0.000038   \n",
       "3         0                2         0  0.01562  0.015530  0.000215   \n",
       "4         1                0         2  0.06873  0.008774  0.000038   \n",
       "\n",
       "   machineConstraint  processID primary_job_id primary_machine_id  \n",
       "0                  0          0          53523              53503  \n",
       "1                  0          1          26734              26714  \n",
       "2                  0          2          81934              81924  \n",
       "3                  0          3          53101              53131  \n",
       "4                  0          4          06734              06744  "
      ],
      "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>time</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>eventType</th>\n      <th>userName</th>\n      <th>schedulingClass</th>\n      <th>priority</th>\n      <th>CPU</th>\n      <th>RAM</th>\n      <th>Disk</th>\n      <th>machineConstraint</th>\n      <th>processID</th>\n      <th>primary_job_id</th>\n      <th>primary_machine_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:06.899535</td>\n      <td>2</td>\n      <td>1115</td>\n      <td>0</td>\n      <td>3</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.06873</td>\n      <td>0.011930</td>\n      <td>0.000115</td>\n      <td>0</td>\n      <td>0</td>\n      <td>53523</td>\n      <td>53503</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011-05-01 05:44:06.899762</td>\n      <td>3</td>\n      <td>2</td>\n      <td>1</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>1</td>\n      <td>26734</td>\n      <td>26714</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:06.899918</td>\n      <td>3</td>\n      <td>8</td>\n      <td>2</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>2</td>\n      <td>81934</td>\n      <td>81924</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:07.630135</td>\n      <td>0</td>\n      <td>17</td>\n      <td>3</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.015530</td>\n      <td>0.000215</td>\n      <td>0</td>\n      <td>3</td>\n      <td>53101</td>\n      <td>53131</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:07.975760</td>\n      <td>3</td>\n      <td>6</td>\n      <td>4</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>4</td>\n      <td>06734</td>\n      <td>06744</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "source": [
    "# Creating key column\n",
    "\n",
    "timestamps_te = list(task_events_clean['time'].astype(str)) # timestamps\n",
    "jobid_te = list(task_events_clean['machineID'].astype(str))\n",
    "eventtype_te = list(task_events_clean['eventType'].astype(str))\n",
    "\n",
    "millisecs = []\n",
    "for i in range(len(timestamps_te)): \n",
    "    ms = timestamps_te[i][::-1][0:3] # extract final three digits of time\n",
    "    ji = jobid_te[i]\n",
    "    et = eventtype_te[i]\n",
    "    millisecs.append(ms+ji+et) # concatenate jobid, eventtype, time\n",
    "    #millisecs.append(str(list(job_events['time'])[i]).split(' ')[1].split('.')[1])\n",
    "\n",
    "task_events_clean['primary_machine_id'] = millisecs\n",
    "task_events_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                        time  jobID  taskIndex  machineID  eventType  \\\n",
       "0 2011-05-01 05:44:06.899535      2       1115          0          3   \n",
       "1 2011-05-01 05:44:06.899762      3          2          1          4   \n",
       "2 2011-05-01 05:44:06.899918      3          8          2          4   \n",
       "3 2011-05-01 05:44:07.630135      0         17          3          1   \n",
       "4 2011-05-01 05:44:07.975760      3          6          4          4   \n",
       "\n",
       "   userName  schedulingClass  priority      CPU       RAM      Disk  \\\n",
       "0         2                0         0  0.06873  0.011930  0.000115   \n",
       "1         1                0         2  0.06873  0.008774  0.000038   \n",
       "2         1                0         2  0.06873  0.008774  0.000038   \n",
       "3         0                2         0  0.01562  0.015530  0.000215   \n",
       "4         1                0         2  0.06873  0.008774  0.000038   \n",
       "\n",
       "   machineConstraint  processID primary_job_id primary_machine_id  \\\n",
       "0                  0          0          53523              53503   \n",
       "1                  0          1          26734              26714   \n",
       "2                  0          2          81934              81924   \n",
       "3                  0          3          53101              53131   \n",
       "4                  0          4          06734              06744   \n",
       "\n",
       "  primary_task_id  \n",
       "0        53521115  \n",
       "1           26732  \n",
       "2           81938  \n",
       "3          531017  \n",
       "4           06736  "
      ],
      "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>time</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>eventType</th>\n      <th>userName</th>\n      <th>schedulingClass</th>\n      <th>priority</th>\n      <th>CPU</th>\n      <th>RAM</th>\n      <th>Disk</th>\n      <th>machineConstraint</th>\n      <th>processID</th>\n      <th>primary_job_id</th>\n      <th>primary_machine_id</th>\n      <th>primary_task_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:06.899535</td>\n      <td>2</td>\n      <td>1115</td>\n      <td>0</td>\n      <td>3</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.06873</td>\n      <td>0.011930</td>\n      <td>0.000115</td>\n      <td>0</td>\n      <td>0</td>\n      <td>53523</td>\n      <td>53503</td>\n      <td>53521115</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011-05-01 05:44:06.899762</td>\n      <td>3</td>\n      <td>2</td>\n      <td>1</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>1</td>\n      <td>26734</td>\n      <td>26714</td>\n      <td>26732</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:06.899918</td>\n      <td>3</td>\n      <td>8</td>\n      <td>2</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>2</td>\n      <td>81934</td>\n      <td>81924</td>\n      <td>81938</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:07.630135</td>\n      <td>0</td>\n      <td>17</td>\n      <td>3</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0.01562</td>\n      <td>0.015530</td>\n      <td>0.000215</td>\n      <td>0</td>\n      <td>3</td>\n      <td>53101</td>\n      <td>53131</td>\n      <td>531017</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:07.975760</td>\n      <td>3</td>\n      <td>6</td>\n      <td>4</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0.06873</td>\n      <td>0.008774</td>\n      <td>0.000038</td>\n      <td>0</td>\n      <td>4</td>\n      <td>06734</td>\n      <td>06744</td>\n      <td>06736</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "# Creating key column\n",
    "\n",
    "timestamps_te = list(task_events_clean['time'].astype(str)) # timestamps\n",
    "jobid_te = list(task_events_clean['jobID'].astype(str))\n",
    "eventtype_te = list(task_events_clean['taskIndex'].astype(str))\n",
    "\n",
    "millisecs = []\n",
    "for i in range(len(timestamps_te)): \n",
    "    ms = timestamps_te[i][::-1][0:3] # extract final three digits of time\n",
    "    ji = jobid_te[i]\n",
    "    et = eventtype_te[i]\n",
    "    millisecs.append(ms+ji+et) # concatenate jobid, eventtype, time\n",
    "    #millisecs.append(str(list(job_events['time'])[i]).split(' ')[1].split('.')[1])\n",
    "\n",
    "task_events_clean['primary_task_id'] = millisecs\n",
    "task_events_clean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task Usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "(17944928, 20)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "            0            1           2     3           4         5        6   \\\n",
       "0  20645000000  20700000000  4665896876   394   317508493  0.048950  0.06689   \n",
       "1  20645000000  20700000000  6176114691    80  2912464652  0.000000  0.00000   \n",
       "2  20645000000  20700000000  6206862915    49   635988945  0.000533  0.04016   \n",
       "3  20645000000  20700000000  6238340468   541     1301873  0.031680  0.00647   \n",
       "4  20645000000  20700000000  6238340468  1424  3890452312  0.007507  0.01242   \n",
       "\n",
       "         7         8         9         10        11        12        13  \\\n",
       "0  0.077150  0.004684  0.005333  0.067020  0.000104  0.000378  0.079710   \n",
       "1  0.000000  0.000000  0.000000  0.000000  0.000000  0.000000  0.000000   \n",
       "2  0.042660  0.000165  0.000305  0.040410  0.000025  0.000080  0.003906   \n",
       "3  0.008163  0.000302  0.000536  0.007393  0.000057  0.000000  0.082520   \n",
       "4  0.013550  0.000123  0.000375  0.012480  0.000013  0.000003  0.065310   \n",
       "\n",
       "         14     15        16  17  18        19  \n",
       "0  0.002571  1.222  0.001147   0   0  0.039860  \n",
       "1       NaN    NaN       NaN   0   0  0.000000  \n",
       "2  0.001215  3.805  0.011300   0   0  0.000468  \n",
       "3  0.001429  1.286  0.001875   0   0  0.080440  \n",
       "4  0.000143  1.378  0.002397   0   0  0.005470  "
      ],
      "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>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n      <th>6</th>\n      <th>7</th>\n      <th>8</th>\n      <th>9</th>\n      <th>10</th>\n      <th>11</th>\n      <th>12</th>\n      <th>13</th>\n      <th>14</th>\n      <th>15</th>\n      <th>16</th>\n      <th>17</th>\n      <th>18</th>\n      <th>19</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>4665896876</td>\n      <td>394</td>\n      <td>317508493</td>\n      <td>0.048950</td>\n      <td>0.06689</td>\n      <td>0.077150</td>\n      <td>0.004684</td>\n      <td>0.005333</td>\n      <td>0.067020</td>\n      <td>0.000104</td>\n      <td>0.000378</td>\n      <td>0.079710</td>\n      <td>0.002571</td>\n      <td>1.222</td>\n      <td>0.001147</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.039860</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6176114691</td>\n      <td>80</td>\n      <td>2912464652</td>\n      <td>0.000000</td>\n      <td>0.00000</td>\n      <td>0.000000</td>\n      <td>0.000000</td>\n      <td>0.000000</td>\n      <td>0.000000</td>\n      <td>0.000000</td>\n      <td>0.000000</td>\n      <td>0.000000</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6206862915</td>\n      <td>49</td>\n      <td>635988945</td>\n      <td>0.000533</td>\n      <td>0.04016</td>\n      <td>0.042660</td>\n      <td>0.000165</td>\n      <td>0.000305</td>\n      <td>0.040410</td>\n      <td>0.000025</td>\n      <td>0.000080</td>\n      <td>0.003906</td>\n      <td>0.001215</td>\n      <td>3.805</td>\n      <td>0.011300</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.000468</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6238340468</td>\n      <td>541</td>\n      <td>1301873</td>\n      <td>0.031680</td>\n      <td>0.00647</td>\n      <td>0.008163</td>\n      <td>0.000302</td>\n      <td>0.000536</td>\n      <td>0.007393</td>\n      <td>0.000057</td>\n      <td>0.000000</td>\n      <td>0.082520</td>\n      <td>0.001429</td>\n      <td>1.286</td>\n      <td>0.001875</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.080440</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6238340468</td>\n      <td>1424</td>\n      <td>3890452312</td>\n      <td>0.007507</td>\n      <td>0.01242</td>\n      <td>0.013550</td>\n      <td>0.000123</td>\n      <td>0.000375</td>\n      <td>0.012480</td>\n      <td>0.000013</td>\n      <td>0.000003</td>\n      <td>0.065310</td>\n      <td>0.000143</td>\n      <td>1.378</td>\n      <td>0.002397</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005470</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "source": [
    "print(task_usage.shape)\n",
    "task_usage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "task_usage.columns = ['first', 'last', 'jobID', 'taskIndex', 'machineID', 'cpuMeanUsage', 'canonicalMemUsage', 'assignedMemUsage', 'unmappedCacheMemUsage', 'totalCacheMemUsage', 'maxMemUsage', 'meanDiskTime', 'meanDiskSpaceUsed', 'cpuMaxUsage', 'maxDiskTime', 'cyclesPerInstruction', 'memAccessPerInstruction', 'samplePortion', 'aggType', 'cpuSampledUsage']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Remove NaN values\n",
    "#\n",
    "\n",
    "task_usage = task_usage.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "(15126583, 20)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "         first         last       jobID  taskIndex   machineID  cpuMeanUsage  \\\n",
       "0  20645000000  20700000000  4665896876        394   317508493      0.048950   \n",
       "2  20645000000  20700000000  6206862915         49   635988945      0.000533   \n",
       "3  20645000000  20700000000  6238340468        541     1301873      0.031680   \n",
       "4  20645000000  20700000000  6238340468       1424  3890452312      0.007507   \n",
       "5  20645000000  20700000000  6238340468       1971      905071      0.010560   \n",
       "\n",
       "   canonicalMemUsage  assignedMemUsage  unmappedCacheMemUsage  \\\n",
       "0            0.06689          0.077150               0.004684   \n",
       "2            0.04016          0.042660               0.000165   \n",
       "3            0.00647          0.008163               0.000302   \n",
       "4            0.01242          0.013550               0.000123   \n",
       "5            0.01036          0.011550               0.000117   \n",
       "\n",
       "   totalCacheMemUsage  maxMemUsage  meanDiskTime  meanDiskSpaceUsed  \\\n",
       "0            0.005333     0.067020      0.000104           0.000378   \n",
       "2            0.000305     0.040410      0.000025           0.000080   \n",
       "3            0.000536     0.007393      0.000057           0.000000   \n",
       "4            0.000375     0.012480      0.000013           0.000003   \n",
       "5            0.000368     0.010410      0.000021           0.000003   \n",
       "\n",
       "   cpuMaxUsage  maxDiskTime  cyclesPerInstruction  memAccessPerInstruction  \\\n",
       "0     0.079710     0.002571                 1.222                 0.001147   \n",
       "2     0.003906     0.001215                 3.805                 0.011300   \n",
       "3     0.082520     0.001429                 1.286                 0.001875   \n",
       "4     0.065310     0.000143                 1.378                 0.002397   \n",
       "5     0.069950     0.000215                 1.648                 0.003659   \n",
       "\n",
       "   samplePortion  aggType  cpuSampledUsage  \n",
       "0              0        0         0.039860  \n",
       "2              0        0         0.000468  \n",
       "3              0        0         0.080440  \n",
       "4              0        0         0.005470  \n",
       "5              0        0         0.005974  "
      ],
      "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>first</th>\n      <th>last</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>cpuMeanUsage</th>\n      <th>canonicalMemUsage</th>\n      <th>assignedMemUsage</th>\n      <th>unmappedCacheMemUsage</th>\n      <th>totalCacheMemUsage</th>\n      <th>maxMemUsage</th>\n      <th>meanDiskTime</th>\n      <th>meanDiskSpaceUsed</th>\n      <th>cpuMaxUsage</th>\n      <th>maxDiskTime</th>\n      <th>cyclesPerInstruction</th>\n      <th>memAccessPerInstruction</th>\n      <th>samplePortion</th>\n      <th>aggType</th>\n      <th>cpuSampledUsage</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>4665896876</td>\n      <td>394</td>\n      <td>317508493</td>\n      <td>0.048950</td>\n      <td>0.06689</td>\n      <td>0.077150</td>\n      <td>0.004684</td>\n      <td>0.005333</td>\n      <td>0.067020</td>\n      <td>0.000104</td>\n      <td>0.000378</td>\n      <td>0.079710</td>\n      <td>0.002571</td>\n      <td>1.222</td>\n      <td>0.001147</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.039860</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6206862915</td>\n      <td>49</td>\n      <td>635988945</td>\n      <td>0.000533</td>\n      <td>0.04016</td>\n      <td>0.042660</td>\n      <td>0.000165</td>\n      <td>0.000305</td>\n      <td>0.040410</td>\n      <td>0.000025</td>\n      <td>0.000080</td>\n      <td>0.003906</td>\n      <td>0.001215</td>\n      <td>3.805</td>\n      <td>0.011300</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.000468</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6238340468</td>\n      <td>541</td>\n      <td>1301873</td>\n      <td>0.031680</td>\n      <td>0.00647</td>\n      <td>0.008163</td>\n      <td>0.000302</td>\n      <td>0.000536</td>\n      <td>0.007393</td>\n      <td>0.000057</td>\n      <td>0.000000</td>\n      <td>0.082520</td>\n      <td>0.001429</td>\n      <td>1.286</td>\n      <td>0.001875</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.080440</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6238340468</td>\n      <td>1424</td>\n      <td>3890452312</td>\n      <td>0.007507</td>\n      <td>0.01242</td>\n      <td>0.013550</td>\n      <td>0.000123</td>\n      <td>0.000375</td>\n      <td>0.012480</td>\n      <td>0.000013</td>\n      <td>0.000003</td>\n      <td>0.065310</td>\n      <td>0.000143</td>\n      <td>1.378</td>\n      <td>0.002397</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005470</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6238340468</td>\n      <td>1971</td>\n      <td>905071</td>\n      <td>0.010560</td>\n      <td>0.01036</td>\n      <td>0.011550</td>\n      <td>0.000117</td>\n      <td>0.000368</td>\n      <td>0.010410</td>\n      <td>0.000021</td>\n      <td>0.000003</td>\n      <td>0.069950</td>\n      <td>0.000215</td>\n      <td>1.648</td>\n      <td>0.003659</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005974</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 24
    }
   ],
   "source": [
    "print(task_usage.shape)\n",
    "task_usage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "         first         last       jobID  taskIndex   machineID  cpuMeanUsage  \\\n",
       "0  20645000000  20700000000  4665896876        394   317508493      0.048950   \n",
       "2  20645000000  20700000000  6206862915         49   635988945      0.000533   \n",
       "3  20645000000  20700000000  6238340468        541     1301873      0.031680   \n",
       "4  20645000000  20700000000  6238340468       1424  3890452312      0.007507   \n",
       "5  20645000000  20700000000  6238340468       1971      905071      0.010560   \n",
       "\n",
       "   canonicalMemUsage  assignedMemUsage  unmappedCacheMemUsage  \\\n",
       "0            0.06689          0.077150               0.004684   \n",
       "2            0.04016          0.042660               0.000165   \n",
       "3            0.00647          0.008163               0.000302   \n",
       "4            0.01242          0.013550               0.000123   \n",
       "5            0.01036          0.011550               0.000117   \n",
       "\n",
       "   totalCacheMemUsage  ...  meanDiskTime  meanDiskSpaceUsed  cpuMaxUsage  \\\n",
       "0            0.005333  ...      0.000104           0.000378     0.079710   \n",
       "2            0.000305  ...      0.000025           0.000080     0.003906   \n",
       "3            0.000536  ...      0.000057           0.000000     0.082520   \n",
       "4            0.000375  ...      0.000013           0.000003     0.065310   \n",
       "5            0.000368  ...      0.000021           0.000003     0.069950   \n",
       "\n",
       "   maxDiskTime  cyclesPerInstruction  memAccessPerInstruction  samplePortion  \\\n",
       "0     0.002571                 1.222                 0.001147              0   \n",
       "2     0.001215                 3.805                 0.011300              0   \n",
       "3     0.001429                 1.286                 0.001875              0   \n",
       "4     0.000143                 1.378                 0.002397              0   \n",
       "5     0.000215                 1.648                 0.003659              0   \n",
       "\n",
       "   aggType  cpuSampledUsage  processID  \n",
       "0        0         0.039860          0  \n",
       "2        0         0.000468          1  \n",
       "3        0         0.080440          2  \n",
       "4        0         0.005470          3  \n",
       "5        0         0.005974          4  \n",
       "\n",
       "[5 rows x 21 columns]"
      ],
      "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>first</th>\n      <th>last</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>cpuMeanUsage</th>\n      <th>canonicalMemUsage</th>\n      <th>assignedMemUsage</th>\n      <th>unmappedCacheMemUsage</th>\n      <th>totalCacheMemUsage</th>\n      <th>...</th>\n      <th>meanDiskTime</th>\n      <th>meanDiskSpaceUsed</th>\n      <th>cpuMaxUsage</th>\n      <th>maxDiskTime</th>\n      <th>cyclesPerInstruction</th>\n      <th>memAccessPerInstruction</th>\n      <th>samplePortion</th>\n      <th>aggType</th>\n      <th>cpuSampledUsage</th>\n      <th>processID</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>4665896876</td>\n      <td>394</td>\n      <td>317508493</td>\n      <td>0.048950</td>\n      <td>0.06689</td>\n      <td>0.077150</td>\n      <td>0.004684</td>\n      <td>0.005333</td>\n      <td>...</td>\n      <td>0.000104</td>\n      <td>0.000378</td>\n      <td>0.079710</td>\n      <td>0.002571</td>\n      <td>1.222</td>\n      <td>0.001147</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.039860</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6206862915</td>\n      <td>49</td>\n      <td>635988945</td>\n      <td>0.000533</td>\n      <td>0.04016</td>\n      <td>0.042660</td>\n      <td>0.000165</td>\n      <td>0.000305</td>\n      <td>...</td>\n      <td>0.000025</td>\n      <td>0.000080</td>\n      <td>0.003906</td>\n      <td>0.001215</td>\n      <td>3.805</td>\n      <td>0.011300</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.000468</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6238340468</td>\n      <td>541</td>\n      <td>1301873</td>\n      <td>0.031680</td>\n      <td>0.00647</td>\n      <td>0.008163</td>\n      <td>0.000302</td>\n      <td>0.000536</td>\n      <td>...</td>\n      <td>0.000057</td>\n      <td>0.000000</td>\n      <td>0.082520</td>\n      <td>0.001429</td>\n      <td>1.286</td>\n      <td>0.001875</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.080440</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6238340468</td>\n      <td>1424</td>\n      <td>3890452312</td>\n      <td>0.007507</td>\n      <td>0.01242</td>\n      <td>0.013550</td>\n      <td>0.000123</td>\n      <td>0.000375</td>\n      <td>...</td>\n      <td>0.000013</td>\n      <td>0.000003</td>\n      <td>0.065310</td>\n      <td>0.000143</td>\n      <td>1.378</td>\n      <td>0.002397</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005470</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>20645000000</td>\n      <td>20700000000</td>\n      <td>6238340468</td>\n      <td>1971</td>\n      <td>905071</td>\n      <td>0.010560</td>\n      <td>0.01036</td>\n      <td>0.011550</td>\n      <td>0.000117</td>\n      <td>0.000368</td>\n      <td>...</td>\n      <td>0.000021</td>\n      <td>0.000003</td>\n      <td>0.069950</td>\n      <td>0.000215</td>\n      <td>1.648</td>\n      <td>0.003659</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005974</td>\n      <td>4</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 21 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "source": [
    "# # Find unique pairs of taskIndex and jobID (only in task_events_clean)\n",
    "# Quickest way to find unique pairs is to find sum of 0.01*taskIndex and jobID\n",
    "# This way takes less than 5 seconds compared to 30 minutes any other way\n",
    "# Number generated is almost certainly going to be unique to that specific process\n",
    "\n",
    "task_usage['processID'] = (task_usage['jobID']+0.01*task_usage['taskIndex'])\n",
    "usage_combos_df, usage_unique_combos = pd.factorize(task_usage['processID'])\n",
    "task_usage['processID'] = usage_combos_df\n",
    "        \n",
    "task_usage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "# Unique pairs in our method  301578\n",
      "# Unique pairs using pandas function  301578\n"
     ]
    }
   ],
   "source": [
    "#\n",
    "# Check number of unique pairs is the same\n",
    "#\n",
    "print('# Unique pairs in our method ', len(task_usage['processID'].unique()))\n",
    "print('# Unique pairs using pandas function ', len(task_usage[['jobID', 'taskIndex']].drop_duplicates().to_numpy()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                first                last       jobID  taskIndex   machineID  \\\n",
       "0 2011-05-01 05:44:05 2011-05-01 05:45:00  4665896876        394   317508493   \n",
       "2 2011-05-01 05:44:05 2011-05-01 05:45:00  6206862915         49   635988945   \n",
       "3 2011-05-01 05:44:05 2011-05-01 05:45:00  6238340468        541     1301873   \n",
       "4 2011-05-01 05:44:05 2011-05-01 05:45:00  6238340468       1424  3890452312   \n",
       "5 2011-05-01 05:44:05 2011-05-01 05:45:00  6238340468       1971      905071   \n",
       "\n",
       "   cpuMeanUsage  canonicalMemUsage  assignedMemUsage  unmappedCacheMemUsage  \\\n",
       "0      0.048950            0.06689          0.077150               0.004684   \n",
       "2      0.000533            0.04016          0.042660               0.000165   \n",
       "3      0.031680            0.00647          0.008163               0.000302   \n",
       "4      0.007507            0.01242          0.013550               0.000123   \n",
       "5      0.010560            0.01036          0.011550               0.000117   \n",
       "\n",
       "   totalCacheMemUsage  ...  meanDiskTime  meanDiskSpaceUsed  cpuMaxUsage  \\\n",
       "0            0.005333  ...      0.000104           0.000378     0.079710   \n",
       "2            0.000305  ...      0.000025           0.000080     0.003906   \n",
       "3            0.000536  ...      0.000057           0.000000     0.082520   \n",
       "4            0.000375  ...      0.000013           0.000003     0.065310   \n",
       "5            0.000368  ...      0.000021           0.000003     0.069950   \n",
       "\n",
       "   maxDiskTime  cyclesPerInstruction  memAccessPerInstruction  samplePortion  \\\n",
       "0     0.002571                 1.222                 0.001147              0   \n",
       "2     0.001215                 3.805                 0.011300              0   \n",
       "3     0.001429                 1.286                 0.001875              0   \n",
       "4     0.000143                 1.378                 0.002397              0   \n",
       "5     0.000215                 1.648                 0.003659              0   \n",
       "\n",
       "   aggType  cpuSampledUsage  processID  \n",
       "0        0         0.039860          0  \n",
       "2        0         0.000468          1  \n",
       "3        0         0.080440          2  \n",
       "4        0         0.005470          3  \n",
       "5        0         0.005974          4  \n",
       "\n",
       "[5 rows x 21 columns]"
      ],
      "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>first</th>\n      <th>last</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>cpuMeanUsage</th>\n      <th>canonicalMemUsage</th>\n      <th>assignedMemUsage</th>\n      <th>unmappedCacheMemUsage</th>\n      <th>totalCacheMemUsage</th>\n      <th>...</th>\n      <th>meanDiskTime</th>\n      <th>meanDiskSpaceUsed</th>\n      <th>cpuMaxUsage</th>\n      <th>maxDiskTime</th>\n      <th>cyclesPerInstruction</th>\n      <th>memAccessPerInstruction</th>\n      <th>samplePortion</th>\n      <th>aggType</th>\n      <th>cpuSampledUsage</th>\n      <th>processID</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>4665896876</td>\n      <td>394</td>\n      <td>317508493</td>\n      <td>0.048950</td>\n      <td>0.06689</td>\n      <td>0.077150</td>\n      <td>0.004684</td>\n      <td>0.005333</td>\n      <td>...</td>\n      <td>0.000104</td>\n      <td>0.000378</td>\n      <td>0.079710</td>\n      <td>0.002571</td>\n      <td>1.222</td>\n      <td>0.001147</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.039860</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>6206862915</td>\n      <td>49</td>\n      <td>635988945</td>\n      <td>0.000533</td>\n      <td>0.04016</td>\n      <td>0.042660</td>\n      <td>0.000165</td>\n      <td>0.000305</td>\n      <td>...</td>\n      <td>0.000025</td>\n      <td>0.000080</td>\n      <td>0.003906</td>\n      <td>0.001215</td>\n      <td>3.805</td>\n      <td>0.011300</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.000468</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>6238340468</td>\n      <td>541</td>\n      <td>1301873</td>\n      <td>0.031680</td>\n      <td>0.00647</td>\n      <td>0.008163</td>\n      <td>0.000302</td>\n      <td>0.000536</td>\n      <td>...</td>\n      <td>0.000057</td>\n      <td>0.000000</td>\n      <td>0.082520</td>\n      <td>0.001429</td>\n      <td>1.286</td>\n      <td>0.001875</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.080440</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>6238340468</td>\n      <td>1424</td>\n      <td>3890452312</td>\n      <td>0.007507</td>\n      <td>0.01242</td>\n      <td>0.013550</td>\n      <td>0.000123</td>\n      <td>0.000375</td>\n      <td>...</td>\n      <td>0.000013</td>\n      <td>0.000003</td>\n      <td>0.065310</td>\n      <td>0.000143</td>\n      <td>1.378</td>\n      <td>0.002397</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005470</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>6238340468</td>\n      <td>1971</td>\n      <td>905071</td>\n      <td>0.010560</td>\n      <td>0.01036</td>\n      <td>0.011550</td>\n      <td>0.000117</td>\n      <td>0.000368</td>\n      <td>...</td>\n      <td>0.000021</td>\n      <td>0.000003</td>\n      <td>0.069950</td>\n      <td>0.000215</td>\n      <td>1.648</td>\n      <td>0.003659</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005974</td>\n      <td>4</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 21 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "source": [
    "#\n",
    "# Change first and last to timestamp\n",
    "#\n",
    "task_usage['first'] = pd.to_datetime(task_usage['first'], unit='us', origin='2011-05-01')\n",
    "task_usage['last'] = pd.to_datetime(task_usage['last'], unit='us', origin='2011-05-01')\n",
    "\n",
    "task_usage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                first                last  jobID  taskIndex  machineID  \\\n",
       "0 2011-05-01 05:44:05 2011-05-01 05:45:00      0          0          0   \n",
       "2 2011-05-01 05:44:05 2011-05-01 05:45:00      1          1          1   \n",
       "3 2011-05-01 05:44:05 2011-05-01 05:45:00      2          2          2   \n",
       "4 2011-05-01 05:44:05 2011-05-01 05:45:00      2          3          3   \n",
       "5 2011-05-01 05:44:05 2011-05-01 05:45:00      2          4          4   \n",
       "\n",
       "   cpuMeanUsage  canonicalMemUsage  assignedMemUsage  unmappedCacheMemUsage  \\\n",
       "0      0.048950            0.06689          0.077150               0.004684   \n",
       "2      0.000533            0.04016          0.042660               0.000165   \n",
       "3      0.031680            0.00647          0.008163               0.000302   \n",
       "4      0.007507            0.01242          0.013550               0.000123   \n",
       "5      0.010560            0.01036          0.011550               0.000117   \n",
       "\n",
       "   totalCacheMemUsage  ...  meanDiskTime  meanDiskSpaceUsed  cpuMaxUsage  \\\n",
       "0            0.005333  ...      0.000104           0.000378     0.079710   \n",
       "2            0.000305  ...      0.000025           0.000080     0.003906   \n",
       "3            0.000536  ...      0.000057           0.000000     0.082520   \n",
       "4            0.000375  ...      0.000013           0.000003     0.065310   \n",
       "5            0.000368  ...      0.000021           0.000003     0.069950   \n",
       "\n",
       "   maxDiskTime  cyclesPerInstruction  memAccessPerInstruction  samplePortion  \\\n",
       "0     0.002571                 1.222                 0.001147              0   \n",
       "2     0.001215                 3.805                 0.011300              0   \n",
       "3     0.001429                 1.286                 0.001875              0   \n",
       "4     0.000143                 1.378                 0.002397              0   \n",
       "5     0.000215                 1.648                 0.003659              0   \n",
       "\n",
       "   aggType  cpuSampledUsage  processID  \n",
       "0        0         0.039860          0  \n",
       "2        0         0.000468          1  \n",
       "3        0         0.080440          2  \n",
       "4        0         0.005470          3  \n",
       "5        0         0.005974          4  \n",
       "\n",
       "[5 rows x 21 columns]"
      ],
      "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>first</th>\n      <th>last</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>cpuMeanUsage</th>\n      <th>canonicalMemUsage</th>\n      <th>assignedMemUsage</th>\n      <th>unmappedCacheMemUsage</th>\n      <th>totalCacheMemUsage</th>\n      <th>...</th>\n      <th>meanDiskTime</th>\n      <th>meanDiskSpaceUsed</th>\n      <th>cpuMaxUsage</th>\n      <th>maxDiskTime</th>\n      <th>cyclesPerInstruction</th>\n      <th>memAccessPerInstruction</th>\n      <th>samplePortion</th>\n      <th>aggType</th>\n      <th>cpuSampledUsage</th>\n      <th>processID</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.048950</td>\n      <td>0.06689</td>\n      <td>0.077150</td>\n      <td>0.004684</td>\n      <td>0.005333</td>\n      <td>...</td>\n      <td>0.000104</td>\n      <td>0.000378</td>\n      <td>0.079710</td>\n      <td>0.002571</td>\n      <td>1.222</td>\n      <td>0.001147</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.039860</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>0.000533</td>\n      <td>0.04016</td>\n      <td>0.042660</td>\n      <td>0.000165</td>\n      <td>0.000305</td>\n      <td>...</td>\n      <td>0.000025</td>\n      <td>0.000080</td>\n      <td>0.003906</td>\n      <td>0.001215</td>\n      <td>3.805</td>\n      <td>0.011300</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.000468</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0.031680</td>\n      <td>0.00647</td>\n      <td>0.008163</td>\n      <td>0.000302</td>\n      <td>0.000536</td>\n      <td>...</td>\n      <td>0.000057</td>\n      <td>0.000000</td>\n      <td>0.082520</td>\n      <td>0.001429</td>\n      <td>1.286</td>\n      <td>0.001875</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.080440</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>2</td>\n      <td>3</td>\n      <td>3</td>\n      <td>0.007507</td>\n      <td>0.01242</td>\n      <td>0.013550</td>\n      <td>0.000123</td>\n      <td>0.000375</td>\n      <td>...</td>\n      <td>0.000013</td>\n      <td>0.000003</td>\n      <td>0.065310</td>\n      <td>0.000143</td>\n      <td>1.378</td>\n      <td>0.002397</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005470</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>2</td>\n      <td>4</td>\n      <td>4</td>\n      <td>0.010560</td>\n      <td>0.01036</td>\n      <td>0.011550</td>\n      <td>0.000117</td>\n      <td>0.000368</td>\n      <td>...</td>\n      <td>0.000021</td>\n      <td>0.000003</td>\n      <td>0.069950</td>\n      <td>0.000215</td>\n      <td>1.648</td>\n      <td>0.003659</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005974</td>\n      <td>4</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 21 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "source": [
    "#\n",
    "# Categorise jobID, machineID, taskIndex\n",
    "#\n",
    "factorised_jobids_usage, original_jobids_usage = pd.factorize(task_usage['jobID'])\n",
    "task_usage['jobID'] = factorised_jobids_usage\n",
    "\n",
    "factorised_machineid_usage, original_machineid_usage = pd.factorize(task_usage['machineID'])\n",
    "task_usage['machineID'] = factorised_machineid_usage\n",
    "\n",
    "factorised_taskid_usage, original_taskid_usage = pd.factorize(task_usage['taskIndex'])\n",
    "task_usage['taskIndex'] = factorised_taskid_usage\n",
    "\n",
    "task_usage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                first                last  jobID  taskIndex  machineID  \\\n",
       "0 2011-05-01 05:44:05 2011-05-01 05:45:00      0          0          0   \n",
       "2 2011-05-01 05:44:05 2011-05-01 05:45:00      1          1          1   \n",
       "3 2011-05-01 05:44:05 2011-05-01 05:45:00      2          2          2   \n",
       "4 2011-05-01 05:44:05 2011-05-01 05:45:00      2          3          3   \n",
       "5 2011-05-01 05:44:05 2011-05-01 05:45:00      2          4          4   \n",
       "\n",
       "   cpuMeanUsage  canonicalMemUsage  assignedMemUsage  unmappedCacheMemUsage  \\\n",
       "0      0.048950            0.06689          0.077150               0.004684   \n",
       "2      0.000533            0.04016          0.042660               0.000165   \n",
       "3      0.031680            0.00647          0.008163               0.000302   \n",
       "4      0.007507            0.01242          0.013550               0.000123   \n",
       "5      0.010560            0.01036          0.011550               0.000117   \n",
       "\n",
       "   totalCacheMemUsage  ...  meanDiskSpaceUsed  cpuMaxUsage  maxDiskTime  \\\n",
       "0            0.005333  ...           0.000378     0.079710     0.002571   \n",
       "2            0.000305  ...           0.000080     0.003906     0.001215   \n",
       "3            0.000536  ...           0.000000     0.082520     0.001429   \n",
       "4            0.000375  ...           0.000003     0.065310     0.000143   \n",
       "5            0.000368  ...           0.000003     0.069950     0.000215   \n",
       "\n",
       "   cyclesPerInstruction  memAccessPerInstruction  samplePortion  aggType  \\\n",
       "0                 1.222                 0.001147              0        0   \n",
       "2                 3.805                 0.011300              0        0   \n",
       "3                 1.286                 0.001875              0        0   \n",
       "4                 1.378                 0.002397              0        0   \n",
       "5                 1.648                 0.003659              0        0   \n",
       "\n",
       "   cpuSampledUsage  processID  primary_usage_id  \n",
       "0         0.039860          0              5000  \n",
       "2         0.000468          1              5011  \n",
       "3         0.080440          2              5022  \n",
       "4         0.005470          3              5033  \n",
       "5         0.005974          4              5044  \n",
       "\n",
       "[5 rows x 22 columns]"
      ],
      "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>first</th>\n      <th>last</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>cpuMeanUsage</th>\n      <th>canonicalMemUsage</th>\n      <th>assignedMemUsage</th>\n      <th>unmappedCacheMemUsage</th>\n      <th>totalCacheMemUsage</th>\n      <th>...</th>\n      <th>meanDiskSpaceUsed</th>\n      <th>cpuMaxUsage</th>\n      <th>maxDiskTime</th>\n      <th>cyclesPerInstruction</th>\n      <th>memAccessPerInstruction</th>\n      <th>samplePortion</th>\n      <th>aggType</th>\n      <th>cpuSampledUsage</th>\n      <th>processID</th>\n      <th>primary_usage_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.048950</td>\n      <td>0.06689</td>\n      <td>0.077150</td>\n      <td>0.004684</td>\n      <td>0.005333</td>\n      <td>...</td>\n      <td>0.000378</td>\n      <td>0.079710</td>\n      <td>0.002571</td>\n      <td>1.222</td>\n      <td>0.001147</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.039860</td>\n      <td>0</td>\n      <td>5000</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>0.000533</td>\n      <td>0.04016</td>\n      <td>0.042660</td>\n      <td>0.000165</td>\n      <td>0.000305</td>\n      <td>...</td>\n      <td>0.000080</td>\n      <td>0.003906</td>\n      <td>0.001215</td>\n      <td>3.805</td>\n      <td>0.011300</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.000468</td>\n      <td>1</td>\n      <td>5011</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0.031680</td>\n      <td>0.00647</td>\n      <td>0.008163</td>\n      <td>0.000302</td>\n      <td>0.000536</td>\n      <td>...</td>\n      <td>0.000000</td>\n      <td>0.082520</td>\n      <td>0.001429</td>\n      <td>1.286</td>\n      <td>0.001875</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.080440</td>\n      <td>2</td>\n      <td>5022</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>2</td>\n      <td>3</td>\n      <td>3</td>\n      <td>0.007507</td>\n      <td>0.01242</td>\n      <td>0.013550</td>\n      <td>0.000123</td>\n      <td>0.000375</td>\n      <td>...</td>\n      <td>0.000003</td>\n      <td>0.065310</td>\n      <td>0.000143</td>\n      <td>1.378</td>\n      <td>0.002397</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005470</td>\n      <td>3</td>\n      <td>5033</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>2</td>\n      <td>4</td>\n      <td>4</td>\n      <td>0.010560</td>\n      <td>0.01036</td>\n      <td>0.011550</td>\n      <td>0.000117</td>\n      <td>0.000368</td>\n      <td>...</td>\n      <td>0.000003</td>\n      <td>0.069950</td>\n      <td>0.000215</td>\n      <td>1.648</td>\n      <td>0.003659</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005974</td>\n      <td>4</td>\n      <td>5044</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 22 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "# Creating key column\n",
    "\n",
    "timestamps_tu = list(task_usage['first'].astype(str)) # timestamps\n",
    "jobid_tu = list(task_usage['taskIndex'].astype(str))\n",
    "eventtype_tu = list(task_usage['machineID'].astype(str))\n",
    "\n",
    "millisecs = []\n",
    "for i in range(len(timestamps_tu)): \n",
    "    ms = timestamps_tu[i][::-1][0:2] # extract final three digits of time\n",
    "    ji = jobid_tu[i]\n",
    "    et = eventtype_tu[i]\n",
    "    millisecs.append(ms+ji+et) # concatenate jobid, eventtype, time\n",
    "    #millisecs.append(str(list(job_events['time'])[i]).split(' ')[1].split('.')[1])\n",
    "\n",
    "task_usage['primary_usage_id'] = millisecs\n",
    "task_usage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                first                last  jobID  taskIndex  machineID  \\\n",
       "0 2011-05-01 05:44:05 2011-05-01 05:45:00      0          0          0   \n",
       "2 2011-05-01 05:44:05 2011-05-01 05:45:00      1          1          1   \n",
       "3 2011-05-01 05:44:05 2011-05-01 05:45:00      2          2          2   \n",
       "4 2011-05-01 05:44:05 2011-05-01 05:45:00      2          3          3   \n",
       "5 2011-05-01 05:44:05 2011-05-01 05:45:00      2          4          4   \n",
       "\n",
       "   cpuMeanUsage  canonicalMemUsage  assignedMemUsage  unmappedCacheMemUsage  \\\n",
       "0      0.048950            0.06689          0.077150               0.004684   \n",
       "2      0.000533            0.04016          0.042660               0.000165   \n",
       "3      0.031680            0.00647          0.008163               0.000302   \n",
       "4      0.007507            0.01242          0.013550               0.000123   \n",
       "5      0.010560            0.01036          0.011550               0.000117   \n",
       "\n",
       "   totalCacheMemUsage  ...  cpuMaxUsage  maxDiskTime  cyclesPerInstruction  \\\n",
       "0            0.005333  ...     0.079710     0.002571                 1.222   \n",
       "2            0.000305  ...     0.003906     0.001215                 3.805   \n",
       "3            0.000536  ...     0.082520     0.001429                 1.286   \n",
       "4            0.000375  ...     0.065310     0.000143                 1.378   \n",
       "5            0.000368  ...     0.069950     0.000215                 1.648   \n",
       "\n",
       "   memAccessPerInstruction  samplePortion  aggType  cpuSampledUsage  \\\n",
       "0                 0.001147              0        0         0.039860   \n",
       "2                 0.011300              0        0         0.000468   \n",
       "3                 0.001875              0        0         0.080440   \n",
       "4                 0.002397              0        0         0.005470   \n",
       "5                 0.003659              0        0         0.005974   \n",
       "\n",
       "   processID  primary_usage_id  primary_task_id  \n",
       "0          0              5000             5000  \n",
       "2          1              5011             5011  \n",
       "3          2              5022             5022  \n",
       "4          3              5033             5023  \n",
       "5          4              5044             5024  \n",
       "\n",
       "[5 rows x 23 columns]"
      ],
      "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>first</th>\n      <th>last</th>\n      <th>jobID</th>\n      <th>taskIndex</th>\n      <th>machineID</th>\n      <th>cpuMeanUsage</th>\n      <th>canonicalMemUsage</th>\n      <th>assignedMemUsage</th>\n      <th>unmappedCacheMemUsage</th>\n      <th>totalCacheMemUsage</th>\n      <th>...</th>\n      <th>cpuMaxUsage</th>\n      <th>maxDiskTime</th>\n      <th>cyclesPerInstruction</th>\n      <th>memAccessPerInstruction</th>\n      <th>samplePortion</th>\n      <th>aggType</th>\n      <th>cpuSampledUsage</th>\n      <th>processID</th>\n      <th>primary_usage_id</th>\n      <th>primary_task_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.048950</td>\n      <td>0.06689</td>\n      <td>0.077150</td>\n      <td>0.004684</td>\n      <td>0.005333</td>\n      <td>...</td>\n      <td>0.079710</td>\n      <td>0.002571</td>\n      <td>1.222</td>\n      <td>0.001147</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.039860</td>\n      <td>0</td>\n      <td>5000</td>\n      <td>5000</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>0.000533</td>\n      <td>0.04016</td>\n      <td>0.042660</td>\n      <td>0.000165</td>\n      <td>0.000305</td>\n      <td>...</td>\n      <td>0.003906</td>\n      <td>0.001215</td>\n      <td>3.805</td>\n      <td>0.011300</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.000468</td>\n      <td>1</td>\n      <td>5011</td>\n      <td>5011</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0.031680</td>\n      <td>0.00647</td>\n      <td>0.008163</td>\n      <td>0.000302</td>\n      <td>0.000536</td>\n      <td>...</td>\n      <td>0.082520</td>\n      <td>0.001429</td>\n      <td>1.286</td>\n      <td>0.001875</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.080440</td>\n      <td>2</td>\n      <td>5022</td>\n      <td>5022</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>2</td>\n      <td>3</td>\n      <td>3</td>\n      <td>0.007507</td>\n      <td>0.01242</td>\n      <td>0.013550</td>\n      <td>0.000123</td>\n      <td>0.000375</td>\n      <td>...</td>\n      <td>0.065310</td>\n      <td>0.000143</td>\n      <td>1.378</td>\n      <td>0.002397</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005470</td>\n      <td>3</td>\n      <td>5033</td>\n      <td>5023</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>2011-05-01 05:44:05</td>\n      <td>2011-05-01 05:45:00</td>\n      <td>2</td>\n      <td>4</td>\n      <td>4</td>\n      <td>0.010560</td>\n      <td>0.01036</td>\n      <td>0.011550</td>\n      <td>0.000117</td>\n      <td>0.000368</td>\n      <td>...</td>\n      <td>0.069950</td>\n      <td>0.000215</td>\n      <td>1.648</td>\n      <td>0.003659</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.005974</td>\n      <td>4</td>\n      <td>5044</td>\n      <td>5024</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 23 columns</p>\n</div>"
     },
     "metadata": {},
     "execution_count": 119
    }
   ],
   "source": [
    "# Creating key column\n",
    "\n",
    "timestamps_tu = list(task_usage['first'].astype(str)) # timestamps\n",
    "jobid_tu = list(task_usage['jobID'].astype(str))\n",
    "eventtype_tu = list(task_usage['taskIndex'].astype(str))\n",
    "\n",
    "millisecs = []\n",
    "for i in range(len(timestamps_tu)): \n",
    "    ms = timestamps_tu[i][::-1][0:2] # extract final three digits of time\n",
    "    ji = jobid_tu[i]\n",
    "    et = eventtype_tu[i]\n",
    "    millisecs.append(ms+ji+et) # concatenate jobid, eventtype, time\n",
    "    #millisecs.append(str(list(job_events['time'])[i]).split(' ')[1].split('.')[1])\n",
    "\n",
    "task_usage['primary_task_id'] = millisecs\n",
    "task_usage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "# Categorise jobID, machineID, taskIndex (skipped as this takes a long time to run)\n",
    "#\n",
    "'''\n",
    "factorised_jobids_usage, original_jobids_usage = pd.factorize(task_usage['jobID'])\n",
    "factorised_machineid_usage, original_machineid_usage = pd.factorize(task_usage['machineID'])\n",
    "factorised_taskid_usage, original_taskid_usage = pd.factorize(task_usage['taskIndex'])\n",
    "\n",
    "task_usage['jobID'] = factorised_jobids_usage\n",
    "task_usage['machineID'] = factorised_machineid_usage\n",
    "task_usage['taskIndex'] = factorised_taskid_usage\n",
    "\n",
    "task_usage.head()\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Machine Events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   0   1  2                                             3    4       5\n",
       "0  0   5  0  HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=  0.5  0.2493\n",
       "1  0   6  0  HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=  0.5  0.2493\n",
       "2  0   7  0  HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=  0.5  0.2493\n",
       "3  0  10  0  HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=  0.5  0.2493\n",
       "4  0  13  0  HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=  0.5  0.2493"
      ],
      "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>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n      <th>5</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>5</td>\n      <td>0</td>\n      <td>HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0</td>\n      <td>6</td>\n      <td>0</td>\n      <td>HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0</td>\n      <td>7</td>\n      <td>0</td>\n      <td>HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0</td>\n      <td>10</td>\n      <td>0</td>\n      <td>HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0</td>\n      <td>13</td>\n      <td>0</td>\n      <td>HofLGzk1Or/8Ildj2+Lqv0UGGvY82NLoni8+J/Yy0RU=</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 120
    }
   ],
   "source": [
    "machine_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "        time  machineID  eventType  platformID  capacityCPU  capacityMem\n",
       "0 2011-05-01          5          0           0          0.5       0.2493\n",
       "1 2011-05-01          6          0           0          0.5       0.2493\n",
       "2 2011-05-01          7          0           0          0.5       0.2493\n",
       "3 2011-05-01         10          0           0          0.5       0.2493\n",
       "4 2011-05-01         13          0           0          0.5       0.2493"
      ],
      "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>time</th>\n      <th>machineID</th>\n      <th>eventType</th>\n      <th>platformID</th>\n      <th>capacityCPU</th>\n      <th>capacityMem</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01</td>\n      <td>5</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011-05-01</td>\n      <td>6</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01</td>\n      <td>7</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01</td>\n      <td>10</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01</td>\n      <td>13</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 121
    }
   ],
   "source": [
    "machine_events.columns = ['time', 'machineID', 'eventType', 'platformID', 'capacityCPU', 'capacityMem']\n",
    "\n",
    "# Factorise platformID\n",
    "machine_events['platformID'] = pd.factorize(machine_events['platformID'])[0]\n",
    "\n",
    "# Change time to timestamp\n",
    "machine_events['time'] = pd.to_datetime(machine_events['time'], unit='us', origin='2011-05-01')\n",
    "machine_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "        time  machineID  eventType  platformID  capacityCPU  capacityMem  \\\n",
       "0 2011-05-01          5          0           0          0.5       0.2493   \n",
       "1 2011-05-01          6          0           0          0.5       0.2493   \n",
       "2 2011-05-01          7          0           0          0.5       0.2493   \n",
       "3 2011-05-01         10          0           0          0.5       0.2493   \n",
       "4 2011-05-01         13          0           0          0.5       0.2493   \n",
       "\n",
       "  primary_machine_id  \n",
       "0              00050  \n",
       "1              00060  \n",
       "2              00070  \n",
       "3             000100  \n",
       "4             000130  "
      ],
      "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>time</th>\n      <th>machineID</th>\n      <th>eventType</th>\n      <th>platformID</th>\n      <th>capacityCPU</th>\n      <th>capacityMem</th>\n      <th>primary_machine_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2011-05-01</td>\n      <td>5</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n      <td>00050</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2011-05-01</td>\n      <td>6</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n      <td>00060</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2011-05-01</td>\n      <td>7</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n      <td>00070</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2011-05-01</td>\n      <td>10</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n      <td>000100</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2011-05-01</td>\n      <td>13</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.5</td>\n      <td>0.2493</td>\n      <td>000130</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 122
    }
   ],
   "source": [
    "# Creating key column\n",
    "\n",
    "timestamps_me = list(machine_events['time'].astype(str)) # timestamps\n",
    "jobid_me = list(machine_events['machineID'].astype(str))\n",
    "eventtype_me = list(machine_events['eventType'].astype(str))\n",
    "\n",
    "millisecs = []\n",
    "for i in range(len(timestamps_me)): \n",
    "    ms = timestamps_me[i][::-1][0:3] # extract final three digits of time\n",
    "    ji = jobid_me[i]\n",
    "    et = eventtype_me[i]\n",
    "    millisecs.append(ms+ji+et) # concatenate jobid, eventtype, time\n",
    "    #millisecs.append(str(list(job_events['time'])[i]).split(' ')[1].split('.')[1])\n",
    "\n",
    "machine_events['primary_machine_id'] = millisecs\n",
    "machine_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_events['primary_job_id'] = job_events['primary_job_id'].astype(np.int64)\n",
    "task_events_clean['primary_task_id'] = task_events_clean['primary_task_id'].astype(np.int64)\n",
    "task_events_clean['primary_machine_id'] = task_events_clean['primary_machine_id'].astype(np.int64)\n",
    "task_events_clean['primary_job_id'] = task_events_clean['primary_job_id'].astype(np.int64)\n",
    "task_usage['primary_usage_id'] = task_usage['primary_usage_id'].astype(np.int64)\n",
    "task_usage['primary_task_id'] = task_usage['primary_task_id'].astype(np.int64)\n",
    "machine_events['primary_machine_id'] = machine_events['primary_machine_id'].astype(np.int64)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "job_events.to_csv('job-events.csv', index=False)\n",
    "task_events_clean.to_csv('task-events.csv', index=False)\n",
    "task_usage.to_csv('task-usage.csv', index=False)\n",
    "machine_events.to_csv('machine-events.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.7.7 64-bit",
   "metadata": {
    "interpreter": {
     "hash": "591c6f4e8dd5a542de00483db20b0b87b0b9cd63ff50a8e7046c5495cf4003fe"
    }
   }
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}