notebooks/1-data-preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Gmail Experiment: Data Preprocessing\n",
    "Author: Leon Yin and Surya Mattu<br>\n",
    "Links: [GitHub](https://github.com/the-markup/investigation-wheres-my-email/blob/master/notebooks/1-data-preprocessing.ipynb) | [nbviewer](https://nbviewer.jupyter.org/github/the-markup/investigation-wheres-my-email/blob/master/notebooks/1-data-preprocessing.ipynb) | [Story](https://themarkup.org/google-the-giant/2020/02/26/wheres-my-email) <br>\n",
    "\n",
    "This notebook describes the data preprocessing sets for our Gmail promotions experiment. Documentation for this dataset can be found in Github [here](https://github.com/the-markup/investigation-wheres-my-email#data), read more about the context of why and how we collected this dataset in our [paper](https://themarkup.org/google-the-giant/2020/02/26/show-your-work-wheres-my-email)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import glob\n",
    "import gzip\n",
    "\n",
    "import mailbox\n",
    "from tqdm import tqdm\n",
    "import pandas as pd\n",
    "from sklearn.preprocessing import MultiLabelBinarizer\n",
    "\n",
    "sys.path.append('..')\n",
    "from utils.preprocessing import (\n",
    "    find_email,\n",
    "    find_name,\n",
    "    find_tld_from_email,\n",
    "    is_primary,\n",
    "    get_email_id,\n",
    "    get_text,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "gzip_directory = '../data/input/google_takeout_mboxes_gzipped/'\n",
    "fn_newsletter_categories = '../data/input/newsletter_categories.csv'\n",
    "\n",
    "# outputs\n",
    "mbox_directory = '../data/input/google_takeout_mboxes/'\n",
    "fn_output = '../data/intermediates/email_metadata_merged_with_newsletters.csv.gz'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Decompressing mbox files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 6/6 [00:00<00:00, 14952.96it/s]\n"
     ]
    }
   ],
   "source": [
    "# make a directory for the uncompressed mbox files. \n",
    "os.makedirs(mbox_directory, exist_ok=True)\n",
    "\n",
    "# Let's identify the gzipped mbox files\n",
    "mboxes_gzipped = glob.glob(os.path.join(gzip_directory, '*.mbox.gz'))\n",
    "\n",
    "# un-gzip each, and write them to a new destination\n",
    "for fn in tqdm(mboxes_gzipped):\n",
    "    fn_out = fn.replace(gzip_directory, \n",
    "                        mbox_directory).replace('.gz', '')\n",
    "    # un-gzip if does not exist\n",
    "    if os.path.exists(fn_out):\n",
    "        continue \n",
    "    # write to a non-gzipped file.\n",
    "    with gzip.open(fn, 'rt') as f_:\n",
    "        with open(fn_out, 'w') as f:\n",
    "            for line in f_:\n",
    "                f.write(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extracting fields and preprocessing email metadata\n",
    "If you want more information about the available headers, they are described well over [here](https://web.archive.org/save/https://www.makeuseof.com/tag/how-to-trace-your-emails-back-to-the-source/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Here let's identify the input files we're working with\n",
    "mboxes = glob.glob(os.path.join(mbox_directory, '*.mbox'))\n",
    "len(mboxes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# what we'll be keeping from each mbox record\n",
    "keep_from_mbox = [\n",
    "    'Date', \n",
    "    'X-Gmail-Labels', \n",
    "    'X-GM-THRID', \n",
    "    'To', \n",
    "    'From', \n",
    "    'Delivered-To', \n",
    "    'Received', \n",
    "    \"Subject\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2929it [00:24, 120.42it/s]\n",
      "1934it [00:16, 119.86it/s]\n",
      "2473it [00:20, 119.36it/s]\n",
      "448it [00:03, 127.72it/s]\n",
      "3702it [00:31, 117.17it/s]\n",
      "3387it [00:27, 121.04it/s]\n"
     ]
    }
   ],
   "source": [
    "message_list = []\n",
    "for fn in mboxes:\n",
    "    mb = mailbox.mbox(fn)\n",
    "    for _message in tqdm(mb.itervalues()):\n",
    "        # turn the _messages into a dictionary\n",
    "        email_metadata = dict(_message.items())\n",
    "        \n",
    "        # filter out K-V pairs we're not interested in\n",
    "        email_metadata = {\n",
    "            k : v for k, v in email_metadata.items() \n",
    "            if k in keep_from_mbox\n",
    "        }\n",
    "        \n",
    "        # parse text fields, and append to email metadata\n",
    "        text_meta = get_text(_message)\n",
    "        email_metadata = {**email_metadata, **text_meta}\n",
    "        message_list.append(email_metadata)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14873"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(message_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5417"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load the dataframe from the message list and convert cols to datetime\n",
    "messages = pd.DataFrame(message_list)\n",
    "messages.Date = pd.to_datetime(messages.Date, utc=True)\n",
    "messages.loc[:, 'markup_id'] = messages.apply(get_email_id, axis=1)\n",
    "messages.drop_duplicates('markup_id', inplace=True)\n",
    "\n",
    "# this is the start of our experiment\n",
    "messages = messages[messages['Date'] > '2019-10-16']\n",
    "messages.reset_index(drop=True, inplace=True)\n",
    "len(messages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Feature Engineering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert the labels to binary columns and merge back in\n",
    "vect = MultiLabelBinarizer()\n",
    "X = vect.fit_transform(messages[\"X-Gmail-Labels\"].str.split(','))\n",
    "gmail_category_matrix = pd.DataFrame(X, columns=vect.classes_)\n",
    "messages = messages.join(gmail_category_matrix)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We determed which emails are in the primary inbox by looking at the `X-Gmail-Labels` in `gmail_category_matrix`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\u001b[0;31mSignature:\u001b[0m \u001b[0mis_primary\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mdict\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
       "\u001b[0;31mSource:\u001b[0m   \n",
       "\u001b[0;32mdef\u001b[0m \u001b[0mis_primary\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrow\u001b[0m \u001b[0;34m:\u001b[0m \u001b[0mdict\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m    \u001b[0;34m\"\"\"\u001b[0m\n",
       "\u001b[0;34m    Determines if a row is in the Primary inbox. \u001b[0m\n",
       "\u001b[0;34m    These are emails that are in the Inbox, \u001b[0m\n",
       "\u001b[0;34m    but aren't in Spam, Promotions, or Trash. \u001b[0m\n",
       "\u001b[0;34m    \"\"\"\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m    \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m        \u001b[0;32mif\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m           \u001b[0;32mnot\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Spam'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m           \u001b[0;32mnot\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Category Promotions'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m           \u001b[0;32mnot\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Trash'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m           \u001b[0mrow\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Inbox'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m        \u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m            \u001b[0;32mreturn\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m    \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m        \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m        \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\n",
       "\u001b[0;34m\u001b[0m    \u001b[0;32mreturn\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
       "\u001b[0;31mFile:\u001b[0m      ~/code/production-grade/promotions/utils/preprocessing.py\n",
       "\u001b[0;31mType:\u001b[0m      function\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "??is_primary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "messages.loc[:, \"Primary\"] = messages.apply(is_primary, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean up email headers\n",
    "messages = messages[~messages['From'].isnull()]\n",
    "messages.loc[:, \"From_Email\"] = messages['From'].apply(find_email)\n",
    "messages.loc[:, \"From_Domain\"] = messages[\"From_Email\"].apply(find_tld_from_email)\n",
    "messages.loc[:, \"From_Name\"] = messages[\"From\"].apply(find_name)\n",
    "messages.loc[:, \"To_Email\"] = messages['To'].apply(find_email).str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# late presidential candidates we added on 2/4. No email aliases, so we add them.\n",
    "messages.loc[messages['From_Domain'].str.contains('bloomberg'),\n",
    "            'To_Email'] = 'ajcorrigan88+mbloomberg@gmail.com'\n",
    "\n",
    "messages.loc[messages['From_Domain'].str.contains('steyer'),\n",
    "            'To_Email'] = 'ajcorrigan88+tsteyer@gmail.com'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging Metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "231"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# newsletters we signed up for and which alias we used.\n",
    "newsletter_categories = pd.read_csv(fn_newsletter_categories)\n",
    "len(newsletter_categories)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Advocacy organization or think tank    73\n",
       "House battleground campaign            71\n",
       "House battleground official            70\n",
       "Presidential candidate                 17\n",
       "Name: Category, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# breakdown of entities we signed up for\n",
    "newsletter_categories.Category.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "X-GM-THRID                                           1654372657559145454\n",
       "X-Gmail-Labels                           Spam,Category Promotions,Unread\n",
       "Delivered-To                               ajcorrigan88+jwalsh@gmail.com\n",
       "Received               from nationbuilder.com (unknown [52.21.208.178...\n",
       "Date                                           2019-12-30 19:05:11+00:00\n",
       "From                                        Joe Walsh <joe@joewalsh.org>\n",
       "Subject                                                What's he hiding?\n",
       "To                                         ajcorrigan88+jwalsh@gmail.com\n",
       "text                   Hi Friend,\\nIt was hard to miss Donald Trump’s...\n",
       "script                                                                []\n",
       "style                                                               None\n",
       "markup_id                               d07816a54b6b0aa65faf4bcf0c142f2e\n",
       "Archived                                                               0\n",
       "Category Personal                                                      0\n",
       "Category Promotions                                                    1\n",
       "Category Updates                                                       0\n",
       "Important                                                              0\n",
       "Inbox                                                                  0\n",
       "Opened                                                                 0\n",
       "Spam                                                                   1\n",
       "Unread                                                                 1\n",
       "Primary                                                                0\n",
       "From_Email                                              joe@joewalsh.org\n",
       "From_Domain                                                 joewalsh.org\n",
       "From_Name                                                      Joe Walsh\n",
       "To_Email                                   ajcorrigan88+jwalsh@gmail.com\n",
       "Name                                                           Joe Walsh\n",
       "Website                                         https://www.joewalsh.org\n",
       "Email                                      ajcorrigan88+jwalsh@gmail.com\n",
       "Category                                          Presidential candidate\n",
       "Entity_ID                               3ee34b65e61fb39b1c6bf106224eb388\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = messages.merge(newsletter_categories,  how='left',\n",
    "                    left_on='To_Email', right_on='Email')\n",
    "df.iloc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create boolean index to filter each of these categories\n",
    "official = newsletter_categories['Category'] == 'House battleground official'\n",
    "campaign = newsletter_categories['Category'] == 'House battleground campaign'\n",
    "\n",
    "# to update the Website column\n",
    "name2official_site = dict(zip(\n",
    "    newsletter_categories[official].Name, \n",
    "    newsletter_categories[official].Website\n",
    "))\n",
    "\n",
    "name2campaign_site = dict(zip(\n",
    "    newsletter_categories[campaign].Name,\n",
    "    newsletter_categories[campaign].Website\n",
    "))\n",
    "\n",
    "\n",
    "# to update the Entity_ID columm\n",
    "name2official_entity_id= dict(zip(\n",
    "    newsletter_categories[official].Name, \n",
    "    newsletter_categories[official].Entity_ID\n",
    "))\n",
    "\n",
    "name2campaign_entity_id= dict(zip(\n",
    "    newsletter_categories[campaign].Name,\n",
    "    newsletter_categories[campaign].Entity_ID\n",
    "))\n",
    "\n",
    "del official, campaign"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's what one of these dictionaries looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://annwagner.com/'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name2campaign_site['Ann Wagner']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'fb98f8fa9305ff53ef495e5777d9837f'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name2campaign_entity_id['Ann Wagner']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# what subset of the data are we re-assigning?\n",
    "df.loc[(df['From_Domain'].str.contains('.gov')) &\n",
    "       (df['Category'].str.contains('House battleground')), \n",
    "       # what column is being re-assigned?\n",
    "       'Category'] = 'House battleground official'\n",
    "\n",
    "df.loc[(~df['From_Domain'].str.contains('.gov')) &\n",
    "       (df['Category'].str.contains('House battleground')), \n",
    "       'Category'] = 'House battleground campaign'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create boolean index to filter each of these categories\n",
    "campaign = df.Category == 'House battleground campaign'\n",
    "official = df.Category == 'House battleground official'\n",
    "\n",
    "# update Website column\n",
    "df.loc[campaign, \n",
    "       'Website'] = df.loc[campaign].Name.replace(name2campaign_site)\n",
    "df.loc[official, \n",
    "       'Website'] = df.loc[official].Name.replace(name2official_site)\n",
    "\n",
    "# update Entity_ID\n",
    "df.loc[campaign, \n",
    "       'Entity_ID'] = df.loc[campaign].Name.replace(name2campaign_entity_id)\n",
    "df.loc[official, \n",
    "       'Entity_ID'] = df.loc[official].Name.replace(name2official_entity_id)\n",
    "\n",
    "del campaign, official"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop_duplicates(subset='markup_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's do a quick spot check for one of the House members:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "info@annwagner.com          32\n",
       "mo02awima@mail.house.gov    17\n",
       "listserv@ls1.house.gov       1\n",
       "Name: From_Email, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check against the original data\n",
    "email_alias = 'ajcorrigan88+awagneroffice@gmail.com'\n",
    "messages[messages.To_Email == email_alias].From_Email.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "House battleground campaign    32\n",
       "House battleground official    18\n",
       "Name: Category, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Which sender category did we reassign?\n",
    "df[df.Name == 'Ann Wagner'].Category.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fb98f8fa9305ff53ef495e5777d9837f    32\n",
       "0c5cea595602af6e7b7705e77d393d59    18\n",
       "Name: Entity_ID, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the entity ID also\n",
    "df[df.Name == 'Ann Wagner'].Entity_ID.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Name</th>\n",
       "      <th>Website</th>\n",
       "      <th>Email</th>\n",
       "      <th>Category</th>\n",
       "      <th>Entity_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Ann Wagner</td>\n",
       "      <td>http://annwagner.com/</td>\n",
       "      <td>ajcorrigan88+awagneroffice@gmail.com</td>\n",
       "      <td>House battleground campaign</td>\n",
       "      <td>fb98f8fa9305ff53ef495e5777d9837f</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Name                Website                                 Email  \\\n",
       "30  Ann Wagner  http://annwagner.com/  ajcorrigan88+awagneroffice@gmail.com   \n",
       "\n",
       "                       Category                         Entity_ID  \n",
       "30  House battleground campaign  fb98f8fa9305ff53ef495e5777d9837f  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eID = 'fb98f8fa9305ff53ef495e5777d9837f'\n",
    "newsletter_categories[newsletter_categories.Entity_ID == eID]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering out ambiguous emails and Google"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how many emails from Gmail?\n",
    "len(df[df.From_Domain == 'google.com'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "235"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter out emails from senders who didn't send us an email with an alias\n",
    "len(df[(df.Name.isnull()) & (df.From_Domain != 'google.com')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "frc.org                   72\n",
       "google.com                44\n",
       "govdelivery.com           35\n",
       "frcaction.org             31\n",
       "congressnewsletter.net    23\n",
       "crp.org                   22\n",
       "brianfitzpatrick.com      13\n",
       "fotf.com                  12\n",
       "virginiazoo.org           10\n",
       "teapartypatriots.org       4\n",
       "aarp.org                   4\n",
       "house.gov                  3\n",
       "patriotsforperry.com       1\n",
       "girlscouts.org             1\n",
       "nrahq.org                  1\n",
       "vn.va                      1\n",
       "saymine.com                1\n",
       "youtube.com                1\n",
       "Name: From_Domain, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# emails from senders we're removing\n",
    "df[df.Name.isnull()].From_Domain.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5134"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[~df.Name.isnull()]\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "X-GM-THRID                                           1654372657559145454\n",
       "X-Gmail-Labels                           Spam,Category Promotions,Unread\n",
       "Date                                           2019-12-30 19:05:11+00:00\n",
       "Subject                                                What's he hiding?\n",
       "text                   Hi Friend,\\nIt was hard to miss Donald Trump’s...\n",
       "markup_id                               d07816a54b6b0aa65faf4bcf0c142f2e\n",
       "Category Personal                                                      0\n",
       "Category Promotions                                                    1\n",
       "Category Updates                                                       0\n",
       "Inbox                                                                  0\n",
       "Spam                                                                   1\n",
       "Primary                                                                0\n",
       "From_Email                                              joe@joewalsh.org\n",
       "From_Domain                                                 joewalsh.org\n",
       "From_Name                                                      Joe Walsh\n",
       "To_Email                                   ajcorrigan88+jwalsh@gmail.com\n",
       "Name                                                           Joe Walsh\n",
       "Website                                         https://www.joewalsh.org\n",
       "Category                                          Presidential candidate\n",
       "Entity_ID                               3ee34b65e61fb39b1c6bf106224eb388\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove these columns\n",
    "drop_cols = [\n",
    "    'Email',\n",
    "    'script',\n",
    "    'style',\n",
    "    'Received',\n",
    "    'Delivered-To',\n",
    "    'To', 'From', \n",
    "    'Unread',\n",
    "    'Opened',\n",
    "    'Archived',\n",
    "    'Important',    \n",
    "]\n",
    "\n",
    "df = df[[c for c in df.columns if c not in drop_cols]]\n",
    "df.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5134"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's save the dataset as a CSV.\n",
    "df.to_csv(fn_output, index=False, compression='gzip')\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "def summary(messages):\n",
    "    n_all = len(messages)\n",
    "    n_ppro = len(messages[(messages['Category Promotions']) ==1])\n",
    "    n_pro = len(messages[(messages['Category Promotions'] == 1) & \n",
    "                         (messages['Spam'] != 1) ] )\n",
    "    n_u = len(messages[messages['Category Updates'] == 1])\n",
    "    n_s = len(messages[messages['Spam'] == 1])\n",
    "    n_pri = len(messages[messages['Primary'] == 1])\n",
    "    first_email = messages.Date.min()\n",
    "    last_email = messages.Date.max()\n",
    "    senders = messages['From_Email'].nunique()\n",
    "    alias = messages['To_Email'].nunique()\n",
    "    \n",
    "    print(f\"There are a total of {n_all} emails\\n\"\n",
    "          f\"Sent between {first_email.strftime('%Y-%m-%d')} and {last_email.strftime('%Y-%m-%d %H:%M')}\\n\"\n",
    "          f\"From {senders} email addresses to {alias} alias\\n\\n\"\n",
    "          \"Categories:\\n\"\n",
    "          f\"{n_pri} emails or {n_pri / n_all * 100:.2f}% are in the primary inbox\\n\"\n",
    "          f\"{n_pro} emails or {n_pro / n_all * 100:.2f}% are in the promotions tab\\n\"\n",
    "          f\"{n_ppro} emails or {n_ppro / n_all * 100:.2f}% are promotions (includes spam)\\n\"\n",
    "          f\"{n_u} emails or {n_u / n_all * 100:.2f}% are in the updates tab (includes spam)\\n\"\n",
    "          f\"{n_s} emails or {n_s / n_all * 100:.2f}% are in spam\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are a total of 5134 emails\n",
      "Sent between 2019-10-16 and 2020-02-12 19:17\n",
      "From 229 email addresses to 136 alias\n",
      "\n",
      "Categories:\n",
      "546 emails or 10.63% are in the primary inbox\n",
      "2551 emails or 49.69% are in the promotions tab\n",
      "3961 emails or 77.15% are promotions (includes spam)\n",
      "1157 emails or 22.54% are in the updates tab (includes spam)\n",
      "2037 emails or 39.68% are in spam\n"
     ]
    }
   ],
   "source": [
    "summary(df)"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}