Capstone Project Template.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Project Title\n",
    "### Data Engineering Capstone Project\n",
    "\n",
    "#### Project Summary\n",
    "--describe your project at a high level--\n",
    "\n",
    "The project follows the follow steps:\n",
    "* Step 1: Scope the Project and Gather Data\n",
    "* Step 2: Explore and Assess the Data\n",
    "* Step 3: Define the Data Model\n",
    "* Step 4: Run ETL to Model the Data\n",
    "* Step 5: Complete Project Write Up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Do all imports and installs here\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 1: Scope the Project and Gather Data\n",
    "\n",
    "#### Scope \n",
    "Explain what you plan to do in the project in more detail. What data do you use? What is your end solution look like? What tools did you use? etc>\n",
    "\n",
    "#### Describe and Gather Data \n",
    "Describe the data sets you're using. Where did it come from? What type of information is included? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Read in the data here\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cicid</th>\n",
       "      <th>i94yr</th>\n",
       "      <th>i94mon</th>\n",
       "      <th>i94cit</th>\n",
       "      <th>i94res</th>\n",
       "      <th>i94port</th>\n",
       "      <th>arrdate</th>\n",
       "      <th>i94mode</th>\n",
       "      <th>i94addr</th>\n",
       "      <th>depdate</th>\n",
       "      <th>...</th>\n",
       "      <th>entdepu</th>\n",
       "      <th>matflag</th>\n",
       "      <th>biryear</th>\n",
       "      <th>dtaddto</th>\n",
       "      <th>gender</th>\n",
       "      <th>insnum</th>\n",
       "      <th>airline</th>\n",
       "      <th>admnum</th>\n",
       "      <th>fltno</th>\n",
       "      <th>visatype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>692.0</td>\n",
       "      <td>692.0</td>\n",
       "      <td>XXX</td>\n",
       "      <td>20573.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>U</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>10282016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.897628e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>254.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>ATL</td>\n",
       "      <td>20551.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>D/S</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.736796e+09</td>\n",
       "      <td>00296</td>\n",
       "      <td>F1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20545.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MI</td>\n",
       "      <td>20691.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OS</td>\n",
       "      <td>6.666432e+08</td>\n",
       "      <td>93</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>NYC</td>\n",
       "      <td>20545.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MA</td>\n",
       "      <td>20567.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AA</td>\n",
       "      <td>9.246846e+10</td>\n",
       "      <td>00199</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>NYC</td>\n",
       "      <td>20545.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MA</td>\n",
       "      <td>20567.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AA</td>\n",
       "      <td>9.246846e+10</td>\n",
       "      <td>00199</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   cicid   i94yr  i94mon  i94cit  i94res i94port  arrdate  i94mode i94addr  \\\n",
       "0    6.0  2016.0     4.0   692.0   692.0     XXX  20573.0      NaN     NaN   \n",
       "1    7.0  2016.0     4.0   254.0   276.0     ATL  20551.0      1.0      AL   \n",
       "2   15.0  2016.0     4.0   101.0   101.0     WAS  20545.0      1.0      MI   \n",
       "3   16.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      MA   \n",
       "4   17.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      MA   \n",
       "\n",
       "   depdate   ...     entdepu  matflag  biryear   dtaddto gender insnum  \\\n",
       "0      NaN   ...           U      NaN   1979.0  10282016    NaN    NaN   \n",
       "1      NaN   ...           Y      NaN   1991.0       D/S      M    NaN   \n",
       "2  20691.0   ...         NaN        M   1961.0  09302016      M    NaN   \n",
       "3  20567.0   ...         NaN        M   1988.0  09302016    NaN    NaN   \n",
       "4  20567.0   ...         NaN        M   2012.0  09302016    NaN    NaN   \n",
       "\n",
       "  airline        admnum  fltno visatype  \n",
       "0     NaN  1.897628e+09    NaN       B2  \n",
       "1     NaN  3.736796e+09  00296       F1  \n",
       "2      OS  6.666432e+08     93       B2  \n",
       "3      AA  9.246846e+10  00199       B2  \n",
       "4      AA  9.246846e+10  00199       B2  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "\t\n",
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.\\\n",
    "config(\"spark.jars.packages\",\"saurfang:spark-sas7bdat:2.0.0-s_2.11\")\\\n",
    ".enableHiveSupport().getOrCreate()\n",
    "df_spark =spark.read.format('com.github.saurfang.sas.spark').load('../../data/18-83510-I94-Data-2016/i94_apr16_sub.sas7bdat')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "#write to parquet\n",
    "df_spark.write.parquet(\"sas_data\")\n",
    "df_spark=spark.read.parquet(\"sas_data\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 2: Explore and Assess the Data\n",
    "#### Explore the Data \n",
    "Identify data quality issues, like missing values, duplicate data, etc.\n",
    "\n",
    "#### Cleaning Steps\n",
    "Document steps necessary to clean the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Performing cleaning tasks here\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 3: Define the Data Model\n",
    "#### 3.1 Conceptual Data Model\n",
    "Map out the conceptual data model and explain why you chose that model\n",
    "\n",
    "#### 3.2 Mapping Out Data Pipelines\n",
    "List the steps necessary to pipeline the data into the chosen data model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 4: Run Pipelines to Model the Data \n",
    "#### 4.1 Create the data model\n",
    "Build the data pipelines to create the data model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Write code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 4.2 Data Quality Checks\n",
    "Explain the data quality checks you'll perform to ensure the pipeline ran as expected. These could include:\n",
    " * Integrity constraints on the relational database (e.g., unique key, data type, etc.)\n",
    " * Unit tests for the scripts to ensure they are doing the right thing\n",
    " * Source/Count checks to ensure completeness\n",
    " \n",
    "Run Quality Checks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Perform quality checks here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 4.3 Data dictionary \n",
    "Create a data dictionary for your data model. For each field, provide a brief description of what the data is and where it came from. You can include the data dictionary in the notebook or in a separate file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Step 5: Complete Project Write Up\n",
    "* Clearly state the rationale for the choice of tools and technologies for the project.\n",
    "* Propose how often the data should be updated and why.\n",
    "* Write a description of how you would approach the problem differently under the following scenarios:\n",
    " * The data was increased by 100x.\n",
    " * The data populates a dashboard that must be updated on a daily basis by 7am every day.\n",
    " * The database needed to be accessed by 100+ people."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}