20200917  ratio-applicant-applications.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ratio applicant-applications in multiple subsidy rounds\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This code gives ratio of applications and applicant for two subsidie rounds. It answers: How often do applications come from the same applicant? Is this a new topic or a more or less resubmission of a previously rejected topic? Based on the title or summary give an indication."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Analysis: A pivot table from data warehouse was used to collect the data about two subsidy rounds. The applicants are filtered on the basis of their application submission; once or several times. It was also checked whether the submitted applications have the same titles. The analysis is made of both rounds together and then each round separately. The analysis focuses on three points of attention: applicants, applications, and rounds. The numbers are calculated as a summary of the applications with the same titles that the unique applicants have submitted. Subsequently, an analysis was made in terms of applicant-application ratio, i.e. applicants who submitted the same application (i.e. with the same title) two or more times in the multiple rounds or in a single round."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing various modules required for analysis\n",
    "import xlsxwriter \n",
    "import scipy.sparse as sp\n",
    "import numpy as np\n",
    "import os\n",
    "os.getcwd()\n",
    "import tkinter as tk\n",
    "from tkinter import filedialog\n",
    "import pandas as pd\n",
    "import matplotlib as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importing excels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing round 1 excel\n",
    "#Importing round 2 excel\n",
    "#Importing combined rounds excel\n",
    "#you need to run the script per file\n",
    "root= tk.Tk()\n",
    "\n",
    "canvas1 = tk.Canvas(root, width = 300, height = 300, bg = 'lightsteelblue')\n",
    "canvas1.pack()\n",
    "\n",
    "def getExcel ():\n",
    "    global df\n",
    "    \n",
    "    import_file_path = filedialog.askopenfilename()\n",
    "    df = pd.read_excel (import_file_path)\n",
    "    #print (df)\n",
    "    \n",
    "browseButton_Excel = tk.Button(text='Import Excel File', command=getExcel, bg='green', fg='white', font=('helvetica', 12, 'bold'))\n",
    "canvas1.create_window(150, 150, window=browseButton_Excel)\n",
    "\n",
    "root.mainloop()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "cleaning the header of the pivot table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "header=df.iloc[4]\n",
    "df1=df.rename(columns = header)\n",
    "df1 = df1.iloc[5:]\n",
    "df1 = df1.reset_index(drop=True)\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "omit withdrawn applications by selecting 0 from column \"Withdrawn\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=df1[df1['# Withdrawn']==0]\n",
    "df1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In summary we can give answer to following questions:\n",
    "\n",
    "How many applications are submitted in both rounds: xxxx,\n",
    "\n",
    "How many applications are submitted in round 1: xxx,\n",
    "\n",
    "How many applications are submitted in round 2: xxx,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#How many unique applicants have submitted proposals per round\n",
    "df1.Applicant.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Grouping, sorting of the applicants based on their number of application"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=df1.groupby('Applicant').count().sort_values(by = '# Application') \n",
    "df3=df2.loc[(df2['# Application']>1)]\n",
    "df4 = pd.DataFrame(df3.to_records())#flattening the table\n",
    "Applicants= df4.Applicanttolist()\n",
    "df5=df1.loc[df1['Applicant'].isin(Applicants)]\n",
    "df6=df5.sort_values(by = 'Applicant') \n",
    "df6.head()\n",
    "df6.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking unique applicants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df6['Applicant'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Grouping by unique applicants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df6_gb=df6.groupby([\"Applicant\", \"Round\", \"Title\", \"Discipline\"])[\"# Application\"].count()\n",
    "df6_gb.head(5)\n",
    "df6_gb['Applicant'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Making a bar plot of application vs applicant"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "ax = sns.countplot(x=\"# Application\",data=df2, color=\"#008b9f\")\n",
    "plt.xlabel(\"Applications per applicant\", size=14)\n",
    "plt.ylabel(\"Applicants\", size=14)\n",
    "plt.title(\"Application-Applicant ratio\", size=16)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In summary we can give answer to following questions:\n",
    "\n",
    "How many unique applicants have submitted multiple applications (more than one) in both rounds: xxx,\n",
    "\n",
    "How many unique applicants have submitted multiple applications (more than one) in round 1: xxx,\n",
    "\n",
    "How many unique applicants have submitted multiple applications (more than one) in round 2: xx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#How many unique applicants have submitted multiple applications (more than one)\n",
    "df6.Applicant.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "script for applicant, their application count and whether it is a resubmission"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Applicants_R=df6['Applicant'].unique()\n",
    "#dataframe to append the name of the people and no of times they resubimitted the same application title\n",
    "df8=pd.DataFrame(columns = ['Applicant', 'application_count','Resubmission',])\n",
    "# for each applicant who has resubmitted\n",
    "application_count=0 #no of applications resubmitted\n",
    "for Applicant in Applicants_R:\n",
    "    df7=df6[df6['Applicant']==Applicant]\n",
    "    #dataframe for each applicant\n",
    "    k=4 #column where titels are\n",
    "    l=len(df7.index)-1 #the no of rows to run for each applicant's dataframe\n",
    "    for i in range (0,l): #for each row of an applicant's dataframe\n",
    "        application_count=application_count+1 #counting applications\n",
    "        if df7.iloc[i, k]==df7.iloc[i+1, k]: #if title of ith row is same as title of i+1th row\n",
    "            new_row = {'Applicant':df7.iloc[i,1], 'application_count':len(df7.index), 'Resubmission': 'Yes'}\n",
    "        elif df7.iloc[i, k]!=df7.iloc[i+1, k]:\n",
    "            new_row = {'Applicant':df7.iloc[i,1], 'application_count':len(df7.index), 'Resubmission': 'No'}\n",
    "    df8 = df8.append(new_row, ignore_index=True)\n",
    "#print(df8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "checking dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df7.head()\n",
    "df7.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8.head()\n",
    "df8.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In summary we can give answer to following questions:\n",
    "\n",
    "How many people have resubmitted the same application (with exactly same title) in both rounds : xx,\n",
    "\n",
    "How many people have resubmitted the same application (with exactly same title) in round 1 : xx,\n",
    "\n",
    "How many people have resubmitted the same application (with exactly same title) in round 2 : xx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#How many people have resubmitted the same application (with exactly same title)\n",
    "df8[df8['Resubmission']=='Yes'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In summary we can give answer to following questions:\n",
    "\n",
    "How many applications are resubmitted with the same titel in both rounds: xxx,\n",
    "\n",
    "How many applications are resubmitted with the same titel in round 1: xxx,\n",
    "\n",
    "How many applications are resubmitted with the same titel in round 2: xx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#How many applications are resubmitted with the same titel\n",
    "df9=df8[df8['Resubmission']=='Yes']\n",
    "df9['application_count'].sum()\n",
    "df9.head()\n",
    "df10=df9.groupby('Applicant').count().sort_values(by = 'application_count') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Making a bar plot of application vs applicant for resubmitted applications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "ax = sns.countplot(x=\"application_count\",data=df9, color=\"#008b9f\")\n",
    "plt.xlabel(\"Applications per Applicant\", size=14)\n",
    "plt.ylabel(\"Applicants\", size=14)\n",
    "plt.title(\"Applications-Applicant ratio for resubmitted applications\", size=16)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Number of applicants who have submitted twice or thrice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#for example number of applicants who have submitted the same application thrice\n",
    "df9[df9.application_count == 3].shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In summary, with the application_count you can get following values:\n",
    "\n",
    "for both rounds: \n",
    "Number of applicants who have submitted the same application twice: xx\n",
    "Number of applicants who have submitted the same application thrice:x\n",
    "\n",
    "\n",
    "for round 1: \n",
    "Number of applicants who have submitted the same application twice: xx\n",
    "Number of applicants who have submitted the same application thrice:x\n",
    "\n",
    "for round 2 :\n",
    "Number of applicants who have submitted the same application twice: xx\n",
    "Number of applicants who have submitted the same application thrice:x"
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}