NICCC_GDS_Merge.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "NICCC_GDS_Merge.ipynb",
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/mdhindsa-1/Semester-Project/blob/main/NICCC_GDS_Merge.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Introduction"
      ],
      "metadata": {
        "id": "ZHGSoz3btjbV"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "This notebook it to provide instrutions on how to download and merge the consequences data that feeds into the UVA Data Science Capstone team '22 Google Data Studio Dashboard.\n",
        "\n",
        "- Data Source: https://niccc.nationalreentryresourcecenter.org/consequences\n",
        "- Google Data Sheet: https://datastudio.google.com/u/0/reporting/6bdb02bd-592d-466a-91d2-ff1a1bad86a8/page/p_asix3xorwc"
      ],
      "metadata": {
        "id": "Zjvbu72atiPr"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# NICCC Data Collection"
      ],
      "metadata": {
        "id": "nFXp3hbZtlqc"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## UVA Capstone Data Source\n",
        "\n",
        "The set of data used for the original version of the Dashboard was retreived July 2022 and useable version for this notebook can be found here (with limited access):  https://drive.google.com/drive/folders/1Msm8ZARuR6Sz0O9aglVWBA6XgI4JzgaD?usp=sharing"
      ],
      "metadata": {
        "id": "wLRqMaiG3U0f"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## How to Acquire New Data"
      ],
      "metadata": {
        "id": "75Z_YVAM4zVl"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Data is collected from the NICCC website (https://niccc.nationalreentryresourcecenter.org/consequences).  Limitations exist that consequences can only be downloaded in sets of 2500 so the followings steps were implemented:\n",
        "\n",
        "1. Visit URL listed above\n",
        "2. Filter page by going to Consequences and choosing \"Employment & Volunteering\"\n",
        "3. Filter page by Jurisdiction and export each state using the export button:\n",
        "    - a. Select state in Jurisdiction box\n",
        "    - b. Click the Export Button\n",
        "    - c. Save file state file under the filename in the table below to a Google Drive location.\n",
        "4. The naming convention of the files is correlated to a state and shown in the table below (This is not optimal and much in need of an improvement)"
      ],
      "metadata": {
        "id": "N-oBonuvt8OE"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "| **File Name**     | **State/Territory**  |   |   |   |\n",
        "|-------------------|----------------------|---|---|---|\n",
        "| download (F).csv  | Federal              |   |   |   |\n",
        "| download (0).csv  | Alabama              |   |   |   |\n",
        "| download (1).csv  | Alaska               |   |   |   |\n",
        "| download (2).csv  | Arizona              |   |   |   |\n",
        "| download (3).csv  | Arkansas             |   |   |   |\n",
        "| download (4).csv  | California           |   |   |   |\n",
        "| download (5).csv  | Colorado             |   |   |   |\n",
        "| download (6).csv  | Connecticut          |   |   |   |\n",
        "| download (7).csv  | Delaware             |   |   |   |\n",
        "| download (8).csv  | District of Columbia |   |   |   |\n",
        "| download (9).csv  | Florida              |   |   |   |\n",
        "| download (10).csv | Georgia              |   |   |   |\n",
        "| download (11).csv | Hawaii               |   |   |   |\n",
        "| download (12).csv | Idaho                |   |   |   |\n",
        "| download (13).csv | Illinois             |   |   |   |\n",
        "| download (14).csv | Indiana              |   |   |   |\n",
        "| download (15).csv | Iowa                 |   |   |   |\n",
        "| download (16).csv | Kansas               |   |   |   |\n",
        "| download (17).csv | Kentucky             |   |   |   |\n",
        "| download (18).csv | Louisiana            |   |   |   |\n",
        "| download (19).csv | Maine                |   |   |   |\n",
        "| download (20).csv | Maryland             |   |   |   |\n",
        "| download (21).csv | Massachusetts        |   |   |   |\n",
        "| download (22).csv | Michigan             |   |   |   |\n",
        "| download (23).csv | Minnesota            |   |   |   |\n",
        "| download (24).csv | Mississippi          |   |   |   |\n",
        "| download (25).csv | Missouri             |   |   |   |\n",
        "| download (26).csv | Montana              |   |   |   |\n",
        "| download (27).csv | Nebraska             |   |   |   |\n",
        "| download (28).csv | Nevada               |   |   |   |\n",
        "| download (29).csv | New Hampshire        |   |   |   |\n",
        "| download (30).csv | New Jersey           |   |   |   |\n",
        "| download (31).csv | New Mexico           |   |   |   |\n",
        "| download (32).csv | New York             |   |   |   |\n",
        "| download (33).csv | North Carolina       |   |   |   |\n",
        "| download (34).csv | North Dakota         |   |   |   |\n",
        "| download (35).csv | Ohio                 |   |   |   |\n",
        "| download (36).csv | Oklahoma             |   |   |   |\n",
        "| download (37).csv | Oregon               |   |   |   |\n",
        "| download (38).csv | Pennsylvania         |   |   |   |\n",
        "| download (39).csv | Puerto Rico          |   |   |   |\n",
        "| download (40).csv | Rhode Island         |   |   |   |\n",
        "| download (41).csv | South Carolina       |   |   |   |\n",
        "| download (42).csv | South Dakota         |   |   |   |\n",
        "| download (43).csv | Tennessee            |   |   |   |\n",
        "| download (44).csv | Texas                |   |   |   |\n",
        "| download (45).csv | Utah                 |   |   |   |\n",
        "| download (46).csv | Vermont              |   |   |   |\n",
        "| download (47).csv | Virginia Islands     |   |   |   |\n",
        "| download (48).csv | Virginia             |   |   |   |\n",
        "| download (49).csv | Washington           |   |   |   |\n",
        "| download (50).csv | West Virginia        |   |   |   |\n",
        "| download (51).csv | Wisconsin            |   |   |   |\n",
        "| download (52).csv | Wyoming              |   |   |   |"
      ],
      "metadata": {
        "id": "-xI9rxp9ydlj"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Python Code to Merge State Files into One File"
      ],
      "metadata": {
        "id": "Qw0EKsI-48sg"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Python Package Imports"
      ],
      "metadata": {
        "id": "mG_A7dy6tzvd"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import numpy as np"
      ],
      "metadata": {
        "id": "DgoST0V-tp2i"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Mount Google Drive\n",
        "\n",
        "This is where the connection is made to RE's Google Drive and read the files downloaded in the previous step. \n",
        "\n",
        "This will ask you which account you would like to connect, select the RE account and then on the next screen click Allow.  This is necessary for Google to connect Google Colab to Google Drive."
      ],
      "metadata": {
        "id": "lY4ri68DzQxI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "h7Mvgf0Kzi3I",
        "outputId": "049d4706-2692-4d60-aa14-1c77506bd471"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import os \n",
        "os.getcwd() \n",
        "os.chdir('/content/drive/MyDrive/Capstone') #Change this path to the directory the state NICCC files are kept"
      ],
      "metadata": {
        "id": "_CRu9yqB_R3E"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Merge Data Files"
      ],
      "metadata": {
        "id": "z6vowxYJtqbB"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The files that are downloaded and combined are not always exported correctly.  These bad lines are kept out by utilizing the \"on_bad_lines='skip'\" code.  These lines have been check manually and they are export errors that do not relate to specific statues (as of 7/2022).\n",
        "\n",
        "The resulting doc variable is a dataframe that contains all the information for each state.\n",
        "\n",
        "(There is probably a more efficent coding structure but this shows exactly what the purpose of the code is)"
      ],
      "metadata": {
        "id": "_ep7yVavy1cm"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "docF = pd.read_csv('download (F).csv', on_bad_lines='skip') #federal\n",
        "doc0 = pd.read_csv('download (0).csv')\n",
        "doc1 = pd.read_csv('download (1).csv')\n",
        "doc2 = pd.read_csv('download (2).csv')\n",
        "doc3 = pd.read_csv('download (3).csv')\n",
        "doc4 = pd.read_csv('download (4).csv', on_bad_lines='skip')\n",
        "doc5 = pd.read_csv('download (5).csv')\n",
        "doc6 = pd.read_csv('download (6).csv', on_bad_lines='skip')\n",
        "doc7 = pd.read_csv('download (7).csv', on_bad_lines='skip')\n",
        "doc8 = pd.read_csv('download (8).csv')\n",
        "doc9 = pd.read_csv('download (9).csv')\n",
        "doc10 = pd.read_csv('download (10).csv') # Georgia\n",
        "doc11 = pd.read_csv('download (11).csv')\n",
        "doc12 = pd.read_csv('download (12).csv')\n",
        "doc13 = pd.read_csv('download (13).csv')\n",
        "doc14 = pd.read_csv('download (14).csv')\n",
        "doc15 = pd.read_csv('download (15).csv')\n",
        "doc16 = pd.read_csv('download (16).csv')\n",
        "doc17 = pd.read_csv('download (17).csv')\n",
        "doc18 = pd.read_csv('download (18).csv')\n",
        "doc19 = pd.read_csv('download (19).csv')\n",
        "doc20 = pd.read_csv('download (20).csv')\n",
        "doc21 = pd.read_csv('download (21).csv')\n",
        "doc22 = pd.read_csv('download (22).csv')\n",
        "doc23 = pd.read_csv('download (23).csv')\n",
        "doc24 = pd.read_csv('download (24).csv')\n",
        "doc25 = pd.read_csv('download (25).csv')\n",
        "doc26 = pd.read_csv('download (26).csv')\n",
        "doc27 = pd.read_csv('download (27).csv')\n",
        "doc28 = pd.read_csv('download (28).csv')\n",
        "doc29 = pd.read_csv('download (29).csv', on_bad_lines='skip') #New Hampshrie\n",
        "doc30 = pd.read_csv('download (30).csv')\n",
        "doc31 = pd.read_csv('download (31).csv')\n",
        "doc32 = pd.read_csv('download (32).csv')\n",
        "doc33 = pd.read_csv('download (33).csv')\n",
        "doc34 = pd.read_csv('download (34).csv')\n",
        "doc35 = pd.read_csv('download (35).csv') #Ohio\n",
        "doc36 = pd.read_csv('download (36).csv')\n",
        "doc37 = pd.read_csv('download (37).csv')\n",
        "doc38 = pd.read_csv('download (38).csv')\n",
        "doc39 = pd.read_csv('download (39).csv')\n",
        "doc40 = pd.read_csv('download (40).csv')\n",
        "doc41 = pd.read_csv('download (41).csv')\n",
        "doc42 = pd.read_csv('download (42).csv')\n",
        "doc43 = pd.read_csv('download (43).csv')\n",
        "doc44 = pd.read_csv('download (44).csv')\n",
        "doc45 = pd.read_csv('download (45).csv')\n",
        "doc46 = pd.read_csv('download (46).csv')\n",
        "doc47 = pd.read_csv('download (47).csv')\n",
        "doc48 = pd.read_csv('download (48).csv') #Virginia\n",
        "doc49 = pd.read_csv('download (49).csv') #Washington\n",
        "doc50 = pd.read_csv('download (50).csv') #West Virginia\n",
        "doc51 = pd.read_csv('download (51).csv') #Wisconsin\n",
        "doc52 = pd.read_csv('download (52).csv') #Wyoming"
      ],
      "metadata": {
        "id": "nL1s3SNttsxj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Next, a list is created to label the States in the files that were downloaded."
      ],
      "metadata": {
        "id": "g-qqHaJvj2TJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#created a list of jurisdiction names\n",
        "jurisdiction_names = [\"Federal\", \"Alabama\", \"Alaska\", \"Arizona\",\"Arkansas\", \"California\", \"Colorado\", \"Connecticut\", \"Delaware\", \n",
        "                      \"District of Columbia\", \"Florida\", \"Georgia\", \"Hawaii\", \"Idaho\", \"Illinois\", \"Indiana\", \"Iowa\", \"Kansas\", \n",
        "                      \"Kentucky\", \"Louisiana\", \"Maine\", \"Maryland\",\"Massachusetts\", \"Michigan\", \"Minnesota\", \"Mississippi\", \"Missouri\", \n",
        "                      \"Montana\", \"Nebraska\", \"Nevada\",\"New Hampshire\", \"New Jersey\", \"New Mexico\",  \"New York\", \"North Carolina\", \n",
        "                      \"North Dakota\", \"Ohio\", \"Oklahoma\", \"Oregon\", \"Pennsylvania\", \"Puerto Rico\", \"Rhode Island\", \"South Carolina\", \n",
        "                      \"South Dakota\", \"Tennessee\", \"Texas\", \"Utah\", \"Vermont\",\"Virgin Islands\", \"Virginia\",  \"Washington\", \"West Virginia\", \n",
        "                      \"Wisconsin\", \"Wyoming\"]"
      ],
      "metadata": {
        "id": "-H1VZiJIjq-Y"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "A list of dataframes is created so that it can be iteratively updated."
      ],
      "metadata": {
        "id": "QghhiAHrkHBn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#a list of all file names\n",
        "all_files = [docF, doc0, doc1, doc2, doc3, doc4, doc5, doc6, doc7, doc8, doc9, doc10, doc11, \n",
        "             doc12, doc13, doc14, doc15, doc16, doc17, doc18, doc19, doc20, doc21, doc22, \n",
        "             doc23, doc24, doc25, doc26, doc27, doc28, doc29, doc30, doc31, doc32, doc33, doc34, doc35,\n",
        "            doc36, doc37, doc38, doc39, doc40, doc41, doc42, doc43, doc44, doc45, doc46, doc47, doc48, \n",
        "             doc49, doc50, doc51, doc52]"
      ],
      "metadata": {
        "id": "tEQvZV8uj-2L"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The code below iterates through the two lists and adds the name of the states to the dataframes created when the files were loaded. And then puts them into a list"
      ],
      "metadata": {
        "id": "Y6XPQODXkWNC"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "n = 0\n",
        "cleaned_data = []\n",
        "for x in all_files:\n",
        "    x['Jurisdiction'] = x['Jurisdiction'].fillna(jurisdiction_names[n])\n",
        "    cleaned_data.append(x)\n",
        "    n = n + 1"
      ],
      "metadata": {
        "id": "bStpKc89kOnn"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The export from Ohio produced an error and this is the fix"
      ],
      "metadata": {
        "id": "b6-W-KYqkmte"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "cleaned_data[36] = cleaned_data[36].query('Jurisdiction != \"Education & schools|Teachers & instructors|Motor vehicle sales & service\"') \n",
        "#checked Ohio file, this is an error in the import - removed"
      ],
      "metadata": {
        "id": "vt1pCuxykmFm"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "All of the state dataframes are concatenated together to create a large dataframe. Then the dataframe is saved as a .csv file"
      ],
      "metadata": {
        "id": "vAQHCy94k8Jv"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_full = pd.concat(cleaned_data).reset_index(drop=True)"
      ],
      "metadata": {
        "id": "MQYMfCHFD4Zl"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now let's take a look at the table to ensure everything worked together. "
      ],
      "metadata": {
        "id": "BBxKq3keEr0I"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_full.head()"
      ],
      "metadata": {
        "id": "3kTbK9ujEpLw",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 808
        },
        "outputId": "9d76bd62-725e-4a5b-8702-b0f338dd007f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "          Citation                                       Citation URL  \\\n",
              "0   8 USCS § 1324c  http://uscode.house.gov/view.xhtml?req=granule...   \n",
              "1   12 USCS § 5710  http://uscode.house.gov/view.xhtml?req=granule...   \n",
              "2  12 CFR 1007.103   https://www.law.cornell.edu/cfr/text/12/1007.103   \n",
              "3     12 CFR 741.3      https://www.law.cornell.edu/cfr/text/12/741.3   \n",
              "4    15 USCS § 650  http://uscode.house.gov/view.xhtml?req=granule...   \n",
              "\n",
              "                                               Title Number of Consequences  \\\n",
              "0  Ineligible for employment in immigration docum...                      1   \n",
              "1  Ineligible for employment with recipient of St...                      1   \n",
              "2  Ineligible to serve as mortgage loan originato...                      1   \n",
              "3  Ineligible to serve as credit union director/o...                      1   \n",
              "4  Discharge small business lender management off...                      1   \n",
              "\n",
              "  Relevant Subsections Related Statutes  \\\n",
              "0                  (e)              NaN   \n",
              "1            (a) - (c)              NaN   \n",
              "2    (d)(1)(iii), (ix)              NaN   \n",
              "3                  (c)              NaN   \n",
              "4          (g)(4); (f)              NaN   \n",
              "\n",
              "                                               Notes  \\\n",
              "0  Links may direct to unofficial sources where o...   \n",
              "1  Links may direct to unofficial sources where o...   \n",
              "2  Links may direct to unofficial sources where o...   \n",
              "3  Links may direct to unofficial sources where o...   \n",
              "4  Links may direct to unofficial sources where o...   \n",
              "\n",
              "                                     Current Through Jurisdiction  \\\n",
              "0  2018 legislative session and all 2018 regulato...      Federal   \n",
              "1  2018 legislative session and all 2018 regulato...      Federal   \n",
              "2  2018 legislative session and all 2018 regulato...      Federal   \n",
              "3  2018 legislative session and all 2018 regulato...      Federal   \n",
              "4  2018 legislative session and all 2018 regulato...      Federal   \n",
              "\n",
              "                Consequences                                Keywords  \\\n",
              "0  Employment & volunteering                                     NaN   \n",
              "1  Employment & volunteering                                     NaN   \n",
              "2  Employment & volunteering  Banking, lending, securities & finance   \n",
              "3  Employment & volunteering  Banking, lending, securities & finance   \n",
              "4  Employment & volunteering  Banking, lending, securities & finance   \n",
              "\n",
              "                                        Offense Type              Discretion  \\\n",
              "0  Crimes involving fraud, dishonesty, misreprese...     Mandatory/Automatic   \n",
              "1                                       Sex offenses     Mandatory/Automatic   \n",
              "2  Crimes involving fraud, dishonesty, misreprese...           Discretionary   \n",
              "3  Crimes involving fraud, dishonesty, misreprese...  Discretionary (waiver)   \n",
              "4  Crimes involving fraud, dishonesty, misreprese...           Discretionary   \n",
              "\n",
              "     Duration  \n",
              "0  Indefinite  \n",
              "1  Indefinite  \n",
              "2  Indefinite  \n",
              "3  Indefinite  \n",
              "4  Indefinite  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-ca44e0b0-8601-4468-84e1-ad4698a6716e\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Citation</th>\n",
              "      <th>Citation URL</th>\n",
              "      <th>Title</th>\n",
              "      <th>Number of Consequences</th>\n",
              "      <th>Relevant Subsections</th>\n",
              "      <th>Related Statutes</th>\n",
              "      <th>Notes</th>\n",
              "      <th>Current Through</th>\n",
              "      <th>Jurisdiction</th>\n",
              "      <th>Consequences</th>\n",
              "      <th>Keywords</th>\n",
              "      <th>Offense Type</th>\n",
              "      <th>Discretion</th>\n",
              "      <th>Duration</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>8 USCS § 1324c</td>\n",
              "      <td>http://uscode.house.gov/view.xhtml?req=granule...</td>\n",
              "      <td>Ineligible for employment in immigration docum...</td>\n",
              "      <td>1</td>\n",
              "      <td>(e)</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Links may direct to unofficial sources where o...</td>\n",
              "      <td>2018 legislative session and all 2018 regulato...</td>\n",
              "      <td>Federal</td>\n",
              "      <td>Employment &amp; volunteering</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Crimes involving fraud, dishonesty, misreprese...</td>\n",
              "      <td>Mandatory/Automatic</td>\n",
              "      <td>Indefinite</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>12 USCS § 5710</td>\n",
              "      <td>http://uscode.house.gov/view.xhtml?req=granule...</td>\n",
              "      <td>Ineligible for employment with recipient of St...</td>\n",
              "      <td>1</td>\n",
              "      <td>(a) - (c)</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Links may direct to unofficial sources where o...</td>\n",
              "      <td>2018 legislative session and all 2018 regulato...</td>\n",
              "      <td>Federal</td>\n",
              "      <td>Employment &amp; volunteering</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Sex offenses</td>\n",
              "      <td>Mandatory/Automatic</td>\n",
              "      <td>Indefinite</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>12 CFR 1007.103</td>\n",
              "      <td>https://www.law.cornell.edu/cfr/text/12/1007.103</td>\n",
              "      <td>Ineligible to serve as mortgage loan originato...</td>\n",
              "      <td>1</td>\n",
              "      <td>(d)(1)(iii), (ix)</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Links may direct to unofficial sources where o...</td>\n",
              "      <td>2018 legislative session and all 2018 regulato...</td>\n",
              "      <td>Federal</td>\n",
              "      <td>Employment &amp; volunteering</td>\n",
              "      <td>Banking, lending, securities &amp; finance</td>\n",
              "      <td>Crimes involving fraud, dishonesty, misreprese...</td>\n",
              "      <td>Discretionary</td>\n",
              "      <td>Indefinite</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>12 CFR 741.3</td>\n",
              "      <td>https://www.law.cornell.edu/cfr/text/12/741.3</td>\n",
              "      <td>Ineligible to serve as credit union director/o...</td>\n",
              "      <td>1</td>\n",
              "      <td>(c)</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Links may direct to unofficial sources where o...</td>\n",
              "      <td>2018 legislative session and all 2018 regulato...</td>\n",
              "      <td>Federal</td>\n",
              "      <td>Employment &amp; volunteering</td>\n",
              "      <td>Banking, lending, securities &amp; finance</td>\n",
              "      <td>Crimes involving fraud, dishonesty, misreprese...</td>\n",
              "      <td>Discretionary (waiver)</td>\n",
              "      <td>Indefinite</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>15 USCS § 650</td>\n",
              "      <td>http://uscode.house.gov/view.xhtml?req=granule...</td>\n",
              "      <td>Discharge small business lender management off...</td>\n",
              "      <td>1</td>\n",
              "      <td>(g)(4); (f)</td>\n",
              "      <td>NaN</td>\n",
              "      <td>Links may direct to unofficial sources where o...</td>\n",
              "      <td>2018 legislative session and all 2018 regulato...</td>\n",
              "      <td>Federal</td>\n",
              "      <td>Employment &amp; volunteering</td>\n",
              "      <td>Banking, lending, securities &amp; finance</td>\n",
              "      <td>Crimes involving fraud, dishonesty, misreprese...</td>\n",
              "      <td>Discretionary</td>\n",
              "      <td>Indefinite</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ca44e0b0-8601-4468-84e1-ad4698a6716e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-ca44e0b0-8601-4468-84e1-ad4698a6716e button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-ca44e0b0-8601-4468-84e1-ad4698a6716e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Create .CSV File"
      ],
      "metadata": {
        "id": "3Nx2atfGDwP1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_full.to_csv('Full_Data_NICCC.csv') #Save the file (it will save in the directory set above)\n",
        "!cp Full_Data_NICCC.csv \"/content/drive/MyDrive\" #This command copies the file to a specific folder, this should be the location in which the file will be held for the dashboard"
      ],
      "metadata": {
        "id": "B-GfZ6VLky5l"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The .csv that is stored has to be converted to a google sheets file which is done in Google Sheets."
      ],
      "metadata": {
        "id": "0A6iX6TnBOUY"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Converting .csv to Google Sheets\n",
        "\n",
        "The next portion of the project has to do with opening the .csv into Google Sheets.  The version of Google Data Studio that was used had a malfunctioning connector in which .csv files could not be uploaded to GDS directly.  This was worked around by first opening up the .csv into Google Sheets.\n",
        "\n",
        "\n",
        "- Google Sheets: https://docs.google.com/spreadsheets\n",
        "- Google Data Studio (RE Dashboard): https://datastudio.google.com/"
      ],
      "metadata": {
        "id": "aK0utaffeMUV"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Creating a Google Data Studio Dashboard"
      ],
      "metadata": {
        "id": "gWu0bfrsfQ5r"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Adding Data to GDS"
      ],
      "metadata": {
        "id": "Wtn1iCf4gHHi"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "1. Click on the data studio link above. \n",
        "2. Click Blank Report. A half page will appear with options to upload data. \n",
        "3. Click on the Google Sheets Connector and what appears are all the Google Sheets associated with that Google account. \n",
        "4. Select the Google Sheets version of the Full_Data_NICCC (this is the file that was converted from .csv to Google Sheets as described above).\n",
        "5. Select the appropraiate worksheet (The version created through the .csv should only have one worksheet with the same title as the file). \n",
        "6. Click Add\n"
      ],
      "metadata": {
        "id": "APy8-Uc_fWt0"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Creating Visualizations"
      ],
      "metadata": {
        "id": "ljz193hCgUCh"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "When the data has been aded, it will show a summary table of the data.  This can be removed. \n",
        "\n",
        "1. Click *Add a chart* and select the visulziation needed.\n",
        "2. Place the visualization on the dashbaord.\n",
        "3. Each visualization has it's own set up.  A more indepth explaination is found here at the provided link, or other online locations: https://blog.hubspot.com/marketing/google-data-studio \n"
      ],
      "metadata": {
        "id": "H-JqD8Q6gWN6"
      }
    }
  ]
}