PivotYear.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "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>Version Year</th>\n",
       "      <th>IUSA Number</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP Code</th>\n",
       "      <th>Primary SIC Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>43-355-8116</td>\n",
       "      <td>KAYA</td>\n",
       "      <td>2000 Smallman St # 9</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>43-355-8116</td>\n",
       "      <td>KAYA</td>\n",
       "      <td>2000 Smallman St # 9</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019</td>\n",
       "      <td>43-355-8116</td>\n",
       "      <td>KAYA</td>\n",
       "      <td>2000 Smallman St # 9</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019</td>\n",
       "      <td>40-420-8198</td>\n",
       "      <td>Kayla's Cafe</td>\n",
       "      <td>307 North Ave</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15209</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021</td>\n",
       "      <td>73-208-5301</td>\n",
       "      <td>KAZI Automotive</td>\n",
       "      <td>625 W Warrington Ave</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15226</td>\n",
       "      <td>Automobile Repairing &amp; Service</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6752</th>\n",
       "      <td>2019</td>\n",
       "      <td>72-191-3062</td>\n",
       "      <td>LA Paix Alpacas</td>\n",
       "      <td>106 Piper Dr</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15234</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6753</th>\n",
       "      <td>2021</td>\n",
       "      <td>72-271-6499</td>\n",
       "      <td>LA Prima Espresso Co</td>\n",
       "      <td>100 S Commons</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15212</td>\n",
       "      <td>Coffee Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6754</th>\n",
       "      <td>2021</td>\n",
       "      <td>74-268-9713</td>\n",
       "      <td>LA Prima Espresso Co</td>\n",
       "      <td>1100 Smallman St</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Coffee Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6755</th>\n",
       "      <td>2020</td>\n",
       "      <td>72-271-6499</td>\n",
       "      <td>LA Prima Espresso Co</td>\n",
       "      <td>100 S Commons</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15212</td>\n",
       "      <td>Coffee Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6756</th>\n",
       "      <td>2019</td>\n",
       "      <td>72-271-6499</td>\n",
       "      <td>LA Prima Espresso Co</td>\n",
       "      <td>100 S Commons</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15212</td>\n",
       "      <td>Coffee Shops</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6757 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Version Year  IUSA Number          Company Name               Address  \\\n",
       "0            2020  43-355-8116                  KAYA  2000 Smallman St # 9   \n",
       "1            2021  43-355-8116                  KAYA  2000 Smallman St # 9   \n",
       "2            2019  43-355-8116                  KAYA  2000 Smallman St # 9   \n",
       "3            2019  40-420-8198          Kayla's Cafe         307 North Ave   \n",
       "4            2021  73-208-5301       KAZI Automotive  625 W Warrington Ave   \n",
       "...           ...          ...                   ...                   ...   \n",
       "6752         2019  72-191-3062       LA Paix Alpacas          106 Piper Dr   \n",
       "6753         2021  72-271-6499  LA Prima Espresso Co         100 S Commons   \n",
       "6754         2021  74-268-9713  LA Prima Espresso Co      1100 Smallman St   \n",
       "6755         2020  72-271-6499  LA Prima Espresso Co         100 S Commons   \n",
       "6756         2019  72-271-6499  LA Prima Espresso Co         100 S Commons   \n",
       "\n",
       "            City State ZIP Code         Primary SIC Description  \n",
       "0     Pittsburgh    PA    15222                     Restaurants  \n",
       "1     Pittsburgh    PA    15222                     Restaurants  \n",
       "2     Pittsburgh    PA    15222                     Restaurants  \n",
       "3     Pittsburgh    PA    15209                     Restaurants  \n",
       "4     Pittsburgh    PA    15226  Automobile Repairing & Service  \n",
       "...          ...   ...      ...                             ...  \n",
       "6752  Pittsburgh    PA    15234                     Restaurants  \n",
       "6753  Pittsburgh    PA    15212                    Coffee Shops  \n",
       "6754  Pittsburgh    PA    15222                    Coffee Shops  \n",
       "6755  Pittsburgh    PA    15212                    Coffee Shops  \n",
       "6756  Pittsburgh    PA    15212                    Coffee Shops  \n",
       "\n",
       "[6757 rows x 8 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import winsound\n",
    "\n",
    "# Importing data\n",
    "df = pd.read_csv(\"PghRestaurantAllYear.csv\",dtype=str)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "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>Version Year</th>\n",
       "      <th>IUSA Number</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP Code</th>\n",
       "      <th>Primary SIC Description</th>\n",
       "      <th>Y2019</th>\n",
       "      <th>Y2020</th>\n",
       "      <th>Y2021</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>43-355-8116</td>\n",
       "      <td>KAYA</td>\n",
       "      <td>2000 Smallman St # 9</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Restaurants</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021</td>\n",
       "      <td>43-355-8116</td>\n",
       "      <td>KAYA</td>\n",
       "      <td>2000 Smallman St # 9</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Restaurants</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019</td>\n",
       "      <td>43-355-8116</td>\n",
       "      <td>KAYA</td>\n",
       "      <td>2000 Smallman St # 9</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Restaurants</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019</td>\n",
       "      <td>40-420-8198</td>\n",
       "      <td>Kayla's Cafe</td>\n",
       "      <td>307 North Ave</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15209</td>\n",
       "      <td>Restaurants</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021</td>\n",
       "      <td>73-208-5301</td>\n",
       "      <td>KAZI Automotive</td>\n",
       "      <td>625 W Warrington Ave</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15226</td>\n",
       "      <td>Automobile Repairing &amp; Service</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6752</th>\n",
       "      <td>2019</td>\n",
       "      <td>72-191-3062</td>\n",
       "      <td>LA Paix Alpacas</td>\n",
       "      <td>106 Piper Dr</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15234</td>\n",
       "      <td>Restaurants</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6753</th>\n",
       "      <td>2021</td>\n",
       "      <td>72-271-6499</td>\n",
       "      <td>LA Prima Espresso Co</td>\n",
       "      <td>100 S Commons</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15212</td>\n",
       "      <td>Coffee Shops</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6754</th>\n",
       "      <td>2021</td>\n",
       "      <td>74-268-9713</td>\n",
       "      <td>LA Prima Espresso Co</td>\n",
       "      <td>1100 Smallman St</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Coffee Shops</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6755</th>\n",
       "      <td>2020</td>\n",
       "      <td>72-271-6499</td>\n",
       "      <td>LA Prima Espresso Co</td>\n",
       "      <td>100 S Commons</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15212</td>\n",
       "      <td>Coffee Shops</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6756</th>\n",
       "      <td>2019</td>\n",
       "      <td>72-271-6499</td>\n",
       "      <td>LA Prima Espresso Co</td>\n",
       "      <td>100 S Commons</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15212</td>\n",
       "      <td>Coffee Shops</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6757 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Version Year  IUSA Number          Company Name               Address  \\\n",
       "0            2020  43-355-8116                  KAYA  2000 Smallman St # 9   \n",
       "1            2021  43-355-8116                  KAYA  2000 Smallman St # 9   \n",
       "2            2019  43-355-8116                  KAYA  2000 Smallman St # 9   \n",
       "3            2019  40-420-8198          Kayla's Cafe         307 North Ave   \n",
       "4            2021  73-208-5301       KAZI Automotive  625 W Warrington Ave   \n",
       "...           ...          ...                   ...                   ...   \n",
       "6752         2019  72-191-3062       LA Paix Alpacas          106 Piper Dr   \n",
       "6753         2021  72-271-6499  LA Prima Espresso Co         100 S Commons   \n",
       "6754         2021  74-268-9713  LA Prima Espresso Co      1100 Smallman St   \n",
       "6755         2020  72-271-6499  LA Prima Espresso Co         100 S Commons   \n",
       "6756         2019  72-271-6499  LA Prima Espresso Co         100 S Commons   \n",
       "\n",
       "            City State ZIP Code         Primary SIC Description  Y2019  Y2020  \\\n",
       "0     Pittsburgh    PA    15222                     Restaurants      0      1   \n",
       "1     Pittsburgh    PA    15222                     Restaurants      0      0   \n",
       "2     Pittsburgh    PA    15222                     Restaurants      1      0   \n",
       "3     Pittsburgh    PA    15209                     Restaurants      1      0   \n",
       "4     Pittsburgh    PA    15226  Automobile Repairing & Service      0      0   \n",
       "...          ...   ...      ...                             ...    ...    ...   \n",
       "6752  Pittsburgh    PA    15234                     Restaurants      1      0   \n",
       "6753  Pittsburgh    PA    15212                    Coffee Shops      0      0   \n",
       "6754  Pittsburgh    PA    15222                    Coffee Shops      0      0   \n",
       "6755  Pittsburgh    PA    15212                    Coffee Shops      0      1   \n",
       "6756  Pittsburgh    PA    15212                    Coffee Shops      1      0   \n",
       "\n",
       "      Y2021  \n",
       "0         0  \n",
       "1         1  \n",
       "2         0  \n",
       "3         0  \n",
       "4         1  \n",
       "...     ...  \n",
       "6752      0  \n",
       "6753      1  \n",
       "6754      1  \n",
       "6755      0  \n",
       "6756      0  \n",
       "\n",
       "[6757 rows x 11 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create field\n",
    "df['Y2019'] = np.where(df['Version Year'] == '2019', 1, 0)\n",
    "df['Y2020'] = np.where(df['Version Year'] == '2020', 1, 0)\n",
    "df['Y2021'] = np.where(df['Version Year'] == '2021', 1, 0)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "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>IUSA Number</th>\n",
       "      <th>Y2019</th>\n",
       "      <th>Y2020</th>\n",
       "      <th>Y2021</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00-268-7606</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00-326-1245</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00-866-7537</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00-929-3945</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11-815-7221</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2492</th>\n",
       "      <td>98-137-6593</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2493</th>\n",
       "      <td>98-137-6833</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2494</th>\n",
       "      <td>98-241-1282</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2495</th>\n",
       "      <td>98-241-1456</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2496</th>\n",
       "      <td>98-922-0892</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2497 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      IUSA Number  Y2019  Y2020  Y2021\n",
       "0     00-268-7606      1      1      1\n",
       "1     00-326-1245      1      1      1\n",
       "2     00-866-7537      1      1      1\n",
       "3     00-929-3945      1      1      1\n",
       "4     11-815-7221      1      1      1\n",
       "...           ...    ...    ...    ...\n",
       "2492  98-137-6593      1      1      1\n",
       "2493  98-137-6833      1      1      1\n",
       "2494  98-241-1282      1      1      1\n",
       "2495  98-241-1456      1      1      1\n",
       "2496  98-922-0892      1      1      1\n",
       "\n",
       "[2497 rows x 4 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.groupby([\"IUSA Number\"]).sum().reset_index()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export\n",
    "df.to_csv(\"IUSAYear\" + \".csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "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>IUSA Number</th>\n",
       "      <th>Y2019</th>\n",
       "      <th>Y2020</th>\n",
       "      <th>Y2021</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP Code</th>\n",
       "      <th>Primary SIC Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00-268-7606</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Nicholas Coffee</td>\n",
       "      <td>23 Market Sq # 1</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Coffee &amp; Tea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00-326-1245</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Grant Bar Inc</td>\n",
       "      <td>114 Grant Ave</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15209</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00-866-7537</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ivy Restaurant</td>\n",
       "      <td>360 Castle Shannon Blvd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15234</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00-929-3945</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Schneider's Dairy Inc</td>\n",
       "      <td>726 Frank St</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15227</td>\n",
       "      <td>Dairy Products-Wholesale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11-815-7221</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Delallo's Restaurant-Catering</td>\n",
       "      <td>91 Fort Couch Rd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15241</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2492</th>\n",
       "      <td>98-137-6593</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Starbucks</td>\n",
       "      <td>730 Copeland St # 1</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15232</td>\n",
       "      <td>Coffee Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2493</th>\n",
       "      <td>98-137-6833</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Residence Inn</td>\n",
       "      <td>1500 Park Ln</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15275</td>\n",
       "      <td>Hotels &amp; Motels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2494</th>\n",
       "      <td>98-241-1282</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Vocelli Pizza</td>\n",
       "      <td>691 Clairton Blvd</td>\n",
       "      <td>Pleasant Hills</td>\n",
       "      <td>PA</td>\n",
       "      <td>15236</td>\n",
       "      <td>Pizza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2495</th>\n",
       "      <td>98-241-1456</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Buffalo Wild Wings Grill &amp; Bar</td>\n",
       "      <td>480 Home Dr</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15275</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2496</th>\n",
       "      <td>98-922-0892</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Giant Eagle</td>\n",
       "      <td>132 Ben Avon Heights Rd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15237</td>\n",
       "      <td>Grocers-Retail</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2497 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      IUSA Number  Y2019  Y2020  Y2021                    Company Name  \\\n",
       "0     00-268-7606      1      1      1                 Nicholas Coffee   \n",
       "1     00-326-1245      1      1      1                   Grant Bar Inc   \n",
       "2     00-866-7537      1      1      1                  Ivy Restaurant   \n",
       "3     00-929-3945      1      1      1           Schneider's Dairy Inc   \n",
       "4     11-815-7221      1      1      1   Delallo's Restaurant-Catering   \n",
       "...           ...    ...    ...    ...                             ...   \n",
       "2492  98-137-6593      1      1      1                       Starbucks   \n",
       "2493  98-137-6833      1      1      1                   Residence Inn   \n",
       "2494  98-241-1282      1      1      1                   Vocelli Pizza   \n",
       "2495  98-241-1456      1      1      1  Buffalo Wild Wings Grill & Bar   \n",
       "2496  98-922-0892      1      1      1                     Giant Eagle   \n",
       "\n",
       "                      Address            City State ZIP Code  \\\n",
       "0            23 Market Sq # 1      Pittsburgh    PA    15222   \n",
       "1               114 Grant Ave      Pittsburgh    PA    15209   \n",
       "2     360 Castle Shannon Blvd      Pittsburgh    PA    15234   \n",
       "3                726 Frank St      Pittsburgh    PA    15227   \n",
       "4            91 Fort Couch Rd      Pittsburgh    PA    15241   \n",
       "...                       ...             ...   ...      ...   \n",
       "2492      730 Copeland St # 1      Pittsburgh    PA    15232   \n",
       "2493             1500 Park Ln      Pittsburgh    PA    15275   \n",
       "2494        691 Clairton Blvd  Pleasant Hills    PA    15236   \n",
       "2495              480 Home Dr      Pittsburgh    PA    15275   \n",
       "2496  132 Ben Avon Heights Rd      Pittsburgh    PA    15237   \n",
       "\n",
       "       Primary SIC Description  \n",
       "0                 Coffee & Tea  \n",
       "1                  Restaurants  \n",
       "2                  Restaurants  \n",
       "3     Dairy Products-Wholesale  \n",
       "4                  Restaurants  \n",
       "...                        ...  \n",
       "2492              Coffee Shops  \n",
       "2493           Hotels & Motels  \n",
       "2494                     Pizza  \n",
       "2495               Restaurants  \n",
       "2496            Grocers-Retail  \n",
       "\n",
       "[2497 rows x 10 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rest = pd.read_csv(\"PghRestaurantAllYear.csv\",dtype=str)\n",
    "df_rest = df_rest.drop(columns=['Version Year'])\n",
    "df_rest = df_rest.drop_duplicates(subset='IUSA Number', keep='last')\n",
    "#Join\n",
    "df_join = pd.merge(df, df_rest, on='IUSA Number', how='left')\n",
    "df_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Coffee & Tea', 'Restaurants', 'Dairy Products-Wholesale',\n",
       "       'Hotels & Motels', 'Pizza', 'Ice Cream Parlors', 'Grocers-Retail',\n",
       "       'Bars', 'Bowling Centers', nan, 'Convenience Stores',\n",
       "       'Candy & Confectionery-Manufacturers', 'Museums', 'Churches',\n",
       "       'Caterers', 'Non-Profit Organizations', 'Golf Courses',\n",
       "       'Foods-Carry Out', 'Bakers-Retail',\n",
       "       'Convention & Meeting Facilities & Svc', 'Coffee Shops',\n",
       "       'Juice Bars', 'Cocktail Lounges',\n",
       "       'Ice Cream & Frozen Desserts-Distr (Whls)', 'Doors-Repairing',\n",
       "       'Pretzels-Retail', 'Candy & Confectionery-Retail',\n",
       "       'Grocers-Ethnic Foods', 'Restaurant Management', 'Brewers (Mfrs)',\n",
       "       'Wholesale Clubs', 'Food Products & Manufacturers', 'Farm Markets',\n",
       "       'Seafood-Retail', 'Ham Specialty Stores', 'Doughnuts',\n",
       "       'Book Dealers-Retail', 'Bags-Paper Plastic & Fabric Suppliers',\n",
       "       'Physicians & Surgeons Equip & Supls-Whls',\n",
       "       'Home Demonstration-Merchandise', 'Gift Shops', 'Fire Departments',\n",
       "       'Bagels', 'Food Service-Distributors (Whls)', 'Night Clubs',\n",
       "       'Clubs', 'Golf Courses-Miniature', 'Pubs', 'Bartending Service',\n",
       "       'Chinese Food Products', 'Theatres-Live', 'Animal Hospitals',\n",
       "       'Real Estate', 'Nonclassified Establishments', 'Arcades',\n",
       "       'Florists-Wholesale', 'Bakers-Wholesale', 'Delivery Service',\n",
       "       'Party Centers', 'Poultry Processing Plants (Mfrs)',\n",
       "       'Skating Rinks', 'Music & Live Entertainment',\n",
       "       'Restaurant Equipment & Supplies (Whls)',\n",
       "       'Business Management Consultants',\n",
       "       'Laundry & Garment Services NEC', 'Wedding Supplies & Services',\n",
       "       'Social Service & Welfare Organizations', 'Inns',\n",
       "       'Arts Organizations & Information', 'Liquors-Retail',\n",
       "       'Department Stores', 'Halls & Auditoriums', 'Casinos',\n",
       "       'Canned & Cured Fish & Seafoods (Mfrs)', 'Attorneys', 'Yogurt',\n",
       "       'Chemical Research', 'Resorts', 'Smoke Shops & Supplies',\n",
       "       'Home Builders', 'Miscellaneous Retail Stores NEC',\n",
       "       'Wineries (Mfrs)', 'Pet Washing & Grooming',\n",
       "       'General Merchandise-Retail', 'Garbage Collection',\n",
       "       'Furniture-Dealers-Retail', 'Website Design Service',\n",
       "       'Video Games', 'Kitchen Cabinets & Equipment-Household',\n",
       "       'Retail Shops', 'Fruit Baskets-Gift', 'Juices-Retail', 'Crafts',\n",
       "       'Eggs-Retail', 'Mailing & Shipping Services',\n",
       "       'Motels & Hotels Reservations', 'Entertainers'], dtype=object)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# list all unique Primary SIC Description\n",
    "df_join['Primary SIC Description'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "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>IUSA Number</th>\n",
       "      <th>Y2019</th>\n",
       "      <th>Y2020</th>\n",
       "      <th>Y2021</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP Code</th>\n",
       "      <th>Primary SIC Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00-268-7606</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Nicholas Coffee</td>\n",
       "      <td>23 Market Sq # 1</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Coffee &amp; Tea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00-326-1245</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Grant Bar Inc</td>\n",
       "      <td>114 Grant Ave</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15209</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00-866-7537</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ivy Restaurant</td>\n",
       "      <td>360 Castle Shannon Blvd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15234</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00-929-3945</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Schneider's Dairy Inc</td>\n",
       "      <td>726 Frank St</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15227</td>\n",
       "      <td>Dairy Products-Wholesale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11-815-7221</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Delallo's Restaurant-Catering</td>\n",
       "      <td>91 Fort Couch Rd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15241</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2492</th>\n",
       "      <td>98-137-6593</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Starbucks</td>\n",
       "      <td>730 Copeland St # 1</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15232</td>\n",
       "      <td>Coffee Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2493</th>\n",
       "      <td>98-137-6833</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Residence Inn</td>\n",
       "      <td>1500 Park Ln</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15275</td>\n",
       "      <td>Hotels &amp; Motels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2494</th>\n",
       "      <td>98-241-1282</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Vocelli Pizza</td>\n",
       "      <td>691 Clairton Blvd</td>\n",
       "      <td>Pleasant Hills</td>\n",
       "      <td>PA</td>\n",
       "      <td>15236</td>\n",
       "      <td>Pizza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2495</th>\n",
       "      <td>98-241-1456</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Buffalo Wild Wings Grill &amp; Bar</td>\n",
       "      <td>480 Home Dr</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15275</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2496</th>\n",
       "      <td>98-922-0892</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Giant Eagle</td>\n",
       "      <td>132 Ben Avon Heights Rd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15237</td>\n",
       "      <td>Grocers-Retail</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2467 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      IUSA Number  Y2019  Y2020  Y2021                    Company Name  \\\n",
       "0     00-268-7606      1      1      1                 Nicholas Coffee   \n",
       "1     00-326-1245      1      1      1                   Grant Bar Inc   \n",
       "2     00-866-7537      1      1      1                  Ivy Restaurant   \n",
       "3     00-929-3945      1      1      1           Schneider's Dairy Inc   \n",
       "4     11-815-7221      1      1      1   Delallo's Restaurant-Catering   \n",
       "...           ...    ...    ...    ...                             ...   \n",
       "2492  98-137-6593      1      1      1                       Starbucks   \n",
       "2493  98-137-6833      1      1      1                   Residence Inn   \n",
       "2494  98-241-1282      1      1      1                   Vocelli Pizza   \n",
       "2495  98-241-1456      1      1      1  Buffalo Wild Wings Grill & Bar   \n",
       "2496  98-922-0892      1      1      1                     Giant Eagle   \n",
       "\n",
       "                      Address            City State ZIP Code  \\\n",
       "0            23 Market Sq # 1      Pittsburgh    PA    15222   \n",
       "1               114 Grant Ave      Pittsburgh    PA    15209   \n",
       "2     360 Castle Shannon Blvd      Pittsburgh    PA    15234   \n",
       "3                726 Frank St      Pittsburgh    PA    15227   \n",
       "4            91 Fort Couch Rd      Pittsburgh    PA    15241   \n",
       "...                       ...             ...   ...      ...   \n",
       "2492      730 Copeland St # 1      Pittsburgh    PA    15232   \n",
       "2493             1500 Park Ln      Pittsburgh    PA    15275   \n",
       "2494        691 Clairton Blvd  Pleasant Hills    PA    15236   \n",
       "2495              480 Home Dr      Pittsburgh    PA    15275   \n",
       "2496  132 Ben Avon Heights Rd      Pittsburgh    PA    15237   \n",
       "\n",
       "       Primary SIC Description  \n",
       "0                 Coffee & Tea  \n",
       "1                  Restaurants  \n",
       "2                  Restaurants  \n",
       "3     Dairy Products-Wholesale  \n",
       "4                  Restaurants  \n",
       "...                        ...  \n",
       "2492              Coffee Shops  \n",
       "2493           Hotels & Motels  \n",
       "2494                     Pizza  \n",
       "2495               Restaurants  \n",
       "2496            Grocers-Retail  \n",
       "\n",
       "[2467 rows x 10 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ls = ['Museums','Churches','Golf Courses','Convention & Meeting Facilities & Svc','Doors-Repairing','Book Dealers-Retail', 'Bags-Paper Plastic & Fabric Suppliers','Physicians & Surgeons Equip & Supls-Whls','Home Demonstration-Merchandise', 'Gift Shops', 'Fire Departments', 'Golf Courses-Miniature', 'Theatres-Live', 'Animal Hospitals','Real Estate','Business Management Consultants','Furniture-Dealers-Retail', 'Website Design Service','Video Games', 'Kitchen Cabinets & Equipment-Household', 'Crafts', 'Mailing & Shipping Services']\n",
    "df_join = df_join[~df_join['Primary SIC Description'].isin(ls)]\n",
    "df_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Coffee & Tea', 'Restaurants', 'Dairy Products-Wholesale',\n",
       "       'Hotels & Motels', 'Pizza', 'Ice Cream Parlors', 'Grocers-Retail',\n",
       "       'Bars', 'Bowling Centers', nan, 'Convenience Stores',\n",
       "       'Candy & Confectionery-Manufacturers', 'Caterers',\n",
       "       'Non-Profit Organizations', 'Foods-Carry Out', 'Bakers-Retail',\n",
       "       'Coffee Shops', 'Juice Bars', 'Cocktail Lounges',\n",
       "       'Ice Cream & Frozen Desserts-Distr (Whls)', 'Pretzels-Retail',\n",
       "       'Candy & Confectionery-Retail', 'Grocers-Ethnic Foods',\n",
       "       'Restaurant Management', 'Brewers (Mfrs)', 'Wholesale Clubs',\n",
       "       'Food Products & Manufacturers', 'Farm Markets', 'Seafood-Retail',\n",
       "       'Ham Specialty Stores', 'Doughnuts', 'Bagels',\n",
       "       'Food Service-Distributors (Whls)', 'Night Clubs', 'Clubs', 'Pubs',\n",
       "       'Bartending Service', 'Chinese Food Products',\n",
       "       'Nonclassified Establishments', 'Arcades', 'Florists-Wholesale',\n",
       "       'Bakers-Wholesale', 'Delivery Service', 'Party Centers',\n",
       "       'Poultry Processing Plants (Mfrs)', 'Skating Rinks',\n",
       "       'Music & Live Entertainment',\n",
       "       'Restaurant Equipment & Supplies (Whls)',\n",
       "       'Laundry & Garment Services NEC', 'Wedding Supplies & Services',\n",
       "       'Social Service & Welfare Organizations', 'Inns',\n",
       "       'Arts Organizations & Information', 'Liquors-Retail',\n",
       "       'Department Stores', 'Halls & Auditoriums', 'Casinos',\n",
       "       'Canned & Cured Fish & Seafoods (Mfrs)', 'Attorneys', 'Yogurt',\n",
       "       'Chemical Research', 'Resorts', 'Smoke Shops & Supplies',\n",
       "       'Home Builders', 'Miscellaneous Retail Stores NEC',\n",
       "       'Wineries (Mfrs)', 'Pet Washing & Grooming',\n",
       "       'General Merchandise-Retail', 'Garbage Collection', 'Retail Shops',\n",
       "       'Fruit Baskets-Gift', 'Juices-Retail', 'Eggs-Retail',\n",
       "       'Motels & Hotels Reservations', 'Entertainers'], dtype=object)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# list all unique Primary SIC Description ROUND 2\n",
    "df_join['Primary SIC Description'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "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>IUSA Number</th>\n",
       "      <th>Y2019</th>\n",
       "      <th>Y2020</th>\n",
       "      <th>Y2021</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP Code</th>\n",
       "      <th>Primary SIC Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00-268-7606</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Nicholas Coffee</td>\n",
       "      <td>23 Market Sq # 1</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Coffee &amp; Tea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00-326-1245</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Grant Bar Inc</td>\n",
       "      <td>114 Grant Ave</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15209</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00-866-7537</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ivy Restaurant</td>\n",
       "      <td>360 Castle Shannon Blvd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15234</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11-815-7221</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Delallo's Restaurant-Catering</td>\n",
       "      <td>91 Fort Couch Rd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15241</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>11-819-6567</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Pizza Hut</td>\n",
       "      <td>271 1/2 Clairton Blvd # 1-2</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15236</td>\n",
       "      <td>Pizza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2488</th>\n",
       "      <td>97-512-2219</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Quaker Steak &amp; Lube</td>\n",
       "      <td>110 Andrew Dr</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15275</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2490</th>\n",
       "      <td>98-137-6122</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Rita's Italian Ice/Frzn Cstrd</td>\n",
       "      <td>1320 Babcock Blvd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15209</td>\n",
       "      <td>Ice Cream Parlors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2492</th>\n",
       "      <td>98-137-6593</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Starbucks</td>\n",
       "      <td>730 Copeland St # 1</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15232</td>\n",
       "      <td>Coffee Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2494</th>\n",
       "      <td>98-241-1282</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Vocelli Pizza</td>\n",
       "      <td>691 Clairton Blvd</td>\n",
       "      <td>Pleasant Hills</td>\n",
       "      <td>PA</td>\n",
       "      <td>15236</td>\n",
       "      <td>Pizza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2495</th>\n",
       "      <td>98-241-1456</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Buffalo Wild Wings Grill &amp; Bar</td>\n",
       "      <td>480 Home Dr</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15275</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2247 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      IUSA Number  Y2019  Y2020  Y2021                    Company Name  \\\n",
       "0     00-268-7606      1      1      1                 Nicholas Coffee   \n",
       "1     00-326-1245      1      1      1                   Grant Bar Inc   \n",
       "2     00-866-7537      1      1      1                  Ivy Restaurant   \n",
       "4     11-815-7221      1      1      1   Delallo's Restaurant-Catering   \n",
       "6     11-819-6567      1      1      1                       Pizza Hut   \n",
       "...           ...    ...    ...    ...                             ...   \n",
       "2488  97-512-2219      1      1      1             Quaker Steak & Lube   \n",
       "2490  98-137-6122      1      1      1   Rita's Italian Ice/Frzn Cstrd   \n",
       "2492  98-137-6593      1      1      1                       Starbucks   \n",
       "2494  98-241-1282      1      1      1                   Vocelli Pizza   \n",
       "2495  98-241-1456      1      1      1  Buffalo Wild Wings Grill & Bar   \n",
       "\n",
       "                          Address            City State ZIP Code  \\\n",
       "0                23 Market Sq # 1      Pittsburgh    PA    15222   \n",
       "1                   114 Grant Ave      Pittsburgh    PA    15209   \n",
       "2         360 Castle Shannon Blvd      Pittsburgh    PA    15234   \n",
       "4                91 Fort Couch Rd      Pittsburgh    PA    15241   \n",
       "6     271 1/2 Clairton Blvd # 1-2      Pittsburgh    PA    15236   \n",
       "...                           ...             ...   ...      ...   \n",
       "2488                110 Andrew Dr      Pittsburgh    PA    15275   \n",
       "2490            1320 Babcock Blvd      Pittsburgh    PA    15209   \n",
       "2492          730 Copeland St # 1      Pittsburgh    PA    15232   \n",
       "2494            691 Clairton Blvd  Pleasant Hills    PA    15236   \n",
       "2495                  480 Home Dr      Pittsburgh    PA    15275   \n",
       "\n",
       "     Primary SIC Description  \n",
       "0               Coffee & Tea  \n",
       "1                Restaurants  \n",
       "2                Restaurants  \n",
       "4                Restaurants  \n",
       "6                      Pizza  \n",
       "...                      ...  \n",
       "2488             Restaurants  \n",
       "2490       Ice Cream Parlors  \n",
       "2492            Coffee Shops  \n",
       "2494                   Pizza  \n",
       "2495             Restaurants  \n",
       "\n",
       "[2247 rows x 10 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ls = ['Dairy Products-Wholesale','Hotels & Motels','Grocers-Retail','Bowling Centers','Convenience Stores','Candy & Confectionery-Manufacturers','Caterers','Non-Profit Organizations','Ice Cream & Frozen Desserts-Distr (Whls)','Restaurant Management','Brewers (Mfrs)','Wholesale Clubs','Food Products & Manufacturers', 'Farm Markets','Ham Specialty Stores','Food Service-Distributors (Whls)','Bartending Service','Florists-Wholesale','Bakers-Wholesale', 'Delivery Service','Poultry Processing Plants (Mfrs)', 'Skating Rinks','Music & Live Entertainment','Restaurant Equipment & Supplies (Whls)','Laundry & Garment Services NEC', 'Wedding Supplies & Services','Social Service & Welfare Organizations', 'Inns','Arts Organizations & Information', 'Liquors-Retail','Department Stores', 'Halls & Auditoriums', 'Casinos','Canned & Cured Fish & Seafoods (Mfrs)', 'Attorneys','Chemical Research', 'Resorts', 'Smoke Shops & Supplies','Home Builders', 'Miscellaneous Retail Stores NEC','Wineries (Mfrs)', 'Pet Washing & Grooming','General Merchandise-Retail', 'Garbage Collection', 'Retail Shops','Fruit Baskets-Gift','Motels & Hotels Reservations', 'Entertainers']\n",
    "df_join = df_join[~df_join['Primary SIC Description'].isin(ls)]\n",
    "df_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Coffee & Tea', 'Restaurants', 'Pizza', 'Ice Cream Parlors',\n",
       "       'Bars', nan, 'Foods-Carry Out', 'Bakers-Retail', 'Coffee Shops',\n",
       "       'Juice Bars', 'Cocktail Lounges', 'Pretzels-Retail',\n",
       "       'Candy & Confectionery-Retail', 'Grocers-Ethnic Foods',\n",
       "       'Seafood-Retail', 'Doughnuts', 'Bagels', 'Night Clubs', 'Clubs',\n",
       "       'Pubs', 'Chinese Food Products', 'Nonclassified Establishments',\n",
       "       'Arcades', 'Party Centers', 'Yogurt', 'Juices-Retail',\n",
       "       'Eggs-Retail'], dtype=object)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# list all unique Primary SIC Description ROUND 3\n",
    "df_join['Primary SIC Description'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "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>IUSA Number</th>\n",
       "      <th>Y2019</th>\n",
       "      <th>Y2020</th>\n",
       "      <th>Y2021</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP Code</th>\n",
       "      <th>Primary SIC Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00-268-7606</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Nicholas Coffee</td>\n",
       "      <td>23 Market Sq # 1</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15222</td>\n",
       "      <td>Coffee &amp; Tea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00-326-1245</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Grant Bar Inc</td>\n",
       "      <td>114 Grant Ave</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15209</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00-866-7537</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Ivy Restaurant</td>\n",
       "      <td>360 Castle Shannon Blvd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15234</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11-815-7221</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Delallo's Restaurant-Catering</td>\n",
       "      <td>91 Fort Couch Rd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15241</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>11-819-6567</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Pizza Hut</td>\n",
       "      <td>271 1/2 Clairton Blvd # 1-2</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15236</td>\n",
       "      <td>Pizza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2488</th>\n",
       "      <td>97-512-2219</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Quaker Steak &amp; Lube</td>\n",
       "      <td>110 Andrew Dr</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15275</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2490</th>\n",
       "      <td>98-137-6122</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Rita's Italian Ice/Frzn Cstrd</td>\n",
       "      <td>1320 Babcock Blvd</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15209</td>\n",
       "      <td>Ice Cream Parlors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2492</th>\n",
       "      <td>98-137-6593</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Starbucks</td>\n",
       "      <td>730 Copeland St # 1</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15232</td>\n",
       "      <td>Coffee Shops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2494</th>\n",
       "      <td>98-241-1282</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Vocelli Pizza</td>\n",
       "      <td>691 Clairton Blvd</td>\n",
       "      <td>Pleasant Hills</td>\n",
       "      <td>PA</td>\n",
       "      <td>15236</td>\n",
       "      <td>Pizza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2495</th>\n",
       "      <td>98-241-1456</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Buffalo Wild Wings Grill &amp; Bar</td>\n",
       "      <td>480 Home Dr</td>\n",
       "      <td>Pittsburgh</td>\n",
       "      <td>PA</td>\n",
       "      <td>15275</td>\n",
       "      <td>Restaurants</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2245 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      IUSA Number  Y2019  Y2020  Y2021                    Company Name  \\\n",
       "0     00-268-7606      1      1      1                 Nicholas Coffee   \n",
       "1     00-326-1245      1      1      1                   Grant Bar Inc   \n",
       "2     00-866-7537      1      1      1                  Ivy Restaurant   \n",
       "4     11-815-7221      1      1      1   Delallo's Restaurant-Catering   \n",
       "6     11-819-6567      1      1      1                       Pizza Hut   \n",
       "...           ...    ...    ...    ...                             ...   \n",
       "2488  97-512-2219      1      1      1             Quaker Steak & Lube   \n",
       "2490  98-137-6122      1      1      1   Rita's Italian Ice/Frzn Cstrd   \n",
       "2492  98-137-6593      1      1      1                       Starbucks   \n",
       "2494  98-241-1282      1      1      1                   Vocelli Pizza   \n",
       "2495  98-241-1456      1      1      1  Buffalo Wild Wings Grill & Bar   \n",
       "\n",
       "                          Address            City State ZIP Code  \\\n",
       "0                23 Market Sq # 1      Pittsburgh    PA    15222   \n",
       "1                   114 Grant Ave      Pittsburgh    PA    15209   \n",
       "2         360 Castle Shannon Blvd      Pittsburgh    PA    15234   \n",
       "4                91 Fort Couch Rd      Pittsburgh    PA    15241   \n",
       "6     271 1/2 Clairton Blvd # 1-2      Pittsburgh    PA    15236   \n",
       "...                           ...             ...   ...      ...   \n",
       "2488                110 Andrew Dr      Pittsburgh    PA    15275   \n",
       "2490            1320 Babcock Blvd      Pittsburgh    PA    15209   \n",
       "2492          730 Copeland St # 1      Pittsburgh    PA    15232   \n",
       "2494            691 Clairton Blvd  Pleasant Hills    PA    15236   \n",
       "2495                  480 Home Dr      Pittsburgh    PA    15275   \n",
       "\n",
       "     Primary SIC Description  \n",
       "0               Coffee & Tea  \n",
       "1                Restaurants  \n",
       "2                Restaurants  \n",
       "4                Restaurants  \n",
       "6                      Pizza  \n",
       "...                      ...  \n",
       "2488             Restaurants  \n",
       "2490       Ice Cream Parlors  \n",
       "2492            Coffee Shops  \n",
       "2494                   Pizza  \n",
       "2495             Restaurants  \n",
       "\n",
       "[2245 rows x 10 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ls = ['Arcades' ,'Party Centers']\n",
    "df_join = df_join[~df_join['Primary SIC Description'].isin(ls)]\n",
    "df_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export\n",
    "df_join.to_csv(\"RestaurantYYY\" + \".csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#FINISH\n",
    "duration = 1000  # milliseconds\n",
    "freq = 440  # Hz\n",
    "winsound.Beep(freq, duration)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "b3ba2566441a7c06988d0923437866b63cedc61552a5af99d1f4fb67d367b25f"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 ('base')",
   "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.8.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}