search_data_processing_baolin.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Search Data Processing\n",
    "=========================\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 347,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pyspark\n",
    "import re\n",
    "import string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "search_data = pd.read_csv(\"./data/sample_openData_searchTerms_clean.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5000, 8)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "search_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Search Term</th>\n",
       "      <th>Exit Page</th>\n",
       "      <th>Total Unique Searches</th>\n",
       "      <th>Results Pageviews / Search</th>\n",
       "      <th>% Search Exits</th>\n",
       "      <th>% Search Refinements</th>\n",
       "      <th>Time after Search</th>\n",
       "      <th>Average Search Depth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4995</th>\n",
       "      <td>address range</td>\n",
       "      <td>/Geographic-Locations-and-Boundaries/Streets-o...</td>\n",
       "      <td>2</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0:02:09</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4996</th>\n",
       "      <td>address shapefile</td>\n",
       "      <td>/browse/embed?category=&amp;limit=20&amp;limitTo=&amp;q=ad...</td>\n",
       "      <td>2</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>66.67%</td>\n",
       "      <td>0:00:18</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4997</th>\n",
       "      <td>address to geographic location</td>\n",
       "      <td>/browse/embed?category=&amp;limit=20&amp;limitTo=&amp;q=ad...</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0:00:03</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4998</th>\n",
       "      <td>address with resident names</td>\n",
       "      <td>/browse/embed?category=&amp;limit=20&amp;limitTo=&amp;q=ad...</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0:00:05</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4999</th>\n",
       "      <td>addresse</td>\n",
       "      <td>/browse/embed?category=&amp;limit=20&amp;limitTo=&amp;q=po...</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>100.00%</td>\n",
       "      <td>0:00:03</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Search Term  \\\n",
       "4995                   address range   \n",
       "4996               address shapefile   \n",
       "4997  address to geographic location   \n",
       "4998     address with resident names   \n",
       "4999                        addresse   \n",
       "\n",
       "                                              Exit Page  \\\n",
       "4995  /Geographic-Locations-and-Boundaries/Streets-o...   \n",
       "4996  /browse/embed?category=&limit=20&limitTo=&q=ad...   \n",
       "4997  /browse/embed?category=&limit=20&limitTo=&q=ad...   \n",
       "4998  /browse/embed?category=&limit=20&limitTo=&q=ad...   \n",
       "4999  /browse/embed?category=&limit=20&limitTo=&q=po...   \n",
       "\n",
       "      Total Unique Searches  Results Pageviews / Search % Search Exits  \\\n",
       "4995                      2                         1.5          0.00%   \n",
       "4996                      2                         1.5          0.00%   \n",
       "4997                      2                         1.0          0.00%   \n",
       "4998                      2                         1.0          0.00%   \n",
       "4999                      2                         1.0          0.00%   \n",
       "\n",
       "     % Search Refinements Time after Search  Average Search Depth  \n",
       "4995                0.00%           0:02:09                   8.0  \n",
       "4996               66.67%           0:00:18                   3.0  \n",
       "4997                0.00%           0:00:03                   1.5  \n",
       "4998                0.00%           0:00:05                   1.5  \n",
       "4999              100.00%           0:00:03                   1.5  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "search_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#search_data[search_data['Total Unique Searches']<5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "search_terms = list(set(search_data['Search Term']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2451"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(search_terms)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#print search_terms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Search Term</th>\n",
       "      <th>Exit Page</th>\n",
       "      <th>Total Unique Searches</th>\n",
       "      <th>Results Pageviews / Search</th>\n",
       "      <th>% Search Exits</th>\n",
       "      <th>% Search Refinements</th>\n",
       "      <th>Time after Search</th>\n",
       "      <th>Average Search Depth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3519</th>\n",
       "      <td>194415</td>\n",
       "      <td>/Economy-and-Community/Registered-Business-Loc...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0.00%</td>\n",
       "      <td>0:00:06</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Search Term                                          Exit Page  \\\n",
       "3519      194415  /Economy-and-Community/Registered-Business-Loc...   \n",
       "\n",
       "      Total Unique Searches  Results Pageviews / Search % Search Exits  \\\n",
       "3519                      2                           1          0.00%   \n",
       "\n",
       "     % Search Refinements Time after Search  Average Search Depth  \n",
       "3519                0.00%           0:00:06                   2.5  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "search_data.ix[search_data[\"Search Term\"] == \"194415\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "search_terms_data = search_data[[\"Search Term\"]]\n",
    "search_terms_data = search_terms_data.rename(columns={\"Search Term\": \"search_term\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>search_term</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>crime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>crime</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>streets</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  search_term\n",
       "0    business\n",
       "1       crime\n",
       "2       crime\n",
       "3         311\n",
       "4     streets"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "search_terms_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "search_terms_data['processed_data'] = search_terms_data.search_term\\\n",
    "                                        .apply(lambda text: text.decode('ascii' ,\"ignore\" ).decode('utf-8','ignore'))\\\n",
    "                                        .apply(lambda text: text.lower())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "search_terms_list =  list(set(search_terms_data.processed_data))\n",
    "#print search_terms_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We need to generally tag searches in categories of quality\n",
    "\n",
    "Many search terms indicate that users are not informed on what the purpose of the website is or how to properly use search\n",
    "\n",
    "Search Tags\n",
    "* Good Quality Search\n",
    "    * complete words or phrases\n",
    "    * minor typos\n",
    "* Bad Quality\n",
    "    * Addresses\n",
    "    * Dates\n",
    "    * Zipcodes or just a string of numbers\n",
    "    * General nonsenses eg. ('></script><script>alert(1)</script>', '///', '16exc-3031') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dates_r = re.compile('[0-9]/[0-9]/[0-9]')\n",
    "numbers_r = re.compile('^[0-9][0-9]*[0-9]$')\n",
    "html_r = re.compile('^<.*>$')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'9/1/01', u'3/1/01']"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filter(dates_r.match, search_terms_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[u'</script><script>alert(1)</script>']\n"
     ]
    }
   ],
   "source": [
    "print filter(html_r.match, search_terms_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# removing punctuation\n",
    "\n",
    "def removePunctuation(text):\n",
    "\n",
    "    for c in string.punctuation:\n",
    "        text = text.replace(c,\"\").strip().lower()\n",
    "    return text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# iterative process\n",
    "\n",
    "def text_processing(search):\n",
    "    \n",
    "    return [removePunctuation(i) for i in search]\n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "search = text_processing(search_terms_list)\n",
    "#search"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "regex = \"\\d{1,4}.?\\d{0,4}\\s[a-zA-Z|\\d+]{2,30}\\s[a-zA-Z]|\\s[a-zA-Z]*\"\n",
    "\n",
    "f = [re.findall(regex, i) for i in search\n",
    "     if re.findall(regex, i)!= [] \n",
    "     if re.findall(regex, i)[0][:3] != '311'\n",
    "     ]\n",
    "\n",
    "# http://regexlib.com/REDetails.aspx?regexp_id=430"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "year = [str(j) for j in range(2000,2017)]\n",
    "\n",
    "addresses = [i for i in f if i[0][:4] not in year ]\n",
    "#addresses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 409,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# applying existing code to the full data\n",
    "\n",
    "query = pd.read_csv(\"./data/all_queries.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 410,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ga.searchKeyword</th>\n",
       "      <th>ga.searchStartPage</th>\n",
       "      <th>ga.searchAfterDestinationPage</th>\n",
       "      <th>ga.searchUniques</th>\n",
       "      <th>ga.avgSearchResultViews</th>\n",
       "      <th>ga.avgSearchDepth</th>\n",
       "      <th>ga.percentSearchRefinements</th>\n",
       "      <th>ga.searchDuration</th>\n",
       "      <th>ga.searchExitRate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>crime</td>\n",
       "      <td>'/</td>\n",
       "      <td>'/data?search=crime</td>\n",
       "      <td>451</td>\n",
       "      <td>1.066519</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>business</td>\n",
       "      <td>'/</td>\n",
       "      <td>'/data?search=business</td>\n",
       "      <td>319</td>\n",
       "      <td>1.106583</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>311</td>\n",
       "      <td>'/</td>\n",
       "      <td>'/data?search=311</td>\n",
       "      <td>221</td>\n",
       "      <td>1.135747</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>crime</td>\n",
       "      <td>'/</td>\n",
       "      <td>'/browse/embed?Department-Metrics_Publishing-D...</td>\n",
       "      <td>200</td>\n",
       "      <td>1.070000</td>\n",
       "      <td>4.54</td>\n",
       "      <td>12.149533</td>\n",
       "      <td>48646</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>streets</td>\n",
       "      <td>'/</td>\n",
       "      <td>'/data?search=streets</td>\n",
       "      <td>169</td>\n",
       "      <td>1.047337</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ga.searchKeyword ga.searchStartPage  \\\n",
       "0            crime                 '/   \n",
       "1         business                 '/   \n",
       "2              311                 '/   \n",
       "3            crime                 '/   \n",
       "4          streets                 '/   \n",
       "\n",
       "                       ga.searchAfterDestinationPage  ga.searchUniques  \\\n",
       "0                                '/data?search=crime               451   \n",
       "1                             '/data?search=business               319   \n",
       "2                                  '/data?search=311               221   \n",
       "3  '/browse/embed?Department-Metrics_Publishing-D...               200   \n",
       "4                              '/data?search=streets               169   \n",
       "\n",
       "   ga.avgSearchResultViews  ga.avgSearchDepth  ga.percentSearchRefinements  \\\n",
       "0                 1.066519               0.00                     0.000000   \n",
       "1                 1.106583               0.00                     0.000000   \n",
       "2                 1.135747               0.00                     0.000000   \n",
       "3                 1.070000               4.54                    12.149533   \n",
       "4                 1.047337               0.00                     0.000000   \n",
       "\n",
       "   ga.searchDuration  ga.searchExitRate  \n",
       "0                 62                  0  \n",
       "1                  5                  0  \n",
       "2                  7                  0  \n",
       "3              48646                  0  \n",
       "4                  3                  0  "
      ]
     },
     "execution_count": 410,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 412,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(116339, 9)"
      ]
     },
     "execution_count": 412,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.shape\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 413,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# seeing the data\n",
    "\n",
    "query_list =  list(query['ga.searchKeyword'].values)\n",
    "query_list = [str(word).decode('ascii' ,\"ignore\" ).decode('utf-8','ignore') for word in query_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 414,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20600"
      ]
     },
     "execution_count": 414,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set_query = len(set(query_list))\n",
    "set_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 415,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#adresses\n",
    "regex = \"\\d{1,4}.?\\d{0,4}\\s[a-zA-Z|\\d+|\\W+]{2,30}\\s[a-zA-Z]{2,15}\"\n",
    "\n",
    "full = [re.findall(regex, i) for i in query_list\n",
    "     if re.findall(regex, i)!= [] \n",
    "     if re.findall(regex, i)[0][:3] != '311'\n",
    "     ]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 416,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1967"
      ]
     },
     "execution_count": 416,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(full)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 417,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[u'2010 census tracts'],\n",
       " [u'460 forms Schedule'],\n",
       " [u'1010 fitzgerald ave, san francisco, ca'],\n",
       " [u'1600 California street'],\n",
       " [u'1996-2013 city survey'],\n",
       " [u'2 hour parking'],\n",
       " [u'2 hour parking'],\n",
       " [u'2011 herrera campaign contributions'],\n",
       " [u'2012 SFO Customer Survey'],\n",
       " [u'2013 housing inventory'],\n",
       " [u'2598 Mission Street'],\n",
       " [u'405 howard st'],\n",
       " [u'5 thomas mellon circle'],\n",
       " [u'2000 Blocks (no water'],\n",
       " [u'2000 Blocks (no water'],\n",
       " [u'2000 Tracts (no water'],\n",
       " [u'2000 Tracts (no water'],\n",
       " [u'2000 Blocks (no water'],\n",
       " [u'2000 blocks (no water'],\n",
       " [u'2000 tracts (no water'],\n",
       " [u'2000 tracts (no water'],\n",
       " [u'120 lake street'],\n",
       " [u'0 Beatrice Rd, Brisbane, CA'],\n",
       " [u'19/2015 dashiell hammet'],\n",
       " [u'1 Embarcadero San Francisco'],\n",
       " [u'1 Hawkins Ln SF CA'],\n",
       " [u'1 Longview Court'],\n",
       " [u'1 Polk st'],\n",
       " [u'1 Tuscany Alley'],\n",
       " [u'1 hour parking'],\n",
       " [u'1 post st'],\n",
       " [u'1 scott street'],\n",
       " [u'1 south van ness ave'],\n",
       " [u'1 south vanness'],\n",
       " [u'1. BRIAN DUSSEAULT'],\n",
       " [u'10 gb dataset'],\n",
       " [u'10 glendale street'],\n",
       " [u'100 church street'],\n",
       " [u'100 delano ave'],\n",
       " [u'100 year flood'],\n",
       " [u'100 year flood plain'],\n",
       " [u'100 year storm flood plain'],\n",
       " [u'1000 Sutter Street'],\n",
       " [u'1000 brannan street'],\n",
       " [u'1000 howard st'],\n",
       " [u'1008 Santiago street, san francisco'],\n",
       " [u'101 SAN ALESO AVENIE'],\n",
       " [u'101 utah street'],\n",
       " [u'1023 vallejo street'],\n",
       " [u'1033 polk street'],\n",
       " [u'1040 howard st'],\n",
       " [u'1049 Market Street'],\n",
       " [u'1050 Sansome St'],\n",
       " [u'1051 de haro'],\n",
       " [u'1051 grant ave, sf'],\n",
       " [u'1051 market st'],\n",
       " [u'106 Powell Street'],\n",
       " [u'106 grand view'],\n",
       " [u'1071 Alabama St'],\n",
       " [u'110 sylvan drive, san francisco'],\n",
       " [u'1100 divisadero st'],\n",
       " [u'11025 Carolina Place Pkwy, Pineville'],\n",
       " [u'11025 Carolina Place Pkwy, Pineville', u'8134, United States'],\n",
       " [u'111 4th ave'],\n",
       " [u'111 Ellis Street'],\n",
       " [u'111 powell street'],\n",
       " [u'112 7th street'],\n",
       " [u'1122 Vallejo St'],\n",
       " [u'1125 17th avenue'],\n",
       " [u'1131 Mission St'],\n",
       " [u'114 Henry street'],\n",
       " [u'114 powell street, san francisco, ca'],\n",
       " [u'1146a Guerrero Street, San Francisco'],\n",
       " [u'1155 folsom at'],\n",
       " [u'1155 folsom st'],\n",
       " [u'1158 noe street'],\n",
       " [u'1160 Mission St, San Francisco, cA'],\n",
       " [u'1177 California st'],\n",
       " [u'1188 Harrison Street'],\n",
       " [u'1188 Harrrison Street'],\n",
       " [u'12 hillview court'],\n",
       " [u'1218 44th Ave., San Francisco'],\n",
       " [u'122 farallones st'],\n",
       " [u'1225 17th avenue'],\n",
       " [u'123 Retiro way'],\n",
       " [u'1230 markert st'],\n",
       " [u'1230 market st'],\n",
       " [u'1231 20th ave, san francisco'],\n",
       " [u'1231 MArket St'],\n",
       " [u'1235 Florida Street'],\n",
       " [u'1238 39th avenue'],\n",
       " [u'124 Molimo dr'],\n",
       " [u'126 fillmore street'],\n",
       " [u'126 fillmore stret'],\n",
       " [u'127 san jose ave'],\n",
       " [u'129 randall st'],\n",
       " [u'13 Melrose ave'],\n",
       " [u'13 Melrose ave'],\n",
       " [u'13 Melrose ave'],\n",
       " [u'1300 block of mcallister'],\n",
       " [u'131 Franklin street'],\n",
       " [u'132 corbett ave'],\n",
       " [u'1327 Leavenworth St'],\n",
       " [u'1328a kobbe avenue'],\n",
       " [u'1343 28th avenue'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 seacliff ave'],\n",
       " [u'1355 market street san francisco'],\n",
       " [u'1358 28th avenue, san francisco, ca'],\n",
       " [u'1359 sacramento st'],\n",
       " [u'136 arlington street'],\n",
       " [u'136 rivoli street'],\n",
       " [u'1363 alabama street'],\n",
       " [u'1365 Noriega Street'],\n",
       " [u'138 03rd street'],\n",
       " [u'1381 quesada avenue'],\n",
       " [u'1385 carroll ave'],\n",
       " [u'1390 market street'],\n",
       " [u'140 mason street'],\n",
       " [u'140 new Montgomery st'],\n",
       " [u'140 new Montgomery st'],\n",
       " [u'1405 Van Ness Ave'],\n",
       " [u'1407 EGBERT STREET SAN FRANCISCO'],\n",
       " [u'1409 EGBERT STREET SAN FRANCISCO'],\n",
       " [u'141 Milton Street'],\n",
       " [u'141 Milton Street San Francisco, CA'],\n",
       " [u'141 Milton Street San Francisco, CA'],\n",
       " [u'141 eddy street'],\n",
       " [u'141 eddy street sf ca'],\n",
       " [u'1412 hudson ave'],\n",
       " [u'1415 scott street'],\n",
       " [u'1416 polk street'],\n",
       " [u'1420 5th Ave., san francisco, ca'],\n",
       " [u'1423 golden gate'],\n",
       " [u'1426 48th avenue'],\n",
       " [u'1427 11th Ave'],\n",
       " [u'1428 jackson st'],\n",
       " [u'1432 grove street san francisco ca'],\n",
       " [u'1439 16th ave'],\n",
       " [u'1442 43rd ave'],\n",
       " [u'1443 46th ave'],\n",
       " [u'1444 market st'],\n",
       " [u'1450 green st'],\n",
       " [u'1453 mission st'],\n",
       " [u'1453 mission street'],\n",
       " [u'1455 Custer Street'],\n",
       " [u'146 galewood circle'],\n",
       " [u'1480 larkin st'],\n",
       " [u'150 Lawrence Ave'],\n",
       " [u'150 Lawrence Ave san francisco'],\n",
       " [u'1500 Mission Street'],\n",
       " [u'1500 mc allister street san francisco'],\n",
       " [u'1500 mc allister street san francisco'],\n",
       " [u'1514 alemany blvd'],\n",
       " [u'1515 s. van ness'],\n",
       " [u'1523 guerrero st'],\n",
       " [u'153 college ave'],\n",
       " [u'155 arbor st'],\n",
       " [u'155 park st san francsico ca'],\n",
       " [u'1560 8th ave'],\n",
       " [u'1560 howard street'],\n",
       " [u'1560 howard stret'],\n",
       " [u'1578 Newhall St. San Francisco'],\n",
       " [u'158 maynarad st'],\n",
       " [u'160 Swiss Avenue'],\n",
       " [u'1600 California street'],\n",
       " [u'1600 Fell street'],\n",
       " [u'162 landers st'],\n",
       " [u'163 Liberty Street'],\n",
       " [u'163 Ney St'],\n",
       " [u'163 russ st'],\n",
       " [u'1630 30th Avenue'],\n",
       " [u'1660 Stockton at'],\n",
       " [u'1660 Stockton street'],\n",
       " [u'1683 42nd Ave, San Francisco CA'],\n",
       " [u'1683 7th avenue'],\n",
       " [u'169 Steuart St'],\n",
       " [u'169 Steuart St; San Francisco, CA'],\n",
       " [u'169 Steuart St; San Francisco, CA'],\n",
       " [u'1693 7th avenue'],\n",
       " [u'17 San Andreas Way, San francisco'],\n",
       " [u'17 Silverview Drive'],\n",
       " [u'1700 Newhall street'],\n",
       " [u'1700 Owens street'],\n",
       " [u'1700 california st'],\n",
       " [u'1709 Broderick St, San Francisco, CA'],\n",
       " [u'1709 Broderick Street'],\n",
       " [u'1738 Ocean Avenue San Francisco, CA'],\n",
       " [u'1740 Cesar Chavez'],\n",
       " [u'1740 Cesar Chavez'],\n",
       " [u'1746 Church Street, San Francisco'],\n",
       " [u'175 Ney Street'],\n",
       " [u'1755 clay st'],\n",
       " [u'177 Stillman St'],\n",
       " [u'177 Stillman Street'],\n",
       " [u'177 Stillman Street report'],\n",
       " [u'177 Stillman Street report'],\n",
       " [u'177 Stillman Street, San Francisco'],\n",
       " [u'177 Stillman Street, San Francisco'],\n",
       " [u'177 Stillman Street, San Francisco'],\n",
       " [u'177 stillman st. sf'],\n",
       " [u'177 stillman st., san francisco, ca'],\n",
       " [u'1773 chestnut st'],\n",
       " [u'1798 grove street'],\n",
       " [u'1798 grove street'],\n",
       " [u'18 latona st'],\n",
       " [u'1801 silliman st'],\n",
       " [u'1805 S. Van Ness'],\n",
       " [u'1813 15th Street'],\n",
       " [u'186 valley street'],\n",
       " [u'1865 4th St, San Francisco'],\n",
       " [u'1895 sacramento st'],\n",
       " [u'19 rausch street'],\n",
       " [u'1900 ferry route'],\n",
       " [u'1917 Broderick street'],\n",
       " [u'1931 35th Ave San Francisco, CA'],\n",
       " [u'1939 Oakdale Ave'],\n",
       " [u'1941 ocean avenue San Francisco California'],\n",
       " [u'1951 oak st'],\n",
       " [u'1952 larkin street'],\n",
       " [u'1970 census block'],\n",
       " [u'1975 Jim Jones'],\n",
       " [u'1977 20th Avenue San Francisco ca'],\n",
       " [u'198 waterville street, san francisco'],\n",
       " [u'199 new Montgomery'],\n",
       " [u'199 new Montgomery st'],\n",
       " [u'199 new montgomery'],\n",
       " [u'1994 Carroll Avenue'],\n",
       " [u'1994 Union St'],\n",
       " [u'1996-2011 City Survey Database'],\n",
       " [u'1996-2013 SF City Survey'],\n",
       " [u'1996-2013 city survey data'],\n",
       " [u'2 Crestline Dr'],\n",
       " [u'2 bedroom homesale'],\n",
       " [u'2 casa way'],\n",
       " [u'2 hour parking'],\n",
       " [u'2 hour parking'],\n",
       " [u'2 hour street parking 2999 California'],\n",
       " [u'2 plus 2 bookeeping'],\n",
       " [u'20 Roussea Street'],\n",
       " [u'20 jones st'],\n",
       " [u'200 6th st'],\n",
       " [u'200 hazelwood ave'],\n",
       " [u'200 mississippi street'],\n",
       " [u'2000 census shapefile'],\n",
       " [u'2000 census tract'],\n",
       " [u'2000 census tract shapefile'],\n",
       " [u'2000 census tracts'],\n",
       " [u'2000 census tracts'],\n",
       " [u'2000 population density'],\n",
       " [u'2004 bicycle network'],\n",
       " [u'2004 bike network'],\n",
       " [u'2004 food vendors'],\n",
       " [u'2005 Pipeline Report'],\n",
       " [u'2005 housing inventory'],\n",
       " [u'2005 housing inventory'],\n",
       " [u'2007 housing inventory'],\n",
       " [u'2007 housing inventory'],\n",
       " [u'2008 crime data'],\n",
       " [u'2009 housing inventory'],\n",
       " [u'201 Hale Street'],\n",
       " [u'2010 census block'],\n",
       " [u'2010 census block centroids'],\n",
       " [u'2010 census house hold data by taz'],\n",
       " [u'2010 census population'],\n",
       " [u'2010 census tracts'],\n",
       " [u'2010 census tracts no water'],\n",
       " [u'2010 map:crime incidents'],\n",
       " [u'2010 population shapefile'],\n",
       " [u'2010 tract population'],\n",
       " [u'2010 us census data'],\n",
       " [u'2010 vehicle ownership'],\n",
       " [u'2011 Customer Survey Dataset'],\n",
       " [u'2011 herrera campaign contributions'],\n",
       " [u'2011 herrera mayor campaign contributions'],\n",
       " [u'2011 mayor campaign contributions'],\n",
       " [u'2011 pipeline report'],\n",
       " [u'2012 Housing Inventory'],\n",
       " [u'2012 SFO Customer Survey'],\n",
       " [u'2012 campaign finance'],\n",
       " [u'2012 campaign finance'],\n",
       " [u'2012 campaign finance'],\n",
       " [u'2012 census population'],\n",
       " [u'2012 housing inventory'],\n",
       " [u'2012 housing inventory'],\n",
       " [u'2013 City Survey Report'],\n",
       " [u'2013 Housing Inventory'],\n",
       " [u'2013 SFO Customer Survey'],\n",
       " [u'2013 building permits'],\n",
       " [u'2013 census shapefile'],\n",
       " [u'2013 housing inventory'],\n",
       " [u'2013 housing inventory'],\n",
       " [u'2013 sfpd incidents'],\n",
       " [u'2014 City Serve report'],\n",
       " [u'2014 Housing Inventory'],\n",
       " [u'2014 census tracts'],\n",
       " [u'2014 energy benchmark 285 geary'],\n",
       " [u'2014 housing inventory'],\n",
       " [u'2014 housing inventory'],\n",
       " [u'2014 housing inventory'],\n",
       " [u'2014 housing inventory'],\n",
       " [u'2014 housing inventory'],\n",
       " [u'2014 map:crime incidents'],\n",
       " [u'2014 q4 planning pipeline'],\n",
       " [u'2015 311 budget'],\n",
       " [u'2015 Crime Statistics'],\n",
       " [u'2015 Form 460; Schedule'],\n",
       " [u'2015 Housing Inventory'],\n",
       " [u'2015 Housing Inventory'],\n",
       " [u'2015 Housing Inventory'],\n",
       " [u'2015 Murder by race statistics'],\n",
       " [u'2015 Neighborhood profiles'],\n",
       " [u'2015 Q4 pipeline'],\n",
       " [u'2015 budget information'],\n",
       " [u'2015 city survey'],\n",
       " [u'2015 crime statistics'],\n",
       " [u'2015 enacted budget'],\n",
       " [u'2015 housing inventory'],\n",
       " [u'2015 housing inventory'],\n",
       " [u'2015 residential development pipeline'],\n",
       " [u'2016 Assessor map'],\n",
       " [u'2016 Assessor map updates'],\n",
       " [u'2016 Feb 26 arrests in bay view'],\n",
       " [u'2016 budget publication'],\n",
       " [u'2016 housing development'],\n",
       " [u'201o census population'],\n",
       " [u'2020 Ellis st'],\n",
       " [u'2021 baker st'],\n",
       " [u'203 Cotter st'],\n",
       " [u'2030 alemany blvd'],\n",
       " [u'205 Upper Terrace'],\n",
       " [u'2079 15TH AVENUE'],\n",
       " [u'208 caselli ave'],\n",
       " [u'2090 Evans Avenue'],\n",
       " [u'2096 castro street'],\n",
       " [u'2124 Great Hwy'],\n",
       " [u'2130 22nd St San Francisco CA'],\n",
       " [u'2130 JAckson street'],\n",
       " [u'2134 GREEN STREET #3 SAN FRANCISCO, CA'],\n",
       " [u'2135 mission street'],\n",
       " [u'2145 19th Ave, San Francisco, CA'],\n",
       " [u'215 Niagara Ave Permit Parking'],\n",
       " [u'2166 32nd ave'],\n",
       " [u'2172 Pacific Avenue'],\n",
       " [u'2174 California Street'],\n",
       " [u'2190 Lombard St, San Francisco, CA'],\n",
       " [u'220 30th Ave'],\n",
       " [u'2200 market permit'],\n",
       " [u'2201 Market Street'],\n",
       " [u'2208 MISSION STREET'],\n",
       " [u'2208 Mission Street'],\n",
       " [u'221 san carlos'],\n",
       " [u'2211 stockton st'],\n",
       " [u'224 ordway st'],\n",
       " [u'2240 Market Street'],\n",
       " [u'2243 32nd Avenue'],\n",
       " [u'2250 POWELL STREET'],\n",
       " [u'2259 25th ave'],\n",
       " [u'2275 sutter st'],\n",
       " [u'2279 Mission Street'],\n",
       " [u'2282 Market Street'],\n",
       " [u'2282 market st'],\n",
       " [u'2282 market street'],\n",
       " [u'2283 37th ave'],\n",
       " [u'230 Eddy Street'],\n",
       " [u'2310 mason street'],\n",
       " [u'2325 Pine Street'],\n",
       " [u'233 San Carlos'],\n",
       " [u'2332 TARAVAL ST SF'],\n",
       " [u'2338 TARAVAL ST SF'],\n",
       " [u'2349 Francisco St'],\n",
       " [u'235 Presidio Avenue'],\n",
       " [u'2385 bush st'],\n",
       " [u'2385 bush street'],\n",
       " [u'239 carl street'],\n",
       " [u'2394 Mariner Square Drive, Alameda, CA'],\n",
       " [u'240 2nd Street'],\n",
       " [u'240 Dolores St'],\n",
       " [u'2400 14th avenue'],\n",
       " [u'2400 CALIFORNIA ST SAN FRANCISCO CA'],\n",
       " [u'2412 Harrison st'],\n",
       " [u'245 San Jose'],\n",
       " [u'2455 Polk Street'],\n",
       " [u'246 2nd street'],\n",
       " [u'2462 30th street'],\n",
       " [u'250 Executive park'],\n",
       " [u'2500 bryant st, san francisco ca'],\n",
       " [u'251 frederick st'],\n",
       " [u'252 27TH Ave, San Francisco, CA'],\n",
       " [u'255 King Stree'],\n",
       " [u'256 Fowler Ave'],\n",
       " [u'2577 1/2 post street'],\n",
       " [u'258 9 th st'],\n",
       " [u'258 9th st'],\n",
       " [u'26 eagle street'],\n",
       " [u'260 church st'],\n",
       " [u'2650 green, Sf'],\n",
       " [u'2652 Harrison St'],\n",
       " [u'2652 Harrison St'],\n",
       " [u'2660 San Bruno Avenue, San Francisco'],\n",
       " [u'267 Green Street'],\n",
       " [u'267 Green Street'],\n",
       " [u'269 joost ave'],\n",
       " [u'2695 43rd avenue'],\n",
       " [u'270 McAllister Street'],\n",
       " [u'270 McAllister Street, San Francisco'],\n",
       " [u'277 Columbus Ave'],\n",
       " [u'278 states street'],\n",
       " [u'280 circular ave'],\n",
       " [u'282 harbor rd san francsico, ca'],\n",
       " [u'284 12th avenue'],\n",
       " [u'285 geary st'],\n",
       " [u'285 geary st energy audit'],\n",
       " [u'288 GOLD MINE DRIVE'],\n",
       " [u'29 Downey st'],\n",
       " [u'2901 sacramento st'],\n",
       " [u'2911 16th st police'],\n",
       " [u'2912 19th ave'],\n",
       " [u'2915 Octavia St'],\n",
       " [u'2919 folsom st'],\n",
       " [u'2919 folsom st'],\n",
       " [u'2919 mission street'],\n",
       " [u'2919-21 folsom st'],\n",
       " [u'2922 sacramento street'],\n",
       " [u'2923 lawton st'],\n",
       " [u'2945 baker st apt'],\n",
       " [u'2950 Vallejo st, sf'],\n",
       " [u'2950 valajo st, sf'],\n",
       " [u'299 Fremont Street'],\n",
       " [u'3 buena vista ave east, sf'],\n",
       " [u'3 rudden avenue sf ca'],\n",
       " [u'300 sea cliff'],\n",
       " [u'3000 Washington Street 4000 Washington'],\n",
       " [u'3008 18th street'],\n",
       " [u'301 Claremont Blvd'],\n",
       " [u'301 baltimore way'],\n",
       " [u'301 king st'],\n",
       " [u'3024 mission st'],\n",
       " [u'3024 mission st'],\n",
       " [u'3060 16th Street'],\n",
       " [u'3060 16th Street building'],\n",
       " [u'3067 25th street'],\n",
       " [u'3067 25th street san francisco ca'],\n",
       " [u'307 Niagra Ave'],\n",
       " [u'308 Font blvd'],\n",
       " [u'3092 16th St'],\n",
       " [u'31 Mullen ave'],\n",
       " [u'313 POTRERO AVE'],\n",
       " [u'3130 Webster St San Francisco, CA'],\n",
       " [u'3139 mission street'],\n",
       " [u'3143 folsom st'],\n",
       " [u'315 28th avenue'],\n",
       " [u'317 oak park dr'],\n",
       " [u'3180 18th Street, San Francisco, CA'],\n",
       " [u'32 richland avenue steven holland'],\n",
       " [u'321 marietta drive'],\n",
       " [u'322 30th ave'],\n",
       " [u'323 cumberland st'],\n",
       " [u'324 arkansas st'],\n",
       " [u'3247 reserve ct, san jose, ca'],\n",
       " [u'329 bryant street san francisco, ca'],\n",
       " [u'33 Albion Street'],\n",
       " [u'33 allston way'],\n",
       " [u'331 jersey st'],\n",
       " [u'331 jersey st parcel map'],\n",
       " [u'3314 STEINER ST'],\n",
       " [u\"333 O'Farrell St, San Francisco, CA\"],\n",
       " [u'333 lake street'],\n",
       " [u'333 market street'],\n",
       " [u'333 market street energy'],\n",
       " [u'3333 California Street'],\n",
       " [u'334 harriet st'],\n",
       " [u'336 moraga ave'],\n",
       " [u'337 spurce st san francisco ca'],\n",
       " [u'337 spurce streeet'],\n",
       " [u'337 spurce street'],\n",
       " [u'340 church st'],\n",
       " [u'340 mason street'],\n",
       " [u'340 mason street investors'],\n",
       " [u'340 warren drive san francisco, ca'],\n",
       " [u'3401 clement st'],\n",
       " [u'342 24th ave'],\n",
       " [u'3434 21st st'],\n",
       " [u'3440 Broderick Street'],\n",
       " [u'345 Williams Avenue'],\n",
       " [u'345 Williams Avenue, San Francisco'],\n",
       " [u'345 spear st'],\n",
       " [u'3465 25th st'],\n",
       " [u'347 precita Ave San Francisco CA'],\n",
       " [u'347-349 Precita Ave'],\n",
       " [u'3490 20th st'],\n",
       " [u'350 2nd st'],\n",
       " [u'350 laurel st san francisco'],\n",
       " [u'350 mission street'],\n",
       " [u'3520 18th partners'],\n",
       " [u'3520 18th partners Business Account Number'],\n",
       " [u'353 BEACH RD, BURLINGAME, CA'],\n",
       " [u'354 Peralta Street'],\n",
       " [u'355 1st street'],\n",
       " [u'355 Bocana Street'],\n",
       " [u'355 Serrano dr'],\n",
       " [u'357 Hayes St'],\n",
       " [u'360 somerset st'],\n",
       " [u'360 somerset street'],\n",
       " [u'361 3rd ave'],\n",
       " [u'372 Point Lobos Ave'],\n",
       " [u'374 5th street san francisco'],\n",
       " [u'375 bush street'],\n",
       " [u'3750 18 street sf ca'],\n",
       " [u'376 ellis street'],\n",
       " [u'3800 16th street san francisco, ca'],\n",
       " [u'3864 23rd street'],\n",
       " [u'3864 23rd street ban'],\n",
       " [u'387 ellis street'],\n",
       " [u'387 ellis street, san francisco, ca'],\n",
       " [u'390 Fremont St'],\n",
       " [u'390 diamond street'],\n",
       " [u'3938 Geary Blvd. San Francisco, CA'],\n",
       " [u'3951 Mission St'],\n",
       " [u'3953 24th street'],\n",
       " [u'397 9th St'],\n",
       " [u'3D Building Model'],\n",
       " [u'3D building model'],\n",
       " [u'3D building models'],\n",
       " [u'3D building models'],\n",
       " [u'3D public art'],\n",
       " [u'3d building model'],\n",
       " [u'4 PERSIA AVENUE'],\n",
       " [u'40 Annapolis Terrace, San Francisco'],\n",
       " [u'40 genebern way, sf'],\n",
       " [u'40 jessie street'],\n",
       " [u'400 Castro Street'],\n",
       " [u'400 Castro soul cycle'],\n",
       " [u'400 Duboce Street'],\n",
       " [u'405 howard st'],\n",
       " [u'4058 17th st'],\n",
       " [u'407 jackson st'],\n",
       " [u'407 sanchez street'],\n",
       " [u'409 Dolores Street'],\n",
       " [u'41 genebern way, sf'],\n",
       " [u'4130 mission street'],\n",
       " [u'4130 mission street'],\n",
       " [u'415 Euclid Ave'],\n",
       " [u'415 Jones St'],\n",
       " [u'415 myra way'],\n",
       " [u'4175 Mission St'],\n",
       " [u'418 Jessie St'],\n",
       " [u'4182 Mission Street'],\n",
       " [u'42 jules avenue'],\n",
       " [u'424 octavia st'],\n",
       " [u'425 10th st'],\n",
       " [u'425 market energy'],\n",
       " [u'425 market street'],\n",
       " [u'425 market street energy'],\n",
       " [u'428 chicago way'],\n",
       " [u'43 ruth street, san francisco'],\n",
       " [u'430 lake st'],\n",
       " [u'439 Duboce Ave'],\n",
       " [u'44 samoset st'],\n",
       " [u'442 holloway ave., SF'],\n",
       " [u'4431 mission st'],\n",
       " [u'444 de haro st'],\n",
       " [u'444 townsend st'],\n",
       " [u'447 Broadway Street'],\n",
       " [u'450 golden gate'],\n",
       " [u'450 golden gate'],\n",
       " [u'453 masonic st'],\n",
       " [u'456 montgomery street suite 2200 san'],\n",
       " [u'459 geary LLC'],\n",
       " [u'46 El Sereno Court'],\n",
       " [u'460 dwight street'],\n",
       " [u'460 forms Schedule'],\n",
       " [u'460 forms Schedule'],\n",
       " [u'460 forms Schedule'],\n",
       " [u'460 forms Schedule'],\n",
       " [u'460 forms Schedule'],\n",
       " [u'460 forms schedul'],\n",
       " [u'460 forms schedula'],\n",
       " [u'460 forms schedule'],\n",
       " [u'460 forms schedule'],\n",
       " [u'460 forms schedule'],\n",
       " [u'460 forms schedule'],\n",
       " [u'460 post street'],\n",
       " [u'460 post street business search'],\n",
       " [u'4610 mission street'],\n",
       " [u'462 DOUGLASS SR'],\n",
       " [u'462 DOUGLASS ST'],\n",
       " [u'466 9th avenue'],\n",
       " [u'4689 mission st'],\n",
       " [u'469 eddy street'],\n",
       " [u'469 union st'],\n",
       " [u'470 lake st'],\n",
       " [u'476 Ellington Ave'],\n",
       " [u'476 Ellington ave'],\n",
       " [u'478 utah st'],\n",
       " [u'487 dolores st'],\n",
       " [u'487 dolores street'],\n",
       " [u'49 Christopher Dr, San Francisco, CA'],\n",
       " [u'49 Christopher Dr, San Francisco, CA'],\n",
       " [u'49 mile scenic drive'],\n",
       " [u'49 south park st'],\n",
       " [u'490 POST ST'],\n",
       " [u'495 geary street'],\n",
       " [u'5 DAY WATER RELATED'],\n",
       " [u'5 Hudson Ct, San Francisco, CA'],\n",
       " [u'5 year plan'],\n",
       " [u'50 Main Street, San Francisco'],\n",
       " [u'50 quint street'],\n",
       " [u'501 The Embarcadero; San Francisco'],\n",
       " [u'501 columbus Ave'],\n",
       " [u'5060 WEST 14TH STREET GREELEY CO'],\n",
       " [u'508 Haight st'],\n",
       " [u'519 capp street san francisco'],\n",
       " [u'521 clement street'],\n",
       " [u'525 29th st'],\n",
       " [u'5252 mission street'],\n",
       " [u'527 42nd ave'],\n",
       " [u'530 Brannan Street'],\n",
       " [u'530 brannan street'],\n",
       " [u'530 brannan street business lincenses'],\n",
       " [u'5300 3rd street'],\n",
       " [u'534 4th street'],\n",
       " [u'5400 mission san francisco'],\n",
       " [u'5413 Hillside av.,el cerrito'],\n",
       " [u'5413 Hillside av.,el cerrito'],\n",
       " [u'543 19TH AVE'],\n",
       " [u'543 columbus Ave'],\n",
       " [u'544 Castro St'],\n",
       " [u'545 sutter street, san francisco'],\n",
       " [u'548 Banks St, San Francisco'],\n",
       " [u'55 Cyril Magnin Street'],\n",
       " [u'550 3rd street san francisco ca'],\n",
       " [u'550 Van Ness'],\n",
       " [u'554 irving street, san francisco'],\n",
       " [u'555 4th st'],\n",
       " [u'555 market street'],\n",
       " [u'555 minnesota street'],\n",
       " [u'56 moss st'],\n",
       " [u'560 davis st san francisco'],\n",
       " [u'560 mission street'],\n",
       " [u'562 Monterey Blvd, San Francisco, CA'],\n",
       " [u'568 Brannan St'],\n",
       " [u'573 andover street'],\n",
       " [u'576 natoma st, san francisco, ca'],\n",
       " [u'579 Teresita Blvd'],\n",
       " [u'5800 3rd St'],\n",
       " [u'584 filbert street'],\n",
       " [u'589 Post St, San Francisco, CA'],\n",
       " [u'595 Market Street, San Francisco, CA'],\n",
       " [u'60 Garcia ave'],\n",
       " [u'60 agua way'],\n",
       " [u'600 Van Ness'],\n",
       " [u'600 fell st'],\n",
       " [u'600 san bruno ave'],\n",
       " [u'601 laguna street'],\n",
       " [u'601 van ness ave'],\n",
       " [u'606 post street'],\n",
       " [u'610 page street'],\n",
       " [u'619 Missouri St., San Francisco, CA'],\n",
       " [u'619 union street'],\n",
       " [u'62 Allston way'],\n",
       " [u'62 linden avenue, san bruno, ca'],\n",
       " [u'62 washburn st'],\n",
       " [u'620 Green Street'],\n",
       " [u'622 Green Street'],\n",
       " [u'625 46th ave'],\n",
       " [u'626 Green Street'],\n",
       " [u'631 cole st'],\n",
       " [u'631 folsom st'],\n",
       " [u'64 Perine Place, Sf'],\n",
       " [u'650 40th ave'],\n",
       " [u'654 46th Avenue'],\n",
       " [u'663 haight street'],\n",
       " [u'665 pine st'],\n",
       " [u'67 Silverview Drive'],\n",
       " [u'675 Naples Street'],\n",
       " [u'677 page st'],\n",
       " [u'677-679 page st'],\n",
       " [u'679 Page STreet'],\n",
       " [u'679 san jose san Francisco'],\n",
       " [u'68 cervantes blvd'],\n",
       " [u'689 20th avenue'],\n",
       " [u'690 folsom street'],\n",
       " [u'690 long bridge street'],\n",
       " [u'700 pennsylvania ave'],\n",
       " [u'701 GOLDEN GATE AV'],\n",
       " [u'701 Minnesota street'],\n",
       " [u'701 Teresita San Francisco, CA'],\n",
       " [u'705 vallejo street'],\n",
       " [u'709 geary st'],\n",
       " [u'71 ford st'],\n",
       " [u'711 clayton st'],\n",
       " [u'72 hour parking restriction'],\n",
       " [u'721 18th avenue'],\n",
       " [u'729 Shotwell St'],\n",
       " [u'735 fell street'],\n",
       " [u'739 Washington Street, Suite'],\n",
       " [u'74 6th Street'],\n",
       " [u'74 granada avenue san francisco'],\n",
       " [u'744 MONTGOMERY ST'],\n",
       " [u'749-757 Jackson St'],\n",
       " [u'75 Dore Street'],\n",
       " [u'750 Sutter St, San Francisco, CA'],\n",
       " [u'754 moultrie group'],\n",
       " [u'755 union st'],\n",
       " [u'755 union st owner name'],\n",
       " [u'760 Geary Steet'],\n",
       " [u'766 vallejo street'],\n",
       " [u'77 beale street buildings'],\n",
       " [u'77 san juan ave'],\n",
       " [u'78 Ina Court'],\n",
       " [u'79 Midcrest Way'],\n",
       " [u'80.004 Employee Compensation report'],\n",
       " [u'800 university avenue, palo alto, california'],\n",
       " [u'810 Dolores san francisco, ca'],\n",
       " [u'810 clay street, san francisco, ca'],\n",
       " [u'812 Guerrero Street'],\n",
       " [u'815 laguna honda'],\n",
       " [u'820 jones street'],\n",
       " [u'822 Irving St'],\n",
       " [u'823 lake street'],\n",
       " [u'829 york street'],\n",
       " [u'83 leo street'],\n",
       " [u'83 mc allister st 513, san francisco'],\n",
       " [u'83 mc allister st, san francisco, ca'],\n",
       " [u'83 mcallister st, san francisco'],\n",
       " [u'8300 Oceanview Terrace'],\n",
       " [u'8300 Oceanview Terrace, 304, San Francisco'],\n",
       " [u'838 detroit street'],\n",
       " [u'838 grant ave'],\n",
       " [u'846 Junipero Serra blvd'],\n",
       " [u'855 folsom st, san francisco'],\n",
       " [u'857 39th Ave'],\n",
       " [u'882 31st ave'],\n",
       " [u'887 45th ave'],\n",
       " [u'887 4th ave'],\n",
       " [u'899 Howard Street San Francisco, CA'],\n",
       " [u'9 county boundary'],\n",
       " [u'9 geneva ave'],\n",
       " [u'9 geneva ave'],\n",
       " [u'90 day cases'],\n",
       " [u'916 pine street'],\n",
       " [u'929 oak st'],\n",
       " [u'929 oak st., sf, ca'],\n",
       " [u'933 treat ave'],\n",
       " [u'939 jackson st'],\n",
       " [u'94 stratford drive'],\n",
       " [u'944 treat av'],\n",
       " [u'944 treat ave'],\n",
       " [u'944 treat avenue'],\n",
       " [u'959 noe street'],\n",
       " [u'960 howard street'],\n",
       " [u'970 key ave'],\n",
       " [u'595 Market Street, San Francisco, CA'],\n",
       " [u'460 - Schedule A - Monetary Contributions'],\n",
       " [u'460 - Schedule E - Payments Made'],\n",
       " [u'460 - Schedule a - Payments Made'],\n",
       " [u'465 - Supplemental Independent Expenditure',\n",
       "  u'3 - Independent Expenditures Made',\n",
       "  u'3, 2015 Election'],\n",
       " [u'460 cover Page'],\n",
       " [u'460 cover Page'],\n",
       " [u'4 2014 Election'],\n",
       " [u'2000 Block Group'],\n",
       " [u'2000 Blocks (no water'],\n",
       " [u'2000 Blocks (no water'],\n",
       " [u'2000 Blocks (no water'],\n",
       " [u'2000 Blocks (no water) Census'],\n",
       " [u'2000 Tracts (no water'],\n",
       " [u'2000 Tracts (no water'],\n",
       " [u'2000 Tracts (no water'],\n",
       " [u'2000 Tracts (no water'],\n",
       " [u'2000 Tracts (no water'],\n",
       " [u'2000 block group'],\n",
       " [u'2010: Census Block Groups'],\n",
       " [u'2015 in San Francisco'],\n",
       " [u'5 year plan'],\n",
       " [u'202 24th ave sf'],\n",
       " [u'355 24th ave'],\n",
       " [u'2. Increase jobs that provide healthy'],\n",
       " [u'65 AND OLDER'],\n",
       " [u'14 Street Sidewalk Evaluation Results'],\n",
       " [u'2130 22nd St San Francisco CA'],\n",
       " [u'90 day emergency shelter waitlist'],\n",
       " [u'90 day emergency shelter waitlist'],\n",
       " [u'38 Air Pollutant Exposure Zone'],\n",
       " [u'1975 Jim Jones'],\n",
       " [u'98 battery st suite'],\n",
       " [u'98 battery st suite'],\n",
       " [u'15% fund raising target'],\n",
       " [u'403 main st'],\n",
       " [u'65 in zip code'],\n",
       " [u'65 in zip code'],\n",
       " [u'65 in zip code'],\n",
       " [u'2 2015 pipeline'],\n",
       " [u'5m water rise'],\n",
       " [u'5m water rise'],\n",
       " [u'5m water rise'],\n",
       " [u'445 Athens Street'],\n",
       " [u'445 Athens Street'],\n",
       " [u'445 Athens Street'],\n",
       " [u'83 California State Plane Zone'],\n",
       " [u'88 INVESTMENT PARTNERS, LLC'],\n",
       " [u'2 hour street parking 2999 California'],\n",
       " [u'39 liveaboard marina'],\n",
       " [u'1 2015 pipline'],\n",
       " [u'700) - Filers with unpaid late filing'],\n",
       " [u'700) Filers with unpaid late filing fees'],\n",
       " [u'700) Non Filers'],\n",
       " [u'388 Market Street, Suite'],\n",
       " [u'0 Miles 1 Note: For Neighborhood Commercial',\n",
       "  u'5: Generalized Neighborhoods Commercial',\n",
       "  u'00 Use: ArcMap 1:70,650 scale for this'],\n",
       " [u'0 Miles 1 Note: For Neighborhood Commercial',\n",
       "  u'5: Generalized Neighborhoods Commercial',\n",
       "  u'00 Use: ArcMap 1:70,650 scale for this'],\n",
       " [u'2010 Demographic Profile Data, SF County'],\n",
       " [u'201 california llc'],\n",
       " [u'201 california owner llc'],\n",
       " [u'2015 richmond district'],\n",
       " [u'1000 feet school'],\n",
       " [u'32 richland avenue'],\n",
       " [u'2000 Block group'],\n",
       " [u'2000 Tracts (no water'],\n",
       " [u'2000 block (no water'],\n",
       " [u'2000 block goup'],\n",
       " [u'2000 block group'],\n",
       " [u'2000 block group'],\n",
       " [u'2000 block group'],\n",
       " [u'2000 blocks (no water'],\n",
       " [u'2000 blocks (no water'],\n",
       " [u'2000 blocks (no water'],\n",
       " [u'2000 blocks (no water'],\n",
       " [u'2000 blocks (no water'],\n",
       " [u'2000 blocks (no water'],\n",
       " [u'2000 blocks (no water'],\n",
       " [u'2000 blocks no water'],\n",
       " [u'2000 blocks no water'],\n",
       " [u'2000 no water'],\n",
       " [u'2000 no water'],\n",
       " [u'2000 no water'],\n",
       " [u'2000 tracts (no water'],\n",
       " [u'2000 tracts (no water'],\n",
       " [u'2000 tracts (no water'],\n",
       " [u'2000 tracts (no water'],\n",
       " [u'2000 tracts (no water'],\n",
       " [u'2000 tracts (no water'],\n",
       " [u'2000 tracts no water'],\n",
       " [u'2000 tracts no water'],\n",
       " [u'2010 block groups'],\n",
       " [u'2010 block groups'],\n",
       " [u'2010 census block for san francisco'],\n",
       " [u'2010 census block for san francisco'],\n",
       " [u'2010 census blocks'],\n",
       " [u'2010 census blocks for san francisco'],\n",
       " [u'2010 census blocks for san francisco'],\n",
       " [u'2010: census blocks'],\n",
       " [u'2010: census blocks for san'],\n",
       " [u'2010: census blocks for san'],\n",
       " [u'2010: census blocks for san francisco'],\n",
       " [u'2010: census blocks for san francisco'],\n",
       " [u'2000 no water'],\n",
       " [u'2000 tracts (no water'],\n",
       " [u'2000 no water'],\n",
       " [u'5 zip codes'],\n",
       " [u'2009 sfo survey'],\n",
       " [u'460 cover apge'],\n",
       " [u'26 curtis street san francisco'],\n",
       " [u'8300 Oceanview Terrace 304, San Francisco'],\n",
       " [u'1 to April'],\n",
       " [u'98 battery st suite'],\n",
       " [u'15% fund raising target'],\n",
       " [u'403 main st'],\n",
       " [u'348 church street'],\n",
       " [u'65 in zip code'],\n",
       " [u'2 2011 pipeline'],\n",
       " [u'2 2011 pipeline report'],\n",
       " [u'2 2015 pipeline'],\n",
       " [u'5m water rise'],\n",
       " [u'202 4th ave'],\n",
       " [u'1618 POLK ST SAN FRANCISCO, CA, US'],\n",
       " [u'5 immigrants in california'],\n",
       " [u'5m water rise'],\n",
       " [u'445 Athens Street'],\n",
       " [u'120 lake street'],\n",
       " [u'19/2015 dashiell hammet'],\n",
       " [u'1 Hawkins Ln SF CA'],\n",
       " [u'1 Hawkins Ln SF CA'],\n",
       " [u'1 Longview Court'],\n",
       " [u'1 hour parking'],\n",
       " [u'1 scott street'],\n",
       " [u'1 scott street'],\n",
       " [u'1 south van ness ave'],\n",
       " [u'1 south vanness'],\n",
       " [u'1. BRIAN DUSSEAULT'],\n",
       " [u'10 gb dataset'],\n",
       " [u'10 glendale street'],\n",
       " [u'10 glendale street'],\n",
       " [u'10 glendale street'],\n",
       " [u'10 glendale street'],\n",
       " [u'10 glendale street'],\n",
       " [u'100 delano ave'],\n",
       " [u'100 delano ave'],\n",
       " [u'100 delano ave'],\n",
       " [u'100 delano ave'],\n",
       " [u'100 delano ave'],\n",
       " [u'100 delano ave'],\n",
       " [u'100 delano ave'],\n",
       " [u'100 year flood'],\n",
       " [u'100 year flood'],\n",
       " [u'100 year flood plain'],\n",
       " [u'100 year storm flood plain'],\n",
       " [u'1000 brannan street'],\n",
       " [u'1008 Santiago street, san francisco'],\n",
       " [u'1008 Santiago street, san francisco'],\n",
       " [u'1008 Santiago street, san francisco'],\n",
       " [u'101 SAN ALESO AVENIE'],\n",
       " [u'1010 fitzgerald ave, san francisco, ca'],\n",
       " [u'1010 fitzgerald ave, san francisco, ca'],\n",
       " [u'1033 polk street'],\n",
       " [u'1033 polk street'],\n",
       " [u'1040 howard st'],\n",
       " [u'1049 Market Street'],\n",
       " [u'1051 de haro'],\n",
       " [u'1051 grant ave, sf'],\n",
       " [u'1051 grant ave, sf'],\n",
       " [u'1051 grant ave, sf'],\n",
       " [u'1051 market st'],\n",
       " [u'106 Powell Street'],\n",
       " [u'106 Powell Street'],\n",
       " [u'1071 Alabama St'],\n",
       " [u'110 sylvan drive, san francisco'],\n",
       " [u'1100 divisadero st'],\n",
       " [u'111 4th ave'],\n",
       " [u'111 powell street'],\n",
       " [u'112 7th street'],\n",
       " [u'1122 Vallejo St'],\n",
       " [u'1131 Mission St'],\n",
       " [u'114 Henry street'],\n",
       " [u'114 powell street, san francisco, ca'],\n",
       " [u'114 powell street, san francisco, ca'],\n",
       " [u'114 powell street, san francisco, ca'],\n",
       " [u'114 powell street, san francisco, ca'],\n",
       " [u'1146a Guerrero Street, San Francisco'],\n",
       " [u'1160 Mission St, San Francisco, cA'],\n",
       " [u'1177 California st'],\n",
       " [u'12 hillview court'],\n",
       " [u'1218 44th Ave., San Francisco'],\n",
       " [u'122 farallones st'],\n",
       " [u'1230 markert st'],\n",
       " [u'1230 markert st'],\n",
       " [u'1230 market st'],\n",
       " [u'1230 market st'],\n",
       " [u'1230 market st'],\n",
       " [u'1231 20th ave, san francisco'],\n",
       " [u'1231 MArket St'],\n",
       " [u'1235 Florida Street'],\n",
       " [u'1238 39th avenue'],\n",
       " [u'129 randall st'],\n",
       " [u'1300 block of mcallister'],\n",
       " [u'1300 block of mcallister'],\n",
       " [u'132 corbett ave'],\n",
       " [u'1327 Leavenworth St'],\n",
       " [u'1328a kobbe avenue'],\n",
       " [u'1328a kobbe avenue'],\n",
       " [u'1343 28th avenue'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'135 Post Street'],\n",
       " [u'1355 market street san francisco'],\n",
       " [u'1355 market street san francisco'],\n",
       " [u'1355 market street san francisco'],\n",
       " [u'1355 market street san francisco'],\n",
       " [u'138 03rd street'],\n",
       " [u'138 03rd street'],\n",
       " [u'138 03rd street'],\n",
       " [u'138 03rd street'],\n",
       " [u'138 03rd street'],\n",
       " [u'138 03rd street'],\n",
       " [u'138 03rd street'],\n",
       " [u'1381 quesada avenue'],\n",
       " [u'1385 carroll ave'],\n",
       " [u'1390 market street'],\n",
       " [u'1390 market street'],\n",
       " [u'140 mason street'],\n",
       " [u'1405 Van Ness Ave'],\n",
       " [u'1405 Van Ness Ave'],\n",
       " [u'1407 EGBERT STREET SAN FRANCISCO'],\n",
       " [u'1409 EGBERT STREET SAN FRANCISCO'],\n",
       " [u'141 Milton Street'],\n",
       " [u'141 Milton Street San Francisco, CA'],\n",
       " [u'141 Milton Street San Francisco, CA'],\n",
       " [u'141 eddy street'],\n",
       " [u'141 eddy street sf ca'],\n",
       " ...]"
      ]
     },
     "execution_count": 417,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 418,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# flatten full list \n",
    "full = sc.parallelize(full).flatMap(lambda row: row).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 419,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1978"
      ]
     },
     "execution_count": 419,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(full)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 420,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.7580993520518358"
      ]
     },
     "execution_count": 420,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(696.0 / 18520.0) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 421,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'141 milton street san francisco, ca',\n",
       " u'220 30th ave',\n",
       " u'631 folsom st',\n",
       " u'939 jackson st',\n",
       " u'1480 larkin st',\n",
       " u'425 10th st',\n",
       " u'135 post street',\n",
       " u'400 duboce street',\n",
       " u'466 9th avenue',\n",
       " u'32 richland avenue',\n",
       " u'56 moss st',\n",
       " u'1. brian dusseault',\n",
       " u'619 union street',\n",
       " u'1700 newhall street',\n",
       " u'3067 25th street san francisco ca',\n",
       " u'20 jones st',\n",
       " u'425 market street energy',\n",
       " u'2201 market street',\n",
       " u'1416 polk street',\n",
       " u'944 treat av',\n",
       " u'3864 23rd street',\n",
       " u'1500 mc allister street san francisco',\n",
       " u'201 hale street',\n",
       " u'1407 egbert street san francisco',\n",
       " u'141 eddy street sf ca',\n",
       " u'5 thomas mellon circle',\n",
       " u'729 shotwell st',\n",
       " u'322 30th ave',\n",
       " u'200 mississippi street',\n",
       " u'521 clement street',\n",
       " u'3143 folsom st',\n",
       " u'33 allston way',\n",
       " u'5300 3rd street',\n",
       " u'1146a guerrero street, san francisco',\n",
       " u'601 van ness ave',\n",
       " u'1630 30th avenue',\n",
       " u'1385 carroll ave',\n",
       " u'3060 16th street building',\n",
       " u'60 agua way',\n",
       " u'111 powell street',\n",
       " u'4182 mission street',\n",
       " u'337 spurce st san francisco ca',\n",
       " u'345 williams avenue',\n",
       " u'68 cervantes blvd',\n",
       " u'00 use: arcmap 1:70,650 scale for this',\n",
       " u'1231 20th ave, san francisco',\n",
       " u'1023 vallejo street',\n",
       " u'13 melrose ave',\n",
       " u'846 junipero serra blvd',\n",
       " u'760 geary steet',\n",
       " u'3864 23rd street ban',\n",
       " u'78 ina court',\n",
       " u'1895 sacramento st',\n",
       " u'430 lake st',\n",
       " u'11025 carolina place pkwy, pineville',\n",
       " u'94 stratford drive',\n",
       " u'162 landers st',\n",
       " u'100 year storm flood plain',\n",
       " u'1798 grove street',\n",
       " u'208 caselli ave',\n",
       " u'1359 sacramento st',\n",
       " u'4 persia avenue',\n",
       " u'1225 17th avenue',\n",
       " u'3 rudden avenue sf ca',\n",
       " u'2385 bush street',\n",
       " u'62 allston way',\n",
       " u'501 the embarcadero; san francisco',\n",
       " u'106 grand view',\n",
       " u'10 glendale street',\n",
       " u'1363 alabama street',\n",
       " u'90 day cases',\n",
       " u'700) - filers with unpaid late filing',\n",
       " u'1000 feet school',\n",
       " u'88 investment partners, llc',\n",
       " u'390 fremont st',\n",
       " u'3247 reserve ct, san jose, ca',\n",
       " u'205 upper terrace',\n",
       " u'4610 mission street',\n",
       " u'3 buena vista ave east, sf',\n",
       " u'123 retiro way',\n",
       " u'1773 chestnut st',\n",
       " u'2130 jackson street',\n",
       " u'308 font blvd',\n",
       " u'1738 ocean avenue san francisco, ca',\n",
       " u'323 cumberland st',\n",
       " u'158 maynarad st',\n",
       " u'5252 mission street',\n",
       " u'83 mc allister st, san francisco, ca',\n",
       " u'282 harbor rd san francsico, ca',\n",
       " u'245 san jose',\n",
       " u'415 jones st',\n",
       " u'534 4th street',\n",
       " u'501 columbus ave',\n",
       " u'64 perine place, sf',\n",
       " u'2915 octavia st',\n",
       " u'735 fell street',\n",
       " u'1683 7th avenue',\n",
       " u'766 vallejo street',\n",
       " u'163 russ st',\n",
       " u'1328a kobbe avenue',\n",
       " u'79 midcrest way',\n",
       " u'374 5th street san francisco',\n",
       " u'55 cyril magnin street',\n",
       " u'2282 market street',\n",
       " u'425 market energy',\n",
       " u'150 lawrence ave',\n",
       " u'4 2014 election',\n",
       " u'933 treat ave',\n",
       " u'1010 fitzgerald ave, san francisco, ca',\n",
       " u'5413 hillside av.,el cerrito',\n",
       " u'407 jackson st',\n",
       " u'548 banks st, san francisco',\n",
       " u'519 capp street san francisco',\n",
       " u'829 york street',\n",
       " u'606 post street',\n",
       " u'2950 vallejo st, sf',\n",
       " u'1917 broderick street',\n",
       " u'460 - schedule e - payments made',\n",
       " u'8300 oceanview terrace, 304, san francisco',\n",
       " u'299 fremont street',\n",
       " u'450 golden gate',\n",
       " u'415 myra way',\n",
       " u'970 key ave',\n",
       " u'1390 market street',\n",
       " u'754 moultrie group',\n",
       " u'2 casa way',\n",
       " u'400 castro soul cycle',\n",
       " u'33 albion street',\n",
       " u'584 filbert street',\n",
       " u'1405 van ness ave',\n",
       " u'1365 noriega street',\n",
       " u'2945 baker st apt',\n",
       " u\"333 o'farrell st, san francisco, ca\",\n",
       " u'800 university avenue, palo alto, california',\n",
       " u'530 brannan street',\n",
       " u'2919 folsom st',\n",
       " u'301 king st',\n",
       " u'2282 market st',\n",
       " u'140 mason street',\n",
       " u'155 arbor st',\n",
       " u'250 executive park',\n",
       " u'3180 18th street, san francisco, ca',\n",
       " u'26 curtis street san francisco',\n",
       " u'2172 pacific avenue',\n",
       " u'576 natoma st, san francisco, ca',\n",
       " u'169 steuart st; san francisco, ca',\n",
       " u'240 2nd street',\n",
       " u'199 new montgomery st',\n",
       " u'1050 sansome st',\n",
       " u'5 zip codes',\n",
       " u'1218 44th ave., san francisco',\n",
       " u'439 duboce ave',\n",
       " u'114 henry street',\n",
       " u'31 mullen ave',\n",
       " u'1514 alemany blvd',\n",
       " u'46 el sereno court',\n",
       " u'355 bocana street',\n",
       " u'8300 oceanview terrace',\n",
       " u'1515 s. van ness',\n",
       " u'456 montgomery street suite 2200 san',\n",
       " u'677 page st',\n",
       " u'899 howard street san francisco, ca',\n",
       " u'1 embarcadero san francisco',\n",
       " u'337 spurce street',\n",
       " u'3067 25th street',\n",
       " u'650 40th ave',\n",
       " u'136 arlington street',\n",
       " u'415 euclid ave',\n",
       " u'1300 block of mcallister',\n",
       " u'397 9th st',\n",
       " u'1442 43rd ave',\n",
       " u'215 niagara ave permit parking',\n",
       " u'1683 42nd ave, san francisco ca',\n",
       " u'1996-2013 city survey',\n",
       " u'1051 grant ave, sf',\n",
       " u'2325 pine street',\n",
       " u'1230 market st',\n",
       " u'3d public art',\n",
       " u'2 plus 2 bookeeping',\n",
       " u'916 pine street',\n",
       " u'444 de haro st',\n",
       " u'560 davis st san francisco',\n",
       " u'929 oak st',\n",
       " u'122 farallones st',\n",
       " u'340 mason street',\n",
       " u'562 monterey blvd, san francisco, ca',\n",
       " u'701 teresita san francisco, ca',\n",
       " u'1188 harrrison street',\n",
       " u'1746 church street, san francisco',\n",
       " u'101 utah street',\n",
       " u'1049 market street',\n",
       " u'19 rausch street',\n",
       " u'258 9th st',\n",
       " u'690 long bridge street',\n",
       " u'3 - independent expenditures made',\n",
       " u'1355 market street san francisco',\n",
       " u'1051 de haro',\n",
       " u'2 2011 pipeline',\n",
       " u'3490 20th st',\n",
       " u'579 teresita blvd',\n",
       " u'2412 harrison st',\n",
       " u'336 moraga ave',\n",
       " u'1 south vanness',\n",
       " u'812 guerrero street',\n",
       " u'260 church st',\n",
       " u'1160 mission st, san francisco, ca',\n",
       " u'43 ruth street, san francisco',\n",
       " u'2240 market street',\n",
       " u'1426 48th avenue',\n",
       " u'360 somerset street',\n",
       " u'388 market street, suite',\n",
       " u'4058 17th st',\n",
       " u'1238 39th avenue',\n",
       " u'2598 mission street',\n",
       " u'1700 owens street',\n",
       " u'487 dolores st',\n",
       " u'136 rivoli street',\n",
       " u'810 clay street, san francisco, ca',\n",
       " u'3750 18 street sf ca',\n",
       " u'701 golden gate av',\n",
       " u'348 church street',\n",
       " u'1 tuscany alley',\n",
       " u'3953 24th street',\n",
       " u'141 eddy street',\n",
       " u'838 grant ave',\n",
       " u'340 mason street investors',\n",
       " u'460 post street business search',\n",
       " u'3401 clement st',\n",
       " u'5 hudson ct, san francisco, ca',\n",
       " u'1100 divisadero st',\n",
       " u'2400 14th avenue',\n",
       " u'2919-21 folsom st',\n",
       " u'665 pine st',\n",
       " u'10 gb dataset',\n",
       " u'462 douglass sr',\n",
       " u'345 spear st',\n",
       " u'462 douglass st',\n",
       " u'90 day emergency shelter waitlist',\n",
       " u'269 joost ave',\n",
       " u'67 silverview drive',\n",
       " u'1740 cesar chavez',\n",
       " u'1 scott street',\n",
       " u'1 polk st',\n",
       " u'1000 howard st',\n",
       " u'1409 egbert street san francisco',\n",
       " u'525 29th st',\n",
       " u'1 2015 pipline',\n",
       " u'460 cover page',\n",
       " u'40 jessie street',\n",
       " u'2923 lawton st',\n",
       " u'201 california owner llc',\n",
       " u'2174 california street',\n",
       " u'50 quint street',\n",
       " u'453 masonic st',\n",
       " u'0 beatrice rd, brisbane, ca',\n",
       " u'201o census population',\n",
       " u'1122 vallejo st',\n",
       " u'929 oak st., sf, ca',\n",
       " u'882 31st ave',\n",
       " u'1358 28th avenue, san francisco, ca',\n",
       " u'186 valley street',\n",
       " u'331 jersey st',\n",
       " u'1975 jim jones',\n",
       " u'390 diamond street',\n",
       " u'555 minnesota street',\n",
       " u'221 san carlos',\n",
       " u'177 stillman street',\n",
       " u'1343 28th avenue',\n",
       " u'690 folsom street',\n",
       " u'80.004 employee compensation report',\n",
       " u'944 treat avenue',\n",
       " u'15% fund raising target',\n",
       " u'74 6th street',\n",
       " u'2283 37th ave',\n",
       " u'354 peralta street',\n",
       " u'284 12th avenue',\n",
       " u'1444 market st',\n",
       " u'1578 newhall st. san francisco',\n",
       " u'2096 castro street',\n",
       " u'3d building models',\n",
       " u'106 powell street',\n",
       " u'2 2015 pipeline',\n",
       " u'403 main st',\n",
       " u'1 hawkins ln sf ca',\n",
       " u'459 geary llc',\n",
       " u'600 san bruno ave',\n",
       " u'3139 mission street',\n",
       " u'469 eddy street',\n",
       " u'1700 california st',\n",
       " u'8300 oceanview terrace 304, san francisco',\n",
       " u'49 christopher dr, san francisco, ca',\n",
       " u'677-679 page st',\n",
       " u'709 geary st',\n",
       " u'270 mcallister street',\n",
       " u'2. increase jobs that provide healthy',\n",
       " u'2124 great hwy',\n",
       " u'278 states street',\n",
       " u'146 galewood circle',\n",
       " u'280 circular ave',\n",
       " u'3d building model',\n",
       " u'1125 17th avenue',\n",
       " u'820 jones street',\n",
       " u'545 sutter street, san francisco',\n",
       " u'1000 brannan street',\n",
       " u'1235 florida street',\n",
       " u'4130 mission street',\n",
       " u'418 jessie st',\n",
       " u'2310 mason street',\n",
       " u'2130 22nd st san francisco ca',\n",
       " u'400 castro street',\n",
       " u'1443 46th ave',\n",
       " u'98 battery st suite',\n",
       " u'301 baltimore way',\n",
       " u'1994 union st',\n",
       " u'1131 mission st',\n",
       " u'2349 francisco st',\n",
       " u'126 fillmore stret',\n",
       " u'2660 san bruno avenue, san francisco',\n",
       " u'2190 lombard st, san francisco, ca',\n",
       " u'1709 broderick street',\n",
       " u'41 genebern way, sf',\n",
       " u'2134 green street #3 san francisco, ca',\n",
       " u'141 milton street',\n",
       " u'465 - supplemental independent expenditure',\n",
       " u'1000 sutter street',\n",
       " u'9 geneva ave',\n",
       " u'8134, united states',\n",
       " u'1033 polk street',\n",
       " u'135 seacliff ave',\n",
       " u'387 ellis street',\n",
       " u'177 stillman st',\n",
       " u'749-757 jackson st',\n",
       " u'5 year plan',\n",
       " u'3434 21st st',\n",
       " u'960 howard street',\n",
       " u'337 spurce streeet',\n",
       " u'543 columbus ave',\n",
       " u'1996-2013 city survey data',\n",
       " u'199 new montgomery',\n",
       " u'177 stillman st., san francisco, ca',\n",
       " u'654 46th avenue',\n",
       " u'1423 golden gate',\n",
       " u'124 molimo dr',\n",
       " u'2090 evans avenue',\n",
       " u'1523 guerrero st',\n",
       " u'357 hayes st',\n",
       " u'1952 larkin street',\n",
       " u'347 precita ave san francisco ca',\n",
       " u'527 42nd ave',\n",
       " u'101 san aleso avenie',\n",
       " u'270 mcallister street, san francisco',\n",
       " u'5400 mission san francisco',\n",
       " u'235 presidio avenue',\n",
       " u'2275 sutter st',\n",
       " u'944 treat ave',\n",
       " u'177 stillman st. sf',\n",
       " u'2462 30th street',\n",
       " u'460 post street',\n",
       " u'675 naples street',\n",
       " u'460 - schedule a - monetary contributions',\n",
       " u'129 randall st',\n",
       " u'2020 ellis st',\n",
       " u'2259 25th ave',\n",
       " u'744 montgomery st',\n",
       " u'334 harriet st',\n",
       " u'2332 taraval st sf',\n",
       " u'160 swiss avenue',\n",
       " u'460 forms schedula',\n",
       " u'1155 folsom st',\n",
       " u'1 hour parking',\n",
       " u'1453 mission st',\n",
       " u'224 ordway st',\n",
       " u'3, 2015 election',\n",
       " u'601 laguna street',\n",
       " u'111 ellis street',\n",
       " u'1455 custer street',\n",
       " u'127 san jose ave',\n",
       " u'857 39th ave',\n",
       " u'240 dolores st',\n",
       " u'1970 census block',\n",
       " u'610 page street',\n",
       " u'5 immigrants in california',\n",
       " u'555 4th st',\n",
       " u'65 and older',\n",
       " u'1618 polk st san francisco, ca, us',\n",
       " u'1415 scott street',\n",
       " u'3060 16th street',\n",
       " u'701 minnesota street',\n",
       " u'595 market street, san francisco, ca',\n",
       " u'3314 steiner st',\n",
       " u'17 silverview drive',\n",
       " u'203 cotter st',\n",
       " u'1155 folsom at',\n",
       " u'460 cover apge',\n",
       " u'3024 mission st',\n",
       " u'111 4th ave',\n",
       " u'619 missouri st., san francisco, ca',\n",
       " u'49 south park st',\n",
       " u'2211 stockton st',\n",
       " u'2135 mission street',\n",
       " u'65 in zip code',\n",
       " u'663 haight street',\n",
       " u'2079 15th avenue',\n",
       " u'1693 7th avenue',\n",
       " u'959 noe street',\n",
       " u'345 williams avenue, san francisco',\n",
       " u'1709 broderick st, san francisco, ca',\n",
       " u'2 hour street parking 2999 california',\n",
       " u'50 main street, san francisco',\n",
       " u'201 california llc',\n",
       " u'177 stillman street, san francisco',\n",
       " u'600 van ness',\n",
       " u'1439 16th ave',\n",
       " u'3333 california street',\n",
       " u'2385 bush st',\n",
       " u'18 latona st',\n",
       " u'508 haight st',\n",
       " u'1177 california st',\n",
       " u'2 crestline dr',\n",
       " u'255 king stree',\n",
       " u'444 townsend st',\n",
       " u'83 california state plane zone',\n",
       " u'74 granada avenue san francisco',\n",
       " u'77 beale street buildings',\n",
       " u'600 fell st',\n",
       " u'1450 green st',\n",
       " u'2950 valajo st, sf',\n",
       " u'1600 fell street',\n",
       " u'445 athens street',\n",
       " u'375 bush street',\n",
       " u'1188 harrison street',\n",
       " u'3000 washington street 4000 washington',\n",
       " u'1 post st',\n",
       " u'83 mcallister st, san francisco',\n",
       " u'333 market street energy',\n",
       " u'424 octavia st',\n",
       " u'246 2nd street',\n",
       " u'689 20th avenue',\n",
       " u'700) filers with unpaid late filing fees',\n",
       " u'2912 19th ave',\n",
       " u'4431 mission st',\n",
       " u'1327 leavenworth st',\n",
       " u'5m water rise',\n",
       " u'333 lake street',\n",
       " u'39 liveaboard marina',\n",
       " u'256 fowler ave',\n",
       " u'1994 carroll avenue',\n",
       " u'361 3rd ave',\n",
       " u'71 ford st',\n",
       " u'2145 19th ave, san francisco, ca',\n",
       " u'2030 alemany blvd',\n",
       " u'573 andover street',\n",
       " u'425 market street',\n",
       " u'626 green street',\n",
       " u'2338 taraval st sf',\n",
       " u'1500 mission street',\n",
       " u'750 sutter st, san francisco, ca',\n",
       " u'560 mission street',\n",
       " u'26 eagle street',\n",
       " u'2500 bryant st, san francisco ca',\n",
       " u'700 pennsylvania ave',\n",
       " u'2 bedroom homesale',\n",
       " u'2911 16th st police',\n",
       " u'3130 webster st san francisco, ca',\n",
       " u'447 broadway street',\n",
       " u'568 brannan st',\n",
       " u'705 vallejo street',\n",
       " u'131 franklin street',\n",
       " u'1801 silliman st',\n",
       " u'1008 santiago street, san francisco',\n",
       " u'1660 stockton street',\n",
       " u'175 ney street',\n",
       " u'3008 18th street',\n",
       " u'355 24th ave',\n",
       " u'622 green street',\n",
       " u'38 air pollutant exposure zone',\n",
       " u'333 market street',\n",
       " u'1 longview court',\n",
       " u'554 irving street, san francisco',\n",
       " u'1939 oakdale ave',\n",
       " u'100 year flood',\n",
       " u'823 lake street',\n",
       " u'2166 32nd ave',\n",
       " u'1996-2011 city survey database',\n",
       " u'1931 35th ave san francisco, ca',\n",
       " u'2243 32nd avenue',\n",
       " u'530 brannan street business lincenses',\n",
       " u'100 delano ave',\n",
       " u'631 cole st',\n",
       " u'169 steuart st',\n",
       " u'350 mission street',\n",
       " u'355 serrano dr',\n",
       " u'1453 mission street',\n",
       " u'2021 baker st',\n",
       " u'126 fillmore street',\n",
       " u'428 chicago way',\n",
       " u'887 45th ave',\n",
       " u'2695 43rd avenue',\n",
       " u'2455 polk street',\n",
       " u'324 arkansas st',\n",
       " u'5: generalized neighborhoods commercial',\n",
       " u'17 san andreas way, san francisco',\n",
       " u'9 county boundary',\n",
       " u'1951 oak st',\n",
       " u'340 warren drive san francisco, ca',\n",
       " u'239 carl street',\n",
       " u'331 jersey st parcel map',\n",
       " u'490 post st',\n",
       " u'163 liberty street',\n",
       " u'4175 mission st',\n",
       " u'2208 mission street',\n",
       " u'5 day water related',\n",
       " u'1560 8th ave',\n",
       " u'711 clayton st',\n",
       " u'1428 jackson st',\n",
       " u'887 4th ave',\n",
       " u'589 post st, san francisco, ca',\n",
       " u'2394 mariner square drive, alameda, ca',\n",
       " u'1813 15th street',\n",
       " u'721 18th avenue',\n",
       " u'810 dolores san francisco, ca',\n",
       " u'543 19th ave',\n",
       " u'342 24th ave',\n",
       " u'2922 sacramento street',\n",
       " u'1158 noe street',\n",
       " u'32 richland avenue steven holland',\n",
       " u'1941 ocean avenue san francisco california',\n",
       " u'855 folsom st, san francisco',\n",
       " u'2 hour parking',\n",
       " u'550 3rd street san francisco ca',\n",
       " u'114 powell street, san francisco, ca',\n",
       " u'3465 25th st',\n",
       " u'625 46th ave',\n",
       " u'138 03rd street',\n",
       " u'460 forms schedul',\n",
       " u'120 lake street',\n",
       " u'407 sanchez street',\n",
       " u'4689 mission st',\n",
       " u'1420 5th ave., san francisco, ca',\n",
       " u'301 claremont blvd',\n",
       " u'469 union st',\n",
       " u'202 4th ave',\n",
       " u'251 frederick st',\n",
       " u'1996-2013 sf city survey',\n",
       " u'1755 clay st',\n",
       " u'2250 powell street',\n",
       " u'112 7th street',\n",
       " u'233 san carlos',\n",
       " u'555 market street',\n",
       " u'177 stillman street report',\n",
       " u'1977 20th avenue san francisco ca',\n",
       " u'285 geary st energy audit',\n",
       " u'5060 west 14th street greeley co',\n",
       " u'100 year flood plain',\n",
       " u'3800 16th street san francisco, ca',\n",
       " u'40 annapolis terrace, san francisco',\n",
       " u'3440 broderick street',\n",
       " u'1 to april',\n",
       " u'62 washburn st',\n",
       " u'3520 18th partners',\n",
       " u'62 linden avenue, san bruno, ca',\n",
       " u'3938 geary blvd. san francisco, ca',\n",
       " u'2200 market permit',\n",
       " u'230 eddy street',\n",
       " u'2279 mission street',\n",
       " u'60 garcia ave',\n",
       " u'44 samoset st',\n",
       " u'1231 market st',\n",
       " u'1600 california street',\n",
       " u'755 union st owner name',\n",
       " u'442 holloway ave., sf',\n",
       " u'355 1st street',\n",
       " u'372 point lobos ave',\n",
       " u'700) non filers',\n",
       " u'2919 mission street',\n",
       " u'1560 howard stret',\n",
       " u'679 san jose san francisco',\n",
       " u'321 marietta drive',\n",
       " u'350 laurel st san francisco',\n",
       " u'2577 1/2 post street',\n",
       " u'2901 sacramento st',\n",
       " u'1230 markert st',\n",
       " u'75 dore street',\n",
       " u'2 2011 pipeline report',\n",
       " u'476 ellington ave',\n",
       " u'340 church st',\n",
       " u'100 church street',\n",
       " u'1412 hudson ave',\n",
       " u'14 street sidewalk evaluation results',\n",
       " u'267 green street',\n",
       " u'550 van ness',\n",
       " u'77 san juan ave',\n",
       " u'307 niagra ave',\n",
       " u'252 27th ave, san francisco, ca',\n",
       " u'329 bryant street san francisco, ca',\n",
       " u'360 somerset st',\n",
       " u'3520 18th partners business account number',\n",
       " u'679 page street',\n",
       " u'739 washington street, suite',\n",
       " u'83 mc allister st 513, san francisco',\n",
       " u'822 irving st',\n",
       " u'288 gold mine drive',\n",
       " u'313 potrero ave',\n",
       " u'3092 16th st',\n",
       " u'1040 howard st',\n",
       " u'5800 3rd st',\n",
       " u'202 24th ave sf',\n",
       " u'478 utah st',\n",
       " u'1560 howard street',\n",
       " u'49 mile scenic drive',\n",
       " u'460 forms schedule',\n",
       " u'405 howard st',\n",
       " u'300 sea cliff',\n",
       " u'40 genebern way, sf',\n",
       " u'1381 quesada avenue',\n",
       " u'470 lake st',\n",
       " u'347-349 precita ave',\n",
       " u'1 south van ness ave',\n",
       " u'409 dolores street',\n",
       " u'200 6th st',\n",
       " u'110 sylvan drive, san francisco',\n",
       " u'258 9 th st',\n",
       " u'2650 green, sf',\n",
       " u'487 dolores street',\n",
       " u'198 waterville street, san francisco',\n",
       " u'277 columbus ave',\n",
       " u'285 geary st',\n",
       " u'815 laguna honda',\n",
       " u'20 roussea street',\n",
       " u'19/2015 dashiell hammet',\n",
       " u'1660 stockton at',\n",
       " u'29 downey st',\n",
       " u'1427 11th ave',\n",
       " u'315 28th avenue',\n",
       " u'460 dwight street',\n",
       " u'350 2nd st',\n",
       " u'1805 s. van ness',\n",
       " u'150 lawrence ave san francisco',\n",
       " u'140 new montgomery st',\n",
       " u'155 park st san francsico ca',\n",
       " u'544 castro st',\n",
       " u'1432 grove street san francisco ca',\n",
       " u'460 - schedule a - payments made',\n",
       " u'376 ellis street',\n",
       " u'620 green street',\n",
       " u'495 geary street',\n",
       " u'2652 harrison st',\n",
       " u'200 hazelwood ave',\n",
       " u'83 leo street',\n",
       " u'153 college ave',\n",
       " u'1071 alabama st',\n",
       " u'1865 4th st, san francisco',\n",
       " u'3951 mission st',\n",
       " u'838 detroit street',\n",
       " u'2400 california st san francisco ca',\n",
       " u'72 hour parking restriction',\n",
       " u'387 ellis street, san francisco, ca',\n",
       " u'163 ney st',\n",
       " u'755 union st',\n",
       " u'42 jules avenue',\n",
       " u'12 hillview court',\n",
       " u'1051 market st',\n",
       " u'1900 ferry route',\n",
       " u'317 oak park dr',\n",
       " u'132 corbett ave',\n",
       " u'0 miles 1 note: for neighborhood commercial',\n",
       " u'353 beach rd, burlingame, ca']"
      ]
     },
     "execution_count": 421,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "year_full = [str(j) for j in range(2000,2017)]\n",
    "# taking out the years\n",
    "\n",
    "addresses_full = [i.lower() for i in full if i[:4] not in year ]\n",
    "addresses_full = list(set(addresses_full))\n",
    "addresses_full"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 422,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'2000 block group',\n",
       " u'2007 housing inventory',\n",
       " u'2010 census block for san francisco',\n",
       " u'2000 blocks (no water',\n",
       " u'2010 census tracts no water',\n",
       " u'2010: census blocks for san',\n",
       " u'2016 assessor map',\n",
       " u'2010 census population',\n",
       " u'2015 crime statistics',\n",
       " u'2014 energy benchmark 285 geary',\n",
       " u'2009 sfo survey',\n",
       " u'2015 enacted budget',\n",
       " u'2010 tract population',\n",
       " u'2015 housing inventory',\n",
       " u'2013 city survey report',\n",
       " u'2012 campaign finance',\n",
       " u'2005 pipeline report',\n",
       " u'2000 tracts no water',\n",
       " u'2014 city serve report',\n",
       " u'2016 budget publication',\n",
       " u'2000 census tract',\n",
       " u'2011 mayor campaign contributions',\n",
       " u'2000 block goup',\n",
       " u'2012 sfo customer survey',\n",
       " u'2015 richmond district',\n",
       " u'2010 demographic profile data, sf county',\n",
       " u'2015 in san francisco',\n",
       " u'2015 form 460; schedule',\n",
       " u'2014 map:crime incidents',\n",
       " u'2011 herrera campaign contributions',\n",
       " u'2015 residential development pipeline',\n",
       " u'2004 bicycle network',\n",
       " u'2010 vehicle ownership',\n",
       " u'2008 crime data',\n",
       " u'2015 murder by race statistics',\n",
       " u'2000 census tracts',\n",
       " u'2000 census shapefile',\n",
       " u'2010 census block',\n",
       " u'2010: census block groups',\n",
       " u'2010: census blocks',\n",
       " u'2012 housing inventory',\n",
       " u'2013 housing inventory',\n",
       " u'2015 budget information',\n",
       " u'2010 block groups',\n",
       " u'2000 block (no water',\n",
       " u'2011 customer survey dataset',\n",
       " u'2013 building permits',\n",
       " u'2011 herrera mayor campaign contributions',\n",
       " u'2010 census blocks',\n",
       " u'2000 census tract shapefile',\n",
       " u'2014 q4 planning pipeline',\n",
       " u'2015 q4 pipeline',\n",
       " u'2010: census blocks for san francisco',\n",
       " u'2010 population shapefile',\n",
       " u'2000 blocks (no water) census',\n",
       " u'2000 blocks no water',\n",
       " u'2010 census blocks for san francisco',\n",
       " u'2015 311 budget',\n",
       " u'2009 housing inventory',\n",
       " u'2015 city survey',\n",
       " u'2000 no water',\n",
       " u'2014 census tracts',\n",
       " u'2004 food vendors',\n",
       " u'2014 housing inventory',\n",
       " u'2000 population density',\n",
       " u'2010 census block centroids',\n",
       " u'2000 tracts (no water',\n",
       " u'2010 us census data',\n",
       " u'2005 housing inventory',\n",
       " u'2016 housing development',\n",
       " u'2010 map:crime incidents',\n",
       " u'2016 feb 26 arrests in bay view',\n",
       " u'2013 sfpd incidents',\n",
       " u'2016 assessor map updates',\n",
       " u'2010 census house hold data by taz',\n",
       " u'2015 neighborhood profiles',\n",
       " u'2010 census tracts',\n",
       " u'2013 census shapefile',\n",
       " u'2011 pipeline report',\n",
       " u'2013 sfo customer survey',\n",
       " u'2004 bike network',\n",
       " u'2012 census population']"
      ]
     },
     "execution_count": 422,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "year_listing = [i.lower() for i in full if i[:4] in year ]\n",
    "year_listing = list(set(year_listing))\n",
    "year_listing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 423,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "clean_list = sc.parallelize(query_list).filter(lambda word: word.lower() not in addresses_full).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 424,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "links = \"((https?|http):((//)|(\\\\\\\\))+[\\w\\d:#@%/;$()~_?\\+-=\\\\\\.&]*)\"\n",
    "\n",
    "https = [re.findall(links, i) for i in clean_list\n",
    "     if re.findall(links, i)!= [] \n",
    "     if re.findall(links, i)!= \"//\" \n",
    "     ]\n",
    "# http://stackoverflow.com/questions/6718633/python-regular-expression-again-match-url"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 425,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# finding all the unique links that people have put into the search query\n",
    "\n",
    "https = (sc.parallelize(https).flatMap(lambda word: word)\n",
    "        .flatMap(lambda word: word)\n",
    "        .filter(lambda word: word != '//')\n",
    "        .filter(lambda word: word != 'https')\n",
    "        .filter(lambda word: word != '''''')\n",
    "        .filter(lambda word: word != 'http')\n",
    "        .collect())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 426,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'https://data.sfgov.org/Public-Health/Excessive-Rent-Burden-San-Francisco-CA/9wty-qwgq',\n",
       " u'https://data.sfgov.org/Public-Health/Restaurant-Scores/stya-26eb',\n",
       " u'http://googlewebmastercentral.blogspot.com/2014/11/helping-users-find-mobile-friendly-pages.html',\n",
       " u'http://www.bsis.ca.gov/forms_pubs/fire_fact.shtml',\n",
       " u'http://www.datasf.org/story.php?title=street-sweeper-schedule-and-route-',\n",
       " u'https://10.183.241.201/rgcertprintv2default.aspx',\n",
       " u'https://data.sfgov.org/Economy-and-Community/Off-Sale-Alcohol-Outlets-San-Francisco-CA/fIy-9zhp',\n",
       " u'https://data.sfgov.org/Economy-and-Community/Registered-Business-Locations-San-Francisco/g8m3-pdis',\n",
       " u'https://data.sfgov.org/Economy-and-Community/Registered-Business-Locations-San-Francisco/g8m3-pdis?',\n",
       " u'https://data.sfgov.org/d/ejmn-jyk6',\n",
       " u'https://data.sfgov.org/data?category=',\n",
       " u'https://data.sfgov.org/data?category=Transportation',\n",
       " u'https://extxfer.sfdph.org/food/',\n",
       " u'https://www.fiverr.com/kawsarhossain',\n",
       " u'https://www.kaggle.com/c/sf-crime',\n",
       " u'https://data.sfgov.org/Public-Health/Excessive-Rent-Burden-San-Francisco-CA/9wty-qwgq',\n",
       " u'http://googlewebmastercentral.blogspot.com/2014/11/helping-users-find-mobile-friendly-pages.html',\n",
       " u'http://googlewebmastercentral.blogspot.com/2014/11/helping-users-find-mobile-friendly-pages.html',\n",
       " u'http://googlewebmastercentral.blogspot.com/2014/11/helping-users-find-mobile-friendly-pages.html',\n",
       " u'http://googlewebmastercentral.blogspot.com/2014/11/helping-users-find-mobile-friendly-pages.html',\n",
       " u'http://googlewebmastercentral.blogspot.com/2014/11/helping-users-find-mobile-friendly-pages.html',\n",
       " u'http://www.bsis.ca.gov/forms_pubs/fire_fact.shtml',\n",
       " u'https://10.183.241.201/rgcertprintv2default.aspx',\n",
       " u'https://10.183.241.201/rgcertprintv2default.aspx',\n",
       " u'https://10.183.241.201/rgcertprintv2default.aspx',\n",
       " u'https://data.sfgov.org/Economy-and-Community/Off-Sale-Alcohol-Outlets-San-Francisco-CA/fIy-9zhp',\n",
       " u'https://data.sfgov.org/Economy-and-Community/Registered-Business-Locations-San-Francisco/g8m3-pdis?',\n",
       " u'https://data.sfgov.org/d/ejmn-jyk6',\n",
       " u'https://data.sfgov.org/data?category=Transportation',\n",
       " u'https://www.fiverr.com/kawsarhossain',\n",
       " u'https://www.fiverr.com/kawsarhossain',\n",
       " u'https://www.fiverr.com/kawsarhossain',\n",
       " u'https://www.fiverr.com/kawsarhossain',\n",
       " u'https://www.fiverr.com/kawsarhossain',\n",
       " u'https://www.fiverr.com/kawsarhossain']"
      ]
     },
     "execution_count": 426,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "https"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 427,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "clean_list = (sc.parallelize(query_list).map(lambda word: word.lower()).filter(lambda word: word not in https)\n",
    "              .filter(lambda word: word not in addresses_full)\n",
    "              .collect())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 430,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from collections import Counter\n",
    "word_count = Counter(clean_list)\n",
    "word_count_sorted = sorted(word_count.items(),key = lambda x: x[1], reverse=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 431,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'crime',\n",
       " u'streets',\n",
       " u'shapefile',\n",
       " u'street',\n",
       " u'parking',\n",
       " u'census',\n",
       " u'bike',\n",
       " u'business',\n",
       " u'building',\n",
       " u'water',\n",
       " u'neighborhood',\n",
       " u'parks',\n",
       " u'population',\n",
       " u'311',\n",
       " u'traffic',\n",
       " u'bicycle',\n",
       " u'muni',\n",
       " u'parcel',\n",
       " u'income',\n",
       " u'fire',\n",
       " u'housing',\n",
       " u'school',\n",
       " u'rent',\n",
       " u'bart',\n",
       " u'zoning',\n",
       " u'land use',\n",
       " u'neighborhoods',\n",
       " u'bus',\n",
       " u'gis',\n",
       " u'parcels',\n",
       " u'homeless',\n",
       " u'elevation',\n",
       " u'food',\n",
       " u'restaurant',\n",
       " u'buildings',\n",
       " u'san francisco',\n",
       " u'boundary',\n",
       " u'pipeline',\n",
       " u'roads',\n",
       " u'police',\n",
       " u'schools',\n",
       " u'park',\n",
       " u'address',\n",
       " u'transit',\n",
       " u'budget',\n",
       " u'assessor',\n",
       " u'tree',\n",
       " u'transportation',\n",
       " u'taxi',\n",
       " u'sfmta',\n",
       " u'restaurants',\n",
       " u'building footprints',\n",
       " u'property',\n",
       " u'city',\n",
       " u'trees',\n",
       " u'pedestrian',\n",
       " u'construction',\n",
       " u'health',\n",
       " u'businesses',\n",
       " u'shapefiles',\n",
       " u'bridge',\n",
       " u'block',\n",
       " u'education',\n",
       " u'business license',\n",
       " u'bay area',\n",
       " u'district',\n",
       " u'graffiti',\n",
       " u'building permits',\n",
       " u'building footprint',\n",
       " u'street cleaning',\n",
       " u'road',\n",
       " u'sfpd',\n",
       " u'permit',\n",
       " u'census tract',\n",
       " u'sewer',\n",
       " u'blocks',\n",
       " u'noise',\n",
       " u'car',\n",
       " u'map',\n",
       " u'city boundary',\n",
       " u'hospital',\n",
       " u'districts',\n",
       " u'airport',\n",
       " u'zip code',\n",
       " u'topography',\n",
       " u'business licenses',\n",
       " u'open space',\n",
       " u'affordable housing',\n",
       " u'contour',\n",
       " u'accidents',\n",
       " u'real estate',\n",
       " u'permits',\n",
       " u'eviction',\n",
       " u'basemap',\n",
       " u'parking meter',\n",
       " u'sfo',\n",
       " u'alcohol',\n",
       " u'salary',\n",
       " u'aerial',\n",
       " u'parking meters',\n",
       " u'demographics',\n",
       " u'shoreline',\n",
       " u'accident',\n",
       " u'campaign finance',\n",
       " u'flood',\n",
       " u'air',\n",
       " u'tax',\n",
       " u'zip',\n",
       " u'demographic',\n",
       " u'contours',\n",
       " u'evictions',\n",
       " u'energy',\n",
       " u'assessment',\n",
       " u'weather',\n",
       " u'age',\n",
       " u'centerline',\n",
       " u'bus stops',\n",
       " u'art',\n",
       " u'census shapefile',\n",
       " u'employment',\n",
       " u'streets of san francisco',\n",
       " u'footprint',\n",
       " u'planning',\n",
       " u'addresses',\n",
       " u'air quality',\n",
       " u'centerlines',\n",
       " u'bay',\n",
       " u'sidewalk',\n",
       " u'census tracts',\n",
       " u'open spaces',\n",
       " u'bike lanes',\n",
       " u'cancer',\n",
       " u'hotel',\n",
       " u'landuse',\n",
       " u'lobbyist',\n",
       " u'zip codes',\n",
       " u'waste',\n",
       " u'city lots',\n",
       " u'coffee',\n",
       " u'business registration',\n",
       " u'development pipeline',\n",
       " u'public schools',\n",
       " u'library',\n",
       " u'boundaries',\n",
       " u'dem',\n",
       " u'bars',\n",
       " u'census 2010',\n",
       " u'facilities',\n",
       " u'street sweeping',\n",
       " u'poverty',\n",
       " u'building permit',\n",
       " u'census 2000',\n",
       " u'hospitals',\n",
       " u'housing inventory',\n",
       " u'cleaning',\n",
       " u'meter',\n",
       " u'census block',\n",
       " u'base map',\n",
       " u'density',\n",
       " u'wind',\n",
       " u'population density',\n",
       " u'bike parking',\n",
       " u'cnn',\n",
       " u'sidewalks',\n",
       " u'topo',\n",
       " u'bike network',\n",
       " u'climate',\n",
       " u'kml',\n",
       " u'curb',\n",
       " u'460',\n",
       " u'business account number',\n",
       " u'vacant',\n",
       " u'public transportation',\n",
       " u'highway',\n",
       " u'bikeway',\n",
       " u'retail',\n",
       " u'crimes',\n",
       " u'property assessment',\n",
       " u'uber',\n",
       " u'recreation',\n",
       " u'employee',\n",
       " u'json',\n",
       " u'tourism',\n",
       " u'parking tickets',\n",
       " u'911',\n",
       " u'race',\n",
       " u'cad',\n",
       " u'soil',\n",
       " u'children',\n",
       " u'curbs',\n",
       " u'city limits',\n",
       " u'police districts',\n",
       " u'sales',\n",
       " u'public',\n",
       " u'street parking',\n",
       " u'chinatown',\n",
       " u'zoning districts',\n",
       " u'building height',\n",
       " u'tickets',\n",
       " u'intersections',\n",
       " u'community',\n",
       " u'caltrain',\n",
       " u'csv',\n",
       " u'citation',\n",
       " u'shore',\n",
       " u'supervisor',\n",
       " u'food truck',\n",
       " u'height',\n",
       " u'rental',\n",
       " u'human waste',\n",
       " u'college',\n",
       " u'light',\n",
       " u'job',\n",
       " u'address locator',\n",
       " u'commercial',\n",
       " u'earthquake',\n",
       " u'campaign',\n",
       " u'ban',\n",
       " u'tow',\n",
       " u'vegetation',\n",
       " u'lots',\n",
       " u'survey',\n",
       " u'residential',\n",
       " u'rail',\n",
       " u'bridges',\n",
       " u'hiv',\n",
       " u'shelter',\n",
       " u'registered business',\n",
       " u'poop',\n",
       " u'footprints',\n",
       " u'crime data',\n",
       " u'fire incidents',\n",
       " u'imagery',\n",
       " u'2015',\n",
       " u'vehicle',\n",
       " u'trail',\n",
       " u'shp',\n",
       " u'base',\n",
       " u'historic',\n",
       " u'complaints',\n",
       " u'bay area general',\n",
       " u'street names',\n",
       " u'tenderloin',\n",
       " u'tract',\n",
       " u'inventory',\n",
       " u'port',\n",
       " u'slope',\n",
       " u'open business',\n",
       " u'sfpd incidents',\n",
       " u'water bodies in san francisco',\n",
       " u'film',\n",
       " u'homicide',\n",
       " u'bicycle parking',\n",
       " u'liquor',\n",
       " u'incidents',\n",
       " u'intersection',\n",
       " u'orthophoto',\n",
       " u'food trucks',\n",
       " u'libraries',\n",
       " u'land',\n",
       " u'vendor',\n",
       " u'mobile food',\n",
       " u'garbage',\n",
       " u'price',\n",
       " u'building heights',\n",
       " u'bus stop',\n",
       " u'gps',\n",
       " u'hotels',\n",
       " u'sf shoreline and islands',\n",
       " u'location',\n",
       " u'ortho',\n",
       " u'2014',\n",
       " u'inclusionary housing',\n",
       " u'ethnicity',\n",
       " u'shape file',\n",
       " u'street light',\n",
       " u'events',\n",
       " u'bike lane',\n",
       " u'sffind neighborhoods',\n",
       " u'route',\n",
       " u'contracts',\n",
       " u'insurance',\n",
       " u'violations',\n",
       " u'mission bay',\n",
       " u'development',\n",
       " u'rent control',\n",
       " u'green',\n",
       " u'street centerlines',\n",
       " u'planning neighborhoods',\n",
       " u'street trees',\n",
       " u'violation',\n",
       " u'bayview',\n",
       " u'childcare',\n",
       " u'lidar',\n",
       " u'bus routes',\n",
       " u'census blocks',\n",
       " u'3d',\n",
       " u'community gardens',\n",
       " u'cable',\n",
       " u'animal',\n",
       " u'registered businesses',\n",
       " u'board of supervisors',\n",
       " u'speed',\n",
       " u'popos',\n",
       " u'sfpark',\n",
       " u'fire stations',\n",
       " u'calls for service',\n",
       " u'shoreline shapefile',\n",
       " u'public space',\n",
       " u'grocery',\n",
       " u'airbnb',\n",
       " u'traffic counts',\n",
       " u'sfshore',\n",
       " u'ambulance',\n",
       " u'asthma',\n",
       " u'freeway',\n",
       " u'sffd',\n",
       " u'pollution',\n",
       " u'salaries',\n",
       " u'median income',\n",
       " u'inclusionary',\n",
       " u'block group',\n",
       " u'trails',\n",
       " u'supervisor districts',\n",
       " u'utilities',\n",
       " u'house',\n",
       " u'ferry',\n",
       " u'property tax',\n",
       " u'presidio',\n",
       " u'utility',\n",
       " u'museum',\n",
       " u'affordable',\n",
       " u'collision',\n",
       " u'green space',\n",
       " u'meters',\n",
       " u'disabled',\n",
       " u'stclines',\n",
       " u'highways',\n",
       " u'bikes',\n",
       " u'water bodies',\n",
       " u'dpw',\n",
       " u'treasure island',\n",
       " u'jobs',\n",
       " u'solar',\n",
       " u'trash',\n",
       " u'incident',\n",
       " u'bike share',\n",
       " u'street map',\n",
       " u'compensation',\n",
       " u'ocean',\n",
       " u'wifi',\n",
       " u'ticket',\n",
       " u'license',\n",
       " u'public housing',\n",
       " u'taz',\n",
       " u'homelessness',\n",
       " u'citations',\n",
       " u'spending',\n",
       " u'mta',\n",
       " u'traffic density',\n",
       " u'coast',\n",
       " u'colleges',\n",
       " u'shape',\n",
       " u'tow zone',\n",
       " u'census 2000 tracts (no water)',\n",
       " u'garden',\n",
       " u'business registration certificate',\n",
       " u'network',\n",
       " u'bay area - general',\n",
       " u'wireless',\n",
       " u'sfmta bikeway network',\n",
       " u'internet',\n",
       " u'.shp',\n",
       " u'public art',\n",
       " u'crime incidents',\n",
       " u'inspection',\n",
       " u'building age',\n",
       " u'county',\n",
       " u'sanfrancisco.gdb',\n",
       " u'unemployment',\n",
       " u'temperature',\n",
       " u'supervisor district',\n",
       " u'real time',\n",
       " u'housing prices',\n",
       " u'article 11',\n",
       " u'city survey',\n",
       " u'death',\n",
       " u'bicycle accidents',\n",
       " u'topographic',\n",
       " u'zipcode',\n",
       " u'seismic',\n",
       " u'murder',\n",
       " u'fire department',\n",
       " u'electricity',\n",
       " u'traffic count',\n",
       " u'parcel data',\n",
       " u'emergency',\n",
       " u'property assessment roll',\n",
       " u'lyft',\n",
       " u'stormwater',\n",
       " u'theft',\n",
       " u'property value',\n",
       " u'movie',\n",
       " u'farmers market',\n",
       " u'terrain',\n",
       " u'medical',\n",
       " u'form 700',\n",
       " u'electricity use',\n",
       " u'traffic accidents',\n",
       " u'city blocks',\n",
       " u'household income',\n",
       " u'apn',\n",
       " u'pbc',\n",
       " u'san francisco crime',\n",
       " u'dbi',\n",
       " u'rain',\n",
       " u'fire station',\n",
       " u'bernal lot size',\n",
       " u'right of way',\n",
       " u'citylots',\n",
       " u'311 cases',\n",
       " u'child',\n",
       " u'benches',\n",
       " u'open',\n",
       " u'church',\n",
       " u'article 10',\n",
       " u'crime rate',\n",
       " u'sro',\n",
       " u'camera',\n",
       " u'census 2000 blocks (no water)',\n",
       " u'payroll',\n",
       " u'parking lots',\n",
       " u'berkeley',\n",
       " u'telegraph hill',\n",
       " u'dui',\n",
       " u'something',\n",
       " u'arrests',\n",
       " u'speed limit',\n",
       " u'truck',\n",
       " u'apartment buildings',\n",
       " u'image',\n",
       " u'sweeping',\n",
       " u'injury',\n",
       " u'north beach',\n",
       " u'watershed',\n",
       " u'cafe',\n",
       " u'sf solar map',\n",
       " u'job density',\n",
       " u'china',\n",
       " u'car ownership',\n",
       " u'obesity',\n",
       " u'hydrology',\n",
       " u'paving',\n",
       " u'employees',\n",
       " u'market',\n",
       " u'oakland',\n",
       " u'sanfrancisco',\n",
       " u'border',\n",
       " u'dog',\n",
       " u'parking citation',\n",
       " u'auto',\n",
       " u'streets shapefile',\n",
       " u'case',\n",
       " u'complaint',\n",
       " u'vendors',\n",
       " u'street of san francisco',\n",
       " u'api',\n",
       " u'tech',\n",
       " u'vote',\n",
       " u'air pollution',\n",
       " u'child care',\n",
       " u'bay trail',\n",
       " u'parking ticket',\n",
       " u'police district',\n",
       " u'sfusd',\n",
       " u'census 2000 tracts',\n",
       " u'zone',\n",
       " u'film locations',\n",
       " u'station',\n",
       " u'parking citations',\n",
       " u'landmark',\n",
       " u'ada',\n",
       " u'eas',\n",
       " u'year built',\n",
       " u'geojson',\n",
       " u'food inspection',\n",
       " u'ethics',\n",
       " u'feces',\n",
       " u'dpt_bike_network',\n",
       " u'drug',\n",
       " u'airports',\n",
       " u'geology',\n",
       " u'envista',\n",
       " u'census 2000 blocks',\n",
       " u'photo',\n",
       " u'bicycle accident',\n",
       " u'university',\n",
       " u'trimmers',\n",
       " u'outline',\n",
       " u'finance',\n",
       " u'train',\n",
       " u'election',\n",
       " u'contract',\n",
       " u'speeding',\n",
       " u'hazard',\n",
       " u'lobby',\n",
       " u'assessment roll',\n",
       " u'social',\n",
       " u'fte',\n",
       " u'marriage',\n",
       " u'precinct',\n",
       " u'bird',\n",
       " u'bank',\n",
       " u'cab',\n",
       " u'bus lines',\n",
       " u'bar',\n",
       " u'cable car',\n",
       " u'ridership',\n",
       " u'revenue',\n",
       " u'housing price',\n",
       " u'police stations',\n",
       " u'travel',\n",
       " u'wine',\n",
       " u'lgbt',\n",
       " u'flooding',\n",
       " u'open business locations',\n",
       " u'center lines',\n",
       " u'dumping',\n",
       " u'vendor payments',\n",
       " u'sf shore',\n",
       " u'pipeline 2015',\n",
       " u'san francisco streets',\n",
       " u'chicago',\n",
       " u'coastal',\n",
       " u'biz point',\n",
       " u'road map',\n",
       " u'pipe',\n",
       " u'recycling',\n",
       " u'campaign consultant',\n",
       " u'street network',\n",
       " u'street centerline',\n",
       " u'union square',\n",
       " u'lot',\n",
       " u'city features',\n",
       " u'bench',\n",
       " u'biking',\n",
       " u'bike theft',\n",
       " u'movies',\n",
       " u'public transit',\n",
       " u'stations',\n",
       " u'census2000',\n",
       " u'services',\n",
       " u'liquefaction',\n",
       " u'toilet',\n",
       " u'sweep',\n",
       " u'sfpuc',\n",
       " u'94110',\n",
       " u'streetlight',\n",
       " u'technology',\n",
       " u'street shapefile',\n",
       " u'planning districts',\n",
       " u'advisories',\n",
       " u'garage',\n",
       " u'traffic accident',\n",
       " u'tobacco',\n",
       " u'allergies',\n",
       " u'on street parking',\n",
       " u'universities',\n",
       " u'google',\n",
       " u'arterial',\n",
       " u'bathroom',\n",
       " u'lobbyists',\n",
       " u'gis data',\n",
       " u'transit stops',\n",
       " u'routes',\n",
       " u'agriculture',\n",
       " u'form 460',\n",
       " u'bus accidents',\n",
       " u'bike shapefile',\n",
       " u'transport',\n",
       " u'lobbyist activity',\n",
       " u'high injury',\n",
       " u'latitude',\n",
       " u'data catalog',\n",
       " u'museums',\n",
       " u'2010 census',\n",
       " u'open spaces city planning',\n",
       " u'crime map',\n",
       " u'pole',\n",
       " u'avenida epitacio pessoa ipanema rio de janeiro',\n",
       " u'parcel map',\n",
       " u'storm',\n",
       " u'dataset inventory',\n",
       " u'castro',\n",
       " u'address points',\n",
       " u'health care',\n",
       " u'2010',\n",
       " u'health score',\n",
       " u'bike route',\n",
       " u'water usage',\n",
       " u'use',\n",
       " u'business locations',\n",
       " u'inspections',\n",
       " u'economic',\n",
       " u'busines',\n",
       " u'twitter',\n",
       " u'cafe bakery',\n",
       " u'collisions',\n",
       " u'farmers markets',\n",
       " u'active business',\n",
       " u'maher',\n",
       " u'elections',\n",
       " u'raster',\n",
       " u'chinese',\n",
       " u'illegal dumping',\n",
       " u'canopy',\n",
       " u'traffic volume',\n",
       " u'cannabis',\n",
       " u'owner',\n",
       " u'city limit',\n",
       " u'restaurant inspection',\n",
       " u'clean',\n",
       " u'speed limits',\n",
       " u'sf development pipeline',\n",
       " u'maps',\n",
       " u'general plan',\n",
       " u'rent board',\n",
       " u'business district',\n",
       " u'landmarks',\n",
       " u'coastal zone',\n",
       " u'sea level rise',\n",
       " u'curb ramp',\n",
       " u'property owner',\n",
       " u'cities',\n",
       " u'avl',\n",
       " u'pavement',\n",
       " u'bayview hunters point',\n",
       " u'data',\n",
       " u'gdp',\n",
       " u'at',\n",
       " u'building outline',\n",
       " u'sfdph',\n",
       " u'census 2000 block group',\n",
       " u'employee compensation',\n",
       " u'beach',\n",
       " u'restaurant inspections',\n",
       " u'security',\n",
       " u'schedule e',\n",
       " u'cost',\n",
       " u'fires',\n",
       " u'landslide',\n",
       " u'muni routes',\n",
       " u'street lights',\n",
       " u'bus route',\n",
       " u'sf311',\n",
       " u'wind monitoring',\n",
       " u'new york',\n",
       " u'home',\n",
       " u'public works',\n",
       " u'liquor store',\n",
       " u'drainage',\n",
       " u'environment',\n",
       " u'brownfield',\n",
       " u'community center',\n",
       " u'san francisco shoreline',\n",
       " u'affordability',\n",
       " u'street sweep',\n",
       " u'property values',\n",
       " u'subway',\n",
       " u'city boundaries',\n",
       " u'expenditures',\n",
       " u'center line',\n",
       " u'business location',\n",
       " u'vmt',\n",
       " u'economy',\n",
       " u'tree canopy',\n",
       " u'area',\n",
       " u'mobile',\n",
       " u'pipeline 2012',\n",
       " u'healthcare',\n",
       " u'road network',\n",
       " u'lights',\n",
       " u'digital elevation model',\n",
       " u'baker places',\n",
       " u'parking restrictions',\n",
       " u'performance',\n",
       " u'cctv',\n",
       " u'building outlines',\n",
       " u'clinic',\n",
       " u'tourist',\n",
       " u'street tree',\n",
       " u'sanitation',\n",
       " u'traffic tickets',\n",
       " u'plant',\n",
       " u'prostitution',\n",
       " u'basil thai',\n",
       " u'university of san francisco',\n",
       " u'workers',\n",
       " u'point',\n",
       " u'household',\n",
       " u'facility',\n",
       " u'altitude',\n",
       " u'time',\n",
       " u'parklet',\n",
       " u'preservation',\n",
       " u'parklets',\n",
       " u'water quality',\n",
       " u'eviction notices',\n",
       " u'public health',\n",
       " u'counties',\n",
       " u'bikeway network',\n",
       " u'churches',\n",
       " u'ems',\n",
       " u'birth',\n",
       " u'nonprofit',\n",
       " u'muni stops',\n",
       " u'maintenance',\n",
       " u'cell',\n",
       " u'benchmark',\n",
       " u'pothole',\n",
       " u'car sharing',\n",
       " u'san francisco boundary',\n",
       " u'lobbying',\n",
       " u'street center',\n",
       " u'shop',\n",
       " u'traffic lights',\n",
       " u'commute',\n",
       " u'precipitation',\n",
       " u'2000',\n",
       " u'recology',\n",
       " u'shooting',\n",
       " u'street maps',\n",
       " u'cases',\n",
       " u'cbd',\n",
       " u'gentrification',\n",
       " u'low income',\n",
       " u'tracts',\n",
       " u'460 e',\n",
       " u'precincts',\n",
       " u'certified payroll',\n",
       " u'aspect',\n",
       " u'golden gate',\n",
       " u'animals',\n",
       " u'motorcycle',\n",
       " u'vacancy',\n",
       " u'gardens',\n",
       " u'cell phone',\n",
       " u'tax lots',\n",
       " u'test',\n",
       " u'sffacs',\n",
       " u'grocery stores',\n",
       " u'commuting',\n",
       " u'non profit',\n",
       " u'coastline',\n",
       " u'buisness',\n",
       " u'pipeline 2013',\n",
       " u'san francisco map',\n",
       " u'bike accidents',\n",
       " u'restroom',\n",
       " u'railroad',\n",
       " u'vision zero',\n",
       " u'drain',\n",
       " u'youth',\n",
       " u'prices',\n",
       " u'aadt',\n",
       " u'names',\n",
       " u'street sweeping schedule',\n",
       " u'adt',\n",
       " u'sf shoreline',\n",
       " u'appeals',\n",
       " u'fiber',\n",
       " u'condominium',\n",
       " u'building construction',\n",
       " u'ethnic',\n",
       " u'river',\n",
       " u'burglary',\n",
       " u'police station',\n",
       " u'small business',\n",
       " u'below market rate',\n",
       " u'median rent',\n",
       " u'food inspections',\n",
       " u'mortgage',\n",
       " u'uncashed',\n",
       " u'bikeshare',\n",
       " u'city outline',\n",
       " u'county boundary',\n",
       " u'parcel owner',\n",
       " u'board',\n",
       " u'parking signs',\n",
       " u'creek',\n",
       " u'parking lot',\n",
       " u'sweeper',\n",
       " u'registered business locations',\n",
       " u'mission district',\n",
       " u'foodtruck',\n",
       " u'public library',\n",
       " u'sf streets',\n",
       " u'suicide',\n",
       " u'.csv',\n",
       " u'pci',\n",
       " u'metro',\n",
       " u'injuries',\n",
       " u'parking violations',\n",
       " u'criminal',\n",
       " u'golden gate bridge',\n",
       " u'search datasf',\n",
       " u'crime statistics',\n",
       " u'sports',\n",
       " u'building use',\n",
       " u'land cover',\n",
       " u'developer',\n",
       " u'dog patch',\n",
       " u'realtor',\n",
       " u'walk',\n",
       " u'boundry',\n",
       " u'grocery store',\n",
       " u'base mao',\n",
       " u'licenses',\n",
       " u'bay area counties',\n",
       " u'habitat',\n",
       " u'san francisco shapefile',\n",
       " u'pipeline 2014',\n",
       " u'zipcodes',\n",
       " u'fuel',\n",
       " u'private schools',\n",
       " u'stop',\n",
       " u'pbc code',\n",
       " u'appraisal roll',\n",
       " u'entitlements',\n",
       " u'marijuana',\n",
       " u'basemap street centerlines shapefile',\n",
       " u'gtfs',\n",
       " u'gay',\n",
       " u'rape',\n",
       " u'city facilities',\n",
       " u'news',\n",
       " u'cancer risk',\n",
       " u'bicycle theft',\n",
       " u'mission',\n",
       " u'tow away',\n",
       " u'traffic light',\n",
       " u'ownership',\n",
       " u'people',\n",
       " u'fog',\n",
       " u'public parks',\n",
       " u'resturant',\n",
       " u'freeways',\n",
       " u'cases by channel',\n",
       " u'startup',\n",
       " u'projects',\n",
       " u'2013',\n",
       " u'manhole',\n",
       " u'taxes',\n",
       " u'diabetes',\n",
       " u'assess',\n",
       " u'san francisco county',\n",
       " u'sfopenbook',\n",
       " u'sport',\n",
       " u'census2000_blk_nowater',\n",
       " u'event',\n",
       " u'registered',\n",
       " u'bike routes',\n",
       " u'entertainment',\n",
       " u'baytrail',\n",
       " u'sea level',\n",
       " u'naics',\n",
       " u'companies',\n",
       " u'waterfront',\n",
       " u'grid',\n",
       " u'bay area zip codes',\n",
       " u'block groups',\n",
       " u'homeless shelter',\n",
       " u'excavation',\n",
       " u'sales tax',\n",
       " u'industrial',\n",
       " u'co2',\n",
       " u'cost of living',\n",
       " u'deaths',\n",
       " u'real-time',\n",
       " u'conference',\n",
       " u'home sales',\n",
       " u'heat',\n",
       " u'senior',\n",
       " u'mental health',\n",
       " u'catalog',\n",
       " u'city map',\n",
       " u'lottery',\n",
       " u'sexual assault',\n",
       " u'pdf parcel',\n",
       " u'service requests',\n",
       " u'btod',\n",
       " u'fema',\n",
       " u'land value',\n",
       " u'workforce',\n",
       " u'bike route network',\n",
       " u'police department',\n",
       " u'commute time',\n",
       " u'recreation and parks',\n",
       " u'puc',\n",
       " u'taylor',\n",
       " u'pier',\n",
       " u'basemap street centerlines',\n",
       " u'orthophotos',\n",
       " u'dogs',\n",
       " u'schedule a',\n",
       " u'potholes',\n",
       " u'cars',\n",
       " u'excelsior',\n",
       " u'460 forms schedule a',\n",
       " u'public school',\n",
       " u'checkbook',\n",
       " u'purchase',\n",
       " u'grade',\n",
       " u'crime shapefile',\n",
       " u'concealed weapons permits',\n",
       " u'recreation centers',\n",
       " u'dpt bike network',\n",
       " u'candidate name',\n",
       " u'congestion',\n",
       " u'deed',\n",
       " u'housing code violations',\n",
       " u'health facilities',\n",
       " u'business registration certificate number',\n",
       " u'bay outline',\n",
       " u'san francisco outline',\n",
       " u'immigration',\n",
       " u'house price',\n",
       " u'open spaces - city planning',\n",
       " u'national parks',\n",
       " u'sf boundary',\n",
       " u'abandoned',\n",
       " u'sun',\n",
       " u'311 case data',\n",
       " u'condo',\n",
       " u'resiliency',\n",
       " u'occupation',\n",
       " u'women',\n",
       " u'dwg',\n",
       " u'grafitti',\n",
       " u'music',\n",
       " u'motorcycle parking',\n",
       " u'traffic signals',\n",
       " u'films',\n",
       " u'solar map',\n",
       " u'sale',\n",
       " u'public safety',\n",
       " u'buyouts',\n",
       " u'civic center',\n",
       " u'sf flower mart',\n",
       " u'bay area cities',\n",
       " u'shape files',\n",
       " u'stops',\n",
       " u'enforcement',\n",
       " u'benchmarking',\n",
       " u'claims',\n",
       " u'central subway',\n",
       " u'foreclosure',\n",
       " u'bart stations',\n",
       " u'probation',\n",
       " u'planning cases',\n",
       " u'homicides',\n",
       " u'street closures',\n",
       " u'water use',\n",
       " u'bus line',\n",
       " u'business certificate',\n",
       " u'arrest',\n",
       " u'supervisorial districts',\n",
       " u'neighborhood boundaries',\n",
       " u'recycle',\n",
       " u'external datasets',\n",
       " u'murders',\n",
       " u'ellis',\n",
       " u'bcdc',\n",
       " u'shelters',\n",
       " u'property records',\n",
       " u'impervious',\n",
       " u'value',\n",
       " u'heights',\n",
       " u'3d building',\n",
       " u'urban tree',\n",
       " u'market street',\n",
       " u'street center lines',\n",
       " u'towing',\n",
       " u'disabled parking',\n",
       " u'toilets',\n",
       " u'incomes',\n",
       " u'meeting time',\n",
       " u'car theft',\n",
       " u'gang',\n",
       " u'floodplain',\n",
       " u'arcgis',\n",
       " u'block lot',\n",
       " u'rent prices',\n",
       " u'business map',\n",
       " u'diana lerner',\n",
       " u'form 700 schedules',\n",
       " u'classification',\n",
       " u'disaster',\n",
       " u'juvenile',\n",
       " u'bay area shapefile',\n",
       " ...]"
      ]
     },
     "execution_count": 431,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "word_count_sorted = [i[0] for i in word_count_sorted if i[1]>2]\n",
    "word_count_sorted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 458,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "string_word = removePunctuation(re.sub(\"\\u\" , '', str(word_count_sorted)))\n",
    "string_word1 = removePunctuation(re.sub(\"\\u\" , '', str(query_list)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 465,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[u'bildings']\n",
      "[u'san']\n",
      "[u'bernal']\n",
      "[u'castro']\n",
      "[u'bsiness', u'maher']\n",
      "[u'san']\n",
      "[u'bart']\n",
      "[u'ellis']\n",
      "[u'diana', u'lerner']\n",
      "[u'ellis']\n",
      "[u'bart']\n",
      "[u'levis']\n",
      "[u'ord']\n",
      "[u'valle']\n",
      "[u'steven', u'holland']\n",
      "[u'consmer']\n",
      "[u'cort']\n",
      "[u'san', u'francisco']\n",
      "[u'drgs']\n",
      "[u'brigitte', u'wilson']\n",
      "[u'jane', u'kim']\n",
      "[u'aaron', u'peskin']\n",
      "[u'bart']\n",
      "[u'thomas', u'angelo', u'hetch']\n",
      "[u'stadim']\n",
      "[u'stitt']\n",
      "[u'salesforcecom', u'maher']\n",
      "[u'stosp']\n",
      "[u'aaron', u'apperson']\n",
      "[u'alain', u'gervais']\n",
      "[u'mintes']\n",
      "[u'soth', u'san']\n",
      "[u'soltions', u'san']\n",
      "[u'jeff', u'startps']\n",
      "[u'sadi']\n",
      "[u'jng', u'dccc']\n",
      "[u'christensen']\n",
      "[u'bilding', u'rajewen', u'haight']\n",
      "[u'watson']\n",
      "[u'lewis']\n",
      "[u'francisco']\n",
      "[u'bondary']\n",
      "[u'noe']\n",
      "[u'cort']\n",
      "[u'san']\n",
      "[u'jim', u'jones']\n",
      "[u'andrew', u'rex']\n",
      "[u'weiner']\n",
      "[u'bart']\n",
      "[u'sanborn']\n",
      "[u'ariel', u'tandoori']\n",
      "[u'kate', u'spasde', u'constrctionlist']\n",
      "[u'hosing', u'condit']\n",
      "[u'ca94118']\n",
      "[u'daly']\n",
      "[u'carman', u'tomarah', u'l']\n",
      "[u'anda', u'bsinesse', u'david', u'jordan', u'sfchamp']\n",
      "[u'apn']\n",
      "[u'christopher', u'charley', u'shapefile']\n",
      "[u'landmarcrimek']\n",
      "[u'webster']\n",
      "[u'jonah', u'yee']\n",
      "[u'corses', u'harrison']\n",
      "[u'kao']\n",
      "[u'amor', u'santiago']\n",
      "[u'bart']\n",
      "[u'san', u'francisco']\n",
      "[u'leo', u'brnett']\n",
      "[u'kevin', u'klng']\n",
      "[u'sfmta']\n",
      "[u'vallejo']\n",
      "[u'bart']\n",
      "[u'reqest']\n",
      "[u'rinary']\n",
      "[u'molly', u'saeger']\n",
      "[u'mathews', u'vassallo']\n",
      "[u'peskin', u'lalanne']\n",
      "[u'sfmta']\n",
      "[u'failre']\n",
      "[u'palo']\n",
      "[u'jlie', u'christensen']\n",
      "[u'accont']\n",
      "[u'roldan', u'enriqez', u'oter']\n",
      "[u'j']\n",
      "[u'charles', u'walton', u'censs']\n",
      "[u'sanfrancisco', u'impervios']\n",
      "[u'henry', u'albert', u'villarreal']\n",
      "[u'dolores']\n",
      "[u'anson', u'mooney']\n",
      "[u'larkin']\n",
      "[u'san']\n",
      "[u'pedicab', u'graff']\n",
      "[u'kristian']\n",
      "[u'bock']\n",
      "[u'san']\n",
      "[u'michael', u'schlz']\n",
      "[u'san']\n",
      "[u'shaprfile', u'valverde', u'constrction']\n",
      "[u'constrction']\n",
      "[u'san']\n",
      "[u'bsiness']\n",
      "[u'henderson']\n",
      "[u'jon']\n",
      "[u'bateman']\n",
      "[u'monments', u'starp']\n",
      "[u'ellis']\n",
      "[u'consmption']\n",
      "[u'jane', u'kim']\n",
      "[u'san']\n",
      "[u'francis', u'ngyen']\n",
      "[u'francisco', u'dem', u'david']\n",
      "[u'ber']\n",
      "[u'bart']\n",
      "[u'dennis', u'herrera']\n",
      "[u'lasala', u'nert']\n",
      "[u'hosing']\n",
      "[u'fazil']\n",
      "[u'melanie', u'lok']\n",
      "[u'alan']\n",
      "[u'hosing']\n",
      "[u'jennifer', u'wong', u'noi']\n",
      "[u'sorcing']\n",
      "[u'ami']\n",
      "[u'michel', u'legrand']\n",
      "[u'geocode', u'carlson']\n",
      "[u'san']\n",
      "[u'mary']\n",
      "[u'smmaries']\n",
      "[u'inclsionary']\n",
      "[u'restarants', u'casey']\n",
      "[u'phill', u'mnicipal']\n",
      "[u'vincent', u'kircher']\n",
      "[u'richard', u'chiriboga']\n",
      "[u'enterovirs']\n",
      "[u'mintes']\n",
      "[u'michael', u'jeff']\n",
      "[u'crim']\n",
      "[u'schedled']\n",
      "[u'noel', u'larios', u'scriptscriptalert1script']\n",
      "[u'polk', u'xlm']\n",
      "[u'calvin']\n",
      "[u'joanne', u'minsky']\n",
      "[u'san']\n",
      "[u'athority']\n",
      "[u'sara', u'reek']\n",
      "[u'roderick', u'baltazar', u'shapefile']\n",
      "[u'corey', u'canapary']\n",
      "[u'genrification']\n",
      "[u'mashp', u'levi', u'strass']\n",
      "[u'sanchez']\n",
      "[u'brigitte']\n",
      "[u'michael', u'webster']\n",
      "[u'bernal']\n",
      "[u'mary', u'jng', u'dccc']\n",
      "[u'bateman']\n",
      "[u'raymond', u'chow']\n",
      "[u'injry']\n",
      "[u'bildings']\n",
      "[u'shapefile']\n",
      "[u'ave', u'salla']\n",
      "[u'kwok']\n",
      "[u'ahmed']\n",
      "[u'shapefile']\n",
      "[u'holliday']\n",
      "[u'chaz']\n",
      "[u'harris']\n",
      "[u'matinez']\n",
      "[u'san']\n",
      "[u'david', u'owen', u'mni']\n",
      "[u'rashad', u'eid']\n",
      "[u'lombard']\n",
      "[u'cort']\n",
      "[u'shafter']\n",
      "[u'ayear']\n",
      "[u'san']\n",
      "[u'bdget']\n",
      "[u'cesar', u'chavez']\n",
      "[u'minh']\n",
      "[u'ngyen']\n",
      "[u'karen', u'lm']\n",
      "[u'colter']\n",
      "[u'howard']\n",
      "[u'commnity']\n",
      "[u'walsh']\n",
      "[u'san', u'francisco']\n",
      "[u'antonin', u'scalia']\n",
      "[u'conty']\n",
      "[u'ami']\n",
      "[u'cnn', u'frnitre']\n",
      "[u'stillman']\n",
      "[u'carl']\n",
      "[u'ada']\n",
      "[u'jessie']\n",
      "[u'brendan', u'y']\n",
      "[u'collison']\n",
      "[u'jean', u'ng']\n",
      "[u'armin']\n",
      "[u'bildings', u'harris']\n",
      "[u'aaron', u'peskin']\n",
      "[u'joseph']\n",
      "[u'james', u'peavey']\n",
      "[u'adrian']\n",
      "[u'sbdivisions', u'wiseman']\n",
      "[u'dorothy', u'tyson', u'san']\n",
      "[u'kenneth', u'malvar']\n",
      "[u'teng']\n",
      "[u'cliate']\n",
      "[u'rammp']\n",
      "[u'asdf', u'coates']\n",
      "[u'francisco', u'bart']\n",
      "[u'kim', u'reynolds']\n",
      "[u'serco']\n",
      "[u'molnar']\n",
      "[u'l']\n",
      "[u'toby', u'kanzawa']\n",
      "[u'solomon', u'pblic']\n",
      "[u'fnd', u'clementina']\n",
      "[u'tblreislotdetail', u'bart', u'statios']\n",
      "[u'alex', u'lebel']\n",
      "[u'ttx']\n",
      "[u'snset']\n",
      "[u'brlingame']\n",
      "[u'brannan']\n",
      "[u'san', u'francisco']\n",
      "[u'bart']\n",
      "[u'armando', u'lna']\n",
      "[u'lois']\n",
      "[u'wiener']\n",
      "[u'bart']\n",
      "[u'clement', u'brea']\n",
      "[u'cordova', u'fractre']\n",
      "[u'san', u'francisco', u'reqests']\n",
      "[u'bilding']\n",
      "[u'christopher', u'webster']\n",
      "[u'parkin']\n",
      "[u'brophy']\n",
      "[u'conway', u'brasilv']\n",
      "[u't2btt49r']\n",
      "[u'aaron', u'peskin', u'smmary']\n",
      "[u'townsend']\n",
      "[u'aaron', u'peskin']\n",
      "[u'dyogi']\n",
      "[u'anthony']\n",
      "[u'san']\n",
      "[u'joel']\n",
      "[u'pepi']\n",
      "[u'bart']\n",
      "[u'bidling']\n",
      "[u'san', u'francisco', u'birt']\n",
      "[u'linda']\n",
      "[u'fillmore', u'blake', u'rawdin']\n",
      "[u'hilary', u'ronen']\n",
      "[u'bilding']\n",
      "[u'alsabeel', u'aaron', u'peskin']\n",
      "[u'roedors']\n",
      "[u'hosing', u'constrcted']\n",
      "[u'certficate']\n",
      "[u'lerner']\n",
      "[u'vehiclar']\n",
      "[u'lydia', u'chavez']\n",
      "[u'ceccx', u'clifton']\n",
      "[u'san', u'francisco']\n"
     ]
    }
   ],
   "source": [
    "from polyglot.text import Text\n",
    "\n",
    "NER = Text(string_word1)\n",
    "\n",
    "NER = words.entities\n",
    "\n",
    "for entity in NER:\n",
    "    if entity.tag == \"I-PER\":\n",
    "        print entity\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# important people:\n",
    "- dennis herrera , city attorney\n",
    "- aaron peskin , sf supervisor\n",
    "- jane kim , SF District 6 Supervisor\n",
    "- hilary ronen , SF District 9 supervisor\n",
    "- raymond chow , san francisco gangster/felon (high possibility)\n",
    "- Kenneth Malvar , City Sightseeing operator (high possibility)\n",
    "http://www.sfgate.com/bayarea/article/Driver-blamed-for-tour-bus-crash-in-SF-s-Union-6994435.php\n",
    "\n",
    "\n",
    "- Blake Rawdin , private MD(possibly)\n",
    "- Antonin Scalia, Supreme Court Justice (possibly)\n",
    "- charle, walton Co-Founder and Partner, Kindred Partners, LLC (possibly)\n",
    "- Henry albert , private eye doctor(possibly)\n",
    "- melanie lok , owner of mlok consulting private consulting (possibly)\n",
    "- bateman, bateman group(possibly)\n",
    "- David Owen , SF Attorney(possibly)\n",
    "- Jonah Yee, Private Eye Doctor(possibly)\n",
    "- Molly Seager, Private Therapist(possibly)\n",
    "- anson mooney(arrested)\n",
    "https://jailalert.com/arrest-records/anson-mooney-310655.html\n",
    "- alain gervais, private owner salon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 398,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#not_caught = [ '3180 18th street' , '100 church', '17th street' , '800 university avenue, palo alto, california', '17 san andreas way, san francisco' , '2631 23rd']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 399,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.feature_extraction.text import CountVectorizer, TfidfVectorizer\n",
    "from sklearn.decomposition import NMF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 400,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NMF(alpha=0.0, beta=1, eta=0.1, init=None, l1_ratio=0.0, max_iter=200,\n",
       "  n_components=10, nls_max_iter=2000, random_state=1, shuffle=False,\n",
       "  solver='cd', sparseness=None, tol=0.0001, verbose=0)"
      ]
     },
     "execution_count": 400,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vec=CountVectorizer(stop_words='english',analyzer='word')\n",
    "X_train_counts = vec.fit_transform(word_count_sorted)\n",
    "vocab = vec.get_feature_names()\n",
    "nmf = NMF(n_components = 10, random_state = 1)\n",
    "nmf.fit(X_train_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 401,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def print_top_words(model, feature_names, n_top_words):\n",
    "    for topic_idx, topic in enumerate(model.components_):\n",
    "        print(\"Topic #%d:\" % topic_idx)\n",
    "        print(\" \".join([feature_names[i]\n",
    "                        for i in topic.argsort()[:-n_top_words - 1:-1]]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 402,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Topic #0:\n",
      "gtfs imagery graffiti ocean pipeline realtor_neighborhoods employment shapefile movie parking meters elevation buildings sanfrancisco.gdb sewer city owned race parking dem companies dog business license school fte hospital BART murder homeless recidivism income calls for service land-use lidar current year road network bicycle parking traffic citylots muni home sales elevation sfpd fires shapefile street neighborhood business license trees json street cleaning contracts building\n",
      "Topic #1:\n",
      "noise bay area parking taxi parcels SFMTA Bikeway Network san francisco streets supervisor districts building data graffiti Gentrification parcels historic districts meters airport Fire senior centers population recidivism chapter 20 pedestrian high injury SF solar map GPS crash human rights affordable housing evictions Street parking city map parcels BAN grafitti neighborhood city survey budget zoning house census bicycle accidents population neighborhood crime crime median income charity shapefile street library dataset gis evictions counties\n",
      "Topic #2:\n",
      "road network census museum shapefile street counties building sanfrancisco.gdb airport playground schedule e trees jobs pipeline building age median income business facilities planning AIDS cleaning avl crim recidivism restroom GPS libraries crime rates trees census shapefile labor playgrounds intersection housing inventory bus pipeline school shapefiles contour zoning planning neighborhoods cnn Tower gps street sweeping street cleaning restaurants footprints json homeless building footprint\n",
      "Topic #3:\n",
      "SF Shoreline and Islands human waste recidivism dog AIDS census shapefile bicycle birch realtor_neighborhoods bicycle current year map police incidents lidar cleaning historic zoning addresses airport pedestrian streets district highways soil crime building mission bay museum building footprints parking meters building height Crime soils SFPD reported incidents street centerline building footprint Restaurant city owned population building heights registered business locations pipeline businesses BART housing inventory survey retail crime land-use sfo\n",
      "Topic #4:\n",
      "street sweeping building footprint sanfrancisco.gdb historic planning assessor income planning police street cleaning bicycle police contracts bicycle parking meters earthquake trees hospital zip code block group landuse land-use temperature no parking parking cso bay area open business low income businesses restaurants residential elevation crim curbs street cleaning shapefiles pizza human waste human waste pipeline 2015 grocery store json business account number parcel dispensary city survey city survey airport sewer\n",
      "Topic #5:\n",
      "street evaluation building realtor_neighborhoods human waste sfpd housing price projects eviction notices planning district parcels parcel bike shapefile trail criminal budget school bicycle parking fte POPOS school businesses income shape files assessor property roll bikeshare street business account number inspection food zip code 311 call building age census light purchase order building evictions homeless fire station parking homeless population current year public schools language water assessor shapefile parking book contracts alikhani\n",
      "Topic #6:\n",
      "bay area cmt building age business data mental health sewer crime maps neighborhood buildings parcel POPOS current year crime curbs income permits BLOCK schedule e building ocean poverty Restaurant Restaurant charity zoning districts median income motionloft murder residential ocean 311 cases GDP sfo sewer Food truck historic business pipeline election results pipeline AIDS cad housing campaign business license airbnb water employee compensation supervisor districts bike\n",
      "Topic #7:\n",
      "dog employment shapefile ems FOIA block lot Murders traffic count counties events FOOTPRINT building age Restaurant Murder bike park hospital lobbyist contacts short term rental Movie locations traffic noise eviction building footprint parking charity business license taxi PARCELS bay area castro crime incident cost of living employees Restaurant shoreline eviction green space budget permit SF solar map healthcare pedestrian high injury IoE assessor property roll housing pipeline q4 chapter 20 census elevation water hospital shore line\n",
      "Topic #8:\n",
      "california accessor building footprint chapter 20 accident facilities street map bicycle parking parking Restaurant payroll assessment roll sfo library dataset drug use charity mental health shore line dem assault bike film locations income business license Murders median income Murder json historic districts retail building footprint bike lane contours POPOS Rent museum street trees crime data fleet parking meters permit shape files resturaunt hotels campaign finance library POPOS bikeshare pay CRIME\n",
      "Topic #9:\n",
      "json building building footprint realtor_neighborhoods human waste land-use police incidents ocean parcels shoreline Restaurant race bicycle accidents Restaurant hotels crime basemap drug use restaurant campaign homeless population dem footprints shapfiles street sweeping population taxi purchase order homeless cad trees block lot income bicycle parking address parcel parking museum bike parking parks parcel neighborhoods libraries envista community gardens secured property roll businesss New businesses Murder businesses\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "print(print_top_words(nmf, clean_list, 50))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 403,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[u'san', u'francisco', u'street', u'city', u'business', u'shapefile', u'data', u'of', u'census', u'building', u'map', u'parking', u'sf', u'and', u'bay', u'water', u'crime', u'housing', u'bike', u'area', u'district', u'public', u'traffic', u'property', u'block', u'in', u'streets', u'food', u'open', u'code', u'districts', u'campaign', u'population', u'income', u'use', u'tax', u'car', u'neighborhood', u'bicycle', u'2000', u'fire', u'by', u'buildings', u'health', u'gis', u'2015', u'community', u'transit', u'pipeline', u'planning', u'school', u'park', u'market', u'permit', u'land', u'parks', u'permits', u'number', u'bus', u'police', u'parcel', u'zip', u'muni', u'2010', u'for', u'zoning', u'center', u'460', u'sfpd', u'rent', u'finance', u'2014', u'neighborhoods', u'blocks', u'county', u'businesses', u'lines', u'commercial', u'ca', u'facilities', u'locations', u'space', u'service', u'construction', u'schedule', u'registered', u'no', u'rate', u'boundary', u'311', u'development', u'tree', u'incidents', u'assessor', u'art', u'network', u'tracts', u'services', u'restaurant', u'inventory']\n"
     ]
    }
   ],
   "source": [
    "vectorizer = TfidfVectorizer()\n",
    "X = vectorizer.fit_transform(word_count_sorted)\n",
    "indices = np.argsort(vectorizer.idf_)\n",
    "features = vectorizer.get_feature_names()\n",
    "top_n = 100\n",
    "top_features = [features[i] for i in indices[:top_n]] \n",
    "\n",
    "print top_features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Recommendations:\n",
    "\n",
    "- spell corrector for mispelled words\n",
    "- recommendations for words while typing of words of correct search keywords, fuzzy matching\n",
    "- education for how to properly use the open data portal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}