hierarchyAnalysisIssues.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecb69529-eab9-45e2-af17-53eea691040b",
   "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": null,
   "id": "74b12137-8806-4887-bdf2-afe7e83681c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Import Libraries and Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77bd0085-56b9-4276-a099-8ece6eaadae5",
   "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\n",
    "import os\n",
    "import datetime\n",
    "from dateutil.rrule import rrule, MONTHLY, YEARLY, WEEKLY\n",
    "from dateutil.relativedelta import relativedelta\n",
    "from linearmodels.panel import PanelOLS\n",
    "import multiprocessing\n",
    "import statsmodels.formula.api as smf\n",
    "import re\n",
    "from itertools import product\n",
    "import pytz\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30a0858a-675d-4f00-b22c-939f1fe28567",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.style.use(\"seaborn\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90f20a2d-14ab-42b6-ab0a-43c05656d61e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pandarallel.initialize(progress_bar = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fe7eec8-c1b9-47d5-b973-e396a2ffd7d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e581237c-aeac-4565-91d2-38f3501bca16",
   "metadata": {},
   "outputs": [],
   "source": [
    "def readDf(df,cols):\n",
    "    cols = [col for col in cols if col in df.columns]\n",
    "    return df[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0ab0314-dfa3-406d-b025-4003481c0fd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def readGlobDf(filename):\n",
    "    df = pd.concat([pd.read_parquet(file, engine = 'pyarrow') for file in \n",
    "               glob.glob(filename.replace(\".parquet\",\"*.parquet\"))])\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9402da4-6fec-4608-a2fd-809ddc8876db",
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_parquet(f, columns = None):\n",
    "    try:\n",
    "        if columns == None:\n",
    "            return pd.read_parquet(f)\n",
    "        else:\n",
    "            return pd.read_parquet(f, columns = columns)\n",
    "    except:\n",
    "        return pd.DataFrame()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4aece4c6-21d6-4b95-b982-8475e4161de9",
   "metadata": {},
   "source": [
    "# Data that I have"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edc65f56-11b7-466a-8b6b-f9c90ddae154",
   "metadata": {},
   "source": [
    "## Data that links actor, organization and repository ids to logins (names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63b2b868-9b31-4a0e-902e-46be8247a8b2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_actor = readGlobDf('data/merged_data/imputed_ranks/actor_login_id.parquet')\n",
    "df_org = readGlobDf('data/merged_data/imputed_ranks/org_login_id.parquet')\n",
    "df_repo = readGlobDf('data/merged_data/imputed_ranks/repo_login_id.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a926fc6-3a8b-4b8a-b4cd-01304b7e4f14",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_repo['org_login'] = df_repo['repo_name'].apply(lambda x: x.split(\"/\")[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0424458-ffc4-4b28-9708-f458575748f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "org_dict = pd.merge(df_repo, df_org).drop_duplicates(['repo_id'])[['repo_id', 'org_id']].set_index('repo_id').to_dict()['org_id']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e9fe292-0fee-4761-acc4-078c49511a99",
   "metadata": {},
   "source": [
    "## Data on when all actors first appeared"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5252d26c-cb16-4bf9-ac22-ca7789d1f563",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actor_start = readGlobDf('data/merged_data/imputed_ranks/allActors.parquet')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b78c2845-18a3-4be6-ba76-2a1c723724f7",
   "metadata": {},
   "source": [
    "## Data on imputed ranks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ca351f5-68e6-4dd1-8730-96af49895b5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ranked_v1 = readGlobDf('data/merged_data/imputed_ranks/all_ranked_v1.parquet')\n",
    "all_ranked_v1['created_at'] = pd.to_datetime(all_ranked_v1['created_at'])\n",
    "\n",
    "all_ranked_v1 = pd.merge(\n",
    "    all_ranked_v1.drop('org_id', axis = 1), \n",
    "    df_actor_start[['repo_id','org_id']].sort_values('repo_id').drop_duplicates('repo_id'), how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1e5d0db-e36c-44ad-838e-ee570514ea5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ranked_v2 = readGlobDf('data/merged_data/imputed_ranks/all_ranked_v2.parquet')\n",
    "all_ranked_v2['created_at'] = pd.to_datetime(all_ranked_v2['created_at'])\n",
    "\n",
    "all_ranked_v2 = pd.merge(\n",
    "    all_ranked_v2.drop('org_id', axis = 1), \n",
    "    df_actor_start[['repo_id','org_id']].sort_values('repo_id').drop_duplicates('repo_id'), how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad856119-a84c-4bea-a630-bf5ed7134d0c",
   "metadata": {},
   "source": [
    "## Data on whose committed to a repo, and aggregated data describing their email addresses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "210d9717-a0cd-41e2-8213-f9d1af39c754",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "committer_push = readGlobDf('data/merged_data/committers_info_push.parquet')\n",
    "committer_pr = readGlobDf('data/merged_data/committers_info_pr.parquet')\n",
    "\n",
    "# note that the below is an underestimate of the # of corporate contributors (because some people use their personal emails)\n",
    "committer_info = readGlobDf('data/merged_data/committer_detailed_info.parquet')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e70335b-fb71-4a50-890a-dd49fe722b9a",
   "metadata": {},
   "source": [
    "## Data on Issues and PRs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d23b4f5-751c-4052-846f-9669db98150d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "issueEventData = readGlobDf('data/merged_data/issue_data.parquet')\n",
    "prEventData = readGlobDf('data/merged_data/prEventData.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9a85f5f-f03c-40db-8121-2c16b9213a3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "linked_issues = pd.read_parquet('data/merged_data/linked_issues.parquet')\n",
    "issueEventData = pd.merge(issueEventData, linked_issues[['repo_id','potential_issues','pr_number']],\n",
    "                          left_on = ['repo_id','issue_number'], \n",
    "                          right_on = ['repo_id','potential_issues'], how = 'left')\n",
    "pr_authors = pd.concat([prEventData[prEventData['pr_action'] == 'opened'][['repo_id','pr_number','actor_id']].rename({'actor_id':'pr_user_id'}, axis = 1),\n",
    "                        prEventData[['repo_id','pr_number','pr_user_id']].dropna()]).drop_duplicates()\n",
    "issueEventData = pd.merge(issueEventData, pr_authors, how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfe93865-246c-4978-b97e-a6a557c5864f",
   "metadata": {},
   "outputs": [],
   "source": [
    "prEventData = pd.merge(prEventData, linked_issues[['repo_id','potential_issues','pr_number']],\n",
    "                       on = ['repo_id','pr_number'], how = 'left')\n",
    "issue_authors = issueEventData[['repo_id','issue_number','issue_user_id']].dropna().drop_duplicates()\n",
    "prEventData = pd.merge(prEventData, issue_authors,\n",
    "                       left_on = ['repo_id','potential_issues'],\n",
    "                       right_on = ['repo_id','issue_number'], how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ac319bf-8e50-4f80-81c0-da69ddb7483d",
   "metadata": {},
   "source": [
    "# Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ab23c6d-7957-4d8a-a435-a464cb795df2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "org_true = all_ranked_v2[all_ranked_v2['org_id'].isna()].index\n",
    "all_ranked_v2.loc[org_true, 'corrected_permissions'] = all_ranked_v2.loc[org_true, 'corrected_permissions'].apply(\n",
    "    lambda x: 'collaborator' if x != 'owner' else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd3e203f-39bc-4f04-85bb-110dfd4c287d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pytz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29681be8-d358-48ff-819c-2bc852bcc1ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('\\nOrganization',\n",
    "      all_ranked_v2[~all_ranked_v2['org_id'].isna()].sort_values(\n",
    "           'created_at', ascending = False).drop_duplicates(\n",
    "          ['repo_id', 'actor_id', 'org_id'])['corrected_permissions'].value_counts(normalize = True).round(2))\n",
    "print('\\nNot Organization',\n",
    "      all_ranked_v2[all_ranked_v2['org_id'].isna()].sort_values(\n",
    "          'created_at', ascending = False).drop_duplicates(\n",
    "          ['repo_id', 'actor_id', 'org_id'])['corrected_permissions'].value_counts(normalize = True).round(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cd3be0d-e325-4a36-a181-85384360cf4a",
   "metadata": {},
   "source": [
    "## Analysis - think about TASKS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e52c48a-9d3b-4581-aaa7-298382007956",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "issueEventData['created_at']= pd.to_datetime(issueEventData['created_at'], format='mixed', errors = 'coerce')\n",
    "issueEventData['created_at'] = issueEventData['created_at'].dt.tz_localize(pytz.UTC)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d869c68b-295d-4150-b630-057799fcba5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_comments = issueEventData[(issueEventData['type'] == 'IssueCommentEvent')]\n",
    "df_closed = issueEventData[issueEventData['issue_action'] == 'closed']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96e6fc6c-9936-4e4f-af48-0173170486c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ranked_v2['permissions_date'] = all_ranked_v2.apply(lambda x: [x['corrected_permissions'], x['created_at']], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee0d8412-c041-448a-b642-f7aa69f39f30",
   "metadata": {},
   "outputs": [],
   "source": [
    "permissions_dict = all_ranked_v2[(~all_ranked_v2['org_id'].isna()) & (all_ranked_v2['corrected_permissions'] != 'owner')].groupby(\n",
    "    ['actor_id', 'repo_id']).agg({'permissions_date':lambda x: sorted(list(x), key = lambda y: y[1])}).to_dict()['permissions_date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82715169-246c-44a6-8e63-3311287c111b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def addPermissions(lst, date):\n",
    "    if type(lst) == float:\n",
    "        return np.nan\n",
    "\n",
    "    if len(lst) == 1:\n",
    "        return lst[0][0]\n",
    "    else:\n",
    "        lst = sorted(lst, key = lambda x: x[1], reverse = True)\n",
    "        for i in range(len(lst)):\n",
    "            if lst[i][1].tz_localize(None)>=date.tz_localize(None):\n",
    "                return lst[i][0]\n",
    "        return lst[-1][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89b67bc2-82e5-452b-a237-a35aa03203aa",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_comments['repo_id'] = df_comments['repo_id'].apply(lambda x: org_dict.get(x, f\"REPO_{x}\"))\n",
    "df_closed['repo_id'] = df_closed['repo_id'].apply(lambda x: org_dict.get(x, f\"REPO_{x}\"))\n",
    "df_comments['permissions'] = df_comments.parallel_apply(lambda x: addPermissions(\n",
    "    permissions_dict.get((x['actor_id'], x['repo_id']), np.nan), x['created_at']), axis = 1)\n",
    "df_closed['permissions'] = df_closed.parallel_apply(lambda x: addPermissions(permissions_dict.get((x['actor_id'], x['repo_id']), np.nan), x['created_at']), axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49fa12c1-2867-40fd-8235-bfbeeddad998",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_commenting_permissions = df_comments[~df_comments['permissions'].isna()]\n",
    "df_commenting_permissions['organization'] = True\n",
    "df_commenting_permissions['corrected_permissions'] = df_commenting_permissions['permissions']\n",
    "\n",
    "df_closed_permissions = df_closed[~df_closed['permissions'].isna()]\n",
    "df_closed_permissions['organization'] = True\n",
    "df_closed_permissions['corrected_permissions'] = df_closed_permissions['permissions']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08615144-9f39-4775-8bba-0a3810208c5c",
   "metadata": {},
   "source": [
    "## Commits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62a1b317-3425-4d1c-84cb-f343ab49e497",
   "metadata": {},
   "outputs": [],
   "source": [
    "committer_push.dropna(inplace = True)\n",
    "committer_pr.dropna(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9feea2df-670b-46ec-80a5-58fbe843fb60",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "committer_push['committer_info'] = committer_push['committer_info'].apply(lambda x: ast.literal_eval if type(x) != list else x)\n",
    "committer_pr['committer_info'] = committer_pr['committer_info'].apply(lambda x: ast.literal_eval if type(x) != list else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44ac612e-1e0b-4365-9ae5-aa12fdaab2cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "committer_push['email'] = committer_push['email'].apply(lambda x: x.lower())\n",
    "committer_pr['email'] = committer_pr['email'].apply(lambda x: x.lower())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60dbc412-5a5f-456d-8268-42c4a95a95b5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "email_committer_info = pd.concat([committer_push[['email', 'actor_id']],\n",
    "                                 committer_pr[['email', 'actor_id']]]).drop_duplicates().set_index('email').to_dict()['actor_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60dac5d2-8a98-44f4-b41b-b2ab421262f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def readCommits(file, usecols):\n",
    "    return pd.read_parquet(file, usecols = usecols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ffc9bf2-149c-4a92-9962-cc88047c21f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['repo_id', 'repo_name', 'actor_id', 'org_id', 'commit changes total','commit author name',\n",
    "        'commit author email','committer name','commmitter email','commit files changed count','commit time',\n",
    "       'commit additions','commit deletions','commit file changes']\n",
    "cols_pr = cols.copy()\n",
    "cols.extend(['push_id',])\n",
    "cols_pr.extend(['pr_number'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a08bdd9-6bc4-441d-a8f6-d38a5a24c765",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "df_commits_pr = pd.concat([pd.read_parquet(file, engine = 'pyarrow') for file in \n",
    "                           glob.glob('data/github_commits/parquet/filtered_github_data/*_pr_*')])\n",
    "df_commits_pr['type'] = 'pr commits'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df3c2831-b393-4cd5-9d3c-ee2fb3e9d9bf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "df_commits_push = pd.concat([pd.read_parquet(file, engine = 'pyarrow') for file in \n",
    "                           glob.glob('data/github_commits/parquet/filtered_github_data/*_push_*')])\n",
    "df_commits_push['type'] = 'push commits'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7747bd91-ad85-49cc-aab0-95adcae76cfd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "df_commits_pr_pre = pd.concat([pd.read_parquet(file, engine = 'pyarrow') for file in \n",
    "                           glob.glob('data/github_commits/parquet/github_data_pre_18/*_pr_*')])\n",
    "df_commits_pr_pre['type']  = 'pr commits'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30e3bb24-1ded-44a1-9e25-b22ab992bc86",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "df_commits_push_pre = pd.concat([pd.read_parquet(file, engine = 'pyarrow') for file in \n",
    "                           glob.glob('data/github_commits/parquet/github_data_pre_18/*_push_*')])\n",
    "df_commits_push_pre['type']  = 'push commits'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96bdeb0c-15ac-494c-9ab5-27bdca62bda9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "df_commits_pr_post = pd.concat([read_parquet(file) for file in \n",
    "                           glob.glob('data/github_commits/parquet/github_data_2324/*_pr_*')])\n",
    "df_commits_pr_post['type']  = 'pr commits'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26b212cb-ac96-442e-96a9-f12ab16e8628",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "df_commits_push_post = pd.concat([read_parquet(file) for file in \n",
    "                           glob.glob('data/github_commits/parquet/github_data_2324/*_push_*')])\n",
    "df_commits_push_post['type']  = 'push commits'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae59fdd3-94b1-4a5e-99a2-3a510a9fc396",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_commits = pd.concat([df_commits_pr, df_commits_push, df_commits_pr_pre, df_commits_push_pre,\n",
    "                        df_commits_pr_post, df_commits_push_post])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df38e12e-ea5c-4cd2-ac58-c63ff6c1a4cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_commits.reset_index(drop = True, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e9989cd-befc-482d-a22b-cd9438ac10fb",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pushData = glob.glob('data/github_clean/filtered_github_data/pushEvent*')\n",
    "pushData.extend(glob.glob('data/github_clean/github_data_pre_18/pushEvent*'))\n",
    "pushData.extend(glob.glob('data/github_clean/github_data_2324/pushEvent*'))\n",
    "\n",
    "pushEventData = pd.concat([pd.read_parquet(ele) for ele in pushData if '.parquet' in ele]) [[\n",
    "    'actor_login', 'actor_id', 'org_id', 'org_login', 'type', 'created_at', 'repo_id', 'repo_name', 'push_id', 'push_ref']]\n",
    "df_commits = pd.merge(df_commits, pushEventData[['push_id', 'push_ref']].drop_duplicates(), how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc14d900-f918-407f-893f-9a1b80838993",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_commits['commit time'] = pd.to_datetime(df_commits['commit time'], unit = 's')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5720a634-02e0-4147-9628-37b2ab2b65b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_commits = df_commits[~df_commits['commit author email'].isna()]\n",
    "df_commits = df_commits[~df_commits['repo_id'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cee2756-5da6-4a9b-a161-0531e1f2831e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_commits['commit_actor_id'] = df_commits['commit author email'].apply(lambda x: email_committer_info.get(x.lower(), np.nan))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc76faee-d26a-4071-b913-99db68583f44",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_commits = df_commits[~df_commits['commit_actor_id'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5b06573-c4d6-4b66-b735-f180996b059f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_commits['commit day'] = df_commits['commit time'].apply(lambda x: datetime.datetime(x.year, x.month, x.day))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61249270-36e4-47a0-89c0-52d5620cb9ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "earliest_commit = df_commits.groupby(['commit_actor_id', 'repo_id'])['commit time'].min()\n",
    "earliest_commit = earliest_commit.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18dc5108-9bb2-4487-955c-723dc5874dec",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "earliest_commit['commit time'] = earliest_commit['commit time'].dt.tz_localize(pytz.UTC)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c589876-e7a5-4164-8c8d-a08c410c688b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "daily_commit_info = df_commits.groupby(['commit_actor_id', 'repo_id', 'commit day']).agg({'commit changes total':['sum', 'count']})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ba412c9-7d51-4fd7-a699-733d46448688",
   "metadata": {},
   "source": [
    "# Thinking More Critically About Tasks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18ec542f-5f66-408f-96c0-63418ecfaa0d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_actor_start['permissions'] = 'read'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1da1970-c4c3-4b5f-a335-e6febeb24231",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actor_all = pd.concat([df_actor_start, all_ranked_v1]).reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5de33688-42b4-4872-bdaa-16df35fd3a1a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_actor_all = pd.merge(df_actor_all,\n",
    "         all_ranked_v1.drop(['type'], axis = 1).rename({'created_at':'promoted_date', 'permissions':'corrected_permissions'}, axis = 1),\n",
    "         how = 'left', on = ['repo_id', 'actor_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5949d229-4fa0-44ca-bea1-d6af78fcccdc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_actor_all['org_id'] = df_actor_all.parallel_apply(lambda x: x['org_id_x'] if not pd.isnull(x['org_id_x']) \n",
    "                                            else x['org_id_y'] if not pd.isnull(x['org_id_y']) else np.nan, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "037d5664-658b-441b-b01e-cc620e24cade",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_actor_all = df_actor_all[~df_actor_all['created_at'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e63986a-88a1-4072-9792-01b32eaec025",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_actor_all['created_at']= pd.to_datetime(df_actor_all['created_at'], format='mixed', errors = 'coerce')\n",
    "#df_actor_all['created_at'] = df_actor_all['created_at'].apply(lambda x: x.astimezone(pytz.UTC))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95b36d76-23f5-4b31-a1bd-d8672c0c353c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# assume that if someone jumps from having read to more than read privileges in 7 days, it's bc they always had those privileges\n",
    "read_to_privileged = df_actor_all[df_actor_all.apply(\n",
    "    lambda x: (x['permissions'] == 'read' and \n",
    "              (x['promoted_date']-x['created_at']).days <= 7 and not pd.isnull(x['promoted_date'])), axis = 1)].index\n",
    "df_actor_all.loc[read_to_privileged, 'permissions'] = df_actor_all.loc[read_to_privileged, 'corrected_permissions']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69b753f0-af3c-48b3-bf69-12bbb0421b7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# assume that if someone jumps from having read to more than read privileges in 7 days, it's bc they always had those privileges\n",
    "read_to_privileged = df_actor_all[df_actor_all.apply(\n",
    "    lambda x: (x['permissions'] == 'read' and \n",
    "              (x['promoted_date']-x['created_at']).days <= 7 and not pd.isnull(x['promoted_date'])), axis = 1)].index\n",
    "df_actor_all.loc[read_to_privileged, 'permissions'] = df_actor_all.loc[read_to_privileged, 'corrected_permissions']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8bf5190e-9222-4fde-afd1-b7f3aea38fa1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_actor_all = df_actor_all.sort_values('created_at').drop_duplicates(['repo_id', 'actor_id', 'permissions']).drop(\n",
    "    ['type','promoted_date','corrected_permissions'], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5222ae74-d663-495f-a585-be3a589297b4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "perm_dict = {'read': 4, 'triage': 3, 'write': 2, 'owner': 1}\n",
    "df_actor_all['permissions_ranked'] = df_actor_all['permissions'].apply(lambda x: perm_dict[x])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "707381f6-81d8-4e82-b3a3-e8342120865a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actor_all = df_actor_all.sort_values(['created_at', 'permissions']).drop_duplicates(\n",
    "    ['created_at', 'repo_id', 'actor_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd5c5278-6c65-44b3-9a4c-5b3894b0f25c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actor_all.drop(['org_id_x','org_id_y'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d437a61-8bba-4f90-8828-ec3a5a9b8f4f",
   "metadata": {},
   "source": [
    "# Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e85c7c6-bb51-4957-a067-6bd728ca75f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "top_python = pd.read_csv('data/inputs/top_python_stars.csv').rename({'id':'repo_id'}, axis = 1)\n",
    "top_python_grouped = top_python.groupby('repo_id')['watch_count'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55022a72-3d1f-4694-9290-1b1327a376d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#python_opensource = pd.read_table('data/inputs/README-Python.md', sep=\"|\", header=0, index_col=0, skipinitialspace=True, on_bad_lines='warn').dropna(axis=1, how='all').iloc[1:]   \n",
    "#python_opensource['repo_name'] = python_opensource['NAME/PLACE'].apply(lambda x: \"/\".join(x.split(\"(\")[1].replace(\")\",\"\").split(\"/\")[-3:-1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df3b04c3-bd48-4ad8-b34b-c67c8a35e39a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actor_all = df_actor_all.reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a74033c5-447a-434e-9dc4-70c5fffd253e",
   "metadata": {},
   "outputs": [],
   "source": [
    "python_opensource = pd.merge(top_python_grouped.sort_values('watch_count').tail(1000), df_repo)\n",
    "repo_ids = python_opensource['repo_id'].tolist()\n",
    "free_copilot_inds = df_actor_all[(df_actor_all['created_at']<datetime.datetime(2022, 6, 23, tzinfo = pytz.UTC)) & \n",
    "         (df_actor_all['permissions'].isin(['owner', 'write'])) & (df_actor_all['repo_id'].isin(repo_ids))].index\n",
    "df_actor_all.loc[free_copilot_inds, 'Free_Copilot'] = True\n",
    "free_cp = df_actor_all[df_actor_all['Free_Copilot'] == True]['actor_id'].tolist()\n",
    "df_actor_all['Free_Copilot'] = df_actor_all['actor_id'].isin(free_cp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76ff4096-13e6-47c0-bcd2-fcda254f2964",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bots = pd.to_numeric(df_actor[df_actor['actor_login'].apply(lambda x: '[bot]' in x)]['actor_id']).unique()\n",
    "df_actor_all = df_actor_all[~df_actor_all['actor_id'].isin(bots)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16da743f-888e-4ffd-b194-eee68d346cee",
   "metadata": {},
   "source": [
    "## Issue Templates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d14a6d2b-376b-4b82-998e-0c2f93179cb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8688beeb-31ec-4a50-abea-dd1c54a191ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b527c321-611f-4640-9c28-d8eea43a18cf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_commits['commit file changes'] = df_commits['commit file changes'].parallel_apply(lambda x: json.loads(x) if x != None else x)\n",
    "df_commits['changed files'] = df_commits['commit file changes'].parallel_apply(lambda x: [ele['file'] for ele in x] if x != None else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5588476f-731b-443e-9f71-3c0918bf2e07",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_commits[df_commits['changed files'].apply(lambda x: any(['ISSUE_TEMPLATE' in ele for ele in x]) if x != None else False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1df5e681-4f24-4bc0-b0d8-c0ea7e220ebf",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8864ed15-f2dc-440d-88dd-e750eb4936c8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e56ca7d-48c9-487b-bf75-f8f446694765",
   "metadata": {},
   "outputs": [],
   "source": [
    "break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24e9c20d-4c53-4aa2-8477-8631f348bb1f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "30935f91-fe51-48e6-ba58-1076abb97053",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## Population Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2fa3930-5d2e-43a5-babb-32e1e7f89b6b",
   "metadata": {},
   "outputs": [],
   "source": [
    "special_actors = df_actor_all[df_actor_all['permissions'].isin(['owner','write'])]['actor_id'].unique().tolist()\n",
    "population = df_actor_all\n",
    "population['Free_Copilot'] = population['Free_Copilot'].astype(int)\n",
    "population['key'] = population['repo_id'].apply(lambda x: str(int(x))) + \"_\" + population['actor_id'].apply(lambda x: str(int(x)))\n",
    "population['top_repos'] = pd.to_numeric(population['repo_id'].isin(repo_ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "abcf1afc-6d84-44e9-974a-d16e880c01a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "watch_parquet = glob.glob('data/github_clean/filtered_github_data/watchEvent*.parquet')\n",
    "watch_parquet.extend(glob.glob('data/github_clean/github_data_pre_18/watchEvent*.parquet'))\n",
    "watch_parquet.extend(glob.glob('data/github_clean/github_data_2324/watchEvent*.parquet'))\n",
    "df_watch_parquet = pd.concat([read_parquet(ele, ['created_at', 'repo_id']) for ele in watch_parquet])\n",
    "df_watch_parquet['created_at'] = pd.to_datetime(df_watch_parquet['created_at'], format='mixed')\n",
    "df_watch = df_watch_parquet[df_watch_parquet['created_at']<datetime.datetime(2022, 6, 23, tzinfo = pytz.UTC)]\n",
    "\n",
    "fork_parquet = glob.glob('data/github_clean/filtered_github_data/forkEvent*.parquet')\n",
    "fork_parquet.extend(glob.glob('data/github_clean/github_data_pre_18/forkEvent*.parquet'))\n",
    "fork_parquet.extend(glob.glob('data/github_clean/github_data_2324/forkEvent*.parquet'))\n",
    "df_fork_parquet = pd.concat([read_parquet(ele, ['created_at', 'repo_id']) for ele in fork_parquet])\n",
    "df_fork_parquet['created_at'] = pd.to_datetime(df_fork_parquet['created_at'])\n",
    "df_fork = df_fork_parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08dea9cb-a3e3-4a2e-ad9d-23a3910f5a94",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_starsforks = pd.concat([df_watch.groupby('repo_id').count(), df_fork.groupby('repo_id').count()], axis = 1)\n",
    "df_starsforks.columns = ['stars_copilotrelease', 'forks_copilotrelease']\n",
    "df_starsforks = df_starsforks.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b09ca60e-50ec-4230-ae9c-16f6991fb318",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "individuals_contact = committer_info[['actor_id', 'emails', 'names']].explode(['actor_id']).explode(['emails']).explode(['names']).dropna()\n",
    "individuals_contact['actor_id'] = pd.to_numeric(individuals_contact['actor_id'])\n",
    "individuals_contact = individuals_contact.groupby('actor_id').agg({'emails':list, 'names': list}).reset_index()\n",
    "individuals_contact['emails'] = individuals_contact['emails'].apply(lambda x: list(set(x)))\n",
    "individuals_contact['names'] = individuals_contact['names'].apply(lambda x: list(set(x)))\n",
    "individuals_contact['Actual Name'] =individuals_contact['names'].apply(\n",
    "    lambda x: [ele for ele in x if (len(ele.split(\" \"))>=2 and \n",
    "               all([word[0].isupper() if len(word)>0 else False for word in ele.split(\" \")]))] if type(x) == list else x)\n",
    "individuals_contact['Actual Name'] = individuals_contact['Actual Name'].apply(lambda x: x if len(x)>0 and type(x) == list else np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62789d9d-5939-4789-b3a4-f9c031dec7ff",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "population = pd.merge(population, df_starsforks, how = 'left')\n",
    "population = pd.merge(population, individuals_contact, how = 'left')\n",
    "population = pd.merge(population, df_repo[['repo_id','repo_name']], how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5a16ea9-179e-4fb7-be2f-d52d0f7a12fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "population.to_parquet('data/intermediaries/population_copilot.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edf774be-fac5-4077-b1c2-fd773cb0488f",
   "metadata": {},
   "outputs": [],
   "source": [
    "break"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efde0bcb-1c16-4349-bc49-017b24b37f7d",
   "metadata": {},
   "source": [
    "## Forming the Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1fc15f6c-6b62-4129-b675-9b574db5c6b9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "issueEventData['created_at_month'] = issueEventData['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 1, tzinfo = pytz.UTC))\n",
    "prEventData['created_at_month'] = prEventData['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 1, tzinfo = pytz.UTC) if type(x.year) != float else np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17425957-504b-4918-9a1d-6a2bb8031e3a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_commits['created_at_month'] = df_commits['commit time'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 1, tzinfo = pytz.UTC) if type(x.year) != float else np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b489488-c12e-4623-8197-dcff253a1e7e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# opened issues\n",
    "df_active = pd.concat([\n",
    "    # issues opened\n",
    "            issueEventData[issueEventData['issue_action'] == 'opened'].groupby(\n",
    "                ['actor_id','repo_id','created_at_month'])['issue_action'].count().rename('issues_opened'),\n",
    "    # how many of the issues that were opened were associated with a PR that same person opened a PR for\n",
    "           issueEventData[issueEventData.apply(lambda x: x['issue_action'] == 'opened' and x['pr_user_id'] == x['actor_id'], axis = 1)].groupby(\n",
    "               ['actor_id','repo_id','created_at_month'])['issue_action'].count().rename('issues_opened_pr'),\n",
    "    # issue comments\n",
    "           issueEventData[issueEventData['type'] == 'IssueCommentEvent'].groupby(\n",
    "               ['actor_id','repo_id','created_at_month'])['type'].count().rename('issue_comments'),\n",
    "    # issue comments that required a PR\n",
    "           issueEventData[issueEventData.apply(lambda x: not pd.isnull(x['potential_issues']) and x['type'] == 'IssueCommentEvent', axis = 1)].groupby(\n",
    "               ['actor_id','repo_id','created_at_month'])['type'].count().rename('issue_comments_pr'),\n",
    "    # of unique issues that this person commented on\n",
    "           issueEventData[issueEventData['type'] == 'IssueCommentEvent'][['actor_id','repo_id','issue_number','created_at_month']].drop_duplicates(\n",
    "               ['actor_id','repo_id','issue_number','created_at_month']).groupby(\n",
    "               ['actor_id','repo_id','created_at_month'])['created_at_month'].count().rename('unique_issue_comments'),\n",
    "    # of unique issues that this person commented on that required a PR\n",
    "           issueEventData[issueEventData.apply(\n",
    "               lambda x: not pd.isnull(x['potential_issues']) and x['type'] == 'IssueCommentEvent', axis = 1)][['actor_id','repo_id','issue_number','created_at_month']].drop_duplicates().groupby(\n",
    "               ['actor_id','repo_id','created_at_month'])['created_at_month'].count().rename('unique_issue_comments_pr'),\n",
    "    # how many PRs were opened that month\n",
    "            prEventData[prEventData['pr_action'] == 'opened'][['actor_id','repo_id','pr_number','created_at_month']].drop_duplicates().groupby(\n",
    "                ['actor_id','repo_id','created_at_month'])['created_at_month'].count().rename('prs_opened'),\n",
    "    # how many of the PRs that were opened were associated with an issue\n",
    "            prEventData[(prEventData['actor_id'] == prEventData['issue_user_id']) &\n",
    "                        (prEventData['pr_action'] == 'opened')][['actor_id','repo_id','pr_number','created_at_month']].drop_duplicates().groupby(\n",
    "                ['actor_id','repo_id','created_at_month'])['created_at_month'].count().rename('prs_opened_issue'),\n",
    "          ], axis = 1).reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cbadd4bb-ccd2-4a13-95f6-4dd314296069",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_active = df_active.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f5b3519-f1c6-4fcf-aba1-744d19cc1358",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_all_dates = df_active.groupby(['actor_id','repo_id'])['created_at_month'].min().rename('earliest_appearance').reset_index()\n",
    "df_all_dates['created_at_month'] = df_all_dates['earliest_appearance'].apply(\n",
    "    lambda x: pd.date_range(x, datetime.datetime(2024, 2, 1, tzinfo = pytz.UTC), freq='MS'))\n",
    "df_all_dates = df_all_dates.explode('created_at_month').drop('earliest_appearance', axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e08161d1-2607-439f-aae1-d24e66511454",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_active['earliest_date'] = df_active.groupby(['actor_id','repo_id'])['created_at_month'].transform('min')\n",
    "df_active['latest_date'] = df_active.groupby(['actor_id','repo_id'])['created_at_month'].transform('max')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b7d5a49-b49c-4586-a434-060e7a0cf1fc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# generate months in between activity\n",
    "df_all_dates = pd.merge(df_all_dates, df_active[['actor_id','repo_id','earliest_date','latest_date']].drop_duplicates())\n",
    "df_all_dates_trim = df_all_dates[(df_all_dates['created_at_month'] >= df_all_dates['earliest_date']) &\n",
    "    (df_all_dates['created_at_month']<=df_all_dates['latest_date'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff2fba46-c7e9-431f-8b96-afc345d61b73",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# months\n",
    "df_active_full = pd.merge(df_all_dates_trim.drop(['earliest_date','latest_date'], axis = 1), df_active, how = 'left')\n",
    "df_active_full = df_active_full.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5e30ced-3e77-4c06-9aa4-dcd4e7cedf6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge in population rank data\n",
    "# read shapiro paper about event studies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1306da5-4f0d-4b55-ae10-07dee5963169",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_active_full_pop = pd.merge(df_active_full, population, how = 'left')\n",
    "df_active_full_pop['rank_count'] = df_active_full_pop.groupby(['actor_id','repo_id','created_at_month'])['actor_id'].transform('count')\n",
    "df_active_full_pop = df_active_full_pop[\n",
    "    (df_active_full_pop['created_at_month']+datetime.timedelta(days=31)>=df_active_full_pop['created_at']) |\n",
    "    (df_active_full_pop['rank_count'] == 1)].sort_values(\n",
    "    ['actor_id','repo_id','created_at_month','created_at'])\n",
    "df_active_full_pop = df_active_full_pop.drop_duplicates(['actor_id','repo_id','created_at_month'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aac9db98-c927-4bd0-9054-9c719152e27f",
   "metadata": {},
   "source": [
    "## Controls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2dc7e8d0-bee1-4492-9378-c4f4fa13e15c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_fork\n",
    "#df_watch"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cb8d405-2fd6-4fa9-99f5-2958320af59e",
   "metadata": {},
   "source": [
    "## Reference ONLY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9af77db-035e-4ecd-bb56-388bc6107aa8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_fork['created_at'] = pd.to_datetime(df_fork['created_at'], utc = True)\n",
    "df_watch['created_at'] = pd.to_datetime(df_watch['created_at'], utc = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffe0800e-c07e-4dc7-9657-bc8195eb63e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fork['created_at_month_year'] = df_fork['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "df_watch['created_at_month_year'] = df_watch['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "df_watch_fork = pd.concat([df_watch.groupby(['created_at_month_year', 'repo_id']).count(),\n",
    "                           df_fork.groupby(['created_at_month_year', 'repo_id']).count()], axis = 1)\n",
    "df_watch_fork.columns = ['stars', 'forks']\n",
    "df_watch_fork = pd.concat([df_watch_fork.fillna(0).reset_index().sort_values(\n",
    "    ['repo_id', 'created_at_month_year']),\n",
    "               df_watch_fork.fillna(0).reset_index().sort_values(\n",
    "                   ['repo_id', 'created_at_month_year']).groupby(\n",
    "                   ['repo_id'])[['stars','forks']].transform('cumsum')], axis = 1)\n",
    "df_watch_fork.columns = ['created_at_month_year', 'repo_id', 'gained stars', 'gained forks', 'cumulative stars', 'cumulative forks']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e5f4d1b-74a7-46e0-aaf1-4e8bc1ec5c3e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_repo_issue = issueEventData[issueEventData['issue_action'].isin(['opened', None])].sort_values(\n",
    "    'created_at').drop_duplicates(['repo_id','issue_number'])\n",
    "df_repo_issue['created_at_month_year'] = df_repo_issue['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "df_repo_pr = prEventData[prEventData['pr_action'].isin(['opened', None])].sort_values(\n",
    "    'created_at').drop_duplicates(['repo_id','pr_number'])\n",
    "df_repo_pr = df_repo_pr[~df_repo_pr['created_at'].isna()]\n",
    "df_repo_pr['created_at_month_year'] = df_repo_pr['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "df_issue_pr = pd.concat([df_repo_issue.groupby(['created_at_month_year', 'repo_id'])['type'].count(),\n",
    "                           df_repo_pr.groupby(['created_at_month_year', 'repo_id'])['type'].count()], axis = 1)\n",
    "df_issue_pr.columns = ['opened issues', 'opened prs']\n",
    "df_issue_pr = pd.concat([df_issue_pr.fillna(0).reset_index().sort_values(\n",
    "    ['repo_id', 'created_at_month_year']),\n",
    "               df_issue_pr.fillna(0).reset_index().sort_values(\n",
    "                   ['repo_id', 'created_at_month_year']).groupby(\n",
    "                   ['repo_id'])[['opened issues','opened prs']].transform('cumsum')], axis = 1)\n",
    "df_issue_pr.columns = ['created_at_month_year', 'repo_id', 'opened issues','opened prs', 'cumulative opened issues','cumulative opened prs']\n",
    "                         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "271be53c-bb09-45ad-b88f-4d878ea194f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ranked_v1['created_at_month_year'] = all_ranked_v1['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "\n",
    "df_ranks = pd.concat([all_ranked_v1[all_ranked_v1['permissions']=='triage'].groupby(\n",
    "    ['created_at_month_year','repo_id'])['organization'].count(),\n",
    "                         all_ranked_v1[all_ranked_v1['permissions'].isin(['triage','write','owner'])].groupby(\n",
    "    ['created_at_month_year','repo_id'])['organization'].count(),], axis = 1)\n",
    "df_ranks.columns = ['triagers', 'writers']\n",
    "df_ranks_cum = pd.concat([df_ranks.fillna(0).reset_index().sort_values(\n",
    "    ['repo_id', 'created_at_month_year']),\n",
    "               df_ranks.fillna(0).reset_index().sort_values(\n",
    "                   ['repo_id', 'created_at_month_year']).groupby(\n",
    "                   ['repo_id'])[['triagers','writers']].transform('cumsum')], axis = 1)\n",
    "df_ranks_cum.columns = ['created_at_month_year', 'repo_id', 'added triagers','added writers', 'cumulative triagers','cumulative writers']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "defc3e38-2528-43d3-9d60-1e0de6323d58",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_repo_controls = pd.merge(pd.merge(df_watch_fork, df_issue_pr, how = 'left'), df_ranks_cum, how = 'left').fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ce8e1c3-826a-4b1c-a8a4-8e34bcd21959",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_commits['created_at_month_year'] = df_commits['commit day'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83e4c8ea-23d9-4246-8078-270b3f646ea9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_contributing_count = df_commits[['repo_id', 'commit_actor_id', 'created_at_month_year']].drop_duplicates().groupby(\n",
    "    ['commit_actor_id','created_at_month_year']).count()\n",
    "df_contributing_count = df_contributing_count.reset_index()\n",
    "df_contributing_count.rename({'repo_id': 'commit_projects'}, axis = 1, inplace = True)\n",
    "df_commits_user = df_commits[['commit_actor_id', 'created_at_month_year']].value_counts()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "baebf57f-cde3-4a50-9790-030d7cf9945a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_repo_issue_user = issueEventData[issueEventData['issue_action'].isin(['opened', None])].sort_values(\n",
    "    'created_at').drop_duplicates(['actor_id','issue_number'])\n",
    "df_repo_issue_user['created_at_month_year'] = df_repo_issue['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "df_repo_pr_user = prEventData[prEventData['pr_action'].isin(['opened', None])].sort_values(\n",
    "    'created_at').drop_duplicates(['actor_id','pr_number'])\n",
    "df_repo_pr_user = df_repo_pr[~df_repo_pr['created_at'].isna()]\n",
    "df_repo_pr_user['created_at_month_year'] = df_repo_pr['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "df_issue_pr_user = pd.concat([df_repo_issue_user.groupby(['created_at_month_year', 'actor_id'])['type'].count(),\n",
    "                           df_repo_pr_user.groupby(['created_at_month_year', 'actor_id'])['type'].count()], axis = 1)\n",
    "df_issue_pr_user.columns = ['opened issues', 'opened prs']\n",
    "df_issue_pr_user = pd.concat([df_issue_pr_user.fillna(0).reset_index().sort_values(\n",
    "    ['actor_id', 'created_at_month_year']),\n",
    "               df_issue_pr_user.fillna(0).reset_index().sort_values(\n",
    "                   ['actor_id', 'created_at_month_year']).groupby(\n",
    "                   ['actor_id'])[['opened issues','opened prs']].transform('cumsum')], axis = 1)\n",
    "df_issue_pr_user.columns = ['created_at_month_year', 'actor_id', 'user opened issues','user opened prs', 'cumulative user opened issues','cumulative user opened prs']\n",
    "                         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58f91b4f-4922-466c-b07f-ae56e47c7f0a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_ranks_user = pd.concat([all_ranked_v1[all_ranked_v1['permissions']=='triage'].groupby(\n",
    "    ['created_at_month_year','actor_id'])['organization'].count(),\n",
    "                         all_ranked_v1[all_ranked_v1['permissions'].isin(['triage','write','owner'])].groupby(\n",
    "    ['created_at_month_year','actor_id'])['organization'].count(),], axis = 1)\n",
    "df_ranks_user.columns = ['triagers', 'writers']\n",
    "df_ranks_user_cum = pd.concat([df_ranks_user.fillna(0).reset_index().sort_values(\n",
    "    ['actor_id', 'created_at_month_year']),\n",
    "               df_ranks_user.fillna(0).reset_index().sort_values(\n",
    "                   ['actor_id', 'created_at_month_year']).groupby(\n",
    "                   ['actor_id'])[['triagers','writers']].transform('cumsum')], axis = 1)\n",
    "df_ranks_user_cum.columns = ['created_at_month_year', 'actor_id', 'user added triagers','user added writers', 'user cumulative triagers','user cumulative writers']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ae2b36f-c3d0-4022-b3a1-794d87c3d737",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "contributed_projects_forks_watch = pd.merge(df_commits[['repo_id', 'commit_actor_id', 'created_at_month_year']].drop_duplicates(),\n",
    "                                            df_watch_fork).groupby(['commit_actor_id','created_at_month_year'])[\n",
    "    ['gained stars','gained forks','cumulative stars','cumulative forks']].sum().reset_index()\n",
    "contributed_projects_forks_watch.columns = [\n",
    "    'commit_actor_id', 'created_at_month_year','user gained stars','user gained forks','user cumulative stars','user cumulative forks']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17748996-ad53-4ec5-9831-f9cc0c967d1d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_contributor_controls = pd.concat([df_commits_user.reset_index().set_index(['commit_actor_id','created_at_month_year']).rename(\n",
    "    {'count':'commit count'}, axis = 1),\n",
    "           df_contributing_count.set_index(['commit_actor_id','created_at_month_year']),\n",
    "           df_issue_pr_user.set_index(['actor_id','created_at_month_year']),\n",
    "           df_ranks_user_cum.set_index(['actor_id','created_at_month_year']),\n",
    "           #contributed_projects_forks_watch.set_index(['commit_actor_id','created_at_month_year'])\n",
    "                                    ], axis = 1)\n",
    "df_contributor_controls = df_contributor_controls.reset_index().rename({'level_0':'commit_actor_id'}, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73611b5e-c675-4af0-a02e-34f94b6ca6a1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_repo_controls\n",
    "df_contributor_controls"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3b516e5-3dcd-4d20-ae4b-3be616dcab5f",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "## DiD - Commits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fff0e7cb-7a50-468f-92fa-57ba5e0b4147",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pr_status_info = prEventData[['created_at', 'pr_number', 'repo_id', 'pr_action']].sort_values(\n",
    "    'created_at', ascending = True).dropna().drop_duplicates(['pr_number', 'repo_id', 'pr_action'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad32f0e0-24e4-4177-883f-feb90ee5d83f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_commits['key'] = df_commits['repo_id'].apply(lambda x: str(int(x))) + \"_\" + df_commits['commit_actor_id'].apply(lambda x: str(int(x)))\n",
    "\n",
    "\n",
    "df_copilot_commits = df_commits[df_commits['key'].isin(population['key'].tolist())]\n",
    "df_copilot_commits['created_at_month_year'] = df_copilot_commits['commit time'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "df_copilot_commits = df_copilot_commits[\n",
    "    (df_copilot_commits['created_at_month_year']>= datetime.datetime(2021, 6, 23, tzinfo = pytz.UTC))]\n",
    "\n",
    "pr_status_dict = pr_status_info[['pr_number','repo_id','pr_action']].set_index(['pr_number','repo_id']).to_dict()['pr_action']\n",
    "df_copilot_commits['pr_status'] = df_copilot_commits.apply(lambda x: pr_status_dict.get((x['pr_number'], x['repo_id']), np.nan), axis = 1)\n",
    "\n",
    "df_copilot_commits = pd.merge(df_copilot_commits, prEventData[['repo_id', 'pr_number','pr_merged_by_id']].dropna(), how = 'left')\n",
    "df_copilot_commits['pr_merged'] = (~df_copilot_commits['pr_merged_by_id'].isna()).astype(int)\n",
    "df_copilot_commits['push_ref'] = (df_copilot_commits['push_ref'].isin(['refs/heads/master', 'refs/heads/main'])).astype(int)\n",
    "df_copilot_commits['commit changes total pr merged'] = df_copilot_commits.apply(\n",
    "    lambda x: x['commit changes total']*x['pr_merged'], axis = 1)\n",
    "df_copilot_commits['commit changes total push main'] = df_copilot_commits.apply(\n",
    "    lambda x: x['commit changes total']*x['push_ref'], axis = 1)\n",
    "df_copilot_commits['commit changes added pr merged'] = df_copilot_commits.apply(\n",
    "    lambda x: x['commit additions']*x['pr_merged'], axis = 1)\n",
    "df_copilot_commits['commit changes added push main'] = df_copilot_commits.apply(\n",
    "    lambda x: x['commit additions']*x['push_ref'], axis = 1)\n",
    "df_copilot_commits['commit changes deleted pr merged'] = df_copilot_commits.apply(\n",
    "    lambda x: x['commit deletions']*x['pr_merged'], axis = 1)\n",
    "df_copilot_commits['commit changes deleted push main'] = df_copilot_commits.apply(\n",
    "    lambda x: x['commit deletions']*x['push_ref'], axis = 1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e7969e7-a3c2-4f8e-b4e7-b7879e8db622",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copilot_commits_grouped =  df_copilot_commits.groupby(['commit_actor_id', 'created_at_month_year', 'repo_id', 'type']).agg(\n",
    "    {'commit changes total':['sum', 'count'],\n",
    "     'commit additions': 'sum',\n",
    "     'commit deletions': 'sum',\n",
    "     'commit files changed count': 'sum',\n",
    "     'pr_merged':'sum','push_ref':'sum',\n",
    "     'commit changes total pr merged': 'sum',\n",
    "     'commit changes total push main': 'sum',\n",
    "     'commit changes added pr merged': 'sum',\n",
    "     'commit changes added push main': 'sum',\n",
    "     'commit changes deleted pr merged': 'sum',\n",
    "     'commit changes deleted push main': 'sum'}, ).reset_index()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b796ad5-b22b-47d8-89dc-de1b17d29bf3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_copilot_commits_grouped.columns =  ['commit_actor_id', 'created_at_month_year', 'repo_id','type',\n",
    "     'commit_changes_sum', 'commit_count', 'commit_additions_sum', 'commit_deletions_sum',\n",
    "     'commit_files_changed_sum', 'pr_merged_sum', 'push_main_sum','commit_changes_pr_merged_sum',\n",
    "     'commit_changes_push_main_sum', 'commit_additions_pr_merged_sum', 'commit_additions_push_main_sum',\n",
    "     'commit_deletions_pr_merged_sum', 'commit_deletions_push_main_sum',]\n",
    "df_copilot_commits_grouped = df_copilot_commits_grouped.pivot(\n",
    "    index = ['commit_actor_id', 'created_at_month_year','repo_id'],\n",
    "    columns =  'type', values = ['commit_changes_sum', 'commit_count', 'commit_additions_sum', 'commit_deletions_sum',\n",
    "                                 'commit_files_changed_sum','pr_merged_sum', 'push_main_sum','commit_changes_pr_merged_sum',\n",
    "                                'commit_changes_push_main_sum', 'commit_additions_pr_merged_sum', 'commit_additions_push_main_sum',\n",
    "                                 'commit_deletions_pr_merged_sum', 'commit_deletions_push_main_sum',]).reset_index()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "607d255b-0c2f-4742-a0a7-f170f4793ab3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_copilot_commits_grouped = df_copilot_commits_grouped.drop(\n",
    "    [('pr_merged_sum','push commits'), ('push_main_sum', 'pr commits'), ('commit_changes_push_main_sum','pr commits'),\n",
    "     ('commit_changes_pr_merged_sum','push commits'), ('commit_additions_push_main_sum','pr commits'),\n",
    "     ('commit_additions_pr_merged_sum','push commits'), ('commit_deletions_push_main_sum','pr commits'),\n",
    "     ('commit_deletions_pr_merged_sum','push commits')], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b64b0c7-5e09-419f-be1a-06ed02d3b351",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_copilot_commits_grouped.columns =  ['commit_actor_id', 'created_at_month_year', 'repo_id' ,'commit_changes_sum_pr','commit_changes_sum_push', \n",
    "     'commit_count_pr','commit_count_push', 'commit_additions_sum_pr','commit_additions_sum_push', \n",
    "     'commit_deletions_sum_pr','commit_deletions_sum_push', 'commit_files_changed_sum_pr',\n",
    "     'commit_files_changed_sum_push', 'pr_merged_sum', 'push_main_sum','commit_changes_pr_merged_sum',\n",
    "     'commit_changes_push_main_sum', 'commit_additions_pr_merged_sum', 'commit_additions_push_main_sum',\n",
    "     'commit_deletions_pr_merged_sum', 'commit_deletions_push_main_sum']\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "757b7da6-4068-48fb-be87-b9fbecdee70e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copilot_commits_grouped = pd.merge(\n",
    "    df_copilot_commits_grouped,\n",
    "    population[['actor_id', 'repo_id', 'Free_Copilot','greater_1000_stars','greater_500_stars']].rename({'actor_id':'commit_actor_id', 'Free_Copilot':'treatment'}, axis = 1).drop_duplicates())\n",
    "#df_copilot_commits_grouped = pd.merge(df_copilot_commits_grouped, df_repo_controls)\n",
    "#df_copilot_commits_grouped = pd.merge(df_copilot_commits_grouped, df_contributor_controls)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ce92a10-c79e-4dda-b0bf-e63c222181b5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_copilot_commits_grouped.fillna(0).to_csv('results/data/df_copilot_commits_grouped.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2895c0ec-ef65-4f54-947f-00489e3dcc5c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "issueEventData['issue_pull_request_number'] = issueEventData['issue_pull_request'].apply(lambda x: ast.literal_eval(x)['html_url'] if not pd.isnull(x) else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aed44428-c2f2-423c-b9cc-cfd4f3e27f6a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "issueEventData['issue_pull_request_number'] = pd.to_numeric(issueEventData['issue_pull_request_number'].apply(lambda x: x.split(\"/\")[-1] if not pd.isnull(x) else x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "795d2148-ccc3-4937-a547-3ba4c16ea291",
   "metadata": {},
   "outputs": [],
   "source": [
    "prEventData_na = prEventData[(~prEventData['repo_id'].isna()) & \n",
    "    (prEventData.apply(lambda x: not pd.isnull(x['pr_user_id']) or x['pr_action'] == 'opened', axis= 1))]\n",
    "prEventData_na['key'] = prEventData_na.apply(\n",
    "    lambda x: str(int(x['repo_id']))+\"_\"+\n",
    "    str(int(x['pr_user_id'])) if x['pr_action'] != 'opened' else str(int(x['actor_id'])), axis = 1)\n",
    "\n",
    "prEventData_copilot = prEventData_na[prEventData_na['key'].isin(population['key'].tolist())]\n",
    "prEventData_copilot['created_at_month_year'] = prEventData_copilot['created_at'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "\n",
    "linked_prs = issueEventData[['repo_id','issue_pull_request_number']].dropna().drop_duplicates()\n",
    "linked_prs['linked_pr'] = 1\n",
    "prEventData_copilot = pd.merge(prEventData_copilot, linked_prs.rename({'issue_pull_request_number':'pr_number'}, axis = 1), how = 'left')\n",
    "prEventData_copilot['linked_pr'] = prEventData_copilot['linked_pr'].fillna(0)\n",
    "\n",
    "prEventData_copilot = pd.merge(prEventData_copilot.drop('pr_merged_by_id', axis = 1), \n",
    "                                   prEventData[['repo_id', 'pr_number','pr_merged_by_id']].drop_duplicates(), how = 'left')\n",
    "prEventData_copilot['merged_pr'] = prEventData_copilot['pr_merged_by_id'].apply(lambda x: not pd.isnull(x)).astype(int)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5dbd8665-73ac-447b-b84a-cb3e9e17d286",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pr_commit_data = df_commits[['repo_id', 'created_at_month_year', 'commit changes total', 'commit additions','commit deletions']].groupby(\n",
    "    ['repo_id', 'created_at_month_year']).sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "042ea9b1-e9ba-4f3d-9b7d-4e712d1785e9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "prEventData_copilot = pd.merge(prEventData_copilot,pr_commit_data,how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "427f66fb-def5-40b2-8a6e-293bc06d1f7e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "prEventData_copilot['commit_changes_merged_prs'] = prEventData_copilot.apply(\n",
    "    lambda x: x['commit changes total'] * (1-int(pd.isnull(x['pr_merged_by_id']))), axis = 1)\n",
    "prEventData_copilot['commit_additions_merged_prs'] = prEventData_copilot.apply(\n",
    "    lambda x: x['commit additions'] * (1-int(pd.isnull(x['pr_merged_by_id']))), axis = 1)\n",
    "prEventData_copilot['commit_deletions_merged_prs'] = prEventData_copilot.apply(\n",
    "    lambda x: x['commit deletions'] * (1-int(pd.isnull(x['pr_merged_by_id']))), axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba09d1a0-017f-40e2-a0ef-b32d43e7e140",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_copilot_prs_grouped =  prEventData_copilot[['pr_user_id', 'repo_id', 'pr_number', 'created_at', \n",
    "                                               'created_at_month_year','linked_pr','merged_pr',\n",
    "                                              'commit changes total', 'commit additions','commit deletions',\n",
    "                                              'commit_changes_merged_prs','commit_additions_merged_prs','commit_deletions_merged_prs']].sort_values(\n",
    "    'created_at').drop_duplicates(\n",
    "    ['pr_user_id', 'repo_id', 'pr_number',]).groupby(\n",
    "    ['pr_user_id', 'created_at_month_year', 'repo_id']).agg(\n",
    "    {'pr_number':['count'], 'linked_pr':['sum'], 'merged_pr':'sum', \n",
    "    'commit changes total':'sum', 'commit additions':'sum','commit deletions':'sum',\n",
    "     'commit_changes_merged_prs':'sum', 'commit_additions_merged_prs':'sum','commit_deletions_merged_prs':'sum'}).reset_index()\n",
    "df_copilot_prs_grouped.columns = ['pr_user_id','created_at_month_year','repo_id','pr_number_count','linked_pr_sum','merged_pr_sum',\n",
    "                                 'pr_commit_changes','pr_commit_additions','pr_commit_deletions',\n",
    "                                 'merged_pr_commit_changes','merged_pr_commit_additions','merged_pr_commit_deletions']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "988ac7fd-2511-4085-8c3e-87989ad51c78",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copilot_prs_grouped = pd.merge(df_copilot_prs_grouped, \n",
    "         population[['actor_id', 'repo_id',  'Free_Copilot','greater_1000_stars','greater_500_stars']].rename({'actor_id':'pr_user_id', 'Free_Copilot':'treatment'}, axis = 1).drop_duplicates())\n",
    "#df_copilot_prs_grouped = pd.merge(df_copilot_prs_grouped, df_repo_controls)\n",
    "#df_copilot_prs_grouped = pd.merge(df_copilot_prs_grouped, df_contributor_controls.rename({'commit_actor_id':'pr_user_id'}, axis = 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a657fc3-ac84-45c0-9047-e363330153da",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copilot_prs_grouped.fillna(0).to_csv('results/data/df_copilot_prs_grouped.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "073f22e8-2a58-4b15-ab55-d09ad5cebd93",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_commits['push_ref_sum'] = df_commits['push_ref'].apply(lambda x: x in ['refs/heads/master', 'refs/heads/main'])\n",
    "df_commits['push_ref_changes'] = pd.to_numeric(df_commits['push_ref_sum'] * df_commits['commit changes total'])\n",
    "df_commits['push_ref_additions'] = pd.to_numeric(df_commits['push_ref_sum'] * df_commits['commit additions'])\n",
    "df_commits['push_ref_deletions'] = pd.to_numeric(df_commits['push_ref_sum'] * df_commits['commit deletions'])\n",
    "\n",
    "df_push = df_commits[\n",
    "    ['push_id', 'commit_actor_id', 'commit time', 'repo_id', 'push_ref_sum','push_ref_changes','push_ref_additions','push_ref_deletions',\n",
    "    'commit changes total','commit additions', 'commit deletions']].dropna().sort_values('commit time').drop_duplicates(\n",
    "    ['push_id', 'commit_actor_id', 'repo_id']).groupby(\n",
    "    ['commit_actor_id','repo_id', 'commit time']).agg(\n",
    "    {'push_id':'count', 'push_ref_sum':'sum','push_ref_changes':'sum', 'push_ref_additions':'sum', 'push_ref_deletions':'sum',\n",
    "     'commit changes total':'sum', 'commit additions':'sum','commit deletions':'sum', }).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac839752-863a-43c4-8095-3768ad625997",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_push['key'] = df_push.apply(\n",
    "    lambda x: str(int(x['repo_id']))+\"_\"+str(int(x['commit_actor_id'])), axis = 1)\n",
    "\n",
    "df_push = df_push[df_push['key'].isin(population['key'].tolist())]\n",
    "df_push['created_at_month_year'] = df_push['commit time'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))\n",
    "df_push.rename({'push_id':'push_count', 'push_ref_sum':'push_main_count'}, axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efedea85-0441-4794-8e7f-014ab0ace44e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_copilot_push_grouped = pd.merge(\n",
    "    df_push.groupby(['created_at_month_year', 'commit_actor_id', 'repo_id'])[\n",
    "    ['push_count','push_main_count','commit changes total','commit additions','commit deletions',\n",
    "     'push_ref_changes','push_ref_additions','push_ref_deletions',]].sum().reset_index(),\n",
    "    population[['actor_id',  'repo_id', 'Free_Copilot','greater_1000_stars','greater_500_stars']].rename(\n",
    "        {'actor_id':'commit_actor_id', 'Free_Copilot':'treatment'}, axis = 1).drop_duplicates())\n",
    "#df_copilot_push_grouped = pd.merge(df_copilot_push_grouped, df_repo_controls)\n",
    "#df_copilot_push_grouped = pd.merge(df_copilot_push_grouped, df_contributor_controls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39c1546c-398f-4cdf-a1ee-33ec470a6b48",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copilot_push_grouped.fillna(0).to_csv('results/data/df_copilot_push_grouped.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a170ac9-8617-43e4-b970-ff4ab99fd299",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "prEventDataMerged = prEventData[~prEventData['pr_merged_by_login'].isna()][['repo_id', 'pr_number']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "424402d3-7e9e-4761-9199-0c633063d3e5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pr_commits_earliest = df_commits.groupby(['repo_id', 'pr_number']).agg({'commit time':['min', 'count']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61279b98-ad8b-44aa-96c6-44ad9be52558",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_lengths = pd.merge(prEventData,prEventDataMerged).groupby(['repo_id', 'actor_id','pr_number', 'pr_action'])['created_at'].min().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa473af3-097c-4983-87f2-1ec9d6fbdec7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_lengths['created_at'] = df_pr_lengths['created_at'].astype(str)\n",
    "df_pr_lengths_wide = df_pr_lengths.pivot(index = ['repo_id', 'actor_id', 'pr_number'], columns = 'pr_action',values = 'created_at')\n",
    "df_pr_lengths_wide = df_pr_lengths_wide[df_pr_lengths_wide.columns[:2]].dropna().reset_index()\n",
    "df_pr_lengths_wide.columns = ['repo_id', 'actor_id', 'pr_number', 'closed', 'opened']\n",
    "df_pr_lengths_wide['closed'] = pd.to_datetime(df_pr_lengths_wide['closed'])\n",
    "df_pr_lengths_wide['opened']  = pd.to_datetime(df_pr_lengths_wide['opened'])\n",
    "df_commits_pr_grouped = df_commits.groupby(['pr_number', 'repo_id']).agg(\n",
    "    {'commit time':['count', 'min', 'max'], 'commit changes total':'sum', }).reset_index().drop_duplicates()\n",
    "df_commits_pr_grouped.columns = ['pr_number', 'repo_id', 'commits', 'earliest_date', 'latest_date', 'commit changes']\n",
    "\n",
    "df_pr_lengths_wide = pd.merge(df_pr_lengths_wide, df_commits_pr_grouped)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ef0a1c9-75e8-4d5d-93d6-780f0fdd4de8",
   "metadata": {},
   "outputs": [],
   "source": [
    "pr_commits_earliest = pr_commits_earliest.reset_index()\n",
    "pr_commits_earliest.columns = ['repo_id', 'pr_number', 'earliest_commit_time', 'commit_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c57016a9-748c-4e65-b888-faea5d29370c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_lengths_wide = pd.merge(df_pr_lengths_wide,pr_commits_earliest)\n",
    "df_pr_lengths_wide['hours'] = (df_pr_lengths_wide['closed']-df_pr_lengths_wide['opened']).apply(lambda x: x.total_seconds()/3600)\n",
    "df_pr_lengths_wide['opened'] = pd.to_datetime(df_pr_lengths_wide['opened'], utc = True)\n",
    "df_pr_lengths_wide['earliest_commit_time'] = pd.to_datetime(df_pr_lengths_wide['earliest_commit_time'], utc = True)\n",
    "df_pr_lengths_wide['created_at_month_year'] = np.minimum(df_pr_lengths_wide['opened'],df_pr_lengths_wide['earliest_commit_time']).apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eaab0932-a61c-42c3-8a78-d01d7f4571e6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_lengths_wide['hours_per_commit'] = df_pr_lengths_wide['hours']/df_pr_lengths_wide['commits']\n",
    "df_pr_lengths_wide['hours_per_commit_change'] = df_pr_lengths_wide['hours']/df_pr_lengths_wide['commit changes']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f803404-e7ac-4a05-a48a-f64e34d6d4b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pr_lengths_wide['code_hours'] = (df_pr_lengths_wide['latest_date']-df_pr_lengths_wide['earliest_date']).apply(lambda x: x.total_seconds()/3600)\n",
    "df_pr_lengths_wide['code_hours_per_commit'] = df_pr_lengths_wide['code_hours']/df_pr_lengths_wide['commits']\n",
    "df_pr_lengths_wide['code_hours_per_commit_change'] = df_pr_lengths_wide['code_hours'] /df_pr_lengths_wide['commit changes']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df6ad341-b513-4da1-b155-7eafc197c2ea",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_pr_lengths_agg = df_pr_lengths_wide.groupby(['repo_id','actor_id','created_at_month_year']).agg(\n",
    "    {'hours':'mean','hours_per_commit':'mean','hours_per_commit_change':'mean',\n",
    "     'commit_count':'sum', 'commit changes':'sum',\n",
    "    'code_hours':'mean','code_hours_per_commit':'mean', 'code_hours_per_commit_change':'mean',}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f77d6d01-db2b-4cdf-9f84-8c29bb9ee65d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copilot_pr_lengths_grouped = pd.merge(\n",
    "    df_pr_lengths_agg, \n",
    "    population[['actor_id',  'repo_id', 'Free_Copilot','greater_1000_stars','greater_500_stars']].rename({'Free_Copilot':'treatment'}, axis = 1).drop_duplicates())\n",
    "#df_copilot_pr_lengths_grouped = pd.merge(df_copilot_pr_lengths_grouped, df_repo_controls)\n",
    "#df_copilot_pr_lengths_grouped = pd.merge(df_copilot_pr_lengths_grouped, df_contributor_controls.rename({'commit_actor_id':'actor_id'}, axis = 1))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3800914e-3ddc-4329-89ac-83a87c72cdcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copilot_pr_lengths_grouped.fillna(0).to_csv('results/data/df_copilot_pr_lengths_grouped.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef1f6157-8a2a-4364-8827-294541eef124",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pushEventData['created_at'] = pd.to_datetime(pushEventData['created_at'], utc = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9c0fe1f-b75c-41f3-b400-592708eeb72c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_push_lengths = pd.merge(df_commits[df_commits['type'] == 'push commits'][['repo_id', 'push_id','commit time', 'commit changes total', 'commit files changed count']].rename({'commit time':'earliest_date'},axis= 1),\n",
    "                           pushEventData[['push_id','repo_id', 'actor_id','created_at', 'push_ref']]).groupby(\n",
    "    ['repo_id', 'actor_id','push_id', 'push_ref']).agg(\n",
    "    {'created_at': 'min', 'earliest_date':['min','max'], 'commit changes total':'sum','commit files changed count':'count'}).reset_index()\n",
    "\n",
    "df_push_lengths.columns = [\n",
    "    'repo_id', 'actor_id','push_id', 'push_ref', 'created_at','earliest_date','latest_date','commit changes total','commit files changed count']\n",
    "df_push_lengths['created_at'] = pd.to_datetime(df_push_lengths['created_at'], utc = True)\n",
    "df_push_lengths['earliest_date'] = df_push_lengths['earliest_date'].dt.tz_localize(pytz.UTC)\n",
    "df_push_lengths = df_push_lengths[(df_push_lengths['created_at']-df_push_lengths['earliest_date']).dt.days>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f08a8bd-44d7-4d05-b6d8-a4055c910057",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_push_lengths['hours'] = (df_push_lengths['created_at']-df_push_lengths['earliest_date']).dt.total_seconds()/3600\n",
    "df_push_lengths['hours_per_commit'] = (df_push_lengths['hours'])/df_push_lengths['commit files changed count']\n",
    "df_push_lengths['hours_per_commit_change'] = (df_push_lengths['hours'])/df_push_lengths['commit changes total']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "800c9ba6-d710-4573-93e5-a75e845dae72",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_copilot_push_lengths = pd.merge(df_push_lengths, \n",
    "                                   population[['actor_id', 'Free_Copilot']].rename({'Free_Copilot':'treatment'}, axis = 1).drop_duplicates())\n",
    "df_copilot_push_lengths['created_at_month_year'] = df_copilot_push_lengths['earliest_date'].apply(\n",
    "    lambda x: datetime.datetime(x.year, x.month, 28, tzinfo = pytz.UTC))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a15b38a3-9d03-46eb-878f-bbf3fe0c9d86",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_copilot_push_lengths_grouped = df_copilot_push_lengths.groupby(\n",
    "    ['repo_id','actor_id', 'created_at_month_year'])[['hours','hours_per_commit','hours_per_commit_change',]].mean().reset_index()\n",
    "df_copilot_push_lengths_grouped = pd.merge(df_copilot_push_lengths_grouped, \n",
    "         population[['actor_id', 'repo_id',  'Free_Copilot','greater_1000_stars','greater_500_stars']].rename({'Free_Copilot':'treatment'}, axis = 1).drop_duplicates())\n",
    "#df_copilot_push_lengths_grouped = pd.merge(df_copilot_push_lengths_grouped, df_repo_controls)\n",
    "#df_copilot_push_lengths_grouped = pd.merge(df_copilot_push_lengths_grouped, df_contributor_controls.rename({'commit_actor_id':'actor_id'}, axis = 1))\n",
    "df_copilot_push_lengths_grouped.fillna(0).to_csv('results/data/df_copilot_push_lengths_grouped.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8b7cb05-3bd4-4fbb-bdfd-3d637fcc9e55",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_copilot_push_lengths_grouped_main = df_copilot_push_lengths[df_copilot_push_lengths['push_ref'].isin(['refs/heads/master','refs/heads/main'])].groupby(\n",
    "    ['repo_id','actor_id', 'created_at_month_year'])[['hours','hours_per_commit','hours_per_commit_change',]].mean().reset_index()\n",
    "df_copilot_push_lengths_grouped_main = pd.merge(df_copilot_push_lengths_grouped_main, \n",
    "         population[['actor_id', 'repo_id',  'Free_Copilot','greater_1000_stars','greater_500_stars']].rename({'Free_Copilot':'treatment'}, axis = 1).drop_duplicates())\n",
    "#df_copilot_push_lengths_grouped = pd.merge(df_copilot_push_lengths_grouped, df_repo_controls)\n",
    "#df_copilot_push_lengths_grouped = pd.merge(df_copilot_push_lengths_grouped, df_contributor_controls.rename({'commit_actor_id':'actor_id'}, axis = 1))\n",
    "df_copilot_push_lengths_grouped_main.fillna(0).to_csv('results/data/df_copilot_push_lengths_main_grouped.csv')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aab6efa4-a982-48c4-a64f-a699b6ca825c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copilot_push_lengths_grouped_notmain = df_copilot_push_lengths[~df_copilot_push_lengths['push_ref'].isin(['refs/heads/master','refs/heads/main'])].groupby(\n",
    "    ['repo_id','actor_id', 'created_at_month_year'])[['hours','hours_per_commit','hours_per_commit_change',]].mean().reset_index()\n",
    "df_copilot_push_lengths_grouped_notmain = pd.merge(df_copilot_push_lengths_grouped_notmain, \n",
    "         population[['actor_id',  'repo_id', 'Free_Copilot','greater_1000_stars','greater_500_stars']].rename({'Free_Copilot':'treatment'}, axis = 1).drop_duplicates())\n",
    "#df_copilot_push_lengths_grouped = pd.merge(df_copilot_push_lengths_grouped, df_repo_controls)\n",
    "#df_copilot_push_lengths_grouped = pd.merge(df_copilot_push_lengths_grouped, df_contributor_controls.rename({'commit_actor_id':'actor_id'}, axis = 1))\n",
    "df_copilot_push_lengths_grouped_notmain.fillna(0).to_csv('results/data/df_copilot_push_lengths_notmain_grouped.csv')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee94c4ec-b638-47f8-b553-10eadf01277d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_all_data = pd.concat([df_copilot_commits_grouped.fillna(0).rename({'commit_actor_id':'actor_id'}, axis = 1).set_index(\n",
    "    ['actor_id','created_at_month_year','repo_id']).drop([\n",
    "               'treatment','greater_1000_stars','greater_500_stars'], axis = 1),\n",
    "           df_copilot_push_lengths_grouped.fillna(0).rename(\n",
    "               {'hours':'avg_hours_per_push', 'hours_per_commit':'avg_hours_per_push_commit', \n",
    "                'hours_per_commit_change':'avg_hours_per_push_commit_change'}, axis = 1).set_index(\n",
    "               ['actor_id','created_at_month_year','repo_id']).drop([\n",
    "               'treatment','greater_1000_stars','greater_500_stars'], axis = 1),\n",
    "           df_copilot_pr_lengths_grouped.fillna(0).set_index(['actor_id','created_at_month_year','repo_id']).rename(\n",
    "               {'hours':'avg_hours_per_pr', 'hours_per_commit':'avg_hours_per_pr_commit', \n",
    "                'hours_per_commit_change':'avg_hours_per_pr_commit_change',\n",
    "               'commit_count':'pr_commit_count', 'commit changes': 'pr_length_commit_changes',\n",
    "               'code_hours':'pr_code_hours','code_hours_per_commit':'code_hours_per_pr_commit',\n",
    "               'code_hours_per_commit_change':'code_hours_per_pr_commit_change', }, axis = 1).drop([\n",
    "               'treatment','greater_1000_stars','greater_500_stars'], axis = 1),\n",
    "           df_copilot_push_grouped.fillna(0).rename({'commit_actor_id':'actor_id'}, axis = 1).set_index(\n",
    "               ['actor_id','created_at_month_year','repo_id']).rename(\n",
    "               {'commit changes total':'push_commit_changes_total','commit additions':'push_commit_additions',\n",
    "                'commit deletions':'push_commit_deletions', 'push_ref_changes':'push_main_commit_changes',\n",
    "               'push_ref_additions':'push_main_commit_additions', 'push_ref_deletions':'push_main_commit_deletions'}, axis = 1).drop([\n",
    "               'treatment','greater_1000_stars','greater_500_stars'], axis = 1),\n",
    "           df_copilot_prs_grouped.fillna(0).rename({'pr_user_id':'actor_id'}, axis = 1).set_index(\n",
    "               ['actor_id','created_at_month_year','repo_id']).rename({\n",
    "               'pr_number_count':'pr_count', 'linked_pr_sum':'issue_pr_count', 'merged_pr_sum':'merged_pr_count'}, axis = 1).drop([\n",
    "               'treatment','greater_1000_stars','greater_500_stars'], axis = 1),\n",
    "          ], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36121590-a982-4ff6-b347-0203ee1e1c37",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data.rename({'pr_merged_sum':'commit_count_pr_merged',\n",
    "         'push_main_sum': 'commit_count_push_main'}, axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "843aa319-853e-4f85-b464-255be6e600f6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "agg_dict = {}\n",
    "for col in df_all_data.columns[3:]:\n",
    "    agg_dict[col] = 'sum'\n",
    "agg_dict['repo_id'] = 'count'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb355de6-3ccc-481b-9aa8-2993269c7f02",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data = df_all_data.reset_index()\n",
    "df_all_data.fillna('NaN')\n",
    "df_all_data['active_repos'] = df_all_data.groupby(['actor_id','created_at_month_year'])['repo_id'].transform('count')\n",
    "df_all_data = df_all_data.groupby(['actor_id','created_at_month_year','active_repos','repo_id'], dropna = False).sum(min_count=1).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "163ff7a9-dec5-4bc1-918f-8a64c7aa768b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_all_data_final = pd.merge(\n",
    "    df_all_data,#[(~df_all_data['commit_count_pr'].isna())],\n",
    "    population[['actor_id','repo_id','Free_Copilot']].drop_duplicates().rename(\n",
    "             {'Free_Copilot':'treatment'}, axis = 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31d853fd-9c79-4a39-9f3a-8655bd3283df",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_fork_total = df_fork[['created_at_month_year','repo_id']].value_counts().reset_index().sort_values(\n",
    "    'created_at_month_year').rename({'count':'fork_count'}, axis = 1)\n",
    "df_fork_total['cumulative_forks'] = df_fork_total.groupby('repo_id').transform('cumcount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1783fac5-a33a-4148-8bf6-a8367f0ced13",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_watch_total = df_watch[['created_at_month_year','repo_id']].value_counts().reset_index().sort_values(\n",
    "    'created_at_month_year').rename({'count':'watch_count'}, axis = 1)\n",
    "df_watch_total['cumulative_watches'] = df_watch_total.groupby('repo_id').transform('cumcount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35525256-4ec1-4d10-98f1-44dc80eed119",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data_final['month'] = df_all_data_final['created_at_month_year'].dt.month\n",
    "df_all_data_final['appearances'] = df_all_data_final.sort_values('created_at_month_year').groupby(\n",
    "    ['actor_id','repo_id']).transform('cumcount')\n",
    "df_all_data_final['coworkers'] = df_all_data_final.groupby(['repo_id','created_at_month_year'])['month'].transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19804eef-0505-4281-a31d-1d85272b434b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# how many issues did they open\n",
    "# how many issues did they comment on?\n",
    "# how many issues is the project dealing with\n",
    "issueEventData['created_at_month_year'] = issueEventData['created_at'].apply(lambda x: datetime.datetime(x.year, x.month, 28, 0 ,0, 0, tzinfo = pytz.UTC))\n",
    "repo_issue_opened = issueEventData[issueEventData['issue_action'] == 'opened'].groupby(\n",
    "    ['repo_id', 'created_at_month_year'])['created_at'].count().rename('repo_issues_opened').reset_index()\n",
    "repo_issue_closed = issueEventData[issueEventData['issue_action'] == 'closed'].groupby(\n",
    "    ['repo_id', 'created_at_month_year'])['created_at'].count().rename('repo_issues_closed').reset_index()\n",
    "user_issue_handling = issueEventData[issueEventData['type'] == 'IssueEvent'].groupby(\n",
    "    ['actor_id','repo_id', 'created_at_month_year'])['created_at'].count().rename('issues_managed').reset_index()\n",
    "user_issue_comments = issueEventData[issueEventData['type'] == 'IssueCommentEvent'].groupby(\n",
    "    ['actor_id','repo_id', 'created_at_month_year'])['created_at'].count().rename('comments_made').reset_index()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78ff0528-57b1-4ca0-b13f-76c1e276805e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data_final = pd.merge(pd.merge(df_all_data_final, df_fork_total, how = 'left'), df_watch_total, how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7bd0113-0605-4e91-890b-27a4574d45d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data_final = pd.merge(pd.merge(pd.merge(pd.merge(df_all_data_final, \n",
    "                                               repo_issue_opened, how = 'left'), user_issue_handling, how = 'left'),\n",
    "                             user_issue_comments, how = 'left'),\n",
    "                             repo_issue_closed, how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "546107b1-72bc-4945-b79d-aea0331bfaa0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data_final['repos_2000'] = df_all_data_final['repo_id'].isin(repos2000)\n",
    "df_all_data_final['repos_1000'] = df_all_data_final['repo_id'].isin(repos1000)\n",
    "df_all_data_final['repos_500'] = df_all_data_final['repo_id'].isin(repos500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67624d7a-80eb-41e3-a772-1fdfe62f01ea",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_all_dates = df_all_data_final.groupby(['actor_id','repo_id'])['created_at_month_year'].min().rename('earliest_appearance').reset_index()\n",
    "df_all_dates['created_at_month_year'] = df_all_dates['earliest_appearance'].apply(\n",
    "    lambda x: pd.date_range(x - pd.DateOffset(days=27), \n",
    "                            datetime.datetime(2024, 2, 28, tzinfo = pytz.UTC), freq='MS') + pd.DateOffset(days=27))\n",
    "df_all_dates = df_all_dates.explode('created_at_month_year').drop('earliest_appearance', axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff6be383-a8a3-4625-89fd-fb8d4fd1b2cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data_final['earliest_date'] = df_all_data_final.groupby(['actor_id','repo_id'])['created_at_month_year'].transform('min')\n",
    "df_all_data_final['latest_date'] = df_all_data_final.groupby(['actor_id','repo_id'])['created_at_month_year'].transform('max')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f6124c8-2d3c-4076-a222-4b9922fe0c7f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_all_dates.set_index(['actor_id','repo_id','created_at_month_year'], inplace = True)\n",
    "df_all_data_final.set_index(['actor_id','repo_id','created_at_month_year'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bc7524b-8084-4fa8-b8e7-cb48ee52291b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data_2[(df_all_data_2['repos_2000'] == True) & (df_all_data_2['treatment'] == 1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caada908-75b1-4c01-a686-2faddbd76033",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "python_opensource[python_opensource['repo_id'] == 25437858]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e3ea86f-20aa-4ef3-8e12-b4fac9ce88c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_actor_all[df_actor_all['actor_id'] == 71]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e8b3277-8d40-44a1-8a7f-445be49cf255",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_repo[df_repo['repo_id'] == 25437858.0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73ffcad4-d692-4788-8171-cf5c9e2b2e46",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43e406ab-4a5c-4ce5-b1a0-071d1495a625",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data = pd.concat([df_all_dates,df_all_data_final], axis = 1).reset_index()\n",
    "df_all_data.sort_values(['actor_id','repo_id','created_at_month_year'], inplace = True)\n",
    "df_all_data[['earliest_date','latest_date']] = df_all_data[['earliest_date','latest_date']].fillna(method = 'ffill')\n",
    "df_all_data = df_all_data[df_all_data['created_at_month_year']>=df_all_data['earliest_date']]\n",
    "df_all_data = df_all_data[df_all_data['created_at_month_year']<=df_all_data['latest_date']]\n",
    "df_all_data['created_at_year'] = df_all_data['created_at_month_year'].apply(lambda x: x.year)\n",
    "df_all_data = df_all_data[df_all_data['created_at_year']<2025]\n",
    "df_all_data[['repos_2000','repos_1000','repos_500']] = df_all_data[['repos_2000','repos_1000','repos_500']].fillna(method = 'ffill')\n",
    "df_all_data_2 = df_all_data.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28e6882f-518c-4bcc-b8b6-c00af7b681c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data_2.drop('df_all_dates', axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43b4fb80-5f18-450c-8919-0917cd761240",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all_data_2.rename({'pr_merged_sum':'commit_count_pr_merged',\n",
    "         'push_main_sum': 'commit_count_push_main'}, axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61260d8f-78d5-424d-ac83-ba73a59e7e99",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_all_data_2.to_parquet('results/data/df_final.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6927bf8-dd26-430d-820c-d82bd92875c9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51d2d1d2-d7ff-40c9-8743-1809d2202bca",
   "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
}