# Honeypot Case Study, Imperative 

In this case study you'll analyse some template emails and actual email from three agencies in the honeypot client list. Your job is to determine if a sent email was derived from a template and if the success of template versus non-template e-mails differs.

We'll treat this case study as a number of separate parts, and return to them later on as we attempt to generalize repetitive code and then move onto object-oriented programming, which will take us to the end of the workshop.

We have two files, one contains the templates and the other are the actual messages sent, with a binary acceptance variable. We need to identify if the message sent was actually a template. This is not as easy as it sounds because we don't have information if a template was used or not, and actually, that wouldn't be much help anyway, since users can modify templates. So we need to somehow compare the text of each message sent to each of the templates and decide if they are similar enough to warrant being called a template. Once we've identified the messages as either having used or not used a template, we should determine of acceptance rate differs between the two. 

1. Read in the interview requests 
1. Read in the message templates
1. Get list of recruiters
1. Combine data sets
1. Use count vectorizer to create a sparse matrix
1. Calculate cosine similarity and assign template id to each message
1. Save new table to a file
1. Perform a $\chi^2$ test to determine if the result is significant


## Environment set-up

```{exercise, name="Begin a new virtual environment"}
Start by making a new virtual environment in VS Code and install the necessary libraries.
```

Use the appropriate command in the integrated terminal window, e.g.:

```
python -m venv .venv
source .venv/bin/activate
```

```{exercise, name="Import packages"}
Begin your script by importing your packages as shown.
```

```{python}
import pandas as pd
from bs4 import BeautifulSoup
from sklearn.feature_extraction.text import CountVectorizer
from sklearn.metrics.pairwise import cosine_similarity
from scipy.stats import chisquare
```

```{exercise, name="Set global parameters"}
Set the threshold for cosine matching. We'll do this technique later on in the script, but we want to set all our global variables at the beginning, so it's easier to keep track of them. In Max's script, this was set to 0.92, but on this little subset of the data, I've reduced it considerably so that we can see a nice result.
```

```{python}
# For cosine matching
SCORE_THRESHOLD = 0.50
```

## Read in the interview requests

```{r echo = FALSE}
interview_requests <- read.csv("data/honeypot/Interview_requests.csv")
```


```{r echo = FALSE, warning = FALSE, message = FALSE}
library(tidyverse)
# table(interview_requests$recruiter_id, interview_requests$accepted)

ggplot(interview_requests, aes(as.factor(recruiter_id), fill = as.factor(accepted))) +
  geom_bar(position = "dodge") +
  labs(x = "recruiter_id", y = "Count", fill = "accepted") +
  scale_fill_brewer(palette = "Set1") +
  theme_minimal() +
  theme(panel.grid.major.x = element_blank())
```

## Read in the message templates

```{exercise, name="Message templates"}
Import the message templates as `df_templates` and examine. We'll use `BeautifulSoup()` to clean up the HTML tags.
```

```{python eval = TRUE, echo = FALSE}
# B - get_message_templates, type == "template" 
df_templates = pd.read_csv('data/honeypot/message_templates.csv')
```

There are four columns:

| Column        | Type | description                         |
|:--------------|:-----|:------------------------------------|
| id            | int  | Each message gets a unique integer id. |
| recruiter_id  | int  | each recruiter is identified by a unique integer id. The data has been anonymized for the purposes of this case study. |
| message       | str  | The actual message, with HTML tags. |
| accepted      | bool | result, positive of negative |

If you've read your file in correctly, the following functions should work:

```{python}
df_templates['message'] = [BeautifulSoup(
            '' if not text else text).get_text() for text in df_templates['message']]
```

```{python}
df_templates['type'] = 'template'
```

```{python}
df_templates.info() # A pandas DataFrame, 15x4
```

```{python}
df_templates.columns # id, recruiter_id, message, type
```

```{exercise, name="Interview requests"}
Import the interview requests as `df_invites` and examine.
```

```{python eval = TRUE, echo = FALSE}
# C - get_interview_requests type == "invite"
df_invites = pd.read_csv('data/honeypot/Interview_requests.csv')
```

There are three columns:

| column | type | description |
|--------|-------------|----|
| id | int | Each message template gets a unique integer id. |
| recruiter_id | int | each recruiter is identified by a unique integer id. The data has been anonymized for the purposes of this case study. This matches the values in `interview_requests.csv` |
| message | str | The actual message, with HTML tags. |

If you've read your file in correctly, the following functions should work:

```{python echo = TRUE}
df_invites['message'] = [BeautifulSoup(
            '' if not text else text).get_text() for text in df_invites['message']]
```

```{python}
df_invites['type'] = 'invite'
```

```{python}
df_invites.info() # A pandas DataFrame, 165x5
```

```{python}
df_invites.columns # id, recruiter_id, message, accepted, type
```

These are the message templates (HTML tags and the preceding "Hi [talent_name]," have been removed for display purposes):

```{r echo = FALSE}
message_templates <- read.csv("data/honeypot/message_templates.csv")
message_templates %>% 
  select(message) %>% 
  mutate(message = as.character(message),
         message = str_remove_all(message, "<{1}.*?\\>{1}"),
         message = str_remove(message, "Hi \\[talent_name\\],")) %>% 
  DT::datatable(filter = 'bottom', options = list(pageLength = 5))
```

## Get list of recruiters


```{exercise, name="See recruiters"}

Create a list, `recruiter_list` that contains the unique recruiter ids from `df_templates`.

```

```{python echo = FALSE, eval = TRUE}
# Create recruiter_list
recruiter_list = df_templates.recruiter_id.unique().tolist()
recruiter_list # [23, 834, 3688]
```

## Combine data sets

```{exercise, name="combine all messages"}
Create `all_messages`, which contains the two data frames together
```

```{python echo = FALSE, eval = TRUE}
# Create all_messages
# all_messages = pd.concat([df_templates, df_invites], ignore_index=True, sort=True)
all_messages = pd.concat([df_templates, df_invites], ignore_index=True)
all_messages.info() # a pandas DataFrame, 180x5
```

```{exercise, name="Add columns"}
Add two new columns to the DateFrame. We'll update the values below 
```

```{python}
all_messages['frequency'] = 0
all_messages['template'] = None

all_messages.info() # a pandas DataFrame, 180x7
all_messages.columns # accepted, id, message, recruiter_id, type, frequency, template
```

We want all the data to be in one DataFrame since we're going to build a corpus that should give an id value to each word. If they were in separate DataFrames, we'd have extra work to bring the two corpora together and make sure the word ids match.

## Count vectorizer

To get familiar with this process, let's readn the `sklearn.feature_extraction.text.CountVectorizer` help page: _Convert a collection of text documents to a matrix of token counts. This implementation produces a sparse representation of the counts using scipy.sparse.csr_matrix._

To understand how this works, let's take a look at a basic example. Remember, making a play dataset is a typical and useful way to get to grips on a new function or concept

```{python eval = FALSE}
corpus = ['first document',
          'second document.',
          'third text',
          'first text',
         ]

vectorizer = CountVectorizer()
X = vectorizer.fit_transform(corpus)
print(X)

```

The data is arranged as per (`text index number`, `index number of word in text`) `count`:

```
## (0, 1)	1
## (0, 0)	1
## (1, 0)	1
## (1, 2)	1
## (2, 4)	1
## (2, 3)	1
## (3, 1)	1
## (3, 3)	1
```

```{python eval = FALSE}
print(vectorizer.get_feature_names())
```

```
## ['document', 'first', 'second', 'text', 'third']
```

```{python eval = FALSE}
print(X.toarray())
```

Each column is a word according to `CV.get_feature_names()`, each row is an input text. The value in the matrix is how many times that word was found in that text.

```
## [[1 1 0 0 0]
##  [1 0 1 0 0]
##  [0 0 0 1 1]
##  [0 1 0 1 0]]
```

This is a sparse matrix, which just means that there will be many zeros. That's expected since there are many words in the corpus that don't appear in each text. We can use this to calculate the cosine similarity.

OK, let's apply this to our data set:

```{exercise, name="create a sparse matrix of word mappings"}
Use count vectorizer as shown in the example above, call your instance `CV` and the `fit_transform()` result `countvectors`.
```

```{python echo = FALSE, eval = TRUE}
# create countvectors from all messages
CV = CountVectorizer()

# Learn the vocabulary dictionary and return term-document matrix.
countvectors = CV.fit_transform(all_messages['message'])
```

```{python echo = TRUE, eval = FALSE}
print(countvectors)
```

```
  (0, 237)	1
  (0, 484)	1
  (0, 548)	1
  (0, 41)	  2
  (0, 294)	1
  (0, 196)	1
  (0, 367)	1
  (0, 512)	1
  (0, 137)	2
  (0, 514)	4
  ...
```

```{python echo = TRUE, eval = FALSE}
# All the unique words in the text
print(CV.get_feature_names())
```

```
## ['00', '11', '12', '14', '15', '16', '23rd', '28th', '30', '30mins', '45', '45min' ...
```

```{python echo = TRUE, eval = FALSE}
type(countvectors) # scipy.sparse.csr.csr_matrix
print(countvectors.toarray())
```

This is a big, sparse matrix. 
```
[[0 0 0 ... 0 2 0]
 [0 0 0 ... 0 2 0]
 [0 0 0 ... 0 4 0]
 ...
 [0 0 0 ... 0 3 0]
 [0 0 0 ... 0 3 0]
 [0 0 0 ... 0 3 0]] 
```

```{python echo = FALSE, eval = FALSE}

# get indices, testing here
# all_messages['recruiter_id'] # first, get all the recruiter ids
# all_messages['recruiter_id'] == 23 # for a specific ID, i.e. make a boolean
# all_messages['type'] == 'template'
# all_messages[(all_messages['recruiter_id'] == 23) & (all_messages['type'] == 'template')].index.tolist()
# recruiter_id = 23

```

## Calculate cosine similarity

Before we calculate the cosine similarity, let's take a look at what it's doing using our little play example:

```{python eval = FALSE}
cosine_similarity(X)
```

```
## array([[1. , 0.5, 0. , 0.5],
##        [0.5, 1. , 0. , 0. ],
##        [0. , 0. , 1. , 0.5],
##        [0.5, 0. , 0.5, 1. ]])
```

Notice that the diagonal is 1, which makes sense, since it's self-matching each text. Recall our four strings, the first 'first document' has a cosine similarity score of 0.5, 0.0, and 0.5 for 'second document.', 'third text', 'first text', respectively. It looks like it just counts the proportion of words in common but it's a bit subtler than that. Let's refer to the source of all knowledge: [Wikipedia](https://en.wikipedia.org/wiki/Cosine_similarity):

> Cosine similarity is a measure of similarity between two non-zero vectors of an inner product space that measures the cosine of the angle between them. The cosine of 0° is 1, and it is less than 1 for any angle in the interval (0,π] radians. It is thus a judgment of orientation and not magnitude: two vectors with the same orientation have a cosine similarity of 1, two vectors oriented at 90° relative to each other have a similarity of 0, and two vectors diametrically opposed have a similarity of -1, independent of their magnitude.

```{exercise, name="Problems with counting words"}
What would happen if we were just counting the number of common words?
```

To over come this problem, cosine similarity measures the cosine of the angle between two vectors projected in a multi-dimensional space. The two vectors, are you probably guessed are arrays containing the word counts of two documents.

If we just asses the number of common words, large texts will appear more similar.

$$\text{similarity} = \cos(\theta) = {\mathbf{A} \cdot \mathbf{B} \over \|\mathbf{A}\| \|\mathbf{B}\|} = \frac{ \sum\limits_{i=1}^{n}{A_i  B_i} }{ \sqrt{\sum\limits_{i=1}^{n}{A_i^2}}  \sqrt{\sum\limits_{i=1}^{n}{B_i^2}} },$$

where $\sum\limits_{i=1}^{n}{A_i  B_i}$ is the dot product of the two vectors $A$ and $B$.

```{python eval = TRUE, echo = TRUE}
# find_used_templates
for recruiter_id in recruiter_list:
    usages = {}
    
    template_indexes = all_messages[(all_messages['recruiter_id'] == recruiter_id) & (all_messages['type'] == 'template')].index.tolist()
    
    invite_indexes = all_messages[(all_messages['recruiter_id'] == recruiter_id) & (all_messages['type'] == 'invite')].index.tolist()
    
    matches = cosine_similarity(countvectors[template_indexes], countvectors[invite_indexes])
    
    matches_zipped = dict(zip(template_indexes, matches))
    
    for template_id in matches_zipped:
        scores = matches_zipped[template_id]
        scores_zipped = dict(zip(invite_indexes, scores))
        template_used = []
    
    for invite_id in scores_zipped:
        if scores_zipped[invite_id] > SCORE_THRESHOLD:
            template_used.append(invite_id)
            usages[template_id] = template_used

print(usages)
```

## Merge data sets

```{exercise, name="Join usages to original data"}
Write a for loop that will merge the two datasets together.
```

```{python eval = TRUE, echo = FALSE}

# H - join_usages_to_original_data - produce final_data
for result in usages:
    usecases = usages[result]
    all_messages.loc[result, 'frequency'] = len(usecases)
    template_id = all_messages.loc[result, 'id'] 
    
    for case in usecases:
        all_messages.loc[case, 'template'] = template_id
```

## Export table

```{exercise, name="Finally we can export the results"}
Use the DataFrame's `.to_csv()` method. Set the index argument to `False` to export to a file.
```

```{python eval = FALSE, echo = FALSE}
all_messages.to_csv('template_usages_final.csv', index=False)
```

Was there a difference?

```{r echo = FALSE}

res92 <- read.csv("data/honeypot/template_usages_final_92.csv")
res50 <- read.csv("data/honeypot/template_usages_final_50.csv")

res50 %>% 
  filter(!is.na(accepted)) %>% 
  mutate(template = replace_na(template, "none")) -> res50

res50 %>%
  ggplot(aes(as.factor(template), fill = factor(accepted))) +
  geom_bar(position = "fill") +
  labs(x = "template", fill = "accepted", y = "proportion") +
  scale_fill_brewer(palette = "Accent") +
  theme_minimal() +
  theme(panel.grid.major.x = element_blank())
```

```{r echo = FALSE}
chi_p <- chisq.test(table(res50$accepted, res50$template))$p.value
```

```{exercise, name="Finally we can export the results"}

Use the `chi2_contingency()` function to calculate the p-value of a $\chi^2$ test.

```

| Index | Value    | Type    | Description              |
|-------|----------|---------|--------------------------|
| 0     | chi2     | float   | The test statistic       | 
| 1     | p        | float   | The p-value of the test  |
| 2     | dof      | int     | Degrees of freedom       |
| 3     | expected | ndarray | The expected frequencies |

It looks like there's no difference between the two groups, but would there be a quantitative way to look at this? 

If we did a $\chi^2$ test, we'll confirm that there is clearly no significant influence ($p = `r chi_p`$) on template use and acceptance rate.