TrackCodeUsage.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "from IPython.display import Image, HTML\n",
    "\n",
    "import time\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## This script assumes the following files are available:\n",
    "\n",
    "- commit-to-timestamp (c2ta) map for the other commits. \n",
    "- blob-to-commit (b2c) map for the hackathon commits \n",
    "- commit-to-project (c2P) map for the other commits \n",
    "- project classification file ('discretized_new.csv')\n",
    "- Blob file type csv file ('Hack_b2type.csv')\n",
    "- Project info from DEVPOST ('PrAllInfoDEVPOST.csv')\n",
    "- DEVPOST project-to-wocURL information (woc-urls.csv) csv file \n",
    "- Project Info from MongoDB ('PrAllInfo.csv')\n",
    "- CSV file for the results from CodeGeneration notebook ('finalResultDF.csv')\n",
    "\n",
    "See README for corresponding commands using World of Code tool."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### UsageFlag Calculation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CommitsDF = pd.read_csv('SampleData/c2taAll.csv',sep=';')\n",
    "b2cDF = pd.read_csv('SampleData/b2c.csv',sep=';')\n",
    "c2PDF = pd.read_csv('SampleData/c2PAll.csv',sep=';')\n",
    "\n",
    "CompareDF3 = pd.read_csv('SampleData/finalResultDF.csv',sep=';')  ## The output of the CodeGeneration notebook\n",
    "PrCatDF = pd.read_csv('discretized_new.csv', sep=',')\n",
    "PrInfoDEVPOST = pd.read_csv('PrAllInfoDEVPOST.csv', sep=';')\n",
    "PrInfo2 = pd.read_csv('PrAllInfo.csv', sep=';')\n",
    "wocUrl = pd.read_csv('woc-urls.csv',sep = ',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filters\n",
    "\n",
    "del CompareDF3['Unnamed: 0']\n",
    "CompareDF3 = CompareDF3[CompareDF3['AuthorFlag'].isin([1,2])]   ## Author and Co-Author\n",
    "CompareDF3 = CompareDF3[CompareDF3['TimingFlag'].isin([2])]   ## During only\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join to get all commits of each blob\n",
    "Comb1 = pd.merge(CompareDF3, b2cDF2 , how='inner', left_on = ['BlobHash'], right_on = ['BlobHash']).drop_duplicates()#[['devpost_id','ProjectID','hackathonStartDate','hackathonEndDate','BlobHash','FirstTimestamp','FirstAuthorID','TimingFlag','AuthorFlag']].drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join to add commit timestamp\n",
    "\n",
    "Comb2 = pd.merge(Comb1, CommitsDF2 , how='inner', left_on = ['BCommitHash'], right_on = ['CommitHash']).drop_duplicates()#[['devpost_id','ProjectID','hackathonStartDate','hackathonEndDate','BlobHash','FirstTimestamp','FirstAuthorID','TimingFlag','AuthorFlag']].drop_duplicates()\n",
    "del Comb2['BCommitHash']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter\n",
    "Comb3 = Comb2.loc[(Comb2['CTimeStamp'] >= Comb2['hackathonStartDate'])]  ## Only Commits after hackathon start date\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join to add project for each commit\n",
    "\n",
    "Comb4 = pd.merge(Comb3, c2P_FullDF , how='inner', left_on = ['CommitHash'], right_on = ['CommitHash']).drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join to add project classification of each commit\n",
    "\n",
    "Comb5 = pd.merge(Comb4, PrCatDF , how='inner', left_on = ['CProject'], right_on = ['projectID']).drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# logic to get UsageFlag\n",
    "\n",
    "def checkUsage(row):\n",
    "    HackProject = row.ProjectID_x\n",
    "    CommitProject = row.CProject\n",
    "    \n",
    "    if(HackProject == CommitProject):\n",
    "        return '1'   # Same Hackathon Project\n",
    "    elif(row.flag == 1):\n",
    "        return '3'   # Usage in a Small Projects\n",
    "    elif(row.flag == 2):\n",
    "        return '4'   # Used in Medium Projects\n",
    "    elif(row.flag == 3):\n",
    "        return '5'   # Used in Large Projects\n",
    "\n",
    "\n",
    "    \n",
    "def iterrows_impl(df):\n",
    "    return pd.Series(\n",
    "        checkUsage(row)     \n",
    "        for row in df.itertuples()\n",
    "    )\n",
    "  \n",
    "\n",
    "Comb5['UsageFlag'] = pd.Series(iterrows_impl(Comb5))\n",
    "\n",
    "Comb5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concat the results on the correct level\n",
    "\n",
    "result = Comb5[['devpost_id','ProjectID_x','hackathonStartDate','hackathonEndDate','BlobHash','TimingFlag','AuthorFlag','UsageFlag']].groupby(['devpost_id','ProjectID_x','hackathonStartDate','hackathonEndDate','BlobHash','TimingFlag','AuthorFlag']).agg({'UsageFlag':lambda x : ','.join(set(x))}).reset_index()\n",
    "print(set(result['UsageFlag']))\n",
    "result.to_csv('final_result.csv',sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalResult = result.copy()\n",
    "finalResult"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "result2 = finalResult[['BlobHash','UsageFlag']].groupby(['UsageFlag']).agg(['count'])\n",
    "result2['Percentage'] = result2.apply(lambda x: 100 * x / float(x.sum()))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from matplotlib.pyplot import figure\n",
    "from matplotlib import pyplot as plt \n",
    "from matplotlib.gridspec import GridSpec\n",
    "\n",
    "figure(num=None, figsize=(8, 6), dpi=80, facecolor='w', edgecolor='k')\n",
    "\n",
    "x = result2.sort_values(by=['Percentage']).reset_index()['UsageFlag'].values.flatten()\n",
    "data = result2.sort_values(by=['Percentage'])[['Percentage']].values.flatten() \n",
    "\n",
    "x_pos = [i for i, _ in enumerate(x)]\n",
    "\n",
    "plt.barh(x_pos, data, color='black')\n",
    "plt.ylabel(\"Blob Usage Category\")\n",
    "plt.xlabel(\"Percentage\")\n",
    "plt.title(\"Percentage of usage categories\")\n",
    "\n",
    "plt.yticks(x_pos, x)\n",
    "\n",
    "for i, v in enumerate(data):\n",
    "    plt.text(v +0.5, i -0.1, str(round(v,2)) + '%', color='Black')\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from matplotlib.pyplot import figure\n",
    "figure(num=None, figsize=(8, 6), dpi=80, facecolor='w', edgecolor='k')\n",
    "\n",
    "x = result2.sort_values(by=['Percentage']).reset_index()['UsageFlag'].values.flatten()\n",
    "data = result2.sort_values(by=['Percentage'])[['BlobHash']].values.flatten() \n",
    "\n",
    "x_pos = [i for i, _ in enumerate(x)]\n",
    "\n",
    "plt.barh(x_pos, data,log=True, color='black')\n",
    "plt.ylabel(\"Blob Usage category\")\n",
    "plt.xlabel(\"Blob Count (Log Scale)\")\n",
    "plt.title(\"Blob Count for each usage category ( Log Scale )\")\n",
    "\n",
    "plt.yticks(x_pos, x)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# logic to get UsageFlagMax\n",
    "# To calculate the maximum usage\n",
    "\n",
    "def checkUsageMax(row):\n",
    "    UsageFlagL = str(row.UsageFlag).split(\",\")        \n",
    "    UsageFlagMax = max(UsageFlagL)\n",
    "    return UsageFlagMax\n",
    "\n",
    "\n",
    "    \n",
    "def iterrows_impl(df):\n",
    "    return pd.Series(\n",
    "        checkUsageMax(row)     \n",
    "        for row in df.itertuples()\n",
    "    )\n",
    "  \n",
    "\n",
    "finalResult['UsageFlagMax'] = pd.Series(iterrows_impl(finalResult))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(set(finalResult['UsageFlagMax']))\n",
    "finalResult.shape\n",
    "\n",
    "result3 = finalResult[['BlobHash','UsageFlagMax']].groupby(['UsageFlagMax']).agg(['count'])\n",
    "result3['Percentage'] = result3.apply(lambda x: 100 * x / float(x.sum()))\n",
    "result3\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from matplotlib.pyplot import figure\n",
    "figure(num=None, figsize=(8, 6), dpi=80, facecolor='w', edgecolor='k')\n",
    "\n",
    "x = result3.sort_values(by=['Percentage']).reset_index()['UsageFlagMax'].values.flatten()\n",
    "data = result3.sort_values(by=['Percentage'])[['Percentage']].values.flatten() \n",
    "\n",
    "x_pos = [i for i, _ in enumerate(x)]\n",
    "\n",
    "plt.barh(x_pos, data, color='black')\n",
    "plt.ylabel(\"Blob Usage Category Max\")\n",
    "plt.xlabel(\"Percentage\")\n",
    "plt.title(\"Percentage of usage categories Max\")\n",
    "\n",
    "plt.yticks(x_pos, x)\n",
    "\n",
    "for i, v in enumerate(data):\n",
    "    plt.text(v + 1, i -0.1, str(round(v,2)) + '%', color='Black')\n",
    "    \n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from matplotlib.pyplot import figure\n",
    "figure(num=None, figsize=(8, 6), dpi=80, facecolor='w', edgecolor='k')\n",
    "\n",
    "x = result3.sort_values(by=['Percentage']).reset_index()['UsageFlagMax'].values.flatten()\n",
    "data = result3.sort_values(by=['Percentage'])[['BlobHash']].values.flatten() \n",
    "\n",
    "x_pos = [i for i, _ in enumerate(x)]\n",
    "\n",
    "plt.barh(x_pos, data, color='black')\n",
    "plt.ylabel(\"Blob Usage Category Max\")\n",
    "plt.xlabel(\"Blob Count\")\n",
    "plt.title(\"Blob Count for each usage categories Max\")\n",
    "\n",
    "plt.yticks(x_pos, x)\n",
    "\n",
    "for i, v in enumerate(data):\n",
    "    plt.text(v + 1, i -0.1, str(round(v,2)) , color='Black')\n",
    "    \n",
    "\n",
    "    \n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalResult.to_csv('finalResult.csv',sep=';' ,index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Comb5.to_csv('Comp5.csv',sep=';' ,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Transformation for further analysis\n",
    "\n",
    "### Calculate Pivot for Blob Usage per project on week basis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Comb6 = Comb5.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter any timestamp fields\n",
    "\n",
    "mask = Comb6['CTimeStamp'].str.len() == 19\n",
    "Comb6 = Comb6.loc[mask].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply a flag for commits less than 2 years from hackathon end date\n",
    "\n",
    "from dateutil.relativedelta import relativedelta\n",
    "\n",
    "Comb6['hackathonEndDate'] =  pd.to_datetime(Comb6['hackathonEndDate'], format='%Y-%m-%d %H:%M:%S')\n",
    "Comb6['CTimeStamp'] =  pd.to_datetime(Comb6['CTimeStamp'], format='%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "def check2YearsFlag(row):\n",
    "    CTimeStamp = row.CTimeStamp\n",
    "    hackathonEndDate = row.hackathonEndDate\n",
    "    hEndDateWith2Years = row.hackathonEndDate + relativedelta(months=+24)\n",
    "    if(CTimeStamp < hEndDateWith2Years):\n",
    "        return '1'\n",
    "    else:\n",
    "        return '0'\n",
    "\n",
    "    \n",
    "def iterrows_impl(df):\n",
    "    return pd.Series(\n",
    "        check2YearsFlag(row)     \n",
    "        for row in df.itertuples()\n",
    "    )\n",
    "  \n",
    "\n",
    "Comb6['2YearsFlag'] = pd.Series(iterrows_impl(Comb6))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply the two years filter\n",
    "\n",
    "Comb7 = Comb6[Comb6['2YearsFlag']=='1'].reset_index().copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update column data type\n",
    "\n",
    "Comb7['hackathonEndDate'] =  pd.to_datetime(Comb7['hackathonEndDate'], format='%Y-%m-%d %H:%M:%S')\n",
    "Comb7['CTimeStamp'] =  pd.to_datetime(Comb7['CTimeStamp'], format='%Y-%m-%d %H:%M:%S')\n",
    "Comb7['hackathonStartDate'] =  pd.to_datetime(Comb7['hackathonStartDate'], format='%Y-%m-%d %H:%M:%S')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate Week Numbers\n",
    "\n",
    "def WeekCalc(row):\n",
    "    CTimeStampDD = (row.CTimeStamp - timedelta(days=row.CTimeStamp.weekday()))\n",
    "    hackathonEndDateDD = (row.hackathonEndDate - timedelta(days=row.hackathonEndDate.weekday()))\n",
    "    weekDifference = (CTimeStampDD - hackathonEndDateDD).days / 7\n",
    "    if(weekDifference < 0):\n",
    "        return 0\n",
    "    else:\n",
    "        return int(round(weekDifference, 0))\n",
    "    \n",
    "    \n",
    "def iterrows_impl(df):\n",
    "    return pd.Series(\n",
    "        WeekCalc(row)     \n",
    "        for row in df.itertuples()\n",
    "    )\n",
    "  \n",
    "\n",
    "Comb7['WeekNumber'] = pd.Series(iterrows_impl(Comb7))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete unwanted columns\n",
    "\n",
    "del Comb7['ProjectID']\n",
    "del Comb7['MainProjectL']\n",
    "del Comb7['2YearsFlag']\n",
    "del Comb7['level_0']\n",
    "del Comb7['index']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grouping to correct level with concat\n",
    "dd = Comb7[['BlobHash','WeekNumber','UsageFlag']].groupby(['BlobHash','WeekNumber']).agg({'UsageFlag':lambda x : ','.join(set(x))}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the maximum usage\n",
    "\n",
    "def checkUsageMax(row):\n",
    "    UsageFlagL = str(row.UsageFlag).split(\",\")        \n",
    "    UsageFlagMax = max(UsageFlagL)\n",
    "    return UsageFlagMax\n",
    "\n",
    "\n",
    "    \n",
    "def iterrows_impl(df):\n",
    "    return pd.Series(\n",
    "        checkUsageMax(row)     \n",
    "        for row in df.itertuples()\n",
    "    )\n",
    "  \n",
    "\n",
    "dd['UsageFlagMax'] = pd.Series(iterrows_impl(dd))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "WeekData = dd[['WeekNumber','UsageFlagMax','BlobHash']].drop_duplicates().groupby(['WeekNumber','UsageFlagMax']).count().reset_index()\n",
    "WeekData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build pivot table\n",
    "\n",
    "pivotWeekData = pd.pivot_table(WeekData,index=['WeekNumber'],values=['BlobHash'],columns=['UsageFlagMax'],aggfunc=[np.sum],fill_value=0).reset_index()\n",
    "pivotWeekData.columns = ['WeekNumber','1','3','4','5']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivotWeekData['TotalUsed'] = pivotWeekData['1'] + pivotWeekData['3'] + pivotWeekData['4'] + pivotWeekData['5'] \n",
    "pivotWeekData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivotWeekData['TotalUnused'] = 581579 - pivotWeekData['TotalUsed']  ## 581579: Total unique blobs\n",
    "pivotWeekData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivotWeekData.to_csv('pivotWeekData.csv', sep=';', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate RQ3 a and b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add flag for any usage instance\n",
    "\n",
    "ProjectsUsedinOtherProjectsDF = Comb5[['ProjectID_x','BlobHash','UsageFlag']].drop_duplicates().reset_index()\n",
    "del ProjectsUsedinOtherProjectsDF['index']\n",
    "\n",
    "ProjectsUsedinOtherProjectsDF.loc[ProjectsUsedinOtherProjectsDF['UsageFlag'] != '1', 'ContinuationFlag'] = 1 \n",
    "ProjectsUsedinOtherProjectsDF.loc[ProjectsUsedinOtherProjectsDF['UsageFlag'] == '1', 'ContinuationFlag'] = 0 \n",
    "\n",
    "ProjectsUsedinOtherProjectsDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grouping and calculating the percentage of usage per project\n",
    "\n",
    "PratioDF = ProjectsUsedinOtherProjectsDF.groupby(['ProjectID_x']).agg({'ContinuationFlag':'sum', 'BlobHash': 'count'}).reset_index()\n",
    "PratioDF.columns = ['ProjectID_x','ContinuationBlobCount','TotalBlobCount']\n",
    "PratioDF['Ratio'] = (PratioDF['ContinuationBlobCount'] / PratioDF['TotalBlobCount'])\n",
    "PratioDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "PrInfoAll = PrInfo2\n",
    "PrInfoAll"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add project Info from WoC\n",
    "PratioVariablesDF = pd.merge( PratioDF, PrInfoAll, how='left', left_on=['ProjectID_x'], right_on=['projectID'])\n",
    "PratioVariablesDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PrInfoDEVPOST = PrInfoDEVPOST[['id','hackathon-id','number-of-technologies','technologies','number-of-participants','likes','comments','hackathon-prize-money','hackathon-number-of-prizes','hackathon-is-colocated','winner']].drop_duplicates()\n",
    "PrInfoDEVPOST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del wocUrl['Unnamed: 4']\n",
    "del wocUrl['Unnamed: 5']\n",
    "wocUrl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add project Info from DEVPOST\n",
    "\n",
    "PrInfoAllDEVPOST = pd.merge(PrInfoDEVPOST, wocUrl, how='inner', left_on=['id','hackathon-id'], right_on=['devpost_id','hackathon_id'])[['woc_url','number-of-technologies','technologies','number-of-participants','likes','comments','hackathon-prize-money','hackathon-number-of-prizes','hackathon-is-colocated','winner']].drop_duplicates()\n",
    "PrInfoAllDEVPOST.columns = ['projectID', 'number-of-technologies','technologies','number-of-participants','likes','comments','hackathon-prize-money','hackathon-number-of-prizes','hackathon-is-colocated','winner']\n",
    "PrInfoAllDEVPOST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalProjectInfo = pd.merge(PrInfoAllDEVPOST, PratioVariablesDF, how='inner', left_on=['projectID'], right_on=['ProjectID_x']).drop_duplicates()\n",
    "finalProjectInfo = finalProjectInfo[['projectID_x', 'number-of-technologies', 'technologies',\n",
    "       'number-of-participants', 'likes', 'comments', 'hackathon-prize-money',\n",
    "       'hackathon-number-of-prizes', 'hackathon-is-colocated', 'winner',\n",
    "       'ProjectID_x', 'projectID_y',\n",
    "       'numStars', 'NumAuthors', 'NumBlobs', 'rootFork', 'communitySize',\n",
    "       'NumFiles', 'NumCommits', 'EarlistCommitDate', 'LatestCommitDate',\n",
    "       'FileInfo','TotalBlobCount','ContinuationBlobCount','Ratio']]\n",
    "finalProjectInfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalProjectInfo.columns = ['projectID_x', 'number-of-technologies', 'technologies',\n",
    "       'number-of-participants', 'likes', 'comments', 'hackathon-prize-money',\n",
    "       'hackathon-number-of-prizes', 'hackathon-is-colocated', 'winner',\n",
    "       'ProjectID_x', 'projectID_y', 'numStars', 'NumAuthors', 'NumBlobs',\n",
    "       'rootFork', 'communitySize', 'NumFiles', 'NumCommits',\n",
    "       'EarlistCommitDate', 'LatestCommitDate', 'FileInfo', 'TotalBlobs',\n",
    "       'sumContinuedBlobs', 'Ratio']\n",
    "\n",
    "del finalProjectInfo['ProjectID_x']\n",
    "del finalProjectInfo['projectID_y']\n",
    "finalProjectInfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalProjectInfo.to_csv('RQ3a.csv', sep=';',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add flag for any usage instance in large open source project\n",
    "\n",
    "ProjectsUsedinOtherProjectsDF = Comb5[['ProjectID_x','BlobHash','UsageFlag']].drop_duplicates().reset_index()\n",
    "del ProjectsUsedinOtherProjectsDF['index']\n",
    "\n",
    "ProjectsUsedinOtherProjectsDF.loc[ProjectsUsedinOtherProjectsDF['UsageFlag'] == '5', 'ContinuationFlag'] = 1 \n",
    "ProjectsUsedinOtherProjectsDF.loc[ProjectsUsedinOtherProjectsDF['UsageFlag'] != '5', 'ContinuationFlag'] = 0 \n",
    "\n",
    "ProjectsUsedinOtherProjectsDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grouping and calculating the percentage of usage per project\n",
    "\n",
    "PratioDF = ProjectsUsedinOtherProjectsDF.groupby(['ProjectID_x']).agg({'ContinuationFlag':'sum', 'BlobHash': 'count'}).reset_index()\n",
    "# ProjectsUsedinOtherProjectsDF['BlobHash'] = pd.to_numeric(ProjectsUsedinOtherProjectsDF['BlobHash'])\n",
    "PratioDF.columns = ['ProjectID_x','ContinuationBlobCount','TotalBlobCount']\n",
    "PratioDF['Ratio'] = (PratioDF['ContinuationBlobCount'] / PratioDF['TotalBlobCount'])\n",
    "PratioDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add project Info from WoC\n",
    "\n",
    "PratioVariablesDF = pd.merge( PratioDF, PrInfoAll, how='left', left_on=['ProjectID_x'], right_on=['projectID'])\n",
    "PratioVariablesDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "PrInfoDEVPOST = PrInfoDEVPOST[['id','hackathon-id','number-of-technologies','technologies','number-of-participants','likes','comments','hackathon-prize-money','hackathon-number-of-prizes','hackathon-is-colocated','winner']].drop_duplicates()\n",
    "PrInfoDEVPOST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add project Info from DEVPOST\n",
    "\n",
    "PrInfoAllDEVPOST = pd.merge(PrInfoDEVPOST, wocUrl, how='inner', left_on=['id','hackathon-id'], right_on=['devpost_id','hackathon_id'])[['woc_url','number-of-technologies','technologies','number-of-participants','likes','comments','hackathon-prize-money','hackathon-number-of-prizes','hackathon-is-colocated','winner']].drop_duplicates()\n",
    "PrInfoAllDEVPOST.columns = ['projectID', 'number-of-technologies','technologies','number-of-participants','likes','comments','hackathon-prize-money','hackathon-number-of-prizes','hackathon-is-colocated','winner']\n",
    "PrInfoAllDEVPOST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalProjectInfo = pd.merge(PrInfoAllDEVPOST, PratioVariablesDF, how='inner', left_on=['projectID'], right_on=['ProjectID_x']).drop_duplicates()\n",
    "finalProjectInfo = finalProjectInfo[['projectID_x', 'number-of-technologies', 'technologies',\n",
    "       'number-of-participants', 'likes', 'comments', 'hackathon-prize-money',\n",
    "       'hackathon-number-of-prizes', 'hackathon-is-colocated', 'winner',\n",
    "       'ProjectID_x', 'projectID_y',\n",
    "       'numStars', 'NumAuthors', 'NumBlobs', 'rootFork', 'communitySize',\n",
    "       'NumFiles', 'NumCommits', 'EarlistCommitDate', 'LatestCommitDate',\n",
    "       'FileInfo','TotalBlobCount','ContinuationBlobCount','Ratio']]\n",
    "finalProjectInfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalProjectInfo.columns = ['projectID_x', 'number-of-technologies', 'technologies',\n",
    "       'number-of-participants', 'likes', 'comments', 'hackathon-prize-money',\n",
    "       'hackathon-number-of-prizes', 'hackathon-is-colocated', 'winner',\n",
    "       'ProjectID_x', 'projectID_y', 'numStars', 'NumAuthors', 'NumBlobs',\n",
    "       'rootFork', 'communitySize', 'NumFiles', 'NumCommits',\n",
    "       'EarlistCommitDate', 'LatestCommitDate', 'FileInfo', 'TotalBlobs',\n",
    "       'sumContinuedBlobs', 'Ratio']\n",
    "\n",
    "del finalProjectInfo['ProjectID_x']\n",
    "del finalProjectInfo['projectID_y']\n",
    "finalProjectInfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "finalProjectInfo.to_csv('RQ3b.csv', sep=';',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate what are the fractions of different blob types for each project"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Hack_b2type = pd.read_csv('Hack_b2type.csv', sep=',')\n",
    "Hack_b2type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add datasets for blob to hackathon project info\n",
    "\n",
    "hbacDF = pd.read_csv('../CodeGeneration/hbac-20201108.csv',sep=';', encoding = \"ISO-8859-1\")\n",
    "hcbDF = pd.read_csv('../CodeGeneration/hcb-20201029a.csv',sep=';')\n",
    "hpcDF = pd.read_csv('../CodeGeneration/hpc-20201028.csv',sep=';')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Joining\n",
    "BC = pd.merge(hbacDF, hcbDF, how='inner', left_on=['BlobHash'], right_on=['BlobHash'])[['BlobHash','CommitHash','FirstTimestamp','FirstAuthorID','FirstCommitHash']]\n",
    "BCP = pd.merge(BC, hpcDF, how='inner', left_on=['CommitHash'], right_on=['CommitHash'])[['BlobHash','ProjectID','FirstTimestamp','FirstAuthorID','FirstCommitHash']].drop_duplicates()\n",
    "BCP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join with blob file type\n",
    "CodeInfoDF = pd.merge(BCP, Hack_b2type, how='inner', left_on=['BlobHash'], right_on=['Blob']).drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count of blobs per project\n",
    "\n",
    "BlobCountProject = CodeInfoDF.groupby(['ProjectID']).BlobHash.agg('count').to_frame('TotalBlobsPerProject').reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get count of blobs per project, type\n",
    "\n",
    "CodeInfoGroup = CodeInfoDF[['ProjectID','Type','BlobHash']].groupby(['ProjectID','Type']).agg({'BlobHash': 'count'}).reset_index()\n",
    "CodeInfoGroup.columns = ['ProjectID','Type','TotalBlobsPerType']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Join dataframes\n",
    "\n",
    "CodeInfoGroup2 = pd.merge(CodeInfoGroup,BlobCountProject, how='inner', left_on=['ProjectID'], right_on=['ProjectID'])\n",
    "CodeInfoGroup2['BlobTypeRatio'] = CodeInfoGroup2['TotalBlobsPerType'] / CodeInfoGroup2['TotalBlobsPerProject']\n",
    "CodeInfoGroup2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build Pivot\n",
    "\n",
    "pivotCodeInfoGroup = pd.pivot_table(CodeInfoGroup2,index=['ProjectID'],values=['BlobTypeRatio'],columns=['Type'],aggfunc=[np.sum],fill_value=0).reset_index()\n",
    "pivotCodeInfoGroup.columns = ['ProjectID','pctOther','pctData','pctMarkup','pctCode','pctProse']\n",
    "pivotCodeInfoGroup.to_csv('pivotCodeInfoGroup.csv', sep=';', index=False)\n",
    "pivotCodeInfoGroup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Regenerate the graphs as per new requirements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove instances where code was not reused\n",
    "\n",
    "Comb8 = Comb5[Comb5['UsageFlag'] != '1'].copy()\n",
    "Comb8[\"UsageFlag\"].replace({\"3\": \"1\", \"4\": \"2\",\"5\": \"3\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concat and group on correct level\n",
    "\n",
    "result = Comb8[['devpost_id','ProjectID_x','hackathonStartDate','hackathonEndDate','BlobHash','TimingFlag','AuthorFlag','UsageFlag']].groupby(['devpost_id','ProjectID_x','hackathonStartDate','hackathonEndDate','BlobHash','TimingFlag','AuthorFlag']).agg({'UsageFlag':lambda x : ','.join(set(x))}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "rr = result[['BlobHash','UsageFlag']].groupby(['UsageFlag']).agg(['count'])\n",
    "rr['Percentage'] = rr.apply(lambda x: 100 * x / float(x.sum()))\n",
    "rr\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from matplotlib.pyplot import figure\n",
    "from matplotlib import pyplot as plt \n",
    "from matplotlib.gridspec import GridSpec\n",
    "from pylab import figure, text, scatter, show\n",
    "\n",
    "figure(num=None, figsize=(8, 6), dpi=80, facecolor='w', edgecolor='k')\n",
    "\n",
    "x = rr.sort_values(by=['Percentage']).reset_index()['UsageFlag'].values.flatten()\n",
    "data = rr.sort_values(by=['Percentage'])[['Percentage']].values.flatten() \n",
    "\n",
    "x_pos = [i for i, _ in enumerate(x)]\n",
    "\n",
    "plt.barh(x_pos, data, color='black')\n",
    "plt.ylabel(\"Blob Usage Category\")\n",
    "plt.xlabel(\"Percentage\")\n",
    "plt.title(\"Percentage of usage categories\")\n",
    "\n",
    "text(51, 0.4, '1: Small', fontsize=12)\n",
    "text(51, 0, '2: Medium', fontsize=12)\n",
    "text(51,-0.4, '3: Large', fontsize=12)\n",
    "\n",
    "\n",
    "plt.yticks(x_pos, x)\n",
    "\n",
    "for i, v in enumerate(data):\n",
    "    plt.text(v +0.5, i -0.1, str(round(v,2)) + '%', color='Black')\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# logic to get UsageFlagMax\n",
    "# For maximum usage\n",
    "\n",
    "def checkUsageMax(row):\n",
    "    UsageFlagL = str(row.UsageFlag).split(\",\")        \n",
    "    UsageFlagMax = max(UsageFlagL)\n",
    "    return UsageFlagMax\n",
    "\n",
    "\n",
    "    \n",
    "def iterrows_impl(df):\n",
    "    return pd.Series(\n",
    "        checkUsageMax(row)     \n",
    "        for row in df.itertuples()\n",
    "    )\n",
    "  \n",
    "\n",
    "result['UsageFlagMax'] = pd.Series(iterrows_impl(result))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "result4 = result[['BlobHash','UsageFlagMax']].groupby(['UsageFlagMax']).agg(['count'])\n",
    "result4['Percentage'] = result4.apply(lambda x: 100 * x / float(x.sum()))\n",
    "result4 = result4.reset_index()\n",
    "result4.columns = ['UsageFlagMax','BlobCount', 'BlobPercentage']\n",
    "# result4[\"UsageFlagMax\"].replace({\"1\": \"[1] Small\", \"2\": \"[2] Medium\",\"3\": \"[3] Large\"}, inplace=True)\n",
    "result4.reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from matplotlib.pyplot import figure\n",
    "from pylab import figure, text, scatter, show\n",
    "import matplotlib.patches as mpatches\n",
    "figure(num=None, figsize=(8, 6), dpi=80, facecolor='w', edgecolor='k')\n",
    "\n",
    "x = result4.sort_values(by=['BlobPercentage']).reset_index()['UsageFlagMax'].values.flatten()\n",
    "data = result4.sort_values(by=['BlobPercentage'])[['BlobPercentage']].values.flatten() \n",
    "\n",
    "x_pos = [i for i, _ in enumerate(x)]\n",
    "\n",
    "plt.barh(x_pos, data, color='black')\n",
    "plt.ylabel(\"Blob Usage Category Max\")\n",
    "plt.xlabel(\"Percentage\")\n",
    "plt.title(\"Percentage of usage categories Max\")\n",
    "\n",
    "text(51, -0.2, '1: Small', fontsize=12)\n",
    "text(51, -0.3, '2: Medium', fontsize=12)\n",
    "text(51,-0.4, '3: Large', fontsize=12)\n",
    "\n",
    "\n",
    "plt.yticks(x_pos, x)\n",
    "\n",
    "for i, v in enumerate(data):\n",
    "    plt.text(v + 1, i -0.1, str(round(v,2)) + '%', color='Black')\n",
    "    \n",
    "plt.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}