scripts/mturk.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bad IP\n",
    "\n",
    "Analyzing the metadata on IPs from which Mechanical Turk survey responses are posted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "mturk_ips = pd.read_csv(\"../data/ip_metadata.csv\", encoding ='latin1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1991"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mturk_ips.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ip',\n",
       " 'maxmind.city.names.en',\n",
       " 'maxmind.country.iso_code',\n",
       " 'maxmind.country.names.en',\n",
       " 'maxmind.location.latitude',\n",
       " 'maxmind.location.longitude',\n",
       " 'maxmind.location.metro_code',\n",
       " 'maxmind.postal.code',\n",
       " 'maxmind.registered_country.names.en',\n",
       " 'maxmind.traits.ip_address',\n",
       " 'tzwhere.timezone',\n",
       " 'abuseipdb.bad_isp',\n",
       " 'ipvoid.blacklist_status',\n",
       " 'ipvoid.city',\n",
       " 'ipvoid.continent',\n",
       " 'ipvoid.country_code',\n",
       " 'ipvoid.latitude_longitude',\n",
       " 'ipvoid.region',\n",
       " 'shodan.area_code',\n",
       " 'shodan.hostnames',\n",
       " 'shodan.country_name',\n",
       " 'shodan.vulns',\n",
       " 'shodan.longitude',\n",
       " 'shodan.city',\n",
       " 'shodan.country_code',\n",
       " 'shodan.latitude',\n",
       " 'shodan.ports',\n",
       " 'virustotal.country',\n",
       " 'virustotal.verbose_msg',\n",
       " 'virustotal.detected_urls']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(mturk_ips)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many of the IPs are duplicated? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "67"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mturk_ips['ip'].duplicated().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What proportion of IPs are from foreign countries?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "United States                       1866\n",
       "Venezuela                             42\n",
       "India                                 17\n",
       "Canada                                 6\n",
       "Puerto Rico                            4\n",
       "Philippines                            3\n",
       "Brazil                                 3\n",
       "Kenya                                  3\n",
       "Honduras                               3\n",
       "Germany                                2\n",
       "Mexico                                 2\n",
       "Tajikistan                             2\n",
       "Uzbekistan                             2\n",
       "United Kingdom                         2\n",
       "Albania                                2\n",
       "Thailand                               2\n",
       "Nepal                                  2\n",
       "Vietnam                                2\n",
       "Egypt                                  2\n",
       "Ecuador                                2\n",
       "Colombia                               1\n",
       "Georgia                                1\n",
       "Seychelles                             1\n",
       "Chile                                  1\n",
       "Argentina                              1\n",
       "Greece                                 1\n",
       "Pakistan                               1\n",
       "Russia                                 1\n",
       "Ghana                                  1\n",
       "Guinea                                 1\n",
       "Republic of Korea                      1\n",
       "Saint Vincent and the Grenadines       1\n",
       "Nigeria                                1\n",
       "Jamaica                                1\n",
       "Czechia                                1\n",
       "Macedonia                              1\n",
       "Suriname                               1\n",
       "Taiwan                                 1\n",
       "United Arab Emirates                   1\n",
       "Portugal                               1\n",
       "Name: maxmind.country.names.en, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mturk_ips['maxmind.country.names.en'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " (US) United States                       1866\n",
       " (VE) Venezuela                             42\n",
       " (IN) India                                 17\n",
       " (CA) Canada                                 6\n",
       " (PR) Puerto Rico                            4\n",
       " (KE) Kenya                                  3\n",
       " (BR) Brazil                                 3\n",
       " (PH) Philippines                            3\n",
       " (HN) Honduras                               3\n",
       " Unknown                                     2\n",
       " (GB) United Kingdom                         2\n",
       " (TJ) Tajikistan                             2\n",
       " (NP) Nepal                                  2\n",
       " (MX) Mexico                                 2\n",
       " (VN) Vietnam                                2\n",
       " (EC) Ecuador                                2\n",
       " (EG) Egypt                                  2\n",
       " (AL) Albania                                2\n",
       " (UZ) Uzbekistan                             2\n",
       " (DE) Germany                                2\n",
       " (TH) Thailand                               2\n",
       " (RU) Russia                                 1\n",
       " (CZ) Czechia                                1\n",
       " (GR) Greece                                 1\n",
       " (MK) Macedonia                              1\n",
       " (PK) Pakistan                               1\n",
       " (AR) Argentina                              1\n",
       " (CO) Colombia                               1\n",
       " (GN) Guinea                                 1\n",
       " (PT) Portugal                               1\n",
       " (NG) Nigeria                                1\n",
       " (TW) Taiwan                                 1\n",
       " (SC) Seychelles                             1\n",
       " (CL) Chile                                  1\n",
       " (KR) Republic of Korea                      1\n",
       " (GH) Ghana                                  1\n",
       " (AE) United Arab Emirates                   1\n",
       " (SR) Suriname                               1\n",
       " (GE) Georgia                                1\n",
       " (JM) Jamaica                                1\n",
       " (VC) Saint Vincent and the Grenadines       1\n",
       "Name: ipvoid.country_code, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mturk_ips['ipvoid.country_code'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "546"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.notna(mturk_ips['shodan.country_code']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "405"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.notna(mturk_ips['virustotal.country']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's see how many of the IPs are blacklisted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "321"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mturk_ips['ipvoid.blacklist_status'].str.contains('BLACKLISTED').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "386"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(mturk_ips['ipvoid.blacklist_status'].str.contains('BLACKLISTED') | (mturk_ips['maxmind.country.names.en'].str.contains('United States') == False) | mturk_ips['ip'].duplicated()).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}