explore_bbr_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd # Beautiful tool for data wrangling! e.g. '!pip install pandas' from a Notebook\n",
    "# See https://mariadb.com/blog/how-connect-python-programs-mariadb e.g. '!pip install mysql' from Notebook\n",
    "import mysql.connector as mariadb \n",
    "import re\n",
    "pd.set_option(\"display.max_rows\",35) # Useful when having large Pandas DataFrames like we do here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = mariadb.connect(user='mos', password='', database='monuments_db',buffered=True)\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Monuments_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5686, 22)"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * FROM monuments_all WHERE country='se-bbr'\")\n",
    "all_bbr = pd.io.sql.read_sql('select * from monuments_all WHERE country=\"se-bbr\"', conn)\n",
    "all_bbr.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### monuments_all_se-bbr_(sv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5686, 16)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_name = \"se_bbr\" # I've renamed monuments_se-bbr_(se) to 'se_bbr' in local database, change to correct name\n",
    "se_bbr = pd.io.sql.read_sql('select * from se_bbr', conn)\n",
    "se_bbr.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['bbr', 'namn', 'region-iso', 'funktion', 'byggar', 'arkitekt', 'plats',\n",
       "       'kommun', 'lat', 'lon', 'bild', 'commonscat', 'source', 'changed',\n",
       "       'monument_article', 'registrant_url'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se_bbr.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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>namn</th>\n",
       "      <th>funktion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>[[Huddungeby]], [[Anders Olsgården]] (Huddunge...</td>\n",
       "      <td>Bondgård (2 byggnader)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>[[Harbonäs]] säteri (Harbonäs 2:1)</td>\n",
       "      <td>Säteri (1 byggnad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>[[Huddungeby]], [[Jan Ers-gården]] (Huddungeby...</td>\n",
       "      <td>Bondgård (4 byggnader)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>[[Östervåla gästgivargård]] (Östervåla-Åby 8:6...</td>\n",
       "      <td>Gästgivargård (1 byggnad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>[[Biskops-Arnö]] (Biskops-Arnö 1:1)</td>\n",
       "      <td>Biskopsgård (9 byggnader)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>[[Skoklosters slott]] (Skokloster 2:4; f.d. Sk...</td>\n",
       "      <td>Slott (5 byggnader)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>[[Flottsunds fyr]] (Kungshamn 1-5:1)</td>\n",
       "      <td>Fyrplats (1 byggnad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>[[Krusenbergs herrgård]] (Krusenberg 1:58, 1:1...</td>\n",
       "      <td>Herrgård,Säteri (5 byggnader)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>[[Mora stenar]] (Mora 1:1)</td>\n",
       "      <td>Museum (1 byggnad)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>[[Skottsila]] f.d. mönsterskrivarboställe (Sko...</td>\n",
       "      <td>Ämbets- och tjänstemannaboställe (4 byggnader)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>[[Söderfors bruk]] (Söderfors bruk 1:1 och 1:1...</td>\n",
       "      <td>Park (95 byggnader)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 namn  \\\n",
       "10  [[Huddungeby]], [[Anders Olsgården]] (Huddunge...   \n",
       "11                 [[Harbonäs]] säteri (Harbonäs 2:1)   \n",
       "12  [[Huddungeby]], [[Jan Ers-gården]] (Huddungeby...   \n",
       "13  [[Östervåla gästgivargård]] (Östervåla-Åby 8:6...   \n",
       "14                [[Biskops-Arnö]] (Biskops-Arnö 1:1)   \n",
       "15  [[Skoklosters slott]] (Skokloster 2:4; f.d. Sk...   \n",
       "16               [[Flottsunds fyr]] (Kungshamn 1-5:1)   \n",
       "17  [[Krusenbergs herrgård]] (Krusenberg 1:58, 1:1...   \n",
       "18                         [[Mora stenar]] (Mora 1:1)   \n",
       "19  [[Skottsila]] f.d. mönsterskrivarboställe (Sko...   \n",
       "20  [[Söderfors bruk]] (Söderfors bruk 1:1 och 1:1...   \n",
       "\n",
       "                                          funktion  \n",
       "10                          Bondgård (2 byggnader)  \n",
       "11                              Säteri (1 byggnad)  \n",
       "12                          Bondgård (4 byggnader)  \n",
       "13                       Gästgivargård (1 byggnad)  \n",
       "14                       Biskopsgård (9 byggnader)  \n",
       "15                             Slott (5 byggnader)  \n",
       "16                            Fyrplats (1 byggnad)  \n",
       "17                   Herrgård,Säteri (5 byggnader)  \n",
       "18                              Museum (1 byggnad)  \n",
       "19  Ämbets- och tjänstemannaboställe (4 byggnader)  \n",
       "20                             Park (95 byggnader)  "
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se_bbr.ix[10:20, [\"namn\",\"funktion\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "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>funktion</th>\n",
       "      <th>namn</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kommun</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Ale</th>\n",
       "      <td>Repslageri (1 byggnad)</td>\n",
       "      <td>[[Repslagarmuseet]] (Starrkärr 15:4, Carlmarks...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alingsås</th>\n",
       "      <td>Rådhus (1 byggnad)</td>\n",
       "      <td>[[Alingsås rådhus]] (Kristina 7; f.d. Kristina 3)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alvesta</th>\n",
       "      <td>Järnvägsstation (1 byggnad)</td>\n",
       "      <td>[[Alvesta stationshus]] (Alvesta 11:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aneby</th>\n",
       "      <td>Herrgård, säteri (1 byggnad)</td>\n",
       "      <td>[[Degla gård]] (Degla 1:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arboga</th>\n",
       "      <td>Kungsgård (11 byggnader)</td>\n",
       "      <td>[[Kungsgården 3 och 5]] (Kungsgården 3 och 5)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              funktion  \\\n",
       "kommun                                   \n",
       "Ale             Repslageri (1 byggnad)   \n",
       "Alingsås            Rådhus (1 byggnad)   \n",
       "Alvesta    Järnvägsstation (1 byggnad)   \n",
       "Aneby     Herrgård, säteri (1 byggnad)   \n",
       "Arboga        Kungsgård (11 byggnader)   \n",
       "\n",
       "                                                       namn  \n",
       "kommun                                                       \n",
       "Ale       [[Repslagarmuseet]] (Starrkärr 15:4, Carlmarks...  \n",
       "Alingsås  [[Alingsås rådhus]] (Kristina 7; f.d. Kristina 3)  \n",
       "Alvesta              [[Alvesta stationshus]] (Alvesta 11:1)  \n",
       "Aneby                            [[Degla gård]] (Degla 1:1)  \n",
       "Arboga        [[Kungsgården 3 och 5]] (Kungsgården 3 och 5)  "
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first = se_bbr.groupby(\"kommun\")[[\"funktion\",\"namn\"]].first()\n",
    "first.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "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>funktion</th>\n",
       "      <th>namn</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kommun</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Ale</th>\n",
       "      <td>Repslageri (1 byggnad)</td>\n",
       "      <td>[[Repslagarmuseet]] (Starrkärr 15:4, Carlmarks...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Täby</th>\n",
       "      <td>Slott (3 byggnader)</td>\n",
       "      <td>[[Näsby slott]] (Näsbypark 57:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Åre</th>\n",
       "      <td>Järnvägsstation (inga registrerade byggnader)</td>\n",
       "      <td>[[Enafors station]] (Handöl 6:4)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             funktion  \\\n",
       "kommun                                                  \n",
       "Ale                            Repslageri (1 byggnad)   \n",
       "Täby                              Slott (3 byggnader)   \n",
       "Åre     Järnvägsstation (inga registrerade byggnader)   \n",
       "\n",
       "                                                     namn  \n",
       "kommun                                                     \n",
       "Ale     [[Repslagarmuseet]] (Starrkärr 15:4, Carlmarks...  \n",
       "Täby                     [[Näsby slott]] (Näsbypark 57:1)  \n",
       "Åre                      [[Enafors station]] (Handöl 6:4)  "
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first.loc[[\"Ale\",\"Täby\",\"Åre\"],[\"funktion\",\"namn\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>name</th>\n",
       "      <th>name2</th>\n",
       "      <th>name3</th>\n",
       "      <th>buildId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[[Ekolsunds f.d. värdshus]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Ekolsund 3:7)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[[Ekolsunds slott]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Ekolsund 1:227; f.d. 1:102)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[[Gryta majorsboställe]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Gryta 5:3)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[[Grönsö slott]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Grönsö 1:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[[Härnevi kvarn|Härnevi kvarn och såg]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Stora Härnevi 2:2, 11:1 och Kylsta 8:2)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>[[Kvekgården]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Kvek 2:34 och 2:36)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>[[Landsberga översteboställe]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Landsberga 1:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>[[Salnecke slott]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Salnecke 1:10)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>[[Utö hus|Utöhus]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Utö 2:1; f.d. Utöhus 1:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>[[Veckholms gamla prästgård]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Veckholm 1:8; f.d. 1:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>[[Huddungeby]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[[Anders Olsgården]]</td>\n",
       "      <td>(Huddungeby 3:19)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>[[Harbonäs]]</td>\n",
       "      <td>säteri</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Harbonäs 2:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>[[Huddungeby]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[[Jan Ers-gården]]</td>\n",
       "      <td>(Huddungeby 3:20)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>[[Östervåla gästgivargård]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Östervåla-Åby 8:69; f.d. 8:9)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>[[Biskops-Arnö]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Biskops-Arnö 1:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>[[Skoklosters slott]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Skokloster 2:4; f.d. Skokloster 2:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>[[Flottsunds fyr]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Kungshamn 1-5:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5669</th>\n",
       "      <td>[[Gråträsks kapell]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Gråträsk 5:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5670</th>\n",
       "      <td>[[Hortlax kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Hortlax 49:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5671</th>\n",
       "      <td>[[Infjärdens kyrka|Infjärdenkyrkan]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Sjulnäs 7:45)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5672</th>\n",
       "      <td>[[Långträsks kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Piteå-Långträsk 3:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5673</th>\n",
       "      <td>[[Norrfjärdens kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Helgenäs 2:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5674</th>\n",
       "      <td>[[Öjeby kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Piteå landsförsamlings kyrka)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5675</th>\n",
       "      <td>[[Piteå stadskyrka|Piteå stads kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Stadsön 5:37)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5676</th>\n",
       "      <td>[[Rosviks kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Rosvik 13:395)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5677</th>\n",
       "      <td>[[Strömnäskyrkan]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Fasanen 14)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5678</th>\n",
       "      <td>[[Vidsels kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Brännmark 1:179)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5679</th>\n",
       "      <td>[[Älvsby kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Älvsbyn 25:41)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5680</th>\n",
       "      <td>[[Jocks kapell]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Jock 18:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5681</th>\n",
       "      <td>[[Lansjärvs kapell]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Lansjärv 2:22)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5682</th>\n",
       "      <td>[[Överkalix kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Överkalix kyrka 1:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5683</th>\n",
       "      <td>[[Hietaniemi kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Koivukylä 39:1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5684</th>\n",
       "      <td>[[Svansteins kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Turtola 1:154)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5685</th>\n",
       "      <td>[[Övertorneå kyrka]]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(Matarengi 13:35)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5686 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         name    name2                 name3  \\\n",
       "0                 [[Ekolsunds f.d. värdshus]]      NaN                   NaN   \n",
       "1                         [[Ekolsunds slott]]      NaN                   NaN   \n",
       "2                    [[Gryta majorsboställe]]      NaN                   NaN   \n",
       "3                            [[Grönsö slott]]      NaN                   NaN   \n",
       "4     [[Härnevi kvarn|Härnevi kvarn och såg]]      NaN                   NaN   \n",
       "5                              [[Kvekgården]]      NaN                   NaN   \n",
       "6              [[Landsberga översteboställe]]      NaN                   NaN   \n",
       "7                          [[Salnecke slott]]      NaN                   NaN   \n",
       "8                          [[Utö hus|Utöhus]]      NaN                   NaN   \n",
       "9               [[Veckholms gamla prästgård]]      NaN                   NaN   \n",
       "10                             [[Huddungeby]]      NaN  [[Anders Olsgården]]   \n",
       "11                               [[Harbonäs]]  säteri                    NaN   \n",
       "12                             [[Huddungeby]]      NaN    [[Jan Ers-gården]]   \n",
       "13                [[Östervåla gästgivargård]]      NaN                   NaN   \n",
       "14                           [[Biskops-Arnö]]      NaN                   NaN   \n",
       "15                      [[Skoklosters slott]]      NaN                   NaN   \n",
       "16                         [[Flottsunds fyr]]      NaN                   NaN   \n",
       "...                                       ...      ...                   ...   \n",
       "5669                     [[Gråträsks kapell]]      NaN                   NaN   \n",
       "5670                        [[Hortlax kyrka]]      NaN                   NaN   \n",
       "5671     [[Infjärdens kyrka|Infjärdenkyrkan]]      NaN                   NaN   \n",
       "5672                     [[Långträsks kyrka]]      NaN                   NaN   \n",
       "5673                   [[Norrfjärdens kyrka]]      NaN                   NaN   \n",
       "5674                          [[Öjeby kyrka]]      NaN                   NaN   \n",
       "5675   [[Piteå stadskyrka|Piteå stads kyrka]]      NaN                   NaN   \n",
       "5676                        [[Rosviks kyrka]]      NaN                   NaN   \n",
       "5677                       [[Strömnäskyrkan]]      NaN                   NaN   \n",
       "5678                        [[Vidsels kyrka]]      NaN                   NaN   \n",
       "5679                         [[Älvsby kyrka]]      NaN                   NaN   \n",
       "5680                         [[Jocks kapell]]      NaN                   NaN   \n",
       "5681                     [[Lansjärvs kapell]]      NaN                   NaN   \n",
       "5682                      [[Överkalix kyrka]]      NaN                   NaN   \n",
       "5683                     [[Hietaniemi kyrka]]      NaN                   NaN   \n",
       "5684                     [[Svansteins kyrka]]      NaN                   NaN   \n",
       "5685                     [[Övertorneå kyrka]]      NaN                   NaN   \n",
       "\n",
       "                                       buildId  \n",
       "0                               (Ekolsund 3:7)  \n",
       "1                 (Ekolsund 1:227; f.d. 1:102)  \n",
       "2                                  (Gryta 5:3)  \n",
       "3                                 (Grönsö 1:1)  \n",
       "4     (Stora Härnevi 2:2, 11:1 och Kylsta 8:2)  \n",
       "5                         (Kvek 2:34 och 2:36)  \n",
       "6                             (Landsberga 1:1)  \n",
       "7                              (Salnecke 1:10)  \n",
       "8                   (Utö 2:1; f.d. Utöhus 1:1)  \n",
       "9                     (Veckholm 1:8; f.d. 1:1)  \n",
       "10                           (Huddungeby 3:19)  \n",
       "11                              (Harbonäs 2:1)  \n",
       "12                           (Huddungeby 3:20)  \n",
       "13              (Östervåla-Åby 8:69; f.d. 8:9)  \n",
       "14                          (Biskops-Arnö 1:1)  \n",
       "15       (Skokloster 2:4; f.d. Skokloster 2:1)  \n",
       "16                           (Kungshamn 1-5:1)  \n",
       "...                                        ...  \n",
       "5669                            (Gråträsk 5:1)  \n",
       "5670                            (Hortlax 49:1)  \n",
       "5671                            (Sjulnäs 7:45)  \n",
       "5672                     (Piteå-Långträsk 3:1)  \n",
       "5673                            (Helgenäs 2:1)  \n",
       "5674            (Piteå landsförsamlings kyrka)  \n",
       "5675                            (Stadsön 5:37)  \n",
       "5676                           (Rosvik 13:395)  \n",
       "5677                              (Fasanen 14)  \n",
       "5678                         (Brännmark 1:179)  \n",
       "5679                           (Älvsbyn 25:41)  \n",
       "5680                               (Jock 18:1)  \n",
       "5681                           (Lansjärv 2:22)  \n",
       "5682                     (Överkalix kyrka 1:1)  \n",
       "5683                          (Koivukylä 39:1)  \n",
       "5684                           (Turtola 1:154)  \n",
       "5685                         (Matarengi 13:35)  \n",
       "\n",
       "[5686 rows x 4 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se_bbr.namn.str.extract('(?P<name>\\[\\[[\\w\\.\\|\\- ]+\\]\\])\\,? ?(?P<name2>[ \\w]+)? ?(?P<name3>\\[\\[[\\w\\.\\|\\- ]+\\]\\])? ?(?P<buildId>\\([\\w\\.\\d \\|\\:\\-;,]+\\))',expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bbr                                                    21300000005034\n",
       "namn                       [[Långträsks kyrka]] (Piteå-Långträsk 3:1)\n",
       "region-iso                                                      SE-BD\n",
       "funktion                       Kyrka med begravningsplats (1 byggnad)\n",
       "byggar                                                               \n",
       "arkitekt                                                             \n",
       "plats                                                                \n",
       "kommun                                                          Piteå\n",
       "lat                                                           65.3868\n",
       "lon                                                           20.3236\n",
       "bild                                               Långträskkyrka.jpg\n",
       "commonscat                                                           \n",
       "source              //sv.wikipedia.org/w/index.php?title=Lista_%C3...\n",
       "changed                                           2016-06-08 06:52:31\n",
       "monument_article                                     Långträsks_kyrka\n",
       "registrant_url      http://www.bebyggelseregistret.raa.se/bbr2/anl...\n",
       "Name: 5672, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se_bbr.ix[5672]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check If we have extracted the two always occuring fields 'name' and 'buildId' for all objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "se_bbr_namn = se_bbr.namn.str.extract('(?P<name>\\[\\[[\\w`\\.,\\|\\- ]+\\]\\])\\,? ?(?P<name2>[ \\w]+)? ?(?P<name3>\\[\\[[\\w\\.\\|\\- ]+\\]\\])? ?(?P<buildId>\\([\\w\\.\\d \\|\\:\\-;,]+\\))',expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(se_bbr_namn[se_bbr_namn[\"name\"] == pd.np.nan])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5686"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(se_bbr_namn[se_bbr_namn[\"buildId\"] != pd.np.nan])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>bbr</th>\n",
       "      <th>namn</th>\n",
       "      <th>region-iso</th>\n",
       "      <th>funktion</th>\n",
       "      <th>byggar</th>\n",
       "      <th>arkitekt</th>\n",
       "      <th>plats</th>\n",
       "      <th>kommun</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>bild</th>\n",
       "      <th>commonscat</th>\n",
       "      <th>source</th>\n",
       "      <th>changed</th>\n",
       "      <th>monument_article</th>\n",
       "      <th>registrant_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [bbr, namn, region-iso, funktion, byggar, arkitekt, plats, kommun, lat, lon, bild, commonscat, source, changed, monument_article, registrant_url]\n",
       "Index: []"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se_bbr[pd.isnull(se_bbr[\"namn\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5148"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(se_bbr_namn.name.value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "89"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(se_bbr_namn.name2.value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(se_bbr_namn.name3.value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many objects did we get the other fields 'name2' and 'name3' for?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[[Sproge]]                                    2\n",
       "[[Burs]]                                      1\n",
       "[[Österviks kapell]]                          1\n",
       "[[Officerspaviljongen Trianon]]               1\n",
       "[[Laboratorium Chemicum]]                     1\n",
       "[[Testebokvarn]]                              1\n",
       "[[Flemingska palatset]]                       1\n",
       "[[Kungliga Skogs- och Lantbruksakademien]]    1\n",
       "[[Loftboden]]                                 1\n",
       "[[Norns kapell]]                              1\n",
       "[[Ekermanska huset]]                          1\n",
       "[[Lurgatan]]                                  1\n",
       "[[Västerleds församlingshem]]                 1\n",
       "[[von Leesenska-Ribbingska huset]]            1\n",
       "[[Kungsbropalatset]]                          1\n",
       "[[Hejnum]]                                    1\n",
       "[[Rosendals gård|Rosendal]]                   1\n",
       "                                             ..\n",
       "[[Eklundshof]]                                1\n",
       "[[Nyköpings rådhus|rådhus]]                   1\n",
       "[[Anders Olsgården]]                          1\n",
       "[[Bäl]]                                       1\n",
       "[[Svedens gård]]                              1\n",
       "[[Mahognyvillan]]                             1\n",
       "[[Bunge flygfält|flygfältet]]                 1\n",
       "[[Skogshögskolan i Garpenberg]]               1\n",
       "[[Sölvesborg]]                                1\n",
       "[[Kastellholmen]]                             1\n",
       "[[Stjärnsunds bruk|smedja]]                   1\n",
       "[[Armémuseum]]                                1\n",
       "[[Schefferska huset]]                         1\n",
       "[[Högvaktsterrassen]]                         1\n",
       "[[Othem]]                                     1\n",
       "[[Spökparken]]                                1\n",
       "[[Jan Ers-gården]]                            1\n",
       "Name: name3, dtype: int64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se_bbr_namn.name3.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>bbr</th>\n",
       "      <th>namn</th>\n",
       "      <th>region-iso</th>\n",
       "      <th>funktion</th>\n",
       "      <th>byggar</th>\n",
       "      <th>arkitekt</th>\n",
       "      <th>plats</th>\n",
       "      <th>kommun</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>bild</th>\n",
       "      <th>commonscat</th>\n",
       "      <th>source</th>\n",
       "      <th>changed</th>\n",
       "      <th>monument_article</th>\n",
       "      <th>registrant_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2461</th>\n",
       "      <td>21300000014845</td>\n",
       "      <td>[[Stora Norrgårde]] i [[Sproge]] (Sproge Stora...</td>\n",
       "      <td>SE-I</td>\n",
       "      <td>Bondgård (4 byggnader)</td>\n",
       "      <td>1700-talets slut</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Gotland</td>\n",
       "      <td>57.26773</td>\n",
       "      <td>18.17013</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>//sv.wikipedia.org/w/index.php?title=Lista_%C3...</td>\n",
       "      <td>2016-06-08 06:52:07</td>\n",
       "      <td>Stora_Norrgårde</td>\n",
       "      <td>http://www.bebyggelseregistret.raa.se/bbr2/anl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2467</th>\n",
       "      <td>21300000014852</td>\n",
       "      <td>[[Stymnes]] i [[Sproge]] (Sproge Stymnes 1:3)</td>\n",
       "      <td>SE-I</td>\n",
       "      <td>Bondgård (2 byggnader)</td>\n",
       "      <td>1870-talet</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Gotland</td>\n",
       "      <td>57.25262</td>\n",
       "      <td>18.21990</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>//sv.wikipedia.org/w/index.php?title=Lista_%C3...</td>\n",
       "      <td>2016-06-08 06:52:07</td>\n",
       "      <td>Stymnes</td>\n",
       "      <td>http://www.bebyggelseregistret.raa.se/bbr2/anl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2556</th>\n",
       "      <td>21300000002840</td>\n",
       "      <td>[[Sproge kyrka]] (Sproge Kyrkogården 1:1)</td>\n",
       "      <td>SE-I</td>\n",
       "      <td>Kyrka med begravningsplats (1&amp;nbsp;byggnad)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Gotland</td>\n",
       "      <td>57.25364</td>\n",
       "      <td>18.21091</td>\n",
       "      <td>Sproge-kyrka-Gotland-N1.jpg</td>\n",
       "      <td></td>\n",
       "      <td>//sv.wikipedia.org/w/index.php?title=Lista_%C3...</td>\n",
       "      <td>2016-06-08 06:52:07</td>\n",
       "      <td>Sproge_kyrka</td>\n",
       "      <td>http://www.bebyggelseregistret.raa.se/bbr2/anl...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 bbr                                               namn  \\\n",
       "2461  21300000014845  [[Stora Norrgårde]] i [[Sproge]] (Sproge Stora...   \n",
       "2467  21300000014852      [[Stymnes]] i [[Sproge]] (Sproge Stymnes 1:3)   \n",
       "2556  21300000002840          [[Sproge kyrka]] (Sproge Kyrkogården 1:1)   \n",
       "\n",
       "     region-iso                                     funktion  \\\n",
       "2461       SE-I                       Bondgård (4 byggnader)   \n",
       "2467       SE-I                       Bondgård (2 byggnader)   \n",
       "2556       SE-I  Kyrka med begravningsplats (1&nbsp;byggnad)   \n",
       "\n",
       "                byggar arkitekt plats   kommun       lat       lon  \\\n",
       "2461  1700-talets slut                 Gotland  57.26773  18.17013   \n",
       "2467        1870-talet                 Gotland  57.25262  18.21990   \n",
       "2556                                   Gotland  57.25364  18.21091   \n",
       "\n",
       "                             bild commonscat  \\\n",
       "2461                                           \n",
       "2467                                           \n",
       "2556  Sproge-kyrka-Gotland-N1.jpg              \n",
       "\n",
       "                                                 source             changed  \\\n",
       "2461  //sv.wikipedia.org/w/index.php?title=Lista_%C3... 2016-06-08 06:52:07   \n",
       "2467  //sv.wikipedia.org/w/index.php?title=Lista_%C3... 2016-06-08 06:52:07   \n",
       "2556  //sv.wikipedia.org/w/index.php?title=Lista_%C3... 2016-06-08 06:52:07   \n",
       "\n",
       "     monument_article                                     registrant_url  \n",
       "2461  Stora_Norrgårde  http://www.bebyggelseregistret.raa.se/bbr2/anl...  \n",
       "2467          Stymnes  http://www.bebyggelseregistret.raa.se/bbr2/anl...  \n",
       "2556     Sproge_kyrka  http://www.bebyggelseregistret.raa.se/bbr2/anl...  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se_bbr[se_bbr.namn.str.contains(\"sproge\", flags=re.IGNORECASE) == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}