data_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Import dependencies and open files\n",
    "only requirement is pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Specify the path to the Excel file\n",
    "excel_file_path = \"Pay It Forward Data Inquiry (Responses).xlsx\"\n",
    "\n",
    "# Read the Excel file into a pandas dataframe\n",
    "df = pd.read_excel(excel_file_path)\n",
    "\n",
    "# Now you can perform data analysis on the dataframe\n",
    "# print(df.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Logic for data cleaning"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "vscode": {
     "languageId": "raw"
    }
   },
   "source": [
    "LOGIC: \n",
    "#### Data cleaning\n",
    "• Add additional fields \n",
    "• Change data type within fields \n",
    "\n",
    "Create new columns with cloned data from the corresponding columns in sequential order.\n",
    "\n",
    "##### Standard field mappings: original field name\n",
    "Timestamp\t\n",
    "Are you familiar with the term \"food insecurity?\"\t\n",
    "***NON-FIRST YEARS: Approximately how much $ were you left with in your top up meal plan account at the end of first year?\t\n",
    "***NON-FIRST YEARS: If you have any money left over in your top up account, approximately how much do you think you have?\t\n",
    "***FIRST YEARS: Approximately how much $ are you left with in your top up meal plan account right now\t\n",
    "If given the option to donate the remaining $ to your food insecure peers at Western, how much would you donate?\t\n",
    "***FACULTY/STAFF: Approximately how much $ are you left with in your top up meal plan account right now \n",
    "\n",
    "##### Standard field mappings: destination field name\n",
    "Timestamp \n",
    "Food_Insecurity_Familiarity \n",
    "Upper_Year_Dollars_Remaining_At_End_Of_First_Year \n",
    "Upper_Year_Dollars_Remaining_Now \n",
    "First_Year_Dollar_Remaining_Now \n",
    "Donation_Percentage \n",
    "Faculty_Staff_Dollar_Remaining_Now \n",
    "\n",
    "Then, clean the new fields accordingly.\n",
    "\n",
    "##### Change data type:\n",
    "In fields Upper_Year_Dollars_Remaining_At_End_Of_First_Year and First_Year_Dollar_Remaining_Now, map:\n",
    "Finished it in residence --> 0\n",
    "<$50 --> 25\n",
    "$50 - $200 --> 125\n",
    "$200 - $1000 --> 600\n",
    ">$1000 (greater than $1000) --> 1250\n",
    "\n",
    "In field Upper_Year_Dollars_Remaining_Now, map:\n",
    "Less than $10 --> 5\n",
    "$10 - $50 --> 30\n",
    "$50 - $100 --> 75\n",
    "$100 - $1000 --> 550\n",
    ">$1000 (greater than $1000) --> 1250\n",
    "\n",
    "In field Faculty_Staff_Dollar_Remaining_Now, map:\n",
    "$0 --> 0\n",
    "<$10 --> 5\n",
    "$15 --> 15\n",
    ">$20 --> 25\n",
    "\n",
    "In field Donation_Percentage, map:\n",
    "$0 --> 0\n",
    "5% of remaining funds --> 0.05\n",
    "10% of remaining funds --> 0.1\n",
    "15% of remaining funds --> 0.15\n",
    "20% - 50% of remaining funds --> 0.35\n",
    "50% - 90% --> 0.7\n",
    "100% - Drain the account and donate all of it --> 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read the cleaned data from the CSV file\n",
    "df = pd.read_csv('cleaned_data.csv')\n",
    "\n",
    "# Calculate average percentage to be donated by first-year students\n",
    "average_percentage = df.loc[df['Donation_Percentage'].notnull(), 'Donation_Percentage'].mean()\n",
    "\n",
    "# Calculate average dollar amount to be donated by first-year students with money remaining\n",
    "df['Donation_Amount'] = df['First_Year_Dollar_Remaining_Now'] * df['Donation_Percentage']\n",
    "average_donation_amount = df.loc[df['Donation_Amount'].notnull(), 'Donation_Amount'].mean()\n",
    "\n",
    "# Calculate the number of first-year students expected to opt-in to Pay it Forward\n",
    "total_first_year_population = 7850\n",
    "percentage_populated = df['Donation_Amount'].count() / len(df)\n",
    "expected_opt_in_students = percentage_populated * total_first_year_population\n",
    "\n",
    "# Calculate the total collectible amount from all opted-in first-year students\n",
    "total_first_year_collectible_amount = average_donation_amount * expected_opt_in_students\n",
    "\n",
    "# Print the results\n",
    "print(f\"Percentage of first-years opting-in to Pay it Forward: {percentage_populated*100:.2f}%\")\n",
    "print(f\"Average percentage of final account balance to be donated by participating first-year students: {average_percentage*100:.2f}%\")\n",
    "print(f\"Dollar amount collectible from each opted-in first-year student with money remaining: ${average_donation_amount:.2f}\")\n",
    "print(f\"Number of Western University first-year students: {total_first_year_population}\")\n",
    "print(f\"Number of Western University first-year students expected to opt-in to Pay it Forward: {expected_opt_in_students:.0f}\")\n",
    "print(f\"Total collectible amount from all opted-in first-years: ${total_first_year_collectible_amount:.2f}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data cleaning logic implementation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Create duplicated columns with newly mapped names as shown above\n",
    "df['Food_Insecurity_Familiarity'] = df['Are you familiar with the term \"food insecurity?\"']\n",
    "df['Upper_Year_Dollars_Remaining_At_End_Of_First_Year'] = df['***NON-FIRST YEARS: Approximately how much $ were you left with in your top up meal plan account at the end of first year?']\n",
    "df['Upper_Year_Dollars_Remaining_Now'] = df['***NON-FIRST YEARS: If you have any money left over in your top up account, approximately how much do you think you have?']\n",
    "df['First_Year_Dollar_Remaining_Now'] = df['***FIRST YEARS: Approximately how much $ are you left with in your top up meal plan account right now']\n",
    "df['Donation_Percentage'] = df['If given the option to donate the remaining $ to your food insecure peers at Western, how much would you donate?']\n",
    "df['Faculty_Staff_Dollar_Remaining_Now'] = df['***FACULTY/STAFF: Approximately how much $ are you left with in your top up meal plan account right now']\n",
    "\n",
    "\n",
    "# Change data type in fields Upper_Year_Dollars_Remaining_At_End_Of_First_Year and First_Year_Dollar_Remaining_Now\n",
    "df['Upper_Year_Dollars_Remaining_At_End_Of_First_Year'] = df['Upper_Year_Dollars_Remaining_At_End_Of_First_Year'].map({\n",
    "    'Finished it in residence': 0,\n",
    "    '<$50': 25,\n",
    "    '$50 - $200': 125,\n",
    "    '$200 - $1000': 600,\n",
    "    '>$1000 (greater than $1000)': 1250\n",
    "})\n",
    "\n",
    "df['First_Year_Dollar_Remaining_Now'] = df['First_Year_Dollar_Remaining_Now'].map({\n",
    "    'Finished it in residence': 0,\n",
    "    '<$50': 25,\n",
    "    '$50 - $200': 125,\n",
    "    '$200 - $1000': 600,\n",
    "    '>$1000 (greater than $1000)': 1250\n",
    "})\n",
    "\n",
    "# Change data type in field Upper_Year_Dollars_Remaining_Now\n",
    "df['Upper_Year_Dollars_Remaining_Now'] = df['Upper_Year_Dollars_Remaining_Now'].map({\n",
    "    'Less than $10': 5,\n",
    "    '$10 - $50': 30,\n",
    "    '$50 - $100': 75,\n",
    "    '$100 - $1000': 550,\n",
    "    '>$1000 (greater than $1000)': 1250\n",
    "})\n",
    "\n",
    "# Change data type in field Faculty_Staff_Dollar_Remaining_Now\n",
    "df['Faculty_Staff_Dollar_Remaining_Now'] = df['Faculty_Staff_Dollar_Remaining_Now'].map({\n",
    "    '$0': 0,\n",
    "    '<$10': 5,\n",
    "    '$15': 15,\n",
    "    '>$20': 25\n",
    "})\n",
    "\n",
    "# Change data type in field Donation_Percentage\n",
    "df['Donation_Percentage'] = df['Donation_Percentage'].map({\n",
    "    '$0': 0,\n",
    "    '5% of remaining funds': 0.05,\n",
    "    '10% of remaining funds': 0.1,\n",
    "    '15% of remaining funds': 0.15,\n",
    "    '20% - 50% of remaining funds': 0.35,\n",
    "    '50% - 90%': 0.7,\n",
    "    '100% - Drain the account and donate all of it': 1\n",
    "})\n",
    "\n",
    "# print(df)\n",
    "\n",
    "# Save the dataframe to a CSV that can be overwritten; named 'cleaned_data.csv'\n",
    "df.to_csv('cleaned_data.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calculate key metrics\n",
    "• Average percentage to be donated by first-year students <br>\n",
    "• Average dollar amount to be donated by first-year students with money remaning | calculated based off $ remaining and % to donate <br>\n",
    "• Average dollar amount collectible from all first-year students, including those with no money remaining\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of first-years opting-in to Pay it Forward: \n",
      "31.42% \n",
      "\n",
      "Average percentage of final account balance to be donated by participating first-year students:\n",
      "41.02%\n",
      "\n",
      "Dollar amount collectible from each opted-in first-year student with money remaining: \n",
      "$64.75\n",
      "\n",
      "Number of Western University first-year students: 7,850\n",
      "\n",
      "Number of Western University first-year students expected to opt-in to Pay it Forward: 2,467\n",
      "\n",
      "Total collectible amount from all opted-in first-years = $ 159,718.75\n",
      "\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Calculate average percentage to be donated by first-year students; this should exclude empty rows\n",
    "average_percentage = df['Donation_Percentage'].mean()\n",
    "\n",
    "# Calculate average dollar amount to be donated by first-year students with money remaining\n",
    "df['Donation_Amount'] = df['First_Year_Dollar_Remaining_Now'] * df['Donation_Percentage']\n",
    "average_donation_amount = df['Donation_Amount'].mean()\n",
    "\n",
    "# Count rows where average_donation_amount is not null, then divide by the total number of rows\n",
    "populated_rows = df['Donation_Amount'].count()\n",
    "total_rows = len(df)\n",
    "percentage_populated = populated_rows / total_rows\n",
    "\n",
    "# Show fraction (populated/total—print these actual numbers) and percentage of rows with populated donation amounts\n",
    "\n",
    "# print(\"Total rows:\", total_rows)\n",
    "print(f\"Percentage of first-years opting-in to Pay it Forward: \\n{percentage_populated*100:,.2f}% \")\n",
    "\n",
    "# Print the results\n",
    "print(f\"\\nAverage percentage of final account balance to be donated by participating first-year students:\\n{average_percentage*100:,.2f}%\")\n",
    "\n",
    "print(f\"\\nDollar amount collectible from each opted-in first-year student with money remaining: \\n${average_donation_amount:,.2f}\\n\")\n",
    "\n",
    "# https://www.ipb.uwo.ca/documents/2023_full_time_first_year_enrolment_by_applicant_group_program.pdf\n",
    "total_first_year_population = 7850\n",
    "\n",
    "print(f\"Number of Western University first-year students: {total_first_year_population:,.0f}\\n\")\n",
    "\n",
    "print(f\"Number of Western University first-year students expected to opt-in to Pay it Forward: {percentage_populated * total_first_year_population:,.0f}\\n\")\n",
    "\n",
    "total_first_year_collectible_amount = average_donation_amount * total_first_year_population * percentage_populated\n",
    "\n",
    "print(f\"Total collectible amount from all opted-in first-years = $ {total_first_year_collectible_amount:,.2f}\\n\")\n",
    "\n",
    "# Save the dataframe to a CSV that can be overwritten; named 'cleaned_data.csv'\n",
    "df.to_csv('cleaned_data.csv', index=False)"
   ]
  }
 ],
 "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}