02_Data_Exploration_UMETRICS.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>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " # <center>UMETRICS Dataset Exploration</center>\n",
    "<br>\n",
    "<center>Rukhshan Arif Mian, Brian Kim, Ekaterina Levitskaya, Maryah Garner</center>\n",
    "<br>\n",
    "<a href=\"https://doi.org/10.5281/zenodo.6463883\"><img src=\"https://zenodo.org/badge/DOI/10.5281/zenodo.6463883.svg\" alt=\"DOI\"></a>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Learning Objectives\n",
    "\n",
    "This notebook extends on the previous notebook (`01_Data_Exploration_SED_SDR.ipynb`) in that we apply similar exploratory analysis using SQL and R but on a different data source, UMETRICS."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**UMETRICS**: institution- / individual-level administrative data from universities.\n",
    "\n",
    "Within UMETRICS, we introduce 3 tables:\n",
    "\n",
    "1. `semester`: sources of funding at an individual-semester level. Full path: `ds_iris_umetrics.dbo.semester`\n",
    "2. `core_award`: transaction data on every sponsored project that has direct or overhead (indirect) expenditures. Full path: `ds_iris_umetrics.dbo.core_award`\n",
    "3. `core_employee`: provides information about the individuals working on awards at IRIS member universities. Full path: `ds_iris_umetrics.dbo.core_employee`\n",
    "\n",
    "**This notebook provides an introduction and examples for:**\n",
    "\n",
    "- How to create new tables from larger tables in a database (sometimes called the \"analytical frame\")\n",
    "- How to explore different variables of interest\n",
    "- How to explore aggregate metrics\n",
    "- How to join tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### R Setup\n",
    "\n",
    "Before using R functions that are not available in `base` R, load them using the built-in function `library()`. For example, running `library(tidyverse)` loads the `tidyverse` suite of packages. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> As we did in `01_Data_Exploration_SED_SDR.ipynb`, we suppress any messages and warnings associated with reading in the libraries to avoid clutter in our output. We also use the `ODBC` R package which allows us to interact with the database using SQL to pull data into R. Some additional manipulations are handled by R (by converting the datasets into dataframes)."
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "## Load the Data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Establish a Connection to the Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following code cells show how to use R to read data from a relational database. First, connect to the database using the `dbConnect` function. The `Driver` argument specifies the type of SQL database, while the `Server` arguments point to where the database is within the ADRF. \n",
    "\n",
    "\n",
    "> When creating your own notebook(s) in this course, remember to copy the following code chunk and use it to start each notebook to be able to connect to the database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Database Connection__"
   ]
  },
  {
   "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": [
    "### Formulate Data Query\n",
    "\n",
    "Writing a SQL query in Jupyter Notebook using R is similar to writing a SQL query in DBeaver. Depending on the questions of interest, different queries can be used to pull different data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Create a query as a `character` string object in R__\n",
    "\n",
    "The query in the below code block is used to pull all columns from the UMETRICS `semester` table, which provides us with semester level information.\n",
    ">  We use `SELECT TOP` to read-in only the first 10 rows; this is to acquaint you with the `semester` table. Examine all the different columns in this table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create query character string\n",
    "    # Database name: ds_iris_umetrics\n",
    "    # Schema name: dbo\n",
    "    # Table name: semester\n",
    "qry <- \n",
    "\" \n",
    "SELECT TOP 10 *\n",
    "FROM ds_iris_umetrics.dbo.semester\n",
    "\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading in the Data \n",
    "\n",
    "The data can be read into an R dataframe using the `con` and `query` as inputs to `dbGetQuery()`. \n",
    "\n",
    "> Recall that `con` (our server connection) includes a reference that tells it what driver to use. Forgetting to set up the driver would cause an error. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in dataframe and assign to umetrics_df\n",
    "umetrics_df <- dbGetQuery(con, qry)\n",
    "\n",
    "# see first few rows of umetrics_df\n",
    "head(umetrics_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# view the columns in this df\n",
    "names(umetrics_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary Statistics\n",
    "\n",
    "This section covers aggregating statistics on the data. The goal of this exercise is to get a better understanding of the data. Ask the following questions: *Are the data generally clean? What are possible sources of error? What are the types of objects and what are the variables?* To answer broader research questions, start by looking at simple aggregate statistics in each of the data sources."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Note: __**Large tables can take a long time to process on shared databases**__, so we demonstrate using SQL and R with consideration for how much data is read back into R."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Motivating Questions:**\n",
    "\n",
    "- What sources frequently fund doctorate students from the 2015 cohort?\n",
    "- For the top 3 sources of funding, how does the frequency of funding vary over time?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recall the SED cohort for 2015 that we used in the previous notebook. We will use UMETRICS data here to help provide insight into the funding history of doctorate students.\n",
    "\n",
    "The `semester` table provides us with funding history by each semester between 2011 and 2020. We utilize the `INNER JOIN` clause in SQL to combine the SED cohort for 2015 with the semester-level table.\n",
    "\n",
    ">**Note**: This join can be performed in R as well (using the `inner_join` function) but both the SED and semester tables are fairly large. As noted just above, because large tables can take a long time to process, using SQL to combine larger tables is always encouraged. \n",
    "\n",
    "\n",
    "Once we have combined our tables in SQL, we will read them into R as a dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining SED with UMETRICS\n",
    "To combine SED with UMETRICS, we'll join SED with the SED-UMETRICS crosswalk and then with the UMETRICS `semester` table. The SED-UMETRICS crosswalk allows us to link **drf_id** (INDIVIDUAL ID NUMBER) from SED with **emp_number** (INDIVIDUAL EMPLOYEE NUMBER) in UMETRICS, making it possible for us to combine two different tables with different primary keys.\n",
    "\n",
    "There are three steps we take to combine SED with UMETRICS:\n",
    "1. Combine the SED data (only selecting the **drf_id** and **phdinst**)) with the SED-UMETRICS crosswalk using an inner-join on **drf_id**\n",
    "2. Combine the resulting table and with the UMETRICS semester table (all columns) using an inner-join on **emp_number**\n",
    "3. Filter to only keep the 2015 SED cohort.\n",
    "\n",
    "> Performing a `JOIN` or an `INNER JOIN` only keep observations that are in both SED and UMETRICS. In this notebook, we use `INNER JOIN` to remind you the type of join that is being performed. We encourage you to think about cases where it might be useful to use a different type of join. For your research project, you might want to include all observations from SED even if they do not appear in UMETRICS – allowing you to keep individuals that did not receive any funding. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First, join the SED table using THE SED-UMETRICS crosswalk, and then join the resulting table with the IRIS semester table\n",
    "qry <- \"\n",
    "SELECT sed.drf_id, sed.phdinst, iris_semester.*\n",
    "FROM ds_nsf_ncses.dbo.nsf_sed sed\n",
    "INNER JOIN tr_ncses_2021.dbo.sed_umetrics_xwalk xwalk ON sed.drf_id = xwalk.drf_id\n",
    "INNER JOIN ds_iris_umetrics.dbo.semester iris_semester ON iris_semester.emp_number = xwalk.emp_number\n",
    "WHERE phdfy = '2015'\n",
    "\"\n",
    "# read in dataframe and assign to sed_umetrics\n",
    "sed_umetrics <- dbGetQuery(con, qry)\n",
    "\n",
    "# first few observations\n",
    "head(sed_umetrics)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# column names\n",
    "names(sed_umetrics)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking for duplicates\n",
    "\n",
    "One of our assumptions with the combined SED-UMETRICS dataframe is that it is unique at the individual-semester level. That is, we only see one observation for every individual-semester (**drf_id-semester**) combination. We can check this assumption using the code below. Here, we group by **drf_id** and **semester**, count the number of observations we see (per group), and then sort this count in descending order to see the highest counts first. \n",
    "\n",
    "If the combine SED-UMETRICS datafrmae is unique at the individual-semester level, each **drf_id-semester** combination should appear only once. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sed_umetrics %>%\n",
    "    group_by(drf_id, semester) %>%\n",
    "    summarize(count = n()) %>%\n",
    "    arrange(desc(count)) %>%\n",
    "    head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Arranged in descending order of count, we can see that there are no duplicates because the highest count is 1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Modal funders\n",
    "\n",
    "In the `semester` table, there is a variable, **modal_funder**, which indicates a source which most frequently funds a given student in a given semester. You are encouraged to refer to the data dictionary to learn more about this variable.\n",
    "\n",
    "Recall our first motivating question: *What sources frequently fund doctorate students from the 2015 cohort?* To address this question, we utilize **modal_funder** to explore the counts of doctorate students who received funding from a particular source. Furthermore, we count the number of semesters funded by each source between 2011 and 2020."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# group by modal funder, create counts, sort in descending order, omit missing values\n",
    "modal_funders <- sed_umetrics %>%\n",
    "    group_by(modal_funder) %>% # group by modal_funder\n",
    "    summarise(count_individuals = n_distinct(drf_id), # count the number of distinct individuals (drf_id)\n",
    "              count_inst = n_distinct(phdinst), # count the number of distinct phd institutions (phdinst)\n",
    "              total_semesters_funded = n_distinct(semester)) %>% # count the number of (distinct) semesters funded (semester)\n",
    "    arrange(desc(count_individuals)) #sort in descending order of individual count\n",
    "\n",
    "head(modal_funders)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we use the `n_distinct` function above. This function counts the number of unique values in a vector or variable – we use this to avoid double\n",
    "counting. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even though it is not required at all times, in this case, it is useful for us to sort **count_individuals** in descending order so we are able to see the most frequent sources of funding first. We do this by using `arrange(desc(count_individuals))`. We would use `arrange(count_indivdiduals)` to sort this in ascending order if we wanted to see the least frequent source of funding first.\n",
    "\n",
    ">**Note**: not everyone has a modal funder recorded; In fact, as you see, having 'NA' for modal funder is the most common outcome recorded – this is because the modal funder for those individuals is not coming from a federal agency. For the purpose of this analysis we will remove these **NA** observations using the `filter` function. When working on your research project we recommend TRYING to understand the **NA** observations and how they relate to your question(s) before removing them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "modal_funders <- modal_funders %>%\n",
    "    filter(!is.na(modal_funder)) # ! refers to not. here, we are looking for non-missing values (not is.na(modal_funder))\n",
    "\n",
    "head(modal_funders)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Frequency of funding over time\n",
    "\n",
    "Next, we address our second motivating question: For the top 3 sources of funding, how does the frequency of funding vary over time? The first step here is to identify the top 3 sources of funding.\n",
    "\n",
    "The `modal_funders` dataframe we created above is sorted (in descending order) on the number of individuals funded by a particular source. That is, the first observation in this dataframe corresponds to the source that most frequently funds doctorate students, the second observation corresponds to the second most frequent source of funding, and so on. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Subsetting our data to include the first 3 observations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Alternative method to getting modal funders\n",
    "top3_modal_funders <- head(modal_funders, 3) # here, 3 refers to keeping the first 3 observations\n",
    "top3_modal_funders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our goal is to see how these sources of funding varied, in terms of students funded, over time. To further clarify, we want to know the number of doctorate students funded by each source in each semester. \n",
    "\n",
    "The code for this is as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "top3_by_sem <- sed_umetrics %>%\n",
    "    filter(modal_funder %in% top3_modal_funders$modal_funder) %>% # filter to keep only the most frequent funding sources\n",
    "    group_by(modal_funder, semester) %>% # grouping by funding source and semester\n",
    "    summarise(count_individuals = n_distinct(drf_id), # counting the number of distinct individuals (doctorate students)\n",
    "              count_inst = n_distinct(phdinst)) %>% # counting the number of distinct institutions\n",
    "    arrange(semester) # sorting in ascending order by semester\n",
    "\n",
    "head(top3_by_sem)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exploring Binary Variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Within our semester table, we have binary variables for each source of funding as well. These are shown below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "names(sed_umetrics)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That is, we have a column named **NSF** that equals 1 if an individual received any funding from NSF during a semester and equals 0 if otherwise. You are encouraged to refer to the data dictionary to learn more about the binary variables in this table.\n",
    "\n",
    "In this section, we look into methods we can utilize to aggregate and explore binary variables in R. We consider **NSF** for this notebook. Our **motivating questions** are as follows:\n",
    "\n",
    "- How many number of individuals has NSF funded over time?\n",
    "- For each student, find the number of semesters that were funded by NSF. What is the highest number of semesters a student was funded by NSF?\n",
    "- Number of students funded for each duration. That is, how many students were funded by NSF for 1 semester? 2 semesters? 3 semesters? and so on. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### NSF Column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We explore this column using the code below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use the table function to view the distribution of values in the NSF column\n",
    "table(sed_umetrics$NSF, useNA=\"always\") # useNA = \"always\" to include the number of missing values for this column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another way to explore a binary variable is to do the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use the `sum` function. Our column only consists of 1s or 0s (in this case, we ignore missing values using `na.rm`)\n",
    "sum(sed_umetrics$NSF, na.rm = TRUE) # we can sum the column to arrive at the number of observations for whom **NSF** equals 1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many individuals has NSF funded over time?\n",
    "\n",
    "To address our first motivating question, we subset our data frame to only keep students who have been funded by NSF in at least one semester. Next, we group by each semester and count the number of individuals. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts_NSF_fund <- sed_umetrics %>%\n",
    "    filter(NSF == 1) %>% # filter to keep observations for whom NSF = 1\n",
    "    group_by(semester) %>% # group by semester\n",
    "    summarise(count_indiv_NSF_funded = n_distinct(drf_id), # count the distinct number of doctorate students\n",
    "              count_inst = n_distinct(phdinst)) # count the distinct number of institutions\n",
    "\n",
    "head(counts_NSF_fund)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note: some observations would not pass disclosure review (it’s a good example). We would need some kind of aggregation here.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of semesters NSF funded (by individual):\n",
    "\n",
    "Next, we look at the number of semesters in which each student received any type of funding by NSF. To further clarify, for how many semesters did a student receive any type of funding from NSF? \n",
    "\n",
    "This type of exploration is at the individual (**drf_id**) level and allows us to see the total number of semesters that NSF provided any type of funding for a doctorate student. \n",
    "\n",
    "We approach this using the code below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NSF_num_sem <- sed_umetrics %>%\n",
    "    filter(NSF == 1) %>% # keep observations for NSF (where NSF = 1)\n",
    "    group_by(drf_id) %>% # group by individual\n",
    "    summarise(num_sem_funded = n_distinct(semester), # count the number of semesters funded\n",
    "              count_inst = n_distinct(phdinst)) %>% # count the number of institutions\n",
    "    arrange(desc(num_sem_funded))\n",
    "\n",
    "head(NSF_num_sem)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Number of semesters funded and corresponding number of students\n",
    "Next, we count the number of semesters funded by NSF and the corresponding counts of individuals. Note that this is different from our first motivating question. Previously we looked at the number of individuals funded by NSF in each semester. Here, we look at the number of individuals who were funded for 1, 2, 3, 4 (and so on) semesters.\n",
    "\n",
    "We first get the counts of number of semeseters funded by NSF by each student (exactly what we did in the code above):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NSF_num_indiv_sem <- sed_umetrics %>%\n",
    "    filter(NSF == 1) %>% # keep observations for NSF\n",
    "    group_by(drf_id) %>% # group by individual\n",
    "    summarise(num_sem_funded = n_distinct(semester)) # count the number of semesters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After doing so, we use the code below to count the number of individuals associated with a certain semester count."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "NSF_num_indiv_sem_grp <- NSF_num_indiv_sem %>%\n",
    "    group_by(num_sem_funded) %>% # group by number of semesters funded\n",
    "    summarise(num_indiv = n_distinct(drf_id)) # count the number of individuals\n",
    "\n",
    "head(NSF_num_indiv_sem_grp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exploring Core Awards\n",
    "\n",
    "The aim of this sub-section is to give you a brief overview of the `core_award` table in the `ds_iris_umetrics` database. We encourage you to think about how you may want to utilize the columns in this table for you research project. `core_award` provides us with information related to awards in UMETRICS' database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We call the first 10 observations of `core_award`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry <- \"\n",
    "SELECT TOP 10 * \n",
    "FROM ds_iris_umetrics.dbo.core_award\"\n",
    "core_award <- dbGetQuery(con, qry)\n",
    "\n",
    "head(core_award)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You are encouraged to explore the columns (as shown below) and refer to the data dictionary to learn more about what each column stands for."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "names(core_award)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exploring Core Employee"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly to `core_award`, we have a `core_employee` table that includes information on awards at an employee level. As in the case for `core_award`, we encourage you to think about how these tables can be utilized as part of your research project. Furthermore, you may think about if it is possible to combine `core_award` with `core_employee`. If so, how could a combined table be useful when thinking about your research question? What might be the associated disadvantages? (hint: extremely large table)\n",
    "\n",
    "As a note, these tables (especially `core_employee`) are fairly large so it might make sense to define a specific cohort and join these two tables based on that.\n",
    "\n",
    "We provide a brief introduction of the `core_employee` table below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry <- \"\n",
    "SELECT TOP 10 * \n",
    "FROM ds_iris_umetrics.dbo.core_employee\n",
    "\"\n",
    "\n",
    "core_employee <- dbGetQuery(con, qry)\n",
    "head(core_employee)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "names(core_employee)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You are encouraged to refer to the data dictionary to learn more about the different columns in `core_employee`.\n",
    "\n",
    "Note: In the case of reading in the `core_award` and `core_employee` tables, we choose to only read in the first (top) 10 observations. One of the drawbacks of using R is that it struggles with handling very large data. Thus, in order to effectively use these tables, it is useful to perform exploration and analysis tasks in DBeaver. If we are to use R, it might be useful to define a specific cohort and analyzing that as opposed to calling in the complete tables. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving Results\n",
    "\n",
    "We have already created a directory structure for you. Within the `Module 2` folder, you will see the following:\n",
    "\n",
    "- Figures\n",
    "- Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will now save some of the tables that we created above as csv files.\n",
    "\n",
    "For this purpose, we utilize the following code:\n",
    "\n",
    "`write.csv(df_to_save, \"Tables\\\\df_to_save.csv\", row.names = FALSE)`\n",
    "\n",
    "Here:\n",
    "\n",
    "- `df_to_save` refers to the R data frame we want to save. \n",
    "- `\"Tables\\\\df_to_save.csv\"` refers to the filepath, we would like to save our data frame in.\n",
    "- `row.names = FALSE` excludes row numbers when we save our data frame as a csv. If we had `TRUE` instead of `FALSE`, we would have an extra column (of row numbers) as the first column in our csv. \n",
    "\n",
    "\n",
    "We have added the saving code for each data frame we want to save and you do not have to make any updates to the code below in order for it to run."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# top 3 modal funders – semester wise counts\n",
    "write.csv(top3_by_sem, \"Tables\\\\top3_by_sem.csv\", row.names = FALSE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Closing the Database connection\n",
    "\n",
    "Uncomment the code below to close the database connection. This allows us to free up resources (most importantly, memory) for future work. However, you are advised to close the connection only when you have executed the cells above. \n",
    "\n",
    "**Note**: Once the connection is closed, you will have to re-open the connection (at the start of the notebook) to utilize run the code again. You will not be able to run any SQL-related code in R once the connection is closed. That is, once you have run `dbDisconnect(con)`, you will have to re-run the code associated with establishing a connection with SQL to interact with any tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close the database connection\n",
    "# dbDisconnect(con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "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"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {
    "height": "566px",
    "left": "0px",
    "right": "954px",
    "top": "110px",
    "width": "179px"
   },
   "toc_section_display": "block",
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}