issuesAnalysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7add0c8e-4c37-4e1d-a1f5-0665f1668909",
   "metadata": {},
   "source": [
    "## Data Cleaning Goals\n",
    "1. I want to create data that will allow me to\n",
    "   \n",
    "   a. analyze the structure of hierarchy across GitHub Repositories (whose opening issues? whose commenting on issues? how many are there overall?)\n",
    "   \n",
    "   b. track the sequence of participation for each issue\n",
    "   \n",
    "   c. link PRs to issues\n",
    "   \n",
    "   d. collect covariates related to issues so I can measure \"issue difficulty\"\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3f0a94b9-c839-468d-8e9d-fe6c5fa1f976",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import display, HTML\n",
    "\n",
    "def pretty_print(df):\n",
    "    return display(HTML(df.to_html().replace(\"\\\\n\",\"<br>\") ) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7c5a58ac-1efe-4472-b9bf-50fa1721b27b",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Import Libraries and Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6ec58803-cdc1-4a7a-8597-326e8f6b77b8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import glob\n",
    "import dask.dataframe as dd\n",
    "import pandas as pd\n",
    "from pandarallel import pandarallel\n",
    "import matplotlib.pyplot as plt\n",
    "from datetime import datetime, timezone\n",
    "import ast\n",
    "import numpy as np\n",
    "from operator import itemgetter\n",
    "from stargazer.stargazer import Stargazer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a1c863b9-f93f-420f-920b-6f1546f478b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.style.use(\"seaborn\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "29a81293-26e6-4433-a769-9eacb9891964",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO: Pandarallel will run on 32 workers.\n",
      "INFO: Pandarallel will use Memory file system to transfer data between the main process and workers.\n"
     ]
    }
   ],
   "source": [
    "pandarallel.initialize(progress_bar = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "138b397f-fb37-4c8e-87da-6b9cd22be566",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2c3e5b6d-2bad-4904-8569-1e1100662fa5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3min 9s, sys: 29 s, total: 3min 38s\n",
      "Wall time: 4min 21s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Read data on issue comments, issues\n",
    "issue_com = glob.glob('data/github_clean/filtered_github_data/issueCo*')\n",
    "issue_com.extend(glob.glob('data/github_clean/github_data_pre_18/issueCo*'))\n",
    "df_issue_comments = pd.concat([pd.read_csv(ele, index_col = 0) for ele in issue_com]).reset_index(drop = True)\n",
    "\n",
    "issues = glob.glob('data/github_clean/filtered_github_data/issues*')\n",
    "issues.extend(glob.glob('data/github_clean/github_data_pre_18/issues*'))\n",
    "df_issue = pd.concat([pd.read_csv(ele, index_col = 0) for ele in issues]).reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fbc4692-7986-4046-b92d-204d7c29233d",
   "metadata": {},
   "source": [
    "# Reduce Memory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "97c61fec-1810-4923-80f8-ab980dd22a52",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reduce memory usage\n",
    "def reduce_mem_usage(df, verbose=True):\n",
    "    numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n",
    "    start_mem = df.memory_usage(deep=True).sum() / 1024**2    \n",
    "    for col in df.columns:\n",
    "        col_type = df[col].dtypes\n",
    "        if col_type in numerics:\n",
    "            c_min = df[col].min()\n",
    "            c_max = df[col].max()\n",
    "            if str(col_type)[:3] == 'int':  # for integers\n",
    "                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n",
    "                    df[col] = df[col].astype(np.int8)\n",
    "                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n",
    "                    df[col] = df[col].astype(np.int16)\n",
    "                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n",
    "                    df[col] = df[col].astype(np.int32)\n",
    "                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n",
    "                    df[col] = df[col].astype(np.int64)  \n",
    "            else:  # for floats.\n",
    "                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n",
    "                    df[col] = df[col].astype(np.float16)\n",
    "                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n",
    "                    df[col] = df[col].astype(np.float32)\n",
    "                else:\n",
    "                    df[col] = df[col].astype(np.float64)    \n",
    "    end_mem = df.memory_usage(deep=True).sum() / 1024**2\n",
    "    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f1a15b2-c140-4f84-bb41-9d44443aa9ba",
   "metadata": {},
   "source": [
    "## Storing Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "05ad98d9-37ff-44f1-bdf9-985bd5af340b",
   "metadata": {},
   "outputs": [],
   "source": [
    "OriginalDataStatistics = pd.DataFrame()\n",
    "DataDescriptives = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f3ce80e-a701-4539-a596-4bff442a783a",
   "metadata": {},
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4ae10678-f6cc-4227-b2c8-9775b851706b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# columns to rename\n",
    "mod_columns = [ele for ele in df_issue_comments.columns if 'latest' in ele]\n",
    "mod_dict = {ele : ele.replace('latest_', '') for ele in mod_columns}\n",
    "df_issue_comments.rename(mod_dict, axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b536752a-3bec-46c8-b6e7-58daa96e3e1f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "full_issue_data = pd.concat([df_issue,df_issue_comments])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "10b48efd-68d7-433b-bd41-710dc48db005",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/user/20506/ipykernel_2655443/1104531115.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",
      "  df_issue_clean['key'] = df_issue_clean['repo_id'].apply(str) + \"_\" + df_issue_clean['issue_number'].apply(str)\n"
     ]
    }
   ],
   "source": [
    "# clean data: remove entries with NA issue number values  \n",
    "df_issue_clean = full_issue_data[~full_issue_data['issue_number'].isna()]\n",
    "# clean data: add key variable\n",
    "df_issue_clean['key'] = df_issue_clean['repo_id'].apply(str) + \"_\" + df_issue_clean['issue_number'].apply(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "91b71438-9f93-49ea-aa96-687171ede526",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/user/20506/ipykernel_2655443/3002124751.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",
      "  df_issue_clean['created_at'] = pd.to_datetime(df_issue_clean['created_at'])\n",
      "/tmp/user/20506/ipykernel_2655443/3002124751.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_issue_clean['created_at'] = df_issue_clean['created_at'].dt.tz_localize(None)\n"
     ]
    }
   ],
   "source": [
    "df_issue_clean['created_at'] = pd.to_datetime(df_issue_clean['created_at'])\n",
    "df_issue_clean['created_at'] = df_issue_clean['created_at'].dt.tz_localize(None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "2e6e78e4-e33c-43d3-bbd4-9f25c29b85f1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# FIX people who aren't classified as owner\n",
    "fix_ind = df_issue_clean[df_issue_clean.apply(lambda x: x['repo_name'].split(\"/\")[0] == x['issue_user_login'], axis = 1)].index\n",
    "df_issue_clean.loc[fix_ind, 'issue_author_association'] = 'OWNER'\n",
    "\n",
    "fix_ind_actor = df_issue_clean[df_issue_clean.apply(lambda x: x['repo_name'].split(\"/\")[0] == x['actor_login'], axis = 1)].index\n",
    "df_issue_clean.loc[fix_ind_actor, 'actor_repo_association'] = 'OWNER'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "78f21572-6d9d-453c-b2b9-24d69c667a41",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_actor_missing = df_issue_clean[['actor_repo_association', 'actor_login', 'key']].drop_duplicates()\n",
    "df_actor_missing['count'] = df_actor_missing.groupby(['actor_login','key']).transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a8874a1f-7379-4fba-9789-3c522b9cf8e1",
   "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>actor_repo_association</th>\n",
       "      <th>actor_login</th>\n",
       "      <th>key</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OWNER</td>\n",
       "      <td>tkalus</td>\n",
       "      <td>96523010_197.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>pyup-bot</td>\n",
       "      <td>54683816_1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Murukarthick</td>\n",
       "      <td>97612481_440.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OWNER</td>\n",
       "      <td>DeniRibicic</td>\n",
       "      <td>97612481_736.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>lorena1976</td>\n",
       "      <td>33614304_1192.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3966532</th>\n",
       "      <td>COLLABORATOR</td>\n",
       "      <td>cycleuser</td>\n",
       "      <td>62860862_5.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3966534</th>\n",
       "      <td>NONE</td>\n",
       "      <td>coveralls</td>\n",
       "      <td>101275731_20.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3966537</th>\n",
       "      <td>COLLABORATOR</td>\n",
       "      <td>markotoplak</td>\n",
       "      <td>53335377_182.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3966539</th>\n",
       "      <td>NaN</td>\n",
       "      <td>dwt</td>\n",
       "      <td>1372698_2.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3966540</th>\n",
       "      <td>NaN</td>\n",
       "      <td>carljm</td>\n",
       "      <td>1446474_107.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3245550 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        actor_repo_association   actor_login              key  count\n",
       "0                        OWNER        tkalus   96523010_197.0      2\n",
       "1                          NaN      pyup-bot     54683816_1.0      0\n",
       "2                          NaN  Murukarthick   97612481_440.0      1\n",
       "3                        OWNER   DeniRibicic   97612481_736.0      1\n",
       "4                          NaN    lorena1976  33614304_1192.0      0\n",
       "...                        ...           ...              ...    ...\n",
       "3966532           COLLABORATOR     cycleuser     62860862_5.0      1\n",
       "3966534                   NONE     coveralls   101275731_20.0      1\n",
       "3966537           COLLABORATOR   markotoplak   53335377_182.0      1\n",
       "3966539                    NaN           dwt      1372698_2.0      0\n",
       "3966540                    NaN        carljm    1446474_107.0      0\n",
       "\n",
       "[3245550 rows x 4 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_actor_missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "89a5116d-cfaa-49f7-bce9-8dff2af165fc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# issue_author_association - author association for individual who opened the issue\n",
    "# actor_repo_association - author association for individual associated with repository\n",
    "# need to add actor_repo_association for IssuesEvent\n",
    "# for \"opened\" action, just fill in with issue_author_association\n",
    "opened_issues = df_issue_clean[(df_issue_clean['type'] == 'IssuesEvent') & (df_issue_clean['issue_action'] == 'opened')].index\n",
    "df_issue_clean.loc[opened_issues, 'actor_repo_association'] = df_issue_clean.loc[opened_issues, 'issue_author_association']\n",
    "# for \"closed\" action, need to impute - create table of ranks throughout time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "80993151-05c0-40c2-9843-2dd363c88610",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "all_ranks = df_issue_clean[((df_issue_clean['type'] == 'IssuesEvent') & (df_issue_clean['issue_action'] == 'opened')) | \n",
    "    (df_issue_clean['type'] == 'IssueCommentEvent')][[\n",
    "    'actor_login', 'created_at', 'actor_repo_association', 'repo_name']].drop_duplicates().sort_values(\n",
    "    ['actor_login', 'repo_name', 'created_at']).dropna().reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "471fd1d8-5d32-4c77-9b4f-e8868f87d40f",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ranks = all_ranks.sort_values(['actor_login', 'repo_name', 'created_at'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0c88453e-0125-4b5d-a0f8-c8bea90109df",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "all_ranks_change = all_ranks.groupby(['actor_login', 'repo_name', 'actor_repo_association'])['created_at'].min().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "17529af2-666a-4848-a448-3c736be35111",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ranks_change['min_date'] = all_ranks_change.groupby(['actor_login', 'repo_name'])['created_at'].transform('min')\n",
    "all_ranks_change['max_date'] = all_ranks_change.groupby(['actor_login', 'repo_name'])['created_at'].transform('max')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2a7f76af-059a-4a37-babe-945c07b63240",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ranks_change['count'] = all_ranks_change.sort_values('created_at').groupby(['actor_login', 'repo_name']).cumcount()+1\n",
    "all_ranks_change['max_count'] = all_ranks_change.sort_values('created_at').groupby(['actor_login', 'repo_name']).transform('count')['count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "12f8a652-8e9a-4173-9f71-3dc76172db12",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# remove people who suddenly become NONE after being ranked\n",
    "all_ranks_change = all_ranks_change[~all_ranks_change.apply(lambda x: x['actor_repo_association'] == 'NONE' and x['count']>1 and x['count']<=x['max_count'],\n",
    "                                       axis = 1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "49ee6b84-fa69-4210-89e7-9b772023bd2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ranks_change = all_ranks_change.sort_values(\n",
    "    by = ['actor_login', 'repo_name','created_at'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "21488c2e-8c08-43fb-8555-5240f73a3375",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "all_ranks_change['interval_end'] = all_ranks_change.sort_values(\n",
    "    by = ['created_at']).groupby(\n",
    "    ['actor_login', 'repo_name'], sort = True)['created_at'].shift(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "eeba88b1-91ae-4ad3-a105-028c344b13c5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "all_ranks_change.loc[all_ranks_change[all_ranks_change['interval_end'].isna()].index,'interval_end'] = datetime(2023, 8, 31, 11, 59, 59)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "92f3b8e9-bd2f-4bc1-b17e-9a609b5e2522",
   "metadata": {},
   "outputs": [],
   "source": [
    "## some people are owners when they should not be...\n",
    "# remove if they're an owner when they're not supposed to be... \n",
    "all_ranks_change = all_ranks_change[(all_ranks_change['actor_repo_association'] != 'OWNER') |\n",
    "    (all_ranks_change.apply(lambda x: x['actor_login'] == x['repo_name'].split(\"/\")[0], axis = 1))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "959d8e38-dbf3-47bc-96e9-8b664cbed2a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ranks_change['interval_actor'] = all_ranks_change.apply(\n",
    "    lambda x: [x['actor_repo_association'], x['created_at'], x['interval_end']], axis = 1)\n",
    "grouped_rank = all_ranks_change.sort_values(['actor_login', 'repo_name', 'created_at']).groupby(\n",
    "    ['actor_login', 'repo_name', 'min_date', 'max_date']).agg({'interval_actor':list}).reset_index().set_index(['actor_login', 'repo_name'])\n",
    "grouped_rank['min_date'] = pd.to_datetime(grouped_rank['min_date'])\n",
    "grouped_rank['max_date'] = pd.to_datetime(grouped_rank['max_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "ba4c0dce-5bef-4e01-be0d-448f88bff785",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "grouped_rank = grouped_rank.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "0ca1d9e7-1216-4ff9-b856-d94476ddd77f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "grouped_rank['rank_max_date_actor'] = grouped_rank.apply(lambda x: [x['max_date'], x['interval_actor'][-1][0]], axis = 1)\n",
    "grouped_rank['rank_min_date_actor'] = grouped_rank.apply(\n",
    "    lambda x: [x['max_date'], 'NONE'] if x['actor_login'] != x['repo_name'].split(\"/\")[0] else [x['max_date'], 'OWNER'], axis = 1)\n",
    "grouped_rank['rank_max_date_issue'] = grouped_rank['rank_max_date_actor']\n",
    "grouped_rank['rank_min_date_issue'] = grouped_rank['rank_min_date_actor']\n",
    "grouped_rank['interval_issue'] = grouped_rank['interval_actor']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2021497f-fae6-483e-a4fb-b17e9610db93",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_issue_clean = pd.merge(df_issue_clean, grouped_rank[['actor_login', 'repo_name', 'rank_max_date_actor', 'rank_min_date_actor', 'interval_actor']],\n",
    "                          how = 'left', on = ['actor_login', 'repo_name'])\n",
    "\n",
    "df_issue_clean = pd.merge(df_issue_clean, grouped_rank[['actor_login', 'repo_name', 'rank_max_date_issue', 'rank_min_date_issue', 'interval_issue']].rename({\n",
    "    'actor_login':'issue_user_login'}, axis = 1),\n",
    "                          how = 'left', on = ['issue_user_login', 'repo_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "99cea56c-ebd1-47f7-8aa2-fcd32e9f744b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"df_issue_clean = df_issue_clean.drop(['rank_max_date_x','rank_min_date_x','interval_x'], axis = 1).rename({\\n    'rank_max_date_y':'rank_max_date',\\n    'rank_min_date_y':'rank_min_date',\\n    'interval_y':'interval'}, axis = 1)\""
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"df_issue_clean = df_issue_clean.drop(['rank_max_date_x','rank_min_date_x','interval_x'], axis = 1).rename({\n",
    "    'rank_max_date_y':'rank_max_date',\n",
    "    'rank_min_date_y':'rank_min_date',\n",
    "    'interval_y':'interval'}, axis = 1)\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "e84ffeff-949a-490b-a025-93323104eef2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_issue_clean['actor_repo_association_repaired'] = df_issue_clean['actor_repo_association']\n",
    "na_repo_association = df_issue_clean[['rank_max_date_actor', 'rank_min_date_actor', 'interval_actor']].dropna().index\n",
    "na_issue_repo_association = df_issue_clean[['rank_max_date_issue', 'rank_min_date_issue', 'interval_issue']].dropna().index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "deca1099-5e9b-4ce0-9918-4bb8fa226b82",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.24620620437992236 1246916\n"
     ]
    }
   ],
   "source": [
    "print(df_issue_clean['actor_repo_association_repaired'].isna().mean(),\n",
    "      df_issue_clean['actor_repo_association_repaired'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "b0e0d434-b7a6-437a-ba12-22c8c02df8f5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.17998320522546507 829895\n"
     ]
    }
   ],
   "source": [
    "print(df_issue_clean.loc[na_repo_association,'actor_repo_association_repaired'].isna().mean(),\n",
    "      df_issue_clean.loc[na_repo_association,'actor_repo_association_repaired'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "7af51f0f-04a8-4cbc-be45-35b7b6ab7753",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for col in ['rank_max_date_actor', 'rank_min_date_actor']:\n",
    "    df_issue_clean.loc[na_repo_association, col] = df_issue_clean.loc[na_repo_association, col].apply(\n",
    "        lambda x: [pd.to_datetime(x[0]), x[1]])\n",
    "for col in ['rank_max_date_issue', 'rank_min_date_issue']:\n",
    "    df_issue_clean.loc[na_issue_repo_association, col] = df_issue_clean.loc[na_issue_repo_association, col].apply(\n",
    "        lambda x: [pd.to_datetime(x[0]), x[1]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "eaa0488b-f03e-4f02-8a7a-baf26e951ea7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_issue_clean.loc[na_repo_association, 'actor_repo_association_repaired'] = df_issue_clean.loc[na_repo_association].apply(\n",
    "    lambda x: x['rank_min_date_actor'][1] if (x['created_at']<x['rank_min_date_actor'][0]) else \n",
    "    x['rank_max_date_actor'][1] if x['created_at']>=x['rank_max_date_actor'][0] else\n",
    "    [ele[0] for ele in x['interval_actor'] if x['created_at'] >= ele[1] and x['created_at'] < ele[2]], axis = 1)\n",
    "df_issue_clean.loc[na_repo_association, 'actor_repo_association_repaired'] = df_issue_clean.loc[na_repo_association, \n",
    "    'actor_repo_association_repaired'].apply(lambda x: x[0] if type(x) == list and len(x)>=1 else np.nan if type(x) == list else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "6756e0b1-fb26-4da8-9de9-5805e8031279",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_issue_clean.to_csv('df_issue_clean.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "834a161e-5b60-4264-9b68-b732d1739ad6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_issue_clean = pd.read_csv('df_issue_clean.csv', index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "92b92c22-bfba-4921-ac4f-132f00dcc300",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_issue_clean['issue_author_association_repaired'] = df_issue_clean['issue_author_association'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "89aee5f8-de6f-4dec-a7a4-e6d630db6dcd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_issue_clean.loc[na_issue_repo_association, 'issue_author_association_repaired'] = \\\n",
    "    df_issue_clean.loc[na_issue_repo_association].apply(\n",
    "    lambda x: x['rank_min_date_issue'][1] if (x['created_at']<x['rank_min_date_issue'][0]) else \n",
    "    x['rank_max_date_issue'][1] if x['created_at']>=x['rank_max_date_issue'][0] else\n",
    "    [ele[0] for ele in x['interval_issue'] if x['created_at'] >= ele[1] and x['created_at'] < ele[2]], axis = 1)\n",
    "df_issue_clean.loc[na_issue_repo_association, 'issue_author_association_repaired'] = df_issue_clean.loc[na_issue_repo_association,\n",
    "    'issue_author_association_repaired'].apply(lambda x: x[0] if type(x) == list and len(x)>=1 else np.nan if type(x) == list else x)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "ffb4e7c4-6b32-47fb-92cc-1e3603685365",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.08234167943688235 417021\n",
      "0.12564885233918563 636351\n"
     ]
    }
   ],
   "source": [
    "print(df_issue_clean['actor_repo_association_repaired'].isna().mean(),\n",
    "      df_issue_clean['actor_repo_association_repaired'].isna().sum())\n",
    "print(df_issue_clean['issue_author_association_repaired'].isna().mean(),\n",
    "      df_issue_clean['issue_author_association_repaired'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "d7b22518-946d-4903-af85-5bb1362e7be6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.0 0\n",
      "0.0 0\n"
     ]
    }
   ],
   "source": [
    "print(df_issue_clean.loc[na_repo_association,'actor_repo_association_repaired'].isna().mean(),\n",
    "      df_issue_clean.loc[na_repo_association,'actor_repo_association_repaired'].isna().sum())\n",
    "print(df_issue_clean.loc[na_issue_repo_association,'issue_author_association_repaired'].isna().mean(),\n",
    "      df_issue_clean.loc[na_issue_repo_association,'issue_author_association_repaired'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "97f9d2fb-696d-41c0-8dde-0dfda034283c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "ca6861df-4b14-4bd1-9ba1-1f26599ec223",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_export = df_issue_clean[['created_at', 'type', 'repo_id', 'repo_name', 'actor_login', 'actor_id', 'org_id', 'org_login',\n",
    "                'issue_user_login', 'issue_user_id','issue_action','issue_assignee','issue_assignees',\n",
    "                'issue_comment_count','issue_closed_at','issue_number','issue_state', \n",
    "                'issue_pull_request','actor_repo_association_repaired','issue_author_association_repaired']]\n",
    "df_export['issue_comment_count'] = pd.to_numeric(df_export['issue_comment_count'],errors = 'coerce')\n",
    "#df_export = reduce_mem_usage(df_export)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "6b74ad02-d5d0-40de-a2f7-ca55e21a13c5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5064519 entries, 0 to 5064518\n",
      "Data columns (total 20 columns):\n",
      " #   Column                             Dtype         \n",
      "---  ------                             -----         \n",
      " 0   created_at                         datetime64[ns]\n",
      " 1   type                               object        \n",
      " 2   repo_id                            int64         \n",
      " 3   repo_name                          object        \n",
      " 4   actor_login                        object        \n",
      " 5   actor_id                           int64         \n",
      " 6   org_id                             float64       \n",
      " 7   org_login                          object        \n",
      " 8   issue_user_login                   object        \n",
      " 9   issue_user_id                      float64       \n",
      " 10  issue_action                       object        \n",
      " 11  issue_assignee                     object        \n",
      " 12  issue_assignees                    object        \n",
      " 13  issue_comment_count                float64       \n",
      " 14  issue_closed_at                    object        \n",
      " 15  issue_number                       float64       \n",
      " 16  issue_state                        object        \n",
      " 17  issue_pull_request                 object        \n",
      " 18  actor_repo_association_repaired    object        \n",
      " 19  issue_author_association_repaired  object        \n",
      "dtypes: datetime64[ns](1), float64(4), int64(2), object(13)\n",
      "memory usage: 5.2 GB\n"
     ]
    }
   ],
   "source": [
    "df_export.info(memory_usage = 'deep')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "b351168d-f745-4565-94e6-5139d071e037",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_export.to_parquet('data/merged_data/issue_data.parquet', engine = 'pyarrow')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "ec8fa0bc-4345-4ccb-89c9-1dcdf362ad7c",
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "'break' outside loop (668683560.py, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  Cell \u001b[0;32mIn[48], line 1\u001b[0;36m\u001b[0m\n\u001b[0;31m    break\u001b[0m\n\u001b[0m    ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m 'break' outside loop\n"
     ]
    }
   ],
   "source": [
    "break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "351110c4-9f88-4e8d-be8d-e1d818f4e36b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Some Statistics on Issues\n",
    "uq_issue_comments = df_issue_clean[df_issue_clean['type'] == 'IssueCommentEvent']['key'].unique().tolist()\n",
    "uq_issues = df_issue_clean[df_issue_clean['type'] == 'IssuesEvent']['key'].unique().tolist()\n",
    "OriginalDataStatistics.loc['Unique Issues (from comments data)', 'count'] = len(uq_issue_comments)\n",
    "OriginalDataStatistics.loc['Unique Issues (from issues data)', 'count'] = len(uq_issues)\n",
    "uq_issue_comments.extend(uq_issues)\n",
    "uq_issue_comments = list(set(uq_issue_comments))\n",
    "OriginalDataStatistics.loc['Unique Issues (from all data)']  = len(uq_issue_comments)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1df52cf-61f1-47e1-b19b-a46ceb9793ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "OriginalDataStatistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fa5df7c-a376-446c-9ce6-c3e4997506e4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# total number of issues \n",
    "DataDescriptives.loc[0, 'Total # of Issues'] = df_issue_clean['key'].unique().shape[0]\n",
    "nunique_issues = df_issue_clean.groupby('repo_name').agg({'issue_number':'nunique'})\n",
    "DataDescriptives.loc[0, 'Total # of Projects'] = nunique_issues.shape[0]\n",
    "DataDescriptives.loc[0, 'Average # of Issues/Project'] = nunique_issues.mean()[0]\n",
    "DataDescriptives.loc[0,\"Median # of Issues/Project\"] = nunique_issues.median()[0]\n",
    "DataDescriptives.style.set_caption(\"Data Descriptives\")\n",
    "DataDescriptives.round(2).to_markdown('descriptives/issues/aggregate_statistics.md')\n",
    "DataDescriptives"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bcd0017c-6c20-4fe6-a64c-d150d1705667",
   "metadata": {},
   "outputs": [],
   "source": [
    "# PLOT ISSUE COUNT PER REPOSITORY\n",
    "# Initialize layout\n",
    "fig, ax = plt.subplots()\n",
    "#plot\n",
    "ax.hist(nunique_issues['issue_number'], bins=20, edgecolor=\"black\")\n",
    "ax.set_title('Number of issues/repository')\n",
    "ax.set_xlabel('Number of Issues')\n",
    "ax.set_ylabel('Number of Repositories')\n",
    "ax.set_yscale('log')\n",
    "plt.savefig('descriptives/issues/issue_repository.png')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66454126-caa2-4ea2-aefa-4c3607ae14f4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# PROPORTION OF ALL ISSUES CONTAINED IN top 300 of repos with most issues\n",
    "(nunique_issues/nunique_issues.sum()).sort_values('issue_number', ascending = False).head(300).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f23ae25-7aa2-4b59-8d7a-a5a7b0a688e8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# LATEST STATUS FOR ISSUE\n",
    "df_issue_clean['created_at'] = pd.to_datetime(df_issue_clean['created_at'])\n",
    "df_issue_clean = df_issue_clean.sort_values('created_at', ascending = False)\n",
    "df_issue_latest_status = df_issue_clean[~df_issue_clean.duplicated(['repo_id', 'issue_number'])]\n",
    "print(df_issue_latest_status['issue_action'].rename('Issue Latest Status').value_counts(normalize = True).round(3).to_markdown())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "578bbcd7-4acb-42e1-8f56-1bbd80e16174",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "closed_issues = df_issue_latest_status[df_issue_latest_status['issue_action'] == 'closed']['key'].tolist()\n",
    "# includes open, reopened\n",
    "open_issues = df_issue_latest_status[df_issue_latest_status['issue_action'] != 'closed']['key'].tolist()\n",
    "\n",
    "status_dates = df_issue_clean.groupby(['repo_name', 'issue_number', 'key', 'issue_action']).agg({'created_at': ['min', 'max']}).reset_index()\n",
    "status_dates.columns = ['repo_name', 'issue_number', 'key', 'issue_action', 'min_date', 'max_date']\n",
    "status_dates = status_dates.pivot(index = ['repo_name', 'issue_number', 'key'], columns = 'issue_action', values = ['min_date' ,'max_date']).reset_index()\n",
    "status_dates.columns = ['repo_name', 'issue_number', 'key', 'closed_min_date', 'opened_min_date', 'reopened_min_date', \n",
    "                        'closed_max_date', 'opened_max_date', 'reopened_max_date']\n",
    "# remove ones that do not have an opened_min_date\n",
    "status_dates = status_dates[~status_dates['opened_min_date'].isna()]\n",
    "# ones that are closed - time open: use earliest open date minus latest close date\n",
    "closed_ind = status_dates[status_dates['key'].isin(closed_issues)].index\n",
    "status_dates.loc[closed_ind, 'open_time'] = status_dates.loc[closed_ind, 'closed_max_date'] - \\\n",
    "    status_dates.loc[closed_ind, 'opened_min_date']\n",
    "# ones that are still open - time open: use 8/31/2023 - opened date\n",
    "open_ind = status_dates[status_dates['key'].isin(open_issues)].index\n",
    "last_date = datetime(2023, 8, 31, 11, 59, 59)\n",
    "status_dates.loc[open_ind, 'open_time'] = last_date - status_dates.loc[open_ind, 'opened_min_date']\n",
    "\n",
    "# type of issue\n",
    "status_dates.loc[closed_ind, 'status'] = 'closed'\n",
    "status_dates.loc[open_ind, 'status'] = 'open'\n",
    "status_dates['open_time_days'] = status_dates['open_time'].apply(lambda x: x.days)\n",
    "\n",
    "### PLOT EXCLUDES ISSUES THAT WE DO NOT HAVE OPENED/CLOSED dates for\n",
    "# Initialize layout\n",
    "fig, ax = plt.subplots(1, 2, figsize = (10, 4))\n",
    "#plot\n",
    "status_dates.hist('open_time_days', by = 'status', bins=20, edgecolor=\"black\", ax = ax)\n",
    "fig.suptitle('Histogram of Days Open for Issues')\n",
    "for a in ax:\n",
    "    a.set_ylabel('Number of Issues')\n",
    "    a.set_xlabel('Days Open')\n",
    "    #a.set_yscale('log')\n",
    "ax[0].set_title('Closed Issues (as of 8/31/2023)')\n",
    "ax[1].set_title('Open Issues (as of 8/31/2023)')\n",
    "fig.tight_layout(rect=[0, 0.03, 1, 0.95])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c34f9a08-94b8-40dd-89d2-3fefce4afcd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def lessThanGroups(status_dates):\n",
    "    og_shape = status_dates.shape[0]\n",
    "    one_day = status_dates[status_dates['open_time_days']<=1].shape[0]/og_shape\n",
    "    one_week = status_dates[status_dates['open_time_days']<=7].shape[0]/og_shape\n",
    "    one_month = status_dates[status_dates['open_time_days']<=30].shape[0]/og_shape\n",
    "    six_month = status_dates[status_dates['open_time_days']<=180].shape[0]/og_shape\n",
    "    one_year = status_dates[status_dates['open_time_days']<=365].shape[0]/og_shape\n",
    "    g_one_year = status_dates[status_dates['open_time_days']>365].shape[0]/og_shape\n",
    "    return [one_day, one_week, one_month, six_month, one_year, g_one_year, og_shape]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa7d0ffe-c906-40eb-8e93-fb8119250a31",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_open_days = pd.DataFrame([lessThanGroups(status_dates[status_dates['status'] == 'closed']),\n",
    "              lessThanGroups(status_dates[status_dates['status'] == 'open'])]).round(2)\n",
    "df_open_days.index = ['closzed issues', 'open issues']\n",
    "df_open_days.columns = ['open $\\leq$ 1 day', 'open $\\leq$ 1 week', 'open $\\leq$ 1 month', 'open $\\leq$ 6 months', \\\n",
    "                        'open $\\leq$ 1 year', 'open $>$ 1 year', '# of issues']\n",
    "df_open_days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d2ebb4b-3598-4664-839a-9bf51f7c1282",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# TEST WHAT HAPPENS WHEN WE \"APPROXIMATE\" \n",
    "closed_issues = df_issue_latest_status[df_issue_latest_status['issue_state'] == 'closed']['key'].tolist()\n",
    "# includes open, reopened\n",
    "open_issues = df_issue_latest_status[df_issue_latest_status['issue_state'] != 'closed']['key'].tolist()\n",
    "\n",
    "status_dates = df_issue_clean.groupby(['repo_name', 'issue_number', 'key', 'issue_state']).agg({'created_at': ['min', 'max']}).reset_index()\n",
    "status_dates.columns = ['repo_name', 'issue_number', 'key', 'issue_state', 'min_date', 'max_date']\n",
    "status_dates = status_dates.pivot(index = ['repo_name', 'issue_number', 'key'], columns = 'issue_state', values = ['min_date' ,'max_date']).reset_index()\n",
    "status_dates.columns = ['repo_name', 'issue_number', 'key', 'closed_min_date', 'opened_min_date', \n",
    "                        'closed_max_date', 'opened_max_date']\n",
    "# remove ones that do not have an opened_min_date\n",
    "status_dates = status_dates[~status_dates['opened_min_date'].isna()]\n",
    "# ones that are closed - time open: use earliest open date minus latest close date\n",
    "closed_ind = status_dates[status_dates['key'].isin(closed_issues)].index\n",
    "status_dates.loc[closed_ind, 'open_time'] = status_dates.loc[closed_ind, 'closed_max_date'] - \\\n",
    "    status_dates.loc[closed_ind, 'opened_min_date']\n",
    "# ones that are still open - time open: use 8/31/2023 - opened date\n",
    "open_ind = status_dates[status_dates['key'].isin(open_issues)].index\n",
    "last_date = datetime(2023, 8, 31, 11, 59, 59)\n",
    "status_dates.loc[open_ind, 'open_time'] = last_date - status_dates.loc[open_ind, 'opened_min_date']\n",
    "\n",
    "# type of issue\n",
    "status_dates.loc[closed_ind, 'status'] = 'closed'\n",
    "status_dates.loc[open_ind, 'status'] = 'open'\n",
    "status_dates['open_time_days'] = status_dates['open_time'].apply(lambda x: x.days)\n",
    "\n",
    "### PLOT EXCLUDES ISSUES THAT WE DO NOT HAVE OPENED/CLOSED dates for\n",
    "# Initialize layout\n",
    "fig, ax = plt.subplots(1, 2, figsize = (10, 4))\n",
    "#plot\n",
    "status_dates.hist('open_time_days', by = 'status', bins=20, edgecolor=\"black\", ax = ax)\n",
    "fig.suptitle('Histogram of Days Open for Issues')\n",
    "for a in ax:\n",
    "    a.set_ylabel('Number of Issues')\n",
    "    a.set_xlabel('Days Open')\n",
    "    #a.set_yscale('log')\n",
    "ax[0].set_title('Closed Issues (as of 8/31/2023)')\n",
    "ax[1].set_title('Open Issues (as of 8/31/2023)')\n",
    "fig.tight_layout(rect=[0, 0.03, 1, 0.95])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b33a0117-7f38-4cac-9b1b-586fcc2982ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_open_days = pd.DataFrame([lessThanGroups(status_dates[status_dates['status'] == 'closed']),\n",
    "              lessThanGroups(status_dates[status_dates['status'] == 'open'])]).round(2)\n",
    "df_open_days.index = ['closed issues', 'open issues']\n",
    "df_open_days.columns = ['open $\\leq$ 1 day', 'open $\\leq$ 1 week', 'open $\\leq$ 1 month', 'open $\\leq$ 6 months', \\\n",
    "                        'open $\\leq$ 1 year', 'open $>$ 1 year', '# of issues']\n",
    "df_open_days"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a417ae6-7f53-4109-bc69-3fd32e086a19",
   "metadata": {},
   "source": [
    "## Whose Opening Issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "569f838d-60fc-489a-962e-20a12962b5a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "opened_issues = df_issue_clean.sort_values(['key', 'created_at'])[['key', 'issue_author_association_repaired']].dropna().drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1f49579-6d5a-4f7e-adbd-6fe36163e2f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def groupActions(df_issue_clean):\n",
    "    closed_issues = df_issue_clean[df_issue_clean['issue_action'] == 'closed'].sort_values(\n",
    "    ['key', 'created_at'])[['key', 'issue_author_association_repaired']].drop_duplicates()\n",
    "    opened_issues = df_issue_clean.sort_values(['key', 'created_at'])[['key', 'issue_author_association_repaired']].dropna().drop_duplicates()\n",
    "    closed_issues['status'] = 'closed'\n",
    "    opened_issues['status'] = 'opened'\n",
    "    all_issues = pd.concat([closed_issues, opened_issues])\n",
    "    group_actions = all_issues.groupby('status')['issue_author_association_repaired'].value_counts(\n",
    "        normalize = True).reset_index().pivot(index = 'status', columns = 'issue_author_association_repaired', values = 'proportion')\n",
    "    group_actions = pd.concat([group_actions, all_issues['status'].value_counts()], axis = 1)\n",
    "    group_actions.columns.name = ''\n",
    "    group_actions = group_actions.loc[['closed', 'opened']]\n",
    "    group_actions.index = ['Closing', 'Opening']\n",
    "    return group_actions[['NONE', 'CONTRIBUTOR', 'MEMBER', 'COLLABORATOR', 'OWNER', 'count']], all_issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "464ef266-a2fa-4b9b-9f80-37f5dd507fd8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tb, all_issues = groupActions(df_issue_clean)\n",
    "tb.round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d8294f2-6f64-4086-900e-ff82ec33bbff",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "all_issues['repo'] = all_issues['key'].parallel_apply(lambda x: x.split(\"_\")[0])\n",
    "# percent opened by owner vs. # of issues\n",
    "# percent opened by NONE vs. # of issues\n",
    "all_issues['opened_owner'] = all_issues['issue_author_association_repaired'] == 'OWNER'\n",
    "all_issues['opened_none'] = all_issues['issue_author_association_repaired'] == 'NONE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9697b889-db85-478d-9ccf-69af10df4ddd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "grouped_all_issues = all_issues.groupby('repo').agg({'opened_owner': ['count', 'mean'],'opened_none':['mean']})\n",
    "grouped_all_issues.columns = ['issue_count', 'opened_owner_mean', 'opened_none_mean']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52710cf8-8686-40ac-b218-b009c53700c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "### PLOT EXCLUDES ISSUES THAT WE DO NOT HAVE OPENED/CLOSED dates for\n",
    "# Initialize layout\n",
    "fig, ax = plt.subplots(2, 2, figsize = (10, 8))\n",
    "#plot\n",
    "ax[0,0].scatter(x = grouped_all_issues['issue_count'], y = grouped_all_issues['opened_owner_mean'])\n",
    "ax[0,1].scatter(x = grouped_all_issues['issue_count'], y = grouped_all_issues['opened_none_mean'])\n",
    "ax[1,0].scatter(x = grouped_all_issues.query('issue_count>1000')['issue_count'], \n",
    "                y = grouped_all_issues.query('issue_count>1000')['opened_owner_mean'])\n",
    "ax[1,1].scatter(x = grouped_all_issues.query('issue_count>1000')['issue_count'], \n",
    "                y = grouped_all_issues.query('issue_count>1000')['opened_none_mean'])\n",
    "\n",
    "for i in range(2):\n",
    "    ax[i, 0].set_ylabel('% of Issues Opened by Owner')\n",
    "    ax[i, 1].set_ylabel('% of Issues Opened by Unaffiliated Contributors')\n",
    "\n",
    "    ax[i, 0].set_xlabel('Number of Issues')\n",
    "    ax[i, 1].set_xlabel('Number of Issues')\n",
    "    ax[1, i].set_title('Min 1000 Issues')\n",
    "fig.suptitle(\"% of Issues Opened by Group vs. Issue Count\")\n",
    "fig.tight_layout(rect=[0, 0.03, 1, 0.95])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4f6ece9-4c83-46fd-b3ee-89738d24db45",
   "metadata": {},
   "outputs": [],
   "source": [
    "tb_ray, all_issues_ray = groupActions(df_issue_clean[df_issue_clean['repo_name'] == 'ray-project/ray'])\n",
    "tb_ray.round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "353b38e3-3e56-4d8b-932b-e825fa184ba0",
   "metadata": {},
   "source": [
    "# Existence of Hierarchy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f58d3528-a254-458d-b238-e85882e4114f",
   "metadata": {},
   "source": [
    "Activity is defined as opening, closing and commenting on an issue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5043f6b1-bca8-45de-9d90-c5b8e5891ec6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "explicit_open_keys = df_issue_clean.query('issue_action == \"opened\"')['key'].unique().tolist()\n",
    "remaining_issues = df_issue_clean[~df_issue_clean['key'].isin(explicit_open_keys)][['key', 'issue_author_association']].drop_duplicates()['issue_author_association']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fc7ad7b-47b1-4490-8e2e-6e8723d48086",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_generated_activity_base(df_issue_clean):\n",
    "    rank_identifying_cols = ['actor_login', 'repo_name', 'actor_repo_association_repaired']\n",
    "    generated_activity_base = pd.concat([\n",
    "        df_issue_clean[['key', 'issue_author_association_repaired']].drop_duplicates()['issue_author_association_repaired'].value_counts(),\n",
    "        df_issue_clean.query('issue_action == \"closed\"')['actor_repo_association_repaired'].value_counts(),\n",
    "        df_issue_clean.query('issue_action == \"closed\" and actor_login == issue_user_login')['actor_repo_association_repaired'].value_counts(),\n",
    "        df_issue_clean.query('issue_action == \"closed\" and actor_login != issue_user_login')['actor_repo_association_repaired'].value_counts(),\n",
    "        df_issue_clean.query('type == \"IssueCommentEvent\"')['actor_repo_association_repaired'].value_counts(),\n",
    "        df_issue_clean.query('type == \"IssueCommentEvent\" and actor_login == issue_user_login')['actor_repo_association_repaired'].value_counts(),\n",
    "        df_issue_clean.query('type == \"IssueCommentEvent\" and actor_login != issue_user_login')['actor_repo_association_repaired'].value_counts(),\n",
    "        df_issue_clean[rank_identifying_cols].drop_duplicates()['actor_repo_association_repaired'].value_counts(),\n",
    "    ], axis = 1).round(2).loc[\n",
    "        ['NONE', 'CONTRIBUTOR', 'MEMBER', 'COLLABORATOR', 'OWNER']]\n",
    "    generated_activity_base.columns = ['# of Opened Issues', '# of Closed Issues', '# of Closed Issues (Own Issue)', '# of Closed Issues (Others Issue)', \n",
    "                                       '# of Comments', '# of Comments (Own Issue)', '# of Comments (Others Issue)', '# of Individiuals']\n",
    "    generated_activity_base['Amount of Activity'] = generated_activity_base.apply(\n",
    "        lambda x: x[['# of Opened Issues', '# of Closed Issues', '# of Comments']].sum(), axis = 1)\n",
    "    generated_activity_base['Proportion'] = generated_activity_base['Amount of Activity']/generated_activity_base['Amount of Activity'].sum()\n",
    "    generated_activity_base = generated_activity_base[['Amount of Activity', 'Proportion', '# of Opened Issues', '# of Closed Issues', \n",
    "                                                       '# of Closed Issues (Own Issue)', '# of Closed Issues (Others Issue)', \n",
    "                                                       '# of Comments', '# of Comments (Own Issue)', '# of Comments (Others Issue)', '# of Individiuals']]\n",
    "    return generated_activity_base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "824be717-270b-454c-a67a-4744a1b030e8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "generated_activity_base = make_generated_activity_base(df_issue_clean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "054361e5-0f93-4b6e-9431-44a654c4f840",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "generated_activity = generated_activity_base.copy()\n",
    "generated_activity['Activity/Individual'] = generated_activity['Amount of Activity']/generated_activity['# of Individiuals']\n",
    "for col in ['Opened Issues', 'Closed Issues', 'Closed Issues (Own Issue)',  'Closed Issues (Others Issue)',\n",
    "            'Comments', 'Comments (Own Issue)', 'Comments (Others Issue)']:\n",
    "    generated_activity[f'{col}/ Individual'] = generated_activity.apply(\n",
    "        lambda x: f\"{x[f'# of {col}']/x['# of Individiuals']:.2f}\\n({100*x[f'# of {col}']/x['Amount of Activity']:.2f}%)\", axis = 1)\n",
    "for col in ['# of Opened Issues', '# of Closed Issues', '# of Closed Issues (Own Issue)', \n",
    "            '# of Closed Issues (Others Issue)', '# of Comments', '# of Comments (Own Issue)', \n",
    "            '# of Comments (Others Issue)', '# of Individiuals']:\n",
    "    generated_activity[col] = generated_activity[col].apply(lambda x: f\"{x}\\n({x/generated_activity[col].sum():.2f})\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02cebca5-f609-4a50-978b-1c93440aac9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pretty_print(generated_activity.round(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b2f7dfc-a832-4f61-891c-625ed62e914b",
   "metadata": {},
   "source": [
    "Now, I'm going to weight the individual count by how long they were that rank for. I do this because I know in some weird instances, we see people obtain ranks for very short periods of time. This will allow me to more precisely understand how much activity an individual contributed to. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a8dc1ed-4b36-4231-924f-5df3f340e1d4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "all_users = pd.concat([df_issue_clean[['created_at', 'actor_login', 'repo_name']].rename({'actor_login':'user'}, axis = 1),\n",
    "                       df_issue_clean[['created_at', 'issue_user_login', 'repo_name']].rename({'issue_user_login':'user'}, axis = 1)])\n",
    "earliest = all_users.sort_values('created_at').drop_duplicates(['user', 'repo_name']).rename({'created_at': 'earliest_date'}, axis = 1)\n",
    "latest = all_users.sort_values('created_at', ascending = False).drop_duplicates(['user', 'repo_name']).rename({'created_at': 'latest_date'}, axis = 1)\n",
    "user_dates = pd.merge(latest, earliest, on = ['user', 'repo_name'], how = 'outer')[['user', 'repo_name', 'earliest_date', 'latest_date']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a41b0578-425e-438f-bfdc-385c551db01d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "user_dates['key'] = user_dates['user']+\"_\"+user_dates['repo_name']\n",
    "early_dict = user_dates.set_index('key')['earliest_date'].to_dict()\n",
    "last_dict = user_dates.set_index('key')['latest_date'].to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "545ebbf4-ad3f-4440-806b-3417718f1b1f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from pandas import Timestamp\n",
    "for col in grouped_rank.columns[4:10]:\n",
    "    print(col)\n",
    "    grouped_rank[col] = grouped_rank[col].parallel_apply(lambda x: eval(x, {'datetime': datetime,\n",
    "                                                                            'Timestamp': Timestamp}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85612a11-062a-4dbf-8f99-0df52ffc3f66",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def make_time_spent(grouped_rank):\n",
    "    time_spent = pd.DataFrame(grouped_rank['final_interval'].parallel_apply(\n",
    "        lambda x: [[ele[0], pd.to_datetime(ele[2])-pd.to_datetime(ele[1])] for ele in x]).explode())\n",
    "    time_spent['rank'] = time_spent['final_interval'].apply(lambda x: x[0])\n",
    "    time_spent['time spent'] = time_spent['final_interval'].apply(lambda x: x[1])\n",
    "    time_spent['Time Spent Seconds'] = time_spent['time spent'].apply(lambda x: x.total_seconds())\n",
    "    time_spent['Time Spent (Prop.)'] = time_spent['Time Spent Seconds']/time_spent['Time Spent Seconds'].sum()\n",
    "    time_spent['Time Spent (Worker-Years)'] = time_spent['Time Spent Seconds']/(86400*30*12)\n",
    "\n",
    "    return time_spent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5ae719b-f168-43b9-bedc-475c13a36d42",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "grouped_rank['key'] = grouped_rank['actor_login']+\"_\"+grouped_rank['repo_name']\n",
    "grouped_rank['final_interval'] = grouped_rank.parallel_apply(\n",
    "    lambda x: [[x['interval_actor'][0][0], early_dict[x['key']], last_dict[x['key']]]] if len(x['interval_actor']) == 1 else \n",
    "    [[x['interval_actor'][0][0], early_dict[x['key']], x['interval_actor'][0][2]]] + x['interval_actor'][1:-1] + \\\n",
    "    [[x['interval_actor'][-1][0], x['interval_actor'][-1][1], last_dict[x['key']]]], axis = 1)\n",
    "\n",
    "time_spent = make_time_spent(grouped_rank)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "613e6dd5-a53a-4744-a231-725065291232",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_generated_activity_time(generated_activity_base, time_spent):\n",
    "    generated_activity_time = pd.concat([generated_activity_base, time_spent.groupby('rank')['Time Spent (Worker-Years)'].sum()], axis = 1)\n",
    "    generated_activity_time['Activity/Worker-Year'] = generated_activity_time['Amount of Activity']/generated_activity_time['Time Spent (Worker-Years)']\n",
    "    for col in ['Opened Issues', 'Closed Issues', 'Closed Issues (Own Issue)',  'Closed Issues (Others Issue)',\n",
    "                'Comments', 'Comments (Own Issue)', 'Comments (Others Issue)']:\n",
    "        generated_activity_time[f'{col}/Time Spent'] = generated_activity_time.apply(\n",
    "            lambda x: f\"{x[f'# of {col}']/x['Time Spent (Worker-Years)']:.2f}\\n ({100*x[f'# of {col}']/x['Amount of Activity']:.2f}%)\", axis = 1)\n",
    "    \n",
    "    \n",
    "    for col in ['# of Opened Issues', '# of Closed Issues', '# of Closed Issues (Own Issue)', \n",
    "                '# of Closed Issues (Others Issue)', '# of Comments', '# of Comments (Own Issue)', \n",
    "                '# of Comments (Others Issue)', '# of Individiuals']:\n",
    "        generated_activity_time[col] = generated_activity_time[col].apply(lambda x: f\"{x:,}\\n({x/generated_activity_time[col].sum():.2f})\")\n",
    "\n",
    "    generated_activity_time['Amount of Activity'] = generated_activity_time['Amount of Activity'].apply(lambda x: f\"{x:,}\")\n",
    "    generated_activity_time['Proportion'] = generated_activity_time['Proportion'].round(2)\n",
    "\n",
    "    generated_activity_time['Time Spent (Worker-Years)'] = generated_activity_time['Time Spent (Worker-Years)'].apply(\n",
    "        lambda x: f\"{x:,.2f}\\n({x/generated_activity_time['Time Spent (Worker-Years)'].sum():.2f})\")\n",
    "    generated_activity_time['Activity/Worker-Year'] = generated_activity_time['Activity/Worker-Year'].round(2)\n",
    "\n",
    "    return generated_activity_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43b77be9-d9c6-4e5d-85e7-3ba8b85eda92",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "generated_activity_time = make_generated_activity_time(generated_activity_base, time_spent)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbfa3f00-5782-4fb1-83bd-d0b3db86f0dd",
   "metadata": {},
   "source": [
    "\n",
    "main difference between this and a previous analysis is that owners become more involved if you consider the \"intensity\" of \n",
    "someone's actions\n",
    "\n",
    "ie: how much work someone does within a given time period\n",
    "\n",
    "I prefer this one because it makes individuals more comparable within a particular time period\n",
    "\n",
    "**most important findings:**\n",
    "1) Large Disparity in Activity/Individual - Higher ranked individuals do more things in the same relative time period\n",
    "2) Individuals progressively spend \n",
    "    a) less of their time opening issues (asking questions/flagging problems)\n",
    "    b) more of their time closing issues (administrative work). In particular its notable how many issues owners close, \n",
    "    especially given that they do less things. Owners close A LOT of issues..\n",
    "3) As individuals rise in rank,\n",
    "    a) they spend more time commenting on issues, especially other people's issues\n",
    "    b) they don't spend that much time commenting on their own issues - why is that?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcdb8ef8-2f08-42fc-809c-203b69b83188",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pretty_print(generated_activity_time.round(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16927abc-d1c4-4301-93d1-682aded993ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "pretty_print(generated_activity_time[['Amount of Activity','Proportion','# of Opened Issues','# of Closed Issues',\n",
    "                         '# of Closed Issues (Own Issue)','# of Closed Issues (Others Issue)','# of Comments','# of Comments (Own Issue)',\n",
    "                         '# of Comments (Others Issue)']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "641be1f0-0d7e-423a-98a4-3061ef8ba516",
   "metadata": {},
   "outputs": [],
   "source": [
    "pretty_print(generated_activity_time[['Amount of Activity','Time Spent (Worker-Years)','Activity/Worker-Year','Opened Issues/Time Spent',\n",
    "                                      'Closed Issues/Time Spent','Closed Issues (Own Issue)/Time Spent','Closed Issues (Others Issue)/Time Spent','Comments/Time Spent',\n",
    "                                      'Comments (Own Issue)/Time Spent','Comments (Others Issue)/Time Spent']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cd5ce2b-651f-4bf3-a7cf-459bf1012c6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# how does it vary by repository size?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb25b7ed-1d15-4552-877b-3f055415903e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "repo_contributors = df_issue_clean[['repo_name', 'actor_login']].drop_duplicates()['repo_name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25644cad-7c13-433e-8cb7-4e98d6ecf230",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize = (4, 3))\n",
    "pd.Series(repo_contributors.values).hist(ax = ax, bins = 30)\n",
    "ax.set_title('Histogram of # of Contributors to Repositories')\n",
    "ax.set_xlabel('Number of Unique Contributors')\n",
    "ax.set_ylabel('Number of Repositories')\n",
    "ax.set_yscale('log')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79eff479-763d-44d9-9ebe-533c4c760e3e",
   "metadata": {},
   "source": [
    "## Time for More Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f29ae6f-4889-4e7c-9b7a-b793e19b14db",
   "metadata": {},
   "source": [
    "The main difference between this and a previous analysis is that owners become more involved if you consider the \"intensity\" of \n",
    "someone's actions (how much work someone does within a given time period). I prefer this one because it makes individuals more comparable within a particular time period\n",
    "\n",
    "**most important findings:**\n",
    "1) Large Disparity in Activity/Individual - Higher ranked individuals do more things in the same relative time period\n",
    "2) Individuals progressively spend\n",
    "   \n",
    "    a) less of their time opening issues (asking questions/flagging problems)\n",
    "   \n",
    "    b) more of their time closing issues (administrative work). In particular its notable how many issues owners close, \n",
    "    especially given that they do less things. Owners close A LOT of issues..\n",
    "4) As individuals rise in rank,\n",
    "\n",
    "    a) they spend more time commenting on issues, especially other people's issues\n",
    "\n",
    "    b) they don't spend that much time commenting on their own issues - why is that?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7964cd20-0828-49f0-9665-020cbe37e764",
   "metadata": {},
   "source": [
    "1) Why do they do more things?\n",
    "\n",
    "2) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "975a18fb-6436-4050-a140-8c4b48fb22b8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pretty_print(generated_activity_time.round(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "019dde92-6716-4c00-b35d-fd37880ccdfc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "organization_list = df_issue_clean['org_login'].dropna().unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "466d44e0-4eac-490e-a5ce-d06bb3382147",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "watch_lst = glob.glob('data/github_clean/github_data_pre_18/watchEvent*')\n",
    "watch_lst.extend(glob.glob('data/github_clean/filtered_github_data/watchEvent*'))\n",
    "watch_data = pd.concat([pd.read_csv(file, usecols = ['repo_name'], engine = 'pyarrow') for file in watch_lst])\n",
    "watch_data = watch_data.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "762b83e0-ea53-4b90-b760-780a33316285",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "fork_lst = glob.glob('data/github_clean/github_data_pre_18/forkEvent*')\n",
    "fork_lst.extend(glob.glob('data/github_clean/filtered_github_data/forkEvent*'))\n",
    "fork_data = pd.concat([pd.read_csv(file, usecols = ['repo_name'], engine = 'pyarrow') for file in fork_lst])\n",
    "fork_data = fork_data.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8ec6885-bfc1-4822-802f-b1a76b2fc78a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculateTimeSpent(grouped_rank):\n",
    "    time_spent = pd.DataFrame(grouped_rank['final_interval'].parallel_apply(\n",
    "        lambda x: [[ele[0], pd.to_datetime(ele[2])-pd.to_datetime(ele[1])] for ele in x]).explode())\n",
    "    time_spent['rank'] = time_spent['final_interval'].apply(lambda x: x[0])\n",
    "    time_spent['time spent'] = time_spent['final_interval'].apply(lambda x: x[1])\n",
    "    time_spent['Time Spent Seconds'] = time_spent['time spent'].apply(lambda x: x.total_seconds())\n",
    "    time_spent['Time Spent (Prop.)'] = time_spent['Time Spent Seconds']/time_spent['Time Spent Seconds'].sum()\n",
    "    time_spent['Time Spent (Worker-Years)'] = time_spent['Time Spent Seconds']/(86400*30*12)\n",
    "    return time_spent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b9adf1a-3231-442e-a1cb-f6bfde3238f9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "time_spent_org = calculateTimeSpent(grouped_rank[grouped_rank['repo_name'].apply(lambda x: x.split(\"/\")[0] in organization_list)])\n",
    "time_spent_noorg = calculateTimeSpent(grouped_rank[~grouped_rank['repo_name'].apply(lambda x: x.split(\"/\")[0] in organization_list)])\n",
    "time_spent_many_contributor = calculateTimeSpent(grouped_rank[grouped_rank['repo_name'].isin(repo_contributors.head(300).index)])\n",
    "time_spent_no_many = calculateTimeSpent(grouped_rank[~grouped_rank['repo_name'].isin(repo_contributors.head(300).index)])\n",
    "time_spent_most_activity = calculateTimeSpent(grouped_rank[grouped_rank['repo_name'].isin(\n",
    "    df_issue_clean['repo_name'].value_counts().head(300).index)])\n",
    "time_spent_not_most = calculateTimeSpent(grouped_rank[~grouped_rank['repo_name'].isin(\n",
    "    df_issue_clean['repo_name'].value_counts().head(300).index)])\n",
    "time_spent_most_watch = calculateTimeSpent(grouped_rank[grouped_rank['repo_name'].isin(\n",
    "    watch_data.reset_index()['repo_name'].head(300).tolist())])\n",
    "time_spent_not_watch = calculateTimeSpent(grouped_rank[~grouped_rank['repo_name'].isin(\n",
    "    watch_data.reset_index()['repo_name'].head(300).tolist())])\n",
    "time_spent_most_fork = calculateTimeSpent(grouped_rank[grouped_rank['repo_name'].isin(\n",
    "    fork_data.reset_index()['repo_name'].head(300).tolist())])\n",
    "time_spent_not_fork = calculateTimeSpent(grouped_rank[~grouped_rank['repo_name'].isin(\n",
    "    fork_data.reset_index()['repo_name'].head(300).tolist())])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f89ecf0f-ea5b-4b7d-bf3b-9d94b479a706",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "hierarchy_dist_most = pd.concat([\n",
    "    time_spent_org.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Organization'),\n",
    "    time_spent_many_contributor.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Top 300 (repos) Most Contributors'),\n",
    "    time_spent_most_activity.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Top 300 (repos) Most Activity'),\n",
    "    time_spent_most_watch.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Top 300 (repos) Most Watches'),\n",
    "    time_spent_most_fork.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Top 300 (repos) Most Forks'),\n",
    "], axis = 1)\n",
    "hierarchy_dist_less = pd.concat([\n",
    "    time_spent_noorg.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Not Organization'),\n",
    "    time_spent_no_many.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Not Top 300 (repos) Most Contributors'),\n",
    "    time_spent_not_most.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Not Top 300 (repos) Most Activity'),\n",
    "    time_spent_not_watch.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Not Top 300 (repos) Most Watches'),\n",
    "    time_spent_not_fork.groupby('rank')['Time Spent (Worker-Years)'].sum().rename('Not Top 300 (repos) Most Forks'),\n",
    "], axis = 1)\n",
    "\n",
    "for col in hierarchy_dist_most.columns:\n",
    "    hierarchy_dist_most[col] = hierarchy_dist_most[col].apply(lambda x: f\"{x:.0f}\\n({x/hierarchy_dist_most[col].sum():.2f})\")\n",
    "for col in hierarchy_dist_less.columns:\n",
    "    hierarchy_dist_less[col] = hierarchy_dist_less[col].apply(lambda x: f\"{x:.0f}\\n({x/hierarchy_dist_less[col].sum():.2f})\")\n",
    "\n",
    "hierarchy_dist_most.columns.name = 'Time Spent (Worker-Years)'\n",
    "hierarchy_dist_less.columns.name = 'Time Spent (Worker-Years)'\n",
    "pretty_print(hierarchy_dist_most.loc[['NONE', 'CONTRIBUTOR', 'MEMBER','COLLABORATOR', 'OWNER']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e90ee6a1-d48e-4049-ae3e-2464fc51cea3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pretty_print(hierarchy_dist_less.loc[['NONE', 'CONTRIBUTOR', 'MEMBER','COLLABORATOR', 'OWNER']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70e39962-d327-42c3-bc26-e88cc2ec1003",
   "metadata": {},
   "outputs": [],
   "source": [
    "top300_contri_grouped_rank = grouped_rank[grouped_rank['repo_name'].isin(repo_contributors.head(300).index)]\n",
    "top300_contri_df_issue_clean = df_issue_clean[df_issue_clean['repo_name'].isin(repo_contributors.head(300).index)]\n",
    "nottop300_contri_grouped_rank = grouped_rank[~grouped_rank['repo_name'].isin(repo_contributors.head(300).index)]\n",
    "nottop300_contri_df_issue_clean = df_issue_clean[~df_issue_clean['repo_name'].isin(repo_contributors.head(300).index)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "accc17b0-e9a3-438c-9a80-d72f0190237d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "top300_contri_time_spent = make_time_spent(top300_contri_grouped_rank)\n",
    "top300_contri_generated_activity_base = make_generated_activity_base(top300_contri_df_issue_clean)\n",
    "top300_contri_generated_activity_time = make_generated_activity_time(top300_contri_generated_activity_base, top300_contri_time_spent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0647ff00-dc93-4488-abe4-30b2706d17a7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "nottop300_contri_time_spent = make_time_spent(nottop300_contri_grouped_rank)\n",
    "nottop300_contri_generated_activity_base = make_generated_activity_base(nottop300_contri_df_issue_clean)\n",
    "nottop300_contri_generated_activity_time = make_generated_activity_time(nottop300_contri_generated_activity_base, nottop300_contri_time_spent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d248dd63-a12a-4a62-b3e5-5c767496ee14",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "top300_contri_generated_activity_time.columns.name = 'Top 300 Repositories, by Contributor Count'\n",
    "pretty_print(top300_contri_generated_activity_time[['Amount of Activity','Proportion','# of Opened Issues','# of Closed Issues',\n",
    "                         '# of Closed Issues (Own Issue)','# of Closed Issues (Others Issue)','# of Comments','# of Comments (Own Issue)',\n",
    "                         '# of Comments (Others Issue)']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d5bd5cc-96e0-4cc3-8da0-9bc12ebdb7f7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "nottop300_contri_generated_activity_time.columns.name = 'All Repositories except Top 300 by Contributor Count'\n",
    "pretty_print(nottop300_contri_generated_activity_time[['Amount of Activity','Proportion','# of Opened Issues','# of Closed Issues',\n",
    "                         '# of Closed Issues (Own Issue)','# of Closed Issues (Others Issue)','# of Comments','# of Comments (Own Issue)',\n",
    "                         '# of Comments (Others Issue)']]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1d9cf30-044e-401f-a655-42a16a17877b",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(top300_contri_df_issue_clean.shape[0]/top300_contri_df_issue_clean['key'].unique().shape[0], top300_contri_df_issue_clean['key'].unique().shape[0])\n",
    "print(nottop300_contri_df_issue_clean.shape[0]/nottop300_contri_df_issue_clean['key'].unique().shape[0], nottop300_contri_df_issue_clean['key'].unique().shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe4ff05d-75ad-4f54-8ae1-0e3efcd04917",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pretty_print(top300_contri_generated_activity_time[['Amount of Activity','Time Spent (Worker-Years)','Activity/Worker-Year','Opened Issues/Time Spent',\n",
    "                                      'Closed Issues/Time Spent','Closed Issues (Own Issue)/Time Spent','Closed Issues (Others Issue)/Time Spent','Comments/Time Spent',\n",
    "                                      'Comments (Own Issue)/Time Spent','Comments (Others Issue)/Time Spent']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cac0a5f9-60cb-4b55-9d97-5b582b6f3ebf",
   "metadata": {},
   "outputs": [],
   "source": [
    "pretty_print(nottop300_contri_generated_activity_time[['Amount of Activity','Time Spent (Worker-Years)','Activity/Worker-Year','Opened Issues/Time Spent',\n",
    "                                      'Closed Issues/Time Spent','Closed Issues (Own Issue)/Time Spent','Closed Issues (Others Issue)/Time Spent','Comments/Time Spent',\n",
    "                                      'Comments (Own Issue)/Time Spent','Comments (Others Issue)/Time Spent']])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3250e78-7289-4e54-a04b-989c72f2110b",
   "metadata": {},
   "source": [
    "## Analyzing the Lifetime of an Issue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e709d2a1-c29c-40ea-86b9-eb6619c1e22d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "non_author_comments = df_issue_clean[(df_issue_clean['type'] == 'IssueCommentEvent') & (df_issue_clean['actor_login'] != df_issue_clean['issue_user_login'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b85f58d-0be2-47b3-a0c0-ebebc068b592",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_contributors = df_issue_clean[['key', 'actor_login']].drop_duplicates()\n",
    "print(f\"{num_contributors.shape[0]/df_issue_clean['key'].unique().shape[0]:.2f} contributors to each issue on average\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb28e799-20ba-4763-b5d9-767521f6fd92",
   "metadata": {},
   "outputs": [],
   "source": [
    "contributors_per_issue = num_contributors.groupby('key')['actor_login'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc55526d-f240-4134-93f9-e6df8656cb7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots()\n",
    "contributors_per_issue.plot(kind = 'hist', ax = ax, bins = 50)\n",
    "ax.set_yscale('log')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9667190-3fa7-402b-bdcd-d0d6532de780",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "first_nonauthor_comment = non_author_comments.sort_values('created_at').drop_duplicates(['key'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b5323ee-fa69-45b0-9467-683cd423f7d4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "second_nonauthor_comment = non_author_comments[~non_author_comments.index.isin(first_nonauthor_comment.index)].sort_values(\n",
    "    'created_at').drop_duplicates(['key'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9aaf4f92-f207-4dab-b3ba-990242eddd08",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# second commenter normally same as first commenter\n",
    "# surprisingly a lot of NONEs reply to NONE opened issues (discussion involving unaffiliateds, and then contributors)\n",
    "# mostly NONEs that respond to OWNER issues (interesting...very unhierarchical)\n",
    "# otherwise OWNERs rarely respond to issues\n",
    "# CONTRIBUTOR -> most likely is fellow contributor, then NONE (how to explain?) then MEMBER and then COLLABORATOR\n",
    "# COLLABORATOR -> most likely is COLLABORATOR, then NONE, then CONTRIBUTOR then MEMBER\n",
    "# MEMBER IS WEIRD\n",
    "# similarly MEMBER -> most likely is MEMBER, then NONE, then CONTRIBUTOR, then COLLABORATOR\n",
    "# i mean general vibe is similar RANK, and then decreasing chance of lower -> higher rank individuals\n",
    "\n",
    "\n",
    "commentor_author = pd.concat([first_nonauthor_comment.groupby('issue_author_association_repaired')[\n",
    "                              'actor_repo_association_repaired'].value_counts(),\n",
    "                              first_nonauthor_comment.groupby('issue_author_association_repaired')[\n",
    "                              'actor_repo_association_repaired'].value_counts(normalize = True),\n",
    "                              second_nonauthor_comment.groupby('issue_author_association_repaired')[\n",
    "                              'actor_repo_association_repaired'].value_counts(),\n",
    "                              second_nonauthor_comment.groupby('issue_author_association_repaired')[\n",
    "                              'actor_repo_association_repaired'].value_counts(normalize = True),\n",
    "          ], axis = 1).loc[[\n",
    "    'NONE', 'CONTRIBUTOR', 'MEMBER', 'COLLABORATOR', 'OWNER']]\n",
    "commentor_author = commentor_author.reset_index()\n",
    "#commentor_author.columns = ['Author Rank', 'Replier Rank', 'First Replier (Count)', 'Second Replier']\n",
    "commentor_author.round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6289ce6-b150-46c5-b13f-3306e6f27424",
   "metadata": {},
   "outputs": [],
   "source": [
    "# how many different individuals are involved in commenting on an issue? how many commenters are there? How are the two related?\n",
    "# how often is the closer one of the commenters?\n",
    "# when do you see the first commenter (second person) arrive? third person? what is their rank\n",
    "# "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e53ca02-5353-4dc4-80d5-6c9276850394",
   "metadata": {},
   "outputs": [],
   "source": [
    "non_author_comments['earliest_nonauthor_comment_date'] = non_author_comments.groupby(['key', 'created_at'])['created_at'].transform('min')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f1ad5b1-9ac5-4214-8fa0-8b9dc16155c3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "non_author_comments[non_author_comments['earliest_nonauthor_comment_date'] == non_author_comments['created_at']].groupby(\n",
    "    'issue_author_association_repaired')['actor_repo_association_repaired'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75d50f94-047f-4e9c-8b7d-1889aafc98d6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "993bd998-b184-4061-8a39-5513685a0388",
   "metadata": {},
   "outputs": [],
   "source": [
    "## whose opening issues\n",
    "## whose commenting\n",
    "## whose closing issues\n",
    "\n",
    "df_issue_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "601f5cd2-04a5-4cde-9a83-fe11cfddaf97",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b537eb03-569f-40b0-ba71-79ee63d013ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2185c598-fcf0-48b5-9519-e39724d3a1eb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# read prs\n",
    "\n",
    "add smt about PRs (linked PRs = issue that is PR review + PRs I connected)\n",
    "\n",
    "# now have to dig into PRs....\n",
    "\n",
    "# who works on the PR (code)\n",
    "# who works on the PR (commenting on PR review)\n",
    "# who works on the issue itself (commenting)\n",
    "\n",
    "# who reviews the PR request\n",
    "\n",
    "# closed date\n",
    "# comment info "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27af447a-40f2-4507-8825-198d2f8045fe",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}