notebooks/GitHub_Data_Collection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6eb0addb-d324-4aa5-be8b-4239a78627e5",
   "metadata": {},
   "source": [
    "# GitHub Data Collection\n",
    "\n",
    "In this notebook, we will fetch the GitHub Issue/PR data for repositories we are interested in using the [MI tool](https://github.com/thoth-station/mi), pre-process the raw data into suitable data frames and store them as parquet files to an s3 bucket. We will also create [Trino](https://trino.io/) tables for the generated parquet files so that we can later create dashboards in [Superset](https://superset.operate-first.cloud/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4a95c846-7cc8-4f12-94de-51b79cd7b0e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "from dotenv import find_dotenv, load_dotenv\n",
    "import warnings\n",
    "import trino\n",
    "from s3_communication import S3Communication\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "load_dotenv(find_dotenv())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7650b537-46e8-4150-aade-a8ca2127cbb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Create a .env file on your local with the correct configs\n",
    "REPO = os.getenv(\"REPO\")\n",
    "ORG = os.getenv(\"ORG\")\n",
    "GITHUB_ACCESS_TOKEN = os.getenv(\"GITHUB_ACCESS_TOKEN\")\n",
    "s3_endpoint_url = os.getenv(\"S3_ENDPOINT\")\n",
    "aws_access_key_id = os.getenv(\"S3_ACCESS_KEY\")\n",
    "aws_secret_access_key = os.getenv(\"S3_SECRET_KEY\")\n",
    "s3_bucket = os.getenv(\"S3_BUCKET\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d03859a9-9734-4859-bc68-8f0fe8376452",
   "metadata": {},
   "outputs": [],
   "source": [
    "# init s3 connector\n",
    "s3c = S3Communication(\n",
    "    s3_endpoint_url, aws_access_key_id, aws_secret_access_key, s3_bucket\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c31081ff-96f5-4dfa-adaa-5f5191d2a3c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'os-climate/OS-Climate-Community-Hub'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repo_slug = f\"{ORG}/{REPO}\"\n",
    "repo_slug"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e4d55599-29db-4123-be0c-60d82a503d53",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note: The GitHub access token needs to be exported before importing the srcopmetrics package (current bug)\n",
    "from srcopsmetrics.entities.issue import Issue  # noqa: E402\n",
    "from srcopsmetrics.entities.pull_request import PullRequest  # noqa: E402"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a7d3d480-53b0-493c-9d4b-1609895af296",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO:srcopsmetrics.github_knowledge:Overall repositories found: 1\n",
      "INFO:srcopsmetrics.bot_knowledge:######################## Analysing os-climate/OS-Climate-Community-Hub ########################\n",
      "\n",
      "INFO:srcopsmetrics.utils:No repo identified, creating new directory at /opt/app-root/src/metrics/notebooks/srcopsmetrics/bot_knowledge/os-climate/OS-Climate-Community-Hub\n",
      "INFO:srcopsmetrics.bot_knowledge:########################\n",
      "INFO:srcopsmetrics.bot_knowledge:Detected entities:\n",
      "CodeFrequency # Commit # DependencyUpdate # Fork # Issue # IssueEvent # KebechetUpdateManager # License # PullRequest # PullRequestDiscussion # RawIssue # RawPullRequest # ReadMe # Release # Stargazer # TrafficClones # TrafficPaths # TrafficPaths # TrafficReferrers # TrafficClones # TrafficViews\n",
      "INFO:srcopsmetrics.bot_knowledge:########################\n",
      "INFO:srcopsmetrics.bot_knowledge:Issue inspection\n",
      "INFO:srcopsmetrics.entities.tools.storage:Loading knowledge locally\n",
      "INFO:srcopsmetrics.entities.tools.storage:Data from file %s loaded\n",
      "INFO:srcopsmetrics.entities.interface:No previous knowledge of type Issue found\n",
      "INFO:srcopsmetrics.iterator:-------------Issue Analysis-------------\n",
      "100%|██████████████████████| 35/35 [00:12<00:00,  2.83it/s, RATE remaining=4859]\n",
      "INFO:srcopsmetrics.entities.interface:Knowledge file Issue.json\n",
      "INFO:srcopsmetrics.entities.interface:new 31 entities\n",
      "INFO:srcopsmetrics.entities.interface:(overall 31 entities)\n",
      "INFO:srcopsmetrics.entities.interface:Saved locally at /opt/app-root/src/metrics/notebooks/srcopsmetrics/bot_knowledge/os-climate/OS-Climate-Community-Hub/Issue.json\n",
      "INFO:srcopsmetrics.bot_knowledge:\n",
      "\n",
      "INFO:srcopsmetrics.bot_knowledge:PullRequest inspection\n",
      "INFO:srcopsmetrics.entities.tools.storage:Loading knowledge locally\n",
      "INFO:srcopsmetrics.entities.tools.storage:Data from file %s loaded\n",
      "INFO:srcopsmetrics.entities.interface:No previous knowledge of type PullRequest found\n",
      "INFO:srcopsmetrics.iterator:-------------PullRequest Analysis-------------\n",
      "INFO:srcopsmetrics.entities.pull_request:Extracting PR #29                      \n",
      "INFO:srcopsmetrics.entities.pull_request:Extracting PR #21                      \n",
      "INFO:srcopsmetrics.entities.pull_request:Extracting PR #19                      \n",
      "INFO:srcopsmetrics.entities.pull_request:      -analysing review no. 1/2        \n",
      "INFO:srcopsmetrics.entities.pull_request:      -analysing review no. 2/2        \n",
      "INFO:srcopsmetrics.entities.pull_request:Extracting PR #1                       \n",
      "INFO:srcopsmetrics.entities.pull_request:      -analysing review no. 1/1        \n",
      "100%|████████████████████████| 4/4 [00:05<00:00,  1.49s/it, RATE remaining=4830]\n",
      "INFO:srcopsmetrics.entities.interface:Knowledge file PullRequest.json\n",
      "INFO:srcopsmetrics.entities.interface:new 4 entities\n",
      "INFO:srcopsmetrics.entities.interface:(overall 4 entities)\n",
      "INFO:srcopsmetrics.entities.interface:Saved locally at /opt/app-root/src/metrics/notebooks/srcopsmetrics/bot_knowledge/os-climate/OS-Climate-Community-Hub/PullRequest.json\n",
      "INFO:srcopsmetrics.bot_knowledge:\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Gather the data\n",
    "!rm -r srcopsmetrics/\n",
    "!python -m srcopsmetrics.cli -clr $repo_slug -e Issue,PullRequest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "80c026dd-18af-4c76-bbc9-5ada039ba967",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>created_by</th>\n",
       "      <th>created_at</th>\n",
       "      <th>closed_by</th>\n",
       "      <th>closed_at</th>\n",
       "      <th>labels</th>\n",
       "      <th>interactions</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Request Onboarding to OSC Data Commons</td>\n",
       "      <td>Requesting onboarding for github user: @romeok...</td>\n",
       "      <td>oindrillac</td>\n",
       "      <td>2022-05-23 18:05:16</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>{'onboarding': {'color': 'DE643F', 'labeled_at...</td>\n",
       "      <td>{'HeatherAck': 13}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Request credentials for an os-climate bucket</td>\n",
       "      <td>Requesting credentials for github user: mriefe...</td>\n",
       "      <td>mriefer</td>\n",
       "      <td>2022-05-13 09:02:21</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Request credentials for an os-climate bucket</td>\n",
       "      <td>Requesting credentials for github user: Christ...</td>\n",
       "      <td>ChristianMeyndt</td>\n",
       "      <td>2022-05-13 08:59:09</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Request credentials for an os-climate bucket</td>\n",
       "      <td>Requesting credentials for github user: DaBeID...</td>\n",
       "      <td>DaBeIDS</td>\n",
       "      <td>2022-05-13 08:54:23</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Request Onboarding to OSC Data Commons</td>\n",
       "      <td>Requesting onboarding for github user: mandy-c...</td>\n",
       "      <td>mandy-chessell</td>\n",
       "      <td>2022-05-12 13:28:05</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>2022-05-24 15:56:53</td>\n",
       "      <td>{'onboarding': {'color': 'DE643F', 'labeled_at...</td>\n",
       "      <td>{'HeatherAck': 12}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           title  \\\n",
       "id                                                 \n",
       "35        Request Onboarding to OSC Data Commons   \n",
       "34  Request credentials for an os-climate bucket   \n",
       "33  Request credentials for an os-climate bucket   \n",
       "32  Request credentials for an os-climate bucket   \n",
       "31        Request Onboarding to OSC Data Commons   \n",
       "\n",
       "                                                 body       created_by  \\\n",
       "id                                                                       \n",
       "35  Requesting onboarding for github user: @romeok...       oindrillac   \n",
       "34  Requesting credentials for github user: mriefe...          mriefer   \n",
       "33  Requesting credentials for github user: Christ...  ChristianMeyndt   \n",
       "32  Requesting credentials for github user: DaBeID...          DaBeIDS   \n",
       "31  Requesting onboarding for github user: mandy-c...   mandy-chessell   \n",
       "\n",
       "            created_at   closed_by           closed_at  \\\n",
       "id                                                       \n",
       "35 2022-05-23 18:05:16        None                 NaT   \n",
       "34 2022-05-13 09:02:21        None                 NaT   \n",
       "33 2022-05-13 08:59:09        None                 NaT   \n",
       "32 2022-05-13 08:54:23        None                 NaT   \n",
       "31 2022-05-12 13:28:05  HeatherAck 2022-05-24 15:56:53   \n",
       "\n",
       "                                               labels        interactions  \n",
       "id                                                                         \n",
       "35  {'onboarding': {'color': 'DE643F', 'labeled_at...  {'HeatherAck': 13}  \n",
       "34                                                 {}                  {}  \n",
       "33                                                 {}                  {}  \n",
       "32                                                 {}                  {}  \n",
       "31  {'onboarding': {'color': 'DE643F', 'labeled_at...  {'HeatherAck': 12}  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "issue = Issue(repo_slug)\n",
    "issue_df = issue.load_previous_knowledge(is_local=True)\n",
    "issue_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bc3a75d0-7b01-473e-9d13-ab02721a9a4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "issue_df = issue_df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "137e688b-68a2-4904-933b-e82443bed4ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>created_by</th>\n",
       "      <th>created_at</th>\n",
       "      <th>closed_by</th>\n",
       "      <th>closed_at</th>\n",
       "      <th>labels</th>\n",
       "      <th>interactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35</td>\n",
       "      <td>Request Onboarding to OSC Data Commons</td>\n",
       "      <td>Requesting onboarding for github user: @romeok...</td>\n",
       "      <td>oindrillac</td>\n",
       "      <td>2022-05-23 18:05:16</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>{'onboarding': {'color': 'DE643F', 'labeled_at...</td>\n",
       "      <td>{'HeatherAck': 13}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>34</td>\n",
       "      <td>Request credentials for an os-climate bucket</td>\n",
       "      <td>Requesting credentials for github user: mriefe...</td>\n",
       "      <td>mriefer</td>\n",
       "      <td>2022-05-13 09:02:21</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33</td>\n",
       "      <td>Request credentials for an os-climate bucket</td>\n",
       "      <td>Requesting credentials for github user: Christ...</td>\n",
       "      <td>ChristianMeyndt</td>\n",
       "      <td>2022-05-13 08:59:09</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32</td>\n",
       "      <td>Request credentials for an os-climate bucket</td>\n",
       "      <td>Requesting credentials for github user: DaBeID...</td>\n",
       "      <td>DaBeIDS</td>\n",
       "      <td>2022-05-13 08:54:23</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>Request Onboarding to OSC Data Commons</td>\n",
       "      <td>Requesting onboarding for github user: mandy-c...</td>\n",
       "      <td>mandy-chessell</td>\n",
       "      <td>2022-05-12 13:28:05</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>2022-05-24 15:56:53</td>\n",
       "      <td>{'onboarding': {'color': 'DE643F', 'labeled_at...</td>\n",
       "      <td>{'HeatherAck': 12}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                         title  \\\n",
       "0  35        Request Onboarding to OSC Data Commons   \n",
       "1  34  Request credentials for an os-climate bucket   \n",
       "2  33  Request credentials for an os-climate bucket   \n",
       "3  32  Request credentials for an os-climate bucket   \n",
       "4  31        Request Onboarding to OSC Data Commons   \n",
       "\n",
       "                                                body       created_by  \\\n",
       "0  Requesting onboarding for github user: @romeok...       oindrillac   \n",
       "1  Requesting credentials for github user: mriefe...          mriefer   \n",
       "2  Requesting credentials for github user: Christ...  ChristianMeyndt   \n",
       "3  Requesting credentials for github user: DaBeID...          DaBeIDS   \n",
       "4  Requesting onboarding for github user: mandy-c...   mandy-chessell   \n",
       "\n",
       "           created_at   closed_by           closed_at  \\\n",
       "0 2022-05-23 18:05:16        None                 NaT   \n",
       "1 2022-05-13 09:02:21        None                 NaT   \n",
       "2 2022-05-13 08:59:09        None                 NaT   \n",
       "3 2022-05-13 08:54:23        None                 NaT   \n",
       "4 2022-05-12 13:28:05  HeatherAck 2022-05-24 15:56:53   \n",
       "\n",
       "                                              labels        interactions  \n",
       "0  {'onboarding': {'color': 'DE643F', 'labeled_at...  {'HeatherAck': 13}  \n",
       "1                                                 {}                  {}  \n",
       "2                                                 {}                  {}  \n",
       "3                                                 {}                  {}  \n",
       "4  {'onboarding': {'color': 'DE643F', 'labeled_at...  {'HeatherAck': 12}  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "issue_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7f208b0b-cebe-4910-a8a6-34356b976d22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>created_by</th>\n",
       "      <th>created_at</th>\n",
       "      <th>closed_by</th>\n",
       "      <th>closed_at</th>\n",
       "      <th>org</th>\n",
       "      <th>repo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35</td>\n",
       "      <td>Request Onboarding to OSC Data Commons</td>\n",
       "      <td>Requesting onboarding for github user: @romeok...</td>\n",
       "      <td>oindrillac</td>\n",
       "      <td>2022-05-23 18:05:16</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>os-climate</td>\n",
       "      <td>OS-Climate-Community-Hub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>34</td>\n",
       "      <td>Request credentials for an os-climate bucket</td>\n",
       "      <td>Requesting credentials for github user: mriefe...</td>\n",
       "      <td>mriefer</td>\n",
       "      <td>2022-05-13 09:02:21</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>os-climate</td>\n",
       "      <td>OS-Climate-Community-Hub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33</td>\n",
       "      <td>Request credentials for an os-climate bucket</td>\n",
       "      <td>Requesting credentials for github user: Christ...</td>\n",
       "      <td>ChristianMeyndt</td>\n",
       "      <td>2022-05-13 08:59:09</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>os-climate</td>\n",
       "      <td>OS-Climate-Community-Hub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32</td>\n",
       "      <td>Request credentials for an os-climate bucket</td>\n",
       "      <td>Requesting credentials for github user: DaBeID...</td>\n",
       "      <td>DaBeIDS</td>\n",
       "      <td>2022-05-13 08:54:23</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>os-climate</td>\n",
       "      <td>OS-Climate-Community-Hub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>Request Onboarding to OSC Data Commons</td>\n",
       "      <td>Requesting onboarding for github user: mandy-c...</td>\n",
       "      <td>mandy-chessell</td>\n",
       "      <td>2022-05-12 13:28:05</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>2022-05-24 15:56:53</td>\n",
       "      <td>os-climate</td>\n",
       "      <td>OS-Climate-Community-Hub</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                         title  \\\n",
       "0  35        Request Onboarding to OSC Data Commons   \n",
       "1  34  Request credentials for an os-climate bucket   \n",
       "2  33  Request credentials for an os-climate bucket   \n",
       "3  32  Request credentials for an os-climate bucket   \n",
       "4  31        Request Onboarding to OSC Data Commons   \n",
       "\n",
       "                                                body       created_by  \\\n",
       "0  Requesting onboarding for github user: @romeok...       oindrillac   \n",
       "1  Requesting credentials for github user: mriefe...          mriefer   \n",
       "2  Requesting credentials for github user: Christ...  ChristianMeyndt   \n",
       "3  Requesting credentials for github user: DaBeID...          DaBeIDS   \n",
       "4  Requesting onboarding for github user: mandy-c...   mandy-chessell   \n",
       "\n",
       "           created_at   closed_by           closed_at         org  \\\n",
       "0 2022-05-23 18:05:16        None                 NaT  os-climate   \n",
       "1 2022-05-13 09:02:21        None                 NaT  os-climate   \n",
       "2 2022-05-13 08:59:09        None                 NaT  os-climate   \n",
       "3 2022-05-13 08:54:23        None                 NaT  os-climate   \n",
       "4 2022-05-12 13:28:05  HeatherAck 2022-05-24 15:56:53  os-climate   \n",
       "\n",
       "                       repo  \n",
       "0  OS-Climate-Community-Hub  \n",
       "1  OS-Climate-Community-Hub  \n",
       "2  OS-Climate-Community-Hub  \n",
       "3  OS-Climate-Community-Hub  \n",
       "4  OS-Climate-Community-Hub  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retain only relevant columns\n",
    "issue_cols_to_drop = [\"labels\", \"interactions\"]\n",
    "issue_df = issue_df.drop(columns=issue_cols_to_drop)\n",
    "issue_df[\"org\"] = ORG\n",
    "issue_df[\"repo\"] = REPO\n",
    "\n",
    "issue_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "956038ae-bb74-4944-9e3d-0d32006340e5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ResponseMetadata': {'RequestId': 'tx000000000000000016cc3-00628fbd0a-f6835f-ocs-storagecluster-cephobjectstore',\n",
       "  'HostId': '',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'content-length': '0',\n",
       "   'etag': '\"daa6440b3eb57b30a4584ca6147a1cdc\"',\n",
       "   'accept-ranges': 'bytes',\n",
       "   'x-amz-request-id': 'tx000000000000000016cc3-00628fbd0a-f6835f-ocs-storagecluster-cephobjectstore',\n",
       "   'date': 'Thu, 26 May 2022 17:46:50 GMT',\n",
       "   'set-cookie': 'bbdcd938787a45e68f8d240a4e2dadcf=9305a9992645bb0698c9f5d65ae10c7e; path=/; HttpOnly'},\n",
       "  'RetryAttempts': 0},\n",
       " 'ETag': '\"daa6440b3eb57b30a4584ca6147a1cdc\"'}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Upload the processed df to s3 as a parquet file\n",
    "s3c.upload_df_to_s3(\n",
    "    df=issue_df,\n",
    "    s3_prefix=\"open-services-group/metrics/github/os-climate/issues\",\n",
    "    s3_key=f\"{ORG}-{REPO}.parquet\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d0ccade6-8979-4f58-8418-2d8ee2277833",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>size</th>\n",
       "      <th>created_by</th>\n",
       "      <th>created_at</th>\n",
       "      <th>closed_at</th>\n",
       "      <th>closed_by</th>\n",
       "      <th>merged_at</th>\n",
       "      <th>merged_by</th>\n",
       "      <th>commits_number</th>\n",
       "      <th>changed_files_number</th>\n",
       "      <th>interactions</th>\n",
       "      <th>reviews</th>\n",
       "      <th>labels</th>\n",
       "      <th>commits</th>\n",
       "      <th>changed_files</th>\n",
       "      <th>first_review_at</th>\n",
       "      <th>first_approve_at</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Add OPF OSC board view</td>\n",
       "      <td>Signed-off-by: Erik Erlandson &lt;eerlands@redhat...</td>\n",
       "      <td>XS</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-05-05 22:03:10</td>\n",
       "      <td>2022-05-05 22:03:43</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-05-05 22:03:43</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>[]</td>\n",
       "      <td>[18bbf9c1779aa1ad7474533a7d88e17f3a4ffc96]</td>\n",
       "      <td>[README.md]</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Add request credentials issue template</td>\n",
       "      <td>Fixes #20 \\r\\n\\r\\n@erikerlandson</td>\n",
       "      <td>S</td>\n",
       "      <td>Shreyanand</td>\n",
       "      <td>2022-04-05 20:12:05</td>\n",
       "      <td>2022-04-06 16:06:15</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-04-06 16:06:15</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>{'Shreyanand': 1, 'erikerlandson': 4}</td>\n",
       "      <td>{}</td>\n",
       "      <td>[]</td>\n",
       "      <td>[7f495d13d687feb4cac247cd57f0ea8996ad1538]</td>\n",
       "      <td>[.github/ISSUE_TEMPLATE/request-credentials-fo...</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Onboarding template</td>\n",
       "      <td>Fixes #15</td>\n",
       "      <td>S</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-03-25 18:47:18</td>\n",
       "      <td>2022-03-28 14:58:29</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>2022-03-28 14:58:29</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>{'erikerlandson': 153}</td>\n",
       "      <td>{'923289967': {'author': 'HeatherAck', 'words_...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[fc18e481d703fc0039da25577a13ca545a38c273]</td>\n",
       "      <td>[.github/ISSUE_TEMPLATE/request-onboarding-to-...</td>\n",
       "      <td>2022-03-28 14:57:07</td>\n",
       "      <td>2022-03-28 14:57:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Thoth Configuration Initialization</td>\n",
       "      <td>## Automatic configuration initialization\\nThe...</td>\n",
       "      <td>M</td>\n",
       "      <td>khebhut[bot]</td>\n",
       "      <td>2022-01-28 18:51:07</td>\n",
       "      <td>2022-01-28 19:47:23</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>2022-01-28 19:47:23</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'866646009': {'author': 'HeatherAck', 'words_...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[f2fe0ca0f227c78ca07bb883bbb493b52bcd3e35]</td>\n",
       "      <td>[.thoth.yaml]</td>\n",
       "      <td>2022-01-28 19:47:13</td>\n",
       "      <td>2022-01-28 19:47:13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title  \\\n",
       "id                                           \n",
       "29                  Add OPF OSC board view   \n",
       "21  Add request credentials issue template   \n",
       "19                     Onboarding template   \n",
       "1       Thoth Configuration Initialization   \n",
       "\n",
       "                                                 body size     created_by  \\\n",
       "id                                                                          \n",
       "29  Signed-off-by: Erik Erlandson <eerlands@redhat...   XS  erikerlandson   \n",
       "21                  Fixes #20 \\r\\n\\r\\n@erikerlandson     S     Shreyanand   \n",
       "19                                          Fixes #15    S  erikerlandson   \n",
       "1   ## Automatic configuration initialization\\nThe...    M   khebhut[bot]   \n",
       "\n",
       "            created_at           closed_at      closed_by           merged_at  \\\n",
       "id                                                                              \n",
       "29 2022-05-05 22:03:10 2022-05-05 22:03:43  erikerlandson 2022-05-05 22:03:43   \n",
       "21 2022-04-05 20:12:05 2022-04-06 16:06:15  erikerlandson 2022-04-06 16:06:15   \n",
       "19 2022-03-25 18:47:18 2022-03-28 14:58:29     HeatherAck 2022-03-28 14:58:29   \n",
       "1  2022-01-28 18:51:07 2022-01-28 19:47:23     HeatherAck 2022-01-28 19:47:23   \n",
       "\n",
       "        merged_by  commits_number  changed_files_number  \\\n",
       "id                                                        \n",
       "29  erikerlandson               1                     1   \n",
       "21  erikerlandson               1                     1   \n",
       "19     HeatherAck               1                     1   \n",
       "1      HeatherAck               1                     1   \n",
       "\n",
       "                             interactions  \\\n",
       "id                                          \n",
       "29                                     {}   \n",
       "21  {'Shreyanand': 1, 'erikerlandson': 4}   \n",
       "19                 {'erikerlandson': 153}   \n",
       "1                                      {}   \n",
       "\n",
       "                                              reviews labels  \\\n",
       "id                                                             \n",
       "29                                                 {}     []   \n",
       "21                                                 {}     []   \n",
       "19  {'923289967': {'author': 'HeatherAck', 'words_...     []   \n",
       "1   {'866646009': {'author': 'HeatherAck', 'words_...     []   \n",
       "\n",
       "                                       commits  \\\n",
       "id                                               \n",
       "29  [18bbf9c1779aa1ad7474533a7d88e17f3a4ffc96]   \n",
       "21  [7f495d13d687feb4cac247cd57f0ea8996ad1538]   \n",
       "19  [fc18e481d703fc0039da25577a13ca545a38c273]   \n",
       "1   [f2fe0ca0f227c78ca07bb883bbb493b52bcd3e35]   \n",
       "\n",
       "                                        changed_files     first_review_at  \\\n",
       "id                                                                          \n",
       "29                                        [README.md]                 NaT   \n",
       "21  [.github/ISSUE_TEMPLATE/request-credentials-fo...                 NaT   \n",
       "19  [.github/ISSUE_TEMPLATE/request-onboarding-to-... 2022-03-28 14:57:07   \n",
       "1                                       [.thoth.yaml] 2022-01-28 19:47:13   \n",
       "\n",
       "      first_approve_at  \n",
       "id                      \n",
       "29                 NaT  \n",
       "21                 NaT  \n",
       "19 2022-03-28 14:57:07  \n",
       "1  2022-01-28 19:47:13  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr = PullRequest(repo_slug)\n",
    "pr_df = pr.load_previous_knowledge(is_local=True)\n",
    "pr_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "cb62fef6-589d-4e92-a06b-edd69dee6341",
   "metadata": {},
   "outputs": [],
   "source": [
    "pr_df = pr_df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8921b65f-ad13-4cb9-883f-1f99cd1ee182",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>size</th>\n",
       "      <th>created_by</th>\n",
       "      <th>created_at</th>\n",
       "      <th>closed_at</th>\n",
       "      <th>closed_by</th>\n",
       "      <th>merged_at</th>\n",
       "      <th>merged_by</th>\n",
       "      <th>commits_number</th>\n",
       "      <th>changed_files_number</th>\n",
       "      <th>interactions</th>\n",
       "      <th>reviews</th>\n",
       "      <th>labels</th>\n",
       "      <th>commits</th>\n",
       "      <th>changed_files</th>\n",
       "      <th>first_review_at</th>\n",
       "      <th>first_approve_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29</td>\n",
       "      <td>Add OPF OSC board view</td>\n",
       "      <td>Signed-off-by: Erik Erlandson &lt;eerlands@redhat...</td>\n",
       "      <td>XS</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-05-05 22:03:10</td>\n",
       "      <td>2022-05-05 22:03:43</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-05-05 22:03:43</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>[]</td>\n",
       "      <td>[18bbf9c1779aa1ad7474533a7d88e17f3a4ffc96]</td>\n",
       "      <td>[README.md]</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21</td>\n",
       "      <td>Add request credentials issue template</td>\n",
       "      <td>Fixes #20 \\r\\n\\r\\n@erikerlandson</td>\n",
       "      <td>S</td>\n",
       "      <td>Shreyanand</td>\n",
       "      <td>2022-04-05 20:12:05</td>\n",
       "      <td>2022-04-06 16:06:15</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-04-06 16:06:15</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>{'Shreyanand': 1, 'erikerlandson': 4}</td>\n",
       "      <td>{}</td>\n",
       "      <td>[]</td>\n",
       "      <td>[7f495d13d687feb4cac247cd57f0ea8996ad1538]</td>\n",
       "      <td>[.github/ISSUE_TEMPLATE/request-credentials-fo...</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19</td>\n",
       "      <td>Onboarding template</td>\n",
       "      <td>Fixes #15</td>\n",
       "      <td>S</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-03-25 18:47:18</td>\n",
       "      <td>2022-03-28 14:58:29</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>2022-03-28 14:58:29</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>{'erikerlandson': 153}</td>\n",
       "      <td>{'923289967': {'author': 'HeatherAck', 'words_...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[fc18e481d703fc0039da25577a13ca545a38c273]</td>\n",
       "      <td>[.github/ISSUE_TEMPLATE/request-onboarding-to-...</td>\n",
       "      <td>2022-03-28 14:57:07</td>\n",
       "      <td>2022-03-28 14:57:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Thoth Configuration Initialization</td>\n",
       "      <td>## Automatic configuration initialization\\nThe...</td>\n",
       "      <td>M</td>\n",
       "      <td>khebhut[bot]</td>\n",
       "      <td>2022-01-28 18:51:07</td>\n",
       "      <td>2022-01-28 19:47:23</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>2022-01-28 19:47:23</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'866646009': {'author': 'HeatherAck', 'words_...</td>\n",
       "      <td>[]</td>\n",
       "      <td>[f2fe0ca0f227c78ca07bb883bbb493b52bcd3e35]</td>\n",
       "      <td>[.thoth.yaml]</td>\n",
       "      <td>2022-01-28 19:47:13</td>\n",
       "      <td>2022-01-28 19:47:13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                   title  \\\n",
       "0  29                  Add OPF OSC board view   \n",
       "1  21  Add request credentials issue template   \n",
       "2  19                     Onboarding template   \n",
       "3   1      Thoth Configuration Initialization   \n",
       "\n",
       "                                                body size     created_by  \\\n",
       "0  Signed-off-by: Erik Erlandson <eerlands@redhat...   XS  erikerlandson   \n",
       "1                  Fixes #20 \\r\\n\\r\\n@erikerlandson     S     Shreyanand   \n",
       "2                                          Fixes #15    S  erikerlandson   \n",
       "3  ## Automatic configuration initialization\\nThe...    M   khebhut[bot]   \n",
       "\n",
       "           created_at           closed_at      closed_by           merged_at  \\\n",
       "0 2022-05-05 22:03:10 2022-05-05 22:03:43  erikerlandson 2022-05-05 22:03:43   \n",
       "1 2022-04-05 20:12:05 2022-04-06 16:06:15  erikerlandson 2022-04-06 16:06:15   \n",
       "2 2022-03-25 18:47:18 2022-03-28 14:58:29     HeatherAck 2022-03-28 14:58:29   \n",
       "3 2022-01-28 18:51:07 2022-01-28 19:47:23     HeatherAck 2022-01-28 19:47:23   \n",
       "\n",
       "       merged_by  commits_number  changed_files_number  \\\n",
       "0  erikerlandson               1                     1   \n",
       "1  erikerlandson               1                     1   \n",
       "2     HeatherAck               1                     1   \n",
       "3     HeatherAck               1                     1   \n",
       "\n",
       "                            interactions  \\\n",
       "0                                     {}   \n",
       "1  {'Shreyanand': 1, 'erikerlandson': 4}   \n",
       "2                 {'erikerlandson': 153}   \n",
       "3                                     {}   \n",
       "\n",
       "                                             reviews labels  \\\n",
       "0                                                 {}     []   \n",
       "1                                                 {}     []   \n",
       "2  {'923289967': {'author': 'HeatherAck', 'words_...     []   \n",
       "3  {'866646009': {'author': 'HeatherAck', 'words_...     []   \n",
       "\n",
       "                                      commits  \\\n",
       "0  [18bbf9c1779aa1ad7474533a7d88e17f3a4ffc96]   \n",
       "1  [7f495d13d687feb4cac247cd57f0ea8996ad1538]   \n",
       "2  [fc18e481d703fc0039da25577a13ca545a38c273]   \n",
       "3  [f2fe0ca0f227c78ca07bb883bbb493b52bcd3e35]   \n",
       "\n",
       "                                       changed_files     first_review_at  \\\n",
       "0                                        [README.md]                 NaT   \n",
       "1  [.github/ISSUE_TEMPLATE/request-credentials-fo...                 NaT   \n",
       "2  [.github/ISSUE_TEMPLATE/request-onboarding-to-... 2022-03-28 14:57:07   \n",
       "3                                      [.thoth.yaml] 2022-01-28 19:47:13   \n",
       "\n",
       "     first_approve_at  \n",
       "0                 NaT  \n",
       "1                 NaT  \n",
       "2 2022-03-28 14:57:07  \n",
       "3 2022-01-28 19:47:13  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d1fcf9ad-bfe9-421b-a9a8-301fa37c6f9b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>size</th>\n",
       "      <th>created_by</th>\n",
       "      <th>created_at</th>\n",
       "      <th>closed_at</th>\n",
       "      <th>closed_by</th>\n",
       "      <th>merged_at</th>\n",
       "      <th>merged_by</th>\n",
       "      <th>commits_number</th>\n",
       "      <th>changed_files_number</th>\n",
       "      <th>first_review_at</th>\n",
       "      <th>first_approve_at</th>\n",
       "      <th>org</th>\n",
       "      <th>repo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29</td>\n",
       "      <td>Add OPF OSC board view</td>\n",
       "      <td>Signed-off-by: Erik Erlandson &lt;eerlands@redhat...</td>\n",
       "      <td>XS</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-05-05 22:03:10</td>\n",
       "      <td>2022-05-05 22:03:43</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-05-05 22:03:43</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>os-climate</td>\n",
       "      <td>OS-Climate-Community-Hub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21</td>\n",
       "      <td>Add request credentials issue template</td>\n",
       "      <td>Fixes #20 \\r\\n\\r\\n@erikerlandson</td>\n",
       "      <td>S</td>\n",
       "      <td>Shreyanand</td>\n",
       "      <td>2022-04-05 20:12:05</td>\n",
       "      <td>2022-04-06 16:06:15</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-04-06 16:06:15</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>os-climate</td>\n",
       "      <td>OS-Climate-Community-Hub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19</td>\n",
       "      <td>Onboarding template</td>\n",
       "      <td>Fixes #15</td>\n",
       "      <td>S</td>\n",
       "      <td>erikerlandson</td>\n",
       "      <td>2022-03-25 18:47:18</td>\n",
       "      <td>2022-03-28 14:58:29</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>2022-03-28 14:58:29</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-03-28 14:57:07</td>\n",
       "      <td>2022-03-28 14:57:07</td>\n",
       "      <td>os-climate</td>\n",
       "      <td>OS-Climate-Community-Hub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Thoth Configuration Initialization</td>\n",
       "      <td>## Automatic configuration initialization\\nThe...</td>\n",
       "      <td>M</td>\n",
       "      <td>khebhut[bot]</td>\n",
       "      <td>2022-01-28 18:51:07</td>\n",
       "      <td>2022-01-28 19:47:23</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>2022-01-28 19:47:23</td>\n",
       "      <td>HeatherAck</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-28 19:47:13</td>\n",
       "      <td>2022-01-28 19:47:13</td>\n",
       "      <td>os-climate</td>\n",
       "      <td>OS-Climate-Community-Hub</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                   title  \\\n",
       "0  29                  Add OPF OSC board view   \n",
       "1  21  Add request credentials issue template   \n",
       "2  19                     Onboarding template   \n",
       "3   1      Thoth Configuration Initialization   \n",
       "\n",
       "                                                body size     created_by  \\\n",
       "0  Signed-off-by: Erik Erlandson <eerlands@redhat...   XS  erikerlandson   \n",
       "1                  Fixes #20 \\r\\n\\r\\n@erikerlandson     S     Shreyanand   \n",
       "2                                          Fixes #15    S  erikerlandson   \n",
       "3  ## Automatic configuration initialization\\nThe...    M   khebhut[bot]   \n",
       "\n",
       "           created_at           closed_at      closed_by           merged_at  \\\n",
       "0 2022-05-05 22:03:10 2022-05-05 22:03:43  erikerlandson 2022-05-05 22:03:43   \n",
       "1 2022-04-05 20:12:05 2022-04-06 16:06:15  erikerlandson 2022-04-06 16:06:15   \n",
       "2 2022-03-25 18:47:18 2022-03-28 14:58:29     HeatherAck 2022-03-28 14:58:29   \n",
       "3 2022-01-28 18:51:07 2022-01-28 19:47:23     HeatherAck 2022-01-28 19:47:23   \n",
       "\n",
       "       merged_by  commits_number  changed_files_number     first_review_at  \\\n",
       "0  erikerlandson               1                     1                 NaT   \n",
       "1  erikerlandson               1                     1                 NaT   \n",
       "2     HeatherAck               1                     1 2022-03-28 14:57:07   \n",
       "3     HeatherAck               1                     1 2022-01-28 19:47:13   \n",
       "\n",
       "     first_approve_at         org                      repo  \n",
       "0                 NaT  os-climate  OS-Climate-Community-Hub  \n",
       "1                 NaT  os-climate  OS-Climate-Community-Hub  \n",
       "2 2022-03-28 14:57:07  os-climate  OS-Climate-Community-Hub  \n",
       "3 2022-01-28 19:47:13  os-climate  OS-Climate-Community-Hub  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retain only relevant columns\n",
    "pr_cols_to_drop = [\"interactions\", \"reviews\", \"labels\", \"commits\", \"changed_files\"]\n",
    "prs_df = pr_df.drop(columns=pr_cols_to_drop)\n",
    "prs_df[\"org\"] = ORG\n",
    "prs_df[\"repo\"] = REPO\n",
    "\n",
    "prs_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "075cf9b7-8894-4154-89aa-2febf9b77913",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ResponseMetadata': {'RequestId': 'tx000000000000000016cc4-00628fbd0c-f6835f-ocs-storagecluster-cephobjectstore',\n",
       "  'HostId': '',\n",
       "  'HTTPStatusCode': 200,\n",
       "  'HTTPHeaders': {'content-length': '0',\n",
       "   'etag': '\"2cb47de22b4b4ebc639d7c8ba34dc9e5\"',\n",
       "   'accept-ranges': 'bytes',\n",
       "   'x-amz-request-id': 'tx000000000000000016cc4-00628fbd0c-f6835f-ocs-storagecluster-cephobjectstore',\n",
       "   'date': 'Thu, 26 May 2022 17:46:52 GMT',\n",
       "   'set-cookie': 'bbdcd938787a45e68f8d240a4e2dadcf=9305a9992645bb0698c9f5d65ae10c7e; path=/; HttpOnly'},\n",
       "  'RetryAttempts': 0},\n",
       " 'ETag': '\"2cb47de22b4b4ebc639d7c8ba34dc9e5\"'}"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Upload the processed df to s3 as a parquet file\n",
    "s3c.upload_df_to_s3(\n",
    "    df=prs_df,\n",
    "    s3_prefix=\"open-services-group/metrics/github/os-climate/prs\",\n",
    "    s3_key=f\"{ORG}-{REPO}.parquet\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "851e7c07-5d7f-47d0-a40b-66cbfbc90b06",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Map the datatype columns of df to suitable datatype supported in Trino/Superset\n",
    "_p2smap = {\n",
    "    \"object\": \"varchar\",\n",
    "    \"int64\": \"bigint\",\n",
    "    \"float64\": \"double\",\n",
    "    \"datetime64[ns]\": \"timestamp\",\n",
    "    \"bool\": \"boolean\",\n",
    "}\n",
    "\n",
    "\n",
    "def pandas_type_to_sql(pt):\n",
    "    st = _p2smap.get(pt)\n",
    "    if st is not None:\n",
    "        return st\n",
    "    raise ValueError(\"unexpected pandas column type '{pt}'\".format(pt=pt))\n",
    "\n",
    "\n",
    "# Generate the Trino table schema\n",
    "def generate_table_schema_pairs(df):\n",
    "    ptypes = [str(e) for e in df.dtypes.to_list()]\n",
    "    stypes = [pandas_type_to_sql(e) for e in ptypes]\n",
    "    pz = list(zip(df.columns.to_list(), stypes))\n",
    "    return \",\\n\".join([\"    {n} {t}\".format(n=e[0], t=e[1]) for e in pz])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "be135a48-952a-4bd9-b5de-1bb2f4e28532",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a Trino client\n",
    "conn = trino.dbapi.connect(\n",
    "    auth=trino.auth.BasicAuthentication(\n",
    "        os.environ[\"TRINO_USER\"], os.environ[\"TRINO_PASSWD\"]\n",
    "    ),\n",
    "    host=os.environ[\"TRINO_HOST\"],\n",
    "    port=int(os.environ[\"TRINO_PORT\"]),\n",
    "    http_scheme=\"https\",\n",
    "    verify=True,\n",
    ")\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "47d9d7ad-c932-4dfd-b175-20dd590568ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['data_science_general']"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if Trino connection was successful\n",
    "cur.execute(\"show catalogs\")\n",
    "cur.fetchall()[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bffb3943-52ba-492f-8310-ed1d2d680e78",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the issues table with data populated from parquet file\n",
    "issue_schema = generate_table_schema_pairs(issue_df)\n",
    "\n",
    "tabledef = \"\"\"create table if not exists data_science_general.default.os_climate_issues(\n",
    "{schema}\n",
    ") with (\n",
    "    format = 'parquet',\n",
    "    external_location = 's3a://{s3_bucket}/open-services-group/metrics/github/os-climate/issues'\n",
    ")\"\"\".format(\n",
    "    schema=issue_schema,\n",
    "    s3_bucket=os.environ[\"S3_BUCKET\"],\n",
    ")\n",
    "\n",
    "cur.execute(tabledef)\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf4f8078-2743-4d84-bfb1-0eb34886827c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the PR table with data populated from parquet file\n",
    "pr_schema = generate_table_schema_pairs(prs_df)\n",
    "\n",
    "tabledef = \"\"\"create table if not exists data_science_general.default.os_climate_prs(\n",
    "{schema}\n",
    ") with (\n",
    "    format = 'parquet',\n",
    "    external_location = 's3a://{s3_bucket}/open-services-group/metrics/github/os-climate/prs'\n",
    ")\"\"\".format(\n",
    "    schema=pr_schema,\n",
    "    s3_bucket=os.environ[\"S3_BUCKET\"],\n",
    ")\n",
    "\n",
    "cur.execute(tabledef)\n",
    "cur.fetchall()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}