aggregating_pr_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e04c21bb-f046-44e3-8680-7e9efe43dbec",
   "metadata": {},
   "source": [
    "## Step 1: Exogenous Shock\n",
    "1. Create measure of code quality\n",
    "   \n",
    "    a. Measure of user-side code quality\n",
    "   \n",
    "    b. Measure of maintainer-side code quality\n",
    "3. Analyze contributions made\n",
    "4. Hypothesize how my instrument/exogenous affects either, and examine the empirical effect\n",
    "\n",
    "## Step 2: \n",
    "How do I actually perform analysis?\n",
    "1. Measure 1: Compare within repository groups\n",
    "2. Measure 2: Find some other way to weight what a \"download\" means? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3521104c-0ae8-49ae-9d11-b59d15a4d079",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import ast\n",
    "#from pandarallel import pandarallel\n",
    "import glob\n",
    "import json\n",
    "import numpy as np\n",
    "from itertools import chain\n",
    "import sys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dc0b5ef0-c2e2-4c99-a786-f9920352d3a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pandarallel.initialize(progress_bar=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4455709c-8457-4fff-8669-d4b397a89dfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "0e4b9225",
   "metadata": {},
   "outputs": [],
   "source": [
    "folder = \"filtered_github_data_large\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "f659f309-e289-458e-97a5-35b56bdbc629",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "reading pr data\n"
     ]
    }
   ],
   "source": [
    "#folder = sys.argv[1]\n",
    "\n",
    "print(\"reading pr data\")\n",
    "df_pr = pd.DataFrame()\n",
    "file_count = np.array([int(ele.replace(\"prEventCommits000000000\",\"\").replace(\".csv\",\"\")) for ele in os.listdir(f'data/github_clean/{folder}/') if 'prEventCommits000000000' in ele])\n",
    "\n",
    "for i in range(max(file_count)+1):\n",
    "    if int(i) < 10:\n",
    "        i = f\"0{i}\"\n",
    "    if int(i) < 100:\n",
    "        i = f\"0{i}\"\n",
    "    df_pr_i = pd.read_csv(f'data/github_clean/{folder}/prEventCommits000000000{i}.csv', index_col = 0)\n",
    "    df_pr = pd.concat([df_pr_i,\n",
    "                       df_pr])\n",
    "    \n",
    "df_actor_info = df_pr.groupby(['actor_id', 'actor_login', 'repo_name', 'repo_id', 'org_id','org_login']).agg(\n",
    "    {'created_at': ['min', 'max']})\n",
    "df_actor_info.columns = ['earliest_date', 'latest_date']\n",
    "df_actor_info = df_actor_info.reset_index()\n",
    "df_pr = df_pr[['type', 'created_at', 'repo_id', 'actor_id', 'org_id', 'pr_id',\n",
    "               'pr_number', 'pr_state', 'pr_locked', 'pr_merged_at','pr_closed_at','pr_updated_at',\n",
    "               'pr_commits', 'pr_additions','pr_deletions','pr_changed_files',\n",
    "               'pr_author_association', 'pr_assignees', 'pr_requested_reviewers', 'pr_requested_teams',\n",
    "               'pr_merged_by_login', 'pr_merged_by_id', 'pr_merged_by_type',\n",
    "               'pr_merged_by_site_admin', 'pr_label', 'commit_list',]]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0f51be7a-7173-48f1-991c-15416c7628e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr = df_pr[~df_pr.index.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1e71d95b-428a-4aa6-9807-2f6cbe082418",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 835 ms, sys: 23.8 ms, total: 859 ms\n",
      "Wall time: 954 ms\n",
      "CPU times: user 835 ms, sys: 23.8 ms, total: 859 ms\n",
      "Wall time: 954 ms\n",
      "CPU times: user 835 ms, sys: 23.8 ms, total: 859 ms\n",
      "Wall time: 954 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "files = glob.glob(f\"data/github_clean/{folder}/prReviewEvent0*\")\n",
    "df_pr_review_events = [pd.read_csv(f, index_col = 0) for f in files]\n",
    "df_pr_review_events = pd.concat(df_pr_review_events,ignore_index=True)\n",
    "df_pr_review_events = df_pr_review_events.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "13775c49-511f-41dc-9f03-ddb3049acb14",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.89 s, sys: 198 ms, total: 3.09 s\n",
      "Wall time: 13.5 s\n",
      "CPU times: user 2.89 s, sys: 198 ms, total: 3.09 s\n",
      "Wall time: 13.5 s\n",
      "CPU times: user 2.89 s, sys: 198 ms, total: 3.09 s\n",
      "Wall time: 13.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "files = glob.glob(f\"data/github_clean/{folder}/prReviewCommentEvent0*\")\n",
    "df_pr_review_comment_events = [pd.read_csv(f, index_col = 0) for f in files]\n",
    "df_pr_review_comment_events = pd.concat(df_pr_review_comment_events,ignore_index=True)\n",
    "df_pr_review_comment_events = df_pr_review_comment_events.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2089075a-f0de-44fc-a7ee-91f52d706117",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "review_comments_add = df_pr_review_comment_events.copy()\n",
    "review_comments_add.rename({'pr_review_comment_action':'pr_review_action', 'pr_review_comment_id':'pr_review_id',\n",
    "                            'pr_review_comment_body': 'pr_review_body', 'pr_review_comment_commit_id':'pr_review_commit_id',\n",
    "                            'pr_review_comment_author_association':'pr_review_author_association'},\n",
    "                          axis = 1, inplace = True)\n",
    "review_comments_add['pr_review_state'] = 'commented'\n",
    "review_comments_add.drop(['pr_review_comment_site_admin'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ac8e542c-7305-4d5e-8f58-04afb54d0b3f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 705 ms, sys: 73.9 ms, total: 779 ms\n",
      "Wall time: 794 ms\n",
      "CPU times: user 705 ms, sys: 73.9 ms, total: 779 ms\n",
      "Wall time: 794 ms\n",
      "CPU times: user 705 ms, sys: 73.9 ms, total: 779 ms\n",
      "Wall time: 794 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_pr_all_reviews = pd.concat([df_pr_review_events, review_comments_add]).drop_duplicates().reset_index(drop = True)\n",
    "df_pr_all_reviews['created_at'] = pd.to_datetime(df_pr_all_reviews['created_at'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ab08aa48-dda9-48b0-b058-3dd8747ce207",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_all_reviews.sort_values('pr_review_body', inplace = True)\n",
    "df_pr_all_reviews.drop_duplicates(subset = ['created_at', 'repo_id', 'actor_id', 'pr_review_id', \n",
    "                                            'pr_review_commit_id', 'pr_review_state'], keep = 'first', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "924e2fe8-816f-43ca-a439-5c3f20042c05",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def cleanParquetPR(file):\n",
    "    try:\n",
    "        df = pd.read_parquet(file)\n",
    "        return df\n",
    "    except:\n",
    "        print(file)\n",
    "        return pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9833da0a-b3b9-4796-a090-2c5689184861",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "reading pr parquet files\n",
      "reading pr parquet files\n",
      "reading pr parquet files\n",
      "CPU times: user 27.1 s, sys: 6.11 s, total: 33.2 s\n",
      "Wall time: 2min 32s\n",
      "CPU times: user 27.1 s, sys: 6.11 s, total: 33.2 s\n",
      "Wall time: 2min 32s\n",
      "CPU times: user 27.1 s, sys: 6.11 s, total: 33.2 s\n",
      "Wall time: 2min 32s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "print(\"reading pr parquet files\")\n",
    "files = glob.glob(f\"data/github_commits/parquet/{folder}/*_pr_*\")\n",
    "df_parquet_pr_data = [cleanParquetPR(f) for f in files]\n",
    "df_parquet_pr = pd.concat(df_parquet_pr_data,ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "54e349c3-911e-4a7f-94fa-538aeb110238",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dropping duplicate parquet pr entries\n",
      "dropping duplicate parquet pr entries\n",
      "dropping duplicate parquet pr entries\n",
      "CPU times: user 4.58 s, sys: 346 ms, total: 4.93 s\n",
      "Wall time: 4.94 s\n",
      "CPU times: user 4.58 s, sys: 346 ms, total: 4.93 s\n",
      "Wall time: 4.94 s\n",
      "CPU times: user 4.58 s, sys: 346 ms, total: 4.93 s\n",
      "Wall time: 4.94 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "print(\"dropping duplicate parquet pr entries\")\n",
    "df_parquet_pr.sort_values('pr_state', inplace = True)\n",
    "df_parquet_pr.drop_duplicates(\n",
    "    subset = ['pr_number', 'repo_id', 'repo_name', 'actor_id', 'actor_login', 'org_id', 'org_login','commit sha',\n",
    "              'commit author name', 'commit author email', 'committer name', 'commmitter email', 'commit message', 'commit additions',\n",
    "              'commit deletions', 'commit changes total', 'commit files changed count', 'commit time'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b4d22673-1e6e-44a0-8933-7b0fc5c84ca9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_parquet_pr['commit time'] = pd.to_datetime(df_parquet_pr['commit time'],unit='s')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ab5d2d96-ceaa-4f69-b3cc-1ba57115b829",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "turning stuff into lists\n",
      "pr_assignees\n",
      "turning stuff into lists\n",
      "pr_assignees\n",
      "turning stuff into lists\n",
      "pr_assignees\n",
      "pr_requested_reviewers\n",
      "pr_requested_reviewers\n",
      "pr_requested_reviewers\n",
      "pr_requested_teams\n",
      "pr_requested_teams\n",
      "pr_requested_teams\n",
      "pr_label\n",
      "pr_label\n",
      "pr_label\n",
      "commit_list\n",
      "commit_list\n",
      "commit_list\n"
     ]
    }
   ],
   "source": [
    "print(\"turning stuff into lists\")\n",
    "for col in ['pr_assignees', 'pr_requested_reviewers', 'pr_requested_teams', 'pr_label', 'commit_list']:\n",
    "    print(col)\n",
    "    df_pr[col] = df_pr[col].apply(lambda x: [] if type(x) == float or type(x) == type(None) or \\\n",
    "                                  (type(x) == str and x == \"'float' object has no attribute 'split'\") else x)\n",
    "    df_pr[col] = df_pr[col].apply(lambda x: ast.literal_eval(x) if type(x) == str else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "3ee2afd3-2554-4bdb-9d9e-705f0710bec7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "various data cleaning commands\n",
      "various data cleaning commands\n",
      "various data cleaning commands\n"
     ]
    }
   ],
   "source": [
    "print(\"various data cleaning commands\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "683d182e-1467-4fa4-8c59-22f7141f7983",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr['pr_id'] = pd.to_numeric(df_pr['pr_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "7b2b2599-478e-4ff3-a658-0231959e02ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr['valid_vals'] = df_pr.count(axis = 1)\n",
    "df_pr['retrieved_commits'] = df_pr['commit_list'].apply(len)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "488c0648-97da-4b9d-abcc-8f9f0a5be1bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr = df_pr.sort_values(['valid_vals', 'retrieved_commits', 'created_at'], ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "87e40ab5-42d1-427b-8726-d22cdf01b4eb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr['actor_id_state'] = df_pr['actor_id'].astype(str)+\" | \" \n",
    "df_pr['actor_id_state'] = df_pr['actor_id_state'] + df_pr['pr_state'].apply(lambda x: 'NAN STATE' if type(x) != str else x) + \" | \"\n",
    "df_pr['actor_id_state'] = df_pr['actor_id_state'] + df_pr['org_id'].apply(lambda x: 'NAN ORG' if type(x) != str else x)\n",
    "df_pr['actor_id_state'] = df_pr['actor_id_state'] + \" | \" +  df_pr['pr_author_association'].apply(lambda x: 'NAN AUTHOR ASSOCIATION' if type(x) != str else x) \n",
    "\n",
    "\n",
    "df_parquet_pr['actor_id_state'] = df_parquet_pr['actor_id'].astype(str)+\" | \" +  df_parquet_pr['org_id'].apply(lambda x: 'NAN ORG' if type(x) != str else x)  + \" | \"\n",
    "df_parquet_pr['actor_id_state'] = df_parquet_pr['actor_id_state'] + df_parquet_pr['pr_state'].apply(lambda x: 'NAN STATE' if type(x) != str else x) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "440c87b9-5d3b-4ea5-951d-0108590c8a23",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 21.8 s, sys: 366 ms, total: 22.2 s\n",
      "Wall time: 22.2 s\n",
      "CPU times: user 21.8 s, sys: 366 ms, total: 22.2 s\n",
      "Wall time: 22.2 s\n",
      "CPU times: user 21.8 s, sys: 366 ms, total: 22.2 s\n",
      "Wall time: 22.2 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_pr['actor_id_list'] = df_pr['actor_id_state'].groupby(df_pr['pr_id']).transform(lambda x: [x.tolist()]*len(x))\n",
    "df_pr['actor_id_list'] = df_pr['actor_id_list'].apply(np.unique)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "7db44e95-7d5a-477d-8f38-038019a6503b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_nodup = df_pr.drop_duplicates(subset = ['repo_id', 'pr_id'], keep = 'first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9ae0fae1-bb69-441e-a810-bccaeef56d67",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 38.9 s, sys: 445 ms, total: 39.3 s\n",
      "Wall time: 39.4 s\n",
      "CPU times: user 38.9 s, sys: 445 ms, total: 39.3 s\n",
      "Wall time: 39.4 s\n",
      "CPU times: user 38.9 s, sys: 445 ms, total: 39.3 s\n",
      "Wall time: 39.4 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_parquet_pr['pr_id_temp'] = df_parquet_pr['repo_id'].astype(str)+\"_\"+df_parquet_pr['pr_number'].astype(str)\n",
    "df_parquet_pr['actor_id_list'] = df_parquet_pr['actor_id_state'].groupby(df_parquet_pr['pr_id_temp']).transform(lambda x: [x.tolist()]*len(x))\n",
    "df_parquet_pr['actor_id_list'] = df_parquet_pr['actor_id_list'].apply(np.unique)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6fa09f51-5d0d-4692-bb1e-f549b45f668c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_parquet_pr_nodup = df_parquet_pr.drop_duplicates(\n",
    "    subset = ['repo_id', 'pr_id_temp', 'commit time', 'commit sha'], keep = 'first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "593a1539-119d-4850-9cbb-3e9bb22066d0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/chrisliao/ipykernel_1555741/2364950747.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_parquet_pr_nodup['actor_id_list'] = df_parquet_pr_nodup['actor_id_list'].apply(lambda x: sorted(x))\n",
      "/home/chrisliao/ipykernel_1555741/2364950747.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_parquet_pr_nodup['actor_id_list'] = df_parquet_pr_nodup['actor_id_list'].apply(lambda x: sorted(x))\n",
      "/home/chrisliao/ipykernel_1555741/2364950747.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_parquet_pr_nodup['actor_id_list'] = df_parquet_pr_nodup['actor_id_list'].apply(lambda x: sorted(x))\n",
      "/home/chrisliao/ipykernel_1555741/2364950747.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_pr_nodup['actor_id_list'] = df_pr_nodup['actor_id_list'].apply(lambda x: sorted(x))\n",
      "/home/chrisliao/ipykernel_1555741/2364950747.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_pr_nodup['actor_id_list'] = df_pr_nodup['actor_id_list'].apply(lambda x: sorted(x))\n",
      "/home/chrisliao/ipykernel_1555741/2364950747.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_pr_nodup['actor_id_list'] = df_pr_nodup['actor_id_list'].apply(lambda x: sorted(x))\n"
     ]
    }
   ],
   "source": [
    "df_parquet_pr_nodup['actor_id_list'] = df_parquet_pr_nodup['actor_id_list'].apply(lambda x: sorted(x))\n",
    "df_pr_nodup['actor_id_list'] = df_pr_nodup['actor_id_list'].apply(lambda x: sorted(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b514f477-cb5b-4daa-acd1-16cc4d2db519",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " created merged commit data\n",
      " created merged commit data\n",
      " created merged commit data\n"
     ]
    }
   ],
   "source": [
    "print(\" created merged commit data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5be9493d-1f1d-4b0a-8668-182510210721",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_commits = pd.merge(df_pr_nodup.drop(['type', 'actor_id', 'org_id', 'pr_state', 'pr_author_association', 'actor_id_state',\n",
    "                                           'valid_vals'], axis =1), \n",
    "                         df_parquet_pr_nodup.drop(['actor_id_state','actor_id', 'actor_login', 'org_id', 'org_login'], axis = 1), \n",
    "                         on = ['repo_id', 'pr_number', ], \n",
    "                         how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "4b336179-16b4-4aec-ae1b-c2008efc0a02",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "clean merged commit data\n",
      "clean merged commit data\n",
      "clean merged commit data\n"
     ]
    }
   ],
   "source": [
    "print(\"clean merged commit data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "1d161908-7a99-4ad7-8fd0-39e821667179",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_commits.rename({'actor_id_list_y':'commit_actor_id_list',\n",
    "                      'actor_id_list_x':'pr_actor_id_list'}, axis= 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c514fa00-8234-47be-b50f-132d779fdced",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_commits['created_at'] = pd.to_datetime(df_pr_commits['created_at'])\n",
    "df_pr_commits['pr_merged_at'] = pd.to_datetime(df_pr_commits['pr_merged_at'].apply(lambda x: x if x != \"[]\" else np.nan))\n",
    "df_pr_commits['pr_closed_at'] = pd.to_datetime(df_pr_commits['pr_closed_at'].apply(lambda x: x if x != \"[]\" else np.nan))\n",
    "df_pr_commits['pr_updated_at'] = pd.to_datetime(df_pr_commits['pr_updated_at'].apply(lambda x: x if x != \"[]\" else np.nan))\n",
    "df_pr_commits['commit time'] = pd.to_datetime(df_pr_commits['commit time'],unit='s')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "6af55857-8b33-4cbc-a118-9354a4a087b0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_commits['merge_day'] = df_pr_commits['pr_merged_at'].apply(lambda x: x.day)\n",
    "df_pr_commits['merge_month'] = df_pr_commits['pr_merged_at'].apply(lambda x: x.month)\n",
    "df_pr_commits['merge_year'] = df_pr_commits['pr_merged_at'].apply(lambda x: x.year)\n",
    "\n",
    "df_pr_commits['closed_day'] = df_pr_commits['pr_closed_at'].apply(lambda x: x.day)\n",
    "df_pr_commits['closed_month'] = df_pr_commits['pr_closed_at'].apply(lambda x: x.month)\n",
    "df_pr_commits['closed_year'] = df_pr_commits['pr_closed_at'].apply(lambda x: x.year)\n",
    "\n",
    "df_pr_commits['commit_day'] = df_pr_commits['commit time'].apply(lambda x: x.day)\n",
    "df_pr_commits['commit_month'] = df_pr_commits['commit time'].apply(lambda x: x.month)\n",
    "df_pr_commits['commit_year'] = df_pr_commits['commit time'].apply(lambda x: x.year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "48e3ac3c-d651-4ac3-9f93-65e1ef995d14",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_commits['commit parent'] = df_pr_commits['commit_groups'].apply(lambda x: x[0] if type(x) == list and len(x)>0 else '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "fef114e7-5156-48d8-91d8-87538fcd15da",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_commits.drop(['pr_id_temp', 'commit_groups', 'commit_list'], \n",
    "                   axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "4c037b19-aedd-4017-b9e0-1f704d43ffbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "null_commit_time = df_pr_commits[df_pr_commits['commit_year'].isnull()].index\n",
    "df_pr_commits.loc[null_commit_time, \n",
    "    ['commit_day', 'commit_month', 'commit_year']] = df_pr_commits.loc[null_commit_time, ['merge_day', 'merge_month', 'merge_year']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "d66ab385",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_commits['pr_changed_files'] = df_pr_commits['pr_changed_files'].apply(lambda x: x if x != \"[]\" else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "571d18f0-022c-452b-86a2-f36c7609301f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_commits['pr_commits_wt'] = df_pr_commits['pr_commits'] / df_pr_commits.groupby('pr_id')['pr_id'].transform('count')\n",
    "df_pr_commits['pr_additions_wt'] = pd.to_numeric(df_pr_commits['pr_additions']) / df_pr_commits.groupby('pr_id')['pr_id'].transform('count')\n",
    "df_pr_commits['pr_deletions_wt'] = pd.to_numeric(df_pr_commits['pr_deletions']) / df_pr_commits.groupby('pr_id')['pr_id'].transform('count')\n",
    "df_pr_commits['pr_changed_files_wt'] = pd.to_numeric(df_pr_commits['pr_changed_files']) / df_pr_commits.groupby('pr_id')['pr_id'].transform('count')\n",
    "df_pr_commits['retrieved_commits_wt'] = pd.to_numeric(df_pr_commits['retrieved_commits']) / df_pr_commits.groupby('pr_id')['pr_id'].transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "1c112750-1a66-4314-b89f-604528347819",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_commits['pr_assignees_list'] = df_pr_commits['pr_assignees'].apply(lambda x: [ele['id'] for ele in x] if len(x)>0 else [])\n",
    "df_pr_commits['pr_requested_reviewers_list'] = df_pr_commits['pr_requested_reviewers'].apply(lambda x: [ele['id'] for ele in x] if len(x)>0 else [])\n",
    "df_pr_commits['pr_requested_teams_list'] = df_pr_commits['pr_requested_teams'].apply(lambda x: [ele['id'] for ele in x] if len(x)>0 else [])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "a52c2aa5-ea2b-4b21-80d3-faf6001753b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_commits['closed_wt'] = (1-df_pr_commits['pr_closed_at'].isna()) / df_pr_commits.groupby('pr_id')['pr_id'].transform('count')\n",
    "df_pr_commits['merged_wt'] = 1-df_pr_commits['pr_merged_at'].isna() / df_pr_commits.groupby('pr_id')['pr_id'].transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "f2d05e92-1ddb-4b54-be3e-34f1ad3cbf72",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_commits['pr_actors'] = df_pr_commits['pr_actor_id_list'].apply(lambda x: [ele.split(\"|\")[0].strip() for ele in x])\n",
    "df_pr_commits['pr_commit_actors'] = df_pr_commits['commit_actor_id_list'].apply(lambda x: [ele.split(\"|\")[0].strip() for ele in x] if type(x) == list else [])\n",
    "df_pr_commits['all_pr_actors'] = (df_pr_commits['pr_actors']+df_pr_commits['pr_commit_actors']).apply(lambda x: list(set(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "df4e63fa-bb14-4145-8fbf-f83a969877f8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_commits['pr_orgs'] = df_pr_commits['pr_actor_id_list'].apply(lambda x: [ele.split(\"|\")[2].strip() for ele in x])\n",
    "df_pr_commits['pr_commit_orgs'] = df_pr_commits['commit_actor_id_list'].apply(lambda x: [ele.split(\"|\")[2].strip() for ele in x] if type(x) == list else [])\n",
    "df_pr_commits['all_pr_orgs'] = (df_pr_commits['pr_orgs']+df_pr_commits['pr_commit_orgs']).apply(lambda x: list(set([ele for ele in x if ele != 'NAN ORG'])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "d6d3955e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_commits['commit file changes'] = df_pr_commits['commit file changes'].apply(lambda x: x.decode() if type(x) == bytes else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a6966de-a066-4a6c-929b-343fb36c846e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_commits['commit file changes'] = df_pr_commits['commit file changes'].apply(\n",
    "    lambda x: [] if type(x) == float or type(x) == type(None) else x)\n",
    "df_pr_commits['commit file changes'] = df_pr_commits['commit file changes'].apply(lambda x: ast.literal_eval(x) if type(x) == str else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75978fb6-c95e-477d-9808-8c85c7dd4c8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_commits['committer info'] = df_pr_commits['committer name'] + \" | \" + df_pr_commits['commmitter email']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "592a603e-ce98-41ad-b722-c91594121be0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "print(\"done cleaning df_pr_commits\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3576c2b1-7be2-40ba-9379-c6509f65d679",
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to turn list of lists into lists\n",
    "def rollIntoOne(series):\n",
    "    return len(series.apply(pd.Series).stack().reset_index(drop=True).unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92f958c8-fe05-48f0-b989-3b4100769044",
   "metadata": {},
   "outputs": [],
   "source": [
    "def dropNAUnique(x):\n",
    "    return x.dropna().unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ea0d7f2-5865-452c-89ab-62122ad5613a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def getList(x):\n",
    "    try:\n",
    "        return [ele['file'] for ele in x]\n",
    "    except:\n",
    "        return [ele['file'] for sublst in x for ele in sublst]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e48433c9-fa05-4008-a168-0f57fca849c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def aggData(df, group_cols):\n",
    "    df_results = df.groupby(group_cols, sort=False, observed=True).agg(     \n",
    "        pr_count=('pr_id', 'nunique'),\n",
    "        unique_push_actors=('all_pr_actors', rollIntoOne),\n",
    "        unique_push_orgs=('all_pr_orgs', rollIntoOne),\n",
    "        claimed_commits=('pr_commits_wt', 'sum'),\n",
    "        claimed_additions=('pr_additions_wt', 'sum'),\n",
    "        claimed_deletions=('pr_deletions_wt', 'sum'),\n",
    "        claimed_changed_files=('pr_changed_files_wt', 'sum'),\n",
    "        closed_prs=('closed_wt', 'sum'),\n",
    "        merged_prs=('merged_wt', 'sum'),\n",
    "        merger_id_count=('pr_merged_by_id', 'nunique'),\n",
    "        pr_labels=('pr_label', rollIntoOne),\n",
    "        counted_commits=('retrieved_commits_wt', 'count'), #this is correct, excuse my naming\n",
    "        retrieved_commits=('commit sha', 'count'),\n",
    "        unique_commit_authors=('commit author name', 'nunique'), \n",
    "        unique_commit_author_emails=('commit author email', 'nunique'),\n",
    "        unique_committers=('committer name', 'nunique'),\n",
    "        unique_committer_emails=('commmitter email', 'nunique'),\n",
    "        commit_authors=('commit author name', dropNAUnique),\n",
    "        committers=('committer info', dropNAUnique),\n",
    "        LOC_added=('commit additions', 'sum'),\n",
    "        LOC_deleted=('commit deletions', 'sum'),\n",
    "        files_changed=('commit files changed count', 'sum'),\n",
    "        changed_files=('commit file changes', getList),\n",
    "        uniq_changed_files=('commit file changes',  lambda x: len(getList(x)))\n",
    "    )    \n",
    "    return df_results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c2f2427-6562-4701-b5f4-2efb000fda1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"now exporting merged data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd086239",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_commits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1ccf518",
   "metadata": {},
   "outputs": [],
   "source": [
    "break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fdfe973-b9a9-42c4-8eab-d81770ac8d27",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df_pr_commits.to_csv(f'data/merged_data/{folder}/merged_commit_pr.csv', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "77f9bd67",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actor_info.to_csv(f'data/merged_data/{folder}/pr_actor.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56508c60-8b01-4c7f-90e7-be10810727c5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\"\"\"%%time\n",
    "print(\"merge date, monthly\")\n",
    "import warnings\n",
    "with warnings.catch_warnings():\n",
    "    warnings.filterwarnings(\"ignore\", message=\"divide by zero encountered in divide\")\n",
    "    df_pr_monthly = aggData(df_pr_commits, ['merge_month', 'merge_year', 'repo_id'])\n",
    "    df_pr_monthly.to_csv('data/aggregated_data/aggregated_monthly_labor_pr.csv', encoding='utf-8')\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "367b6b4c-ce24-4536-9ece-1360f00476aa",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\"\"\"%%time\n",
    "print(\"commit date, monthly\")\n",
    "with warnings.catch_warnings():\n",
    "    warnings.filterwarnings(\"ignore\", message=\"divide by zero encountered in divide\")\n",
    "    df_pr_monthly_commit = aggData(df_pr_commits, ['commit_month', 'commit_year', 'repo_id'])\n",
    "    df_pr_monthly_commit.to_csv('data/aggregated_data/aggregated_monthly_labor_commit_pr.csv', encoding='utf-8')\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b897683-53bc-4748-854b-7d0568b0f68f",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"%%time\n",
    "print(\"merge date, daily\")\n",
    "with warnings.catch_warnings():\n",
    "    warnings.filterwarnings(\"ignore\")\n",
    "    df_pr_commits_merged = df_pr_commits[~df_pr_commits['pr_merged_at'].isna()]\n",
    "    df_pr_monthly_merged = aggData(df_pr_commits_merged, ['merge_month', 'merge_year', 'repo_id'])\n",
    "    df_pr_monthly_merged.to_csv('data/aggregated_data/aggregated_monthly_labor_pr_merged_only.csv', encoding='utf-8')\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f36b766-7520-4659-b2cd-33c33fd8cf02",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\"\"\"%%time\n",
    "print(\"merge date, daily\")\n",
    "with warnings.catch_warnings():\n",
    "    warnings.filterwarnings(\"ignore\", message=\"divide by zero encountered in divide\")\n",
    "    df_pr_commits_merged = df_pr_commits[~df_pr_commits['pr_merged_at'].isna()]\n",
    "    df_pr_monthly_commit_merged = aggData(df_pr_commits_merged, ['commit_month', 'commit_year', 'repo_id'])\n",
    "    df_pr_monthly_commit_merged.to_csv('data/aggregated_data/aggregated_monthly_labor_commit_pr_merged_only.csv', encoding='utf-8')\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d7b30a9-abbd-4ff7-a8fb-c3dcfaf26052",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"prReview contains data about prReviews - link to examine 1) whose reviewing, 2) whether requested teams are reviewing,\n",
    "                                                         3) how many reviews\n",
    "prReviewCommentEvent contains statsitics about the type of discussion that''s going on about pr reviews, look at \n",
    "1) number of comments, 2) whose commenting\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e157c64",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27223ea9",
   "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
}