Supplemental_Exploring_Team_Size.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center> <img style=\"float: center;\" src=\"images/CI_horizontal.png\" width=\"450\">\n",
    "<center>\n",
    "    <span style=\"font-size: 1.5em;\">\n",
    "        <a href='https://www.coleridgeinitiative.org'>Website</a>\n",
    "    </span> \n",
    "    <br><center> Brian Kim, Maryah Garner, Ekaterina Levitskaya, Rukhshan Arif Mian </center>\n",
    "    <br>\n",
    "    <a href=\"https://doi.org/10.5281/zenodo.6463912\"><img src=\"https://zenodo.org/badge/DOI/10.5281/zenodo.6463912.svg\" alt=\"DOI\"></a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <center>Supplemental Notebook: Exploring Team Size</center>\n",
    "\n",
    "In the UMETRICS semester-level data, some of the decisions about measurement and aggregation have already been made for you. However, you might be interested in doing this process yourself, using different measures as you think about the assumptions that you make and the phenomena that you want to study. \n",
    "\n",
    "This notebook covers some of the basics of how that might be done through the individual grant and person level UMETRICS data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Switching off warnings\n",
    "options(warn = -1)\n",
    "\n",
    "# Database interaction imports\n",
    "suppressMessages(library(odbc))\n",
    "\n",
    "# for data manipulation/visualization\n",
    "suppressMessages(library(tidyverse))\n",
    "\n",
    "# scaling data, calculating percentages, overriding default graphing\n",
    "suppressMessages(library(scales))\n",
    "\n",
    "# add weights to data\n",
    "suppressMessages(library(survey))\n",
    "\n",
    "#Switching on warnings\n",
    "options(warn = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to the database\n",
    "con <- DBI::dbConnect(odbc::odbc(),\n",
    "                     Driver = \"SQL Server\",\n",
    "                     Server = \"msssql01.c7bdq4o2yhxo.us-gov-west-1.rds.amazonaws.com\",\n",
    "                     Trusted_Connection = \"True\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's start with a very basic measure of team size. We can count how many people were on the grant in that time period by counting the distinct employee numbers associated with each unique award number.\n",
    "\n",
    "Note that we are only going to be looking at the Fall semester of 2014 as an example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry <- \"\n",
    "SELECT unique_award_number, count(distinct emp_number) as team_size\n",
    "FROM ds_iris_umetrics.dbo.core_employee\n",
    "WHERE period_end_date BETWEEN '2014-09-01' AND '2015-01-01'\n",
    "GROUP BY unique_award_number\n",
    "\"\n",
    "\n",
    "team_size_by_award <- dbGetQuery(con, qry)\n",
    "head(team_size_by_award)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then, we can get all employees who were employed during this time period by grabbing only the unique employee and award numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry <- \"\n",
    "SELECT distinct emp_number, unique_award_number\n",
    "FROM ds_iris_umetrics.dbo.core_employee\n",
    "WHERE period_end_date BETWEEN '2014-09-01' AND '2015-01-01'\n",
    "\"\n",
    "\n",
    "core_employee <- dbGetQuery(con, qry)\n",
    "head(core_employee)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can see the team size for each employee by the award they were on. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "core_employee %>% \n",
    "    left_join(team_size_by_award) %>% \n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Due to the existence of NA values, there is some cleaning that would need to be done with this data. For now, consider what we found: for each employee and each award that employee was on during Fall 2014, we can see how many total employees were on the same award.\n",
    "\n",
    "What if we only wanted to see how many graduate students were on the team, and use that as the measure of team size? We can do this too by adjusting the SQL query from before."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry <- \"\n",
    "SELECT unique_award_number, count(distinct emp_number) as team_size\n",
    "FROM ds_iris_umetrics.dbo.core_employee\n",
    "WHERE period_end_date BETWEEN '2014-09-01' AND '2015-01-01' AND umetrics_occupational_class = 'Graduate Student'\n",
    "GROUP BY unique_award_number\n",
    "\"\n",
    "\n",
    "grad_team_by_award <- dbGetQuery(con, qry)\n",
    "head(grad_team_by_award)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "core_employee %>% \n",
    "    left_join(grad_team_by_award) %>% \n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How might you refine this further? What other ways of defining a team size are there? "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}