sec3.2-transformation_incidents.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "c5b01879c7cbf671",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:28.921999Z",
     "start_time": "2024-06-07T07:21:28.912486Z"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from dateutil import parser\n",
    "from dateutil.tz import gettz\n",
    "from datetime import timedelta\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "from util.util_data import read_data, load_json_column, unnest_dict, get_incident_id, write_partitioned_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af2c027b7e1c2207",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## 0. Metrics\n",
    "\n",
    "- incident_id\n",
    "- Incident_Title\n",
    "- incident_impact_level\n",
    "    - 0: impact-none\n",
    "    - 1: impact-minor\n",
    "    - 2: impact-major\n",
    "    - 3: impact-critical\n",
    "    - 4: impact-maintenance\n",
    "- Impact_Color\n",
    "- {service} (0 or 1)\n",
    "- {stage}_flag (0 or 1)\n",
    "- {stage}_timestamp\n",
    "- {stage}_description"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4310422aa5fe377",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## 1. Incident stages\n",
    "### 1.1 Parse incident stages and save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "6dab4cf29a7b15d2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:28.925229Z",
     "start_time": "2024-06-07T07:21:28.923221Z"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_services(service_str):\n",
    "    if pd.isna(service_str):\n",
    "        return []\n",
    "    else:        \n",
    "        service_str = service_str.split(':')[-1].rstrip('.')\n",
    "        service_str = service_str.replace(\"and\", \",\")\n",
    "        services = service_str.split(',')\n",
    "        return [service.strip() for service in services]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "70fa6dabfb7efef7",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:28.928760Z",
     "start_time": "2024-06-07T07:21:28.925748Z"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def parse_update_time(timestamp_str):\n",
    "    tzinfos = {\n",
    "        \"PST\": gettz(\"America/Los_Angeles\"),\n",
    "        \"PDT\": gettz(\"America/Los_Angeles\")\n",
    "    }\n",
    "    timestamp_str = \" \".join(timestamp_str.split(\". \")[1].split(\" - \"))\n",
    "    timestamp = parser.parse(timestamp_str, ignoretz=False, tzinfos=tzinfos)\n",
    "    return timestamp.astimezone(gettz('UTC'))\n",
    "\n",
    "def parse_updates(updates_list, stages=None):\n",
    "    \"\"\"\n",
    "    Parse updates list to a dictionary with keys like 'Investigating_flag', 'Investigating_timestamp', 'Investigating_description. All the stage='Update' are not included in the dictionary.\n",
    "    \"\"\"\n",
    "    if stages is None:\n",
    "        stages = [\"Investigating\", \"Identified\", \"Monitoring\", \"Resolved\", \"Postmortem\"]\n",
    "    updates_dict = {}\n",
    "    \n",
    "    for stage in stages:\n",
    "        stage = stage.lower()\n",
    "        updates_dict[f'{stage}_flag'] = int(0)\n",
    "        updates_dict[f'{stage}_timestamp'] = None\n",
    "        updates_dict[f'{stage}_description'] = None\n",
    "    \n",
    "    for update in updates_list:\n",
    "        stage = update.get(\"Update_Title\")\n",
    "        if stage in stages:\n",
    "            stage = stage.lower()\n",
    "            updates_dict[f'{stage}_flag'] = int(1)\n",
    "            updates_dict[f'{stage}_timestamp'] = parse_update_time(update.get(\"Update_Timestamp\"))\n",
    "            updates_dict[f'{stage}_description'] = update.get(\"Update_Body\")\n",
    "    return updates_dict\n",
    "\n",
    "def get_incident_provider(incident_url):\n",
    "    if pd.isna(incident_url):\n",
    "        return incident_url\n",
    "    else:\n",
    "        return incident_url.split('/')[2].split('.')[1]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "initial_id",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:29.013686Z",
     "start_time": "2024-06-07T07:21:28.929505Z"
    },
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "loading: data/raw/incident/2024-08-31/anthropic/incident_history_202312_202402.csv\n",
      "loading: data/raw/incident/2024-08-31/anthropic/incident_history_202309_202311.csv\n",
      "loading: data/raw/incident/2024-08-31/anthropic/incident_history_202403_202405.csv\n",
      "loading: data/raw/incident/2024-08-31/anthropic/incident_history_202303_202305.csv\n",
      "loading: data/raw/incident/2024-08-31/anthropic/incident_history_202306_202308.csv\n",
      "loading: data/raw/incident/2024-08-31/anthropic/incident_history_202406_202408.csv\n",
      "loading: data/raw/incident/2024-08-31/characterai/incident_history_202312_202402.csv\n",
      "loading: data/raw/incident/2024-08-31/characterai/incident_history_202309_202311.csv\n",
      "loading: data/raw/incident/2024-08-31/characterai/incident_history_202403_202405.csv\n",
      "loading: data/raw/incident/2024-08-31/characterai/incident_history_202406_202408.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202312_202402.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202309_202311.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202203_202205.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202403_202405.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202303_202305.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202212_202302.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202209_202211.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202106_202108.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202306_202308.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202012_202102.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202103_202105.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202112_202202.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202109_202111.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202406_202408.csv\n",
      "loading: data/raw/incident/2024-08-31/openai/incident_history_202206_202208.csv\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "provider\n",
       "openai       365\n",
       "anthropic    141\n",
       "character     36\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load data\n",
    "# df = pd.read_csv('data/raw/incident/incident_history_test.csv')\n",
    "\n",
    "DATA_TYPE = 'incident'\n",
    "DATA_INPUT_DIR = f'data/raw/{DATA_TYPE}/2024-08-31'\n",
    "DATA_OUTPUT_LAYER = 'clean'\n",
    "\n",
    "df = read_data(DATA_INPUT_DIR)\n",
    "df = df.drop_duplicates()\n",
    "df = df.reset_index(drop=True)\n",
    "df = load_json_column(df, 'Updates')\n",
    "\n",
    "# add a column of where the data is from\n",
    "df['provider'] = df['Incident_Link'].apply(get_incident_provider)\n",
    "df['provider'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "7a07ce4f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Incident_Title</th>\n",
       "      <th>Incident_Link</th>\n",
       "      <th>Incident_color</th>\n",
       "      <th>Incident_Impact</th>\n",
       "      <th>Updates</th>\n",
       "      <th>Service</th>\n",
       "      <th>provider</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Elevated Errors</td>\n",
       "      <td>https://status.anthropic.com/incidents/qv89mzd...</td>\n",
       "      <td>#f1c40f</td>\n",
       "      <td>impact-minor</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: claude.ai, console.ant...</td>\n",
       "      <td>anthropic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Elevated error rate on claude.ai</td>\n",
       "      <td>https://status.anthropic.com/incidents/tmsczhz...</td>\n",
       "      <td>#333333</td>\n",
       "      <td>impact-none</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>anthropic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Elevated error rate on Claude-2.0, Claude-2.1</td>\n",
       "      <td>https://status.anthropic.com/incidents/t6v85cs...</td>\n",
       "      <td>#333333</td>\n",
       "      <td>impact-none</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: claude.ai, console.ant...</td>\n",
       "      <td>anthropic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Elevated error rate on Claude-2.0, Claude-2.1</td>\n",
       "      <td>https://status.anthropic.com/incidents/x5f4s8n...</td>\n",
       "      <td>#333333</td>\n",
       "      <td>impact-none</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: claude.ai, console.ant...</td>\n",
       "      <td>anthropic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>elevated error rates on claude-instant-1.2</td>\n",
       "      <td>https://status.anthropic.com/incidents/gr7nb0d...</td>\n",
       "      <td>#333333</td>\n",
       "      <td>impact-none</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: claude.ai, console.ant...</td>\n",
       "      <td>anthropic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>Outage on some Ada and Babbage models</td>\n",
       "      <td>https://status.openai.com/incidents/qz0h6b7jw268</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Postmortem', 'Update_Body':...</td>\n",
       "      <td>This incident affected: API.</td>\n",
       "      <td>openai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>Cluster Outage</td>\n",
       "      <td>https://status.openai.com/incidents/7h9pjy5d1lz5</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Postmortem', 'Update_Body':...</td>\n",
       "      <td>This incident affected: API.</td>\n",
       "      <td>openai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>539</th>\n",
       "      <td>Logged in websites unavailable</td>\n",
       "      <td>https://status.openai.com/incidents/jzk2gzhfc34g</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: Playground.</td>\n",
       "      <td>openai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540</th>\n",
       "      <td>Base babbage model currently down</td>\n",
       "      <td>https://status.openai.com/incidents/2bxby42r6wv4</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: API and Playground.</td>\n",
       "      <td>openai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>Degradation in serving fine tuned models.</td>\n",
       "      <td>https://status.openai.com/incidents/4cmnntq2t89y</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: API and Playground.</td>\n",
       "      <td>openai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>542 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Incident_Title  \\\n",
       "0                                  Elevated Errors   \n",
       "1                 Elevated error rate on claude.ai   \n",
       "2    Elevated error rate on Claude-2.0, Claude-2.1   \n",
       "3    Elevated error rate on Claude-2.0, Claude-2.1   \n",
       "4       elevated error rates on claude-instant-1.2   \n",
       "..                                             ...   \n",
       "537          Outage on some Ada and Babbage models   \n",
       "538                                 Cluster Outage   \n",
       "539                 Logged in websites unavailable   \n",
       "540              Base babbage model currently down   \n",
       "541      Degradation in serving fine tuned models.   \n",
       "\n",
       "                                         Incident_Link Incident_color  \\\n",
       "0    https://status.anthropic.com/incidents/qv89mzd...        #f1c40f   \n",
       "1    https://status.anthropic.com/incidents/tmsczhz...        #333333   \n",
       "2    https://status.anthropic.com/incidents/t6v85cs...        #333333   \n",
       "3    https://status.anthropic.com/incidents/x5f4s8n...        #333333   \n",
       "4    https://status.anthropic.com/incidents/gr7nb0d...        #333333   \n",
       "..                                                 ...            ...   \n",
       "537   https://status.openai.com/incidents/qz0h6b7jw268        #e86c09   \n",
       "538   https://status.openai.com/incidents/7h9pjy5d1lz5        #e86c09   \n",
       "539   https://status.openai.com/incidents/jzk2gzhfc34g        #e86c09   \n",
       "540   https://status.openai.com/incidents/2bxby42r6wv4        #e86c09   \n",
       "541   https://status.openai.com/incidents/4cmnntq2t89y        #e86c09   \n",
       "\n",
       "    Incident_Impact                                            Updates  \\\n",
       "0      impact-minor  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "1       impact-none  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "2       impact-none  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "3       impact-none  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "4       impact-none  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "..              ...                                                ...   \n",
       "537    impact-major  [{'Update_Title': 'Postmortem', 'Update_Body':...   \n",
       "538    impact-major  [{'Update_Title': 'Postmortem', 'Update_Body':...   \n",
       "539    impact-major  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "540    impact-major  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "541    impact-major  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "\n",
       "                                               Service   provider  \n",
       "0    This incident affected: claude.ai, console.ant...  anthropic  \n",
       "1                                                  NaN  anthropic  \n",
       "2    This incident affected: claude.ai, console.ant...  anthropic  \n",
       "3    This incident affected: claude.ai, console.ant...  anthropic  \n",
       "4    This incident affected: claude.ai, console.ant...  anthropic  \n",
       "..                                                 ...        ...  \n",
       "537                       This incident affected: API.     openai  \n",
       "538                       This incident affected: API.     openai  \n",
       "539                This incident affected: Playground.     openai  \n",
       "540        This incident affected: API and Playground.     openai  \n",
       "541        This incident affected: API and Playground.     openai  \n",
       "\n",
       "[542 rows x 7 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "3e435d4656838d09",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:29.019601Z",
     "start_time": "2024-06-07T07:21:29.014560Z"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# get incident id\n",
    "df['incident_id'] = df['Incident_Link'].apply(get_incident_id)\n",
    "\n",
    "# numerize incident impact level\n",
    "impact_mapping = {\n",
    "    'impact-none': 0,\n",
    "    'impact-minor': 1,\n",
    "    'impact-major': 2,\n",
    "    'impact-critical': 3,\n",
    "    'impact-maintenance': 4\n",
    "}\n",
    "# reverse mapping\n",
    "impact_mapping_reverse = {v: k for k, v in impact_mapping.items()}\n",
    "df['incident_impact_level'] = df['Incident_Impact'].map(impact_mapping)\n",
    "\n",
    "# parse services\n",
    "df['services'] = df['Service'].apply(get_services)\n",
    "all_services = ['Playground', 'API', 'Labs', 'ChatGPT', 'api.anthropic.com', 'claude.ai', 'console.anthropic.com', 'Character.AI'] \n",
    "for service in all_services:\n",
    "    df[service] = df['services'].apply(lambda x: 1 if service in x else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "bc9d0ad80f41fc13",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:29.023247Z",
     "start_time": "2024-06-07T07:21:29.020399Z"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Incident_Title</th>\n",
       "      <th>Incident_Link</th>\n",
       "      <th>Incident_color</th>\n",
       "      <th>Incident_Impact</th>\n",
       "      <th>Updates</th>\n",
       "      <th>Service</th>\n",
       "      <th>provider</th>\n",
       "      <th>incident_id</th>\n",
       "      <th>incident_impact_level</th>\n",
       "      <th>services</th>\n",
       "      <th>Playground</th>\n",
       "      <th>API</th>\n",
       "      <th>Labs</th>\n",
       "      <th>ChatGPT</th>\n",
       "      <th>api.anthropic.com</th>\n",
       "      <th>claude.ai</th>\n",
       "      <th>console.anthropic.com</th>\n",
       "      <th>Character.AI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Elevated Errors</td>\n",
       "      <td>https://status.anthropic.com/incidents/qv89mzd...</td>\n",
       "      <td>#f1c40f</td>\n",
       "      <td>impact-minor</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: claude.ai, console.ant...</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>qv89mzdjvtrs</td>\n",
       "      <td>1</td>\n",
       "      <td>[claude.ai, console.anthropic.com, , api.anthr...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Elevated error rate on claude.ai</td>\n",
       "      <td>https://status.anthropic.com/incidents/tmsczhz...</td>\n",
       "      <td>#333333</td>\n",
       "      <td>impact-none</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>tmsczhzhjd63</td>\n",
       "      <td>0</td>\n",
       "      <td>[]</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Elevated error rate on Claude-2.0, Claude-2.1</td>\n",
       "      <td>https://status.anthropic.com/incidents/t6v85cs...</td>\n",
       "      <td>#333333</td>\n",
       "      <td>impact-none</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: claude.ai, console.ant...</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>t6v85cs8j6jb</td>\n",
       "      <td>0</td>\n",
       "      <td>[claude.ai, console.anthropic.com, , api.anthr...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Elevated error rate on Claude-2.0, Claude-2.1</td>\n",
       "      <td>https://status.anthropic.com/incidents/x5f4s8n...</td>\n",
       "      <td>#333333</td>\n",
       "      <td>impact-none</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: claude.ai, console.ant...</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>x5f4s8nvkyqq</td>\n",
       "      <td>0</td>\n",
       "      <td>[claude.ai, console.anthropic.com, , api.anthr...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>elevated error rates on claude-instant-1.2</td>\n",
       "      <td>https://status.anthropic.com/incidents/gr7nb0d...</td>\n",
       "      <td>#333333</td>\n",
       "      <td>impact-none</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: claude.ai, console.ant...</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>gr7nb0dwyydw</td>\n",
       "      <td>0</td>\n",
       "      <td>[claude.ai, console.anthropic.com, , api.anthr...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>Outage on some Ada and Babbage models</td>\n",
       "      <td>https://status.openai.com/incidents/qz0h6b7jw268</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Postmortem', 'Update_Body':...</td>\n",
       "      <td>This incident affected: API.</td>\n",
       "      <td>openai</td>\n",
       "      <td>qz0h6b7jw268</td>\n",
       "      <td>2</td>\n",
       "      <td>[API]</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>Cluster Outage</td>\n",
       "      <td>https://status.openai.com/incidents/7h9pjy5d1lz5</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Postmortem', 'Update_Body':...</td>\n",
       "      <td>This incident affected: API.</td>\n",
       "      <td>openai</td>\n",
       "      <td>7h9pjy5d1lz5</td>\n",
       "      <td>2</td>\n",
       "      <td>[API]</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>539</th>\n",
       "      <td>Logged in websites unavailable</td>\n",
       "      <td>https://status.openai.com/incidents/jzk2gzhfc34g</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: Playground.</td>\n",
       "      <td>openai</td>\n",
       "      <td>jzk2gzhfc34g</td>\n",
       "      <td>2</td>\n",
       "      <td>[Playground]</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540</th>\n",
       "      <td>Base babbage model currently down</td>\n",
       "      <td>https://status.openai.com/incidents/2bxby42r6wv4</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: API and Playground.</td>\n",
       "      <td>openai</td>\n",
       "      <td>2bxby42r6wv4</td>\n",
       "      <td>2</td>\n",
       "      <td>[API, Playground]</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>Degradation in serving fine tuned models.</td>\n",
       "      <td>https://status.openai.com/incidents/4cmnntq2t89y</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>impact-major</td>\n",
       "      <td>[{'Update_Title': 'Resolved', 'Update_Body': '...</td>\n",
       "      <td>This incident affected: API and Playground.</td>\n",
       "      <td>openai</td>\n",
       "      <td>4cmnntq2t89y</td>\n",
       "      <td>2</td>\n",
       "      <td>[API, Playground]</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>542 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    Incident_Title  \\\n",
       "0                                  Elevated Errors   \n",
       "1                 Elevated error rate on claude.ai   \n",
       "2    Elevated error rate on Claude-2.0, Claude-2.1   \n",
       "3    Elevated error rate on Claude-2.0, Claude-2.1   \n",
       "4       elevated error rates on claude-instant-1.2   \n",
       "..                                             ...   \n",
       "537          Outage on some Ada and Babbage models   \n",
       "538                                 Cluster Outage   \n",
       "539                 Logged in websites unavailable   \n",
       "540              Base babbage model currently down   \n",
       "541      Degradation in serving fine tuned models.   \n",
       "\n",
       "                                         Incident_Link Incident_color  \\\n",
       "0    https://status.anthropic.com/incidents/qv89mzd...        #f1c40f   \n",
       "1    https://status.anthropic.com/incidents/tmsczhz...        #333333   \n",
       "2    https://status.anthropic.com/incidents/t6v85cs...        #333333   \n",
       "3    https://status.anthropic.com/incidents/x5f4s8n...        #333333   \n",
       "4    https://status.anthropic.com/incidents/gr7nb0d...        #333333   \n",
       "..                                                 ...            ...   \n",
       "537   https://status.openai.com/incidents/qz0h6b7jw268        #e86c09   \n",
       "538   https://status.openai.com/incidents/7h9pjy5d1lz5        #e86c09   \n",
       "539   https://status.openai.com/incidents/jzk2gzhfc34g        #e86c09   \n",
       "540   https://status.openai.com/incidents/2bxby42r6wv4        #e86c09   \n",
       "541   https://status.openai.com/incidents/4cmnntq2t89y        #e86c09   \n",
       "\n",
       "    Incident_Impact                                            Updates  \\\n",
       "0      impact-minor  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "1       impact-none  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "2       impact-none  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "3       impact-none  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "4       impact-none  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "..              ...                                                ...   \n",
       "537    impact-major  [{'Update_Title': 'Postmortem', 'Update_Body':...   \n",
       "538    impact-major  [{'Update_Title': 'Postmortem', 'Update_Body':...   \n",
       "539    impact-major  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "540    impact-major  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "541    impact-major  [{'Update_Title': 'Resolved', 'Update_Body': '...   \n",
       "\n",
       "                                               Service   provider  \\\n",
       "0    This incident affected: claude.ai, console.ant...  anthropic   \n",
       "1                                                  NaN  anthropic   \n",
       "2    This incident affected: claude.ai, console.ant...  anthropic   \n",
       "3    This incident affected: claude.ai, console.ant...  anthropic   \n",
       "4    This incident affected: claude.ai, console.ant...  anthropic   \n",
       "..                                                 ...        ...   \n",
       "537                       This incident affected: API.     openai   \n",
       "538                       This incident affected: API.     openai   \n",
       "539                This incident affected: Playground.     openai   \n",
       "540        This incident affected: API and Playground.     openai   \n",
       "541        This incident affected: API and Playground.     openai   \n",
       "\n",
       "      incident_id  incident_impact_level  \\\n",
       "0    qv89mzdjvtrs                      1   \n",
       "1    tmsczhzhjd63                      0   \n",
       "2    t6v85cs8j6jb                      0   \n",
       "3    x5f4s8nvkyqq                      0   \n",
       "4    gr7nb0dwyydw                      0   \n",
       "..            ...                    ...   \n",
       "537  qz0h6b7jw268                      2   \n",
       "538  7h9pjy5d1lz5                      2   \n",
       "539  jzk2gzhfc34g                      2   \n",
       "540  2bxby42r6wv4                      2   \n",
       "541  4cmnntq2t89y                      2   \n",
       "\n",
       "                                              services  Playground  API  Labs  \\\n",
       "0    [claude.ai, console.anthropic.com, , api.anthr...           0    0     0   \n",
       "1                                                   []           0    0     0   \n",
       "2    [claude.ai, console.anthropic.com, , api.anthr...           0    0     0   \n",
       "3    [claude.ai, console.anthropic.com, , api.anthr...           0    0     0   \n",
       "4    [claude.ai, console.anthropic.com, , api.anthr...           0    0     0   \n",
       "..                                                 ...         ...  ...   ...   \n",
       "537                                              [API]           0    1     0   \n",
       "538                                              [API]           0    1     0   \n",
       "539                                       [Playground]           1    0     0   \n",
       "540                                  [API, Playground]           1    1     0   \n",
       "541                                  [API, Playground]           1    1     0   \n",
       "\n",
       "     ChatGPT  api.anthropic.com  claude.ai  console.anthropic.com  \\\n",
       "0          0                  1          1                      1   \n",
       "1          0                  0          0                      0   \n",
       "2          0                  1          1                      1   \n",
       "3          0                  1          1                      1   \n",
       "4          0                  1          1                      1   \n",
       "..       ...                ...        ...                    ...   \n",
       "537        0                  0          0                      0   \n",
       "538        0                  0          0                      0   \n",
       "539        0                  0          0                      0   \n",
       "540        0                  0          0                      0   \n",
       "541        0                  0          0                      0   \n",
       "\n",
       "     Character.AI  \n",
       "0               0  \n",
       "1               0  \n",
       "2               0  \n",
       "3               0  \n",
       "4               0  \n",
       "..            ...  \n",
       "537             0  \n",
       "538             0  \n",
       "539             0  \n",
       "540             0  \n",
       "541             0  \n",
       "\n",
       "[542 rows x 18 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "7e16d1afa366b79f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:29.107016Z",
     "start_time": "2024-06-07T07:21:29.045908Z"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "writing data to: data/clean/incident/2024-08-31/incident_stages.csv\n"
     ]
    }
   ],
   "source": [
    "# unnest stages\n",
    "df_stages = df.copy()\n",
    "df_stages['parsed_updates'] = df_stages['Updates'].apply(parse_updates)\n",
    "df_stages = unnest_dict(df_stages, 'parsed_updates')\n",
    "\n",
    "# time span\n",
    "cols_timestamp = [col for col in df_stages.columns if  'timestamp' in col ]\n",
    "cols_timestamp.pop() # remove the postmortem timestamp\n",
    "df_stages['start_timestamp'] = df_stages[cols_timestamp].min(axis=1)\n",
    "df_stages['close_timestamp'] = df_stages[cols_timestamp].max(axis=1)\n",
    "df_stages['time_span'] = df_stages['close_timestamp'] - df_stages['start_timestamp']\n",
    "df_stages['over_one_day'] = df_stages['time_span'] > timedelta(days=1)\n",
    "\n",
    "# reorder columns\n",
    "cols_incident_info = ['incident_id', 'Incident_Title', 'incident_impact_level', 'Incident_color', 'provider']\n",
    "cols_services = all_services\n",
    "cols_stages = [col for col in df_stages.columns if 'flag' in col or 'timestamp' in col or 'description' in col]\n",
    "cols_timespan = ['time_span', 'over_one_day']\n",
    "\n",
    "df_stages_reordered = df_stages[cols_incident_info + cols_services + cols_stages + cols_timespan]\n",
    "\n",
    "# write to csv\n",
    "stages_path = write_partitioned_data(df_stages_reordered, DATA_OUTPUT_LAYER, DATA_TYPE, 'incident_stages.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "b9ad1a19b1644e34",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:29.116192Z",
     "start_time": "2024-06-07T07:21:29.109091Z"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>incident_id</th>\n",
       "      <th>Incident_Title</th>\n",
       "      <th>incident_impact_level</th>\n",
       "      <th>Incident_color</th>\n",
       "      <th>provider</th>\n",
       "      <th>Playground</th>\n",
       "      <th>API</th>\n",
       "      <th>Labs</th>\n",
       "      <th>ChatGPT</th>\n",
       "      <th>api.anthropic.com</th>\n",
       "      <th>...</th>\n",
       "      <th>resolved_flag</th>\n",
       "      <th>resolved_timestamp</th>\n",
       "      <th>resolved_description</th>\n",
       "      <th>postmortem_flag</th>\n",
       "      <th>postmortem_timestamp</th>\n",
       "      <th>postmortem_description</th>\n",
       "      <th>start_timestamp</th>\n",
       "      <th>close_timestamp</th>\n",
       "      <th>time_span</th>\n",
       "      <th>over_one_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qv89mzdjvtrs</td>\n",
       "      <td>Elevated Errors</td>\n",
       "      <td>1</td>\n",
       "      <td>#f1c40f</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-02-26 18:21:00+00:00</td>\n",
       "      <td>This incident has been resolved.</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-02-26 17:09:00+00:00</td>\n",
       "      <td>2024-02-26 18:21:00+00:00</td>\n",
       "      <td>0 days 01:12:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>tmsczhzhjd63</td>\n",
       "      <td>Elevated error rate on claude.ai</td>\n",
       "      <td>0</td>\n",
       "      <td>#333333</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-02-16 20:35:00+00:00</td>\n",
       "      <td>We experienced an elevated level of errors on ...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-02-16 20:35:00+00:00</td>\n",
       "      <td>2024-02-16 20:35:00+00:00</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>t6v85cs8j6jb</td>\n",
       "      <td>Elevated error rate on Claude-2.0, Claude-2.1</td>\n",
       "      <td>0</td>\n",
       "      <td>#333333</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-02-13 04:36:00+00:00</td>\n",
       "      <td>This incident has been resolved.</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-02-13 03:59:00+00:00</td>\n",
       "      <td>2024-02-13 04:36:00+00:00</td>\n",
       "      <td>0 days 00:37:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>x5f4s8nvkyqq</td>\n",
       "      <td>Elevated error rate on Claude-2.0, Claude-2.1</td>\n",
       "      <td>0</td>\n",
       "      <td>#333333</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-02-13 01:26:00+00:00</td>\n",
       "      <td>This incident has been resolved.</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-02-13 00:21:00+00:00</td>\n",
       "      <td>2024-02-13 01:26:00+00:00</td>\n",
       "      <td>0 days 01:05:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gr7nb0dwyydw</td>\n",
       "      <td>elevated error rates on claude-instant-1.2</td>\n",
       "      <td>0</td>\n",
       "      <td>#333333</td>\n",
       "      <td>anthropic</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2024-02-06 01:55:00+00:00</td>\n",
       "      <td>This incident has been resolved.</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>2024-02-05 23:07:00+00:00</td>\n",
       "      <td>2024-02-06 01:55:00+00:00</td>\n",
       "      <td>0 days 02:48:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>qz0h6b7jw268</td>\n",
       "      <td>Outage on some Ada and Babbage models</td>\n",
       "      <td>2</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>openai</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-07-07 08:12:00+00:00</td>\n",
       "      <td>All models have recovered and are operational....</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-07-12 01:03:00+00:00</td>\n",
       "      <td>Summary:\\nOn July 6, 2022 22:17 PDT (July 7 05...</td>\n",
       "      <td>2022-07-07 06:07:00+00:00</td>\n",
       "      <td>2022-07-07 08:12:00+00:00</td>\n",
       "      <td>0 days 02:05:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>7h9pjy5d1lz5</td>\n",
       "      <td>Cluster Outage</td>\n",
       "      <td>2</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>openai</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-06-24 15:25:00+00:00</td>\n",
       "      <td>All models are operational.  Thank you for you...</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-06-28 03:20:00+00:00</td>\n",
       "      <td>Summary:\\nOn June 23, 2022 18:56 PDT (June 24 ...</td>\n",
       "      <td>2022-06-24 02:02:00+00:00</td>\n",
       "      <td>2022-06-24 15:25:00+00:00</td>\n",
       "      <td>0 days 13:23:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>539</th>\n",
       "      <td>jzk2gzhfc34g</td>\n",
       "      <td>Logged in websites unavailable</td>\n",
       "      <td>2</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>openai</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-06-21 07:38:00+00:00</td>\n",
       "      <td>A fix has been implemented by our service prov...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-06-21 07:00:00+00:00</td>\n",
       "      <td>2022-06-21 07:38:00+00:00</td>\n",
       "      <td>0 days 00:38:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540</th>\n",
       "      <td>2bxby42r6wv4</td>\n",
       "      <td>Base babbage model currently down</td>\n",
       "      <td>2</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>openai</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-06-20 17:06:00+00:00</td>\n",
       "      <td>Serving of babbage models is now healthy. The ...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-06-20 16:34:00+00:00</td>\n",
       "      <td>2022-06-20 17:06:00+00:00</td>\n",
       "      <td>0 days 00:32:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>4cmnntq2t89y</td>\n",
       "      <td>Degradation in serving fine tuned models.</td>\n",
       "      <td>2</td>\n",
       "      <td>#e86c09</td>\n",
       "      <td>openai</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-06-03 22:21:00+00:00</td>\n",
       "      <td>Service should be back to normal after all rol...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-06-03 21:15:00+00:00</td>\n",
       "      <td>2022-06-03 22:21:00+00:00</td>\n",
       "      <td>0 days 01:06:00</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>542 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      incident_id                                 Incident_Title  \\\n",
       "0    qv89mzdjvtrs                                Elevated Errors   \n",
       "1    tmsczhzhjd63               Elevated error rate on claude.ai   \n",
       "2    t6v85cs8j6jb  Elevated error rate on Claude-2.0, Claude-2.1   \n",
       "3    x5f4s8nvkyqq  Elevated error rate on Claude-2.0, Claude-2.1   \n",
       "4    gr7nb0dwyydw     elevated error rates on claude-instant-1.2   \n",
       "..            ...                                            ...   \n",
       "537  qz0h6b7jw268          Outage on some Ada and Babbage models   \n",
       "538  7h9pjy5d1lz5                                 Cluster Outage   \n",
       "539  jzk2gzhfc34g                 Logged in websites unavailable   \n",
       "540  2bxby42r6wv4              Base babbage model currently down   \n",
       "541  4cmnntq2t89y      Degradation in serving fine tuned models.   \n",
       "\n",
       "     incident_impact_level Incident_color   provider  Playground  API  Labs  \\\n",
       "0                        1        #f1c40f  anthropic           0    0     0   \n",
       "1                        0        #333333  anthropic           0    0     0   \n",
       "2                        0        #333333  anthropic           0    0     0   \n",
       "3                        0        #333333  anthropic           0    0     0   \n",
       "4                        0        #333333  anthropic           0    0     0   \n",
       "..                     ...            ...        ...         ...  ...   ...   \n",
       "537                      2        #e86c09     openai           0    1     0   \n",
       "538                      2        #e86c09     openai           0    1     0   \n",
       "539                      2        #e86c09     openai           1    0     0   \n",
       "540                      2        #e86c09     openai           1    1     0   \n",
       "541                      2        #e86c09     openai           1    1     0   \n",
       "\n",
       "     ChatGPT  api.anthropic.com  ...  resolved_flag        resolved_timestamp  \\\n",
       "0          0                  1  ...              1 2024-02-26 18:21:00+00:00   \n",
       "1          0                  0  ...              1 2024-02-16 20:35:00+00:00   \n",
       "2          0                  1  ...              1 2024-02-13 04:36:00+00:00   \n",
       "3          0                  1  ...              1 2024-02-13 01:26:00+00:00   \n",
       "4          0                  1  ...              1 2024-02-06 01:55:00+00:00   \n",
       "..       ...                ...  ...            ...                       ...   \n",
       "537        0                  0  ...              1 2022-07-07 08:12:00+00:00   \n",
       "538        0                  0  ...              1 2022-06-24 15:25:00+00:00   \n",
       "539        0                  0  ...              1 2022-06-21 07:38:00+00:00   \n",
       "540        0                  0  ...              1 2022-06-20 17:06:00+00:00   \n",
       "541        0                  0  ...              1 2022-06-03 22:21:00+00:00   \n",
       "\n",
       "                                  resolved_description  postmortem_flag  \\\n",
       "0                     This incident has been resolved.                0   \n",
       "1    We experienced an elevated level of errors on ...                0   \n",
       "2                     This incident has been resolved.                0   \n",
       "3                     This incident has been resolved.                0   \n",
       "4                     This incident has been resolved.                0   \n",
       "..                                                 ...              ...   \n",
       "537  All models have recovered and are operational....                1   \n",
       "538  All models are operational.  Thank you for you...                1   \n",
       "539  A fix has been implemented by our service prov...                0   \n",
       "540  Serving of babbage models is now healthy. The ...                0   \n",
       "541  Service should be back to normal after all rol...                0   \n",
       "\n",
       "         postmortem_timestamp  \\\n",
       "0                         NaT   \n",
       "1                         NaT   \n",
       "2                         NaT   \n",
       "3                         NaT   \n",
       "4                         NaT   \n",
       "..                        ...   \n",
       "537 2022-07-12 01:03:00+00:00   \n",
       "538 2022-06-28 03:20:00+00:00   \n",
       "539                       NaT   \n",
       "540                       NaT   \n",
       "541                       NaT   \n",
       "\n",
       "                                postmortem_description  \\\n",
       "0                                                 None   \n",
       "1                                                 None   \n",
       "2                                                 None   \n",
       "3                                                 None   \n",
       "4                                                 None   \n",
       "..                                                 ...   \n",
       "537  Summary:\\nOn July 6, 2022 22:17 PDT (July 7 05...   \n",
       "538  Summary:\\nOn June 23, 2022 18:56 PDT (June 24 ...   \n",
       "539                                               None   \n",
       "540                                               None   \n",
       "541                                               None   \n",
       "\n",
       "              start_timestamp           close_timestamp       time_span  \\\n",
       "0   2024-02-26 17:09:00+00:00 2024-02-26 18:21:00+00:00 0 days 01:12:00   \n",
       "1   2024-02-16 20:35:00+00:00 2024-02-16 20:35:00+00:00 0 days 00:00:00   \n",
       "2   2024-02-13 03:59:00+00:00 2024-02-13 04:36:00+00:00 0 days 00:37:00   \n",
       "3   2024-02-13 00:21:00+00:00 2024-02-13 01:26:00+00:00 0 days 01:05:00   \n",
       "4   2024-02-05 23:07:00+00:00 2024-02-06 01:55:00+00:00 0 days 02:48:00   \n",
       "..                        ...                       ...             ...   \n",
       "537 2022-07-07 06:07:00+00:00 2022-07-07 08:12:00+00:00 0 days 02:05:00   \n",
       "538 2022-06-24 02:02:00+00:00 2022-06-24 15:25:00+00:00 0 days 13:23:00   \n",
       "539 2022-06-21 07:00:00+00:00 2022-06-21 07:38:00+00:00 0 days 00:38:00   \n",
       "540 2022-06-20 16:34:00+00:00 2022-06-20 17:06:00+00:00 0 days 00:32:00   \n",
       "541 2022-06-03 21:15:00+00:00 2022-06-03 22:21:00+00:00 0 days 01:06:00   \n",
       "\n",
       "     over_one_day  \n",
       "0           False  \n",
       "1           False  \n",
       "2           False  \n",
       "3           False  \n",
       "4           False  \n",
       "..            ...  \n",
       "537         False  \n",
       "538         False  \n",
       "539         False  \n",
       "540         False  \n",
       "541         False  \n",
       "\n",
       "[542 rows x 32 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stages_reordered"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21501899c77e80c8",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "### 1.2 Incident Stages Overview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "2ad0184f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_openai = df_stages_reordered.loc[df_stages_reordered['provider'] == 'openai']\n",
    "df_anthropic = df_stages_reordered.loc[df_stages_reordered['provider'] == 'anthropic']\n",
    "df_characterai = df_stages_reordered.loc[df_stages_reordered['provider'] == 'character']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "6cc5c41219b96a9d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:29.171242Z",
     "start_time": "2024-06-07T07:21:29.116747Z"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n",
      "/var/folders/y1/bc3yybl90rxfhtj2gdqnbwg80000gn/T/ipykernel_77323/3314896956.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_stages[col] = pd.to_datetime(df_stages[col])\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Incident Count</th>\n",
       "      <th>Start</th>\n",
       "      <th>End</th>\n",
       "      <th>impact-none</th>\n",
       "      <th>impact-minor</th>\n",
       "      <th>impact-major</th>\n",
       "      <th>impact-critical</th>\n",
       "      <th>impact-maintenance</th>\n",
       "      <th>investigating</th>\n",
       "      <th>identified</th>\n",
       "      <th>monitoring</th>\n",
       "      <th>resolved</th>\n",
       "      <th>postmortem</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>365</td>\n",
       "      <td>2021-02-09 18:00:00+00:00</td>\n",
       "      <td>2024-08-28 20:36:00+00:00</td>\n",
       "      <td>52</td>\n",
       "      <td>141</td>\n",
       "      <td>125</td>\n",
       "      <td>46</td>\n",
       "      <td>1.0</td>\n",
       "      <td>259</td>\n",
       "      <td>144</td>\n",
       "      <td>225</td>\n",
       "      <td>365</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>141</td>\n",
       "      <td>2023-03-16 03:00:00+00:00</td>\n",
       "      <td>2024-08-30 20:30:00+00:00</td>\n",
       "      <td>44</td>\n",
       "      <td>48</td>\n",
       "      <td>43</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>96</td>\n",
       "      <td>45</td>\n",
       "      <td>51</td>\n",
       "      <td>141</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>36</td>\n",
       "      <td>2023-10-24 21:15:00+00:00</td>\n",
       "      <td>2024-08-17 04:46:00+00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26</td>\n",
       "      <td>16</td>\n",
       "      <td>15</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Incident Count                     Start                       End  \\\n",
       "0             365 2021-02-09 18:00:00+00:00 2024-08-28 20:36:00+00:00   \n",
       "0             141 2023-03-16 03:00:00+00:00 2024-08-30 20:30:00+00:00   \n",
       "0              36 2023-10-24 21:15:00+00:00 2024-08-17 04:46:00+00:00   \n",
       "\n",
       "   impact-none  impact-minor  impact-major  impact-critical  \\\n",
       "0           52           141           125               46   \n",
       "0           44            48            43                5   \n",
       "0            2             4            11               19   \n",
       "\n",
       "   impact-maintenance  investigating  identified  monitoring  resolved  \\\n",
       "0                 1.0            259         144         225       365   \n",
       "0                 1.0             96          45          51       141   \n",
       "0                 NaN             26          16          15        36   \n",
       "\n",
       "   postmortem  \n",
       "0          29  \n",
       "0           2  \n",
       "0           2  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_summary = pd.DataFrame()\n",
    "for df_stages in [df_openai, df_anthropic, df_characterai]:\n",
    "    incident_count = df_stages['incident_id'].nunique()\n",
    "\n",
    "    cols_timestamp = [col for col in df_stages.columns if  'timestamp' in col ]\n",
    "    for col in cols_timestamp:\n",
    "        df_stages[col] = pd.to_datetime(df_stages[col])\n",
    "    start_time = df_stages[cols_timestamp].min().min()\n",
    "    end_time = df_stages[cols_timestamp].max().max()\n",
    "\n",
    "    impact_level_count = df_stages['incident_impact_level'].value_counts().sort_index().rename(index=impact_mapping_reverse)\n",
    "\n",
    "    stage_counts = {}\n",
    "    cols_flag = [col for col in df_stages.columns if  'flag' in col ]\n",
    "    for col in cols_flag:\n",
    "        stage = col.split('_')[0]\n",
    "        stage_counts[stage] = df_stages[col].sum()\n",
    "\n",
    "    flat_dict = {**{'Incident Count': incident_count, 'Start': start_time, 'End': end_time}, **impact_level_count, **stage_counts}\n",
    "    df_stages_summary = pd.DataFrame([flat_dict])\n",
    "    # df_stages_summary.to_csv('plot/table/incident_summary.csv', index=False)\n",
    "\n",
    "    df_summary = pd.concat([df_summary, df_stages_summary])\n",
    "\n",
    "df_summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "368fe37acb446c36",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:29.228358Z",
     "start_time": "2024-06-07T07:21:29.171818Z"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\\\begin{tabular}{rllrrrrrrrrrr}\\n\\\\toprule\\nIncident Count & Start & End & impact-none & impact-minor & impact-major & impact-critical & impact-maintenance & investigating & identified & monitoring & resolved & postmortem \\\\\\\\\\n\\\\midrule\\n322 & 2021-02-09 18:00:00+00:00 & 2024-05-24 21:04:00+00:00 & 42 & 130 & 107 & 42 & 1 & 229 & 132 & 199 & 322 & 25 \\\\\\\\\\n\\\\bottomrule\\n\\\\end{tabular}\\n'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "latex_code = df_stages_summary.to_latex(index=False)\n",
    "latex_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4f64ba754e2dcf88",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-06-07T07:21:29.232516Z",
     "start_time": "2024-06-07T07:21:29.230756Z"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['incident_id', 'Incident_Title', 'incident_impact_level',\n",
       "       'Incident_color', 'Playground', 'API', 'Labs', 'ChatGPT',\n",
       "       'investigating_flag', 'investigating_timestamp',\n",
       "       'investigating_description', 'identified_flag', 'identified_timestamp',\n",
       "       'identified_description', 'monitoring_flag', 'monitoring_timestamp',\n",
       "       'monitoring_description', 'resolved_flag', 'resolved_timestamp',\n",
       "       'resolved_description', 'postmortem_flag', 'postmortem_timestamp',\n",
       "       'postmortem_description', 'start_timestamp', 'close_timestamp',\n",
       "       'time_span', 'over_one_day'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stages.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ebd60f2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}