Chicago_Business_PreProcess.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Chicago-Business-PreProcess.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "source": [
        "!pip install sodapy"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "L4oysiLu376D",
        "outputId": "1a1f0a98-37be-4315-d6a2-3592acd39793"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting sodapy\n",
            "  Downloading sodapy-2.1.0-py2.py3-none-any.whl (14 kB)\n",
            "Requirement already satisfied: requests>=2.20.0 in /usr/local/lib/python3.7/dist-packages (from sodapy) (2.23.0)\n",
            "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests>=2.20.0->sodapy) (1.24.3)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests>=2.20.0->sodapy) (2.10)\n",
            "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests>=2.20.0->sodapy) (3.0.4)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.7/dist-packages (from requests>=2.20.0->sodapy) (2021.10.8)\n",
            "Installing collected packages: sodapy\n",
            "Successfully installed sodapy-2.1.0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "71ajq2FWHE45",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "02b7a5b5-9373-440f-fe9f-783d242fbcde"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n"
          ]
        }
      ],
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "from sodapy import Socrata\n",
        "import  datetime as dt\n",
        "dest = '/content/drive/MyDrive/Python/Chicago-Business/'\n",
        "client = Socrata(\"data.cityofchicago.org\", None)\n",
        "results = client.get_all(\"r5kz-chrr\",where=\"date_extract_y(date_issued) > 2009\")\n",
        "results_df = pd.DataFrame.from_records(results)\n",
        "# transform to dataframe\n",
        "results_df['date_issued'] = pd.to_datetime(results_df['date_issued'])\n",
        "results_df['YearIssue'] = pd.DatetimeIndex(results_df['date_issued']).year"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "IssueDf = results_df[results_df['application_type']=='ISSUE']\n",
        "IssueDf = IssueDf.drop(columns=['legal_name','doing_business_as_name','address','application_type','business_activity_id', 'business_activity'])\n",
        "IssueDf['Month'] = IssueDf['date_issued'].dt.to_period('M')\n",
        "IssueMon = pd.DataFrame(IssueDf.groupby('Month')['date_issued'].count())\n",
        "IssueMon = IssueMon.rename(columns={\"date_issued\": \"Count_License\"})"
      ],
      "metadata": {
        "id": "P6Cb5NlrIYCW"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#=========================#\n",
        "#-- Incentive programms --#\n",
        "#=========================#\n",
        "# the incentives file is not big so just download it using the link in appendices\n",
        "SBIF = pd.read_csv(dest + 'Incentive_SBIF.csv')\n",
        "SBIF['APPROVAL DATE'] = pd.to_datetime(SBIF['APPROVAL DATE'])\n",
        "SBIF['YearApproval'] = pd.DatetimeIndex(SBIF['APPROVAL DATE']).year\n",
        "SBIF['Month'] = SBIF['APPROVAL DATE'].dt.to_period('M')\n",
        "SBIFMon = pd.DataFrame(SBIF.groupby('Month')['INCENTIVE AMOUNT'].sum())\n",
        "SBIFMon = SBIFMon.rename(columns={\"INCENTIVE AMOUNT\": \"Sum_Incentive\"})\n",
        "SBIFMon=SBIFMon.reset_index()"
      ],
      "metadata": {
        "id": "cK6MT7sPIYE2"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#=========================#\n",
        "#-- economic indicators --#\n",
        "#=========================#\n",
        "# download the files using the links in appendices\n",
        "HPI = pd.read_csv(dest + 'CHXRSA.csv') # S&P/Case-Shiller Home Price Index\n",
        "HPI['Month'] = pd.to_datetime(HPI['DATE']).dt.to_period('M')\n",
        "HPI = HPI.drop(columns=['DATE'])\n",
        "Emp = pd.read_csv(dest + 'CHIC917URN.csv') # Unemployment Rate\n",
        "Emp['Month'] = pd.to_datetime(Emp['DATE']).dt.to_period('M')\n",
        "Emp = Emp.drop(columns=['DATE'])\n",
        "ElecPri = pd.read_csv(dest + 'APUS23A72610.csv') # Average Price: Electricity\n",
        "ElecPri['Month'] = pd.to_datetime(ElecPri['DATE']).dt.to_period('M')\n",
        "ElecPri = ElecPri.drop(columns=['DATE'])"
      ],
      "metadata": {
        "id": "YNB4rsO0JNFf"
      },
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#===============#\n",
        "#-- merge all --#\n",
        "#===============#\n",
        "FullDat = IssueMon.merge(SBIFMon,how='left',on='Month')\n",
        "FullDat = FullDat.merge(HPI,how='left',on='Month')\n",
        "FullDat = FullDat.merge(Emp,how='left',on='Month')\n",
        "FullDat = FullDat.merge(ElecPri,how='left',on='Month')\n",
        "# FullDat.to_csv(dest + 'FullDat.csv')"
      ],
      "metadata": {
        "id": "7mbw_U6bJsom"
      },
      "execution_count": 24,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#=======================#\n",
        "#-- log transformation--#\n",
        "#=======================#\n",
        "FullDat = FullDat[FullDat['Month']<'2021-09']\n",
        "# log transformation\n",
        "FullDat['Log_Count_License'] = np.log(FullDat['Count_License'])\n",
        "FullDat['Log_Sum_Incentive'] = np.log(FullDat['Sum_Incentive'])\n",
        "FullDat['Log_HPI'] = np.log(FullDat['CHXRSA'])\n",
        "FullDat['Log_Emp'] = np.log(FullDat['CHIC917URN'])\n",
        "FullDat['APUS23A72610'] = FullDat['APUS23A72610'].astype(float)\n",
        "FullDat['Log_Elec'] = np.log(FullDat['APUS23A72610'])\n",
        "FullDat = FullDat.drop(columns=['Count_License','Sum_Incentive','CHXRSA','CHIC917URN','APUS23A72610'])"
      ],
      "metadata": {
        "id": "VopiKE7SJ_5N"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# replace empty incentive cells in 2020 with 0's\n",
        "FullDat = FullDat.replace(np.nan, 0)\n",
        "# calculate lags at t-3\n",
        "FullDat['Log_Sum_Incentive_lag3']=FullDat['Log_Sum_Incentive'].shift(3)\n",
        "FullDat['Log_HPI_lag3']=FullDat['Log_HPI'].shift(3)\n",
        "FullDat['Log_Emp_lag3']=FullDat['Log_Emp'].shift(3)\n",
        "FullDat['Log_Elec_lag3']=FullDat['Log_Elec'].shift(3)\n",
        "# calculate lags at t-6\n",
        "FullDat['Log_Sum_Incentive_lag6']=FullDat['Log_Sum_Incentive'].shift(6)\n",
        "FullDat['Log_HPI_lag6']=FullDat['Log_HPI'].shift(6)\n",
        "FullDat['Log_Emp_lag6']=FullDat['Log_Emp'].shift(6)\n",
        "FullDat['Log_Elec_lag6']=FullDat['Log_Elec'].shift(6)\n",
        "FullDat.to_csv(dest + 'FullDat-2.csv',index=False)"
      ],
      "metadata": {
        "id": "Wv2jgp4bJzc2"
      },
      "execution_count": 26,
      "outputs": []
    }
  ]
}