Project Board Dashboard Script Editable.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7fae0959-89eb-40dd-be2f-a48fe0e62cf8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: python-dotenv in c:\\users\\kimbe\\anaconda3\\lib\\site-packages (1.0.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install python-dotenv \n",
    "# this package is not necessary for the .py script in the repository"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f8f249aa-fd5f-4530-b646-a3ea4cb33d65",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "import json\n",
    "import re\n",
    "from dotenv import load_dotenv # library not needed in .py script in repository"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "afa9c165-7495-4cce-838b-23631ffc19e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "load_dotenv() # do not add this line to .py script in repository"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01cb04e0-9d79-4b92-9fb0-2be1f0737b96",
   "metadata": {},
   "source": [
    "### Set Up for API Calls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bb1e2caf-0ebc-40d6-881c-7309423a2a71",
   "metadata": {},
   "outputs": [],
   "source": [
    "GitHub_token = os.getenv(\"GITHUB_TOKEN\")\n",
    "user = os.getenv(\"USER\")\n",
    "\n",
    "# Script's author has added personal GitHub_token to live-automation-dashboard repository's GitHub secrets for scheduled runs\n",
    "# To run this .ipynb script on your own device for testing successfully, \n",
    "# please generate your own GitHub token and add the token and your GitHub username info to your own .env file (refer to template in repo)\n",
    "\n",
    "# Please use following code for .py file script in repository (already in use - to swap token, go to GitHub Secrets for live-dashboard-automation repository and change username below in .py script):\n",
    "# GitHub_token = os.environ[\"API_KEY_GITHUB_PROJECTBOARD_DASHBOARD\"]\n",
    "# user = 'kimberlytanyh'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4aa390e4-762a-40fd-a11e-a45d51075e06",
   "metadata": {},
   "source": [
    "## Get Cards in Project Board Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "355d6ba4-c7c3-455a-baa4-91104e9f7341",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "87"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Get Cards in Ice Box\n",
    "\n",
    "url = 'https://api.github.com/projects/columns/7198227/cards'\n",
    "ice_box = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 11):\n",
    "    params = {\"per_page\": 100, \"page\": i}\n",
    "    response = requests.get(url, auth=(user, GitHub_token), params = params)\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if len(df) > 0:\n",
    "        ice_box = pd.concat([ice_box, df], ignore_index = True)\n",
    "    else:\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8034772b-611a-4242-8e29-d704ca58aeeb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Get Cards in ER Column\n",
    "\n",
    "url = 'https://api.github.com/projects/columns/19403960/cards'\n",
    "er = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 11):\n",
    "    params = {\"per_page\": 100, \"page\": i}\n",
    "    response = requests.get(url, auth=(user, GitHub_token), params = params)\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if len(df) > 0:\n",
    "        er = pd.concat([er, df], ignore_index = True)\n",
    "    else: \n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6c4da6af-7c60-4970-86f2-27dd5bd164a0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "166\n"
     ]
    }
   ],
   "source": [
    "### Get Cards in New Issue Approval Column\n",
    "\n",
    "url = 'https://api.github.com/projects/columns/15235217/cards'\n",
    "newissue_approval = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 11):\n",
    "    params = {\"per_page\": 100, \"page\": i}\n",
    "    response = requests.get(url, auth=(user, GitHub_token), params = params)\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if len(df) > 0:\n",
    "        newissue_approval = pd.concat([newissue_approval, df], ignore_index = True)\n",
    "    else: \n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1b4deee8-ba34-4c50-a02c-4adfc60315af",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "80\n"
     ]
    }
   ],
   "source": [
    "### Get Cards in Prioritized Backlog Column\n",
    "\n",
    "url = 'https://api.github.com/projects/columns/7198257/cards'\n",
    "prioritized_backlog = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 11):\n",
    "    params = {\"per_page\": 100, \"page\": i}\n",
    "    response = requests.get(url, auth=(user, GitHub_token), params = params)\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if len(df) > 0:\n",
    "        prioritized_backlog = pd.concat([prioritized_backlog, df], ignore_index = True)\n",
    "    else: \n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "247ce27f-3843-45e2-8137-d6b8fd62ab83",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "141\n"
     ]
    }
   ],
   "source": [
    "### Get Cards in \"In Progress (Actively Working)\" Column\n",
    "\n",
    "url = 'https://api.github.com/projects/columns/7198228/cards'\n",
    "in_progress = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 11):\n",
    "    params = {\"per_page\": 100, \"page\": i}\n",
    "    response = requests.get(url, auth=(user, GitHub_token), params = params)\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if len(df) > 0:\n",
    "        in_progress = pd.concat([in_progress, df], ignore_index = True)\n",
    "    else: \n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "41f0806b-1cb7-474a-b4d7-e9ed90833ace",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9\n"
     ]
    }
   ],
   "source": [
    "### Get Cards in Questions/In Review Column\n",
    "\n",
    "url = 'https://api.github.com/projects/columns/8178690/cards'\n",
    "questions = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 11):\n",
    "    params = {\"per_page\": 100, \"page\": i}\n",
    "    response = requests.get(url, auth=(user, GitHub_token), params = params)\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if len(df) > 0:\n",
    "        questions = pd.concat([questions, df], ignore_index = True)\n",
    "    else: \n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "97559c0d-1022-4ee1-a58b-9b2e8b9a81de",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "464\n"
     ]
    }
   ],
   "source": [
    "### Get Cards in QA Column\n",
    "\n",
    "url = 'https://api.github.com/projects/columns/15490305/cards'\n",
    "QA = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 11):\n",
    "    params = {\"per_page\": 100, \"page\": i}\n",
    "    response = requests.get(url, auth=(user, GitHub_token), params = params)\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if len(df) > 0:\n",
    "        QA = pd.concat([QA, df], ignore_index = True)\n",
    "    else: \n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "551d7c5b-8d8e-4945-b711-f9b21b4ed1e3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3\n"
     ]
    }
   ],
   "source": [
    "### Get Cards in UAT Column\n",
    "\n",
    "url = 'https://api.github.com/projects/columns/17206624/cards'\n",
    "UAT = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 11):\n",
    "    params = {\"per_page\": 100, \"page\": i}\n",
    "    response = requests.get(url, auth=(user, GitHub_token), params = params)\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if len(df) > 0:\n",
    "        UAT = pd.concat([UAT, df], ignore_index = True)\n",
    "    else: \n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b12bf5ae-e5d9-4f8f-be02-89cdc2ea0e58",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8\n"
     ]
    }
   ],
   "source": [
    "### Get Cards in \"QA - senior review\" Column\n",
    "\n",
    "url = 'https://api.github.com/projects/columns/19257634/cards'\n",
    "QA_review = pd.DataFrame()\n",
    "\n",
    "for i in range(1, 11):\n",
    "    params = {\"per_page\": 100, \"page\": i}\n",
    "    response = requests.get(url, auth=(user, GitHub_token), params = params)\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if len(df) > 0:\n",
    "        QA_review = pd.concat([QA_review, df], ignore_index = True)\n",
    "    else: \n",
    "        break"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74e149b8-72c9-429d-bdc4-a9b7795d698a",
   "metadata": {},
   "source": [
    "### Get Issue Links in Project Board Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "dd62f178-442d-4a22-ab4f-8612a8d57e45",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Get Issue Links in Ice Box and Get Issue Data\n",
    "\n",
    "icebox_issues = list(ice_box[~ice_box['content_url'].isna()]['content_url'])  \n",
    "\n",
    "icebox_issues_df = pd.DataFrame()\n",
    "\n",
    "try:\n",
    "    for url in icebox_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        icebox_issues_df = pd.concat([icebox_issues_df, issue_data], ignore_index = True)\n",
    "except ValueError:\n",
    "    time.sleep(3600)\n",
    "    for url in icebox_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        icebox_issues_df = pd.concat([icebox_issues_df, issue_data], ignore_index = True)\n",
    "        \n",
    "from datetime import datetime\n",
    "import pytz\n",
    "\n",
    "# Get the timezone object for New York\n",
    "tz_LA = pytz.timezone('US/Pacific') \n",
    "\n",
    "# Get the current time in New York\n",
    "datetime_LA = datetime.now(tz_LA)\n",
    "\n",
    "# Format the time as a string and print it\n",
    "icebox_issues_df[\"Runtime\"] = \"LA time: \"+datetime_LA.strftime(\"%m/%d/%Y %H:%M:%S\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "40f7e32f-ca16-4b31-88a8-b16e4c2f7485",
   "metadata": {},
   "outputs": [],
   "source": [
    "icebox_issues_df.drop(columns = ['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'id',\n",
    "                                 'node_id', 'number', 'state', 'locked', 'assignee', 'assignees', 'comments', 'created_at',\n",
    "                                 'updated_at', 'closed_at', 'author_association', 'active_lock_reason', 'body', 'closed_by',\n",
    "                                 'timeline_url', 'performed_via_github_app', 'state_reason', 'user.login', 'user.id',\n",
    "                                 'user.node_id', 'user.avatar_url', 'user.gravatar_id', 'user.url', 'user.html_url', 'user.followers_url',\n",
    "                                 'user.following_url', 'user.gists_url', 'user.starred_url', 'user.subscriptions_url',\n",
    "                                 'user.organizations_url', 'user.repos_url', 'user.events_url', 'user.received_events_url',\n",
    "                                 'user.type', 'user.site_admin', 'milestone.url', 'milestone.html_url', 'milestone.labels_url',\n",
    "                                 'milestone.id', 'milestone.node_id', 'milestone.number', 'milestone.title', 'milestone.description',\n",
    "                                 'milestone.creator.login', 'milestone.creator.id', 'milestone.creator.node_id', 'milestone.creator.avatar_url',\n",
    "                                 'milestone.creator.gravatar_id', 'milestone.creator.url', 'milestone.creator.html_url',\n",
    "                                 'milestone.creator.followers_url', 'milestone.creator.following_url', 'milestone.creator.gists_url', 'milestone.creator.starred_url',\n",
    "                                 'milestone.creator.subscriptions_url', 'milestone.creator.organizations_url', 'milestone.creator.repos_url',\n",
    "                                 'milestone.creator.events_url', 'milestone.creator.received_events_url',\n",
    "                                 'milestone.creator.type', 'milestone.creator.site_admin', 'milestone.open_issues', 'milestone.closed_issues',\n",
    "                                 'milestone.state', 'milestone.created_at', 'milestone.updated_at', 'milestone.due_on',\n",
    "                                 'milestone.closed_at', 'reactions.url', 'reactions.total_count', 'reactions.+1', 'reactions.-1', 'reactions.laugh',\n",
    "                                 'reactions.hooray', 'reactions.confused', 'reactions.heart', 'reactions.rocket', 'reactions.eyes',\n",
    "                                 'assignee.login', 'assignee.id', 'assignee.node_id', 'assignee.avatar_url', 'assignee.gravatar_id', 'assignee.url',\n",
    "                                 'assignee.html_url', 'assignee.followers_url', 'assignee.following_url', 'assignee.gists_url',\n",
    "                                 'assignee.starred_url', 'assignee.subscriptions_url', 'assignee.organizations_url',\n",
    "                                 'assignee.repos_url', 'assignee.events_url', 'assignee.received_events_url', 'assignee.type',\n",
    "                                 'assignee.site_admin', 'closed_by.login',\n",
    "                                 'closed_by.id', 'closed_by.node_id', 'closed_by.avatar_url', 'closed_by.gravatar_id', 'closed_by.url',\n",
    "                                 'closed_by.html_url', 'closed_by.followers_url', 'closed_by.following_url', 'closed_by.gists_url',\n",
    "                                 'closed_by.starred_url', 'closed_by.subscriptions_url', 'closed_by.organizations_url', 'closed_by.repos_url',\n",
    "                                 'closed_by.events_url', 'closed_by.received_events_url', 'closed_by.type', 'closed_by.site_admin'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "939208e1-8095-4328-9a72-363c231f1776",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Flatten labels column\n",
    "\n",
    "flatten_icebox = icebox_issues_df.to_json(orient = \"records\")\n",
    "parsed_icebox = json.loads(flatten_icebox)\n",
    "icebox_issues_df2 = pd.json_normalize(parsed_icebox, record_path = [\"labels\"], record_prefix = \"labels.\", meta = [\"Runtime\", \"html_url\", \"title\"])\n",
    "\n",
    "icebox_issues_df2.drop(columns = ['labels.id', 'labels.node_id', 'labels.url', 'labels.description',\n",
    "       'labels.color', 'labels.default'], inplace = True)\n",
    "\n",
    "# Remove issues with ignore labels in icebox column\n",
    "if len([label for label in icebox_issues_df2[\"labels.name\"].unique() if re.search('ignore', label.lower())])>0:\n",
    "    remove = list(icebox_issues_df2[icebox_issues_df2[\"labels.name\"].str.contains(\"gnore\")][\"html_url\"])\n",
    "    icebox_issues_df2 = icebox_issues_df2[~icebox_issues_df2[\"html_url\"].isin(remove)]\n",
    "else:\n",
    "    remove = []\n",
    "\n",
    "# Finishing touches for icebox dataset (include issues with zero labels)\n",
    "icebox_difference = list(set(icebox_issues_df[\"html_url\"]).difference(set(icebox_issues_df2[\"html_url\"])))\n",
    "icebox_no_labels = list(set(icebox_difference).difference(set(remove)))\n",
    "icebox_no_labels_df = icebox_issues_df[icebox_issues_df[\"html_url\"].isin(icebox_no_labels)][[\"Runtime\", \"html_url\", \"title\"]]\n",
    "icebox_no_labels_df[\"labels.name\"] = \"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5fff33ab-f774-470a-b9ca-ba29b18ec2cf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['labels.name', 'Runtime', 'html_url', 'title', 'Project Board Column'], dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "556"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "icebox_no_labels_df = icebox_no_labels_df[[\"labels.name\", \"Runtime\", \"html_url\", \"title\"]]\n",
    "\n",
    "icebox_issues_df3 = pd.concat([icebox_issues_df2, icebox_no_labels_df], ignore_index = True)\n",
    "\n",
    "icebox_issues_df3[\"Project Board Column\"] = \"1 - Icebox\"\n",
    "\n",
    "print(icebox_issues_df3.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "097a8892-2ab1-4aa2-8922-7d5f0c360b7c",
   "metadata": {},
   "source": [
    "### Digress: Create Variables with List of Complexity Labels and Status Breakdowns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a4b344b7-3705-4f40-844a-a71a5e4bee79",
   "metadata": {},
   "outputs": [],
   "source": [
    "complexity_labels = [\"Complexity: Prework\", \"Complexity: Missing\", \"Complexity: Large\", \n",
    "                     \"Complexity: Extra Large\", \"Complexity: Small\", \"good first issue\", \n",
    "                     \"Complexity: Medium\", \"Complexity: See issue making label\", \"prework\", \n",
    "                     \"Complexity: Good second issue\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "36eebed8-f85f-45a7-a87a-ce6c6b288e1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "extra_breakdown = [\"Draft\", \"2 weeks inactive\", \"ready for product\", \n",
    "                   \"ready for dev lead\", \"Ready for Prioritization\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a4962f3-6bf5-43db-9635-348c603c6bef",
   "metadata": {},
   "source": [
    "### Back to Getting Issue Links and Creating Datasets for Each Project Board Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e1495ff3-65ee-43a7-9770-bc4c395e8d13",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\1538308126.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  icebox_wdataset[\"front/back end count\"] = icebox_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\1538308126.py:11: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_icebox.loc[list(icebox_wdataset[icebox_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\1538308126.py:13: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_icebox.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "#### Finish up creating Icebox dataset\n",
    "\n",
    "# retain only labels with \"role\" in it or complexity labels, and \"Draft\", \"ready for product\", \"ready for prioritization\", \"ready for dev lead\"\n",
    "final_icebox = icebox_issues_df2[(icebox_issues_df2[\"labels.name\"].str.contains(\"role\") | icebox_issues_df2[\"labels.name\"].isin(complexity_labels) | \n",
    "                          icebox_issues_df2[\"labels.name\"].isin(extra_breakdown) | icebox_issues_df2[\"labels.name\"].str.contains(\"Ready\", case = False))]\n",
    "\n",
    "# Make combined label for issues with front and backend labels\n",
    "icebox_wdataset = final_icebox[final_icebox[\"labels.name\"].str.contains(\"front end\") | final_icebox[\"labels.name\"].str.contains(\"back end\")]\n",
    "icebox_wdataset[\"front/back end count\"] = icebox_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "final_icebox.loc[list(icebox_wdataset[icebox_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "final_icebox.drop_duplicates(inplace = True)\n",
    "\n",
    "final_icebox2 = final_icebox[[\"Runtime\", \"labels.name\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "60b3ddf0-0a5e-4fd9-8986-249569d39636",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\2762510469.py:91: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  ER_wdataset[\"front/back end count\"] = ER_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\2762510469.py:93: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_ER.loc[list(ER_wdataset[ER_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\2762510469.py:95: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_ER.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "### Get Issue Links in ER Column and Get Issue Data\n",
    "\n",
    "er_issues = list(er[~er['content_url'].isna()]['content_url'])  \n",
    "\n",
    "ER_issues_df = pd.DataFrame()\n",
    "\n",
    "try:\n",
    "    for url in er_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        ER_issues_df = pd.concat([ER_issues_df, issue_data], ignore_index = True)\n",
    "except ValueError:\n",
    "    time.sleep(3600)\n",
    "    for url in er_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        ER_issues_df = pd.concat([ER_issues_df, issue_data], ignore_index = True)\n",
    "\n",
    "# Get the timezone object for New York\n",
    "tz_LA = pytz.timezone('US/Pacific') \n",
    "\n",
    "# Get the current time in New York\n",
    "datetime_LA = datetime.now(tz_LA)\n",
    "\n",
    "# Format the time as a string and print it\n",
    "ER_issues_df[\"Runtime\"] = \"LA time: \"+datetime_LA.strftime(\"%m/%d/%Y %H:%M:%S\")\n",
    "\n",
    "# Drop unneeded columns\n",
    "ER_issues_df.drop(columns = ['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'id',\n",
    "                             'node_id', 'number', 'state', 'locked', 'assignee', 'assignees', 'comments', 'created_at',\n",
    "                             'updated_at', 'closed_at', 'author_association', 'active_lock_reason', 'body', 'closed_by',\n",
    "                             'timeline_url', 'performed_via_github_app', 'state_reason', 'user.login', 'user.id',\n",
    "                             'user.node_id', 'user.avatar_url', 'user.gravatar_id', 'user.url', 'user.html_url', 'user.followers_url',\n",
    "                             'user.following_url', 'user.gists_url', 'user.starred_url', 'user.subscriptions_url',\n",
    "                             'user.organizations_url', 'user.repos_url', 'user.events_url', 'user.received_events_url',\n",
    "                             'user.type', 'user.site_admin', 'milestone.url', 'milestone.html_url', 'milestone.labels_url',\n",
    "                             'milestone.id', 'milestone.node_id', 'milestone.number', 'milestone.title', 'milestone.description',\n",
    "                             'milestone.creator.login', 'milestone.creator.id', 'milestone.creator.node_id', 'milestone.creator.avatar_url',\n",
    "                             'milestone.creator.gravatar_id', 'milestone.creator.url', 'milestone.creator.html_url',\n",
    "                             'milestone.creator.followers_url', 'milestone.creator.following_url', 'milestone.creator.gists_url', 'milestone.creator.starred_url',\n",
    "                             'milestone.creator.subscriptions_url', 'milestone.creator.organizations_url', 'milestone.creator.repos_url',\n",
    "                             'milestone.creator.events_url', 'milestone.creator.received_events_url',\n",
    "                             'milestone.creator.type', 'milestone.creator.site_admin', 'milestone.open_issues', 'milestone.closed_issues',\n",
    "                             'milestone.state', 'milestone.created_at', 'milestone.updated_at', 'milestone.due_on',\n",
    "                             'milestone.closed_at', 'reactions.url', 'reactions.total_count', 'reactions.+1', 'reactions.-1', 'reactions.laugh',\n",
    "                             'reactions.hooray', 'reactions.confused', 'reactions.heart', 'reactions.rocket', 'reactions.eyes',\n",
    "                             'assignee.login', 'assignee.id', 'assignee.node_id', 'assignee.avatar_url', 'assignee.gravatar_id', 'assignee.url',\n",
    "                             'assignee.html_url', 'assignee.followers_url', 'assignee.following_url', 'assignee.gists_url',\n",
    "                             'assignee.starred_url', 'assignee.subscriptions_url', 'assignee.organizations_url',\n",
    "                             'assignee.repos_url', 'assignee.events_url', 'assignee.received_events_url', 'assignee.type',\n",
    "                             'assignee.site_admin', 'closed_by.login',\n",
    "                             'closed_by.id', 'closed_by.node_id', 'closed_by.avatar_url', 'closed_by.gravatar_id', 'closed_by.url',\n",
    "                             'closed_by.html_url', 'closed_by.followers_url', 'closed_by.following_url', 'closed_by.gists_url',\n",
    "                             'closed_by.starred_url', 'closed_by.subscriptions_url', 'closed_by.organizations_url', 'closed_by.repos_url',\n",
    "                             'closed_by.events_url', 'closed_by.received_events_url', 'closed_by.type', 'closed_by.site_admin'], inplace = True)\n",
    "\n",
    "# Flatten labels column\n",
    "\n",
    "flatten_ER = ER_issues_df.to_json(orient = \"records\")\n",
    "parsed_ER = json.loads(flatten_ER)\n",
    "ER_issues_df2 = pd.json_normalize(parsed_ER, record_path = [\"labels\"], record_prefix = \"labels.\", meta = [\"Runtime\", \"html_url\", \"title\"])\n",
    "\n",
    "\n",
    "ER_issues_df2.drop(columns = ['labels.id', 'labels.node_id', 'labels.url', 'labels.description',\n",
    "       'labels.color', 'labels.default'], inplace = True)\n",
    "\n",
    "# Remove issues with ignore labels in ER column\n",
    "if len([label for label in ER_issues_df2[\"labels.name\"].unique() if re.search('ignore', label.lower())])>0:\n",
    "    remove = list(ER_issues_df2[ER_issues_df2[\"labels.name\"].str.contains(\"gnore\")][\"html_url\"])\n",
    "    ER_issues_df2 = ER_issues_df2[~ER_issues_df2[\"html_url\"].isin(remove)]\n",
    "else:\n",
    "    remove = []\n",
    "\n",
    "# Finishing touches for ER dataset (include issues with zero labels)\n",
    "ER_difference = list(set(ER_issues_df[\"html_url\"]).difference(set(ER_issues_df2[\"html_url\"])))\n",
    "ER_no_labels = list(set(ER_difference).difference(set(remove)))\n",
    "ER_no_labels_df = ER_issues_df[ER_issues_df[\"html_url\"].isin(ER_no_labels)][[\"Runtime\", \"html_url\", \"title\"]]\n",
    "ER_no_labels_df[\"labels.name\"] = \"\"\n",
    "ER_no_labels_df = ER_no_labels_df[[\"labels.name\", \"Runtime\", \"html_url\", \"title\"]]\n",
    "\n",
    "ER_issues_df3 = pd.concat([ER_issues_df2, ER_no_labels_df], ignore_index = True)\n",
    "\n",
    "ER_issues_df3[\"Project Board Column\"] = \"2- ER\"\n",
    "\n",
    "# retain only labels with \"role\" in it or complexity labels, and \"Draft\", \"ready for product\", \"ready for prioritization\", \"ready for dev lead\"\n",
    "final_ER = ER_issues_df2[(ER_issues_df2[\"labels.name\"].str.contains(\"role\") | ER_issues_df2[\"labels.name\"].isin(complexity_labels) | \n",
    "                          ER_issues_df2[\"labels.name\"].isin(extra_breakdown))| ER_issues_df2[\"labels.name\"].str.contains(\"Ready\", case=False)]\n",
    "\n",
    "# Make combined label for issues with front and backend labels\n",
    "ER_wdataset = final_ER[final_ER[\"labels.name\"].str.contains(\"front end\") | final_ER[\"labels.name\"].str.contains(\"back end\")]\n",
    "ER_wdataset[\"front/back end count\"] = ER_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "final_ER.loc[list(ER_wdataset[ER_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "final_ER.drop_duplicates(inplace = True)\n",
    "\n",
    "final_ER2 = final_ER[[\"Runtime\", \"labels.name\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2dbf10cb-3a83-4c16-b045-2ed6e9aa2c21",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\3912394854.py:95: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  NIA_wdataset[\"front/back end count\"] = NIA_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\3912394854.py:97: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_NIA.loc[list(NIA_wdataset[NIA_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\3912394854.py:99: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_NIA.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "### Get Issue Links in New Issue Approval Column and Get Issue Data\n",
    "\n",
    "# Retain only issue cards (take out description cards)\n",
    "\n",
    "newissue_approval = newissue_approval[~newissue_approval[\"content_url\"].isna()]\n",
    "\n",
    "NIA_issues = list(newissue_approval['content_url'])\n",
    "\n",
    "NIA_issues_df = pd.DataFrame()\n",
    "\n",
    "try:\n",
    "    for url in NIA_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        NIA_issues_df = pd.concat([NIA_issues_df, issue_data], ignore_index = True)\n",
    "except ValueError:\n",
    "    time.sleep(3600)\n",
    "    for url in NIA_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        NIA_issues_df = pd.concat([NIA_issues_df, issue_data], ignore_index = True)\n",
    "\n",
    "\n",
    "# Get the timezone object for New York\n",
    "tz_LA = pytz.timezone('US/Pacific') \n",
    "\n",
    "# Get the current time in New York\n",
    "datetime_LA = datetime.now(tz_LA)\n",
    "\n",
    "# Format the time as a string \n",
    "NIA_issues_df[\"Runtime\"] = \"LA time: \"+datetime_LA.strftime(\"%m/%d/%Y %H:%M:%S\")\n",
    "\n",
    "# Drop unneeded columns\n",
    "NIA_issues_df.drop(columns = ['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'id',\n",
    "                              'node_id', 'number', 'state', 'locked', 'assignee', 'assignees', 'comments', 'created_at',\n",
    "                              'updated_at', 'closed_at', 'author_association', 'active_lock_reason', 'body', 'closed_by',\n",
    "                              'timeline_url', 'performed_via_github_app', 'state_reason', 'user.login', 'user.id',\n",
    "                              'user.node_id', 'user.avatar_url', 'user.gravatar_id', 'user.url', 'user.html_url', 'user.followers_url',\n",
    "                              'user.following_url', 'user.gists_url', 'user.starred_url', 'user.subscriptions_url',\n",
    "                              'user.organizations_url', 'user.repos_url', 'user.events_url', 'user.received_events_url',\n",
    "                              'user.type', 'user.site_admin', 'milestone.url', 'milestone.html_url', 'milestone.labels_url',\n",
    "                              'milestone.id', 'milestone.node_id', 'milestone.number', 'milestone.title', 'milestone.description',\n",
    "                              'milestone.creator.login', 'milestone.creator.id', 'milestone.creator.node_id', 'milestone.creator.avatar_url',\n",
    "                              'milestone.creator.gravatar_id', 'milestone.creator.url', 'milestone.creator.html_url',\n",
    "                              'milestone.creator.followers_url', 'milestone.creator.following_url', 'milestone.creator.gists_url', 'milestone.creator.starred_url',\n",
    "                              'milestone.creator.subscriptions_url', 'milestone.creator.organizations_url', 'milestone.creator.repos_url',\n",
    "                              'milestone.creator.events_url', 'milestone.creator.received_events_url',\n",
    "                              'milestone.creator.type', 'milestone.creator.site_admin', 'milestone.open_issues', 'milestone.closed_issues',\n",
    "                              'milestone.state', 'milestone.created_at', 'milestone.updated_at', 'milestone.due_on',\n",
    "                              'milestone.closed_at', 'reactions.url', 'reactions.total_count', 'reactions.+1', 'reactions.-1', 'reactions.laugh',\n",
    "                              'reactions.hooray', 'reactions.confused', 'reactions.heart', 'reactions.rocket', 'reactions.eyes',\n",
    "                              'assignee.login', 'assignee.id', 'assignee.node_id', 'assignee.avatar_url', 'assignee.gravatar_id', 'assignee.url',\n",
    "                              'assignee.html_url', 'assignee.followers_url', 'assignee.following_url', 'assignee.gists_url',\n",
    "                              'assignee.starred_url', 'assignee.subscriptions_url', 'assignee.organizations_url',\n",
    "                              'assignee.repos_url', 'assignee.events_url', 'assignee.received_events_url', 'assignee.type',\n",
    "                              'assignee.site_admin', 'closed_by.login',\n",
    "                              'closed_by.id', 'closed_by.node_id', 'closed_by.avatar_url', 'closed_by.gravatar_id', 'closed_by.url',\n",
    "                              'closed_by.html_url', 'closed_by.followers_url', 'closed_by.following_url', 'closed_by.gists_url',\n",
    "                              'closed_by.starred_url', 'closed_by.subscriptions_url', 'closed_by.organizations_url', 'closed_by.repos_url',\n",
    "                              'closed_by.events_url', 'closed_by.received_events_url', 'closed_by.type', 'closed_by.site_admin'], inplace = True)\n",
    "\n",
    "flatten_NIA = NIA_issues_df.to_json(orient = \"records\")\n",
    "parsed_NIA = json.loads(flatten_NIA)\n",
    "NIA_issues_df2 = pd.json_normalize(parsed_NIA, record_path = [\"labels\"], record_prefix = \"labels.\", meta = [\"Runtime\", \"html_url\", \"title\"])\n",
    "\n",
    "\n",
    "NIA_issues_df2.drop(columns = ['labels.id', 'labels.node_id', 'labels.url', 'labels.description',\n",
    "       'labels.color', 'labels.default'], inplace = True)\n",
    "\n",
    "# Remove issues with ignore labels in NIA column\n",
    "if len([label for label in NIA_issues_df2[\"labels.name\"].unique() if re.search('ignore', label.lower())])>0:\n",
    "    remove = list(NIA_issues_df2[NIA_issues_df2[\"labels.name\"].str.contains(\"gnore\")][\"html_url\"])\n",
    "    NIA_issues_df2 = NIA_issues_df2[~NIA_issues_df2[\"html_url\"].isin(remove)]\n",
    "else:\n",
    "    remove = []\n",
    "    \n",
    "# Finishing touches for ER dataset (include issues with no labels)\n",
    "NIA_difference = list(set(NIA_issues_df[\"html_url\"]).difference(set(NIA_issues_df2[\"html_url\"])))\n",
    "NIA_no_labels = list(set(NIA_difference).difference(set(remove)))\n",
    "NIA_no_labels_df = NIA_issues_df[NIA_issues_df[\"html_url\"].isin(NIA_no_labels)][[\"Runtime\", \"html_url\", \"title\"]]\n",
    "NIA_no_labels_df[\"labels.name\"] = \"\"\n",
    "NIA_no_labels_df = NIA_no_labels_df[[\"labels.name\", \"Runtime\", \"html_url\", \"title\"]]\n",
    "\n",
    "NIA_issues_df3 = pd.concat([NIA_issues_df2, NIA_no_labels_df], ignore_index = True)\n",
    "\n",
    "NIA_issues_df3[\"Project Board Column\"] = \"3 - New Issue Approval\"\n",
    "len(NIA_issues_df3)\n",
    "\n",
    "# retain only labels with \"role\" in it or complexity labels, and \"Draft\", \"ready for product\", \"ready for prioritization\", \"ready for dev lead\"\n",
    "final_NIA = NIA_issues_df2[(NIA_issues_df2[\"labels.name\"].str.contains(\"role\") | NIA_issues_df2[\"labels.name\"].isin(complexity_labels) | \n",
    "                          NIA_issues_df2[\"labels.name\"].isin(extra_breakdown) | NIA_issues_df2[\"labels.name\"].str.contains(\"Ready\", case = False))]\n",
    "\n",
    "# Make combined label for issues with front and backend labels\n",
    "NIA_wdataset = final_NIA[final_NIA[\"labels.name\"].str.contains(\"front end\") | final_NIA[\"labels.name\"].str.contains(\"back end\")]\n",
    "NIA_wdataset[\"front/back end count\"] = NIA_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "final_NIA.loc[list(NIA_wdataset[NIA_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "final_NIA.drop_duplicates(inplace = True)\n",
    "\n",
    "final_NIA2 = final_NIA[[\"Runtime\", \"labels.name\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "cd267c12-50db-4009-8395-41b0abdb4598",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\2667164008.py:92: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  pb_wdataset[\"front/back end count\"] = pb_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\2667164008.py:94: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_pb.loc[list(pb_wdataset[pb_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\2667164008.py:96: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_pb.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "### Get Issue Links in Prioritized Backlog Column and Get Issue Data\n",
    "\n",
    "# Retain only issue cards (take out description cards)\n",
    "\n",
    "prioritized_backlog = prioritized_backlog[~prioritized_backlog[\"content_url\"].isna()]\n",
    "\n",
    "pb_issues = list(prioritized_backlog[\"content_url\"])\n",
    "\n",
    "pb_issues_df = pd.DataFrame()\n",
    "\n",
    "try:\n",
    "    for url in pb_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        pb_issues_df = pd.concat([pb_issues_df, issue_data], ignore_index = True)\n",
    "except ValueError:\n",
    "    time.sleep(3600)\n",
    "    for url in pb_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        pb_issues_df = pd.concat([pb_issues_df, issue_data], ignore_index = True)\n",
    "        \n",
    "# Get the timezone object for New York\n",
    "tz_LA = pytz.timezone('US/Pacific') \n",
    "\n",
    "# Get the current time in New York\n",
    "datetime_LA = datetime.now(tz_LA)\n",
    "\n",
    "# Format the time as a string and add to Runtime column\n",
    "pb_issues_df[\"Runtime\"] = \"LA time: \"+datetime_LA.strftime(\"%m/%d/%Y %H:%M:%S\")\n",
    "\n",
    "# Drop unneeded columns\n",
    "pb_issues_df.drop(columns = ['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'id',\n",
    "                             'node_id', 'number', 'state', 'locked', 'assignee', 'assignees', 'comments', 'created_at',\n",
    "                             'updated_at', 'closed_at', 'author_association', 'active_lock_reason', 'body', 'closed_by',\n",
    "                             'timeline_url', 'performed_via_github_app', 'state_reason', 'user.login', 'user.id',\n",
    "                             'user.node_id', 'user.avatar_url', 'user.gravatar_id', 'user.url', 'user.html_url', 'user.followers_url',\n",
    "                             'user.following_url', 'user.gists_url', 'user.starred_url', 'user.subscriptions_url',\n",
    "                             'user.organizations_url', 'user.repos_url', 'user.events_url', 'user.received_events_url',\n",
    "                             'user.type', 'user.site_admin', 'milestone.url', 'milestone.html_url', 'milestone.labels_url',\n",
    "                             'milestone.id', 'milestone.node_id', 'milestone.number', 'milestone.title', 'milestone.description',\n",
    "                             'milestone.creator.login', 'milestone.creator.id', 'milestone.creator.node_id', 'milestone.creator.avatar_url',\n",
    "                             'milestone.creator.gravatar_id', 'milestone.creator.url', 'milestone.creator.html_url',\n",
    "                             'milestone.creator.followers_url', 'milestone.creator.following_url', 'milestone.creator.gists_url', 'milestone.creator.starred_url',\n",
    "                             'milestone.creator.subscriptions_url', 'milestone.creator.organizations_url', 'milestone.creator.repos_url',\n",
    "                             'milestone.creator.events_url', 'milestone.creator.received_events_url',\n",
    "                             'milestone.creator.type', 'milestone.creator.site_admin', 'milestone.open_issues', 'milestone.closed_issues',\n",
    "                             'milestone.state', 'milestone.created_at', 'milestone.updated_at', 'milestone.due_on',\n",
    "                             'milestone.closed_at', 'reactions.url', 'reactions.total_count', 'reactions.+1', 'reactions.-1', 'reactions.laugh',\n",
    "                             'reactions.hooray', 'reactions.confused', 'reactions.heart', 'reactions.rocket', 'reactions.eyes', 'closed_by.login',\n",
    "                             'closed_by.id', 'closed_by.node_id', 'closed_by.avatar_url', 'closed_by.gravatar_id', 'closed_by.url',\n",
    "                             'closed_by.html_url', 'closed_by.followers_url', 'closed_by.following_url', 'closed_by.gists_url',\n",
    "                             'closed_by.starred_url', 'closed_by.subscriptions_url', 'closed_by.organizations_url', 'closed_by.repos_url',\n",
    "                             'closed_by.events_url', 'closed_by.received_events_url', 'closed_by.type', 'closed_by.site_admin'], inplace = True)\n",
    "\n",
    "# Expand labels column (flatten)\n",
    "\n",
    "flatten_pb = pb_issues_df.to_json(orient = \"records\")\n",
    "parsed_pb = json.loads(flatten_pb)\n",
    "pb_issues_df2 = pd.json_normalize(parsed_pb, record_path = ['labels'], record_prefix = 'labels.', meta = [\"Runtime\", \"html_url\", \"title\"])\n",
    "\n",
    "# Drop unneeded columns\n",
    "\n",
    "pb_issues_df2.drop(columns = ['labels.id', 'labels.node_id', 'labels.url', 'labels.description',\n",
    "       'labels.color', 'labels.default'], inplace = True)\n",
    "\n",
    "# Remove issues with ignore labels in Prioritized Backlog column\n",
    "if len([label for label in pb_issues_df2[\"labels.name\"].unique() if re.search('ignore', label.lower())])>0:\n",
    "    remove = list(pb_issues_df2[pb_issues_df2[\"labels.name\"].str.contains(\"gnore\")][\"html_url\"])\n",
    "    pb_issues_df2 = pb_issues_df2[~pb_issues_df2[\"html_url\"].isin(remove)]\n",
    "else:\n",
    "    remove = []\n",
    "    \n",
    "# Finishing touches for Prioritized Backlog dataset (include issues with no labels)\n",
    "pb_difference = list(set(pb_issues_df[\"html_url\"]).difference(set(pb_issues_df2[\"html_url\"])))\n",
    "pb_no_labels = list(set(pb_difference).difference(set(remove)))\n",
    "pb_no_labels_df = pb_issues_df[pb_issues_df[\"html_url\"].isin(pb_no_labels)][[\"Runtime\", \"html_url\", \"title\"]]\n",
    "pb_no_labels_df[\"labels.name\"] = \"\"\n",
    "pb_no_labels_df = pb_no_labels_df[[\"labels.name\", \"Runtime\", \"html_url\", \"title\"]]\n",
    "\n",
    "pb_issues_df3 = pd.concat([pb_issues_df2, pb_no_labels_df], ignore_index = False)\n",
    "\n",
    "pb_issues_df3[\"Project Board Column\"] = \"4 - Prioritized Backlog\"\n",
    "\n",
    "# retain only labels with \"role\" in it, complexity labels, and \"Draft\", \"ready for product\", \"ready for prioritization\", \"ready for dev lead\"\n",
    "\n",
    "final_pb = pb_issues_df2[(pb_issues_df2[\"labels.name\"].str.contains(\"role\") | pb_issues_df2[\"labels.name\"].isin(complexity_labels) | \n",
    "                          pb_issues_df2[\"labels.name\"].isin(extra_breakdown) | pb_issues_df2[\"labels.name\"].str.contains(\"Ready\", case = False))]\n",
    "\n",
    "# Make combined label for issues with front and backend labels\n",
    "pb_wdataset = final_pb[final_pb[\"labels.name\"].str.contains(\"front end\") | final_pb[\"labels.name\"].str.contains(\"back end\")]\n",
    "pb_wdataset[\"front/back end count\"] = pb_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "final_pb.loc[list(pb_wdataset[pb_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "final_pb.drop_duplicates(inplace = True)\n",
    "\n",
    "final_pb2 = final_pb[[\"Runtime\",\"labels.name\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "1264ab08-3bac-49fc-8191-0dd47e61ec46",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\1023262916.py:90: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  ip_wdataset[\"front/back end count\"] = ip_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\1023262916.py:92: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_ip.loc[list(ip_wdataset[ip_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\1023262916.py:94: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_ip.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "### Get Issue Links in \"In Progress Column\" and Get Issue Data\n",
    "\n",
    "in_progress_issues = list(in_progress[~in_progress['content_url'].isna()]['content_url']) \n",
    "\n",
    "ip_df = pd.DataFrame()\n",
    "\n",
    "try:\n",
    "    for url in in_progress_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        ip_df = pd.concat([ip_df, issue_data], ignore_index = True)\n",
    "except ValueError:\n",
    "    time.sleep(3600)\n",
    "    for url in in_progress_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        ip_df = pd.concat([ip_df, issue_data], ignore_index = True)\n",
    "        \n",
    "# Get the timezone object for New York\n",
    "tz_LA = pytz.timezone('US/Pacific') \n",
    "\n",
    "# Get the current time in New York\n",
    "datetime_LA = datetime.now(tz_LA)\n",
    "\n",
    "# Format the time as a string and add it to Runtime column\n",
    "ip_df[\"Runtime\"] = \"LA time: \"+datetime_LA.strftime(\"%m/%d/%Y %H:%M:%S\")\n",
    "\n",
    "# Drop unneeded columns\n",
    "ip_df.drop(columns = ['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'id',\n",
    "                      'node_id', 'number', 'state', 'locked', 'assignee', 'assignees', 'comments', 'created_at',\n",
    "                      'updated_at', 'closed_at', 'author_association', 'active_lock_reason', 'body', 'closed_by',\n",
    "                      'timeline_url', 'performed_via_github_app', 'state_reason', 'user.login', 'user.id',\n",
    "                      'user.node_id', 'user.avatar_url', 'user.gravatar_id', 'user.url', 'user.html_url', 'user.followers_url',\n",
    "                      'user.following_url', 'user.gists_url', 'user.starred_url', 'user.subscriptions_url',\n",
    "                      'user.organizations_url', 'user.repos_url', 'user.events_url', 'user.received_events_url',\n",
    "                      'user.type', 'user.site_admin', 'milestone.url', 'milestone.html_url', 'milestone.labels_url',\n",
    "                      'milestone.id', 'milestone.node_id', 'milestone.number', 'milestone.title', 'milestone.description',\n",
    "                      'milestone.creator.login', 'milestone.creator.id', 'milestone.creator.node_id', 'milestone.creator.avatar_url',\n",
    "                      'milestone.creator.gravatar_id', 'milestone.creator.url', 'milestone.creator.html_url',\n",
    "                      'milestone.creator.followers_url', 'milestone.creator.following_url', 'milestone.creator.gists_url', 'milestone.creator.starred_url',\n",
    "                      'milestone.creator.subscriptions_url', 'milestone.creator.organizations_url', 'milestone.creator.repos_url',\n",
    "                      'milestone.creator.events_url', 'milestone.creator.received_events_url',\n",
    "                      'milestone.creator.type', 'milestone.creator.site_admin', 'milestone.open_issues', 'milestone.closed_issues',\n",
    "                      'milestone.state', 'milestone.created_at', 'milestone.updated_at', 'milestone.due_on',\n",
    "                      'milestone.closed_at', 'reactions.url', 'reactions.total_count', 'reactions.+1', 'reactions.-1', 'reactions.laugh',\n",
    "                      'reactions.hooray', 'reactions.confused', 'reactions.heart', 'reactions.rocket', 'reactions.eyes',\n",
    "                      'assignee.login', 'assignee.id', 'assignee.node_id', 'assignee.avatar_url', 'assignee.gravatar_id', 'assignee.url',\n",
    "                      'assignee.html_url', 'assignee.followers_url', 'assignee.following_url', 'assignee.gists_url',\n",
    "                      'assignee.starred_url', 'assignee.subscriptions_url', 'assignee.organizations_url',\n",
    "                      'assignee.repos_url', 'assignee.events_url', 'assignee.received_events_url', 'assignee.type',\n",
    "                      'assignee.site_admin', 'closed_by.login',\n",
    "                      'closed_by.id', 'closed_by.node_id', 'closed_by.avatar_url', 'closed_by.gravatar_id', 'closed_by.url',\n",
    "                      'closed_by.html_url', 'closed_by.followers_url', 'closed_by.following_url', 'closed_by.gists_url',\n",
    "                      'closed_by.starred_url', 'closed_by.subscriptions_url', 'closed_by.organizations_url', 'closed_by.repos_url',\n",
    "                      'closed_by.events_url', 'closed_by.received_events_url', 'closed_by.type', 'closed_by.site_admin'], inplace = True)\n",
    "\n",
    "# Flatten labels column\n",
    "\n",
    "flatten_ip = ip_df.to_json(orient = \"records\")\n",
    "parsed_ip = json.loads(flatten_ip)\n",
    "ip_df2 = pd.json_normalize(parsed_ip, record_path = [\"labels\"], record_prefix = \"labels.\", meta = [\"Runtime\", \"html_url\", \"title\"])\n",
    "\n",
    "ip_df2.drop(columns = ['labels.id', 'labels.node_id', 'labels.url', 'labels.description',\n",
    "       'labels.color', 'labels.default'], inplace = True)\n",
    "\n",
    "# Remove issues with ignore labels in In Progress column\n",
    "if len([label for label in ip_df2[\"labels.name\"].unique() if re.search('ignore', label.lower())])>0:\n",
    "    remove = list(ip_df2[ip_df2[\"labels.name\"].str.contains(\"gnore\")][\"html_url\"])\n",
    "    ip_df2 = ip_df2[~ip_df2[\"html_url\"].isin(remove)]\n",
    "else:\n",
    "    remove = []\n",
    "    \n",
    "# Finishing touches for In Progress dataset (include issues with no labels)\n",
    "ip_difference = list(set(ip_df[\"html_url\"]).difference(set(ip_df2[\"html_url\"])))\n",
    "ip_no_labels = list(set(ip_difference).difference(set(remove)))\n",
    "ip_no_labels_df = ip_df[ip_df[\"html_url\"].isin(ip_no_labels)][[\"Runtime\", \"html_url\", \"title\"]]\n",
    "ip_no_labels_df[\"labels.name\"] = \"\"\n",
    "ip_no_labels_df = ip_no_labels_df[[\"labels.name\", \"Runtime\", \"html_url\", \"title\"]]\n",
    "\n",
    "ip_issues_df3 = pd.concat([ip_df2, ip_no_labels_df], ignore_index = True)\n",
    "\n",
    "ip_issues_df3[\"Project Board Column\"] = \"5 - In Progress\"\n",
    "\n",
    "# retain only labels with \"role\" in it or complexity labels, and \"Draft\", \"ready for product\", \"ready for prioritization\", \"ready for dev lead\"\n",
    "final_ip = ip_df2[(ip_df2[\"labels.name\"].str.contains(\"role\") | ip_df2[\"labels.name\"].isin(complexity_labels) | \n",
    "                          ip_df2[\"labels.name\"].isin(extra_breakdown) | ip_df2[\"labels.name\"].str.contains(\"Ready\", case = False))]\n",
    "\n",
    "# Make combined label for issues with front and backend labels\n",
    "ip_wdataset = final_ip[final_ip[\"labels.name\"].str.contains(\"front end\") | final_ip[\"labels.name\"].str.contains(\"back end\")]\n",
    "ip_wdataset[\"front/back end count\"] = ip_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "final_ip.loc[list(ip_wdataset[ip_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "final_ip.drop_duplicates(inplace = True)\n",
    "\n",
    "final_ip2 = final_ip[[\"Runtime\", \"labels.name\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "ce51d0f1-f98f-453c-b92b-8e7678ba2df6",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\1436804771.py:87: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  questions_wdataset[\"front/back end count\"] = questions_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\1436804771.py:89: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_questions.loc[list(questions_wdataset[questions_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\1436804771.py:91: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_questions.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "### Get Issue Links in \"Questions/ In Review\" and Get Issue Data\n",
    "\n",
    "questions_issues = list(questions[~questions['content_url'].isna()]['content_url'])  \n",
    "\n",
    "questions_issues_df = pd.DataFrame()\n",
    "\n",
    "try:\n",
    "    for url in questions_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        questions_issues_df = pd.concat([questions_issues_df, issue_data], ignore_index = True)\n",
    "except ValueError:\n",
    "    time.sleep(3600)\n",
    "    for url in questions_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        questions_issues_df = pd.concat([questions_issues_df, issue_data], ignore_index = True)\n",
    "        \n",
    "# Get the timezone object for New York\n",
    "tz_LA = pytz.timezone('US/Pacific') \n",
    "\n",
    "# Get the current time in New York\n",
    "datetime_LA = datetime.now(tz_LA)\n",
    "\n",
    "# Format the time as a string and add to Runtime column\n",
    "questions_issues_df[\"Runtime\"] = \"LA time: \"+datetime_LA.strftime(\"%m/%d/%Y %H:%M:%S\")\n",
    "\n",
    "# Drop unneeded columns\n",
    "questions_issues_df.drop(columns = ['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'id',\n",
    "                                    'node_id', 'number', 'state', 'locked', 'assignees', 'comments', 'created_at',\n",
    "                                    'updated_at', 'closed_at', 'author_association', 'active_lock_reason', 'body', 'closed_by',\n",
    "                                    'timeline_url', 'performed_via_github_app', 'state_reason', 'user.login', 'user.id',\n",
    "                                    'user.node_id', 'user.avatar_url', 'user.gravatar_id', 'user.url', 'user.html_url', 'user.followers_url',\n",
    "                                    'user.following_url', 'user.gists_url', 'user.starred_url', 'user.subscriptions_url',\n",
    "                                    'user.organizations_url', 'user.repos_url', 'user.events_url', 'user.received_events_url',\n",
    "                                    'user.type', 'user.site_admin', 'milestone.url', 'milestone.html_url', 'milestone.labels_url',\n",
    "                                    'milestone.id', 'milestone.node_id', 'milestone.number', 'milestone.title', 'milestone.description',\n",
    "                                    'milestone.creator.login', 'milestone.creator.id', 'milestone.creator.node_id', 'milestone.creator.avatar_url',\n",
    "                                    'milestone.creator.gravatar_id', 'milestone.creator.url', 'milestone.creator.html_url',\n",
    "                                    'milestone.creator.followers_url', 'milestone.creator.following_url', 'milestone.creator.gists_url', 'milestone.creator.starred_url',\n",
    "                                    'milestone.creator.subscriptions_url', 'milestone.creator.organizations_url', 'milestone.creator.repos_url',\n",
    "                                    'milestone.creator.events_url', 'milestone.creator.received_events_url',\n",
    "                                    'milestone.creator.type', 'milestone.creator.site_admin', 'milestone.open_issues', 'milestone.closed_issues',\n",
    "                                    'milestone.state', 'milestone.created_at', 'milestone.updated_at', 'milestone.due_on',\n",
    "                                    'milestone.closed_at', 'reactions.url', 'reactions.total_count', 'reactions.+1', 'reactions.-1', 'reactions.laugh',\n",
    "                                    'reactions.hooray', 'reactions.confused', 'reactions.heart', 'reactions.rocket', 'reactions.eyes',\n",
    "                                    'assignee.login', 'assignee.id', 'assignee.node_id', 'assignee.avatar_url', 'assignee.gravatar_id', 'assignee.url',\n",
    "                                    'assignee.html_url', 'assignee.followers_url', 'assignee.following_url', 'assignee.gists_url',\n",
    "                                    'assignee.starred_url', 'assignee.subscriptions_url', 'assignee.organizations_url',\n",
    "                                    'assignee.repos_url', 'assignee.events_url', 'assignee.received_events_url', 'assignee.type',\n",
    "                                    'assignee.site_admin'], inplace = True)\n",
    "\n",
    "# Flatten labels column\n",
    "\n",
    "flatten_questions = questions_issues_df.to_json(orient = \"records\")\n",
    "parsed_questions= json.loads(flatten_questions)\n",
    "questions_issues_df2 = pd.json_normalize(parsed_questions, record_path = [\"labels\"], record_prefix = \"labels.\", meta = [\"Runtime\", \"html_url\", \"title\"])\n",
    "\n",
    "questions_issues_df2.drop(columns = ['labels.id', 'labels.node_id', 'labels.url', 'labels.description',\n",
    "       'labels.color', 'labels.default'], inplace = True)\n",
    "\n",
    "# Remove issues with ignore labels in Questions/In Review column\n",
    "if len([label for label in questions_issues_df2[\"labels.name\"].unique() if re.search('ignore', label.lower())])>0:\n",
    "    remove = list(questions_issues_df2[questions_issues_df2[\"labels.name\"].str.contains(\"gnore\")][\"html_url\"])\n",
    "    questions_issues_df2 = questions_issues_df2[~questions_issues_df2[\"html_url\"].isin(remove)]\n",
    "else:\n",
    "    remove = []\n",
    "\n",
    "# Finishing touches for Questions/ In Review dataset (include issues with no labels)\n",
    "questions_difference = list(set(questions_issues_df[\"html_url\"]).difference(set(questions_issues_df2[\"html_url\"])))\n",
    "questions_no_labels = list(set(questions_difference).difference(set(remove)))\n",
    "questions_no_labels_df = questions_issues_df[questions_issues_df[\"html_url\"].isin(questions_no_labels)][[\"Runtime\", \"html_url\", \"title\"]]\n",
    "questions_no_labels_df[\"labels.name\"] = \"\"\n",
    "questions_no_labels_df = questions_no_labels_df[[\"labels.name\", \"Runtime\", \"html_url\", \"title\"]]\n",
    "\n",
    "\n",
    "questions_issues_df3 = pd.concat([questions_issues_df2, questions_no_labels_df], ignore_index = True)\n",
    "\n",
    "questions_issues_df3[\"Project Board Column\"] = \"6 - Questions/ In Review\"\n",
    "\n",
    "# retain only labels with \"role\" in it or complexity labels, and \"Draft\", \"ready for product\", \"ready for prioritization\", \"ready for dev lead\"\n",
    "final_questions = questions_issues_df2[(questions_issues_df2[\"labels.name\"].str.contains(\"role\") | questions_issues_df2[\"labels.name\"].isin(complexity_labels) \n",
    "                                        | questions_issues_df2[\"labels.name\"].isin(extra_breakdown) | questions_issues_df2[\"labels.name\"].str.contains(\"Ready\", case=False))]\n",
    "\n",
    "# Make combined label for issues with front and backend labels\n",
    "questions_wdataset = final_questions[final_questions[\"labels.name\"].str.contains(\"front end\") | final_questions[\"labels.name\"].str.contains(\"back end\")]\n",
    "questions_wdataset[\"front/back end count\"] = questions_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "final_questions.loc[list(questions_wdataset[questions_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "final_questions.drop_duplicates(inplace = True)\n",
    "\n",
    "final_questions2 = final_questions[[\"Runtime\",\"labels.name\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "235d9948-c1ae-4e48-823c-b455e472fcca",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\3777982198.py:86: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  QA_wdataset[\"front/back end count\"] = QA_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\3777982198.py:88: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_QA.loc[list(QA_wdataset[QA_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\3777982198.py:90: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_QA.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "### Get Issue Links in QA Column and Get Issue Data\n",
    "\n",
    "QA_issues = list(QA[~QA['content_url'].isna()]['content_url'])  \n",
    "\n",
    "QA_issues_df = pd.DataFrame()\n",
    "\n",
    "try:\n",
    "    for url in QA_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        QA_issues_df = pd.concat([QA_issues_df, issue_data], ignore_index = True)\n",
    "except ValueError:\n",
    "    time.sleep(3600)\n",
    "    for url in QA_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        QA_issues_df = pd.concat([QA_issues_df, issue_data], ignore_index = True)\n",
    "        \n",
    "# Get the timezone object for New York\n",
    "tz_LA = pytz.timezone('US/Pacific') \n",
    "\n",
    "# Get the current time in New York\n",
    "datetime_LA = datetime.now(tz_LA)\n",
    "\n",
    "# Format the time as a string and add it to Runtime column\n",
    "QA_issues_df[\"Runtime\"] = \"LA time: \"+datetime_LA.strftime(\"%m/%d/%Y %H:%M:%S\")\n",
    "\n",
    "# Drop unneeded columns\n",
    "QA_issues_df.drop(columns = ['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'id',\n",
    "                             'node_id', 'number', 'state', 'locked', 'assignees', 'comments', 'created_at',\n",
    "                             'updated_at', 'closed_at', 'author_association', 'active_lock_reason', 'body', 'closed_by',\n",
    "                             'timeline_url', 'performed_via_github_app', 'state_reason', 'user.login', 'user.id',\n",
    "                             'user.node_id', 'user.avatar_url', 'user.gravatar_id', 'user.url', 'user.html_url', 'user.followers_url',\n",
    "                             'user.following_url', 'user.gists_url', 'user.starred_url', 'user.subscriptions_url',\n",
    "                             'user.organizations_url', 'user.repos_url', 'user.events_url', 'user.received_events_url',\n",
    "                             'user.type', 'user.site_admin', 'milestone.url', 'milestone.html_url', 'milestone.labels_url',\n",
    "                             'milestone.id', 'milestone.node_id', 'milestone.number', 'milestone.title', 'milestone.description',\n",
    "                             'milestone.creator.login', 'milestone.creator.id', 'milestone.creator.node_id', 'milestone.creator.avatar_url',\n",
    "                             'milestone.creator.gravatar_id', 'milestone.creator.url', 'milestone.creator.html_url',\n",
    "                             'milestone.creator.followers_url', 'milestone.creator.following_url', 'milestone.creator.gists_url', 'milestone.creator.starred_url',\n",
    "                             'milestone.creator.subscriptions_url', 'milestone.creator.organizations_url', 'milestone.creator.repos_url',\n",
    "                             'milestone.creator.events_url', 'milestone.creator.received_events_url',\n",
    "                             'milestone.creator.type', 'milestone.creator.site_admin', 'milestone.open_issues', 'milestone.closed_issues',\n",
    "                             'milestone.state', 'milestone.created_at', 'milestone.updated_at', 'milestone.due_on',\n",
    "                             'milestone.closed_at', 'reactions.url', 'reactions.total_count', 'reactions.+1', 'reactions.-1', 'reactions.laugh',\n",
    "                             'reactions.hooray', 'reactions.confused', 'reactions.heart', 'reactions.rocket', 'reactions.eyes',\n",
    "                             'assignee.login', 'assignee.id', 'assignee.node_id', 'assignee.avatar_url', 'assignee.gravatar_id', 'assignee.url',\n",
    "                             'assignee.html_url', 'assignee.followers_url', 'assignee.following_url', 'assignee.gists_url',\n",
    "                             'assignee.starred_url', 'assignee.subscriptions_url', 'assignee.organizations_url',\n",
    "                             'assignee.repos_url', 'assignee.events_url', 'assignee.received_events_url', 'assignee.type',\n",
    "                             'assignee.site_admin'], inplace = True)\n",
    "\n",
    "# Flatten labels column\n",
    "\n",
    "flatten_QA = QA_issues_df.to_json(orient = \"records\")\n",
    "parsed_QA = json.loads(flatten_QA)\n",
    "QA_issues_df2 = pd.json_normalize(parsed_QA, record_path = [\"labels\"], record_prefix = \"labels.\", meta = [\"Runtime\", \"html_url\", \"title\"])\n",
    "\n",
    "QA_issues_df2.drop(columns = ['labels.id', 'labels.node_id', 'labels.url', 'labels.description',\n",
    "       'labels.color', 'labels.default'], inplace = True)\n",
    "\n",
    "# Remove issues with ignore labels in QA column\n",
    "if len([label for label in QA_issues_df2[\"labels.name\"].unique() if re.search('ignore', label.lower())])>0:\n",
    "    remove = list(QA_issues_df2[QA_issues_df2[\"labels.name\"].str.contains(\"gnore\")][\"html_url\"])\n",
    "    QA_issues_df2 = QA_issues_df2[~QA_issues_df2[\"html_url\"].isin(remove)]\n",
    "else:\n",
    "    remove = []\n",
    "\n",
    "# Finishing touches for QA dataset (include issues with no labels)\n",
    "QA_difference = list(set(QA_issues_df[\"html_url\"]).difference(set(QA_issues_df2[\"html_url\"])))\n",
    "QA_no_labels = list(set(QA_difference).difference(set(remove)))\n",
    "QA_no_labels_df = QA_issues_df[QA_issues_df[\"html_url\"].isin(QA_no_labels)][[\"Runtime\", \"html_url\", \"title\"]]\n",
    "QA_no_labels_df[\"labels.name\"] = \"\"\n",
    "QA_no_labels_df = QA_no_labels_df[[\"labels.name\", \"Runtime\", \"html_url\", \"title\"]]\n",
    "\n",
    "QA_issues_df3 = pd.concat([QA_issues_df2, QA_no_labels_df], ignore_index = True)\n",
    "\n",
    "QA_issues_df3[\"Project Board Column\"] = \"7 - QA\"\n",
    "\n",
    "# retain only labels with \"role\" in it or complexity labels, and \"Draft\", \"ready for product\", \"ready for prioritization\", \"ready for dev lead\"\n",
    "final_QA = QA_issues_df2[(QA_issues_df2[\"labels.name\"].str.contains(\"role\") | QA_issues_df2[\"labels.name\"].isin(complexity_labels) | \n",
    "                          QA_issues_df2[\"labels.name\"].isin(extra_breakdown) | QA_issues_df2[\"labels.name\"].str.contains(\"Ready\", case=False))]\n",
    "\n",
    "# Make combined label for issues with front and backend labels\n",
    "QA_wdataset = final_QA[final_QA[\"labels.name\"].str.contains(\"front end\") | final_QA[\"labels.name\"].str.contains(\"back end\")]\n",
    "QA_wdataset[\"front/back end count\"] = QA_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "final_QA.loc[list(QA_wdataset[QA_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "final_QA.drop_duplicates(inplace = True)\n",
    "\n",
    "final_QA2 = final_QA[[\"Runtime\", \"labels.name\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "82b8a4b7-20dc-472e-a1d1-ab8d8c35523c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\890289803.py:86: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  UAT_wdataset[\"front/back end count\"] = UAT_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\890289803.py:88: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_UAT.loc[list(UAT_wdataset[UAT_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\890289803.py:90: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_UAT.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "### Get Issue Links in UAT Column and Get Issue Data\n",
    "\n",
    "UAT_issues = list(UAT[~UAT['content_url'].isna()]['content_url'])\n",
    "\n",
    "UAT_issues_df = pd.DataFrame()\n",
    "\n",
    "try:\n",
    "    for url in UAT_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        UAT_issues_df = pd.concat([UAT_issues_df, issue_data], ignore_index = True)\n",
    "except ValueError:\n",
    "    time.sleep(3600)\n",
    "    for url in UAT_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        UAT_issues_df = pd.concat([UAT_issues_df, issue_data], ignore_index = True)\n",
    "        \n",
    "# Get the timezone object for New York\n",
    "tz_LA = pytz.timezone('US/Pacific') \n",
    "\n",
    "# Get the current time in New York\n",
    "datetime_LA = datetime.now(tz_LA)\n",
    "\n",
    "# Format the time as a string and add it to Runtime column\n",
    "UAT_issues_df[\"Runtime\"] = \"LA time: \"+ datetime_LA.strftime(\"%m/%d/%Y %H:%M:%S\")\n",
    "\n",
    "# Drop unneeded columns\n",
    "UAT_issues_df.drop(columns = ['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'id',\n",
    "                              'node_id', 'number', 'state', 'locked', 'assignees', 'comments', 'created_at',\n",
    "                              'updated_at', 'closed_at', 'author_association', 'active_lock_reason', 'body',\n",
    "                              'timeline_url', 'performed_via_github_app', 'state_reason', 'user.login', 'user.id',\n",
    "                              'user.node_id', 'user.avatar_url', 'user.gravatar_id', 'user.url', 'user.html_url', 'user.followers_url',\n",
    "                              'user.following_url', 'user.gists_url', 'user.starred_url', 'user.subscriptions_url',\n",
    "                              'user.organizations_url', 'user.repos_url', 'user.events_url', 'user.received_events_url',\n",
    "                              'user.type', 'user.site_admin', 'milestone.url', 'milestone.html_url', 'milestone.labels_url',\n",
    "                              'milestone.id', 'milestone.node_id', 'milestone.number', 'milestone.title', 'milestone.description',\n",
    "                              'milestone.creator.login', 'milestone.creator.id', 'milestone.creator.node_id', 'milestone.creator.avatar_url',\n",
    "                              'milestone.creator.gravatar_id', 'milestone.creator.url', 'milestone.creator.html_url',\n",
    "                              'milestone.creator.followers_url', 'milestone.creator.following_url', 'milestone.creator.gists_url', 'milestone.creator.starred_url',\n",
    "                              'milestone.creator.subscriptions_url', 'milestone.creator.organizations_url', 'milestone.creator.repos_url',\n",
    "                              'milestone.creator.events_url', 'milestone.creator.received_events_url',\n",
    "                              'milestone.creator.type', 'milestone.creator.site_admin', 'milestone.open_issues', 'milestone.closed_issues',\n",
    "                              'milestone.state', 'milestone.created_at', 'milestone.updated_at', 'milestone.due_on',\n",
    "                              'milestone.closed_at', 'reactions.url', 'reactions.total_count', 'reactions.+1', 'reactions.-1', 'reactions.laugh',\n",
    "                              'reactions.hooray', 'reactions.confused', 'reactions.heart', 'reactions.rocket', 'reactions.eyes',\n",
    "                              'assignee.login', 'assignee.id', 'assignee.node_id', 'assignee.avatar_url', 'assignee.gravatar_id', 'assignee.url',\n",
    "                              'assignee.html_url', 'assignee.followers_url', 'assignee.following_url', 'assignee.gists_url',\n",
    "                              'assignee.starred_url', 'assignee.subscriptions_url', 'assignee.organizations_url',\n",
    "                              'assignee.repos_url', 'assignee.events_url', 'assignee.received_events_url', 'assignee.type',\n",
    "                              'assignee.site_admin'], inplace = True)\n",
    "\n",
    "# Flatten labels column\n",
    "\n",
    "flatten_UAT = UAT_issues_df.to_json(orient = \"records\")\n",
    "parsed_UAT= json.loads(flatten_UAT)\n",
    "UAT_issues_df2 = pd.json_normalize(parsed_UAT, record_path = [\"labels\"], record_prefix = \"labels.\", meta = [\"Runtime\", \"html_url\", \"title\"])\n",
    "\n",
    "UAT_issues_df2.drop(columns = ['labels.id', 'labels.node_id', 'labels.url', 'labels.description',\n",
    "       'labels.color', 'labels.default'], inplace = True)\n",
    "\n",
    "# Remove issues with ignore labels in UAT column\n",
    "if len([label for label in UAT_issues_df2[\"labels.name\"].unique() if re.search('ignore', label.lower())])>0:\n",
    "    remove = list(UAT_issues_df2[UAT_issues_df2[\"labels.name\"].str.contains(\"gnore\")][\"html_url\"])\n",
    "    UAT_issues_df2 = UAT_issues_df2[~UAT_issues_df2[\"html_url\"].isin(remove)]\n",
    "else:\n",
    "    remove = []\n",
    "\n",
    "# Finishing touches for UAT dataset (include issues with no labels)\n",
    "UAT_difference = list(set(UAT_issues_df[\"html_url\"]).difference(set(UAT_issues_df2[\"html_url\"])))\n",
    "UAT_no_labels = list(set(UAT_difference).difference(set(remove)))\n",
    "UAT_no_labels_df = UAT_issues_df[UAT_issues_df[\"html_url\"].isin(UAT_no_labels)][[\"Runtime\", \"html_url\", \"title\"]]\n",
    "UAT_no_labels_df[\"labels.name\"] = \"\"\n",
    "UAT_no_labels_df = UAT_no_labels_df[[\"labels.name\", \"Runtime\", \"html_url\", \"title\"]]\n",
    "\n",
    "UAT_issues_df3 = pd.concat([UAT_issues_df2, UAT_no_labels_df], ignore_index = True)\n",
    "\n",
    "UAT_issues_df3[\"Project Board Column\"] = \"8 - UAT\"\n",
    "\n",
    "# retain only labels with \"role\" in it or complexity labels, and \"Draft\", \"ready for product\", \"ready for prioritization\", \"ready for dev lead\"\n",
    "final_UAT = UAT_issues_df2[(UAT_issues_df2[\"labels.name\"].str.contains(\"role\") | UAT_issues_df2[\"labels.name\"].isin(complexity_labels) | \n",
    "                          UAT_issues_df2[\"labels.name\"].isin(extra_breakdown) | UAT_issues_df2[\"labels.name\"].str.contains(\"Ready\", case=False))]\n",
    "\n",
    "# Make combined label for issues with front and backend labels\n",
    "UAT_wdataset = final_UAT[final_UAT[\"labels.name\"].str.contains(\"front end\") | final_UAT[\"labels.name\"].str.contains(\"back end\")]\n",
    "UAT_wdataset[\"front/back end count\"] = UAT_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "final_UAT.loc[list(UAT_wdataset[UAT_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "final_UAT.drop_duplicates(inplace = True)\n",
    "\n",
    "final_UAT2 = final_UAT[[\"Runtime\", \"labels.name\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "d9a91403-4ca3-4c27-89bd-7b1f2bb94b32",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\59397888.py:86: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  QA_review_wdataset[\"front/back end count\"] = QA_review_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\59397888.py:88: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_QA_review.loc[list(QA_review_wdataset[QA_review_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\59397888.py:90: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  final_QA_review.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "### Get Issue Links in \"QA - senior review\" Column and Get Issue Data\n",
    "\n",
    "QA_review_issues = list(QA_review[~QA_review['content_url'].isna()]['content_url'])  \n",
    "\n",
    "QA_review_issues_df = pd.DataFrame()\n",
    "\n",
    "try:\n",
    "    for url in QA_review_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        QA_review_issues_df = pd.concat([QA_review_issues_df, issue_data], ignore_index = True)\n",
    "except ValueError:\n",
    "    time.sleep(3600)\n",
    "    for url in QA_review_issues:\n",
    "        response = requests.get(url, auth=(user, GitHub_token))\n",
    "        issue_data = pd.json_normalize(response.json())\n",
    "        QA_review_issues_df = pd.concat([QA_review_issues_df, issue_data], ignore_index = True)\n",
    "        \n",
    "# Get the timezone object for New York\n",
    "tz_LA = pytz.timezone('US/Pacific') \n",
    "\n",
    "# Get the current time in New York\n",
    "datetime_LA = datetime.now(tz_LA)\n",
    "\n",
    "# Format the time as a string and add it to Runtime column\n",
    "QA_review_issues_df[\"Runtime\"] = \"LA time: \"+datetime_LA.strftime(\"%m/%d/%Y %H:%M:%S\")\n",
    "\n",
    "# Drop unneeded columns\n",
    "QA_review_issues_df.drop(columns = ['url', 'repository_url', 'labels_url', 'comments_url', 'events_url', 'id',\n",
    "                                    'node_id', 'number', 'state', 'locked', 'assignees', 'comments', 'created_at',\n",
    "                                    'updated_at', 'closed_at', 'author_association', 'active_lock_reason', 'body', 'closed_by',\n",
    "                                    'timeline_url', 'performed_via_github_app', 'state_reason', 'user.login', 'user.id',\n",
    "                                    'user.node_id', 'user.avatar_url', 'user.gravatar_id', 'user.url', 'user.html_url', 'user.followers_url',\n",
    "                                    'user.following_url', 'user.gists_url', 'user.starred_url', 'user.subscriptions_url',\n",
    "                                    'user.organizations_url', 'user.repos_url', 'user.events_url', 'user.received_events_url',\n",
    "                                    'user.type', 'user.site_admin', 'milestone.url', 'milestone.html_url', 'milestone.labels_url',\n",
    "                                    'milestone.id', 'milestone.node_id', 'milestone.number', 'milestone.title', 'milestone.description',\n",
    "                                    'milestone.creator.login', 'milestone.creator.id', 'milestone.creator.node_id', 'milestone.creator.avatar_url',\n",
    "                                    'milestone.creator.gravatar_id', 'milestone.creator.url', 'milestone.creator.html_url',\n",
    "                                    'milestone.creator.followers_url', 'milestone.creator.following_url', 'milestone.creator.gists_url', 'milestone.creator.starred_url',\n",
    "                                    'milestone.creator.subscriptions_url', 'milestone.creator.organizations_url', 'milestone.creator.repos_url',\n",
    "                                    'milestone.creator.events_url', 'milestone.creator.received_events_url',\n",
    "                                    'milestone.creator.type', 'milestone.creator.site_admin', 'milestone.open_issues', 'milestone.closed_issues',\n",
    "                                    'milestone.state', 'milestone.created_at', 'milestone.updated_at', 'milestone.due_on',\n",
    "                                    'milestone.closed_at', 'reactions.url', 'reactions.total_count', 'reactions.+1', 'reactions.-1', 'reactions.laugh',\n",
    "                                    'reactions.hooray', 'reactions.confused', 'reactions.heart', 'reactions.rocket', 'reactions.eyes',\n",
    "                                    'assignee.login', 'assignee.id', 'assignee.node_id', 'assignee.avatar_url', 'assignee.gravatar_id', 'assignee.url',\n",
    "                                    'assignee.html_url', 'assignee.followers_url', 'assignee.following_url', 'assignee.gists_url',\n",
    "                                    'assignee.starred_url', 'assignee.subscriptions_url', 'assignee.organizations_url',\n",
    "                                    'assignee.repos_url', 'assignee.events_url', 'assignee.received_events_url', 'assignee.type',\n",
    "                                    'assignee.site_admin'], inplace = True)\n",
    "\n",
    "# Flatten labels column\n",
    "\n",
    "flatten_QA_review = QA_review_issues_df.to_json(orient = \"records\")\n",
    "parsed_QA_review= json.loads(flatten_QA_review)\n",
    "QA_review_issues_df2 = pd.json_normalize(parsed_QA_review, record_path = [\"labels\"], record_prefix = \"labels.\", meta = [\"Runtime\", \"html_url\", \"title\"])\n",
    "\n",
    "QA_review_issues_df2.drop(columns = ['labels.id', 'labels.node_id', 'labels.url', 'labels.description',\n",
    "       'labels.color', 'labels.default'], inplace = True)\n",
    "\n",
    "# Remove issues with ignore labels in QA Senior Review column\n",
    "if len([label for label in QA_review_issues_df2[\"labels.name\"].unique() if re.search('ignore', label.lower())])>0:\n",
    "    remove = list(QA_review_issues_df2[QA_review_issues_df2[\"labels.name\"].str.contains(\"gnore\")][\"html_url\"])\n",
    "    QA_review_issues_df2 = QA_review_issues_df2[~QA_review_issues_df2[\"html_url\"].isin(remove)]\n",
    "else: \n",
    "    remove = []\n",
    "    \n",
    "# Finishing touches for QA Senior Review dataset (include issues with no labels)\n",
    "QA_review_difference = list(set(QA_review_issues_df[\"html_url\"]).difference(set(QA_review_issues_df2[\"html_url\"])))\n",
    "QA_review_no_labels = list(set(QA_review_difference).difference(set(remove)))\n",
    "QA_review_no_labels_df = QA_review_issues_df[QA_review_issues_df[\"html_url\"].isin(QA_review_no_labels)][[\"Runtime\", \"html_url\", \"title\"]]\n",
    "QA_review_no_labels_df[\"labels.name\"] = \"\"\n",
    "QA_review_no_labels_df = QA_review_no_labels_df[[\"labels.name\", \"Runtime\", \"html_url\", \"title\"]]\n",
    "\n",
    "QA_review_issues_df3 = pd.concat([QA_review_issues_df2, QA_review_no_labels_df], ignore_index = True)\n",
    "\n",
    "QA_review_issues_df3[\"Project Board Column\"] = \"9 - QA (senior review)\"\n",
    "\n",
    "# retain only labels with \"role\" in it or complexity labels, and \"Draft\", \"ready for product\", \"ready for prioritization\", \"ready for dev lead\"\n",
    "final_QA_review = QA_review_issues_df2[(QA_review_issues_df2[\"labels.name\"].str.contains(\"role\") | QA_review_issues_df2[\"labels.name\"].isin(complexity_labels) | \n",
    "                                        QA_review_issues_df2[\"labels.name\"].isin(extra_breakdown) | QA_review_issues_df2[\"labels.name\"].str.contains(\"Ready\", case=False))]\n",
    "\n",
    "# Make combined label for issues with front and backend labels\n",
    "QA_review_wdataset = final_QA_review[final_QA_review[\"labels.name\"].str.contains(\"front end\") | final_QA_review[\"labels.name\"].str.contains(\"back end\")]\n",
    "QA_review_wdataset[\"front/back end count\"] = QA_review_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "final_QA_review.loc[list(QA_review_wdataset[QA_review_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "final_QA_review.drop_duplicates(inplace = True)\n",
    "\n",
    "final_QA_review2 = final_QA_review[[\"Runtime\", \"labels.name\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "791f990a-8c9c-4604-bf0b-c8866a3f92be",
   "metadata": {},
   "source": [
    "### Create Data Source for Dashboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "833134ad-ae34-47c3-b7f1-b51deb216dc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries to interact with Google Sheets and read spreadsheet with official labels\n",
    "\n",
    "from google.oauth2 import service_account\n",
    "from gspread_dataframe import set_with_dataframe\n",
    "from googleapiclient.discovery import build\n",
    "from pydrive.auth import GoogleAuth\n",
    "from pydrive.drive import GoogleDrive\n",
    "\n",
    "import gspread\n",
    "import base64\n",
    "\n",
    "scopes = ['https://www.googleapis.com/auth/spreadsheets',\n",
    "          'https://www.googleapis.com/auth/drive']\n",
    "\n",
    "## Read in offical GitHub labels from Google spreadsheet for weekly label check table\n",
    "\n",
    "# This is the key for Looker dashboard in 1Password \"website-data science\" Vault: \"Encrypted key for issue-availability-dashboard project\"\n",
    "key_base64 = os.getenv(\"KEY_BASE64\") # replace with key_base64 = os.environ[\"BASE64_PROJECT_BOARD_GOOGLECREDENTIAL\"] for script in live-dashboard-automation repository\n",
    "base64_bytes = key_base64.encode('ascii')\n",
    "key_base64_bytes = base64.b64decode(base64_bytes)\n",
    "key_content = key_base64_bytes.decode('ascii')\n",
    "\n",
    "service_account_info = json.loads(key_content)\n",
    "\n",
    "credentials = service_account.Credentials.from_service_account_info(service_account_info, scopes = scopes)\n",
    "\n",
    "service_sheets = build('sheets', 'v4', credentials = credentials)\n",
    "\n",
    "gc = gspread.authorize(credentials)\n",
    "\n",
    "gauth = GoogleAuth()\n",
    "drive = GoogleDrive(gauth)\n",
    "\n",
    "from gspread_dataframe import set_with_dataframe\n",
    "\n",
    "LabelCheck_GOOGLE_SHEETS_ID = '1-ltg0qMeZSgOnqrCU0nKUDQd1JOXTMWrNTK63VZjXdk'\n",
    "\n",
    "LabelCheck_sheet_name = 'Official GitHub Labels'\n",
    "\n",
    "gs = gc.open_by_key(LabelCheck_GOOGLE_SHEETS_ID)\n",
    "\n",
    "LabelCheck_worksheet = gs.worksheet(LabelCheck_sheet_name)\n",
    "\n",
    "LC_spreadsheet_data = LabelCheck_worksheet.get_all_records()\n",
    "LC_df = pd.DataFrame.from_dict(LC_spreadsheet_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c8e8bef3-dd6e-4aae-9aea-3cdb1b74d126",
   "metadata": {},
   "outputs": [],
   "source": [
    "icebox_role = final_icebox2[final_icebox2[\"labels.name\"].str.contains(\"role\")]\n",
    "icebox_complexity = final_icebox2[final_icebox2[\"labels.name\"].isin(complexity_labels)]\n",
    "\n",
    "icebox_dataset = icebox_role.merge(icebox_complexity, how = \"outer\", on = [\"html_url\", \"title\"])\n",
    "icebox_dataset.rename(columns = {\"labels.name_x\": \"Role Label\", \"labels.name_y\": \"Complexity Label\", \"Runtime_x\":\"Runtime\"}, inplace = True)\n",
    "        \n",
    "icebox_runtime_nulls_loc = icebox_dataset[icebox_dataset[\"Runtime\"].isna()].index\n",
    "icebox_dataset.loc[icebox_runtime_nulls_loc, \"Runtime\"]= icebox_dataset[~icebox_dataset[\"Runtime\"].isna()].iloc[0,0]\n",
    "icebox_dataset.drop(columns = [\"Runtime_y\"], inplace = True)\n",
    "\n",
    "for label in extra_breakdown:\n",
    "    if len(final_icebox2[final_icebox2[\"labels.name\"]==label]) > 0:\n",
    "        icebox_label = final_icebox2[final_icebox2[\"labels.name\"]==label][[\"html_url\", \"title\", \"labels.name\"]]\n",
    "        icebox_dataset = icebox_dataset.merge(icebox_label, how = \"left\", on = [\"html_url\", \"title\"])\n",
    "        icebox_dataset[\"labels.name\"] = icebox_dataset[\"labels.name\"].map(lambda x: 1 if x == label else 0)\n",
    "        icebox_dataset.rename(columns = {\"labels.name\": label}, inplace = True)\n",
    "    elif len(final_icebox2[final_icebox2[\"labels.name\"]==label]) == 0:\n",
    "        icebox_dataset[label] = 0\n",
    "        \n",
    "icebox_dataset[\"Project Board Column\"] = \"1 - Icebox\"\n",
    "\n",
    "icebox_dataset2 = icebox_dataset[[\"Project Board Column\", \"Runtime\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\", \"Draft\", \"2 weeks inactive\", \"ready for product\", \"ready for dev lead\", \"Ready for Prioritization\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "557d4db7-a04d-424b-8ff9-2de46b95b3a5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\3823979780.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  icebox_dataset2[\"Unknown Status\"] = icebox_dataset2[\"html_url\"].map(lambda x: 0 if x in icebox_known_status_issues else 1)\n"
     ]
    }
   ],
   "source": [
    "# Create a column to identify issues with unknown status\n",
    "icebox_unknown_status_wdataset = icebox_issues_df2.copy()\n",
    "icebox_unknown_status_wdataset[\"Known Status\"] = icebox_unknown_status_wdataset[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(ready|draft|^dependency$)\", str(x).lower())) else 0)\n",
    "icebox_known_status_issues = list(icebox_unknown_status_wdataset[icebox_unknown_status_wdataset[\"Known Status\"] == 1][\"html_url\"].unique())\n",
    "icebox_dataset2[\"Unknown Status\"] = icebox_dataset2[\"html_url\"].map(lambda x: 0 if x in icebox_known_status_issues else 1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "134a29d9-d6f4-4d2e-a624-8b68afda52c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create nested dictionary for static links\n",
    "\n",
    "icebox_unique_roles = [x for x in icebox_dataset2[\"Role Label\"].unique() if pd.isna(x) == False]\n",
    "icebox_unique_roles2 = [x for x in icebox_unique_roles if x != \"role: front end and backend/DevOps\"]\n",
    "icebox_unique_complexity = [x for x in icebox_dataset2[\"Complexity Label\"].unique() if pd.isna(x) == False]  \n",
    "static_link_base_icebox = 'https://github.com/hackforla/website/projects/7?card_filter_query=-label%3Adraft%22+-label%3A%22dependency%22'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "c9c294d6-a357-4564-995c-4d2e50801bfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transform all ready series labels and add them to the status link\n",
    "ready_labels = list(LC_df[LC_df[\"label_series\"] == \"ready\"][\"label_name\"].unique())\n",
    "\n",
    "ready_labels_append = \"\"\n",
    "for label in ready_labels:\n",
    "    ready_labels_transformed = label.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    ready_labels_append = ready_labels_append+\"+-label%3A%22\"+ ready_labels_transformed+\"%22\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "fb2c6255-7f6f-43b7-bfda-7ecf8dd4156d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transform all ignore series labels and add them to the status link\n",
    "ignore_labels = list(LC_df[LC_df[\"label_series\"] == \"ignore\"][\"label_name\"].unique())\n",
    "\n",
    "ignore_labels_append = \"\"\n",
    "for label in ignore_labels:\n",
    "    ignore_labels_transformed = label.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    ignore_labels_append = ignore_labels_append+\"+-label%3A%22\"+ ignore_labels_transformed+\"%22\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "da9cb9c4-cccb-4499-a891-c9a3a215edce",
   "metadata": {},
   "outputs": [],
   "source": [
    "static_link_base_icebox = static_link_base_icebox + ready_labels_append + ignore_labels_append"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "c28a805a-3df8-405c-bd62-b6245918292e",
   "metadata": {},
   "outputs": [],
   "source": [
    "icebox_link_dict = { }\n",
    "\n",
    "for role in icebox_unique_roles:\n",
    "    icebox_link_dict[role] = {}\n",
    "    for complexity in icebox_unique_complexity:\n",
    "        role_transformed = role.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        icebox_link_dict[role][complexity] = static_link_base_icebox+\"+label%3A%22\"+role_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "icebox_link_dict[\"role: front end and backend/DevOps\"] = {}     \n",
    "for complexity in icebox_unique_complexity:\n",
    "    frontend_transformed = \"role: front end\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    backend_transformed = \"role: back end/devOps\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    icebox_link_dict[\"role: front end and backend/DevOps\"][complexity] = static_link_base_icebox+\"+label%3A%22\"+frontend_transformed+\"%22\"+\"+label%3A%22\"+backend_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "a8330cab-c07e-4e59-8abe-ca6414ee41f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_38200\\3870073083.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  icebox_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n"
     ]
    }
   ],
   "source": [
    "icebox_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n",
    "for role in icebox_link_dict.keys():\n",
    "    df = icebox_dataset2[icebox_dataset2[\"Role Label\"] == role]\n",
    "    for complexity in icebox_link_dict[list(icebox_link_dict.keys())[0]].keys(): #same for all roles\n",
    "        df2 = df[df[\"Complexity Label\"] == complexity]\n",
    "        indexes = df2.index\n",
    "        icebox_dataset2.loc[indexes, \"Role-based Link for Unknown Status\"] = icebox_link_dict[role][complexity]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "ed56f3aa-c749-461a-a3c1-43adc7136596",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Link for unknown status for all other columns\n",
    "static_link_base = 'https://github.com/hackforla/website/projects/7?card_filter_query=-label%3Adraft' + ready_labels_append + ignore_labels_append"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "fbaada03-120c-40bd-8931-62135d5311f9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3733201623.py:31: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  ER_dataset2[\"Unknown Status\"] = ER_dataset2[\"html_url\"].map(lambda x: 0 if x in ER_known_status_issues else 1)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3733201623.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  ER_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n"
     ]
    }
   ],
   "source": [
    "# Emergent Request\n",
    "\n",
    "ER_role = final_ER2[final_ER2[\"labels.name\"].str.contains(\"role\")]\n",
    "ER_complexity = final_ER2[final_ER2[\"labels.name\"].isin(complexity_labels)]\n",
    "\n",
    "ER_dataset = ER_role.merge(ER_complexity, how = \"outer\", on = [\"html_url\", \"title\"])\n",
    "ER_dataset.rename(columns = {\"labels.name_x\": \"Role Label\", \"labels.name_y\": \"Complexity Label\", \"Runtime_x\":\"Runtime\"}, inplace = True)\n",
    "\n",
    "ER_runtime_nulls_loc = ER_dataset[ER_dataset[\"Runtime\"].isna()].index\n",
    "ER_dataset.loc[ER_runtime_nulls_loc, \"Runtime\"]= ER_dataset[~ER_dataset[\"Runtime\"].isna()].iloc[0,0]\n",
    "ER_dataset.drop(columns = [\"Runtime_y\"], inplace = True)\n",
    "\n",
    "for label in extra_breakdown:\n",
    "    if len(final_ER2[final_ER2[\"labels.name\"]==label]) > 0:\n",
    "        ER_label = final_ER2[final_ER2[\"labels.name\"]==label][[\"html_url\", \"title\", \"labels.name\"]]\n",
    "        ER_dataset = ER_dataset.merge(ER_label, how = \"left\", on = [\"html_url\", \"title\"])\n",
    "        ER_dataset[\"labels.name\"] = ER_dataset[\"labels.name\"].map(lambda x: 1 if x == label else 0)\n",
    "        ER_dataset.rename(columns = {\"labels.name\": label}, inplace = True)\n",
    "    elif len(final_ER2[final_ER2[\"labels.name\"]==label]) == 0:\n",
    "        ER_dataset[label] = 0\n",
    "        \n",
    "ER_dataset[\"Project Board Column\"] = \"2 - ER\"\n",
    "\n",
    "# reoder the columns\n",
    "ER_dataset2 = ER_dataset[[\"Project Board Column\", \"Runtime\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\", \"Draft\", \"2 weeks inactive\", \"ready for product\", \"ready for dev lead\", \"Ready for Prioritization\"]]\n",
    "\n",
    "# Create a column to identify issues with unknown status\n",
    "ER_unknown_status_wdataset = ER_issues_df2.copy()\n",
    "ER_unknown_status_wdataset[\"Known Status\"] = ER_unknown_status_wdataset[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(ready|draft)\", str(x).lower())) else 0)\n",
    "ER_known_status_issues = list(ER_unknown_status_wdataset[ER_unknown_status_wdataset[\"Known Status\"] == 1][\"html_url\"].unique())\n",
    "ER_dataset2[\"Unknown Status\"] = ER_dataset2[\"html_url\"].map(lambda x: 0 if x in ER_known_status_issues else 1) \n",
    "\n",
    "# Create nested dictionary for static links\n",
    "\n",
    "ER_unique_roles = [x for x in ER_dataset2[\"Role Label\"].unique() if pd.isna(x) == False]\n",
    "ER_unique_roles2 = [x for x in ER_unique_roles if x != \"role: front end and backend/DevOps\"]\n",
    "ER_unique_complexity = [x for x in ER_dataset2[\"Complexity Label\"].unique() if pd.isna(x) == False]\n",
    "\n",
    "ER_link_dict = { }\n",
    "\n",
    "for role in ER_unique_roles:\n",
    "    ER_link_dict[role] = {}\n",
    "    for complexity in ER_unique_complexity:\n",
    "        role_transformed = role.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        ER_link_dict[role][complexity] = static_link_base+\"+label%3A%22\"+role_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "ER_link_dict[\"role: front end and backend/DevOps\"] = {}     \n",
    "for complexity in ER_unique_complexity:\n",
    "    frontend_transformed = \"role: front end\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    backend_transformed = \"role: back end/devOps\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    ER_link_dict[\"role: front end and backend/DevOps\"][complexity] = static_link_base+\"+label%3A%22\"+frontend_transformed+\"%22\"+\"+label%3A%22\"+backend_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "ER_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n",
    "for role in ER_link_dict.keys():\n",
    "    df = ER_dataset2[ER_dataset2[\"Role Label\"] == role]\n",
    "    for complexity in ER_link_dict[list(ER_link_dict.keys())[0]].keys(): #same for all roles\n",
    "        df2 = df[df[\"Complexity Label\"] == complexity]\n",
    "        indexes = df2.index\n",
    "        ER_dataset2.loc[indexes, \"Role-based Link for Unknown Status\"] = ER_link_dict[role][complexity]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "4b4a2232-6158-4e0b-aa51-9e04ddb566a4",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3708740151.py:31: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  NIA_dataset2[\"Unknown Status\"] = NIA_dataset2[\"html_url\"].map(lambda x: 0 if x in NIA_known_status_issues else 1)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3708740151.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  NIA_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n"
     ]
    }
   ],
   "source": [
    "# New Issue Approval\n",
    "    \n",
    "NIA_role = final_NIA2[final_NIA2[\"labels.name\"].str.contains(\"role\")]\n",
    "NIA_complexity = final_NIA2[final_NIA2[\"labels.name\"].isin(complexity_labels)]\n",
    "\n",
    "# Join the datasets for data source\n",
    "NIA_dataset = NIA_role.merge(NIA_complexity, how = \"outer\", on = [\"html_url\", \"title\"])\n",
    "NIA_dataset.rename(columns = {\"labels.name_x\": \"Role Label\", \"labels.name_y\": \"Complexity Label\", \"Runtime_x\":\"Runtime\"}, inplace = True)\n",
    "\n",
    "NIA_runtime_nulls_loc = NIA_dataset[NIA_dataset[\"Runtime\"].isna()].index\n",
    "NIA_dataset.loc[NIA_runtime_nulls_loc, \"Runtime\"]= NIA_dataset[~NIA_dataset[\"Runtime\"].isna()].iloc[0,0]\n",
    "NIA_dataset.drop(columns = [\"Runtime_y\"], inplace = True)\n",
    "\n",
    "for label in extra_breakdown:\n",
    "    if len(final_NIA2[final_NIA2[\"labels.name\"]==label]) > 0:\n",
    "        NIA_label = final_NIA2[final_NIA2[\"labels.name\"]==label][[\"html_url\", \"title\", \"labels.name\"]]\n",
    "        NIA_dataset = NIA_dataset.merge(NIA_label, how = \"left\", on = [\"html_url\", \"title\"])\n",
    "        NIA_dataset[\"labels.name\"] = NIA_dataset[\"labels.name\"].map(lambda x: 1 if x == label else 0)\n",
    "        NIA_dataset.rename(columns = {\"labels.name\": label}, inplace = True)\n",
    "    elif len(final_NIA2[final_NIA2[\"labels.name\"]==label]) == 0:\n",
    "        NIA_dataset[label] = 0\n",
    "\n",
    "NIA_dataset[\"Project Board Column\"] = \"3 - New Issue Approval\"\n",
    "\n",
    "NIA_dataset2 = NIA_dataset[[\"Project Board Column\", \"Runtime\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\", \"Draft\", \"2 weeks inactive\", \"ready for product\", \"ready for dev lead\", \"Ready for Prioritization\"]]\n",
    "\n",
    "# Create a column to identify issues with unknown status\n",
    "NIA_unknown_status_wdataset = NIA_issues_df2.copy()\n",
    "NIA_unknown_status_wdataset[\"Known Status\"] = NIA_unknown_status_wdataset[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(ready|draft)\", str(x).lower())) else 0)\n",
    "NIA_known_status_issues = list(NIA_unknown_status_wdataset[NIA_unknown_status_wdataset[\"Known Status\"] == 1][\"html_url\"].unique())\n",
    "NIA_dataset2[\"Unknown Status\"] = NIA_dataset2[\"html_url\"].map(lambda x: 0 if x in NIA_known_status_issues else 1) \n",
    "\n",
    "# Create nested dictionary for static links\n",
    "\n",
    "NIA_unique_roles = [x for x in NIA_dataset2[\"Role Label\"].unique() if pd.isna(x) == False]\n",
    "NIA_unique_roles2 = [x for x in NIA_unique_roles if x != \"role: front end and backend/DevOps\"]\n",
    "NIA_unique_complexity = [x for x in NIA_dataset2[\"Complexity Label\"].unique() if pd.isna(x) == False]\n",
    "\n",
    "NIA_link_dict = { }\n",
    "\n",
    "for role in NIA_unique_roles:\n",
    "    NIA_link_dict[role] = {}\n",
    "    for complexity in NIA_unique_complexity:\n",
    "        role_transformed = role.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        NIA_link_dict[role][complexity] = static_link_base+\"+label%3A%22\"+role_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "NIA_link_dict[\"role: front end and backend/DevOps\"] = {}     \n",
    "for complexity in NIA_unique_complexity:\n",
    "    frontend_transformed = \"role: front end\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    backend_transformed = \"role: back end/devOps\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    NIA_link_dict[\"role: front end and backend/DevOps\"][complexity] = static_link_base+\"+label%3A%22\"+frontend_transformed+\"%22\"+\"+label%3A%22\"+backend_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "    \n",
    "NIA_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n",
    "for role in NIA_link_dict.keys():\n",
    "    df = NIA_dataset2[NIA_dataset2[\"Role Label\"] == role]\n",
    "    for complexity in NIA_link_dict[list(NIA_link_dict.keys())[0]].keys(): #same for all roles\n",
    "        df2 = df[df[\"Complexity Label\"] == complexity]\n",
    "        indexes = df2.index\n",
    "        NIA_dataset2.loc[indexes, \"Role-based Link for Unknown Status\"] = NIA_link_dict[role][complexity]\n",
    "\n",
    "# NIA_dataset2[\"General Link for Developer Unknown Status\"] = \"https://github.com/hackforla/website/projects/7?card_filter_query=-label%3A%22role%3A+user+research%22+-label%3A%22role%3A+product%22+-label%3A%22ready+for+prioritization%22+-label%3Adraft+-label%3A%22ready+for+dev+lead%22+-label%3A%22ready+for+product%22+-label%3A%22role%3A+design%22+-label%3A%22role%3A+research+lead%22+-label%3A%22role%3A+writing%22+-label%3A%22ready+for+design+lead%22+-label%3A%22ready+for+org+rep%22+-label%3A%22role%3A+data+analyst%22\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "c79e4c64-a257-47bd-a331-c85c4ed13c4a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3326490167.py:30: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  pb_dataset2[\"Unknown Status\"] = pb_dataset2[\"html_url\"].map(lambda x: 0 if x in pb_known_status_issues else 1)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3326490167.py:54: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  pb_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n"
     ]
    }
   ],
   "source": [
    "# Prioritized Backlog\n",
    "\n",
    "pb_role = final_pb2[final_pb2[\"labels.name\"].str.contains(\"role\")]\n",
    "pb_complexity = final_pb2[final_pb2[\"labels.name\"].isin(complexity_labels)]\n",
    "\n",
    "pb_dataset = pb_role.merge(pb_complexity, how = \"outer\", on = [\"html_url\", \"title\"])\n",
    "pb_dataset.rename(columns = {\"labels.name_x\": \"Role Label\", \"labels.name_y\": \"Complexity Label\", \"Runtime_x\":\"Runtime\"}, inplace = True)\n",
    "\n",
    "pb_runtime_nulls_loc = pb_dataset[pb_dataset[\"Runtime\"].isna()].index\n",
    "pb_dataset.loc[pb_runtime_nulls_loc, \"Runtime\"]= pb_dataset[~pb_dataset[\"Runtime\"].isna()].iloc[0,0]\n",
    "pb_dataset.drop(columns = [\"Runtime_y\"], inplace = True)\n",
    "\n",
    "for label in extra_breakdown:\n",
    "    if len(final_pb2[final_pb2[\"labels.name\"]==label]) > 0:\n",
    "        pb_label = final_pb2[final_pb2[\"labels.name\"]==label][[\"html_url\", \"title\", \"labels.name\"]]\n",
    "        pb_dataset = pb_dataset.merge(pb_label, how = \"left\", on = [\"html_url\", \"title\"])\n",
    "        pb_dataset[\"labels.name\"] = pb_dataset[\"labels.name\"].map(lambda x: 1 if x == label else 0)\n",
    "        pb_dataset.rename(columns = {\"labels.name\": label}, inplace = True)\n",
    "    elif len(final_pb2[final_pb2[\"labels.name\"]==label]) == 0:\n",
    "        pb_dataset[label] = 0\n",
    "        \n",
    "pb_dataset[\"Project Board Column\"] = \"4 - Prioritized Backlog\"\n",
    "\n",
    "pb_dataset2 = pb_dataset[[\"Project Board Column\", \"Runtime\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\", \"Draft\", \"2 weeks inactive\", \"ready for product\", \"ready for dev lead\", \"Ready for Prioritization\"]]\n",
    "\n",
    "# Create a column to identify issues with unknown status\n",
    "pb_unknown_status_wdataset = pb_issues_df2.copy()\n",
    "pb_unknown_status_wdataset[\"Known Status\"] = pb_unknown_status_wdataset[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(ready|draft)\", str(x).lower())) else 0)\n",
    "pb_known_status_issues = list(pb_unknown_status_wdataset[pb_unknown_status_wdataset[\"Known Status\"] == 1][\"html_url\"].unique())\n",
    "pb_dataset2[\"Unknown Status\"] = pb_dataset2[\"html_url\"].map(lambda x: 0 if x in pb_known_status_issues else 1) \n",
    "\n",
    "# Create nested dictionary for static links\n",
    "\n",
    "pb_unique_roles = [x for x in pb_dataset2[\"Role Label\"].unique() if pd.isna(x) == False]\n",
    "pb_unique_roles2 = [x for x in pb_unique_roles if x != \"role: front end and backend/DevOps\"]\n",
    "pb_unique_complexity = [x for x in pb_dataset2[\"Complexity Label\"].unique() if pd.isna(x) == False]\n",
    "\n",
    "pb_link_dict = { }\n",
    "\n",
    "for role in pb_unique_roles:\n",
    "    pb_link_dict[role] = {}\n",
    "    for complexity in pb_unique_complexity:\n",
    "        role_transformed = role.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        pb_link_dict[role][complexity] = static_link_base+\"+label%3A%22\"+role_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "pb_link_dict[\"role: front end and backend/DevOps\"] = {}     \n",
    "for complexity in pb_unique_complexity:\n",
    "    frontend_transformed = \"role: front end\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    backend_transformed = \"role: back end/devOps\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    pb_link_dict[\"role: front end and backend/DevOps\"][complexity] = static_link_base+\"+label%3A%22\"+frontend_transformed+\"%22\"+\"+label%3A%22\"+backend_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "    \n",
    "pb_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n",
    "for role in pb_link_dict.keys():\n",
    "    df = pb_dataset2[pb_dataset2[\"Role Label\"] == role]\n",
    "    for complexity in pb_link_dict[list(pb_link_dict.keys())[0]].keys(): #same for all roles\n",
    "        df2 = df[df[\"Complexity Label\"] == complexity]\n",
    "        indexes = df2.index\n",
    "        pb_dataset2.loc[indexes, \"Role-based Link for Unknown Status\"] = pb_link_dict[role][complexity]\n",
    "\n",
    "# pb_dataset2[\"General Link for Developer Unknown Status\"] = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "a1fae8a0-69d1-4d2d-9dd6-69b2a39d326d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\2306461390.py:31: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  IP_dataset2[\"Unknown Status\"] = IP_dataset2[\"html_url\"].map(lambda x: 0 if x in IP_known_status_issues else 1)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\2306461390.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  IP_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n"
     ]
    }
   ],
   "source": [
    "# In Progress (actively working)\n",
    "\n",
    "IP_role = final_ip2[final_ip2[\"labels.name\"].str.contains(\"role\")]\n",
    "IP_complexity = final_ip2[final_ip2[\"labels.name\"].isin(complexity_labels)]\n",
    "\n",
    "IP_dataset = IP_role.merge(IP_complexity, how = \"outer\", on = [\"html_url\", \"title\"])\n",
    "IP_dataset.rename(columns = {\"labels.name_x\": \"Role Label\", \"labels.name_y\": \"Complexity Label\", \"Runtime_x\":\"Runtime\"}, inplace = True)\n",
    "\n",
    "IP_runtime_nulls_loc = IP_dataset[IP_dataset[\"Runtime\"].isna()].index\n",
    "IP_dataset.loc[IP_runtime_nulls_loc, \"Runtime\"]= IP_dataset[~IP_dataset[\"Runtime\"].isna()].iloc[0,0]\n",
    "IP_dataset.drop(columns = [\"Runtime_y\"], inplace = True)\n",
    "\n",
    "for label in extra_breakdown:\n",
    "    if len(final_ip2[final_ip2[\"labels.name\"]==label]) > 0:\n",
    "        IP_label = final_ip2[final_ip2[\"labels.name\"]==label][[\"html_url\", \"title\", \"labels.name\"]]\n",
    "        IP_dataset = IP_dataset.merge(IP_label, how = \"left\", on = [\"html_url\", \"title\"])\n",
    "        IP_dataset[\"labels.name\"] = IP_dataset[\"labels.name\"].map(lambda x: 1 if x == label else 0)\n",
    "        IP_dataset.rename(columns = {\"labels.name\": label}, inplace = True)\n",
    "    elif len(final_ip2[final_ip2[\"labels.name\"]==label]) == 0:\n",
    "        IP_dataset[label] = 0\n",
    "\n",
    "IP_dataset[\"Project Board Column\"] = \"5 - In Progress\"\n",
    "\n",
    "# reoder the columns\n",
    "IP_dataset2 = IP_dataset[[\"Project Board Column\", \"Runtime\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\", \"Draft\", \"2 weeks inactive\", \"ready for product\", \"ready for dev lead\", \"Ready for Prioritization\"]]\n",
    "\n",
    "# Create a column to identify issues with unknown status\n",
    "IP_unknown_status_wdataset = ip_df2.copy()\n",
    "IP_unknown_status_wdataset[\"Known Status\"] = IP_unknown_status_wdataset[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(ready|draft)\", str(x).lower())) else 0)\n",
    "IP_known_status_issues = list(IP_unknown_status_wdataset[IP_unknown_status_wdataset[\"Known Status\"] == 1][\"html_url\"].unique())\n",
    "IP_dataset2[\"Unknown Status\"] = IP_dataset2[\"html_url\"].map(lambda x: 0 if x in IP_known_status_issues else 1) \n",
    "\n",
    "# Create nested dictionary for static links\n",
    "\n",
    "IP_unique_roles = [x for x in IP_dataset2[\"Role Label\"].unique() if pd.isna(x) == False]\n",
    "IP_unique_roles2 = [x for x in IP_unique_roles if x != \"role: front end and backend/DevOps\"]\n",
    "IP_unique_complexity = [x for x in IP_dataset2[\"Complexity Label\"].unique() if pd.isna(x) == False]\n",
    "\n",
    "IP_link_dict = { }\n",
    "\n",
    "for role in IP_unique_roles:\n",
    "    IP_link_dict[role] = {}\n",
    "    for complexity in IP_unique_complexity:\n",
    "        role_transformed = role.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        IP_link_dict[role][complexity] = static_link_base+\"+label%3A%22\"+role_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "IP_link_dict[\"role: front end and backend/DevOps\"] = {}     \n",
    "for complexity in IP_unique_complexity:\n",
    "    frontend_transformed = \"role: front end\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    backend_transformed = \"role: back end/devOps\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    IP_link_dict[\"role: front end and backend/DevOps\"][complexity] = static_link_base+\"+label%3A%22\"+frontend_transformed+\"%22\"+\"+label%3A%22\"+backend_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "    \n",
    "IP_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n",
    "for role in IP_link_dict.keys():\n",
    "    df = IP_dataset2[IP_dataset2[\"Role Label\"] == role]\n",
    "    for complexity in IP_link_dict[list(IP_link_dict.keys())[0]].keys(): #same for all roles\n",
    "        df2 = df[df[\"Complexity Label\"] == complexity]\n",
    "        indexes = df2.index\n",
    "        IP_dataset2.loc[indexes, \"Role-based Link for Unknown Status\"] = IP_link_dict[role][complexity]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "b5bc4766-6632-4331-9ba0-f8feb4e0a83d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3520498927.py:33: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  Q_dataset2[\"Unknown Status\"] = Q_dataset2[\"html_url\"].map(lambda x: 0 if x in Q_known_status_issues else 1)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3520498927.py:57: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  Q_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n"
     ]
    }
   ],
   "source": [
    "# Questions / In Review\n",
    "\n",
    "Q_role = final_questions2[final_questions2[\"labels.name\"].str.contains(\"role\")]\n",
    "Q_complexity = final_questions2[final_questions2[\"labels.name\"].isin(complexity_labels)]\n",
    "\n",
    "Q_dataset = Q_role.merge(Q_complexity, how = \"outer\", on = [\"html_url\", \"title\"])\n",
    "Q_dataset.rename(columns = {\"labels.name_x\": \"Role Label\", \"labels.name_y\": \"Complexity Label\", \"Runtime_x\":\"Runtime\"}, inplace = True)\n",
    "\n",
    "Q_runtime_nulls_loc = Q_dataset[Q_dataset[\"Runtime\"].isna()].index\n",
    "Q_dataset.loc[Q_runtime_nulls_loc, \"Runtime\"]= Q_dataset[~Q_dataset[\"Runtime\"].isna()].iloc[0,0]\n",
    "Q_dataset.drop(columns = [\"Runtime_y\"], inplace = True)\n",
    "\n",
    "for label in extra_breakdown:\n",
    "    if len(final_questions2[final_questions2[\"labels.name\"]==label]) > 0:\n",
    "        Q_label = final_questions2[final_questions2[\"labels.name\"]==label][[\"html_url\", \"title\", \"labels.name\"]]\n",
    "        Q_dataset = Q_dataset.merge(Q_label, how = \"left\", on = [\"html_url\", \"title\"])\n",
    "        Q_dataset[\"labels.name\"] = Q_dataset[\"labels.name\"].map(lambda x: 1 if x == label else 0)\n",
    "        Q_dataset.rename(columns = {\"labels.name\": label}, inplace = True)\n",
    "    elif len(final_questions2[final_questions2[\"labels.name\"]==label]) == 0:\n",
    "        Q_dataset[label] = 0\n",
    "        \n",
    "Q_dataset[\"Project Board Column\"] = \"6 - Questions/ In Review\"\n",
    "\n",
    "# reoder the columns\n",
    "Q_dataset2 = Q_dataset[[\"Project Board Column\", \"Runtime\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\", \"Draft\", \"2 weeks inactive\", \"ready for product\", \"ready for dev lead\", \"Ready for Prioritization\"]]\n",
    "\n",
    "# Add in unknown status columns\n",
    "\n",
    "# Create a column to identify issues with unknown status\n",
    "Q_unknown_status_wdataset = questions_issues_df2.copy()\n",
    "Q_unknown_status_wdataset[\"Known Status\"] = Q_unknown_status_wdataset[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(ready|draft)\", str(x).lower())) else 0)\n",
    "Q_known_status_issues = list(Q_unknown_status_wdataset[Q_unknown_status_wdataset[\"Known Status\"] == 1][\"html_url\"].unique())\n",
    "Q_dataset2[\"Unknown Status\"] = Q_dataset2[\"html_url\"].map(lambda x: 0 if x in Q_known_status_issues else 1) \n",
    "\n",
    "# Create nested dictionary for static links\n",
    "\n",
    "Q_unique_roles = [x for x in Q_dataset2[\"Role Label\"].unique() if pd.isna(x) == False]\n",
    "Q_unique_roles2 = [x for x in Q_unique_roles if x != \"role: front end and backend/DevOps\"]\n",
    "Q_unique_complexity = [x for x in Q_dataset2[\"Complexity Label\"].unique() if pd.isna(x) == False]\n",
    "\n",
    "Q_link_dict = { }\n",
    "\n",
    "for role in Q_unique_roles:\n",
    "    Q_link_dict[role] = {}\n",
    "    for complexity in Q_unique_complexity:\n",
    "        role_transformed = role.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        Q_link_dict[role][complexity] = static_link_base+\"+label%3A%22\"+role_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "Q_link_dict[\"role: front end and backend/DevOps\"] = {}     \n",
    "for complexity in Q_unique_complexity:\n",
    "    frontend_transformed = \"role: front end\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    backend_transformed = \"role: back end/devOps\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    Q_link_dict[\"role: front end and backend/DevOps\"][complexity] = static_link_base+\"+label%3A%22\"+frontend_transformed+\"%22\"+\"+label%3A%22\"+backend_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "    \n",
    "Q_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n",
    "for role in Q_link_dict.keys():\n",
    "    df = Q_dataset2[Q_dataset2[\"Role Label\"] == role]\n",
    "    for complexity in Q_link_dict[list(Q_link_dict.keys())[0]].keys(): #same for all roles\n",
    "        df2 = df[df[\"Complexity Label\"] == complexity]\n",
    "        indexes = df2.index\n",
    "        Q_dataset2.loc[indexes, \"Role-based Link for Unknown Status\"] = Q_link_dict[role][complexity]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "9822705f-4641-45f9-9b15-cbf546a0a2db",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\877238546.py:33: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  QA_dataset2[\"Unknown Status\"] = QA_dataset2[\"html_url\"].map(lambda x: 0 if x in QA_known_status_issues else 1)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\877238546.py:57: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  QA_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n"
     ]
    }
   ],
   "source": [
    "# QA\n",
    "\n",
    "QA_role = final_QA2[final_QA2[\"labels.name\"].str.contains(\"role\")]\n",
    "QA_complexity = final_QA2[final_QA2[\"labels.name\"].isin(complexity_labels)]\n",
    "\n",
    "QA_dataset = QA_role.merge(QA_complexity, how = \"outer\", on = [\"html_url\", \"title\"])\n",
    "QA_dataset.rename(columns = {\"labels.name_x\": \"Role Label\", \"labels.name_y\": \"Complexity Label\", \"Runtime_x\":\"Runtime\"}, inplace = True)\n",
    "\n",
    "QA_runtime_nulls_loc = QA_dataset[QA_dataset[\"Runtime\"].isna()].index\n",
    "QA_dataset.loc[QA_runtime_nulls_loc, \"Runtime\"]= QA_dataset[~QA_dataset[\"Runtime\"].isna()].iloc[0,0]\n",
    "QA_dataset.drop(columns = [\"Runtime_y\"], inplace = True)\n",
    "\n",
    "for label in extra_breakdown:\n",
    "    if len(final_QA2[final_QA2[\"labels.name\"]==label]) > 0:\n",
    "        QA_label = final_QA2[final_QA2[\"labels.name\"]==label][[\"html_url\", \"title\", \"labels.name\"]]\n",
    "        QA_dataset = QA_dataset.merge(QA_label, how = \"left\", on = [\"html_url\", \"title\"])\n",
    "        QA_dataset[\"labels.name\"] = QA_dataset[\"labels.name\"].map(lambda x: 1 if x == label else 0)\n",
    "        QA_dataset.rename(columns = {\"labels.name\": label}, inplace = True)\n",
    "    elif len(final_QA2[final_QA2[\"labels.name\"]==label]) == 0:\n",
    "        QA_dataset[label] = 0\n",
    "        \n",
    "QA_dataset[\"Project Board Column\"] = \"7 - QA\"\n",
    "\n",
    "# reoder the columns\n",
    "QA_dataset2 = QA_dataset[[\"Project Board Column\", \"Runtime\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\", \"Draft\", \"2 weeks inactive\", \"ready for product\", \"ready for dev lead\", \"Ready for Prioritization\"]]\n",
    "\n",
    "# Add in unknown status columns\n",
    "\n",
    "# Create a column to identify issues with unknown status\n",
    "QA_unknown_status_wdataset = QA_issues_df2.copy()\n",
    "QA_unknown_status_wdataset[\"Known Status\"] = QA_unknown_status_wdataset[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(ready|draft)\", str(x).lower())) else 0)\n",
    "QA_known_status_issues = list(QA_unknown_status_wdataset[QA_unknown_status_wdataset[\"Known Status\"] == 1][\"html_url\"].unique())\n",
    "QA_dataset2[\"Unknown Status\"] = QA_dataset2[\"html_url\"].map(lambda x: 0 if x in QA_known_status_issues else 1) \n",
    "\n",
    "# Create nested dictionary for static links\n",
    "\n",
    "QA_unique_roles = [x for x in QA_dataset2[\"Role Label\"].unique() if pd.isna(x) == False]\n",
    "QA_unique_roles2 = [x for x in QA_unique_roles if x != \"role: front end and backend/DevOps\"]\n",
    "QA_unique_complexity = [x for x in QA_dataset2[\"Complexity Label\"].unique() if pd.isna(x) == False]\n",
    "\n",
    "QA_link_dict = { }\n",
    "\n",
    "for role in QA_unique_roles:\n",
    "    QA_link_dict[role] = {}\n",
    "    for complexity in QA_unique_complexity:\n",
    "        role_transformed = role.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        QA_link_dict[role][complexity] = static_link_base+\"+label%3A%22\"+role_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "QA_link_dict[\"role: front end and backend/DevOps\"] = {}     \n",
    "for complexity in QA_unique_complexity:\n",
    "    frontend_transformed = \"role: front end\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    backend_transformed = \"role: back end/devOps\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    QA_link_dict[\"role: front end and backend/DevOps\"][complexity] = static_link_base+\"+label%3A%22\"+frontend_transformed+\"%22\"+\"+label%3A%22\"+backend_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "    \n",
    "QA_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n",
    "for role in QA_link_dict.keys():\n",
    "    df = QA_dataset2[QA_dataset2[\"Role Label\"] == role]\n",
    "    for complexity in QA_link_dict[list(QA_link_dict.keys())[0]].keys(): #same for all roles\n",
    "        df2 = df[df[\"Complexity Label\"] == complexity]\n",
    "        indexes = df2.index\n",
    "        QA_dataset2.loc[indexes, \"Role-based Link for Unknown Status\"] = QA_link_dict[role][complexity]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "cbce978a-a024-4f64-af2c-40916c46c8db",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\4025071907.py:32: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  UAT_dataset2[\"Unknown Status\"] = UAT_dataset2[\"html_url\"].map(lambda x: 0 if x in UAT_known_status_issues else 1)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\4025071907.py:56: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  UAT_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n"
     ]
    }
   ],
   "source": [
    "# UAT\n",
    "\n",
    "UAT_role = final_UAT2[final_UAT2[\"labels.name\"].str.contains(\"role\")]\n",
    "UAT_complexity = final_UAT2[final_UAT2[\"labels.name\"].isin(complexity_labels)]\n",
    "\n",
    "UAT_dataset = UAT_role.merge(UAT_complexity, how = \"outer\", on = [\"html_url\", \"title\"])\n",
    "UAT_dataset.rename(columns = {\"labels.name_x\": \"Role Label\", \"labels.name_y\": \"Complexity Label\", \"Runtime_x\":\"Runtime\"}, inplace = True)\n",
    "\n",
    "UAT_runtime_nulls_loc = UAT_dataset[UAT_dataset[\"Runtime\"].isna()].index\n",
    "UAT_dataset.loc[UAT_runtime_nulls_loc, \"Runtime\"]= UAT_dataset[~UAT_dataset[\"Runtime\"].isna()].iloc[0,0]\n",
    "UAT_dataset.drop(columns = [\"Runtime_y\"], inplace = True)\n",
    "\n",
    "for label in extra_breakdown:\n",
    "    if len(final_UAT2[final_UAT2[\"labels.name\"]==label]) > 0:\n",
    "        UAT_label = final_UAT2[final_UAT2[\"labels.name\"]==label][[\"html_url\", \"title\", \"labels.name\"]]\n",
    "        UAT_dataset = UAT_dataset.merge(UAT_label, how = \"left\", on = [\"html_url\", \"title\"])\n",
    "        UAT_dataset[\"labels.name\"] = UAT_dataset[\"labels.name\"].map(lambda x: 1 if x == label else 0)\n",
    "        UAT_dataset.rename(columns = {\"labels.name\": label}, inplace = True)\n",
    "    elif len(final_UAT2[final_UAT2[\"labels.name\"]==label]) == 0:\n",
    "        UAT_dataset[label] = 0\n",
    "\n",
    "UAT_dataset[\"Project Board Column\"] = \"8 - UAT\"\n",
    "\n",
    "UAT_dataset2 = UAT_dataset[[\"Project Board Column\", \"Runtime\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\", \"Draft\", \"2 weeks inactive\", \"ready for product\", \"ready for dev lead\", \"Ready for Prioritization\"]]\n",
    "\n",
    "# Add in unknown status columns\n",
    "\n",
    "# Create a column to identify issues with unknown status\n",
    "UAT_unknown_status_wdataset = UAT_issues_df2.copy()\n",
    "UAT_unknown_status_wdataset[\"Known Status\"] = UAT_unknown_status_wdataset[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(ready|draft)\", str(x).lower())) else 0)\n",
    "UAT_known_status_issues = list(UAT_unknown_status_wdataset[UAT_unknown_status_wdataset[\"Known Status\"] == 1][\"html_url\"].unique())\n",
    "UAT_dataset2[\"Unknown Status\"] = UAT_dataset2[\"html_url\"].map(lambda x: 0 if x in UAT_known_status_issues else 1) \n",
    "\n",
    "# Create nested dictionary for static links\n",
    "\n",
    "UAT_unique_roles = [x for x in UAT_dataset2[\"Role Label\"].unique() if pd.isna(x) == False]\n",
    "UAT_unique_roles2 = [x for x in UAT_unique_roles if x != \"role: front end and backend/DevOps\"]\n",
    "UAT_unique_complexity = [x for x in UAT_dataset2[\"Complexity Label\"].unique() if pd.isna(x) == False]\n",
    "\n",
    "UAT_link_dict = { }\n",
    "\n",
    "for role in UAT_unique_roles:\n",
    "    UAT_link_dict[role] = {}\n",
    "    for complexity in UAT_unique_complexity:\n",
    "        role_transformed = role.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        UAT_link_dict[role][complexity] = static_link_base+\"+label%3A%22\"+role_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "UAT_link_dict[\"role: front end and backend/DevOps\"] = {}     \n",
    "for complexity in UAT_unique_complexity:\n",
    "    frontend_transformed = \"role: front end\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    backend_transformed = \"role: back end/devOps\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    UAT_link_dict[\"role: front end and backend/DevOps\"][complexity] = static_link_base+\"+label%3A%22\"+frontend_transformed+\"%22\"+\"+label%3A%22\"+backend_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "    \n",
    "UAT_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n",
    "for role in UAT_link_dict.keys():\n",
    "    df = UAT_dataset2[UAT_dataset2[\"Role Label\"] == role]\n",
    "    for complexity in UAT_link_dict[list(UAT_link_dict.keys())[0]].keys(): #same for all roles\n",
    "        df2 = df[df[\"Complexity Label\"] == complexity]\n",
    "        indexes = df2.index\n",
    "        UAT_dataset2.loc[indexes, \"Role-based Link for Unknown Status\"] = UAT_link_dict[role][complexity]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "6091715e-6099-43fa-9347-09b2aac0de5a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\1563255188.py:33: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  QA_review_dataset2[\"Unknown Status\"] = QA_review_dataset2[\"html_url\"].map(lambda x: 0 if x in QA_review_known_status_issues else 1)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\1563255188.py:57: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  QA_review_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n"
     ]
    }
   ],
   "source": [
    "# QA - senior review\n",
    "\n",
    "QA_review_role = final_QA_review2[final_QA_review2[\"labels.name\"].str.contains(\"role\")]\n",
    "QA_review_complexity = final_QA_review2[final_QA_review2[\"labels.name\"].isin(complexity_labels)]\n",
    "\n",
    "QA_review_dataset = QA_review_role.merge(QA_review_complexity, how = \"outer\", on = [\"html_url\", \"title\"])\n",
    "QA_review_dataset.rename(columns = {\"labels.name_x\": \"Role Label\", \"labels.name_y\": \"Complexity Label\", \"Runtime_x\":\"Runtime\"}, inplace = True)\n",
    "\n",
    "QA_review_runtime_nulls_loc = QA_review_dataset[QA_review_dataset[\"Runtime\"].isna()].index\n",
    "QA_review_dataset.loc[QA_review_runtime_nulls_loc, \"Runtime\"]= QA_review_dataset[~QA_review_dataset[\"Runtime\"].isna()].iloc[0,0]\n",
    "QA_review_dataset.drop(columns = [\"Runtime_y\"], inplace = True)\n",
    "\n",
    "for label in extra_breakdown:\n",
    "    if len(final_QA_review2[final_QA_review2[\"labels.name\"]==label]) > 0:\n",
    "        QA_review_label = final_QA_review2[final_QA_review2[\"labels.name\"]==label][[\"html_url\", \"title\", \"labels.name\"]]\n",
    "        QA_review_dataset = QA_review_dataset.merge(QA_review_label, how = \"left\", on = [\"html_url\", \"title\"])\n",
    "        QA_review_dataset[\"labels.name\"] = QA_review_dataset[\"labels.name\"].map(lambda x: 1 if x == label else 0)\n",
    "        QA_review_dataset.rename(columns = {\"labels.name\": label}, inplace = True)\n",
    "    elif len(final_QA_review2[final_QA_review2[\"labels.name\"]==label]) == 0:\n",
    "        QA_review_dataset[label] = 0\n",
    "\n",
    "QA_review_dataset[\"Project Board Column\"] = \"9 - QA (senior review)\"\n",
    "\n",
    "QA_review_dataset2 = QA_review_dataset[[\"Project Board Column\", \"Runtime\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\", \"Draft\", \"2 weeks inactive\", \"ready for product\", \"ready for dev lead\", \"Ready for Prioritization\"]]\n",
    "\n",
    "# Add in unknown status columns\n",
    "\n",
    "\n",
    "# Create a column to identify issues with unknown status\n",
    "QA_review_unknown_status_wdataset = QA_review_issues_df2.copy()\n",
    "QA_review_unknown_status_wdataset[\"Known Status\"] = QA_review_unknown_status_wdataset[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(ready|draft)\", str(x).lower())) else 0)\n",
    "QA_review_known_status_issues = list(QA_review_unknown_status_wdataset[QA_review_unknown_status_wdataset[\"Known Status\"] == 1][\"html_url\"].unique())\n",
    "QA_review_dataset2[\"Unknown Status\"] = QA_review_dataset2[\"html_url\"].map(lambda x: 0 if x in QA_review_known_status_issues else 1) \n",
    "\n",
    "# Create nested dictionary for static links\n",
    "\n",
    "QA_review_unique_roles = [x for x in QA_review_dataset2[\"Role Label\"].unique() if pd.isna(x) == False]\n",
    "QA_review_unique_roles2 = [x for x in QA_review_unique_roles if x != \"role: front end and backend/DevOps\"]\n",
    "QA_review_unique_complexity = [x for x in QA_review_dataset2[\"Complexity Label\"].unique() if pd.isna(x) == False]\n",
    "\n",
    "QA_review_link_dict = { }\n",
    "\n",
    "for role in QA_review_unique_roles:\n",
    "    QA_review_link_dict[role] = {}\n",
    "    for complexity in QA_review_unique_complexity:\n",
    "        role_transformed = role.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "        QA_review_link_dict[role][complexity] = static_link_base+\"+label%3A%22\"+role_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "\n",
    "QA_review_link_dict[\"role: front end and backend/DevOps\"] = {}     \n",
    "for complexity in QA_review_unique_complexity:\n",
    "    frontend_transformed = \"role: front end\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    backend_transformed = \"role: back end/devOps\".lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    complexity_transformed = complexity.lower().replace(\":\", \"%3A\").replace(\" \", \"+\")\n",
    "    QA_review_link_dict[\"role: front end and backend/DevOps\"][complexity] = static_link_base+\"+label%3A%22\"+frontend_transformed+\"%22\"+\"+label%3A%22\"+backend_transformed+\"%22\"+\"+label%3A%22\"+complexity_transformed+\"%22\"\n",
    "    \n",
    "QA_review_dataset2[\"Role-based Link for Unknown Status\"] = \"\"\n",
    "for role in QA_review_link_dict.keys():\n",
    "    df = QA_review_dataset2[QA_review_dataset2[\"Role Label\"] == role]\n",
    "    for complexity in QA_review_link_dict[list(QA_review_link_dict.keys())[0]].keys(): #same for all roles\n",
    "        df2 = df[df[\"Complexity Label\"] == complexity]\n",
    "        indexes = df2.index\n",
    "        QA_review_dataset2.loc[indexes, \"Role-based Link for Unknown Status\"] = QA_review_link_dict[role][complexity]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8bd133d-b7bd-4684-b53c-6a10a0d4fd8a",
   "metadata": {},
   "source": [
    "### Combine Data from All Project Board Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "830646bc-d291-470f-9e1c-89880fdecb24",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concat the dataset and see whether dashboard would work\n",
    "final_dataset = pd.concat([icebox_dataset2, ER_dataset2, NIA_dataset2, pb_dataset2, IP_dataset2, Q_dataset2, QA_dataset2, UAT_dataset2, QA_review_dataset2], ignore_index = True)\n",
    "\n",
    "final_dataset.loc[final_dataset[final_dataset[\"Complexity Label\"] == \"good first issue\"].index, \"Complexity Label\"] = \"1 - good first issue\"\n",
    "final_dataset.loc[final_dataset[final_dataset[\"Complexity Label\"] == \"Complexity: Small\"].index, \"Complexity Label\"] = \"2 - Complexity: Small\"\n",
    "final_dataset.loc[final_dataset[final_dataset[\"Complexity Label\"] == \"Complexity: Medium\"].index, \"Complexity Label\"] = \"3 - Complexity: Medium\"\n",
    "final_dataset.loc[final_dataset[final_dataset[\"Complexity Label\"] == \"Complexity: Large\"].index, \"Complexity Label\"] = \"4 - Complexity: Large\"\n",
    "final_dataset.loc[final_dataset[final_dataset[\"Complexity Label\"] == \"Complexity: Extra Large\"].index, \"Complexity Label\"] = \"5 - Complexity: Extra Large\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c9850fe-44bf-456c-89f8-3b90f11ef0a5",
   "metadata": {},
   "source": [
    "### Create Anomaly Detection Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "d8f1da70-921e-4a91-aaaa-bfc64ed1cc4f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3844707707.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anomaly_detection_df.drop(columns = [\"keep\"], inplace = True)\n"
     ]
    }
   ],
   "source": [
    "# Concat the dataframes from all columns\n",
    "anomaly_detection = pd.concat([icebox_issues_df3, ER_issues_df3, NIA_issues_df3, pb_issues_df3, ip_issues_df3, questions_issues_df3, QA_issues_df3, UAT_issues_df3, QA_review_issues_df3], ignore_index = True)\n",
    "anomaly_detection[\"keep\"] = anomaly_detection[\"labels.name\"].map(lambda x: 1 if (re.search(r\"(size|feature|role|complexity|good first issue|prework|^$)\", str(x).lower())) else 0)\n",
    "anomaly_detection_df = anomaly_detection[anomaly_detection[\"keep\"] == 1]\n",
    "anomaly_detection_df.drop(columns = [\"keep\"], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "f0d3ca95-b759-4c7a-a439-c564722de6b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\1800486837.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anomaly_detection_df[\"labels_need_action\"] = anomaly_detection_df[\"labels.name\"].map(lambda x: 1 if x not in official_active_labels else 0)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\1800486837.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anomaly_detection_df[\"outdated_label\"] = anomaly_detection_df[\"labels.name\"].map(lambda x: 1 if x in outdated_labels else 0)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\1800486837.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anomaly_detection_df[\"unknown_label\"] = anomaly_detection_df[\"labels.name\"].map(lambda x: 1 if (x not in official_active_labels and x not in outdated_labels) else 0)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\1800486837.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anomaly_detection_df[\"Label Transformed\"] = anomaly_detection_df[\"labels.name\"].map(lambda x: x.lower().replace(\":\", \"%3A\").replace(\" \", \"+\") if pd.isna(x) == False else x)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\1800486837.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anomaly_detection_df[\"Link for Quick Correction\"] = anomaly_detection_df[\"Label Transformed\"].map(lambda x: \"https://github.com/hackforla/website/issues?q=is%3Aissue+label%3A\"+str(x) if pd.isna(x) == False else np.nan)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\1800486837.py:10: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anomaly_detection_df.drop(columns = [\"Label Transformed\"], inplace = True)\n"
     ]
    }
   ],
   "source": [
    "outdated_labels = list(LC_df[LC_df[\"in_use?\"] == \"No\"][\"label_name\"].unique())\n",
    "official_active_labels = list(set(list(LC_df[\"label_name\"])).difference(set(outdated_labels)))\n",
    "\n",
    "anomaly_detection_df[\"labels_need_action\"] = anomaly_detection_df[\"labels.name\"].map(lambda x: 1 if x not in official_active_labels else 0)\n",
    "anomaly_detection_df[\"outdated_label\"] = anomaly_detection_df[\"labels.name\"].map(lambda x: 1 if x in outdated_labels else 0)\n",
    "anomaly_detection_df[\"unknown_label\"] = anomaly_detection_df[\"labels.name\"].map(lambda x: 1 if (x not in official_active_labels and x not in outdated_labels) else 0)\n",
    "anomaly_detection_df[\"Label Transformed\"] = anomaly_detection_df[\"labels.name\"].map(lambda x: x.lower().replace(\":\", \"%3A\").replace(\" \", \"+\") if pd.isna(x) == False else x)\n",
    "anomaly_detection_df[\"Link for Quick Correction\"] = anomaly_detection_df[\"Label Transformed\"].map(lambda x: \"https://github.com/hackforla/website/issues?q=is%3Aissue+label%3A\"+str(x) if pd.isna(x) == False else np.nan)\n",
    "\n",
    "anomaly_detection_df.drop(columns = [\"Label Transformed\"], inplace = True)\n",
    "\n",
    "anomaly_detection_df = anomaly_detection_df[[\"Project Board Column\", \"Runtime\", \"html_url\", \"title\", \"labels.name\", \"labels_need_action\", \"outdated_label\", \"unknown_label\", \"Link for Quick Correction\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "8c39f8d0-4866-4738-be26-653dd3f948a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\308379422.py:44: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  anomaly_detection_wdataset[\"front/back end count\"] = anomaly_detection_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n"
     ]
    }
   ],
   "source": [
    "\n",
    "anomaly_detection_df2_base = anomaly_detection_df.copy()\n",
    "anomaly_detection_df2_base.drop(columns = [\"labels_need_action\"], inplace = True)\n",
    "anomaly_detection_df2_base.drop(columns = [\"outdated_label\"], inplace = True)\n",
    "anomaly_detection_df2_base.drop(columns = [\"unknown_label\"], inplace = True)\n",
    "anomaly_detection_df2_base.drop(columns = [\"Link for Quick Correction\"], inplace = True)\n",
    "\n",
    "# Includes official labels that are current and outdated\n",
    "official_complexity = list(LC_df[LC_df[\"label_series\"] == \"complexity\"][\"label_name\"])\n",
    "official_feature = list(LC_df[LC_df[\"label_series\"] == \"feature\"][\"label_name\"])\n",
    "official_role = list(LC_df[LC_df[\"label_series\"] == \"role\"][\"label_name\"])\n",
    "official_size = list(LC_df[LC_df[\"label_series\"] == \"size\"][\"label_name\"])\n",
    "\n",
    "anomaly_detection_df2_base[\"Complexity Label\"] = anomaly_detection_df2_base[\"labels.name\"].map(lambda x: 1 if x in official_complexity else 0)\n",
    "anomaly_detection_df2_base[\"Feature Label\"] = anomaly_detection_df2_base[\"labels.name\"].map(lambda x: 1 if x in official_feature else 0)\n",
    "anomaly_detection_df2_base[\"Role Label\"] = anomaly_detection_df2_base[\"labels.name\"].map(lambda x: 1 if x in official_role else 0)\n",
    "anomaly_detection_df2_base[\"Size Label\"]= anomaly_detection_df2_base[\"labels.name\"].map(lambda x: 1 if x in official_size else 0)\n",
    "\n",
    "complexity_missing_series = list(LC_df[(LC_df[\"label_series\"] == \"complexity\") & (LC_df[\"missing_series?\"] == \"Yes\")][\"label_name\"])[0]\n",
    "feature_missing_series = list(LC_df[(LC_df[\"label_series\"] == \"feature\") & (LC_df[\"missing_series?\"] == \"Yes\")][\"label_name\"])[0]\n",
    "role_missing_series = list(LC_df[(LC_df[\"label_series\"] == \"role\") & (LC_df[\"missing_series?\"] == \"Yes\")][\"label_name\"])[0]\n",
    "size_missing_series = list(LC_df[(LC_df[\"label_series\"] == \"size\") & (LC_df[\"missing_series?\"] == \"Yes\")][\"label_name\"])[0]\n",
    "\n",
    "anomaly_detection_df2_base[\"Complexity Missing Label\"] = anomaly_detection_df2_base[\"labels.name\"].map(lambda x: 1 if x == complexity_missing_series else 0)\n",
    "anomaly_detection_df2_base[\"Feature Missing Label\"] = anomaly_detection_df2_base[\"labels.name\"].map(lambda x: 1 if x == feature_missing_series else 0)\n",
    "anomaly_detection_df2_base[\"Role Missing Label\"] = anomaly_detection_df2_base[\"labels.name\"].map(lambda x: 1 if x == role_missing_series else 0)\n",
    "anomaly_detection_df2_base[\"Size Missing Label\"]= anomaly_detection_df2_base[\"labels.name\"].map(lambda x: 1 if x == size_missing_series else 0)\n",
    " \n",
    "anomaly_detection_df2 = anomaly_detection_df2_base.groupby([\"Project Board Column\", \"Runtime\", \"html_url\", \"title\"])[[\"Complexity Label\", \"Feature Label\", \"Role Label\", \"Size Label\", \"Complexity Missing Label\", \"Feature Missing Label\", \"Role Missing Label\", \"Size Missing Label\"]].sum().reset_index()\n",
    "\n",
    "anomaly_detection_df2[\"Complexity defined label\"] = anomaly_detection_df2[\"Complexity Label\"]-anomaly_detection_df2[\"Complexity Missing Label\"]\n",
    "anomaly_detection_df2[\"Feature defined label\"] = anomaly_detection_df2[\"Feature Label\"]-anomaly_detection_df2[\"Feature Missing Label\"]\n",
    "anomaly_detection_df2[\"Role defined label\"] = anomaly_detection_df2[\"Role Label\"]-anomaly_detection_df2[\"Role Missing Label\"]\n",
    "anomaly_detection_df2[\"Size defined label\"] = anomaly_detection_df2[\"Size Label\"]-anomaly_detection_df2[\"Size Missing Label\"]\n",
    "\n",
    "anomaly_detection_df2_join = anomaly_detection_df2_base[anomaly_detection_df2_base[\"Role Label\"] == 1][[\"html_url\", \"labels.name\"]]\n",
    "anomaly_detection_df2 = anomaly_detection_df2.merge(anomaly_detection_df2_join, how = \"left\", on = [\"html_url\"])\n",
    "epic_issues = list(anomaly_detection[anomaly_detection['labels.name'] == 'epic']['html_url'].unique())\n",
    "ER_issues = list(anomaly_detection[anomaly_detection['labels.name'] == 'ER']['html_url'].unique())\n",
    "anomaly_detection_df2['Epic Issue?'] = anomaly_detection_df2['html_url'].map(lambda x: 1 if x in epic_issues else 0)\n",
    "anomaly_detection_df2['ER Issue?'] = anomaly_detection_df2['html_url'].map(lambda x: 1 if x in ER_issues else 0)\n",
    "\n",
    "# change role label of issues with front end and back end labels\n",
    "anomaly_detection_wdataset = anomaly_detection_df2[anomaly_detection_df2[\"labels.name\"].str.contains(\"front end\") | anomaly_detection_df2[\"labels.name\"].str.contains(\"back end\")]\n",
    "anomaly_detection_wdataset[\"front/back end count\"] = anomaly_detection_wdataset.groupby([\"html_url\", \"title\"])[\"labels.name\"].transform(\"count\")\n",
    "\n",
    "anomaly_detection_df2.loc[list(anomaly_detection_wdataset[anomaly_detection_wdataset[\"front/back end count\"] == 2].index), \"labels.name\"] = \"role: front end and backend/DevOps\"\n",
    "\n",
    "# Drop duplicates that have been created by the change\n",
    "anomaly_detection_df2.drop_duplicates(inplace = True)\n",
    "\n",
    "# Create dataset that detects issues with missing dependencies in icebox\n",
    "missing_dependency_label = list(LC_df[(LC_df[\"label_series\"] == \"dependency\") & (LC_df[\"missing_series?\"] == \"Yes\")][\"label_name\"])[0]\n",
    "missing_dependency = anomaly_detection[(anomaly_detection[\"labels.name\"] == missing_dependency_label) & (anomaly_detection[\"Project Board Column\"] == \"1 - Icebox\")]\n",
    "icebox_issues = list(anomaly_detection[anomaly_detection[\"Project Board Column\"] == \"1 - Icebox\"][\"html_url\"].unique())\n",
    "icebox_issues_with_dependency = list(anomaly_detection[(anomaly_detection[\"Project Board Column\"] == \"1 - Icebox\") & (anomaly_detection[\"labels.name\"] == \"Dependency\")][\"html_url\"].unique())\n",
    "icebox_issues_without_dependency = list(set(icebox_issues).difference(set(icebox_issues_with_dependency)))\n",
    "no_dependency = anomaly_detection[anomaly_detection[\"html_url\"].isin(icebox_issues_without_dependency)]\n",
    "missing_dependency = pd.concat([missing_dependency, no_dependency], ignore_index = True)\n",
    "\n",
    "missing_dependency = missing_dependency.iloc[:, [1,4,2,3,0]]\n",
    "\n",
    "if len(missing_dependency) == 0:\n",
    "    missing_dependency.loc[0] = [\" \",\" \",\" \",\" \",\" \"]\n",
    "else:\n",
    "    missing_dependency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "bf2a6d19-da9d-4a8f-8190-472878b193eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create dataset with issues that have labels in missing series (to be joined for anomaly report in Looker)\n",
    "missingseries_labels = list(LC_df[LC_df[\"missing_series?\"] == \"Yes\"][\"label_name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "bf445fea-d4ee-4f44-929d-8ee5148d48c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "issues_w_missinglabels = anomaly_detection[anomaly_detection['labels.name'].isin(missingseries_labels)][[\"Project Board Column\", \"html_url\", \"title\", \"labels.name\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "882099f9-6a0b-4cd6-baf2-81d0e54fa889",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3650092941.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  No_ER_label_filtered.drop(columns = [\"labels.name\"], inplace = True)\n",
      "C:\\Users\\kimbe\\AppData\\Local\\Temp\\ipykernel_24436\\3650092941.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  No_ER_label_filtered.drop_duplicates(inplace = True)\n"
     ]
    }
   ],
   "source": [
    "# Create new table that draws in all issues with ER title that do not have ER label\n",
    "\n",
    "ER_label_check = ER_issues_df3.copy()\n",
    "ER_label_check[\"ER Label?\"] = ER_label_check['html_url'].map(lambda x: 1 if x in ER_issues else 0)\n",
    "No_ER_label = ER_label_check[ER_label_check[\"ER Label?\"] == 0]\n",
    "No_ER_label_filtered = No_ER_label[~No_ER_label[\"title\"].str.contains(\"ER from TLDL\", case = False)]\n",
    "No_ER_label_filtered.drop(columns = [\"labels.name\"], inplace = True)\n",
    "No_ER_label_filtered.drop_duplicates(inplace = True)\n",
    "\n",
    "if len(No_ER_label_filtered) == 0:\n",
    "    No_ER_label_filtered = pd.DataFrame(columns = [\"Runtime\", \"html_url\", \"title\", \"Project Board Column\", \"ER Label?\", \"state\"])\n",
    "    No_ER_label_filtered.loc[0] = [\" \",\" \",\" \",\" \",\" \",\" \"]\n",
    "else:\n",
    "    no_ERlabel_issuestate = pd.DataFrame()\n",
    "\n",
    "    for url in No_ER_label_filtered[\"html_url\"]:\n",
    "        issue_number = re.findall(r'[0-9]+$', url)[0]\n",
    "        html = \"https://api.github.com/repos/hackforla/website/issues/\"+issue_number\n",
    "        response = requests.get(html, auth=(user, GitHub_token))\n",
    "        df = pd.json_normalize(response.json())[[\"html_url\", \"state\"]]\n",
    "        no_ERlabel_issuestate = pd.concat([no_ERlabel_issuestate, df], ignore_index = True)\n",
    "\n",
    "    No_ER_label_filtered = No_ER_label_filtered.merge(no_ERlabel_issuestate, how = \"left\", on = \"html_url\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "7b795f89-8657-49fa-afc2-944ede4c1cd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a table that displays issues with Complexity: Missing label with first comment being an empty description\n",
    "excluded_columns = [\"1 - Icebox\", \"2 - ER\", \"3 - New Issue Approval\"]\n",
    "empty_description_search = final_dataset[(~final_dataset[\"Project Board Column\"].isin(excluded_columns)) & (final_dataset[\"Complexity Label\"] == \"Complexity: Missing\")]\n",
    "\n",
    "empty_comment = []\n",
    "\n",
    "for url in empty_description_search[\"html_url\"]:\n",
    "    issue_number = re.findall(r'[0-9]+$', url)[0]\n",
    "    html = \"https://api.github.com/repos/hackforla/website/issues/\"+issue_number+\"/timeline\"\n",
    "    response = requests.get(html, auth=(user, GitHub_token))\n",
    "    df = pd.DataFrame(response.json())\n",
    "    if (\"body\" not in list(df.columns)):\n",
    "        if (df.iloc[0][\"actor\"]['login'] != 'github-actions[bot]' and df.iloc[0][\"event\"] == \"cross-referenced\"):\n",
    "            empty_comment.append(url)\n",
    "    elif (\"body\" in list(df.columns)):\n",
    "        if (pd.isna(df.iloc[0][\"body\"]) == True and df.iloc[0][\"actor\"]['login'] != 'github-actions[bot]' and df.iloc[0][\"event\"] == \"cross-referenced\"):\n",
    "            empty_comment.append(url)\n",
    "    else:\n",
    "        continue\n",
    "\n",
    "complexity_missing_emptycomment = final_dataset[final_dataset[\"html_url\"].isin(empty_comment)][[\"Project Board Column\", \"Role Label\", \"Complexity Label\", \"html_url\", \"title\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "a085c187-ca5c-431b-963b-0dd57476ea96",
   "metadata": {},
   "outputs": [],
   "source": [
    "if len(complexity_missing_emptycomment) == 0:\n",
    "    complexity_missing_emptycomment.loc[0] = [\" \",\" \",\" \",\" \",\" \"]\n",
    "else:\n",
    "    complexity_missing_emptycomment"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcb08c18-2d81-4fd4-b566-e951ca20f211",
   "metadata": {},
   "source": [
    "### Send Data to Google Sheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "e5c2142a-1b48-4975-84ef-0572844c1ed7",
   "metadata": {},
   "outputs": [],
   "source": [
    "### Send to Google Sheet\n",
    "\n",
    "Main_GOOGLE_SHEETS_ID = '1aJ0yHkXYMWTtMz6eEeolTLmAQOBc2DyptmR5SAmUrjM'\n",
    "\n",
    "sheet_name1 = 'Dataset 2'\n",
    "\n",
    "gs = gc.open_by_key(Main_GOOGLE_SHEETS_ID)\n",
    "\n",
    "worksheet1 = gs.worksheet(sheet_name1)\n",
    "\n",
    "worksheet1.clear()\n",
    "\n",
    "# Insert dataframe of issues into Google Sheet\n",
    "\n",
    "set_with_dataframe(worksheet = worksheet1, dataframe = final_dataset, include_index = False, include_column_header = True, resize = True)\n",
    "\n",
    "sheet_name2 = 'Labels to note'\n",
    "worksheet2 = gs.worksheet(sheet_name2)\n",
    "worksheet2.clear()\n",
    "set_with_dataframe(worksheet = worksheet2, dataframe = anomaly_detection_df, include_index = False, include_column_header = True, resize = True)\n",
    "\n",
    "sheet_name3 = 'Missing Labels'\n",
    "worksheet3 = gs.worksheet(sheet_name3)\n",
    "worksheet3.clear()\n",
    "set_with_dataframe(worksheet = worksheet3, dataframe = anomaly_detection_df2, include_index = False, include_column_header = True, resize = True)\n",
    "\n",
    "sheet_name4 = 'Issues with Missing Series Labels'\n",
    "worksheet4 = gs.worksheet(sheet_name4)\n",
    "worksheet4.clear()\n",
    "set_with_dataframe(worksheet = worksheet4, dataframe = issues_w_missinglabels, include_index = False, include_column_header = True, resize = True)\n",
    "\n",
    "sheet_name5 = 'Icebox Issues with Missing or No Dependency'\n",
    "worksheet5 = gs.worksheet(sheet_name5)\n",
    "worksheet5.clear()\n",
    "set_with_dataframe(worksheet = worksheet5, dataframe = missing_dependency, include_index = False, include_column_header = True, resize = True)\n",
    "\n",
    "sheet_name6 = 'Missing ER Label'\n",
    "worksheet6 = gs.worksheet(sheet_name6)\n",
    "worksheet6.clear()\n",
    "set_with_dataframe(worksheet = worksheet6, dataframe = No_ER_label_filtered, include_index = False, include_column_header = True, resize = True)\n",
    "\n",
    "sheet_name7 = 'Complexity Missing Issues with Empty 1st Comment'\n",
    "worksheet7 = gs.worksheet(sheet_name7)\n",
    "worksheet7.clear()\n",
    "set_with_dataframe(worksheet = worksheet7, dataframe = complexity_missing_emptycomment, include_index = False, include_column_header = True, resize = True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}