Appending Geolocation Tutorial.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# specify directory that your data is in (this is relative)\n",
    "path = 'INPUT/'\n",
    "\n",
    "# file you want to read in\n",
    "file = '2014-15_Demographics-Administrators.csv'\n",
    "\n",
    "# combine the two to create the path to the file\n",
    "file_path = path + file\n",
    "\n",
    "# read in file to append data to\n",
    "data_df = pd.read_csv(file_path, sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>SchYear</th>\n",
       "      <th>DistNumb</th>\n",
       "      <th>DistType</th>\n",
       "      <th>DistName</th>\n",
       "      <th>SchNum</th>\n",
       "      <th>SchCls</th>\n",
       "      <th>SchName</th>\n",
       "      <th>CountyNumber</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>EcoDevRgn</th>\n",
       "      <th>...</th>\n",
       "      <th>MA</th>\n",
       "      <th>PhD</th>\n",
       "      <th>EdUnkn</th>\n",
       "      <th>AvgAge</th>\n",
       "      <th>AgeLess25</th>\n",
       "      <th>Age25_29</th>\n",
       "      <th>Age30_34</th>\n",
       "      <th>Age35_44</th>\n",
       "      <th>Age45_54</th>\n",
       "      <th>Age55Plus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14-15</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>AITKIN PUBLIC SCHOOL DISTRICT                 ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>AITKIN PUBLIC SCHOOL DISTRICT                 ...</td>\n",
       "      <td>1</td>\n",
       "      <td>Aitkin                                        ...</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14-15</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>AITKIN PUBLIC SCHOOL DISTRICT                 ...</td>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>AITKIN SECONDARY SCHOOL                       ...</td>\n",
       "      <td>1</td>\n",
       "      <td>Aitkin                                        ...</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14-15</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>AITKIN PUBLIC SCHOOL DISTRICT                 ...</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>RIPPLESIDE ELEMENTARY                         ...</td>\n",
       "      <td>1</td>\n",
       "      <td>Aitkin                                        ...</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14-15</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>AITKIN PUBLIC SCHOOL DISTRICT                 ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Totals                                        ...</td>\n",
       "      <td>1</td>\n",
       "      <td>Aitkin                                        ...</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14-15</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>MINNEAPOLIS PUBLIC SCHOOL DIST.               ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MINNEAPOLIS PUBLIC SCHOOL DIST.               ...</td>\n",
       "      <td>27</td>\n",
       "      <td>Hennepin                                      ...</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>53</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  SchYear  DistNumb  DistType  \\\n",
       "0   14-15         1         1   \n",
       "1   14-15         1         1   \n",
       "2   14-15         1         1   \n",
       "3   14-15         1         1   \n",
       "4   14-15         1         3   \n",
       "\n",
       "                                            DistName  SchNum  SchCls  \\\n",
       "0  AITKIN PUBLIC SCHOOL DISTRICT                 ...       0       0   \n",
       "1  AITKIN PUBLIC SCHOOL DISTRICT                 ...       1      33   \n",
       "2  AITKIN PUBLIC SCHOOL DISTRICT                 ...       2      10   \n",
       "3  AITKIN PUBLIC SCHOOL DISTRICT                 ...       0       0   \n",
       "4  MINNEAPOLIS PUBLIC SCHOOL DIST.               ...       0       0   \n",
       "\n",
       "                                             SchName  CountyNumber  \\\n",
       "0  AITKIN PUBLIC SCHOOL DISTRICT                 ...             1   \n",
       "1  AITKIN SECONDARY SCHOOL                       ...             1   \n",
       "2  RIPPLESIDE ELEMENTARY                         ...             1   \n",
       "3  Totals                                        ...             1   \n",
       "4  MINNEAPOLIS PUBLIC SCHOOL DIST.               ...            27   \n",
       "\n",
       "                                          CountyName EcoDevRgn    ...      MA  \\\n",
       "0  Aitkin                                        ...         3    ...       1   \n",
       "1  Aitkin                                        ...         3    ...       0   \n",
       "2  Aitkin                                        ...         3    ...       0   \n",
       "3  Aitkin                                        ...         3    ...       1   \n",
       "4  Hennepin                                      ...        11    ...       6   \n",
       "\n",
       "   PhD  EdUnkn  AvgAge  AgeLess25  Age25_29  Age30_34  Age35_44  Age45_54  \\\n",
       "0    0       0      53          0         0         0         0         1   \n",
       "1    0       0      45          0         0         0         0         1   \n",
       "2    0       0      34          0         0         1         0         0   \n",
       "3    0       0      44          0         0         1         0         2   \n",
       "4    1       1      53          0         0         0         2         3   \n",
       "\n",
       "   Age55Plus  \n",
       "0          0  \n",
       "1          0  \n",
       "2          0  \n",
       "3          0  \n",
       "4          4  \n",
       "\n",
       "[5 rows x 44 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# look at top 5 rows\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0      aitkin public school district\n",
      "1            aitkin secondary school\n",
      "2              rippleside elementary\n",
      "3                             totals\n",
      "4    minneapolis public school dist.\n",
      "Name: SchName, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# standardize capitalization & trim whitespace\n",
    "data_df['SchName'] = data_df['SchName'].str.lower().str.strip()\n",
    "print(data_df['SchName'].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['aitkin public school district', 'aitkin secondary school', 'rippleside elementary', 'totals', 'minneapolis public school dist.', 'special education', 'armatage elementary', 'lake harriet lower elementary', 'bancroft elementary', 'barton open elementary', 'bethune elementary', 'burroughs elementary', 'field elementary', 'lake harriet upper school', 'hale elementary', 'hiawatha elementary', 'howe elementary', 'lk nokomis comm-keewaydin campus', 'kenny elementary', 'kenwood elementary', 'loring elementary', 'lyndale elementary', 'jenny lind elementary', 'northrop elementary', 'pillsbury elementary', 'pratt elementary', 'seward elementary', 'sheridan elementary', 'waite park elementary', 'lk nokomis comm-wenonah campus', 'windom school', 'folwell arts magnet', 'jefferson elementary', 'dowling elementary', 'andersen community', 'sullivan elementary', 'anishinabe academy', 'marcy open elementary', 'bryn mawr elementary', 'green central park elementary', 'emerson elementary', 'lucy laney @ cleveland park elem.', 'hall international', 'nellie stone johnson elementary', 'whittier international', 'hmong international academy', 'cityview community', 'anthony middle school', 'anwatin middle com & spanish d i', 'northeast middle', 'olson middle', 'ramsey middle', 'sanford middle', 'urban league academy high', 'edison senior high', 'broadway arts & technology', 'henry senior high', 'roosevelt senior high', 'south senior high', 'southwest senior high', 'washburn senior high', 'north academy arts/communication', 'transition plus services', 'hill city public school district', 'hill city elementary', 'hill city middle school', 'hill city senior high', 'mcgregor public school district', 'mcgregor secondary', 'south st. paul public school dist.', 'south st. paul secondary', 's st. paul community lrng. cntr.', 'lincoln center elementary', 'kaposia education center elementary', 'anoka-hennepin public school dist.', 'anoka high school', 'coon rapids high school', 'anoka middle school for the arts', 'coon rapids middle school', 'jackson middle', 'northdale middle', 'blaine high school', 'andover high school', 'crossroads altn high school', 'champlin park high school', 'oak view middle', 'roosevelt middle', 'secondary technical ed prog s.t.e.p', 'adams elementary', 'crooked lake elementary', 'franklin elementary', 'hamilton elementary', 'hoover elementary', 'johnsville elementary', 'lincoln elementary', 'madison elementary', 'mckinley elementary', 'mississippi elementary', 'monroe elementary', 'morris bye elementary', 'sand creek elementary', 'university elementary', 'wilson elementary', 'evergreen park elementary', 'eisenhower elementary', 'ramsey elementary', 'dayton elementary', 'oxbow creek elementary', 'andover elementary', 'rum river elementary', 'champlin/brooklyn pk acd math ensci', 'crossroads-west campus', 'compass-bell center', 'centennial public school district', 'pines school - rjc', 'centennial high school', 'centennial middle', 'centennial elementary', 'centerville elementary', 'golden lake elementary', 'rice lake elementary', 'blue heron elementary', 'columbia heights public school dist', 'columbia heights senior high', 'columbia academy', 'achieve', 'highland elementary', 'north park elementary', 'valley view elementary', 'early childhood', 'fridley public school district', 'fridley senior high', 'fridley middle', 'hayes elementary', 'stevenson elementary', 'fridley moore lk area learning ctr', 'fridley middle area learning center', 'st. francis public school district', 'st. francis high', 'st. francis middle', 'st. francis elementary', 'cedar creek community school', 'east bethel community school', 'crossroads school & voc. center', 'spring lake park public schools', 'spring lake park senior high', 'westwood middle', 'northpoint elementary', 'westwood intermediate school', 'woodcrest el. spanish immersion', 'park terrace el.', 'detroit lakes public school dist.', 'detroit lakes senior high', 'detroit lakes middle', 'rossman elementary', 'detroit lakes area learning center', 'roosevelt elementary', 'frazee-vergas public school dist.', 'frazee elementary', 'frazee secondary', 'pine point public school district', 'pine point elementary', 'bemidji public school district', 'bemidji middle', 'northern elementary', 'bemidji senior high', 'paul bunyan center', 'j.w. smith elementary', 'central elementary', 'bemidji bylaw', 'solway elementary', 'horace may elementary', 'first city school', 'bemidji area learning center', 'oshki manidoo center', 'blackduck public school district', 'blackduck elementary', 'blackduck secondary', 'kelliher public school district', 'kelliher elementary', 'kelliher secondary', 'red lake public school district', 'red lake elementary', 'red lake early childhood center', 'ponemah elementary', 'red lake secondary', 'red lake middle school', 'sauk rapids-rice public schools', 'sauk rapids-rice senior high', 'mississippi heights elementary', 'rice elementary', 'pleasant view elementary', 'sauk rapids-rice middle school', 'foley public school district', 'foley elementary', 'foley senior high', 'foley intermediate elementary', 'st. clair public school district', 'st. clair elementary', 'st. clair secondary', 'mankato public school district', 'kennedy elementary', 'bridges community elementary', 'rosa parks elementary school', 'washington elementary', 'mankato west senior high', 'eagle lake elementary', 'mankato east senior high', 'dakota meadows junior high', 'mankato east jr.', 'garfield elementary', 'central high  area learning center', 'comfrey public school district', 'comfrey elementary', 'comfrey secondary', 'sleepy eye public school district', 'sleepy eye elementary', 'sleepy eye sec.', 'springfield public school district', 'springfield secondary', 'springfield elementary', 'new ulm public school district', 'new ulm high school', 'barnum public school district', 'barnum elementary', 'barnum secondary', 'carlton public school district', 'carlton secondary', 'south terrace elementary', 'cloquet public school district', 'churchill elementary', 'cloquet area alt. ed. programs', 'cloquet middle', 'cloquet senior', 'cromwell-wright public schools', 'cromwell-wright elementary', 'cromwell-wright secondary', 'moose lake public school district', 'moose lake secondary', 'moose lake elementary', 'esko public school district', 'lincoln secondary', 'winterquist elementary', 'wrenshall public school district', 'central public school district', 'central senior high', 'central middle', 'waconia public school district', 'southview elementary', 'bayview elementary', 'clearwater middle', 'waconia senior high', 'watertown-mayer public school dist.', 'watertown-mayer primary school', 'watertown-mayer elementary school', 'watertown-mayer middle', 'watertown mayer high', 'eastern carver county public school', 'chaska high school', 'chaska middle school east', 'chaska middle school west', 'pioneer ridge middle school', 'chanhassen high school', 'integrated arts academy', 'la academia', 'chaska elementary', 'chanhassen elementary', 'east union elementary', 'kindergarten center', 'bluff creek elementary', 'clover ridge elementary', 'victoria elementary', 'jonathan elementary', 'walker-hackensack-akeley schl. dist', 'w.h.a. elementary', 'walker-hackensack-akeley sec.', 'cass lake-bena elementary', 'cass lake-bena middle school', 'cass lake-bena secondary', 'cass lake-bena area lrng. cntr.', 'pillager public school district', 'pillager elementary', 'pillager senior high school', 'pillager middle school', 'northland community schools', 'remer elementary', 'northland secondary', 'montevideo public school district', 'sanford education center', 'montevideo middle', 'montevideo senior high', 'north branch public schools', 'north branch middle', 'north branch senior high', 'sunrise river elementary', 'rush city public school district', 'c.e. jacobson elementary', 'rush city secondary', 'barnesville public school dist.', 'barnesville elementary', 'barnesville secondary', 'hawley public school district', 'hawley elementary', 'hawley secondary', 'moorhead public school district', 'r. asp elementary', 'ellen hopkins elementary', 's.g.reinertsen elementary', 'horizon middle school', 'moorhead high school', 'rr area learning center', 'minnesota state academies', 'mn academy for the blind', 'mn academy for the deaf', 'bagley public school district', 'bagley elementary', 'bagley secondary', 'cook county public schools', 'cook county middle', 'cook county senior high', 'sawtooth mountain elementary', 'mountain lake public schools', 'mountain lake elementary', 'mountain lake secondary', 'windom public school district', 'winfair elementary', 'windom senior high', 'brainerd public school district', 'baxter elementary', 'harrison elementary', 'lowell elementary', 'riverside elementary', 'brainerd senior high', 'nisswa elementary', 'isd 181 learning center', 'forestview middle', 'crosby-ironton public school dist.', 'crosby-ironton secondary', 'cuyuna range elementary', 'pequot lakes public schools', 'eagle view elementary', 'pequot lakes middle', 'pequot lakes senior high', 'burnsville public school district', 'burnsville senior high', 'metcalf junior high', 'eagle ridge junior high', 'nicollet junior high', 'gideon pond elementary', 'edward neill elementary', 'm.w. savage elementary', 'sioux trail elementary', 'vista view elementary', 'william byrne elementary', 'rahn elementary', 'sky oaks elementary', 'hidden valley elementary', 'harriet bishop elementary', 'burnsville sr high extended day/yr', 'burnsville alternative high school', 'farmington public school district', 'farmington elementary', 'farmington high school', 'levi p. dodge middle school', 'north trail elementary', 'robert boeckman middle school', 'meadowview elementary', 'riverview elementary', 'akin road elementary', 'lakeville public school district', 'lakeville north high', 'lakeville south high', 'mcguire middle', 'link12 lakeville', 'kenwood trail middle school', 'century middle school', 'lakeville area learning center', 'huddleston elementary', 'orchard lake elementary', 'lake marion elementary', 'cherry view elementary', 'lakeview elementary', 'eastview elementary', 'oak hills elementary', 'randolph public school district', 'randolph elementary', 'randolph secondary', 'rosemount-apple valley-eagan', 'dakota hills middle', 'black hawk middle', 'rosemount senior high', 'rosemount middle', 'eagan senior high', 'school of environmental studies', 'falcon ridge middle', 'eastview senior high', 'scott highlands middle', 'apple valley senior high', 'valley middle', 'rosemount elementary', 'northview elementary', 'westview elementary', 'parkview elementary', 'diamond path elementary', 'greenleaf elementary', 'cedar park elementary', 'thomas lake elementary', 'echo park elementary', 'deerwood elementary', 'woodland elementary', 'pinewood el.', 'shannon park elementary', 'oak ridge elementary', 'glacier hills elementary', 'red pine elementary', 'west st. paul-mendota hts.-eagan', 'friendly hills middle', 'henry sibley high school', 'heritage e-stem magnet school', 'moreland art/health science magnet', 'mendota elementary', 'garlough environmental magnet', 'somerset elementary', 'pilot knob stem magnet school', 'inver grove heights schools', 'simley senior high', 'inver grove heights middle', 'hilltop elementary', 'pine bend elementary', 'salem hills elementary', 'hastings public school district', 'hastings high school', 'hastings middle school', 'pinecrest elementary', 'christa mcauliffe elementary', 'hayfield public school district', 'hayfield sec.', 'brownsdale elementary', 'hayfield elementary', 'kasson-mantorville school district', 'kasson-mantorville elementary', 'kasson-mantorville senior high', 'kasson-mantorville middle school', 'alexandria public school district', 'carlos elementary', 'miltona elementary', 'woodland elementary school', 'voyager elementary', 'discovery middle school', 'alexandria area high school', 'osakis public school district', 'osakis elementary', 'osakis secondary', 'chatfield public schools', 'chatfield secondary', 'chatfield elementary school', 'lanesboro public school district', 'lanesboro elementary', 'lanesboro secondary', 'lanesboro online', 'mabel-canton public school dist.', 'mabel-canton elementary', 'mabel-canton secondary', 'rushford-peterson public schools', 'rushford-peterson elementary', 'rushford-peterson senior high', 'rushford-peterson middle', 'albert lea public school district', 'halverson elementary', 'hawthorne elementary', 'sibley elementary', 'albert lea senior high', 'southwest middle', 'albert lea area learning center', 'alden-conger public school district', 'alden-conger secondary', 'alden-conger elementary', 'cannon falls public school district', 'cannon falls elementary', 'cannon falls secondary', 'goodhue public school district', 'goodhue elementary', 'goodhue secondary', 'pine island public school dist.', 'pine island elementary', 'pine island secondary', 'red wing public school district', 'red wing senior high', 'twin bluff middle school', 'burnside elementary.', 'sunnyside elementary', 'ashby public school district', 'ashby elementary', 'ashby secondary', 'herman-norcross school district', 'herman secondary', 'hopkins public school district', 'hopkins senior high', 'hopkins north junior high', 'hopkins west junior high', 'alice smith elementary', 'glen lake elementary', 'harley hopkins family center', 'l.h. tanglen elementary', 'gatewood elementary', 'meadowbrook elementary', 'bloomington public school district', 'kennedy senior high', 'jefferson senior high', 'oak grove middle', 'valley view middle', 'washburn elementary', 'hillcrest elementary', 'indian mounds elementary', 'olson elementary', 'oak grove elementary', 'normandale hills elementary', 'poplar bridge elementary', 'ridgeview elementary', 'westwood elementary', 'beacon alp is', 'eden prairie public school district', 'eden prairie senior high', 'central middle school', 'oak point intermediate elementary.', 'prairie view elementary', 'forest hills elementary', 'eden lake elementary', 'eagle heights spanish immersion', 'cedar ridge elementary', 'edina public school district', 'south view middle', 'edina senior high', 'concord elementary', 'cornelia elementary', 'countryside elementary', 'highlands elementary', 'creek valley elementary', 'normandale elementary', 'minnetonka public school district', 'minnetonka senior high', 'minnetonka east middle', 'minnetonka west middle', 'clear springs elementary', 'deephaven elementary', 'excelsior elementary', 'groveland elementary', 'minnewashta elementary', 'scenic heights elementary', 'deephaven education center', 'westonka public school district', 'mound-westonka high school', 'grandview middle school', 'shirley hills primary school', 'hilltop primary school', 'orono public school district', 'orono senior high', 'orono middle', 'schumann elementary', 'orono intermediate elementary', 'osseo public school district', 'osseo senior high', 'brooklyn junior high', 'osseo junior high', 'osseo secondary transition center', 'osseo area learning center', 'north view ib world school', 'park center ib world school', 'maple grove senior high', 'maple grove junior high', 'edinbrook elementary', 'rush creek elementary', 'basswood elementary', 'birch grove school for the arts', 'crest view elementary', 'weaver lake science math & tech sch', 'fair oaks elementary', 'garden city elementary', 'palmer lake elementary', 'park brook elementary', 'zanewood community school', 'cedar island elementary', 'elm creek elementary', 'fernbrook elementary', 'oak view elementary', 'willow lane early childhood sp. ed.', 'richfield public school district', 'richfield senior high', 'richfield stem school', 'richfield middle', 'sheridan hills elementary', 'richfield dual language school', 'richfield career education program', 'robbinsdale public school district', 'forest elementary', 'school of engineering-arts at olson', 'meadow lake elementary', 'neill elementary', 'robbinsdale spanish immersion el.', 'noble elementary', 'northport elementary', 'sonnesyn elementary', 'zachary lane elementary', 'plymouth middle', 'robbinsdale middle', 'robbinsdale cooper senior high', 'robbinsdale armstrong senior high', 'highview alc', 'st. anthony-new brighton schools', 'st. anthony village senior high', 'wilshire park elementary', 'st. anthony middle', 'st. louis park public school dist.', 'st. louis park senior high', 'st. louis park middle school', 'aquila elementary', 'peter hobart elementary', 'susan lindgren elementary', 'park spanish immersion elementary', 'wayzata public school district', 'the alternative program (tap)', 'wayzata high', 'wayzata west middle', 'wayzata east middle', 'wayzata central middle', 'birchview elementary', 'greenwood elementary', 'oakwood elementary', 'sunset hill elementary', 'plymouth creek elementary', 'gleason lake elementary', 'kimberly lane elementary', 'brooklyn center school district', 'brooklyn center secondary', 'insight school of minnesota', 'earle brown elementary', 'brooklyn center academy', 'intermediate school district 287', 'district service center', 'hennepin technical pathways', 'off campus cte', '287 oll academic', 'invest high school', 'invest transition', 'vector basic', 'vector low incidence', 'focus', 'options', 'explore', 'phase', 'sun', 'sun transition', 'safe', 'strive secondary', 'strive transition', 'cip', 'attain', 'west academy', 'alc brooklyn park campus - is', 'alc edina hs alternative - is', 'alc richfield hs-is', '276 minnetonka hs is', 'w-alt-is', 'neca independent study', 'south education center academy', 'alc richfield - success at 15', 'alc learning online', 'alc-orono high school is', '283-st. louis park alc', 'nso - independent study', 'north education center academy', '277-grandview middle - alc', '283-ind stdy 15 and under - i.s.', '4126 - prairie seeds academy - is', '270 - hap - is', 'west education center alternative', 'mn online high school - i.s.', '4122 - eagle ridge academy - is', '4092 - watershed high school alc', 'houston public school district', 'houston elementary', 'minnesota virtual academy', 'houston secondary', 'spring grove school district', 'spring grove elementary', 'spring grove secondary', 'caledonia public school district', 'caledonia elementary', 'caledonia senior high', 'la crescent-hokah school district', 'la crescent-hokah elementary', 'la crescent senior high', 'la crescent-hokah middle school', 'laporte public school district', 'laporte elementary', 'laporte secondary', 'nevis public school district', 'nevis elementary', 'nevis secondary', 'park rapids public school district', 'park rapids area century elementary', 'park rapids senior high', 'park rapids area century middle', 'braham public school district', 'braham area secondary', 'braham elementary', 'greenway public school district', 'vandyke elementary', 'greenway senior high', 'deer river public school district', 'deer river secondary', 'king elementary', 'grand rapids public school district', 'cohasset elementary', 'bigfork elementary', 'forest lake elementary', 'murphy elementary', 'southwest elementary', \"thistledew: alice o'brien school\", 'bigfork secondary', 'grand rapids senior high', 'grand rapids area learning center', 'robert j. elkington middle school', 'nashwauk-keewatin school district', 'keewatin elementary', 'nashwauk secondary', 'heron lake-okabena school district', 'heron lake-okabena elementary', 'heron lake-okabena secondary', 'mora public school district', 'fairview elementary', 'trailview elementary', 'mora secondary', 'ogilvie public school district', 'ogilvie secondary', 'new london-spicer school district', 'prairie woods elementary', 'new london-spicer middle', 'new london-spicer sr.', 'willmar public school district', 'jefferson learning center', 'willmar senior high', 'willmar middle school', 'prairie lakes education center', 'willmar area learning center', 'lancaster public school district', 'lancaster elementary', 'lancaster secondary', 'international falls school district', 'falls secondary', 'falls elementary', 'littlefork-big falls elementary', 'littlefork-big falls secondary', 'south koochiching school district', 'indus secondary', 'northome secondary', 'northome elementary', 'indus elementary', 'dawson-boyd public school district', 'stevens elementary', 'dawson-boyd secondary', 'lake superior public school dist.', 'minnehaha elementary', 'kelley secondary', 'two harbors secondary', 'solo', 'nw region interdistrict council', 'lake of the woods school district', 'lake of the woods secondary', 'lake of the woods elementary', 'cleveland public school district', 'cleveland elementary', 'cleveland secondary', 'lake agassiz special ed. coop.', 'midwest special education coop.', 'hendricks public school district', 'hendricks elementary', 'hendricks senior high', 'ivanhoe public school district', 'lincoln elementary - ivanhoe', 'lake benton public school district', 'lake benton elementary', 'marshall public school district', 'marshall high school', 'park side elementary', 'west side elementary', 'marshall middle school', 'ma-tec', 'minneota public school district', 'minneota elementary', 'minneota secondary', 'lynd elementary', 'hutchinson public school district', 'hutchinson park elementary', 'hutchinson middle', 'hutchinson senior high', 'hutchinson west elementary', 'lester prairie public school dist.', 'lester prairie elementary', 'lester prairie secondary', 'mahnomen public school district', 'mahnomen elementary', 'mahnomen secondary', 'mahnomen area learning center', 'waubun-ogema-white earth public sch', 'waubun elementary', 'ogema elementary', 'waubun secondary', 'marshall county central schools', 'newfolden elementary', 'marshall county central high', 'viking elementary', 'grygla public school district', 'grygla elementary', 'grygla secondary', 'truman elementary', 'truman secondary', 'eden valley-watkins school district', 'eden valley elementary', 'eden valley secondary', 'litchfield public school district', 'lake ripley elementary', 'wagner elementary', 'litchfield middle', 'litchfield senior high', 'dassel-cokato public school dist.', 'cokato elementary', 'dassel-cokato senior high', 'dassel elementary', 'dassel-cokato middle', 'dassel-cokato alternative ctr.', 'isle public school district', 'isle elementary', 'isle secondary', 'princeton public school district', 'south elementary', 'north elementary', 'princeton middle', 'princeton senior high', 'onamia public school district', 'onamia elementary', 'onamia secondary', 'little falls public school district', 'lindbergh elementary', 'knight elementary', 'community middle', 'little falls senior high', 'little falls continuing education', 'pierz public school district', 'pioneer elementary', 'healy secondary', 'royalton public school district', 'royalton elementary', 'royalton high school', 'swanville public school district', 'swanville secondary', 'upsala public school district', 'upsala secondary', 'austin public school district', 'banfield elementary', 'neveln elementary', 'southgate elementary', 'sumner elementary', 'woodson kindergarten center', 'austin community learning center', 'austin senior high', 'i.j. holton intermediate school', 'ellis middle', 'grand meadow public school district', 'grand meadow elementary', 'grand meadow senior high', 'lyle public school district', 'lyle elementary', 'lyle secondary', 'leroy-ostrander public schools', 'leroy elementary', 'leroy secondary', 'southland public school district', 'southland elementary', 'southland senior high', 'southland middle', 'fulda public school district', 'fulda elementary', 'fulda secondary', 'nicollet public school district', 'nicollet elementary school', 'nicollet middle school', 'nicollet senior high school', 'st. peter public school district', 'north intermediate school', 'st. peter senior high', 'south early learning center', 'adrian public school district', 'adrian elementary', 'adrian secondary', 'ellsworth elementary', 'ellsworth secondary', 'worthington public school district', 'prairie elementary', 'worthington middle', 'worthington area learning center', 'worthington senior high', 'worthington esy program', 'byron public school district', 'byron elementary', 'byron middle school', 'dover-eyota public school district', 'dover-eyota elementary', 'dover-eyota middle school', 'dover-eyota high school', 'stewartville public school district', 'bonner elementary', 'stewartville middle', 'stewartville senior high', 'rochester public school district', 'bamber valley elementary', 'bishop elementary', 'elton hills elementary', 'folwell elementary', 'gage elementary', 'george w. gibbs elementary', 'lincoln k-8 choice elementary', 'longfellow choice elementary', 'pinewood elementary', 'riverside central elementary', 'sunset terrace elementary', 'century senior high', 'rochester alternative learning ctr', 'john marshall senior high', 'mayo senior high', 'john adams middle', 'willow creek middle', 'kellogg middle', 'friedell middle', 'rochester phoenix academy', 'battle lake public school district', 'battle lake secondary', 'fergus falls public school district', 'iq academy minnesota', 'kennedy secondary school', 'henning public school district', 'henning elementary', 'henning secondary', 'parkers prairie public school dist.', 'parkers prairie elementary', 'parkers prairie secondary', 'pelican rapids public school dist.', 'pelican rapids secondary', 'perham-dent public school district', 'heart of the lake elementary', 'prairie wind middle', 'perham senior high', 'underwood public school district', 'underwood secondary', 'underwood alp', 'new york mills public school dist.', 'new york mills elementary', 'new york mills secondary', 'goodridge public school district', 'goodridge elementary', 'goodridge secondary', 'thief river falls school district', 'challenger elementary', 'franklin middle', 'lincoln senior high', 'willow river public school district', 'willow river elementary', 'willow river secondary', 'pine city public school district', 'pine city elementary', 'pine city secondary', 'edgerton elementary', 'climax-shelly public schools', 'climax elementary', 'climax secondary', 'crookston public school district', 'crookston secondary', 'east grand forks public school dist', 'south point elementary', 'new heights elementary', 'east grand forks senior high', 'fertile-beltrami school district', 'fisher public school district', 'fisher elementary', 'fisher secondary', 'fosston public school district', 'magelssen elementary', 'fosston secondary', 'mounds view public school district', 'bel air elementary', 'island lake elementary', 'turtle lake el.', 'valentine hills el.', 'mounds view alc', 'mounds view senior high', 'irondale senior high', 'edgewood middle school', 'highview middle school', 'reach academy', 'chippewa middle school', 'north st paul-maplewood oakdale dis', 'carver elementary', 'castle elementary', 'cowern elementary', 'eagle point elementary', 'oakdale elementary', 'richardson elementary', 'weaver elementary', 'webster elementary', 'skyview community elementary', 'harmony learning center', 'skyview community middle', 'john glenn middle', 'maplewood middle', 'north senior high', 'tartan senior high', 'roseville public school district', 'roseville area senior high', 'roseville area middle', 'parkview center school', 'harambee elementary school', 'fairview alternative high school', 'brimhall elementary', 'central park elementary', 'falcon heights elementary', 'little canada elementary', 'williams elementary', 'fairview program', 'white bear lake school district', 'white bear lake district center', 'white bear north campus senior', 'sunrise park middle', 'white bear south campus senior', 'oneka elementary school', 'birch lake elementary', 'otter lake elementary', 'hugo elementary', 'lakeaires elementary', 'matoska international', 'vadnais heights elementary', 'willow lane elementary', 'white bear area learning center', 'st. paul public school district', 'creative arts secondary school', 'como park senior high', 'harding senior high', 'highland park senior high', 'humboldt secondary school', 'johnson senior high', 'open world learning secondary', 'washington tech secondary magnet', 'battle creek middle', 'farnsworth aerospace upper', 'highland park middle school', 'murray middle school', 'parkway montessori/community middle', 'ramsey middle school', 'adams magnet elementary', 'john a.johnson achievement plus el.', 'battle creek elementary school', 'benjamin e mays magnet', 'chelsea heights elementary', 'cherokee heights elementary school', 'bridge view special education', 'como park elementary', 'daytons bluff elementary', 'expo for excellence elementary sch', 'bruce f vento elementary', 'eastern heights elementary', 'farnsworth aerospace lower', 'four seasons elementary', \"l'etoile du nord french immersion u\", 'frost lake elementary school', 'crossroads montessori', 'galtier elementary school', 'groveland park elementary', 'hamline elementary school', 'the heights community school', 'hazel park preparatory academy', 'highland park elementary', 'jj hill montessori', 'capitol hill magnet/rondo', 'highwood hills elementary', 'jackson preparatory elementary', 'linwood monroe arts plus lower', 'horace mann school', 'maxfield elementary school', 'mississippi creative arts elem', 'linwood monroe arts plus upper', 'nokomis montessori north campus', 'phalen lake hmong studies magnet', 'randolph heights elementary', 'riverview west school of excellence', 'paul & sheila wellstone elementary', 'st. anthony park elementary', 'saint paul music academy', 'obama service learning elementary', 'american indian magnet school', 'rivereast program', 'gordon parks high school', 'alc evening high school', 'leap high school', 'boys totem town', 'red lake falls public school dist.', 'j.a. hughes elementary', 'lafayette secondary', 'milroy public school district', 'wabasso public school district', 'wabasso secondary', 'faribault public school district', 'faribault middle', 'faribault senior high', 'faribault area learning center', 'northfield public school district', 'northfield senior high', 'northfield middle', 'greenvale park elementary', 'bridgewater elementary', 'northfield area learning center', 'hills-beaver creek school district', 'hills-beaver creek elementary', 'hills-beaver creek secondary', 'badger public school district', 'roseau public school district', 'roseau elementary', 'roseau secondary', 'warroad public school district', 'warroad elementary', 'warroad high school', 'chisholm public school district', 'vaughan elementary', 'chisholm elementary', 'chisholm secondary', 'ely public school district', 'memorial middle school', 'memorial high school', 'floodwood public school district', 'hermantown public school district', 'hermantown elementary', 'hermantown middle', 'hermantown senior high', 'hibbing public school district', 'greenhaven elementary', 'hibbing high', 'proctor public school district', 'proctor senior high', 'bay view elementary', 'pike lake elementary', 'virginia public school district', 'virginia secondary', 'nett lake public school district', 'nett lake elementary', 'duluth public school district', 'denfeld high school', 'east high school', 'lincoln park middle school', 'ordean east middle school', 'congdon elementary', 'homecroft elementary', 'lakewood elementary', 'lester park elementary', 'laura macarthur elementary', 'myers-wilkins elementary', 'piedmont elementary', 'stowe elementary', 'woodland hills academy', 'duluth area learning center', 'mountain iron-buhl school district', 'merritt elementary', 'mountain iron-buhl secondary', 'mesabi academy school', 'belle plaine public school district', 'chatfield elementary', 'oak crest elementary', 'belle plaine senior high', 'jordan public school district', 'jordan secondary', 'jordan middle', 'jordan elementary', 'prior lake-savage area schools', 'westwood el.', 'five hawks el.', 'grainwood el.', 'glendale el.', 'jeffers pond elementary', 'redtail ridge elementary school', 'hidden oaks middle school', 'twin oaks middle school', 'prior lake high school', 'prior lake-savage area alc', 'shakopee public school district', 'shakopee senior high', 'shakopee junior high west', 'shakopee junior high east', 'shakopee sr alc', 'sweeney elementary', 'sun path elementary', 'pearson sixth grade center', 'central family center', 'red oak elementary', 'eagle creek elementary school', 'jackson elementary', 'new prague area schools', 'new prague senior high', 'new prague middle school', 'raven stream elementary', 'eagle view', 'falcon ridge', 'becker public school district', 'becker intermediate elementary', 'becker senior high', 'becker middle', 'becker primary', 'big lake public school district', 'independence elementary', 'big lake senior high', 'big lake middle school', 'liberty elementary', 'elk river public school district', 'rogers elementary', 'parker elementary', 'hassan elementary', 'twin lakes elementary', 'zimmerman elementary', 'rogers middle school', 'zimmerman middle', 'vandenberge middle school', 'salk middle school', 'elk river senior high', 'rogers senior high', 'zimmerman high school', 'ivan sand community high school', 'ivan sand community school-middle', 'ivan sand community school- is', 'ivan sand after school credit recov', 'ivan sand community school summer', 'ivan sand transition program', 'meadowvale elementary', 'otsego elementary', 'holdingford public school district', 'holdingford elementary', 'holdingford secondary', 'kimball public school district', 'kimball elementary', 'kimball secondary', 'melrose public school district', 'melrose elementary', 'melrose middle', 'melrose secondary', 'paynesville public school district', 'paynesville elementary', 'paynesville middle', 'paynesville area high school', 'st. cloud public school district', 'special education facilities', 'clearview elementary', 'kennedy community school', 'discovery community elementary', 'talahi community elementary', 'oak hill community elementary', 'north junior high', 'south junior high', 'apollo senior high', 'technical senior high', 'st cloud area learning center', 'sauk centre public school district', 'sauk centre elementary', 'sauk centre secondary', 'albany public school district', 'albany elementary', 'avon elementary', 'albany area schools early childhood', 'albany junior high', 'albany senior high', 'sartell-st. stephen school district', 'sartell senior high', 'pine meadow elementary', 'sartell middle', 'rocori public school district', 'richmond elementary', 'rockville elementary', 'cold spring elementary', 'rocori senior high', 'rocori middle', 'rocori alc', 'blooming prairie public school dist', 'blooming prairie elementary', 'blooming prairie secondary', 'owatonna public school district', 'owatonna senior high', 'owatonna junior high', 'owatonna ecse', 'willow creek intr. elementary', 'medford public school district', 'medford elementary', 'medford secondary', 'hancock elementary', 'hancock sec.', 'chokio-alberta secondary', 'kerkhoven-murdock-sunburg', 'murdock elementary', 'kerkhoven secondary', 'benson public school district', 'benson secondary', 'northside elementary', 'bertha-hewitt public school dist.', 'bertha elementary', 'bertha secondary', 'browerville public school district', 'browerville secondary', 'browns valley public school dist.', 'browns valley elementary', 'wheaton area public school district', 'pearson elementary', 'wheaton secondary', 'wabasha-kellogg public school dist.', 'wabasha-kellogg elementary', 'wabasha-kellogg secondary', 'lake city public school district', 'bluff view elementary', 'verndale public school district', 'verndale secondary', 'sebeka public school district', 'sebeka elementary', 'sebeka secondary', 'menahga public school district', 'menahga elementary', 'menahga secondary', 'waseca public school district', 'hartley elementary', 'waseca intermediate school', 'waseca junior high', 'waseca senior high', 'forest lake public school district', 'forest lake area learning center', 'forest lake senior high', 'century junior high', 'southwest junior high', 'step', 'columbus elementary', 'forest view elementary', 'lino lakes elementary', 'linwood elementary', 'scandia elementary', 'wyoming elementary', 'central montessori  elementary', 'mahtomedi public school district', 'o.h. anderson elementary', 'wildwood elementary', 'mahtomedi middle', 'mahtomedi senior high', 'south washington county school dist', 'cottage grove middle school', 'lake middle school', 'oltman middle school', 'south washington alternative hs', 'park senior high', 'east ridge high school', 'woodbury middle school', 'woodbury senior high', 'armstrong elementary', 'crestview elementary', 'grey cloud elementary', 'hillside elementary', 'cottage grove elementary', 'newport elementary', 'pine hill elementary', 'liberty ridge elementary', 'pullman elementary', 'red rock elementary', 'royal oaks elementary', 'gordon bailey elementary', 'woodbury elementary', 'so wash high school diploma program', 'stillwater area public school dist.', 'stillwater area high school', 'oak-land junior high', 'stillwater junior high', 'st croix valley area learning cntr.', 'afton-lakeland elementary', 'andersen elementary', 'lake elmo elementary', 'lily lake elementary', 'marine elementary', 'oak park elementary', 'withrow elementary', 'stonebridge elementary', 'rutherford elementary', 'butterfield public school district', 'butterfield elementary', 'butterfield secondary', 'madelia public school district', 'madelia elementary', 'madelia secondary', 'eminnesota online academy', 'st. james public school district', 'st. james northside elementary', 'armstrong school', 'st. james secondary', 'breckenridge public school district', 'breckenridge elementary', 'breckenridge middle', 'breckenridge senior high', 'rothsay public school district', 'rothsay elementary', 'rothsay secondary', 'campbell-tintah public school dist.', 'lewiston-altura public school dist.', 'lewiston-altura elementary', 'lewiston-altura secondary', 'st. charles public school district', 'st. charles elementary', 'st. charles secondary', 'winona area public school district', 'winona middle', 'winona senior high', 'rollingstone community elementary', 'washington-kosciusko elementary', 'goodview elementary', 'winona area learning center', 'annandale public school district', 'annandale elementary school', 'annandale middle school', 'annandale senior high', 'buffalo-hanover-montrose public sch', 'parkside elementary', 'hanover elementary', 'buffalo community middle', 'buffalo senior high', 'montrose elementary', 'tatanka elementary', 'northwinds elementary', 'discovery elementary', 'delano public school district', 'delano elementary', 'delano middle', 'delano senior high', 'maple lake public school district', 'maple lake elementary', 'maple lake secondary', 'monticello public school district', 'monticello senior high', 'monticello middle', 'little mountain elementary', 'rockford public school district', 'rockford secondary', 'rockford elementary arts magnet sch', 'rockford middle', 'st. michael-albertville school dist', 'albertville primary', 'st. michael-albertville senior high', 'st. michael-albertville middle east', 'st. michael elementary', 'knights academy', 'big woods elementary', 'fieldstone elementary school', 'st michael-albertville middle west', 'canby public school district', 'canby secondary', 'canby elementary', 'cambridge-isanti public school dist', 'cambridge primary school', 'isanti primary school', 'school for all seasons', 'cambridge intermediate school', 'isanti intermediate school', 'cambridge middle school', 'isanti middle school', 'minnesota center', 'cambridge-isanti high school', 'milaca public school district', 'milaca elementary', 'milaca secondary high', 'ulen-hitterdal public school dist', 'ulen-hitterdal elementary', 'ulen-hitterdal secondary', 'southern plains education coop.', 'northeast metro 916', 'career & technical center', 'bellaire education center', 'alp @ capitol view center', 'creative alternatives program', 'project return', 'karner blue education center', 'alp @ vccs', 'elementary alp @ john glenn', '916 mahtomedi academy', 'east view academy', 'wels north', 'intermediate school district 917', 'dakota cty. alc', 'region 5-north central serv. coop.', 'region 4-lakes country service coop', 'fergus falls area sp. ed. coop.', 'meeker and wright special education', 'cambridge alc west', 'st. francis alc', 'oak land learning center princeton', 'oak land alc west', 'wright technical center', 'wright technical center alc', 'wright technical center alc middle', 'minnesota valley cooperative', 'pine to prairie coop. ctr.', 'regn 6 and 8-sw/wc srv cooperative', 'red rock ridge area learning cntr', 'belview learning ctr bridges', 'cosmos learning center-transitions', 'foundations alternative program', 'blue mound learning center', 'north central learning center', 'area special education cooperative', 'bemidji regional interdist. council', 'perpich center for arts education', 'lake crystal-wellcome memorial', 'lk crystal welcome memorial el.', 'lk crystal-wellcome memorial sec.', 'triton school district', 'triton middle school', 'triton elementary', 'triton high school', 'united south central school dist.', 'united south central elementary', 'united south central high school', 'maple river school district', 'maple river senior high', 'maple river east elementary', 'maple river middle school', 'maple river west elementary', 'kingsland public school district', 'kingsland elementary', 'kingsland middle', 'kingsland senior high', 'st. louis county school district', 'babbitt elementary', 'tower-soudan elementary', 'northeast range secondary', 'south ridge elementary', 'south ridge secondary', 'north woods elementary', 'north woods secondary', 'cherry secondary', 'cherry elementary', 'waterville-elysian-morristown', 'morristown elementary', 'waterville-elysian-morristown jr.', 'waterville elementary', 'waterville-elysian-morristown sr.', 'chisago lakes school district', 'chisago lakes el.', 'lakeside el.', 'chisago lakes middle', 'chisago lakes senior high', 'taylors falls elementary', 'minnewaska school district', 'minnewaska secondary', 'minnewaska area middle', 'minnewaska area elementary', 'minnewaska day treatment program', 'minnewaska secondary alternative pg', 'eveleth-gilbert school district', 'nelle shean elementary', 'eveleth-gilbert junior high', 'eveleth-gilbert senior high', 'wadena-deer creek school district', 'wadena-deer creek senior high', 'wadena-deer creek elementary', 'buffalo lk-hector-stewart public sc', 'buffalo lake-hector elementary', 'buffalo lake-hector secondary', 'dilworth-glyndon-felton', 'dilworth elementary', 'glyndon-felton elementary', 'dilworth-glyndon-felton middle sch', 'dilworth-glyndon-felton senior high', 'hinckley-finlayson school district', 'hinckley elementary', 'hinckley-finlayson secondary', 'finlayson elementary', 'lakeview school district', 'lakeview secondary', 'nrheg school district', 'nrheg secondary', 'nrheg elementary', 'murray county central school dist.', 'murray co. central elementary', 'murray county central secondary', 'staples-motley school district', 'motley elementary', 'staples elementary', 'staples-motley senior high', 'motley-staples middle', 'kittson central school district', 'kittson central elementary', 'kittson central secondary', 'kenyon-wanamingo school district', 'kenyon-wanamingo elementary', 'kenyon-wanamingo senior high', 'kenyon-wanamingo middle', 'pine river-backus school district', 'pine river-backus elementary', 'pine river-backus high school', 'pine river-backus area learning ctr', 'pine river-backus midlevel alc', 'warren-alvarado-oslo school dist.', 'warren elementary', 'warren-alvarado-oslo secondary', 'm.a.c.c.r.a.y. school district', 'm.a.c.c.r.a.y. west elementary', 'm.a.c.c.r.a.y. east elementary', 'm.a.c.c.r.a.y. senior high', 'luverne public school district', 'luverne elementary', 'luverne middle', 'luverne senior high', 'luverne alternative program', 'yellow medicine east', 'bert raney elementary school', 'yellow medicine east high school', 'yellow medicine east middle school', 'fillmore central', 'fillmore central elementary', 'root river program', 'fillmore central senior high', 'norman county east school district', 'norman county east secondary', 'norman county east elementary', 'sibley east school district', 'sibley east-arlington elementary', 'sibley east-arlington senior high', 'sibley east-gaylord elementary', 'sibley east-gaylord junior high', 'clearbrook-gonvick school district', 'clearbrook-gonvick secondary', 'west central area', 'west central area n. el.', 'west central area s. el.', 'west central area sec.', 'wca 5-6 elementary', 'tri-county school district', 'karlstad elementary', 'tri-county secondary', 'belgrade-brooten-elrosa school dist', 'belgrade-brooten-elrosa elementary', 'belgrade-brooten-elrosa sec.', 'g.f.w.', 'g.f.w. el.', 'g.f.w. mid.', 'a.c.g.c. public school district', 'a.c.g.c. elementary', 'acgc elementary grades 5 and 6', 'a.c.g.c. secondary', 'le sueur-henderson school district', 'park elementary', 'le sueur-henderson high school', 'martin county west school district', 'sherburn elementary', 'trimont elementary', 'welcome elementary', 'martin county west senior high', 'norman county west school district', 'norman county  west elementary', 'norman county  west secondary', 'bird island-olivia-lake lillian', 'bold-bird island elementary', 'bold senior high', 'granada-huntley east chain sec.', 'east central school district', 'east central elementary', 'east central senior secondary', 'win-e-mac school district', 'win-e-mac secondary', 'greenbush-middle river school dist.', 'greenbush elementary', 'middle river elementary', 'greenbush-middle river senior high', 'greenbush-middle river junior high', 'howard lake-waverly-winsted', 'winsted elementary', 'howard lake-waverly-winsted sec.', 'humphrey elementary', 'howard lake middle', 'pipestone area school district', 'brown elementary', 'hill elementary', 'heartland elementary', 'pipestone middle', 'pipestone senior high', 'mesabi east school district', 'mesabi east elementary', 'mesabi east secondary', 'fairmont area school district', 'fairmont elementary school', 'fairmont jr./sr. high school', 'long prairie-grey eagle school dist', 'long prairie-grey eagle elementary', 'long prairie-grey secondary school', 'cedar mountain school district', 'cedar mountain elementary', 'cedar mountain secondary', 'eagle valley public school district', 'eagle valley secondary', 'morris area public schools', 'morris area secondary', 'morris area elementary', 'zumbrota-mazeppa school district', 'zumbrota-mazeppa elementary school', 'zumbrota-mazeppa senior high', 'zumbrota-mazeppa primary school', 'janesville-waldorf-pemberton', 'janesville-waldorf-pemberton el.em.', 'janesville-waldorf-pemberton sec', 'lac qui parle valley school dist.', 'madison-marietta-nassau elementary', 'appleton elementary', 'lac qui parle valley secondary', 'ada-borup public school district', 'ada elementary', 'ada-borup secondary', 'stephen-argyle central schools', 'stephen senior high', 'glencoe-silver lake school district', 'baker elementary', 'lakeside elementary', 'lincoln junior high', 'glencoe-silver lake senior high', 'blue earth area public school', 'blue earth elementary', 'blue earth area secondary', 'blue earth area middle', 'winnebago elementary', 'red rock central school district', 'red rock central secondary', 'glenville-emmons school district', 'glenville emmons elementary', 'glenville-emmons secondary', 'clinton-graceville-beardsley', 'clinton-graceville-beardsley el.', 'clinton-graceville-beardsley sr.', 'lake park audubon school district', 'lake park audubon elementary', 'lake park audubon secondary', 'renville county west elementary', 'renville county west senior high', 'jackson county central school dist.', 'pleasantview elementary', 'jackson county central senior high', 'jackson county central middle', 'redwood area school district', 'redwood valley senior high', 'reede gray elementary', 'redwood valley middle', 'westbrook-walnut grove schools', 'walnut grove elementary', 'westbrook-walnut grove secondary', 'plainview-elgin-millville', 'plainview-elgin-millville pk-3', 'plainview-elgin-millville 4-6', 'plainview-elgin-millville junior', 'plainview-elgin-millville high', 'rtr public schools', 'rtr elementary school', 'rtr high school', 'ortonville public schools', 'knoll elementary', 'ortonville secondary', 'tracy area public school district', 'tracy secondary', 'tracy elementary', 'tri-city united school district', 'le center pre k-8', 'tri-city united high school', 'montgomery pre k-8', 'lonsdale pre k-4', 'red lake county central public sch', 'red lake county central elementary', 'red lake county central high school', 'round lake-brewster public schools', 'round lake-brewster elementary', 'brandon-evansville public schools', 'brandon elementary', 'evansville elementary', 'brandon-evansville high school', 'city academy', 'new heights school, inc.', 'cedar riverside community school', 'metro deaf school', 'pact charter school', 'athlos leadership academy', 'community of peace academy', 'community of peace academy sec.', 'world learner charter school', 'minnesota transitions charter sch', 'mts high school', 'banaadir academy', 'mtcs connections academy', 'duluth public schools academy', 'higher ground academy', 'st. paul city school', 'harvest preparatory school', 'life prep', 'face to face academy', 'sojourner truth academy', 'high school for recording arts', 'twin cities academy', 'north lakes academy', 'north lakes academy 56', 'lacrescent montessori academy', 'nerstrand charter school', 'el colegio charter school', 'schoolcraft learning community chtr', 'crosslake community charter school', 'crosslake community high school', 'riverway learning community chtr', 'aurora charter school', 'excell academy charter', 'hope community academy', 'academia cesar chavez charter sch.', 'afsa high school', 'avalon school', 'twin cities international elem sch', 'mn international middle chr school', 'friendship acdmy of fine arts chtr.', 'bluesky charter school', 'ridgeway community school', 'harbor city international charter', 'woodson institute for excellence ch', 'sage academy charter school', 'urban academy charter school', 'arcadia charter school', 'new century academy', 'trio wolf creek distance learning', 'partnership academy, inc.', 'nova classical academy', 'nova classical academy upper school', 'great expectations', 'unity campus', 'hmong college prep academy', 'hmong college prep academy hs', 'paladin career and tech high school', 'treknorth high school', 'voyageurs expeditionary', 'voyageurs expeditionary school', 'augsburg fairview academy', 'st paul conservatory performing art', 'fraser academy', 'lakes international language admy', 'lakes intl language acad-headwaters', 'kaleidoscope charter school', 'st. croix preparatory academy', 'st. croix preparatory academy lower', 'st croix preparatory academy middle', 'st. croix preparatory academy upper', 'ubah medical academy charter school', 'eagle ridge academy charter school', 'eagle ridge academy lower school', 'dakota area community charter sch', 'beacon academy', 'prairie seeds el school academy', 'prairie seeds middle school academy', 'prairie seeds high school academy', 'team academy', 'twin cities academy high school', 'swan river montessori charter sch', 'milroy area charter school', 'loveworks academy for arts', 'paideia academy charter school', 'stride academy charter school', 'new millenium academy charter sch', 'northern lights community school', 'edvisions off campus school', 'twin cities german immersion chrtr', 'naytahwaush community school', 'seven hills preparatory academy', 'spectrum high school', 'new discoveries montessori academy', 'east range academy of tech-science', 'international spanish language acad', 'noble academy', 'minisinaakwaang leadership academy', 'community school of excellence', 'lionsgate academy', 'aspen academy', 'davinci academy', 'global academy', 'natural science academy', 'cologne academy', 'bright water elementary', 'rivers edge academy', 'best academy-lincoln', 'college preparatory elementary', 'cannon river stem school', 'parnassus prep sch-grammar', 'parnassus preparatory school-logic', 'parnassus prep school-rhetoric', 'step academy charter school', 'hennepin elementary school', 'vermilion country school', 'mastery school', 'upper mississippi academy', 'west side summit charter school', 'west concord public charter school', 'sejong academy of minnesota', 'northeast college prep charter scho', 'saint cloud math and science academ', 'bdote learning center', 'art and science academy', 'woodbury leadership academy', 'freshwater ed. dist.', 'freshwater education district', 'zumbro education district', 'zumbro area learning center', 'hiawatha valley ed. district', 'valley view alternative program', 'river valley academy alc', 'hiawatha valley targeted services', 'hiawatha valley north cluster ecse', 'hiawatha valley south cluster ecse', 'sail program', 'win program', 'runestone area ed. district', 'runestone regional learning center', 'mn river valley alc-seatbased', 'west central education district', 'west central area learning center', 'mn valley education district', 'minnesota valley school', 'river bend education district', 'river bend area learning center', 'imprints program', 'reach programs', 'west metro education program', 'fair school downtown', 'fair school crystal', 'northland learning center', 'northland learning center 010', 'northland learning center 020', 'rum river north-south', 'southwest metro educational coop', 'swmetro alc', 'benton-stearns ed. district', 'mid state education district', 'mid stat ed dist. success prog.', 'mid state ed dist steps trans. prog', 'morrison county learning center', 'mid-state north']\n"
     ]
    }
   ],
   "source": [
    "# get list of unique names in the column\n",
    "unique_names = pd.unique(data_df['SchName']).tolist()\n",
    "\n",
    "print(unique_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import googlemaps\n",
    "# You'll need your own API key\n",
    "# https://developers.google.com/maps/signup?hl=en\n",
    "\n",
    "# Read in my API key\n",
    "with open(\"api_key.txt\", \"r\") as file:\n",
    "    api_key = file.read()\n",
    "    \n",
    "# start gmaps api\n",
    "gmaps = googlemaps.Client(key=api_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1946\n"
     ]
    }
   ],
   "source": [
    "# Good to keep API Rate Limits in mind.\n",
    "# https://developers.google.com/places/web-service/usage\n",
    "# 10 per second, 1000 a day\n",
    "# 150,000 if you verify with CC that you are not a bot\n",
    "print(len(unique_names))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "aitkin secondary school\n",
      "{'html_attributions': [],\n",
      " 'results': [{'formatted_address': '306 2nd St NW, Aitkin, MN 56431, '\n",
      "                                   'United States',\n",
      "              'geometry': {'location': {'lat': 46.5336663,\n",
      "                                        'lng': -93.71121749999999}},\n",
      "              'icon': 'https://maps.gstatic.com/mapfiles/place_api/icons/school-71.png',\n",
      "              'id': '3f9a520a9a9d1c4a3eb5eed842a0eb555664ee45',\n",
      "              'name': 'Aitkin Secondary School',\n",
      "              'place_id': 'ChIJ8zaxqeI7sVIR0ExuhpzVYMo',\n",
      "              'reference': 'CnRrAAAA7UJs1vgiS_ph-U7IRDaNu6gFK-glqZepUBH0TXBPXM1O9VRqwh4GsEsFhFtxpKRKLKMbExRcNS-gwxrOvlJvsWASgHL3HygnEJvR9p3mxag1bDVF7chffif_fyXdIZ_Yv9zn4JcARHHrVwP1Nnj1shIQPlUCcGTQAseKFNDGnGbHwxoUzPSeGx50FornNFKBvPy5vzVjtNg',\n",
      "              'types': ['school', 'point_of_interest', 'establishment']}],\n",
      " 'status': 'OK'}\n"
     ]
    }
   ],
   "source": [
    "# let's look at just doing one at a time before we run a whole list\n",
    "# use google maps places API to get geolocation information \n",
    "loc = 'AITKIN SECONDARY SCHOOL'\n",
    "loc = loc.lower()\n",
    "print(loc)\n",
    "gmaps_json = gmaps.places(loc, location='Minnesota', radius=1000, types='school')\n",
    "\n",
    "# use pretty print library to have nicer looking output\n",
    "import pprint\n",
    "pprint.pprint(gmaps_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Aitkin Secondary School 46.5336663 -93.71121749999999 306 2nd St NW, Aitkin, MN 56431, United States\n"
     ]
    }
   ],
   "source": [
    "# let's grab the fields we care about\n",
    "results = gmaps_json[\"results\"][0]\n",
    "\n",
    "std_name = results['name']\n",
    "lat = results['geometry']['location']['lat']\n",
    "lng = results['geometry']['location']['lng']\n",
    "std_address = results['formatted_address']\n",
    "\n",
    "print(std_name, lat, lng, std_address)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "({'AddressNumber': '306',\n",
      "  'StreetName': '2nd',\n",
      "  'StreetNamePostType': 'St',\n",
      "  'StreetNamePostDirectional': 'NW',\n",
      "  'PlaceName': 'Aitkin',\n",
      "  'StateName': 'MN',\n",
      "  'ZipCode': '56431',\n",
      "  'CountryName': 'United States'},\n",
      " 'Street Address')\n"
     ]
    }
   ],
   "source": [
    "# tag the address to get nice filtering criteria like zip and city\n",
    "import usaddress\n",
    "parsed_address = usaddress.tag(std_address)\n",
    "\n",
    "pprint.pprint(parsed_address)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Aitkin MN\n"
     ]
    }
   ],
   "source": [
    "# select the pieces we care about\n",
    "city = parsed_address[0]['PlaceName']\n",
    "state = parsed_address[0]['StateName']\n",
    "\n",
    "print(city, state)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               Search_Term                     Name   Latitude  Longitude  \\\n",
      "0  aitkin secondary school  Aitkin Secondary School  46.533666 -93.711217   \n",
      "\n",
      "     City State  \n",
      "0  Aitkin    MN  \n"
     ]
    }
   ],
   "source": [
    "# let's put this all together to make a single record in a table\n",
    "# important to keep the original name searched, use this to join back\n",
    "df = pd.DataFrame([[loc, std_name, lat, lng, city, state]], \n",
    "                  columns=['Search_Term', 'Name', 'Latitude', 'Longitude', 'City', 'State'])\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import googlemaps\n",
    "import usaddress\n",
    "\n",
    "# ok let's try this across the whole list\n",
    "geoloc_df = pd.DataFrame()\n",
    "for loc in unique_names:\n",
    "    try:\n",
    "        gmaps_json = gmaps.places(loc, location='Minnesota', radius=1000, types='school')\n",
    "        print(gmaps_json)\n",
    "        # parse json\n",
    "        results = gmaps_json[\"results\"][0]\n",
    "        std_name = results['name']\n",
    "        lat = results['geometry']['location']['lat']\n",
    "        lng = results['geometry']['location']['lng']\n",
    "        std_address = results['formatted_address']\n",
    "        # parse the address\n",
    "        parsed_address = usaddress.tag(std_address)\n",
    "        city = parsed_address[0]['PlaceName']\n",
    "        state = parsed_address[0]['StateName']\n",
    "        df = pd.DataFrame([[loc, std_name, lat, lng, city, state]], \n",
    "                  columns=['Raw_Name', 'Name', 'Latitude', 'Longitude', 'City', 'State'])\n",
    "        geoloc_df = geoloc_df.append(df)\n",
    "    # if something fails, just skip. bad coding\n",
    "    except:\n",
    "        print(loc, 'Failed.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# save data so I don't bore you in the talk\n",
    "geoloc_df.to_csv('tutorial_cached.txt', sep='\\t', index=False, mode='w')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                           Raw_Name                                Name  \\\n",
      "0                              test                                test   \n",
      "1          little canada elementary     Little Canada Elementary School   \n",
      "2                            attain                              Attain   \n",
      "3  west st. paul-mendota hts.-eagan  Independent School District No 197   \n",
      "4    lakeville area learning center      Lakeville Area Learning Center   \n",
      "\n",
      "    Latitude   Longitude             City State  \n",
      "0  23.000000  343.000000       Eau Claire    WI  \n",
      "1  45.024745  -93.083058    Little Canada    MN  \n",
      "2  38.926405  -77.225298           McLean    VA  \n",
      "3  44.887310  -93.108026  Mendota Heights    MN  \n",
      "4  44.646357  -93.243841        Lakeville    MN  \n"
     ]
    }
   ],
   "source": [
    "# load data so I don't bore you in the talk\n",
    "geoloc_df = pd.read_csv('cached.txt', sep='\\t')\n",
    "\n",
    "print(geoloc_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   SchYear  DistNumb  DistType  \\\n",
      "1    14-15         1         1   \n",
      "5    14-15         1         3   \n",
      "8    14-15         1         3   \n",
      "9    14-15         1         3   \n",
      "11   14-15         1         3   \n",
      "\n",
      "                                             DistName  SchNum  SchCls  \\\n",
      "1   AITKIN PUBLIC SCHOOL DISTRICT                 ...       1      33   \n",
      "5   MINNEAPOLIS PUBLIC SCHOOL DIST.               ...      21      50   \n",
      "8   MINNEAPOLIS PUBLIC SCHOOL DIST.               ...     105      10   \n",
      "9   MINNEAPOLIS PUBLIC SCHOOL DIST.               ...     106      10   \n",
      "11  MINNEAPOLIS PUBLIC SCHOOL DIST.               ...     110      10   \n",
      "\n",
      "                    SchName  CountyNumber  \\\n",
      "1   aitkin secondary school             1   \n",
      "5         special education            27   \n",
      "8       bancroft elementary            27   \n",
      "9    barton open elementary            27   \n",
      "11     burroughs elementary            27   \n",
      "\n",
      "                                           CountyName EcoDevRgn  ...    \\\n",
      "1   Aitkin                                        ...         3  ...     \n",
      "5   Hennepin                                      ...        11  ...     \n",
      "8   Hennepin                                      ...        11  ...     \n",
      "9   Hennepin                                      ...        11  ...     \n",
      "11  Hennepin                                      ...        11  ...     \n",
      "\n",
      "    Age30_34  Age35_44  Age45_54  Age55Plus                 Raw_Name  \\\n",
      "1          0         0         1          0  aitkin secondary school   \n",
      "5          0         2         2          1        special education   \n",
      "8          0         1         1          0      bancroft elementary   \n",
      "9          0         2         0          0   barton open elementary   \n",
      "11         0         0         0          2     burroughs elementary   \n",
      "\n",
      "                               Name   Latitude  Longitude         City  State  \n",
      "1           Aitkin Secondary School  46.533666 -93.711218       Aitkin     MN  \n",
      "5   Department of Special Education  40.102181 -88.229639    Champaign     IL  \n",
      "8        Bancroft Elementary School  44.933827 -93.255558  Minneapolis     MN  \n",
      "9          Clara Barton Open School  44.925483 -93.291401  Minneapolis     MN  \n",
      "11      Burroughs Elementary School  44.911725 -93.300515  Minneapolis     MN  \n",
      "\n",
      "[5 rows x 50 columns]\n"
     ]
    }
   ],
   "source": [
    "# now let's append the geolocation data to the original data\n",
    "# data might have wholes in it so we'll do a left join (keep everything in original + matches in geolocation)\n",
    "\n",
    "result_df = pd.merge(data_df, geoloc_df, how='left', left_on='SchName', right_on='Raw_Name')\n",
    "\n",
    "# ideally here you would look to see how many don't have matches and may try to fix those by hand\n",
    "matched_df = result_df.dropna(subset=['Name'], how='all')\n",
    "\n",
    "print(matched_df.head())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.4.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}