db-insert-into-ra-one-off-extensions-only-access-type-column.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*This notebook gets reverse-dns entries from `ra_one_off_extensions` table, and searches the sample words to identify dsl and cable deploymentss and create a temporary file `temp.sql` with UPDATE queries that can be run on `ra_one_off_extensions` table*\n",
    "- - - "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>body {\n",
       "    margin: 0;\n",
       "    font-family: Helvetica;\n",
       "}\n",
       "table.dataframe {\n",
       "    border-collapse: collapse;\n",
       "    border: none;\n",
       "}\n",
       "table.dataframe tr {\n",
       "    border: none;\n",
       "}\n",
       "table.dataframe td, table.dataframe th {\n",
       "    margin: 0;\n",
       "    border: 1px solid white;\n",
       "    padding-left: 0.25em;\n",
       "    padding-right: 0.25em;\n",
       "}\n",
       "table.dataframe th:not(:empty) {\n",
       "    background-color: #fec;\n",
       "    text-align: left;\n",
       "    font-weight: normal;\n",
       "}\n",
       "table.dataframe tr:nth-child(2) th:empty {\n",
       "    border-left: none;\n",
       "    border-right: 1px dashed #888;\n",
       "}\n",
       "table.dataframe td {\n",
       "    border: 2px solid #ccf;\n",
       "    background-color: #f4f4ff;\n",
       "}\n",
       "h3 {\n",
       "    color: white;\n",
       "    background-color: black;\n",
       "    padding: 0.5em;\n",
       "}\n",
       "\n",
       ".CodeMirror {\n",
       "    font-family: 'Monaco for Powerline';\n",
       "}\n",
       "\n",
       "div.cell.code_cell {\n",
       "    font-family: 'Monaco for Powerline';\n",
       "}\n",
       "\n",
       "div.output_area pre {\n",
       "    font-family: 'Monaco for Powerline';\n",
       "}\n",
       "</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.core.display import HTML\n",
    "css = open('style-table.css').read() + open('style-notebook.css').read()\n",
    "HTML('<style>{}</style>'.format(css))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import requests\n",
    "import ipaddress\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "DB_LOCATION = 'lastmile.db'\n",
    "RA_ONE_OFF_EXTENSIONS = 'ra_one_off_extensions'\n",
    "RA_PROBE_API = 'ra_probe_api'\n",
    "RA_ASN = 'ra_asn'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "d_fav_rdns = {\n",
    "\n",
    "     'CABLE'     :     ['hsd1', 'cable', 'hfc']\n",
    "   , 'DSL'       :     ['adsl', 'dsl', 'dslb', 'xdsl', 'luxdsl']\n",
    "   , 'FIBRE'     :     ['fios', 'ftth', 'fiber7', 'fttb']    \n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "con = sqlite3.connect(DB_LOCATION)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''SELECT    asn_holder_name\n",
    "           FROM      %s\n",
    "        '''%(RA_ASN)\n",
    "\n",
    "df1 = pd.read_sql(query, con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "holder_names = []\n",
    "for value in df1.values: holder_names.append(value[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''SELECT    ai.probeid, a.asn_access_type, oe.reverse_dns, '' as access_technology\n",
    "           FROM      %s as oe, %s as ai, %s as a\n",
    "           WHERE     oe.probeid = ai.probeid\n",
    "           AND       ai.asn_v4 =  a.asn\n",
    "        '''%(RA_ONE_OFF_EXTENSIONS, RA_PROBE_API, RA_ASN)\n",
    "\n",
    "df = pd.read_sql(query, con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>probeid</th>\n",
       "      <th>asn_access_type</th>\n",
       "      <th>reverse_dns</th>\n",
       "      <th>access_technology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>DSL</td>\n",
       "      <td>idante.xs4all.nl</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>DSL</td>\n",
       "      <td>soenda.xs4all.nl</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>DSL</td>\n",
       "      <td>a83-163-239-181.adsl.xs4all.nl</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>CABLE</td>\n",
       "      <td>541C2103.cm-5-5a.dynamic.ziggo.nl</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>DSL</td>\n",
       "      <td>dfk.xs4all.nl</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   probeid asn_access_type                        reverse_dns  \\\n",
       "0        3             DSL                   idante.xs4all.nl   \n",
       "1        4             DSL                   soenda.xs4all.nl   \n",
       "2        5             DSL     a83-163-239-181.adsl.xs4all.nl   \n",
       "3        7           CABLE  541C2103.cm-5-5a.dynamic.ziggo.nl   \n",
       "4        8             DSL                      dfk.xs4all.nl   \n",
       "\n",
       "  access_technology  \n",
       "0                    \n",
       "1                    \n",
       "2                    \n",
       "3                    \n",
       "4                    "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def create_set_of_tlds():\n",
    "    try: res = requests.get('http://www.internic.net/domain/root.zone')\n",
    "    except Exception as e: print(e)\n",
    "    else: data = res.text\n",
    "        \n",
    "    tld_list = []\n",
    "    for line in data.split('\\n'):\n",
    "        try: tld = line.split()[0]\n",
    "        except Exception as e: print('.')\n",
    "        else:\n",
    "            tld = tld.rstrip('.')\n",
    "            tld_list.append(tld)\n",
    "\n",
    "    return set(tld_list)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ".\n"
     ]
    }
   ],
   "source": [
    "tlds = create_set_of_tlds()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import re\n",
    "def split_rdns_by_word(rdns):\n",
    "    try: rdns_list = re.split('\\.|-',rdns)\n",
    "    except Exception as e: print(rdns, type(e)); return None\n",
    "    else: return rdns_list\n",
    "df['rdns_list'] = df['reverse_dns'].apply(split_rdns_by_word)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "d = {}\n",
    "for i, row in df.iterrows():\n",
    "    probeid = row['probeid']\n",
    "    word_list = row['rdns_list']\n",
    "\n",
    "    for word in word_list: \n",
    "        if word in tlds: continue\n",
    "        try: int(word)\n",
    "        except:\n",
    "            try: d[word].append(probeid)\n",
    "            except KeyError as e: d[word] = []; d[word].append(probeid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from collections import OrderedDict\n",
    "ordered_d = OrderedDict(sorted(d.items(), key=lambda x: len(x[1]), reverse=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "c: 244\n",
      "comcast: 232\n",
      "hsd1: 232\n",
      "dynamic: 207\n"
     ]
    }
   ],
   "source": [
    "for k, v in ordered_d.items():\n",
    "    if len(v) >= 200: print('%s: %d'%(k, len(v)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for i, row in df.iterrows():\n",
    "    probeid = row['probeid']; rdns_list = row['rdns_list']\n",
    "    for k,v in d_fav_rdns.items():\n",
    "        v_set = set(v); rdns_set = set(rdns_list)     \n",
    "        intersect = v_set.intersection(rdns_set)\n",
    "        if len(intersect) > 0: \n",
    "            df.ix[i, 'access_technology'] = k\n",
    "            break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>probeid</th>\n",
       "      <th>asn_access_type</th>\n",
       "      <th>reverse_dns</th>\n",
       "      <th>access_technology</th>\n",
       "      <th>rdns_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>DSL</td>\n",
       "      <td>idante.xs4all.nl</td>\n",
       "      <td></td>\n",
       "      <td>[idante, xs4all, nl]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>DSL</td>\n",
       "      <td>soenda.xs4all.nl</td>\n",
       "      <td></td>\n",
       "      <td>[soenda, xs4all, nl]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>DSL</td>\n",
       "      <td>a83-163-239-181.adsl.xs4all.nl</td>\n",
       "      <td>DSL</td>\n",
       "      <td>[a83, 163, 239, 181, adsl, xs4all, nl]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>CABLE</td>\n",
       "      <td>541C2103.cm-5-5a.dynamic.ziggo.nl</td>\n",
       "      <td></td>\n",
       "      <td>[541C2103, cm, 5, 5a, dynamic, ziggo, nl]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>DSL</td>\n",
       "      <td>dfk.xs4all.nl</td>\n",
       "      <td></td>\n",
       "      <td>[dfk, xs4all, nl]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   probeid asn_access_type                        reverse_dns  \\\n",
       "0        3             DSL                   idante.xs4all.nl   \n",
       "1        4             DSL                   soenda.xs4all.nl   \n",
       "2        5             DSL     a83-163-239-181.adsl.xs4all.nl   \n",
       "3        7           CABLE  541C2103.cm-5-5a.dynamic.ziggo.nl   \n",
       "4        8             DSL                      dfk.xs4all.nl   \n",
       "\n",
       "  access_technology                                  rdns_list  \n",
       "0                                         [idante, xs4all, nl]  \n",
       "1                                         [soenda, xs4all, nl]  \n",
       "2               DSL     [a83, 163, 239, 181, adsl, xs4all, nl]  \n",
       "3                    [541C2103, cm, 5, 5a, dynamic, ziggo, nl]  \n",
       "4                                            [dfk, xs4all, nl]  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "del df['asn_access_type']\n",
    "del df['reverse_dns']\n",
    "del df['rdns_list']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>probeid</th>\n",
       "      <th>access_technology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>DSL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   probeid access_technology\n",
       "0        3                  \n",
       "1        4                  \n",
       "2        5               DSL\n",
       "3        7                  \n",
       "4        8                  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "fsock = open('temp.sql', 'w')\n",
    "fsock.write('BEGIN TRANSACTION;\\n')\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    probeid = row['probeid']; at = row['access_technology']\n",
    "    if at == '': continue\n",
    "    query = 'UPDATE %s SET access_type_technology = \"%s\" WHERE probeid = %d;'%(RA_ONE_OFF_EXTENSIONS, at, probeid)\n",
    "    fsock.write('%s\\n'%query)\n",
    "    \n",
    "    \n",
    "fsock.write('END TRANSACTION;\\n')\n",
    "fsock.close()"
   ]
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}