ISP maturity .ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ISP Maturity - ASN ranking from caida, RDAP registry date, cone sizes, Prefix age from dat file ? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df = pd.read_csv(\"remedies.csv\", sep=';')\n",
    "count_remedy = test_df.value_counts(\"ASN\").reset_index()\n",
    "count_remedy.columns=[\"ASN\", \"count\"]\n",
    "data = count_remedy\n",
    "asn_details = {}\n",
    "f=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['ASN'] = count_remedy.apply(lambda x: int(x['ASN'].split()[0]),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def get_asn_metrics(asn : int)->{}:\n",
    "    details = requests.get(\"https://api.asrank.caida.org/v2/restful/asns/\" + str(asn))\n",
    "    asn_details[asn] = details.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "53\n",
      "54\n",
      "55\n",
      "56\n",
      "57\n",
      "58\n",
      "59\n",
      "60\n",
      "61\n",
      "62\n",
      "63\n",
      "64\n",
      "65\n",
      "66\n",
      "67\n",
      "68\n",
      "69\n",
      "70\n",
      "71\n",
      "72\n",
      "73\n",
      "74\n",
      "75\n",
      "76\n",
      "77\n",
      "78\n",
      "79\n",
      "80\n",
      "81\n",
      "82\n",
      "83\n",
      "84\n",
      "85\n",
      "86\n",
      "87\n",
      "88\n",
      "89\n",
      "90\n",
      "91\n",
      "92\n",
      "93\n",
      "94\n",
      "95\n",
      "96\n",
      "97\n",
      "98\n",
      "99\n",
      "100\n",
      "101\n",
      "102\n",
      "103\n",
      "104\n",
      "105\n",
      "106\n",
      "107\n",
      "108\n",
      "109\n",
      "110\n",
      "111\n",
      "112\n",
      "113\n",
      "114\n",
      "115\n",
      "116\n",
      "117\n",
      "118\n",
      "119\n",
      "120\n",
      "121\n",
      "122\n",
      "123\n",
      "124\n",
      "125\n",
      "126\n",
      "127\n",
      "128\n",
      "129\n",
      "130\n",
      "131\n",
      "132\n",
      "133\n",
      "134\n",
      "135\n",
      "136\n",
      "137\n",
      "138\n",
      "139\n",
      "140\n",
      "141\n",
      "142\n",
      "143\n",
      "144\n",
      "145\n",
      "146\n",
      "147\n",
      "148\n",
      "149\n",
      "150\n",
      "151\n",
      "152\n",
      "153\n",
      "154\n",
      "155\n",
      "156\n",
      "157\n",
      "158\n",
      "159\n",
      "160\n",
      "161\n",
      "162\n",
      "163\n",
      "164\n",
      "165\n",
      "166\n",
      "167\n",
      "168\n",
      "169\n",
      "170\n",
      "171\n",
      "172\n",
      "173\n",
      "174\n",
      "175\n",
      "176\n",
      "177\n",
      "178\n",
      "179\n",
      "180\n",
      "181\n",
      "182\n",
      "183\n",
      "184\n",
      "185\n",
      "186\n",
      "187\n",
      "188\n",
      "189\n",
      "190\n",
      "191\n",
      "192\n",
      "193\n",
      "194\n",
      "195\n",
      "196\n",
      "197\n",
      "198\n",
      "199\n",
      "200\n",
      "201\n",
      "202\n",
      "203\n",
      "204\n",
      "205\n",
      "206\n",
      "207\n",
      "208\n",
      "209\n",
      "210\n",
      "211\n",
      "212\n",
      "213\n",
      "214\n",
      "215\n",
      "216\n",
      "217\n",
      "218\n",
      "219\n",
      "220\n",
      "221\n",
      "222\n",
      "223\n",
      "224\n",
      "225\n",
      "226\n",
      "227\n",
      "228\n",
      "229\n",
      "230\n",
      "231\n",
      "232\n",
      "233\n",
      "234\n",
      "235\n",
      "236\n",
      "237\n",
      "238\n",
      "239\n",
      "240\n",
      "241\n",
      "242\n",
      "243\n",
      "244\n",
      "245\n",
      "246\n",
      "247\n",
      "248\n",
      "249\n",
      "250\n",
      "251\n",
      "252\n",
      "253\n",
      "254\n",
      "255\n",
      "256\n",
      "257\n",
      "258\n",
      "259\n",
      "260\n",
      "261\n",
      "262\n",
      "263\n",
      "264\n",
      "265\n",
      "266\n",
      "267\n",
      "268\n",
      "269\n",
      "270\n",
      "271\n",
      "272\n",
      "273\n",
      "274\n",
      "275\n",
      "276\n",
      "277\n",
      "278\n",
      "279\n",
      "280\n",
      "281\n",
      "282\n",
      "283\n",
      "284\n",
      "285\n",
      "286\n",
      "287\n",
      "288\n",
      "289\n",
      "290\n",
      "291\n",
      "292\n",
      "293\n",
      "294\n",
      "295\n",
      "296\n",
      "297\n",
      "298\n",
      "299\n",
      "300\n",
      "301\n",
      "302\n",
      "303\n",
      "304\n",
      "305\n",
      "306\n",
      "307\n",
      "308\n",
      "309\n",
      "310\n",
      "311\n",
      "312\n",
      "313\n",
      "314\n",
      "315\n",
      "316\n",
      "317\n",
      "318\n",
      "319\n",
      "320\n",
      "321\n",
      "322\n",
      "323\n",
      "324\n",
      "325\n",
      "326\n",
      "327\n",
      "328\n",
      "329\n",
      "330\n",
      "331\n",
      "332\n",
      "333\n",
      "334\n",
      "335\n",
      "336\n",
      "337\n",
      "338\n",
      "339\n",
      "340\n",
      "341\n",
      "342\n",
      "343\n",
      "344\n",
      "345\n",
      "346\n",
      "347\n",
      "348\n",
      "349\n",
      "350\n",
      "351\n",
      "352\n",
      "353\n",
      "354\n",
      "355\n",
      "356\n",
      "357\n",
      "358\n",
      "359\n",
      "360\n",
      "361\n",
      "362\n",
      "363\n",
      "364\n",
      "365\n",
      "366\n",
      "367\n",
      "368\n",
      "369\n",
      "370\n",
      "371\n",
      "372\n",
      "373\n",
      "374\n",
      "375\n",
      "376\n",
      "377\n",
      "378\n",
      "379\n",
      "380\n",
      "381\n",
      "382\n",
      "383\n",
      "384\n",
      "385\n",
      "386\n",
      "387\n",
      "388\n",
      "389\n",
      "390\n",
      "391\n",
      "392\n",
      "393\n",
      "394\n",
      "395\n",
      "396\n",
      "397\n",
      "398\n",
      "399\n",
      "400\n",
      "401\n",
      "402\n",
      "403\n",
      "404\n",
      "405\n",
      "406\n",
      "407\n",
      "408\n",
      "409\n",
      "410\n",
      "411\n",
      "412\n",
      "413\n",
      "414\n",
      "415\n",
      "416\n",
      "417\n",
      "418\n",
      "419\n",
      "420\n",
      "421\n",
      "422\n",
      "423\n",
      "424\n",
      "425\n",
      "426\n",
      "427\n",
      "428\n",
      "429\n",
      "430\n",
      "431\n",
      "432\n",
      "433\n",
      "434\n",
      "435\n",
      "436\n",
      "437\n",
      "438\n",
      "439\n",
      "440\n",
      "441\n",
      "442\n",
      "443\n",
      "444\n",
      "445\n",
      "446\n",
      "447\n",
      "448\n",
      "449\n",
      "450\n",
      "451\n",
      "452\n",
      "453\n",
      "454\n",
      "455\n",
      "456\n",
      "457\n",
      "458\n",
      "459\n",
      "460\n",
      "461\n",
      "462\n",
      "463\n",
      "464\n",
      "465\n",
      "466\n",
      "467\n",
      "468\n",
      "469\n",
      "470\n",
      "471\n",
      "472\n",
      "473\n",
      "474\n",
      "475\n",
      "476\n",
      "477\n",
      "478\n",
      "479\n",
      "480\n",
      "481\n",
      "482\n",
      "483\n",
      "484\n",
      "485\n",
      "486\n",
      "487\n",
      "488\n",
      "489\n",
      "490\n",
      "491\n",
      "492\n",
      "493\n",
      "494\n",
      "495\n",
      "496\n",
      "497\n",
      "498\n",
      "499\n",
      "500\n",
      "501\n",
      "502\n",
      "503\n",
      "504\n",
      "505\n",
      "506\n",
      "507\n",
      "508\n",
      "509\n",
      "510\n",
      "511\n",
      "512\n",
      "513\n",
      "514\n",
      "515\n",
      "516\n",
      "517\n",
      "518\n",
      "519\n",
      "520\n",
      "521\n",
      "522\n",
      "523\n",
      "524\n",
      "525\n",
      "526\n",
      "527\n"
     ]
    }
   ],
   "source": [
    "for i in data['ASN']:\n",
    "    get_asn_metrics(i)\n",
    "    f=f+1\n",
    "    print(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_rank(asn):\n",
    "    try:\n",
    "        return asn_details[asn]['data']['asn']['rank']\n",
    "    except TypeError:\n",
    "        return 0\n",
    "    \n",
    "def get_asns_num(asn):\n",
    "    try:\n",
    "        return asn_details[asn]['data']['asn']['cone']['numberAsns']\n",
    "    except TypeError:\n",
    "        return 0\n",
    "    \n",
    "def get_prefixes_num(asn):\n",
    "    try:\n",
    "        return asn_details[asn]['data']['asn']['cone']['numberPrefixes']\n",
    "    except TypeError:\n",
    "        return 0\n",
    "    \n",
    "def get_cust_num(asn):\n",
    "    try:\n",
    "        return asn_details[asn]['data']['asn']['asnDegree']['customer']\n",
    "    except TypeError:\n",
    "        return 0\n",
    "    \n",
    "    \n",
    "def get_addresses_num(asn):\n",
    "    try:\n",
    "        return asn_details[asn]['data']['asn']['cone']['numberAddresses']\n",
    "    except TypeError:\n",
    "        return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ASN</th>\n",
       "      <th>count</th>\n",
       "      <th>rank</th>\n",
       "      <th>cones_asns_number</th>\n",
       "      <th>cones_prefixes_number</th>\n",
       "      <th>customer_number</th>\n",
       "      <th>addresses_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7018</td>\n",
       "      <td>11</td>\n",
       "      <td>21</td>\n",
       "      <td>3236</td>\n",
       "      <td>59794</td>\n",
       "      <td>2507.0</td>\n",
       "      <td>279025493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7922</td>\n",
       "      <td>10</td>\n",
       "      <td>27</td>\n",
       "      <td>2483</td>\n",
       "      <td>50608</td>\n",
       "      <td>106.0</td>\n",
       "      <td>158536535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18881</td>\n",
       "      <td>8</td>\n",
       "      <td>66</td>\n",
       "      <td>694</td>\n",
       "      <td>16960</td>\n",
       "      <td>182.0</td>\n",
       "      <td>44397304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>27839</td>\n",
       "      <td>8</td>\n",
       "      <td>2696</td>\n",
       "      <td>8</td>\n",
       "      <td>212</td>\n",
       "      <td>5.0</td>\n",
       "      <td>113664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>56554</td>\n",
       "      <td>7</td>\n",
       "      <td>17980</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522</th>\n",
       "      <td>328578</td>\n",
       "      <td>1</td>\n",
       "      <td>61514</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523</th>\n",
       "      <td>33152</td>\n",
       "      <td>1</td>\n",
       "      <td>5615</td>\n",
       "      <td>3</td>\n",
       "      <td>43</td>\n",
       "      <td>1.0</td>\n",
       "      <td>17152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>3320</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>3327</td>\n",
       "      <td>76731</td>\n",
       "      <td>581.0</td>\n",
       "      <td>361208023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>525</th>\n",
       "      <td>33523</td>\n",
       "      <td>1</td>\n",
       "      <td>12213</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>65536</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>526</th>\n",
       "      <td>10326</td>\n",
       "      <td>1</td>\n",
       "      <td>11836</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>67584</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>527 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ASN  count   rank  cones_asns_number  cones_prefixes_number  \\\n",
       "0      7018     11     21               3236                  59794   \n",
       "1      7922     10     27               2483                  50608   \n",
       "2     18881      8     66                694                  16960   \n",
       "3     27839      8   2696                  8                    212   \n",
       "4     56554      7  17980                  1                      2   \n",
       "..      ...    ...    ...                ...                    ...   \n",
       "522  328578      1  61514                  1                      1   \n",
       "523   33152      1   5615                  3                     43   \n",
       "524    3320      1     19               3327                  76731   \n",
       "525   33523      1  12213                  1                      1   \n",
       "526   10326      1  11836                  1                      5   \n",
       "\n",
       "     customer_number  addresses_number  \n",
       "0             2507.0         279025493  \n",
       "1              106.0         158536535  \n",
       "2              182.0          44397304  \n",
       "3                5.0            113664  \n",
       "4                0.0              6144  \n",
       "..               ...               ...  \n",
       "522              0.0               256  \n",
       "523              1.0             17152  \n",
       "524            581.0         361208023  \n",
       "525              0.0             65536  \n",
       "526              0.0             67584  \n",
       "\n",
       "[527 rows x 7 columns]"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['cones_asns_number'] = data.ASN.apply(lambda x: get_asns_num(x))\n",
    "data['rank'] = data.ASN.apply(lambda x: get_rank(x))\n",
    "data['cones_prefixes_number'] = data.ASN.apply(lambda x: get_prefixes_num(x))\n",
    "data['customer_number'] = data.ASN.apply(lambda x: get_cust_num(x))\n",
    "data['addresses_number'] = data.ASN.apply(lambda x: get_addresses_num(x))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'data': {'asn': {'rank': 7,\n",
       "   'asn': '6939',\n",
       "   'asnName': 'HURRICANE',\n",
       "   'source': 'ARIN',\n",
       "   'cliqueMember': True,\n",
       "   'seen': True,\n",
       "   'ixp': None,\n",
       "   'longitude': -114.191878506657,\n",
       "   'latitude': 39.6000779441473,\n",
       "   'organization': {'orgId': 'b15dac8bd6'},\n",
       "   'cone': {'numberAsns': 15095,\n",
       "    'numberPrefixes': 273961,\n",
       "    'numberAddresses': 470687235},\n",
       "   'country': {'iso': 'US'},\n",
       "   'asnDegree': {'total': 8471,\n",
       "    'customer': 1718,\n",
       "    'peer': 6752,\n",
       "    'provider': 1}}}}"
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "asn_details[6939]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "rdap_metrics = {}\n",
    "def get_rdap_metrics(asn):\n",
    "    details = requests.get(\"https://rdap.arin.net/registry/autnum/\" + str(asn)).json()\n",
    "    rdap_metrics[asn]= details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "528\n",
      "529\n",
      "530\n",
      "531\n",
      "532\n",
      "533\n",
      "534\n",
      "535\n",
      "536\n",
      "537\n",
      "538\n",
      "539\n",
      "540\n",
      "541\n",
      "542\n",
      "543\n",
      "544\n",
      "545\n",
      "546\n",
      "547\n",
      "548\n",
      "549\n",
      "550\n",
      "551\n",
      "552\n",
      "553\n",
      "554\n",
      "555\n",
      "556\n",
      "557\n",
      "558\n",
      "559\n",
      "560\n",
      "561\n",
      "562\n",
      "563\n",
      "564\n",
      "565\n",
      "566\n",
      "567\n",
      "568\n",
      "569\n",
      "570\n",
      "571\n",
      "572\n",
      "573\n",
      "574\n",
      "575\n",
      "576\n",
      "577\n",
      "578\n",
      "579\n",
      "580\n",
      "581\n",
      "582\n",
      "583\n",
      "584\n",
      "585\n",
      "586\n",
      "587\n",
      "588\n",
      "589\n",
      "590\n",
      "591\n",
      "592\n",
      "593\n",
      "594\n",
      "595\n",
      "596\n",
      "597\n",
      "598\n",
      "599\n",
      "600\n",
      "601\n",
      "602\n",
      "603\n",
      "604\n",
      "605\n",
      "606\n",
      "607\n",
      "608\n",
      "609\n",
      "610\n",
      "611\n",
      "612\n",
      "613\n",
      "614\n",
      "615\n",
      "616\n",
      "617\n",
      "618\n",
      "619\n",
      "620\n",
      "621\n",
      "622\n",
      "623\n",
      "624\n",
      "625\n",
      "626\n",
      "627\n",
      "628\n",
      "629\n",
      "630\n",
      "631\n",
      "632\n",
      "633\n",
      "634\n",
      "635\n",
      "636\n",
      "637\n",
      "638\n",
      "639\n",
      "640\n",
      "641\n",
      "642\n",
      "643\n",
      "644\n",
      "645\n",
      "646\n",
      "647\n",
      "648\n",
      "649\n",
      "650\n",
      "651\n",
      "652\n",
      "653\n",
      "654\n",
      "655\n",
      "656\n",
      "657\n",
      "658\n",
      "659\n",
      "660\n",
      "661\n",
      "662\n",
      "663\n",
      "664\n",
      "665\n",
      "666\n",
      "667\n",
      "668\n",
      "669\n",
      "670\n",
      "671\n",
      "672\n",
      "673\n",
      "674\n",
      "675\n",
      "676\n",
      "677\n",
      "678\n",
      "679\n",
      "680\n",
      "681\n",
      "682\n",
      "683\n",
      "684\n",
      "685\n",
      "686\n",
      "687\n",
      "688\n",
      "689\n",
      "690\n",
      "691\n",
      "692\n",
      "693\n",
      "694\n",
      "695\n",
      "696\n",
      "697\n",
      "698\n",
      "699\n",
      "700\n",
      "701\n",
      "702\n",
      "703\n",
      "704\n",
      "705\n",
      "706\n",
      "707\n",
      "708\n",
      "709\n",
      "710\n",
      "711\n",
      "712\n",
      "713\n",
      "714\n",
      "715\n",
      "716\n",
      "717\n",
      "718\n",
      "719\n",
      "720\n",
      "721\n",
      "722\n",
      "723\n",
      "724\n",
      "725\n",
      "726\n",
      "727\n",
      "728\n",
      "729\n",
      "730\n",
      "731\n",
      "732\n",
      "733\n",
      "734\n",
      "735\n",
      "736\n",
      "737\n",
      "738\n",
      "739\n",
      "740\n",
      "741\n",
      "742\n",
      "743\n",
      "744\n",
      "745\n",
      "746\n",
      "747\n",
      "748\n",
      "749\n",
      "750\n",
      "751\n",
      "752\n",
      "753\n",
      "754\n",
      "755\n",
      "756\n",
      "757\n",
      "758\n",
      "759\n",
      "760\n",
      "761\n",
      "762\n",
      "763\n",
      "764\n",
      "765\n",
      "766\n",
      "767\n",
      "768\n",
      "769\n",
      "770\n",
      "771\n",
      "772\n",
      "773\n",
      "774\n",
      "775\n",
      "776\n",
      "777\n",
      "778\n",
      "779\n",
      "780\n",
      "781\n",
      "782\n",
      "783\n",
      "784\n",
      "785\n",
      "786\n",
      "787\n",
      "788\n",
      "789\n",
      "790\n",
      "791\n",
      "792\n",
      "793\n",
      "794\n",
      "795\n",
      "796\n",
      "797\n",
      "798\n",
      "799\n",
      "800\n",
      "801\n",
      "802\n",
      "803\n",
      "804\n",
      "805\n",
      "806\n",
      "807\n",
      "808\n",
      "809\n",
      "810\n",
      "811\n",
      "812\n",
      "813\n",
      "814\n",
      "815\n",
      "816\n",
      "817\n",
      "818\n",
      "819\n",
      "820\n",
      "821\n",
      "822\n",
      "823\n",
      "824\n",
      "825\n",
      "826\n",
      "827\n",
      "828\n",
      "829\n",
      "830\n",
      "831\n",
      "832\n",
      "833\n",
      "834\n",
      "835\n",
      "836\n",
      "837\n",
      "838\n",
      "839\n",
      "840\n",
      "841\n",
      "842\n",
      "843\n",
      "844\n",
      "845\n",
      "846\n",
      "847\n",
      "848\n",
      "849\n",
      "850\n",
      "851\n",
      "852\n",
      "853\n",
      "854\n",
      "855\n",
      "856\n",
      "857\n",
      "858\n",
      "859\n",
      "860\n",
      "861\n",
      "862\n",
      "863\n",
      "864\n",
      "865\n",
      "866\n",
      "867\n",
      "868\n",
      "869\n",
      "870\n",
      "871\n",
      "872\n",
      "873\n",
      "874\n",
      "875\n",
      "876\n",
      "877\n",
      "878\n",
      "879\n",
      "880\n",
      "881\n",
      "882\n",
      "883\n",
      "884\n",
      "885\n",
      "886\n",
      "887\n",
      "888\n",
      "889\n",
      "890\n",
      "891\n",
      "892\n",
      "893\n",
      "894\n",
      "895\n",
      "896\n",
      "897\n",
      "898\n",
      "899\n",
      "900\n",
      "901\n",
      "902\n",
      "903\n",
      "904\n",
      "905\n",
      "906\n",
      "907\n",
      "908\n",
      "909\n",
      "910\n",
      "911\n",
      "912\n",
      "913\n",
      "914\n",
      "915\n",
      "916\n",
      "917\n",
      "918\n",
      "919\n",
      "920\n",
      "921\n",
      "922\n",
      "923\n",
      "924\n",
      "925\n",
      "926\n",
      "927\n",
      "928\n",
      "929\n",
      "930\n",
      "931\n",
      "932\n",
      "933\n",
      "934\n",
      "935\n",
      "936\n",
      "937\n",
      "938\n",
      "939\n",
      "940\n",
      "941\n",
      "942\n",
      "943\n",
      "944\n",
      "945\n",
      "946\n",
      "947\n",
      "948\n",
      "949\n",
      "950\n",
      "951\n",
      "952\n",
      "953\n",
      "954\n",
      "955\n",
      "956\n",
      "957\n",
      "958\n",
      "959\n",
      "960\n",
      "961\n",
      "962\n",
      "963\n",
      "964\n",
      "965\n",
      "966\n",
      "967\n",
      "968\n",
      "969\n",
      "970\n",
      "971\n",
      "972\n",
      "973\n",
      "974\n",
      "975\n",
      "976\n",
      "977\n",
      "978\n",
      "979\n",
      "980\n",
      "981\n",
      "982\n",
      "983\n",
      "984\n",
      "985\n",
      "986\n",
      "987\n",
      "988\n",
      "989\n",
      "990\n",
      "991\n",
      "992\n",
      "993\n",
      "994\n",
      "995\n",
      "996\n",
      "997\n",
      "998\n",
      "999\n",
      "1000\n",
      "1001\n",
      "1002\n",
      "1003\n",
      "1004\n",
      "1005\n",
      "1006\n",
      "1007\n",
      "1008\n",
      "1009\n",
      "1010\n",
      "1011\n",
      "1012\n",
      "1013\n",
      "1014\n",
      "1015\n",
      "1016\n",
      "1017\n",
      "1018\n",
      "1019\n",
      "1020\n",
      "1021\n",
      "1022\n",
      "1023\n",
      "1024\n",
      "1025\n",
      "1026\n",
      "1027\n",
      "1028\n",
      "1029\n",
      "1030\n",
      "1031\n",
      "1032\n",
      "1033\n",
      "1034\n",
      "1035\n",
      "1036\n",
      "1037\n",
      "1038\n",
      "1039\n",
      "1040\n",
      "1041\n",
      "1042\n",
      "1043\n",
      "1044\n",
      "1045\n",
      "1046\n",
      "1047\n",
      "1048\n",
      "1049\n",
      "1050\n",
      "1051\n",
      "1052\n",
      "1053\n",
      "1054\n"
     ]
    }
   ],
   "source": [
    "for i in data['ASN']:\n",
    "    get_rdap_metrics(i)\n",
    "    f=f+1\n",
    "    print(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'rdapConformance': ['rdap_level_0'],\n",
       " 'notices': [{'title': 'Terms of Service',\n",
       "   'description': ['By using the ARIN RDAP/Whois service, you are agreeing to the RDAP/Whois Terms of Use'],\n",
       "   'links': [{'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "     'rel': 'about',\n",
       "     'type': 'text/html',\n",
       "     'href': 'https://www.arin.net/resources/registry/whois/tou/'}]},\n",
       "  {'title': 'Whois Inaccuracy Reporting',\n",
       "   'description': ['If you see inaccuracies in the results, please visit: '],\n",
       "   'links': [{'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "     'rel': 'about',\n",
       "     'type': 'text/html',\n",
       "     'href': 'https://www.arin.net/resources/registry/whois/inaccuracy_reporting/'}]},\n",
       "  {'title': 'Copyright Notice',\n",
       "   'description': ['Copyright 1997-2020, American Registry for Internet Numbers, Ltd.']}],\n",
       " 'handle': 'AS6939',\n",
       " 'startAutnum': 6939,\n",
       " 'endAutnum': 6939,\n",
       " 'name': 'HURRICANE',\n",
       " 'events': [{'eventAction': 'last changed',\n",
       "   'eventDate': '2003-11-04T02:00:04-05:00'},\n",
       "  {'eventAction': 'registration', 'eventDate': '1996-06-28T00:00:00-04:00'}],\n",
       " 'links': [{'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "   'rel': 'self',\n",
       "   'type': 'application/rdap+json',\n",
       "   'href': 'https://rdap.arin.net/registry/autnum/6939'},\n",
       "  {'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "   'rel': 'alternate',\n",
       "   'type': 'application/xml',\n",
       "   'href': 'https://whois.arin.net/rest/asn/AS6939'}],\n",
       " 'entities': [{'handle': 'HURC',\n",
       "   'vcardArray': ['vcard',\n",
       "    [['version', {}, 'text', '4.0'],\n",
       "     ['fn', {}, 'text', 'Hurricane Electric LLC'],\n",
       "     ['adr',\n",
       "      {'label': '760 Mission Court\\nFremont\\nCA\\n94539\\nUnited States'},\n",
       "      'text',\n",
       "      ['', '', '', '', '', '', '']],\n",
       "     ['kind', {}, 'text', 'org']]],\n",
       "   'roles': ['registrant'],\n",
       "   'links': [{'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "     'rel': 'self',\n",
       "     'type': 'application/rdap+json',\n",
       "     'href': 'https://rdap.arin.net/registry/entity/HURC'},\n",
       "    {'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "     'rel': 'alternate',\n",
       "     'type': 'application/xml',\n",
       "     'href': 'https://whois.arin.net/rest/org/HURC'}],\n",
       "   'events': [{'eventAction': 'last changed',\n",
       "     'eventDate': '2018-02-09T14:20:25-05:00'}],\n",
       "   'entities': [{'handle': 'ZH17-ARIN',\n",
       "     'vcardArray': ['vcard',\n",
       "      [['version', {}, 'text', '4.0'],\n",
       "       ['adr',\n",
       "        {'label': '760 Mission Court\\nFremont\\nCA\\n94539\\nUnited States'},\n",
       "        'text',\n",
       "        ['', '', '', '', '', '', '']],\n",
       "       ['fn', {}, 'text', 'Hurricane Electric'],\n",
       "       ['org', {}, 'text', 'Hurricane Electric'],\n",
       "       ['kind', {}, 'text', 'group'],\n",
       "       ['email', {}, 'text', 'hostmaster@he.net'],\n",
       "       ['tel', {'type': ['work', 'voice']}, 'text', '+1-510-580-4100']]],\n",
       "     'roles': ['technical', 'administrative'],\n",
       "     'links': [{'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "       'rel': 'self',\n",
       "       'type': 'application/rdap+json',\n",
       "       'href': 'https://rdap.arin.net/registry/entity/ZH17-ARIN'},\n",
       "      {'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "       'rel': 'alternate',\n",
       "       'type': 'application/xml',\n",
       "       'href': 'https://whois.arin.net/rest/poc/ZH17-ARIN'}],\n",
       "     'events': [{'eventAction': 'last changed',\n",
       "       'eventDate': '2020-06-12T14:48:41-04:00'},\n",
       "      {'eventAction': 'registration',\n",
       "       'eventDate': '2000-02-23T10:29:04-05:00'}],\n",
       "     'status': ['validated'],\n",
       "     'port43': 'whois.arin.net',\n",
       "     'objectClassName': 'entity'},\n",
       "    {'handle': 'ABUSE1036-ARIN',\n",
       "     'vcardArray': ['vcard',\n",
       "      [['version', {}, 'text', '4.0'],\n",
       "       ['adr',\n",
       "        {'label': '760 Mission Court\\nFremont\\nCA\\n94539\\nUnited States'},\n",
       "        'text',\n",
       "        ['', '', '', '', '', '', '']],\n",
       "       ['fn', {}, 'text', 'Abuse Department'],\n",
       "       ['org', {}, 'text', 'Abuse Department'],\n",
       "       ['kind', {}, 'text', 'group'],\n",
       "       ['email', {}, 'text', 'abuse@he.net'],\n",
       "       ['tel', {'type': ['work', 'voice']}, 'text', '+1-510-580-4100']]],\n",
       "     'roles': ['abuse'],\n",
       "     'remarks': [{'title': 'Unvalidated POC',\n",
       "       'description': ['ARIN has attempted to validate the data for this POC, but has received no response from the POC since 2019-09-19']}],\n",
       "     'links': [{'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "       'rel': 'self',\n",
       "       'type': 'application/rdap+json',\n",
       "       'href': 'https://rdap.arin.net/registry/entity/ABUSE1036-ARIN'},\n",
       "      {'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "       'rel': 'alternate',\n",
       "       'type': 'application/xml',\n",
       "       'href': 'https://whois.arin.net/rest/poc/ABUSE1036-ARIN'}],\n",
       "     'events': [{'eventAction': 'last changed',\n",
       "       'eventDate': '2018-09-19T13:37:13-04:00'},\n",
       "      {'eventAction': 'registration',\n",
       "       'eventDate': '2005-11-03T21:30:06-05:00'}],\n",
       "     'port43': 'whois.arin.net',\n",
       "     'objectClassName': 'entity'}],\n",
       "   'port43': 'whois.arin.net',\n",
       "   'objectClassName': 'entity'},\n",
       "  {'handle': 'ZH17-ARIN',\n",
       "   'vcardArray': ['vcard',\n",
       "    [['version', {}, 'text', '4.0'],\n",
       "     ['adr',\n",
       "      {'label': '760 Mission Court\\nFremont\\nCA\\n94539\\nUnited States'},\n",
       "      'text',\n",
       "      ['', '', '', '', '', '', '']],\n",
       "     ['fn', {}, 'text', 'Hurricane Electric'],\n",
       "     ['org', {}, 'text', 'Hurricane Electric'],\n",
       "     ['kind', {}, 'text', 'group'],\n",
       "     ['email', {}, 'text', 'hostmaster@he.net'],\n",
       "     ['tel', {'type': ['work', 'voice']}, 'text', '+1-510-580-4100']]],\n",
       "   'roles': ['technical'],\n",
       "   'links': [{'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "     'rel': 'self',\n",
       "     'type': 'application/rdap+json',\n",
       "     'href': 'https://rdap.arin.net/registry/entity/ZH17-ARIN'},\n",
       "    {'value': 'https://rdap.arin.net/registry/autnum/6939',\n",
       "     'rel': 'alternate',\n",
       "     'type': 'application/xml',\n",
       "     'href': 'https://whois.arin.net/rest/poc/ZH17-ARIN'}],\n",
       "   'events': [{'eventAction': 'last changed',\n",
       "     'eventDate': '2020-06-12T14:48:41-04:00'},\n",
       "    {'eventAction': 'registration', 'eventDate': '2000-02-23T10:29:04-05:00'}],\n",
       "   'status': ['validated'],\n",
       "   'port43': 'whois.arin.net',\n",
       "   'objectClassName': 'entity'}],\n",
       " 'port43': 'whois.arin.net',\n",
       " 'objectClassName': 'autnum'}"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdap_metrics[6939]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\seven\\pycharmprojects\\econ_sec\\venv\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3145: DtypeWarning: Columns (0) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    }
   ],
   "source": [
    "recent_df = pd.read_csv(\"recent_tests.csv\", sep=';', dtype={'Sesssion': 'str'})\n",
    "\n",
    "#Resetting types\n",
    "recent_df.astype({'Timestamp': 'str',\n",
    "                'Client IP Block': 'str',\n",
    "                'ASN': 'str', \n",
    "                'Country': 'str', \n",
    "                'NAT': 'str', \n",
    "                'Outbound Private Status': 'str',\n",
    "               'Adjacent Spoof Prefix Length': 'str'}).dtypes\n",
    "\n",
    "df_blocked = recent_df.loc[recent_df['Outbound Routable Status'] == 'blocked']\n",
    "df_received = recent_df.loc[recent_df['Outbound Routable Status'] == 'received']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ASN</th>\n",
       "      <th>Spoofable Blocks</th>\n",
       "      <th>Blocks Fixed</th>\n",
       "      <th>total</th>\n",
       "      <th>remediation rate</th>\n",
       "      <th>cones_asns_number</th>\n",
       "      <th>rank</th>\n",
       "      <th>cones_prefixes_number</th>\n",
       "      <th>customer_number</th>\n",
       "      <th>addresses_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10429</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>3236</td>\n",
       "      <td>21</td>\n",
       "      <td>59794</td>\n",
       "      <td>2507.0</td>\n",
       "      <td>279025493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10965 (MRNET)</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>2483</td>\n",
       "      <td>27</td>\n",
       "      <td>50608</td>\n",
       "      <td>106.0</td>\n",
       "      <td>158536535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11427 (TWC-11427-TEXAS)</td>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>0.176471</td>\n",
       "      <td>694</td>\n",
       "      <td>66</td>\n",
       "      <td>16960</td>\n",
       "      <td>182.0</td>\n",
       "      <td>44397304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11525 (HRTC)</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>8</td>\n",
       "      <td>2696</td>\n",
       "      <td>212</td>\n",
       "      <td>5.0</td>\n",
       "      <td>113664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12540 (IDECNET-AS)</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>1</td>\n",
       "      <td>17980</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>8473 (BAHNHOF)</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>9</td>\n",
       "      <td>2504</td>\n",
       "      <td>49</td>\n",
       "      <td>8.0</td>\n",
       "      <td>48640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>9009 (M247)</td>\n",
       "      <td>38</td>\n",
       "      <td>5</td>\n",
       "      <td>43</td>\n",
       "      <td>0.116279</td>\n",
       "      <td>28</td>\n",
       "      <td>1049</td>\n",
       "      <td>161</td>\n",
       "      <td>3.0</td>\n",
       "      <td>39936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>9070 (Cooolbox)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>2</td>\n",
       "      <td>8552</td>\n",
       "      <td>9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>9318 (SKB-AS)</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>0.187500</td>\n",
       "      <td>12</td>\n",
       "      <td>2055</td>\n",
       "      <td>48</td>\n",
       "      <td>7.0</td>\n",
       "      <td>16128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>9541 (CYBERNET-AP)</td>\n",
       "      <td>67</td>\n",
       "      <td>4</td>\n",
       "      <td>71</td>\n",
       "      <td>0.056338</td>\n",
       "      <td>9</td>\n",
       "      <td>2550</td>\n",
       "      <td>57</td>\n",
       "      <td>6.0</td>\n",
       "      <td>22528</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         ASN  Spoofable Blocks  Blocks Fixed  total  \\\n",
       "0                      10429                 5             1      6   \n",
       "1              10965 (MRNET)                 2             2      4   \n",
       "2    11427 (TWC-11427-TEXAS)                14             3     17   \n",
       "3               11525 (HRTC)                 8             2     10   \n",
       "4         12540 (IDECNET-AS)                 2             1      3   \n",
       "..                       ...               ...           ...    ...   \n",
       "129           8473 (BAHNHOF)                 1             3      4   \n",
       "130              9009 (M247)                38             5     43   \n",
       "131          9070 (Cooolbox)                 1             1      2   \n",
       "132            9318 (SKB-AS)                13             3     16   \n",
       "133       9541 (CYBERNET-AP)                67             4     71   \n",
       "\n",
       "     remediation rate  cones_asns_number   rank  cones_prefixes_number  \\\n",
       "0            0.166667               3236     21                  59794   \n",
       "1            0.500000               2483     27                  50608   \n",
       "2            0.176471                694     66                  16960   \n",
       "3            0.200000                  8   2696                    212   \n",
       "4            0.333333                  1  17980                      2   \n",
       "..                ...                ...    ...                    ...   \n",
       "129          0.750000                  9   2504                     49   \n",
       "130          0.116279                 28   1049                    161   \n",
       "131          0.500000                  2   8552                      9   \n",
       "132          0.187500                 12   2055                     48   \n",
       "133          0.056338                  9   2550                     57   \n",
       "\n",
       "     customer_number  addresses_number  \n",
       "0             2507.0         279025493  \n",
       "1              106.0         158536535  \n",
       "2              182.0          44397304  \n",
       "3                5.0            113664  \n",
       "4                0.0              6144  \n",
       "..               ...               ...  \n",
       "129              8.0             48640  \n",
       "130              3.0             39936  \n",
       "131              1.0              5376  \n",
       "132              7.0             16128  \n",
       "133              6.0             22528  \n",
       "\n",
       "[134 rows x 10 columns]"
      ]
     },
     "execution_count": 222,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spoofable_df = df_received.groupby('ASN')\n",
    "spoofable_df = spoofable_df.count()[['Client IP Block']]\n",
    "spoofable_df = spoofable_df.rename(columns={\"Client IP Block\" : \"Spoofable Blocks\"})\n",
    "\n",
    "remedies_df = test_df.groupby('ASN')\n",
    "remedies_df = remedies_df.count()[['IP Block']]\n",
    "remedies_df = remedies_df.rename(columns={\"IP Block\" : \"Blocks Fixed\"})\n",
    "final_df = spoofable_df.merge(remedies_df, on=\"ASN\")\n",
    "final_df = final_df.rename(columns={\"IP Block\": \"Fixed Blocks\"})\n",
    "final_df['total'] = final_df['Spoofable Blocks'] + final_df['Blocks Fixed']\n",
    "final_df['remediation rate'] = final_df['Blocks Fixed']/final_df['total']  # REMEDIATION RATE ACCORDING TO FORMULA\n",
    "final_df = final_df.reset_index()\n",
    "final_df['cones_asns_number'] = data.ASN.apply(lambda x: get_asns_num(x))\n",
    "final_df['rank'] = data.ASN.apply(lambda x: get_rank(x))\n",
    "final_df['cones_prefixes_number'] = data.ASN.apply(lambda x: get_prefixes_num(x))\n",
    "final_df['customer_number'] = data.ASN.apply(lambda x: get_cust_num(x))\n",
    "final_df['addresses_number'] = data.ASN.apply(lambda x: get_addresses_num(x))\n",
    "final_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Remediation rate correlated with\n",
      "Number of prefixes in cone:  0.053311990980362434\n",
      "Rank:  -0.0065218718834800906\n",
      "Number of ASNs in cone:  0.07542089380964695\n",
      "Number of customers:  0.01733231536330985\n",
      "Number of addresses in cone:  0.05419740574598763\n"
     ]
    }
   ],
   "source": [
    "non_zero_cust_num = final_df[final_df['customer_number']!=0]\n",
    "print(\"Remediation rate correlated with\")\n",
    "print(\"Number of prefixes in cone: \",non_zero_cust_num['remediation rate'].corr(non_zero_cust_num['cones_prefixes_number']))\n",
    "print(\"Rank: \",final_df['remediation rate'].corr(final_df['rank']))\n",
    "print(\"Number of ASNs in cone: \",final_df['remediation rate'].corr(final_df['cones_asns_number']))\n",
    "print(\"Number of customers: \",final_df['remediation rate'].corr(final_df['customer_number']))\n",
    "print(\"Number of addresses in cone: \",final_df['remediation rate'].corr(final_df['addresses_number']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test Work"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pyasn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7018, '63.241.91.0/24')"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "asndb = pyasn.pyasn('tmp/ipasn_20200107.dat')\n",
    "asndb.lookup('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.13122750852300372\n",
      "-0.12127921663473666\n",
      "0.11238985647855468\n",
      "0.12414412267690868\n",
      "0.11050959087257352\n"
     ]
    }
   ],
   "source": [
    "non_zero_cust_num = data[data['customer_number']!=0]\n",
    "print(non_zero_cust_num['count'].corr(non_zero_cust_num['cones_prefixes_number']))\n",
    "print(data['count'].corr(data['rank']))\n",
    "print(data['count'].corr(data['cones_asns_number']))\n",
    "print(data['count'].corr(data['cones_prefixes_number']))\n",
    "print(data['count'].corr(data['addresses_number']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-0.12127921663473666\n",
      "0.11238985647855468\n",
      "0.12414412267690868\n"
     ]
    }
   ],
   "source": [
    "print(data['count'].corr(data['rank']))\n",
    "print(data['count'].corr(data['cones_asns_number']))\n",
    "print(data['count'].corr(data['cones_prefixes_number']))"
   ]
  }
 ],
 "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}