scripts/pwned.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analysis of HIBP/YouGov Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of breaches: 293\n",
      "Total number of breached accounts:  5235843322\n",
      "Number of Unique Domains:  278\n"
     ]
    }
   ],
   "source": [
    "# How many breaches does HIBP have data for?\n",
    "breaches = pd.read_json(\"../data/breaches.json\")\n",
    "\n",
    "print(\"Number of breaches:\", breaches.shape[0])\n",
    "print(\"Total number of breached accounts: \", breaches['PwnCount'].sum())\n",
    "print(\"Number of Unique Domains: \", breaches['Domain'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of people: 5000\n",
      "Number of people whose info. was breached: 4142\n",
      "Number of breaches: 14979\n",
      "Number of rows in the final dataset:  15837\n",
      "Sanity check: number of unique IDs in the final dataset: 5000\n"
     ]
    }
   ],
   "source": [
    "# Read in the data\n",
    "profile = pd.read_csv(\"../data/YGOV1058_profile.csv\", low_memory = False)\n",
    "pwned   = pd.read_csv(\"../data/YGOV1058_pwned.csv\", low_memory = False)\n",
    "\n",
    "print(\"Number of people:\", profile.shape[0])\n",
    "print(\"Number of people whose info. was breached:\", pwned['id'].nunique())\n",
    "print(\"Number of breaches:\", pwned.shape[0])\n",
    "\n",
    "# Merge the two files\n",
    "fin_dat = pd.merge(profile, pwned, on = 'id', how = 'left')\n",
    "print(\"Number of rows in the final dataset: \", fin_dat.shape[0])\n",
    "\n",
    "# Sanity check = Number of unique IDs \n",
    "print(\"Sanity check: number of unique IDs in the final dataset:\", fin_dat['id'].nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Already our heads are spinning. 82.84% of Americans have had their credentials breached in one of the big breaches we have public data on. And the 4,142 Americans' credentials have been part of at least 14,979 breaches."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id',\n",
       " 'gender',\n",
       " 'birthyr',\n",
       " 'race',\n",
       " 'educ',\n",
       " 'faminc',\n",
       " 'inputstate',\n",
       " 'pid3',\n",
       " 'pid7',\n",
       " 'votereg',\n",
       " 'ideo5',\n",
       " 'newsint',\n",
       " 'marstat',\n",
       " 'child18',\n",
       " 'employ',\n",
       " 'presvote16post',\n",
       " 'region',\n",
       " 'Title',\n",
       " 'Name',\n",
       " 'Domain',\n",
       " 'BreachDate',\n",
       " 'AddedDate',\n",
       " 'ModifiedDate',\n",
       " 'PwnCount',\n",
       " 'DataClasses',\n",
       " 'IsVerified',\n",
       " 'IsFabricated',\n",
       " 'IsSensitive',\n",
       " 'IsActive',\n",
       " 'IsRetired',\n",
       " 'IsSpamList',\n",
       " 'LogoType']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(fin_dat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>id</th>\n",
       "      <th>gender</th>\n",
       "      <th>birthyr</th>\n",
       "      <th>race</th>\n",
       "      <th>educ</th>\n",
       "      <th>faminc</th>\n",
       "      <th>inputstate</th>\n",
       "      <th>pid3</th>\n",
       "      <th>pid7</th>\n",
       "      <th>votereg</th>\n",
       "      <th>...</th>\n",
       "      <th>ModifiedDate</th>\n",
       "      <th>PwnCount</th>\n",
       "      <th>DataClasses</th>\n",
       "      <th>IsVerified</th>\n",
       "      <th>IsFabricated</th>\n",
       "      <th>IsSensitive</th>\n",
       "      <th>IsActive</th>\n",
       "      <th>IsRetired</th>\n",
       "      <th>IsSpamList</th>\n",
       "      <th>LogoType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>371823339</td>\n",
       "      <td>1</td>\n",
       "      <td>1993</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>39</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-03-08T23:49:53Z</td>\n",
       "      <td>393430309.0</td>\n",
       "      <td>Email addresses, IP addresses, Names, Physical...</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>png</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>371823339</td>\n",
       "      <td>1</td>\n",
       "      <td>1993</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>39</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-08-07T02:51:12Z</td>\n",
       "      <td>85176234.0</td>\n",
       "      <td>Email addresses, Passwords, Usernames</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>svg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>398212310</td>\n",
       "      <td>1</td>\n",
       "      <td>2000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>97</td>\n",
       "      <td>51</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>392933925</td>\n",
       "      <td>1</td>\n",
       "      <td>2000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>34</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-08-07T02:51:12Z</td>\n",
       "      <td>85176234.0</td>\n",
       "      <td>Email addresses, Passwords, Usernames</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>svg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>392933925</td>\n",
       "      <td>1</td>\n",
       "      <td>2000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>34</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2017-03-25T23:43:45Z</td>\n",
       "      <td>29396116.0</td>\n",
       "      <td>Email addresses, IP addresses, Passwords, User...</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>png</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id  gender  birthyr  race  educ  faminc  inputstate  pid3  pid7  \\\n",
       "0  371823339       1     1993     1     2       4          39     3     5   \n",
       "1  371823339       1     1993     1     2       4          39     3     5   \n",
       "2  398212310       1     2000     1     2      97          51     5     8   \n",
       "3  392933925       1     2000     1     1       1          34     2     7   \n",
       "4  392933925       1     2000     1     1       1          34     2     7   \n",
       "\n",
       "   votereg   ...             ModifiedDate     PwnCount  \\\n",
       "0        1   ...     2017-03-08T23:49:53Z  393430309.0   \n",
       "1        1   ...     2017-08-07T02:51:12Z   85176234.0   \n",
       "2        3   ...                      NaN          NaN   \n",
       "3        1   ...     2017-08-07T02:51:12Z   85176234.0   \n",
       "4        1   ...     2017-03-25T23:43:45Z   29396116.0   \n",
       "\n",
       "                                         DataClasses  IsVerified  \\\n",
       "0  Email addresses, IP addresses, Names, Physical...        True   \n",
       "1              Email addresses, Passwords, Usernames        True   \n",
       "2                                                NaN         NaN   \n",
       "3              Email addresses, Passwords, Usernames        True   \n",
       "4  Email addresses, IP addresses, Passwords, User...        True   \n",
       "\n",
       "   IsFabricated  IsSensitive  IsActive IsRetired IsSpamList LogoType  \n",
       "0         False        False      True     False       True      png  \n",
       "1         False        False      True     False      False      svg  \n",
       "2           NaN          NaN       NaN       NaN        NaN      NaN  \n",
       "3         False        False      True     False      False      svg  \n",
       "4         False        False      True     False      False      png  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_dat.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's start by checking how frequently panelists' email is part of a breach."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    5000.00\n",
       "mean        3.00\n",
       "std         2.62\n",
       "min         0.00\n",
       "25%         1.00\n",
       "50%         3.00\n",
       "75%         4.00\n",
       "max        22.00\n",
       "Name: pwn, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# some people's emails are not part of the breach but join produces NaNs for them. We swap them with 0s\n",
    "fin_dat['pwn'] = pd.notna(fin_dat['PwnCount'])\n",
    "\n",
    "fin_dat.groupby(['id'])['pwn'].sum().describe().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On average, panelists' emails are part of 3 breaches (the median is about the same). The range of the number of breaches the panelists' email is part of ranges from 0 to 22! And the standard deviation is 2.62.\n",
    "\n",
    "So how does this exposure vary by gender, race, education, and age? We answer those questions next, starting with gender."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "female    0.54\n",
      "male      0.46\n",
      "Name: sex, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "sex\n",
       "female    3.17\n",
       "male      2.82\n",
       "Name: pwn, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's first recode gender (see the codebook)\n",
    "fin_dat['sex'] = fin_dat['gender'].replace({1: 'male', 2: 'female'})\n",
    "\n",
    "print((fin_dat['sex'].value_counts()/fin_dat['sex'].value_counts().sum()).round(2))\n",
    "\n",
    "fin_dat.groupby(['id', 'sex'])['pwn'].sum().groupby(['sex']).mean().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Women's emails are part of the breaches a bit more frequently than men's emails (Diff = .35). But what does that mean? On average, emails of 100 men would be part of 282 breaches. For 100 women, the number is 317. So women's emails are at about 12% greater risk than men's. This is a bit surprising given men probably have more accounts online.\n",
    "\n",
    "Next, let's look at race."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "White              0.67\n",
       "Hispanic/Latino    0.13\n",
       "Black              0.12\n",
       "Asian              0.03\n",
       "Middle Eastern     0.02\n",
       "Mixed Race         0.01\n",
       "Native American    0.01\n",
       "Other              0.00\n",
       "Name: race_eth, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Again, we start by recoding ints to something that is human readable\n",
    "fin_dat['race_eth'] = fin_dat['race'].replace({1: 'White', \n",
    "                                               2: 'Black', \n",
    "                                               3: 'Hispanic/Latino', \n",
    "                                               4: 'Asian', \n",
    "                                               5: 'Native American', \n",
    "                                               6: 'Middle Eastern', \n",
    "                                               7: 'Mixed Race', \n",
    "                                               8: 'Other'})\n",
    "\n",
    "# Let's first check how many of each we got\n",
    "(fin_dat['race_eth'].value_counts()/fin_dat['race_eth'].value_counts().sum()).round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "race_eth\n",
       "Asian              2.82\n",
       "Black              3.16\n",
       "Hispanic/Latino    2.50\n",
       "Middle Eastern     2.66\n",
       "Mixed Race         2.45\n",
       "Native American    2.96\n",
       "Other              2.92\n",
       "White              3.12\n",
       "Name: pwn, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Mean number of breaches the emails of people of diff. race/ethnicity are part of\n",
    "fin_dat.groupby(['id', 'race_eth'])['pwn'].sum().groupby(['race_eth']).mean().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is compelling. African Americans' and Whites' emails are most frequently part of breaches. The mean is 3.12 and 3.16 for African Americans and Whites respectively. For Hispanics/Latinos, the corresponding number is just 2.5! For Asians, the mean is 2.82, about 9.6% lower than Whites.\n",
    "\n",
    "We next check how frequency of your email being part of breaches varies by how educated a person is."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "HS Grad.                 0.32\n",
       "Some College             0.20\n",
       "4-year College Degree    0.19\n",
       "Postgrad Degree          0.11\n",
       "2-year College Degree    0.11\n",
       "No HS                    0.06\n",
       "Name: educat, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We start again by changing numbers to semantic labels\n",
    "fin_dat['educat'] = fin_dat['educ'].replace({1: 'No HS', \n",
    "                                             2: 'HS Grad.', \n",
    "                                             3: 'Some College', \n",
    "                                             4: '2-year College Degree', \n",
    "                                             5: '4-year College Degree', \n",
    "                                             6: 'Postgrad Degree'})\n",
    "\n",
    "# Let's check how many of each we got\n",
    "(fin_dat['educat'].value_counts()/fin_dat['educat'].value_counts().sum()).round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "educat\n",
       "2-year College Degree    3.07\n",
       "4-year College Degree    3.22\n",
       "HS Grad.                 2.89\n",
       "No HS                    2.35\n",
       "Postgrad Degree          3.20\n",
       "Some College             3.04\n",
       "Name: pwn, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Average by education\n",
    "fin_dat.groupby(['id', 'educat'])['pwn'].sum().groupby(['educat']).mean().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The numbers are once again compelling. The relationship between average number of breaches a panelists' email is part of and their education is roughly monotonic. The average number of breaches people with no HS are part of is just 2.35. Compare this to postgrads, with a mean of 3.20 or over 36% greater!\n",
    "\n",
    "Lastly, we check the relationship with age. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(50, 65]     0.28\n",
      "(35, 50]     0.26\n",
      "(25, 35]     0.19\n",
      "(65, 100]    0.18\n",
      "(18, 25]     0.09\n",
      "Name: agecat, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "agecat\n",
       "(18, 25]     1.96\n",
       "(25, 35]     3.12\n",
       "(35, 50]     3.34\n",
       "(50, 65]     3.29\n",
       "(65, 100]    2.95\n",
       "Name: pwn, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_dat['agecat'] = pd.cut(2018 - fin_dat['birthyr'], [18, 25, 35, 50, 65, 100])\n",
    "print((fin_dat['agecat'].value_counts()/fin_dat['agecat'].value_counts().sum()).round(2))\n",
    "fin_dat.groupby(['id', 'agecat'])['pwn'].sum().groupby(['agecat']).mean().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see an interesting curvilinear pattern with age. Young people (in part because they may have account with some of the compromised websites) have their emails as part of fewest breaches (Mean = 1.96). There is a steep jump to 3.12 for people between ages 25 and 25, and another jump for people between 35 and 50 (Mean = 3.34). People over 65 have emails that are part of somewhat fewer breaches on average (Mean = 2.95).\n",
    "\n",
    "### Now let's check share of different breaches in the data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Domain\n",
       "rivercitymediaonline.com    2913\n",
       "linkedin.com                1089\n",
       "modbsolutions.com           1067\n",
       "myspace.com                 1059\n",
       "data4marketers.com           996\n",
       "cashcrate.com                856\n",
       "adobe.com                    609\n",
       "disqus.com                   570\n",
       "ticketfly.com                393\n",
       "tumblr.com                   340\n",
       "dropbox.com                  288\n",
       "dailymotion.com              255\n",
       "last.fm                      248\n",
       "evony.com                    171\n",
       "clixsense.com                150\n",
       "cafemom.com                  145\n",
       "imesh.com                    144\n",
       "kickstarter.com              140\n",
       "edmodo.com                   130\n",
       "zomato.com                   112\n",
       "neopets.com                  108\n",
       "reverbnation.com              96\n",
       "forum.btcsec.com              77\n",
       "bitly.com                     77\n",
       "r2games.com                   66\n",
       "8tracks.com                   52\n",
       "funimation.com                48\n",
       "diet.com                      45\n",
       "patreon.com                   43\n",
       "yahoo.com                     36\n",
       "                            ... \n",
       "mrexcel.com                    2\n",
       "uiggy.com                      2\n",
       "crackingforum.com              2\n",
       "creative.com                   2\n",
       "wptapl.com                     2\n",
       "unrealengine.com               2\n",
       "moneybookers.com               2\n",
       "qip.ru                         1\n",
       "mcbans.com                     1\n",
       "hackforums.net                 1\n",
       "loungeboard.net                1\n",
       "ahashare.com                   1\n",
       "armyforceonline.com            1\n",
       "hltv.org                       1\n",
       "youku.com                      1\n",
       "insanelyi.com                  1\n",
       "gaadi.com                      1\n",
       "bigmoneyjobs.com               1\n",
       "mdpi.com                       1\n",
       "ps3hax.net                     1\n",
       "evermotion.org                 1\n",
       "ethereum.org                   1\n",
       "bluesnap.com                   1\n",
       "myrepospace.com                1\n",
       "win7vista.com                  1\n",
       "xboxscene.com                  1\n",
       "pixelfederation.com            1\n",
       "sumotorrent.sx                 1\n",
       "paddypower.com                 1\n",
       "theflyonthewall.com            1\n",
       "Length: 156, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_dat.groupby(['Domain']).size().sort_values(ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_dat.groupby(['Domain']).size()[fin_dat.groupby(['Domain']).size() > 100].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11783"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_dat.groupby(['Domain']).size()[fin_dat.groupby(['Domain']).size() > 100].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lastly, we investigate the kind of breaches. HIBP uses the code 'SpamList' for cases where personal data is being used for spamming people. Here's HIBP: \"Occasionally, large volumes of personal data are found being utilised for the purposes of sending targeted spam. This often includes many of the same attributes frequently found in data breaches such as names, addresses, phones numbers and dates of birth. The lists are often aggregated from multiple sources, frequently by eliciting personal information from people with the promise of a monetary reward . Whilst the data may not have been sourced from a breached system, the personal nature of the information and the fact that it's redistributed in this fashion unbeknownst to the owners warrants inclusion here.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5649"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_dat['IsSpamList'].sum(skipna = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Is there any data from 'fabricated breaches'? More on what HIBP means by fabricated breaches: \"Some breaches may be flagged as \"fabricated\". In these cases, it is highly unlikely that the breach contains legitimate data sourced from the alleged site but it may still be sold or traded under the auspices of legitimacy. Often these incidents are comprised of data aggregated from other locations (or may be entirely fabricated), yet still contain actual email addresses of unbeknownst to the account holder. Fabricated breaches are still included in the system because regardless of their legitimacy, they still contain personal information about individuals who want to understand their exposure on the web.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_dat['IsFabricated'].sum(skipna = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What proportion comes from \"unverified\" breaches? Here's HIBP on what it means by unverified breaches: \"Some breaches may be flagged as \"unverified\". In these cases, whilst there is legitimate data within the alleged breach, it may not have been possible to establish legitimacy beyond reasonable doubt. Unverified breaches are still included in the system because regardless of their legitimacy, they still contain personal information about individuals who want to understand their exposure on the web.\" Suggested reading: https://www.troyhunt.com/introducing-unverified-breaches-to-have-i-been-pwned/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14979"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_dat['IsVerified'].sum(skipna = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It appears that an overwhelming majority of the data comes from verified breaches.\n",
    "\n",
    "It is useful to see if associations with socio-economic indicators hold up when we subset on verified, non-spam breaches."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10188"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_small_dat = fin_dat[pd.isna(fin_dat['IsSpamList']) | (fin_dat['IsSpamList'] == False)]\n",
    "fin_small_dat = fin_small_dat[pd.isna(fin_small_dat['IsVerified']) | fin_small_dat['IsVerified'] == True]\n",
    "\n",
    "fin_small_dat.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "educat\n",
       "2-year College Degree    2.10\n",
       "4-year College Degree    2.37\n",
       "HS Grad.                 1.91\n",
       "No HS                    1.53\n",
       "Postgrad Degree          2.30\n",
       "Some College             2.22\n",
       "Name: pwn, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_small_dat.groupby(['id', 'educat'])['pwn'].sum().groupby(['educat']).mean().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pattern holds up. Again, the number of breached accounts of people with college degree or more is higher than people who only got as far as high school.\n",
    "\n",
    "How about men versus women."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sex\n",
       "female    2.15\n",
       "male      2.05\n",
       "Name: pwn, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_small_dat.groupby(['id', 'sex'])['pwn'].sum().groupby(['sex']).mean().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pattern is more attenuated than above with averages about the same. \n",
    "\n",
    "How about when we split by race and ethnicity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "race_eth\n",
       "Asian              2.16\n",
       "Black              2.03\n",
       "Hispanic/Latino    1.73\n",
       "Middle Eastern     2.05\n",
       "Mixed Race         1.70\n",
       "Native American    1.85\n",
       "Other              2.69\n",
       "White              2.21\n",
       "Name: pwn, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_small_dat.groupby(['id', 'race_eth'])['pwn'].sum().groupby(['race_eth']).mean().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, things look a bit different than above. Asians join Whites at the top of the pile. (We have too few Others to say something very confidently.) African Americans and Hispanics' accounts are less frequently breached."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "agecat\n",
       "(18, 25]     1.63\n",
       "(25, 35]     2.44\n",
       "(35, 50]     2.37\n",
       "(50, 65]     2.16\n",
       "(65, 100]    1.78\n",
       "Name: pwn, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_small_dat.groupby(['id', 'agecat'])['pwn'].sum().groupby(['agecat']).mean().round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The general pattern for age remains roughly similar with the middle aged more likely to have their accounts breached compared to the less than 25 and the over 65."
   ]
  }
 ],
 "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
}