Data_preprocessing.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# Data preprocessing"
      ],
      "metadata": {
        "id": "GqeqqNp5APVw"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "VUHpT4ztbKOB",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "17d9911a-f267-4106-b4b7-3f152653662a"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "<ipython-input-1-246e7720ea68>:6: TqdmExperimentalWarning: Using `tqdm.autonotebook.tqdm` in notebook mode. Use `tqdm.tqdm` instead to force console mode (e.g. in jupyter console)\n",
            "  from tqdm.autonotebook import tqdm\n"
          ]
        }
      ],
      "source": [
        "# Import required libraries\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "import numpy as np\n",
        "from datetime import datetime, timedelta\n",
        "from tqdm.autonotebook import tqdm\n",
        "from IPython.display import Markdown, display"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Access folder containing acitivity data\n",
        "data_folder = \"/content/drive/Shareddrives/OSM-ODECO/HOTOSM/activities_output/\"\n",
        "in_drive = True  # True to mount a drive while working in Google Colab\n",
        "if in_drive:\n",
        "    from google.colab import drive\n",
        "    drive.mount(\"/content/drive\", force_remount=True)"
      ],
      "metadata": {
        "id": "QNvKZKn19SNr",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "572e540e-dffc-4322-8d63-f09d520b3315"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "0) Create file with project data (\"projects.csv)"
      ],
      "metadata": {
        "id": "wGUxDGfJCjzw"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Read file containing the list of HOT projects to be analised\n",
        "input_data = pd.read_csv(data_folder + \"output_archived_projs_selected_ids.csv\")\n",
        "projects = pd.read_csv(data_folder + \"output_archived_projs.csv\")"
      ],
      "metadata": {
        "id": "8RxuJcRp9VAW"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#https://wiki.openstreetmap.org/wiki/Humanitarian_OSM_Team/Priority_countries\n",
        "country_to_abbrev = {\n",
        "    \"Andorra\": \"AD\",\n",
        "    \"United Arab Emirates\": \"AE\",\n",
        "    \"Afghanistan\": \"AF\",\n",
        "    \"Antigua and Barbuda\": \"AG\",\n",
        "    \"Anguilla\": \"AI\",\n",
        "    \"Albania\": \"AL\",\n",
        "    \"Armenia\": \"AM\",\n",
        "    \"Angola\": \"AO\",\n",
        "    \"Antarctica\": \"AQ\",\n",
        "    \"Argentina\": \"AR\",\n",
        "    \"American Samoa\": \"AS\",\n",
        "    \"Austria\": \"AT\",\n",
        "    \"Australia\": \"AU\",\n",
        "    \"Aruba\": \"AW\",\n",
        "    \"Åland Islands\": \"AX\",\n",
        "    \"Azerbaijan\": \"AZ\",\n",
        "    \"Bosnia and Herzegovina\": \"BA\",\n",
        "    \"Barbados\": \"BB\",\n",
        "    \"Bangladesh\": \"BD\",\n",
        "    \"Belgium\": \"BE\",\n",
        "    \"Burkina Faso\": \"BF\",\n",
        "    \"Bulgaria\": \"BG\",\n",
        "    \"Bahrain\": \"BH\",\n",
        "    \"Burundi\": \"BI\",\n",
        "    \"Benin\": \"BJ\",\n",
        "    \"Saint Barthélemy\": \"BL\",\n",
        "    \"Bermuda\": \"BM\",\n",
        "    \"Brunei Darussalam\": \"BN\",\n",
        "    \"Bolivia\": \"BO\",\n",
        "    \"Bonaire, Sint Eustatius and Saba\": \"BQ\",\n",
        "    \"Brazil\": \"BR\",\n",
        "    \"The Bahamas\": \"BS\",\n",
        "    \"Bhutan\": \"BT\",\n",
        "    \"Bouvet Island\": \"BV\",\n",
        "    \"Botswana\": \"BW\",\n",
        "    \"Belarus\": \"BY\",\n",
        "    \"Belize\": \"BZ\",\n",
        "    \"Canada\": \"CA\",\n",
        "    \"Cocos (Keeling) Islands\": \"CC\",\n",
        "    \"Democratic Republic of the Congo\": \"CD\",\n",
        "    \"Central African Republic\": \"CF\",\n",
        "    \"Congo-Brazzaville\": \"CG\",\n",
        "    \"Switzerland\": \"CH\",\n",
        "    \"Côte dIvoire\": \"CI\",\n",
        "    \"Cook Islands\": \"CK\",\n",
        "    \"Chile\": \"CL\",\n",
        "    \"Cameroon\": \"CM\",\n",
        "    \"China\": \"CN\",\n",
        "    \"Colombia\": \"CO\",\n",
        "    \"Costa Rica\": \"CR\",\n",
        "    \"Cuba\": \"CU\",\n",
        "    \"Cabo Verde\": \"CV\",\n",
        "    \"Curaçao\": \"CW\",\n",
        "    \"Christmas Island\": \"CX\",\n",
        "    \"Cyprus\": \"CY\",\n",
        "    \"Czechia\": \"CZ\",\n",
        "    \"Germany\": \"DE\",\n",
        "    \"Djibouti\": \"DJ\",\n",
        "    \"Denmark\": \"DK\",\n",
        "    \"Dominica\": \"DM\",\n",
        "    \"Dominican Republic\": \"DO\",\n",
        "    \"Algeria\": \"DZ\",\n",
        "    \"Ecuador\": \"EC\",\n",
        "    \"Estonia\": \"EE\",\n",
        "    \"Egypt\": \"EG\",\n",
        "    \"Western Sahara\": \"EH\",\n",
        "    \"Eritrea\": \"ER\",\n",
        "    \"Spain\": \"ES\",\n",
        "    \"Ethiopia\": \"ET\",\n",
        "    \"Finland\": \"FI\",\n",
        "    \"Fiji\": \"FJ\",\n",
        "    \"Falkland Islands (Malvinas)\": \"FK\",\n",
        "    \"Federated States of Micronesia\": \"FM\",\n",
        "    \"Faroe Islands\": \"FO\",\n",
        "    \"France\": \"FR\",\n",
        "    \"Gabon\": \"GA\",\n",
        "    \"United Kingdom\": \"GB\",\n",
        "    \"Grenada\": \"GD\",\n",
        "    \"Georgia\": \"GE\",\n",
        "    \"French Guiana\": \"GF\",\n",
        "    \"Guernsey\": \"GG\",\n",
        "    \"Ghana\": \"GH\",\n",
        "    \"Gibraltar\": \"GI\",\n",
        "    \"Greenland\": \"GL\",\n",
        "    \"The Gambia\": \"GM\",\n",
        "    \"Guinea\": \"GN\",\n",
        "    \"Guadeloupe\": \"GP\",\n",
        "    \"Equatorial Guinea\": \"GQ\",\n",
        "    \"Greece\": \"GR\",\n",
        "    \"South Georgia and the South Sandwich Islands\": \"GS\",\n",
        "    \"Guatemala\": \"GT\",\n",
        "    \"Guam\": \"GU\",\n",
        "    \"Guinea-Bissau\": \"GW\",\n",
        "    \"Guyana\": \"GY\",\n",
        "    \"Hong Kong\": \"HK\",\n",
        "    \"Heard Island and McDonald Islands\": \"HM\",\n",
        "    \"Honduras\": \"HN\",\n",
        "    \"Croatia\": \"HR\",\n",
        "    \"Haiti\": \"HT\",\n",
        "    \"Hungary\": \"HU\",\n",
        "    \"Indonesia\": \"ID\",\n",
        "    \"Ireland\": \"IE\",\n",
        "    \"Israel\": \"IL\",\n",
        "    \"Isle of Man\": \"IM\",\n",
        "    \"India\": \"IN\",\n",
        "    \"British Indian Ocean Territory\": \"IO\",\n",
        "    \"Iraq\": \"IQ\",\n",
        "    \"Iran (Islamic Republic of)\": \"IR\",\n",
        "    \"Iceland\": \"IS\",\n",
        "    \"Italy\": \"IT\",\n",
        "    \"Jersey\": \"JE\",\n",
        "    \"Jamaica\": \"JM\",\n",
        "    \"Jordan\": \"JO\",\n",
        "    \"Japan\": \"JP\",\n",
        "    \"Kenya\": \"KE\",\n",
        "    \"Kyrgyzstan\": \"KG\",\n",
        "    \"Cambodia\": \"KH\",\n",
        "    \"Kiribati\": \"KI\",\n",
        "    \"Comoros\": \"KM\",\n",
        "    \"Saint Kitts and Nevis\": \"KN\",\n",
        "    \"Korea (Democratic People's Republic of)\": \"KP\",\n",
        "    \"Korea, Republic of\": \"KR\",\n",
        "    \"Kuwait\": \"KW\",\n",
        "    \"Cayman Islands\": \"KY\",\n",
        "    \"Kazakhstan\": \"KZ\",\n",
        "    \"Laos\": \"LA\",\n",
        "    \"Lebanon\": \"LB\",\n",
        "    \"Saint Lucia\": \"LC\",\n",
        "    \"Liechtenstein\": \"LI\",\n",
        "    \"Sri Lanka\": \"LK\",\n",
        "    \"Liberia\": \"LR\",\n",
        "    \"Lesotho\": \"LS\",\n",
        "    \"Lithuania\": \"LT\",\n",
        "    \"Luxembourg\": \"LU\",\n",
        "    \"Latvia\": \"LV\",\n",
        "    \"Libya\": \"LY\",\n",
        "    \"Morocco\": \"MA\",\n",
        "    \"Monaco\": \"MC\",\n",
        "    \"Moldova\": \"MD\",\n",
        "    \"Montenegro\": \"ME\",\n",
        "    \"Saint Martin (French part)\": \"MF\",\n",
        "    \"Madagascar\": \"MG\",\n",
        "    \"Marshall Islands\": \"MH\",\n",
        "    \"North Macedonia\": \"MK\",\n",
        "    \"Mali\": \"ML\",\n",
        "    \"Myanmar\": \"MM\",\n",
        "    \"Mongolia\": \"MN\",\n",
        "    \"Macao\": \"MO\",\n",
        "    \"Northern Mariana Islands\": \"MP\",\n",
        "    \"Martinique\": \"MQ\",\n",
        "    \"Mauritania\": \"MR\",\n",
        "    \"Montserrat\": \"MS\",\n",
        "    \"Malta\": \"MT\",\n",
        "    \"Mauritius\": \"MU\",\n",
        "    \"Maldives\": \"MV\",\n",
        "    \"Malawi\": \"MW\",\n",
        "    \"Mexico\": \"MX\",\n",
        "    \"Malaysia\": \"MY\",\n",
        "    \"Mozambique\": \"MZ\",\n",
        "    \"Namibia\": \"NA\",\n",
        "    \"New Caledonia\": \"NC\",\n",
        "    \"Niger\": \"NE\",\n",
        "    \"Norfolk Island\": \"NF\",\n",
        "    \"Nigeria\": \"NG\",\n",
        "    \"Nicaragua\": \"NI\",\n",
        "    \"Netherlands\": \"NL\",\n",
        "    \"Netherlands Antilles\": \"AN\",\n",
        "    \"Norway\": \"NO\",\n",
        "    \"Nepal\": \"NP\",\n",
        "    \"Nauru\": \"NR\",\n",
        "    \"Niue\": \"NU\",\n",
        "    \"New Zealand\": \"NZ\",\n",
        "    \"Oman\": \"OM\",\n",
        "    \"Panama\": \"PA\",\n",
        "    \"Peru\": \"PE\",\n",
        "    \"French Polynesia\": \"PF\",\n",
        "    \"Papua New Guinea\": \"PG\",\n",
        "    \"Philippines\": \"PH\",\n",
        "    \"Pakistan\": \"PK\",\n",
        "    \"Poland\": \"PL\",\n",
        "    \"Saint Pierre and Miquelon\": \"PM\",\n",
        "    \"Pitcairn\": \"PN\",\n",
        "    \"Puerto Rico\": \"PR\",\n",
        "    \"Palestine\": \"PS\",\n",
        "    \"Portugal\": \"PT\",\n",
        "    \"Palau\": \"PW\",\n",
        "    \"Paraguay\": \"PY\",\n",
        "    \"Qatar\": \"QA\",\n",
        "    \"Réunion\": \"RE\",\n",
        "    \"Romania\": \"RO\",\n",
        "    \"Serbia\": \"RS\",\n",
        "    \"Russia\": \"RU\",\n",
        "    \"Rwanda\": \"RW\",\n",
        "    \"Saudi Arabia\": \"SA\",\n",
        "    \"Solomon Islands\": \"SB\",\n",
        "    \"Seychelles\": \"SC\",\n",
        "    \"Sudan\": \"SD\",\n",
        "    \"Sweden\": \"SE\",\n",
        "    \"Singapore\": \"SG\",\n",
        "    \"Saint Helena, Ascension and Tristan da Cunha\": \"SH\",\n",
        "    \"Slovenia\": \"SI\",\n",
        "    \"Svalbard and Jan Mayen\": \"SJ\",\n",
        "    \"Slovakia\": \"SK\",\n",
        "    \"Sierra Leone\": \"SL\",\n",
        "    \"San Marino\": \"SM\",\n",
        "    \"Senegal\": \"SN\",\n",
        "    \"Somalia\": \"SO\",\n",
        "    \"Somaliland\": \"SO\",\n",
        "    \"Suriname\": \"SR\",\n",
        "    \"South Sudan\": \"SS\",\n",
        "    \"Sao Tome and Principe\": \"ST\",\n",
        "    \"El Salvador\": \"SV\",\n",
        "    \"Sint Maarten (Dutch part)\": \"SX\",\n",
        "    \"Syria\": \"SY\",\n",
        "    \"Eswatini\": \"SZ\",\n",
        "    \"Turks and Caicos Islands\": \"TC\",\n",
        "    \"Chad\": \"TD\",\n",
        "    \"French Southern Territories\": \"TF\",\n",
        "    \"Togo\": \"TG\",\n",
        "    \"Thailand\": \"TH\",\n",
        "    \"Tajikistan\": \"TJ\",\n",
        "    \"Tokelau\": \"TK\",\n",
        "    \"East Timor\": \"TL\",\n",
        "    \"Turkmenistan\": \"TM\",\n",
        "    \"Tunisia\": \"TN\",\n",
        "    \"Tonga\": \"TO\",\n",
        "    \"Turkey\": \"TR\",\n",
        "    \"Trinidad and Tobago\": \"TT\",\n",
        "    \"Tuvalu\": \"TV\",\n",
        "    \"Taiwan, Province of China\": \"TW\",\n",
        "    \"Tanzania\": \"TZ\",\n",
        "    \"Ukraine\": \"UA\",\n",
        "    \"Uganda\": \"UG\",\n",
        "    \"United States Minor Outlying Islands\": \"UM\",\n",
        "    \"United States\": \"US\",\n",
        "    \"Uruguay\": \"UY\",\n",
        "    \"Uzbekistan\": \"UZ\",\n",
        "    \"Holy See\": \"VA\",\n",
        "    \"Saint Vincent and the Grenadines\": \"VC\",\n",
        "    \"Venezuela\": \"VE\",\n",
        "    \"British Virgin Islands\": \"VG\",\n",
        "    \"United States Virgin Islands\": \"VI\",\n",
        "    \"Vietnam\": \"VN\",\n",
        "    \"Vanuatu\": \"VU\",\n",
        "    \"Wallis and Futuna\": \"WF\",\n",
        "    \"Samoa\": \"WS\",\n",
        "    \"Yemen\": \"YE\",\n",
        "    \"Mayotte\": \"YT\",\n",
        "    \"South Africa\": \"ZA\",\n",
        "    \"Zambia\": \"ZM\",\n",
        "    \"Zimbabwe\": \"ZW\",\n",
        "}"
      ],
      "metadata": {
        "id": "kdxkRKAOA-RE"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "country_region = {\n",
        "    \"Andorra\": \"OTHER\",\n",
        "    \"United Arab Emirates\": \"OTHER\",\n",
        "    \"Afghanistan\": \"AP\",\n",
        "    \"Antigua and Barbuda\": \"LAC\",\n",
        "    \"Anguilla\": \"OTHER\",\n",
        "    \"Albania\": \"OTHER\",\n",
        "    \"Armenia\": \"OTHER\",\n",
        "    \"Angola\": \"ESA\",\n",
        "    \"Antarctica\": \"OTHER\",\n",
        "    \"Argentina\": \"OTHER\",\n",
        "    \"American Samoa\": \"OTHER\",\n",
        "    \"Austria\": \"OTHER\",\n",
        "    \"Australia\": \"OTHER\",\n",
        "    \"Aruba\": \"OTHER\",\n",
        "    \"Åland Islands\": \"OTHER\",\n",
        "    \"Azerbaijan\": \"OTHER\",\n",
        "    \"Bosnia and Herzegovina\": \"OTHER\",\n",
        "    \"Barbados\": \"OTHER\",\n",
        "    \"Bangladesh\": \"AP\",\n",
        "    \"Belgium\": \"OTHER\",\n",
        "    \"Burkina Faso\": \"WNA\",\n",
        "    \"Bulgaria\": \"OTHER\",\n",
        "    \"Bahrain\": \"OTHER\",\n",
        "    \"Burundi\": \"ESA\",\n",
        "    \"Benin\": \"WNA\",\n",
        "    \"Saint Barthélemy\": \"OTHER\",\n",
        "    \"Bermuda\": \"OTHER\",\n",
        "    \"Brunei Darussalam\": \"AP\",\n",
        "    \"Bolivia\": \"LAC\",\n",
        "    \"Bonaire, Sint Eustatius and Saba\": \"OTHER\",\n",
        "    \"Brazil\": \"LAC\",\n",
        "    \"The Bahamas\": \"OTHER\",\n",
        "    \"Bhutan\": \"AP\",\n",
        "    \"Bouvet Island\": \"OTHER\",\n",
        "    \"Botswana\": \"OTHER\",\n",
        "    \"Belarus\": \"OTHER\",\n",
        "    \"Belize\": \"LAC\",\n",
        "    \"Canada\": \"OTHER\",\n",
        "    \"Cocos (Keeling) Islands\": \"OTHER\",\n",
        "    \"Democratic Republic of the Congo\": \"ESA\",\n",
        "    \"Central African Republic\": \"WNA\",\n",
        "    \"Congo-Brazzaville\": \"WNA\",\n",
        "    \"Switzerland\": \"OTHER\",\n",
        "    \"Côte dIvoire\": \"WNA\",\n",
        "    \"Cook Islands\": \"OTHER\",\n",
        "    \"Chile\": \"LAC\",\n",
        "    \"Cameroon\": \"WNA\",\n",
        "    \"China\": \"OTHER\",\n",
        "    \"Colombia\": \"OTHER\",\n",
        "    \"Costa Rica\": \"LAC\",\n",
        "    \"Cuba\": \"OTHER\",\n",
        "    \"Cabo Verde\": \"WNA\",\n",
        "    \"Curaçao\": \"OTHER\",\n",
        "    \"Christmas Island\": \"OTHER\",\n",
        "    \"Cyprus\": \"OTHER\",\n",
        "    \"Czechia\": \"OTHER\",\n",
        "    \"Germany\": \"OTHER\",\n",
        "    \"Djibouti\": \"ESA\",\n",
        "    \"Denmark\": \"OTHER\",\n",
        "    \"Dominica\": \"LAC\",\n",
        "    \"Dominican Republic\": \"LAC\",\n",
        "    \"Algeria\": \"WNA\",\n",
        "    \"Ecuador\": \"LAC\",\n",
        "    \"Estonia\": \"OTHER\",\n",
        "    \"Egypt\": \"ESA\",\n",
        "    \"Western Sahara\": \"OTHER\",\n",
        "    \"Eritrea\": \"OTHER\",\n",
        "    \"Spain\": \"OTHER\",\n",
        "    \"Ethiopia\": \"ESA\",\n",
        "    \"Finland\": \"OTHER\",\n",
        "    \"Fiji\": \"AP\",\n",
        "    \"Falkland Islands (Malvinas)\": \"OTHER\",\n",
        "    \"Federated States of Micronesia\": \"AP\",\n",
        "    \"Faroe Islands\": \"OTHER\",\n",
        "    \"France\": \"OTHER\",\n",
        "    \"Gabon\": \"OTHER\",\n",
        "    \"United Kingdom\": \"OTHER\",\n",
        "    \"Grenada\": \"OTHER\",\n",
        "    \"Georgia\": \"OTHER\",\n",
        "    \"French Guiana\": \"OTHER\",\n",
        "    \"Guernsey\": \"OTHER\",\n",
        "    \"Ghana\": \"WNA\",\n",
        "    \"Gibraltar\": \"OTHER\",\n",
        "    \"Greenland\": \"OTHER\",\n",
        "    \"The Gambia\": \"WNA\",\n",
        "    \"Guinea\": \"WNA\",\n",
        "    \"Guadeloupe\": \"OTHER\",\n",
        "    \"Equatorial Guinea\": \"WNA\",\n",
        "    \"Greece\": \"OTHER\",\n",
        "    \"South Georgia and the South Sandwich Islands\": \"OTHER\",\n",
        "    \"Guatemala\": \"LAC\",\n",
        "    \"Guam\": \"OTHER\",\n",
        "    \"Guinea-Bissau\": \"WNA\",\n",
        "    \"Guyana\": \"LAC\",\n",
        "    \"Hong Kong\": \"OTHER\",\n",
        "    \"Heard Island and McDonald Islands\": \"OTHER\",\n",
        "    \"Honduras\": \"LAC\",\n",
        "    \"Croatia\": \"OTHER\",\n",
        "    \"Haiti\": \"LAC\",\n",
        "    \"Hungary\": \"OTHER\",\n",
        "    \"Indonesia\": \"AP\",\n",
        "    \"Ireland\": \"OTHER\",\n",
        "    \"Israel\": \"OTHER\",\n",
        "    \"Isle of Man\": \"OTHER\",\n",
        "    \"India\": \"AP\",\n",
        "    \"British Indian Ocean Territory\": \"OTHER\",\n",
        "    \"Iraq\": \"OTHER\",\n",
        "    \"Iran (Islamic Republic of)\": \"OTHER\",\n",
        "    \"Iceland\": \"OTHER\",\n",
        "    \"Italy\": \"OTHER\",\n",
        "    \"Jersey\": \"OTHER\",\n",
        "    \"Jamaica\": \"LAC\",\n",
        "    \"Jordan\": \"OTHER\",\n",
        "    \"Japan\": \"OTHER\",\n",
        "    \"Kenya\": \"ESA\",\n",
        "    \"Kyrgyzstan\": \"OTHER\",\n",
        "    \"Cambodia\": \"AP\",\n",
        "    \"Kiribati\": \"AP\",\n",
        "    \"Comoros\": \"ESA\",\n",
        "    \"Saint Kitts and Nevis\": \"OTHER\",\n",
        "    \"Korea (Democratic People's Republic of)\": \"OTHER\",\n",
        "    \"Korea, Republic of\": \"OTHER\",\n",
        "    \"Kuwait\": \"OTHER\",\n",
        "    \"Cayman Islands\": \"OTHER\",\n",
        "    \"Kazakhstan\": \"OTHER\",\n",
        "    \"Laos\": \"AP\",\n",
        "    \"Lebanon\": \"OTHER\",\n",
        "    \"Saint Lucia\": \"OTHER\",\n",
        "    \"Liechtenstein\": \"OTHER\",\n",
        "    \"Sri Lanka\": \"AP\",\n",
        "    \"Liberia\": \"WNA\",\n",
        "    \"Lesotho\": \"ESA\",\n",
        "    \"Lithuania\": \"OTHER\",\n",
        "    \"Luxembourg\": \"OTHER\",\n",
        "    \"Latvia\": \"OTHER\",\n",
        "    \"Libya\": \"OTHER\",\n",
        "    \"Morocco\": \"WNA\",\n",
        "    \"Monaco\": \"OTHER\",\n",
        "    \"Moldova\": \"OTHER\",\n",
        "    \"Montenegro\": \"OTHER\",\n",
        "    \"Saint Martin (French part)\": \"OTHER\",\n",
        "    \"Madagascar\": \"ESA\",\n",
        "    \"Marshall Islands\": \"OTHER\",\n",
        "    \"North Macedonia\": \"OTHER\",\n",
        "    \"Mali\": \"WNA\",\n",
        "    \"Myanmar\": \"AP\",\n",
        "    \"Mongolia\": \"OTHER\",\n",
        "    \"Macao\": \"OTHER\",\n",
        "    \"Northern Mariana Islands\": \"OTHER\",\n",
        "    \"Martinique\": \"OTHER\",\n",
        "    \"Mauritania\": \"WNA\",\n",
        "    \"Montserrat\": \"OTHER\",\n",
        "    \"Malta\": \"OTHER\",\n",
        "    \"Mauritius\": \"ESA\",\n",
        "    \"Maldives\": \"OTHER\",\n",
        "    \"Malawi\": \"ESA\",\n",
        "    \"Mexico\": \"LAC\",\n",
        "    \"Malaysia\": \"AP\",\n",
        "    \"Mozambique\": \"ESA\",\n",
        "    \"Namibia\": \"ESA\",\n",
        "    \"New Caledonia\": \"OTHER\",\n",
        "    \"Niger\": \"WNA\",\n",
        "    \"Norfolk Island\": \"OTHER\",\n",
        "    \"Nigeria\": \"WNA\",\n",
        "    \"Nicaragua\": \"LAC\",\n",
        "    \"Netherlands\": \"OTHER\",\n",
        "    \"Netherlands Antilles\": \"OTHER\",\n",
        "    \"Norway\": \"OTHER\",\n",
        "    \"Nepal\": \"AP\",\n",
        "    \"Nauru\": \"OTHER\",\n",
        "    \"Niue\": \"OTHER\",\n",
        "    \"New Zealand\": \"OTHER\",\n",
        "    \"Oman\": \"OTHER\",\n",
        "    \"Panama\": \"LAC\",\n",
        "    \"Peru\": \"LAC\",\n",
        "    \"French Polynesia\": \"OTHER\",\n",
        "    \"Papua New Guinea\": \"AP\",\n",
        "    \"Philippines\": \"AP\",\n",
        "    \"Pakistan\": \"AP\",\n",
        "    \"Poland\": \"OTHER\",\n",
        "    \"Saint Pierre and Miquelon\": \"OTHER\",\n",
        "    \"Pitcairn\": \"OTHER\",\n",
        "    \"Puerto Rico\": \"OTHER\",\n",
        "    \"Palestine\": \"OTHER\",\n",
        "    \"Portugal\": \"OTHER\",\n",
        "    \"Palau\": \"OTHER\",\n",
        "    \"Paraguay\": \"OTHER\",\n",
        "    \"Qatar\": \"OTHER\",\n",
        "    \"Réunion\": \"OTHER\",\n",
        "    \"Romania\": \"OTHER\",\n",
        "    \"Serbia\": \"OTHER\",\n",
        "    \"Russia\": \"OTHER\",\n",
        "    \"Rwanda\": \"ESA\",\n",
        "    \"Saudi Arabia\": \"OTHER\",\n",
        "    \"Solomon Islands\": \"AP\",\n",
        "    \"Seychelles\": \"OTHER\",\n",
        "    \"Sudan\": \"ESA\",\n",
        "    \"Sweden\": \"OTHER\",\n",
        "    \"Singapore\": \"OTHER\",\n",
        "    \"Saint Helena, Ascension and Tristan da Cunha\": \"OTHER\",\n",
        "    \"Slovenia\": \"OTHER\",\n",
        "    \"Svalbard and Jan Mayen\": \"OTHER\",\n",
        "    \"Slovakia\": \"OTHER\",\n",
        "    \"Sierra Leone\": \"WNA\",\n",
        "    \"San Marino\": \"OTHER\",\n",
        "    \"Senegal\": \"WNA\",\n",
        "    \"Somalia\": \"ESA\",\n",
        "    \"Somaliland\": \"ESA\",\n",
        "    \"Suriname\": \"OTHER\",\n",
        "    \"South Sudan\": \"ESA\",\n",
        "    \"Sao Tome and Principe\": \"WNA\",\n",
        "    \"El Salvador\": \"LAC\",\n",
        "    \"Sint Maarten (Dutch part)\": \"OTHER\",\n",
        "    \"Syria\": \"OTHER\",\n",
        "    \"Eswatini\": \"ESA\",\n",
        "    \"Turks and Caicos Islands\": \"OTHER\",\n",
        "    \"Chad\": \"WNA\",\n",
        "    \"French Southern Territories\": \"OTHER\",\n",
        "    \"Togo\": \"WNA\",\n",
        "    \"Thailand\": \"OTHER\",\n",
        "    \"Tajikistan\": \"OTHER\",\n",
        "    \"Tokelau\": \"OTHER\",\n",
        "    \"East Timor\": \"AP\",\n",
        "    \"Turkmenistan\": \"OTHER\",\n",
        "    \"Tunisia\": \"OTHER\",\n",
        "    \"Tonga\": \"AP\",\n",
        "    \"Turkey\": \"OTHER\",\n",
        "    \"Trinidad and Tobago\": \"LAC\",\n",
        "    \"Tuvalu\": \"OTHER\",\n",
        "    \"Taiwan, Province of China\": \"OTHER\",\n",
        "    \"Tanzania\": \"ESA\",\n",
        "    \"Ukraine\": \"OTHER\",\n",
        "    \"Uganda\": \"ESA\",\n",
        "    \"United States Minor Outlying Islands\": \"OTHER\",\n",
        "    \"United States\": \"OTHER\",\n",
        "    \"Uruguay\": \"LAC\",\n",
        "    \"Uzbekistan\": \"AP\",\n",
        "    \"Holy See\": \"OTHER\",\n",
        "    \"Saint Vincent and the Grenadines\": \"OTHER\",\n",
        "    \"Venezuela\": \"LAC\",\n",
        "    \"British Virgin Islands\": \"OTHER\",\n",
        "    \"United States Virgin Islands\": \"OTHER\",\n",
        "    \"Vietnam\": \"AP\",\n",
        "    \"Vanuatu\": \"AP\",\n",
        "    \"Wallis and Futuna\": \"OTHER\",\n",
        "    \"Samoa\": \"OTHER\",\n",
        "    \"Yemen\": \"AP\",\n",
        "    \"Mayotte\": \"OTHER\",\n",
        "    \"South Africa\": \"OTHER\",\n",
        "    \"Zambia\": \"ESA\",\n",
        "    \"Zimbabwe\": \"ESA\",\n",
        "}"
      ],
      "metadata": {
        "id": "wdeXa1VOBJBQ"
      },
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "countries=[]\n",
        "for index, row in projects.reset_index().iterrows():\n",
        "    try:\n",
        "        countries.append(row['country'].strip('][').split(', ')[0].replace(\"'\",\"\").replace('\"',\"\"))\n",
        "    except:\n",
        "        countries.append(row['country'])\n",
        "projects['country']=countries\n",
        "projects['country_code']= projects['country'].map(country_to_abbrev)\n",
        "input_data=input_data.merge(projects[[\"projectId\",\"country\",\"country_code\",\"difficulty\"]], on=\"projectId\", how=\"left\")\n",
        "stats = pd.read_csv(data_folder + \"output_archived_projs_stats.csv\")\n",
        "input_data=input_data.merge(stats[[\"projectId\",\"totalTasks\"]], on=\"projectId\", how=\"left\")\n",
        "input_data['quantile'] = pd.qcut(input_data['totalTasks'], 4, labels=[\"Q1\",\"Q2\",\"Q3\",\"Q4\"])\n",
        "input_data['region']= input_data['country'].map(country_region)\n",
        "input_data"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "id": "T4z1SqXZBgMg",
        "outputId": "99395cbd-b521-426e-cf6a-4cf155021fcc"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     projectId      country country_code difficulty  totalTasks quantile  \\\n",
              "0        11875        Nepal           NP       EASY          89       Q1   \n",
              "1        11884  Philippines           PH       EASY         194       Q2   \n",
              "2        11890    Guatemala           GT       EASY         734       Q4   \n",
              "3        11891    Guatemala           GT       EASY         606       Q4   \n",
              "4        11892      Nigeria           NG       EASY         600       Q4   \n",
              "..         ...          ...          ...        ...         ...      ...   \n",
              "741      15689       Malawi           MW   MODERATE         102       Q1   \n",
              "742      15705        Ghana           GH       EASY         258       Q2   \n",
              "743      15744        Ghana           GH       EASY         213       Q2   \n",
              "744      15745        Ghana           GH   MODERATE         396       Q3   \n",
              "745      15797       Mexico           MX   MODERATE          76       Q1   \n",
              "\n",
              "    region  \n",
              "0       AP  \n",
              "1       AP  \n",
              "2      LAC  \n",
              "3      LAC  \n",
              "4      WNA  \n",
              "..     ...  \n",
              "741    ESA  \n",
              "742    WNA  \n",
              "743    WNA  \n",
              "744    WNA  \n",
              "745    LAC  \n",
              "\n",
              "[746 rows x 7 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-78df65d7-60c3-4300-a858-3bba1473c238\" class=\"colab-df-container\">\n",
              "    <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>projectId</th>\n",
              "      <th>country</th>\n",
              "      <th>country_code</th>\n",
              "      <th>difficulty</th>\n",
              "      <th>totalTasks</th>\n",
              "      <th>quantile</th>\n",
              "      <th>region</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>11875</td>\n",
              "      <td>Nepal</td>\n",
              "      <td>NP</td>\n",
              "      <td>EASY</td>\n",
              "      <td>89</td>\n",
              "      <td>Q1</td>\n",
              "      <td>AP</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>11884</td>\n",
              "      <td>Philippines</td>\n",
              "      <td>PH</td>\n",
              "      <td>EASY</td>\n",
              "      <td>194</td>\n",
              "      <td>Q2</td>\n",
              "      <td>AP</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>11890</td>\n",
              "      <td>Guatemala</td>\n",
              "      <td>GT</td>\n",
              "      <td>EASY</td>\n",
              "      <td>734</td>\n",
              "      <td>Q4</td>\n",
              "      <td>LAC</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>11891</td>\n",
              "      <td>Guatemala</td>\n",
              "      <td>GT</td>\n",
              "      <td>EASY</td>\n",
              "      <td>606</td>\n",
              "      <td>Q4</td>\n",
              "      <td>LAC</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>11892</td>\n",
              "      <td>Nigeria</td>\n",
              "      <td>NG</td>\n",
              "      <td>EASY</td>\n",
              "      <td>600</td>\n",
              "      <td>Q4</td>\n",
              "      <td>WNA</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>741</th>\n",
              "      <td>15689</td>\n",
              "      <td>Malawi</td>\n",
              "      <td>MW</td>\n",
              "      <td>MODERATE</td>\n",
              "      <td>102</td>\n",
              "      <td>Q1</td>\n",
              "      <td>ESA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>742</th>\n",
              "      <td>15705</td>\n",
              "      <td>Ghana</td>\n",
              "      <td>GH</td>\n",
              "      <td>EASY</td>\n",
              "      <td>258</td>\n",
              "      <td>Q2</td>\n",
              "      <td>WNA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>743</th>\n",
              "      <td>15744</td>\n",
              "      <td>Ghana</td>\n",
              "      <td>GH</td>\n",
              "      <td>EASY</td>\n",
              "      <td>213</td>\n",
              "      <td>Q2</td>\n",
              "      <td>WNA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>744</th>\n",
              "      <td>15745</td>\n",
              "      <td>Ghana</td>\n",
              "      <td>GH</td>\n",
              "      <td>MODERATE</td>\n",
              "      <td>396</td>\n",
              "      <td>Q3</td>\n",
              "      <td>WNA</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>745</th>\n",
              "      <td>15797</td>\n",
              "      <td>Mexico</td>\n",
              "      <td>MX</td>\n",
              "      <td>MODERATE</td>\n",
              "      <td>76</td>\n",
              "      <td>Q1</td>\n",
              "      <td>LAC</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>746 rows × 7 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-78df65d7-60c3-4300-a858-3bba1473c238')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-78df65d7-60c3-4300-a858-3bba1473c238 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-78df65d7-60c3-4300-a858-3bba1473c238');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-c46d13c2-ab58-4277-9377-9123f0318ab8\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-c46d13c2-ab58-4277-9377-9123f0318ab8')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-c46d13c2-ab58-4277-9377-9123f0318ab8 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "input_data.to_csv(\"projects.csv\", index=False)"
      ],
      "metadata": {
        "id": "GlKEKVFKCoDw"
      },
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "1) Create file with initial tasks (\"initial_tasks.csv)"
      ],
      "metadata": {
        "id": "1LdGsHcpLSEx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Concatenate activities of individual tasks in a single log\n",
        "activities=pd.DataFrame()\n",
        "display(Markdown(\"DOWNLOADING TASK GRIDS FOR THE SELECTED PROJECTS\"))\n",
        "with tqdm(total=len(input_data[\"projectId\"]), unit=\" project\") as pbar:\n",
        "  for proj_id in input_data['projectId']:\n",
        "      input_activities_filename = data_folder + \"output_proj_\" + str(proj_id) + \".csv\"\n",
        "      input_users_filename = data_folder + \"output_users_proj_\" + str(proj_id) + \".csv\"\n",
        "      activities = pd.read_csv(input_activities_filename)\n",
        "      users = pd.read_csv(input_users_filename)\n",
        "      users = users[[\"username\", \"mappingLevel\"]]\n",
        "      # Calculate new taskId\n",
        "      activities[\"taskId\"]=str(proj_id)+\"_\"+activities[\"taskId\"].astype(str)\n",
        "      # Calculate start and end dates\n",
        "      activities[\"start\"] = pd.to_datetime(activities[\"actionDate\"])\n",
        "      activities[\"start\"] = np.where(activities[\"action\"] == \"STATE_CHANGE\", activities[\"start\"]+timedelta(seconds=1), activities[\"start\"])\n",
        "      activities[\"complete\"] = np.where(activities[\"action\"] == \"STATE_CHANGE\", activities[\"start\"], activities[\"start\"] + pd.to_timedelta(activities[\"actionText\"], errors=\"coerce\"))\n",
        "      activities[\"complete\"] = np.where(activities[\"complete\"].isnull(), activities[\"start\"], activities[\"complete\"])\n",
        "      # Calculate log states\n",
        "      activities[\"action\"] = np.where(activities[\"action\"] == \"STATE_CHANGE\", activities[\"actionText\"], activities[\"action\"])\n",
        "      # Transform task ids into string\n",
        "      activities[\"taskId\"] = activities[\"taskId\"].apply(str)\n",
        "      # Keep only the desired columns\n",
        "      activities = activities[[\"taskId\", \"action\", \"actionBy\", \"start\", \"complete\"]]\n",
        "      # Remove ready states\n",
        "      activities = activities.drop(activities[activities[\"action\"] == \"READY\"].index)\n",
        "      # Sort by ascending\n",
        "      activities = activities.iloc[::-1]\n",
        "      # Drop duplicates\n",
        "      activities = activities.drop_duplicates()\n",
        "      # activities_droped = activities.drop_duplicates(subset=[\"action\", \"start\", \"actionBy\"])\n",
        "      merged = pd.merge(activities, users, left_on=\"actionBy\", right_on=\"username\", how=\"left\")\n",
        "      activities=activities.append(merged)\n",
        "      pbar.update(1)"
      ],
      "metadata": {
        "id": "AS2jLm5F9bMh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Uyj1W5EIbfIK",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "outputId": "d9644502-9337-4797-a8df-9a84f97fba45"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "           taskId                 action        actionBy  \\\n",
              "0        11875_33     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "1        11875_72     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "2        11875_85     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "3        11875_42     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "4        11875_42                 MAPPED  fajarramadhana   \n",
              "...           ...                    ...             ...   \n",
              "1820599  15797_70     LOCKED_FOR_MAPPING    Evan Pearson   \n",
              "1820600  15797_70     LOCKED_FOR_MAPPING     karabarca29   \n",
              "1820601  15797_70                 MAPPED     karabarca29   \n",
              "1820602  15797_70  LOCKED_FOR_VALIDATION        JuanMelo   \n",
              "1820603  15797_70              VALIDATED        JuanMelo   \n",
              "\n",
              "                                   start                          complete  \\\n",
              "0       2021-12-01 04:45:26.341502+00:00  2021-12-01 04:46:29.933460+00:00   \n",
              "1       2021-12-01 04:46:37.414302+00:00  2021-12-01 04:47:15.559799+00:00   \n",
              "2       2021-12-01 04:47:24.839519+00:00  2021-12-01 04:49:46.389017+00:00   \n",
              "3       2021-12-01 04:49:53.855180+00:00  2021-12-01 04:53:22.263015+00:00   \n",
              "4       2021-12-01 04:53:23.260095+00:00  2021-12-01 04:53:23.260095+00:00   \n",
              "...                                  ...                               ...   \n",
              "1820599 2023-11-27 19:10:46.521133+00:00  2023-11-27 19:11:27.473306+00:00   \n",
              "1820600 2023-11-27 19:16:33.278954+00:00  2023-11-27 19:33:55.401159+00:00   \n",
              "1820601 2023-11-27 19:33:56.397329+00:00  2023-11-27 19:33:56.397329+00:00   \n",
              "1820602 2023-11-30 12:26:37.896972+00:00  2023-11-30 14:31:20.822475+00:00   \n",
              "1820603 2023-11-30 14:31:21.810359+00:00  2023-11-30 14:31:21.810359+00:00   \n",
              "\n",
              "               username mappingLevel  \n",
              "0        fajarramadhana     BEGINNER  \n",
              "1        fajarramadhana     BEGINNER  \n",
              "2        fajarramadhana     BEGINNER  \n",
              "3        fajarramadhana     BEGINNER  \n",
              "4        fajarramadhana     BEGINNER  \n",
              "...                 ...          ...  \n",
              "1820599    Evan Pearson     BEGINNER  \n",
              "1820600     karabarca29     BEGINNER  \n",
              "1820601     karabarca29     BEGINNER  \n",
              "1820602        JuanMelo     ADVANCED  \n",
              "1820603        JuanMelo     ADVANCED  \n",
              "\n",
              "[1820604 rows x 7 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-2bdd7516-86f6-483a-8fd0-b1a39d4ac8d4\" class=\"colab-df-container\">\n",
              "    <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>taskId</th>\n",
              "      <th>action</th>\n",
              "      <th>actionBy</th>\n",
              "      <th>start</th>\n",
              "      <th>complete</th>\n",
              "      <th>username</th>\n",
              "      <th>mappingLevel</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>11875_33</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:45:26.341502+00:00</td>\n",
              "      <td>2021-12-01 04:46:29.933460+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>11875_72</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:46:37.414302+00:00</td>\n",
              "      <td>2021-12-01 04:47:15.559799+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>11875_85</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:47:24.839519+00:00</td>\n",
              "      <td>2021-12-01 04:49:46.389017+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>11875_42</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:49:53.855180+00:00</td>\n",
              "      <td>2021-12-01 04:53:22.263015+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>11875_42</td>\n",
              "      <td>MAPPED</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:53:23.260095+00:00</td>\n",
              "      <td>2021-12-01 04:53:23.260095+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</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>1820599</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>Evan Pearson</td>\n",
              "      <td>2023-11-27 19:10:46.521133+00:00</td>\n",
              "      <td>2023-11-27 19:11:27.473306+00:00</td>\n",
              "      <td>Evan Pearson</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1820600</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>2023-11-27 19:16:33.278954+00:00</td>\n",
              "      <td>2023-11-27 19:33:55.401159+00:00</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1820601</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>MAPPED</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>2023-11-27 19:33:56.397329+00:00</td>\n",
              "      <td>2023-11-27 19:33:56.397329+00:00</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1820602</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_VALIDATION</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>2023-11-30 12:26:37.896972+00:00</td>\n",
              "      <td>2023-11-30 14:31:20.822475+00:00</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1820603</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>VALIDATED</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>2023-11-30 14:31:21.810359+00:00</td>\n",
              "      <td>2023-11-30 14:31:21.810359+00:00</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1820604 rows × 7 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2bdd7516-86f6-483a-8fd0-b1a39d4ac8d4')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-2bdd7516-86f6-483a-8fd0-b1a39d4ac8d4 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-2bdd7516-86f6-483a-8fd0-b1a39d4ac8d4');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-78e40235-36ca-48f0-9aa7-097b0c7ea5b8\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-78e40235-36ca-48f0-9aa7-097b0c7ea5b8')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-78e40235-36ca-48f0-9aa7-097b0c7ea5b8 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 85
        }
      ],
      "source": [
        "# Identify first_activities of tasks\n",
        "activities[\"start\"] = pd.to_datetime(activities[\"start\"])\n",
        "activities = activities.drop_duplicates()\n",
        "first_activities=activities.drop_duplicates(subset=[\"taskId\"])\n",
        "initial_tasks_with_splits=first_activities.sort_values(\"start\").drop_duplicates(subset=[\"actionBy\",\"start\"])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bfYzEqXA8U23"
      },
      "outputs": [],
      "source": [
        "# Identify initial SPLITs and remove duplicates\n",
        "initial_with_split=initial_tasks_with_splits[initial_tasks_with_splits[\"action\"]==\"SPLIT\"]\n",
        "initial_with_split=initial_with_split[initial_with_split.index % 4 == 0]\n",
        "initial_without_split=initial_tasks_with_splits[initial_tasks_with_splits[\"action\"]!=\"SPLIT\"]\n",
        "# List of initial activities including unique SPLITs\n",
        "initial_tasks_with_splits=initial_with_split.taskId.to_list()+initial_without_split.taskId.to_list()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Xdy5uvickNfz",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "outputId": "6a3c73a5-be56-43e6-db23-9f4236f31d25"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "           taskId                 action        actionBy  \\\n",
              "0        11875_33     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "1        11875_72     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "2        11875_85     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "3        11875_42     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "4        11875_42                 MAPPED  fajarramadhana   \n",
              "...           ...                    ...             ...   \n",
              "1820599  15797_70     LOCKED_FOR_MAPPING    Evan Pearson   \n",
              "1820600  15797_70     LOCKED_FOR_MAPPING     karabarca29   \n",
              "1820601  15797_70                 MAPPED     karabarca29   \n",
              "1820602  15797_70  LOCKED_FOR_VALIDATION        JuanMelo   \n",
              "1820603  15797_70              VALIDATED        JuanMelo   \n",
              "\n",
              "                                   start                          complete  \\\n",
              "0       2021-12-01 04:45:26.341502+00:00  2021-12-01 04:46:29.933460+00:00   \n",
              "1       2021-12-01 04:46:37.414302+00:00  2021-12-01 04:47:15.559799+00:00   \n",
              "2       2021-12-01 04:47:24.839519+00:00  2021-12-01 04:49:46.389017+00:00   \n",
              "3       2021-12-01 04:49:53.855180+00:00  2021-12-01 04:53:22.263015+00:00   \n",
              "4       2021-12-01 04:53:23.260095+00:00  2021-12-01 04:53:23.260095+00:00   \n",
              "...                                  ...                               ...   \n",
              "1820599 2023-11-27 19:10:46.521133+00:00  2023-11-27 19:11:27.473306+00:00   \n",
              "1820600 2023-11-27 19:16:33.278954+00:00  2023-11-27 19:33:55.401159+00:00   \n",
              "1820601 2023-11-27 19:33:56.397329+00:00  2023-11-27 19:33:56.397329+00:00   \n",
              "1820602 2023-11-30 12:26:37.896972+00:00  2023-11-30 14:31:20.822475+00:00   \n",
              "1820603 2023-11-30 14:31:21.810359+00:00  2023-11-30 14:31:21.810359+00:00   \n",
              "\n",
              "               username mappingLevel  \n",
              "0        fajarramadhana     BEGINNER  \n",
              "1        fajarramadhana     BEGINNER  \n",
              "2        fajarramadhana     BEGINNER  \n",
              "3        fajarramadhana     BEGINNER  \n",
              "4        fajarramadhana     BEGINNER  \n",
              "...                 ...          ...  \n",
              "1820599    Evan Pearson     BEGINNER  \n",
              "1820600     karabarca29     BEGINNER  \n",
              "1820601     karabarca29     BEGINNER  \n",
              "1820602        JuanMelo     ADVANCED  \n",
              "1820603        JuanMelo     ADVANCED  \n",
              "\n",
              "[1381184 rows x 7 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-f1a1b988-c4a7-466a-8960-a55583432682\" class=\"colab-df-container\">\n",
              "    <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>taskId</th>\n",
              "      <th>action</th>\n",
              "      <th>actionBy</th>\n",
              "      <th>start</th>\n",
              "      <th>complete</th>\n",
              "      <th>username</th>\n",
              "      <th>mappingLevel</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>11875_33</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:45:26.341502+00:00</td>\n",
              "      <td>2021-12-01 04:46:29.933460+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>11875_72</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:46:37.414302+00:00</td>\n",
              "      <td>2021-12-01 04:47:15.559799+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>11875_85</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:47:24.839519+00:00</td>\n",
              "      <td>2021-12-01 04:49:46.389017+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>11875_42</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:49:53.855180+00:00</td>\n",
              "      <td>2021-12-01 04:53:22.263015+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>11875_42</td>\n",
              "      <td>MAPPED</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:53:23.260095+00:00</td>\n",
              "      <td>2021-12-01 04:53:23.260095+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</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>1820599</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>Evan Pearson</td>\n",
              "      <td>2023-11-27 19:10:46.521133+00:00</td>\n",
              "      <td>2023-11-27 19:11:27.473306+00:00</td>\n",
              "      <td>Evan Pearson</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1820600</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>2023-11-27 19:16:33.278954+00:00</td>\n",
              "      <td>2023-11-27 19:33:55.401159+00:00</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1820601</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>MAPPED</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>2023-11-27 19:33:56.397329+00:00</td>\n",
              "      <td>2023-11-27 19:33:56.397329+00:00</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1820602</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_VALIDATION</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>2023-11-30 12:26:37.896972+00:00</td>\n",
              "      <td>2023-11-30 14:31:20.822475+00:00</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1820603</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>VALIDATED</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>2023-11-30 14:31:21.810359+00:00</td>\n",
              "      <td>2023-11-30 14:31:21.810359+00:00</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1381184 rows × 7 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-f1a1b988-c4a7-466a-8960-a55583432682')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-f1a1b988-c4a7-466a-8960-a55583432682 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-f1a1b988-c4a7-466a-8960-a55583432682');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-06bfd3db-3ce3-495d-b733-f138fd5182cf\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-06bfd3db-3ce3-495d-b733-f138fd5182cf')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-06bfd3db-3ce3-495d-b733-f138fd5182cf button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 93
        }
      ],
      "source": [
        "# Select all the activities of relevant tasks\n",
        "tasks_with_splits=activities[activities[\"taskId\"].isin(initial_tasks_with_splits)]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7K3QEPx-kfiP",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "outputId": "a0dbd292-f984-457a-d872-3dea34c68289"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             taskId action        actionBy                            start  \\\n",
              "10896    11893_1247  SPLIT       ALLY SADA 2021-12-06 08:54:19.114617+00:00   \n",
              "10964    11893_1251  SPLIT       ALLY SADA 2021-12-06 12:34:21.974559+00:00   \n",
              "11616    11893_1373  SPLIT      Seth Mkoma 2021-12-08 13:27:42.880591+00:00   \n",
              "9098      11892_592  SPLIT        cayla_cx 2021-12-09 16:11:18.358458+00:00   \n",
              "9128      11892_596  SPLIT         HunterH 2021-12-09 16:26:49.923214+00:00   \n",
              "...             ...    ...             ...                              ...   \n",
              "1815264   15689_100  SPLIT        Jasperre 2023-11-09 18:02:49.257273+00:00   \n",
              "1815345   15689_103  SPLIT  koos krijnders 2023-11-10 09:17:51.094956+00:00   \n",
              "1813436   15655_195  SPLIT         Siem_Hw 2023-11-10 10:49:42.470420+00:00   \n",
              "1815441   15689_105  SPLIT  koos krijnders 2023-11-11 19:48:08.564829+00:00   \n",
              "1815565   15689_109  SPLIT  koos krijnders 2023-11-13 07:59:52.185230+00:00   \n",
              "\n",
              "                                 complete        username mappingLevel  \n",
              "10896    2021-12-06 08:54:19.114617+00:00       ALLY SADA     ADVANCED  \n",
              "10964    2021-12-06 12:34:21.974559+00:00       ALLY SADA     ADVANCED  \n",
              "11616    2021-12-08 13:27:42.880591+00:00      Seth Mkoma     ADVANCED  \n",
              "9098     2021-12-09 16:11:18.358458+00:00        cayla_cx     BEGINNER  \n",
              "9128     2021-12-09 16:26:49.923214+00:00         HunterH     BEGINNER  \n",
              "...                                   ...             ...          ...  \n",
              "1815264  2023-11-09 18:02:49.257273+00:00        Jasperre     BEGINNER  \n",
              "1815345  2023-11-10 09:17:51.094956+00:00  koos krijnders     ADVANCED  \n",
              "1813436  2023-11-10 10:49:42.470420+00:00         Siem_Hw     ADVANCED  \n",
              "1815441  2023-11-11 19:48:08.564829+00:00  koos krijnders     ADVANCED  \n",
              "1815565  2023-11-13 07:59:52.185230+00:00  koos krijnders     ADVANCED  \n",
              "\n",
              "[7680 rows x 7 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-e848cd48-6552-4041-abf4-13910d997c03\" class=\"colab-df-container\">\n",
              "    <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>taskId</th>\n",
              "      <th>action</th>\n",
              "      <th>actionBy</th>\n",
              "      <th>start</th>\n",
              "      <th>complete</th>\n",
              "      <th>username</th>\n",
              "      <th>mappingLevel</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>10896</th>\n",
              "      <td>11893_1247</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>ALLY SADA</td>\n",
              "      <td>2021-12-06 08:54:19.114617+00:00</td>\n",
              "      <td>2021-12-06 08:54:19.114617+00:00</td>\n",
              "      <td>ALLY SADA</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10964</th>\n",
              "      <td>11893_1251</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>ALLY SADA</td>\n",
              "      <td>2021-12-06 12:34:21.974559+00:00</td>\n",
              "      <td>2021-12-06 12:34:21.974559+00:00</td>\n",
              "      <td>ALLY SADA</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11616</th>\n",
              "      <td>11893_1373</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>Seth Mkoma</td>\n",
              "      <td>2021-12-08 13:27:42.880591+00:00</td>\n",
              "      <td>2021-12-08 13:27:42.880591+00:00</td>\n",
              "      <td>Seth Mkoma</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9098</th>\n",
              "      <td>11892_592</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>cayla_cx</td>\n",
              "      <td>2021-12-09 16:11:18.358458+00:00</td>\n",
              "      <td>2021-12-09 16:11:18.358458+00:00</td>\n",
              "      <td>cayla_cx</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9128</th>\n",
              "      <td>11892_596</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>HunterH</td>\n",
              "      <td>2021-12-09 16:26:49.923214+00:00</td>\n",
              "      <td>2021-12-09 16:26:49.923214+00:00</td>\n",
              "      <td>HunterH</td>\n",
              "      <td>BEGINNER</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>1815264</th>\n",
              "      <td>15689_100</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>Jasperre</td>\n",
              "      <td>2023-11-09 18:02:49.257273+00:00</td>\n",
              "      <td>2023-11-09 18:02:49.257273+00:00</td>\n",
              "      <td>Jasperre</td>\n",
              "      <td>BEGINNER</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1815345</th>\n",
              "      <td>15689_103</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>koos krijnders</td>\n",
              "      <td>2023-11-10 09:17:51.094956+00:00</td>\n",
              "      <td>2023-11-10 09:17:51.094956+00:00</td>\n",
              "      <td>koos krijnders</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1813436</th>\n",
              "      <td>15655_195</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>Siem_Hw</td>\n",
              "      <td>2023-11-10 10:49:42.470420+00:00</td>\n",
              "      <td>2023-11-10 10:49:42.470420+00:00</td>\n",
              "      <td>Siem_Hw</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1815441</th>\n",
              "      <td>15689_105</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>koos krijnders</td>\n",
              "      <td>2023-11-11 19:48:08.564829+00:00</td>\n",
              "      <td>2023-11-11 19:48:08.564829+00:00</td>\n",
              "      <td>koos krijnders</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1815565</th>\n",
              "      <td>15689_109</td>\n",
              "      <td>SPLIT</td>\n",
              "      <td>koos krijnders</td>\n",
              "      <td>2023-11-13 07:59:52.185230+00:00</td>\n",
              "      <td>2023-11-13 07:59:52.185230+00:00</td>\n",
              "      <td>koos krijnders</td>\n",
              "      <td>ADVANCED</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>7680 rows × 7 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e848cd48-6552-4041-abf4-13910d997c03')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-e848cd48-6552-4041-abf4-13910d997c03 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-e848cd48-6552-4041-abf4-13910d997c03');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-12334007-6376-4644-8924-920998ab74c8\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-12334007-6376-4644-8924-920998ab74c8')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-12334007-6376-4644-8924-920998ab74c8 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 94
        }
      ],
      "source": [
        "# Identify the initial SPLIT of tasks\n",
        "initial_splits=tasks_with_splits[tasks_with_splits[\"action\"]==\"SPLIT\"].sort_values(\"start\").drop_duplicates(subset=[\"taskId\"])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PYPgiHXjoaBk",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 597
        },
        "outputId": "3b4e0394-c9aa-4743-bb09-5c62affd3978"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "           taskId                 action        actionBy  \\\n",
              "0        11875_33     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "1        11875_72     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "2        11875_85     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "3        11875_42     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "4        11875_42                 MAPPED  fajarramadhana   \n",
              "...           ...                    ...             ...   \n",
              "1381179  15797_70     LOCKED_FOR_MAPPING    Evan Pearson   \n",
              "1381180  15797_70     LOCKED_FOR_MAPPING     karabarca29   \n",
              "1381181  15797_70                 MAPPED     karabarca29   \n",
              "1381182  15797_70  LOCKED_FOR_VALIDATION        JuanMelo   \n",
              "1381183  15797_70              VALIDATED        JuanMelo   \n",
              "\n",
              "                                 start_x                          complete  \\\n",
              "0       2021-12-01 04:45:26.341502+00:00  2021-12-01 04:46:29.933460+00:00   \n",
              "1       2021-12-01 04:46:37.414302+00:00  2021-12-01 04:47:15.559799+00:00   \n",
              "2       2021-12-01 04:47:24.839519+00:00  2021-12-01 04:49:46.389017+00:00   \n",
              "3       2021-12-01 04:49:53.855180+00:00  2021-12-01 04:53:22.263015+00:00   \n",
              "4       2021-12-01 04:53:23.260095+00:00  2021-12-01 04:53:23.260095+00:00   \n",
              "...                                  ...                               ...   \n",
              "1381179 2023-11-27 19:10:46.521133+00:00  2023-11-27 19:11:27.473306+00:00   \n",
              "1381180 2023-11-27 19:16:33.278954+00:00  2023-11-27 19:33:55.401159+00:00   \n",
              "1381181 2023-11-27 19:33:56.397329+00:00  2023-11-27 19:33:56.397329+00:00   \n",
              "1381182 2023-11-30 12:26:37.896972+00:00  2023-11-30 14:31:20.822475+00:00   \n",
              "1381183 2023-11-30 14:31:21.810359+00:00  2023-11-30 14:31:21.810359+00:00   \n",
              "\n",
              "               username mappingLevel                          start_y  \n",
              "0        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "1        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "2        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "3        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "4        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "...                 ...          ...                              ...  \n",
              "1381179    Evan Pearson     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "1381180     karabarca29     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "1381181     karabarca29     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "1381182        JuanMelo     ADVANCED 2025-12-01 04:46:29.933460+00:00  \n",
              "1381183        JuanMelo     ADVANCED 2025-12-01 04:46:29.933460+00:00  \n",
              "\n",
              "[1381184 rows x 8 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-3f8436a7-fcbc-4cfa-a019-b00b78610e1e\" class=\"colab-df-container\">\n",
              "    <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>taskId</th>\n",
              "      <th>action</th>\n",
              "      <th>actionBy</th>\n",
              "      <th>start_x</th>\n",
              "      <th>complete</th>\n",
              "      <th>username</th>\n",
              "      <th>mappingLevel</th>\n",
              "      <th>start_y</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>11875_33</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:45:26.341502+00:00</td>\n",
              "      <td>2021-12-01 04:46:29.933460+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>11875_72</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:46:37.414302+00:00</td>\n",
              "      <td>2021-12-01 04:47:15.559799+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>11875_85</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:47:24.839519+00:00</td>\n",
              "      <td>2021-12-01 04:49:46.389017+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>11875_42</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:49:53.855180+00:00</td>\n",
              "      <td>2021-12-01 04:53:22.263015+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>11875_42</td>\n",
              "      <td>MAPPED</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:53:23.260095+00:00</td>\n",
              "      <td>2021-12-01 04:53:23.260095+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381179</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>Evan Pearson</td>\n",
              "      <td>2023-11-27 19:10:46.521133+00:00</td>\n",
              "      <td>2023-11-27 19:11:27.473306+00:00</td>\n",
              "      <td>Evan Pearson</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381180</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>2023-11-27 19:16:33.278954+00:00</td>\n",
              "      <td>2023-11-27 19:33:55.401159+00:00</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381181</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>MAPPED</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>2023-11-27 19:33:56.397329+00:00</td>\n",
              "      <td>2023-11-27 19:33:56.397329+00:00</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381182</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_VALIDATION</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>2023-11-30 12:26:37.896972+00:00</td>\n",
              "      <td>2023-11-30 14:31:20.822475+00:00</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>ADVANCED</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381183</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>VALIDATED</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>2023-11-30 14:31:21.810359+00:00</td>\n",
              "      <td>2023-11-30 14:31:21.810359+00:00</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>ADVANCED</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1381184 rows × 8 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3f8436a7-fcbc-4cfa-a019-b00b78610e1e')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-3f8436a7-fcbc-4cfa-a019-b00b78610e1e button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-3f8436a7-fcbc-4cfa-a019-b00b78610e1e');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-0bfecc56-e748-4396-9fc3-7cb959d16465\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-0bfecc56-e748-4396-9fc3-7cb959d16465')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-0bfecc56-e748-4396-9fc3-7cb959d16465 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 98
        }
      ],
      "source": [
        "# Concatenate the activities of relevant tasks with the initial date of SPLITs in that tasks if present otherwise \"2025-12-01 04:46:29.933460+00:00\"\n",
        "initial_tasks_with_splits=tasks_with_splits.merge(initial_splits[[\"taskId\",\"start\"]], on=\"taskId\", how=\"left\").fillna(value=pd.to_datetime('2025-12-01 04:46:29.933460+00:00'))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jg5njRwCo8XK",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 597
        },
        "outputId": "bd7eff67-90ac-426f-c165-fc4acebe4b66"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "           taskId                 action        actionBy  \\\n",
              "0        11875_33     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "1        11875_72     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "2        11875_85     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "3        11875_42     LOCKED_FOR_MAPPING  fajarramadhana   \n",
              "4        11875_42                 MAPPED  fajarramadhana   \n",
              "...           ...                    ...             ...   \n",
              "1381179  15797_70     LOCKED_FOR_MAPPING    Evan Pearson   \n",
              "1381180  15797_70     LOCKED_FOR_MAPPING     karabarca29   \n",
              "1381181  15797_70                 MAPPED     karabarca29   \n",
              "1381182  15797_70  LOCKED_FOR_VALIDATION        JuanMelo   \n",
              "1381183  15797_70              VALIDATED        JuanMelo   \n",
              "\n",
              "                                 start_x                          complete  \\\n",
              "0       2021-12-01 04:45:26.341502+00:00  2021-12-01 04:46:29.933460+00:00   \n",
              "1       2021-12-01 04:46:37.414302+00:00  2021-12-01 04:47:15.559799+00:00   \n",
              "2       2021-12-01 04:47:24.839519+00:00  2021-12-01 04:49:46.389017+00:00   \n",
              "3       2021-12-01 04:49:53.855180+00:00  2021-12-01 04:53:22.263015+00:00   \n",
              "4       2021-12-01 04:53:23.260095+00:00  2021-12-01 04:53:23.260095+00:00   \n",
              "...                                  ...                               ...   \n",
              "1381179 2023-11-27 19:10:46.521133+00:00  2023-11-27 19:11:27.473306+00:00   \n",
              "1381180 2023-11-27 19:16:33.278954+00:00  2023-11-27 19:33:55.401159+00:00   \n",
              "1381181 2023-11-27 19:33:56.397329+00:00  2023-11-27 19:33:56.397329+00:00   \n",
              "1381182 2023-11-30 12:26:37.896972+00:00  2023-11-30 14:31:20.822475+00:00   \n",
              "1381183 2023-11-30 14:31:21.810359+00:00  2023-11-30 14:31:21.810359+00:00   \n",
              "\n",
              "               username mappingLevel                          start_y  \n",
              "0        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "1        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "2        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "3        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "4        fajarramadhana     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "...                 ...          ...                              ...  \n",
              "1381179    Evan Pearson     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "1381180     karabarca29     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "1381181     karabarca29     BEGINNER 2025-12-01 04:46:29.933460+00:00  \n",
              "1381182        JuanMelo     ADVANCED 2025-12-01 04:46:29.933460+00:00  \n",
              "1381183        JuanMelo     ADVANCED 2025-12-01 04:46:29.933460+00:00  \n",
              "\n",
              "[1330255 rows x 8 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-85396dc5-2c9e-46a9-b2c6-87b8cbcf61fe\" class=\"colab-df-container\">\n",
              "    <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>taskId</th>\n",
              "      <th>action</th>\n",
              "      <th>actionBy</th>\n",
              "      <th>start_x</th>\n",
              "      <th>complete</th>\n",
              "      <th>username</th>\n",
              "      <th>mappingLevel</th>\n",
              "      <th>start_y</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>11875_33</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:45:26.341502+00:00</td>\n",
              "      <td>2021-12-01 04:46:29.933460+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>11875_72</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:46:37.414302+00:00</td>\n",
              "      <td>2021-12-01 04:47:15.559799+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>11875_85</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:47:24.839519+00:00</td>\n",
              "      <td>2021-12-01 04:49:46.389017+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>11875_42</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:49:53.855180+00:00</td>\n",
              "      <td>2021-12-01 04:53:22.263015+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>11875_42</td>\n",
              "      <td>MAPPED</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>2021-12-01 04:53:23.260095+00:00</td>\n",
              "      <td>2021-12-01 04:53:23.260095+00:00</td>\n",
              "      <td>fajarramadhana</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</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",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381179</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>Evan Pearson</td>\n",
              "      <td>2023-11-27 19:10:46.521133+00:00</td>\n",
              "      <td>2023-11-27 19:11:27.473306+00:00</td>\n",
              "      <td>Evan Pearson</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381180</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_MAPPING</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>2023-11-27 19:16:33.278954+00:00</td>\n",
              "      <td>2023-11-27 19:33:55.401159+00:00</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381181</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>MAPPED</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>2023-11-27 19:33:56.397329+00:00</td>\n",
              "      <td>2023-11-27 19:33:56.397329+00:00</td>\n",
              "      <td>karabarca29</td>\n",
              "      <td>BEGINNER</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381182</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>LOCKED_FOR_VALIDATION</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>2023-11-30 12:26:37.896972+00:00</td>\n",
              "      <td>2023-11-30 14:31:20.822475+00:00</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>ADVANCED</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1381183</th>\n",
              "      <td>15797_70</td>\n",
              "      <td>VALIDATED</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>2023-11-30 14:31:21.810359+00:00</td>\n",
              "      <td>2023-11-30 14:31:21.810359+00:00</td>\n",
              "      <td>JuanMelo</td>\n",
              "      <td>ADVANCED</td>\n",
              "      <td>2025-12-01 04:46:29.933460+00:00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1330255 rows × 8 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-85396dc5-2c9e-46a9-b2c6-87b8cbcf61fe')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-85396dc5-2c9e-46a9-b2c6-87b8cbcf61fe button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-85396dc5-2c9e-46a9-b2c6-87b8cbcf61fe');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-612150b9-67a0-4e04-9784-73b11fa2aca5\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-612150b9-67a0-4e04-9784-73b11fa2aca5')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-612150b9-67a0-4e04-9784-73b11fa2aca5 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {},
          "execution_count": 101
        }
      ],
      "source": [
        "# Remove all the activities happening after observing a split in relevant tasks\n",
        "initial_tasks_with_splits['start_y'] = pd.to_datetime(initial_tasks_with_splits[\"start_y\"], utc=True)\n",
        "initial_tasks_with_splits['start_x'] = pd.to_datetime(initial_tasks_with_splits[\"start_x\"], utc=True)\n",
        "initial_tasks_clean=initial_tasks_with_splits[initial_tasks_with_splits[\"start_x\"]<=initial_tasks_with_splits[\"start_y\"]]"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create file with the log for initial tasks (\"initial_tasks.csv\")\n",
        "initial_tasks_clean.columns=['taskId', 'action', 'actionBy', 'start', 'complete', 'username','mappingLevel', 'start_y']\n",
        "initial_tasks_clean=initial_tasks_clean[['taskId', 'action', 'actionBy', 'start', 'complete','mappingLevel']]\n",
        "initial_tasks_clean.to_csv(\"initial_tasks.csv\", index=False)"
      ],
      "metadata": {
        "id": "nibBUuFzEfuY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "2) Create file with contributor profile (\"contributors.csv\")"
      ],
      "metadata": {
        "id": "eWdmpavTM5-l"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Concatenate contributors of selected projects\n",
        "contributors=pd.DataFrame()\n",
        "display(Markdown(\"DOWNLOADING TASK GRIDS FOR THE SELECTED PROJECTS\"))\n",
        "with tqdm(total=len(input_data[\"projectId\"]), unit=\" project\") as pbar:\n",
        "  for index, row in input_data.iterrows():\n",
        "      input_users_filename = data_folder + \"output_users_proj_\" + str(row[\"projectId\"]) + \".csv\"\n",
        "      users = pd.read_csv(input_users_filename)\n",
        "      users = users[[\"username\", \"mappingLevel\"]]\n",
        "      contributors=contributors.append(users)\n",
        "      pbar.update(1)\n",
        "contributors=contributors.drop_duplicates()"
      ],
      "metadata": {
        "id": "T0Pz7cDKM5DP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Create file with contributors profile (\"contributors.csv\")\n",
        "contributors.to_csv(\"contributors.csv\", index=False)"
      ],
      "metadata": {
        "id": "a30iC6UGN3wQ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "3) Create file for regression analysis with task density (\"regression.csv.csv)"
      ],
      "metadata": {
        "id": "N3cnVH2607yr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Read file with densities per tasks\n",
        "densities=pd.read_csv(\"output_densities.csv\")\n",
        "densities[\"taskId\"]=densities[\"projId\"].astype(str)+\"_\"+densities[\"taskId\"].astype(str)"
      ],
      "metadata": {
        "id": "WulYsAAs1W4X"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Dummy encoding por activity presence\n",
        "activities['splits'] = np.where(activities['action']=='SPLIT', 1, 0)\n",
        "activities['invalidations'] = np.where(activities['action']=='INVALIDATED', 1, 0)\n",
        "activities['locked_for_mappings'] = np.where(activities['action']=='LOCKED_FOR_MAPPING', 1, 0)\n",
        "activities['mapped'] = np.where(activities['action']=='MAPPED', 1, 0)\n",
        "activities['locked_for_validation'] = np.where(activities['action']=='LOCKED_FOR_VALIDATION', 1, 0)\n",
        "activities['validations'] = np.where(activities['action']=='VALIDATED', 1, 0)\n",
        "activities['auto_unlocked_for_mapping'] = np.where(activities['action']=='AUTO_UNLOCKED_FOR_MAPPING', 1, 0)\n",
        "activities['auto_unlocked_for_validation'] = np.where(activities['action']=='AUTO_UNLOCKED_FOR_VALIDATION', 1, 0)\n",
        "activities['badimagery'] = np.where(activities['action']=='BADIMAGERY', 1, 0)\n",
        "activities['extended_for_mapping'] = np.where(activities['action']=='EXTENDED_FOR_MAPPING', 1, 0)"
      ],
      "metadata": {
        "id": "rPooFyCx2bMP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Group by TaskId to count the number or ocurrences of each activity per tasks and adding density per task\n",
        "regression=activities[[\"taskId\",\"splits\",\"invalidations\",\"locked_for_mappings\",\"mapped\",\"locked_for_validation\",\"validations\",\"auto_unlocked_for_mapping\",\"auto_unlocked_for_validation\",\"badimagery\",\"extended_for_mapping\"]].groupby([\"taskId\"]).sum().reset_index().merge(densities, on=\"taskId\", how=\"left\")\n",
        "regression['projectId'] = regression['taskId'].str.split('_').str[0]\n",
        "projects['projectId'] = projects['projectId'].astype(str)\n",
        "regression=regression.merge(projects[[\"projectId\",\"difficulty\",\"priority\"]], on=\"projectId\", how=\"left\")\n",
        "regression[\"splits1\"]= np.where(regression.splits==0,0,1)\n",
        "regression[\"invalidations1\"]= np.where(regression.invalidations==0,0,1)\n",
        "regression[\"locked_for_mappings1\"]= np.where(regression.locked_for_mappings==0,0,1)\n",
        "regression[\"mapped1\"]= np.where(regression.mapped==0,0,1)\n",
        "regression[\"locked_for_validation1\"]= np.where(regression.locked_for_validation==0,0,1)\n",
        "regression[\"validations1\"]= np.where(regression.validations==0,0,1)\n",
        "regression[\"auto_unlocked_for_mapping1\"]= np.where(regression.auto_unlocked_for_mapping==0,0,1)\n",
        "regression[\"auto_unlocked_for_validation1\"]= np.where(regression.auto_unlocked_for_validation==0,0,1)\n",
        "regression[\"badimagery1\"]= np.where(regression.badimagery==0,0,1)\n",
        "regression[\"extended_for_mapping1\"]= np.where(regression.extended_for_mapping==0,0,1)\n",
        "regression['projId'] = regression['projId'].apply(str)\n",
        "regression"
      ],
      "metadata": {
        "id": "vrPS0tt_5fjb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Create file with regression data (\"regression.csv\")\n",
        "regression.to_csv(\"regression.csv\",index=False)"
      ],
      "metadata": {
        "id": "a5SaoZRH5v4s"
      },
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}