---
title: "Analyzing Enron's emails"
author: "Dr. Richard M. Crowley"
date: "ACCT 420, Fall 2019"
output:
  html_document:
    highlight: default
    self_contained: yes
    theme: spacelab
    toc: yes
    toc_float:
      collapsed: no
---


# Importing the data

The data was originally encoded as a zip of folders (users) of folders (outlook folders) of numbered text files (emails).  A python script was used to extract all metadata from emails, clean the To and From fields, strip newlines out of the text, and add everything to a csv.

```{r, warning=F, message=F}
library(tidyverse)
library(quanteda)
library(DT)
library(stringr)
```

### Loading the corpus

Here I will use a version of the Enron data that is straight from the US government's dump of Enron's outlook files.  There is also a dump available on Kaggle at [this link](https://www.kaggle.com/wcukierski/enron-email-dataset/version/2), but it is not quite as clean to work with.

```{r}
# Read in data
df <- read_csv('/media/Data/Data/Enron_Emails/enron_emails.csv')

# Convert date from default Outlook format to a proper date
df$Date <- as.Date(substr(df$Date,1, str_length(df$Date)-3), format="%a, %d %b %Y")
# Trim dates -- some dates are malformed (leading to a missing year, and some
# are after the investigation started on October 19, 2001)
df <- df %>% filter(Date > as.Date("1990-01-01"), Date < as.Date("2001-10-19"))
nrow(df)
head(as.data.frame(df %>% select(Date, From, To)))
```

### Checking for Enron's fraudulent SPVs

Next, let's examine some of the more unique terms used in Enron's fraud, namely, the names of some of the biggest Special Purpose Vehicles (SPVs).  See [this Guardian article](https://www.theguardian.com/film/2002/feb/08/news).  A list of terms to check:

- The use of **SPV**s
- An SPV named **Chewco** Investments
- An SPV named **JEDI** LP
- An SPV names **Obi-1** Holdings LLC

```{r}
df_spv <- df %>% filter(str_detect(str_to_lower(text), "spv"))
df_chewco <- df %>% filter(str_detect(str_to_lower(text), "chewco"))
df_jedi <- df %>% filter(str_detect(str_to_lower(text), "jedi"))
df_obi <- df %>% filter(str_detect(str_to_lower(text), "obi-1"))
```

Next, let's look excerpts from some of these emails.

For SPVs:

```{r}
df_spv$text %>% 
  str_extract_all(regex("([^\\s]+\\s+){0,10}spv.*?(\\s+[^\\s]+){10}", multiline=TRUE, ignore_case=TRUE)) %>%
  unlist() %>%
  unique() %>%
  as.tibble() %>%
  DT::datatable()
```

For Chewco:

```{r}
df_chewco$text %>% 
  str_extract_all(regex("([^\\s]+\\s+){0,10}chewco.*?(\\s+[^\\s]+){10}", multiline=TRUE, ignore_case=TRUE)) %>%
  unlist() %>%
  unique() %>%
  as.tibble() %>%
  DT::datatable()
```

For Jedi:

```{r}
df_jedi$text %>% 
  str_extract_all(regex("([^\\s]+\\s+){0,10}jedi.*?(\\s+[^\\s]+){10}", multiline=TRUE, ignore_case=TRUE)) %>%
  unlist() %>%
  unique() %>%
  as.tibble() %>%
  DT::datatable()
```

There are no hits for Obi-1, however, so we will skip that SPV.

## Enron email network

### Setup

Next, we will take a look at who talked with who at Enron, so we can understand a bit more about the nature of these emails.  Before we do that, though, we need to understand the data structure.  In particular, emails can be sent to multiple individuals at the same time, so we will need to examine this aspect of our data before we can examine who talked with who.

```{r}
# We need to understand how the network is structured first
maxFrom <- max(str_count(df$From, ","), na.rm=T)+1
maxTo <- max(str_count(df$To, ","), na.rm=T)+1
maxCc <- max(str_count(df$Cc, ","), na.rm=T)+1
maxBcc <- max(str_count(df$Bcc, ","), na.rm=T)+1
maxToAll <- max(str_count(df$Bcc, ",") + str_count(df$Cc, ",") + str_count(df$To, ","), na.rm=T)+3
print(paste0("From:", maxFrom, "; To:", maxTo, "; Cc:", maxCc, "; Bcc:", maxBcc, "; ToAll:", maxToAll))
```

For any given email, there is only `r maxFrom` sender (as it should be), but a maximum of `r maxTo` recipients in the To field, `r maxCc` recipients in the Cc field, `r maxBcc` recipients in the Bcc field, or `r maxToAll` across any fields.  As such, we need to do a significant amount of preprocessing to get a usable structure out of this data.

```{r}
library(tidyr)  # for tidying the data

# Use separate() to build additional columns for those with multiple emails
# We will due this on a separate copy of df so as to have a clean copy of df 
# for later analysis
df2 <- df
df2 <- separate(df2, To, paste0("To.", 1:maxTo), sep=", ", fill="right")
df2 <- separate(df2, Cc, paste0("Cc.", 1:maxCc), sep=", ", fill="right")
df2 <- separate(df2, Bcc, paste0("Bcc.", 1:maxBcc), sep=", ", fill="right")
```

To visualize the network of emails, we will use the `visNetwork` R package, which creates interactive network visualizations.

```{r}
library(visNetwork)
nodes <- df2 %>%
  select(starts_with("Bcc."), starts_with("CC."), starts_with("To."), From) %>%
  sapply(unique) %>%
  unlist() %>%
  unique()
enron_nodes <- nodes[str_detect(nodes, "enron\\.com")]
print(paste0("There are ", length(nodes), " emails in the data, including ", length(enron_nodes), " from within Enron."))
```

That's a lot of emails.  We'll have to trim this later.  For now though, let's build a full network graph.  We have the "edges" already, i.e., the emails involved.  Now we need to see who talked to who, and how many times.  For a `visNetwork`, that means we need to code up a data frame with two columns: `from` and `to` (where `to` is going to be a `To`, `Cc` or `Bcc` column).  We will also later count how many times each pair occurs.  Note: `!!` is a shorthand trick to get `dplyr` to use dynamically created variable names.

```{r}
# Build a full edge network
edges <- df2 %>%
  select(From, To.1) %>%
  rename(from=From, to=To.1)
for(var in c(paste0("To.", 2:maxTo), paste0("Cc.", 1:maxCc), paste0("Bcc.", 1:maxBcc))) {
  temp <- df2 %>%
    select(From, !!var) %>%
    rename(from=From, to=!!var)
  edges <- rbind(edges, temp)
}
# Drop any rows with NA -- ~85% of the data
edges <- edges[!is.na(edges$to) & !is.na(edges$from),]
print(paste0("There are ", nrow(edges), " in the data, of which ", nrow(unique(edges)), " are unique."))
```

### Shrinking it down

Let's start by combining identical `(to, from)` pairs.  We'll create a new variable, `count`, to keep track of the number of these.

```{r}
edges <- edges %>% 
  group_by(to, from) %>%
  mutate(count=n()) %>%
  slice(1) %>%
  ungroup()

summary(edges$count)
```

The most common is... Vince Kaminski emailing himself, `r edges[which(edges$count == max(edges$count)),]$count` times.  Let's remove duplicate sender and receivers as well, since that isn't communication **between** people.  This will remove ~1000 edges.

```{r}
edges <- edges[edges$to != edges$from,]
summary(edges$count)
```

To visualize this network, we'll need to significantly shrink this down.  We'll start by determining the most prolific emailers in the network.

```{r}
nodes = data.frame(id=nodes, title=nodes, stringsAsFactors = F)
nodes <- nodes %>%
  left_join(edges[, c("to", "count")], by = c("id" = "to")) %>%
  rename(to_count=count) %>%
  group_by(id) %>%
  mutate(to_count = sum(to_count, na.rm=T)) %>%
  slice(1) %>%
  ungroup() %>%
  left_join(edges[, c("from", "count")], by = c("id" = "from")) %>%
  rename(from_count=count) %>%
  group_by(id) %>%
  mutate(from_count = sum(from_count, na.rm=T)) %>%
  slice(1) %>%
  ungroup() %>%
  mutate(count = to_count + from_count, na.rm=T)
nodes$key_person <- ifelse(nodes$id %in% c('kenneth.lay@enron.com', 'jeff.skilling@enron.com', 'andrew.fastow@enron.com'), 1, 0)
```

We'll take the 3 executives in the data, along with the other top emails up to 100 total accounts.  We will also scale the edge width by the number of emails between individuals, and we will group individuals by their roles within Enron.  To limit the number of edges, we will require more than 10 emails for the edge to be plotted.  I have also included 2 ex post groupings: 1 other key player from the fraud (Greg Whalley), and 1 internal whistle blower, who is none other than Vince Kaminski (the guy who was emailing himself frequently).

```{r}
nodes_f <- nodes %>%
  arrange(-key_person, -count) %>%
  head(n=100) %>%
  mutate(group = ifelse(key_person == 1, "Executives", 
                   ifelse(id %in% c('greg.whalley@enron.com'), "Other key player",
                   ifelse(id %in% c('vkaminski@aol.com','vince.kaminski@enron.com'), 'Internal whistleblower',
                   ifelse(str_detect(id,'enron'), 'Others in Enron', 'Not in Enron')))))
  

nodes_i <- nodes_f[,c("id")]
nodes_i$keep <- 1

edges_f <- edges %>%
  left_join(nodes_i, by=c("to" = "id")) %>%
  rename(keep_to=keep) %>%
  left_join(nodes_i, by=c("from" = "id")) %>%
  mutate(keep = keep * keep_to) %>%
  filter(keep == 1) %>%
  select(-keep, -keep_to) %>%
  filter(count > 10) %>%
  mutate(value = log(count) + 1) %>%
  mutate(title = paste(count, "emails")) %>%
  select(-count)
```

Now, we can plot out the network.  Take a look at the structure of the network, and how the relevant players' networks overlap significantly.  Likewise, notice how Vince Kaminski was also frequently in contact with those individuals, as well as with his own personal account (5,400 times).

```{r}
visNetwork(nodes_f, edges_f) %>%
  visEdges(arrows = "to") %>%
  visGroups(groupname = "Executives", color = list(background = "#75A0DA", border = "#5590BA")) %>%
  visGroups(groupname = "Other key player", color = list(background = "#97C2FC", border = "#77A2DC")) %>%
  visGroups(groupname = "Internal whistleblower", color = list(background = "#FFFF7F", border = "#DDDD5D")) %>%
  visGroups(groupname = "Others in Enron", color = list(background = "#FCC297", border = "#DCA277")) %>% 
  visGroups(groupname = "Not in Enron", color = list(background = "#C2FC97", border = "#A2DC77")) %>% 
  visOptions(highlightNearest = list(enabled = T, degree = 1, hover = T),
             nodesIdSelection = FALSE, selectedBy ="group") %>%
  visLegend()
```


## Using Quanteda

### Term document matrix

Here, we'll take a look at word usage.  The first step is to create a matrix of terms.

```{r, eval=T}
corp <- corpus(df, text_field="text")
```

```{r, eval=T}
tdm <- dfm(corp,
           stem=FALSE,  # Not stemming, as specific word variants may be helpful here
           remove=stopwords(source='smart'),
           remove_punct=TRUE,
           remove_numbers=TRUE) %>%
  dfm_trim(min_termfreq=2, termfreq_type = "count") # 2 so as to keep rare words
```

### Add in an indicator for CEO, CFO, and COO

It is natural to suspect that someone at the top was in charge of the fraud.  Let's make indicators for the key people at Enron.

- CEO: Kenneth Lay
    - `user`: `lay-k`
    - `email`: `kenneth.lay@enron.com`
- CFO: Jeffrey Skilling
    - `user`: `skilling-j`
    - `email`: `jeff.skilling@enron.com`
- COO: Andrew Fastow
    - `user`: Not in the system at the time of the data dump, sadly
    - `email`: `andrew.fastow@enron.com` -- his footprint lives on in others' inboxes

```{r, eval=T}
docvars(tdm, field="CEO_flag") <- ifelse(
  tdm@docvars$From =='kenneth.lay@enron.com' | tdm@docvars$To =='kenneth.lay@enron.com' | tdm@docvars$user == 'lay-k',1,0)
docvars(tdm, field="CFO_flag") <- ifelse(
  tdm@docvars$From =='jeff.skilling@enron.com' | tdm@docvars$To =='jeff.skilling@enron.com' | tdm@docvars$user == 'skilling-j',1,0)
docvars(tdm, field="COO_flag") <- ifelse(
  tdm@docvars$From =='andrew.fastow@enron.com' | tdm@docvars$To =='andrew.fastow@enron.com',1,0)
docvars(tdm, field="Person_wrote") <- ifelse(
  tdm@docvars$From =='kenneth.lay@enron.com','CEO',
  ifelse(tdm@docvars$From =='jeff.skilling@enron.com','CFO',
  ifelse(tdm@docvars$From =='andrew.fastow@enron.com','COO',"Other")))
```

```{r, eval=T}
topfeatures(tdm, n=20, groups = "Person_wrote")
```

### TF-IDF matrix

```{r, eval=T}
tfidf_mat <- dfm_tfidf(tdm, base=2, scheme_tf="prop")
topfeatures(tfidf_mat, n=20, groups = "Person_wrote")
```

Nothing too interesting yet, except perhaps the LJM mentions by the COO is interesting in light of [this](https://www.wsj.com/articles/SB114058115112979889) and [this](https://www.nytimes.com/2002/10/03/business/enron-s-many-strands-fallen-star-from-enron-fast-track-to-total-derailment.html).

### Applying LDA

Note: Due to the size of the text corpus, we will not examine much on implementing a properly specified LDA algorithm, which would likely need to find over 50 topics and would take well over a day to run.  We will, however, take a quick look at a smaller 10 topics model, which still takes around 3 hours to run on a fast computer.

Next, we can output the measures for the STM algorithm.

```{r, eval=F}
# A bunch of emails had no content, like forwards and such.  These get dropped here
out <- convert(tdm, to = 'stm')
# Lost 10140 emails

# Since the above took a while to run, we'll save a point here for replicating in the future
saveRDS(out, '/media/Data/Data/Enron_Emails/pre-LDA.rds')  # 144.3MB
```

Then we run the STM algorithm.

```{r, eval=F}
library(stm)
topics <- stm(out$documents, out$vocab, K=10)
saveRDS(topics, '/media/Data/Data/Enron_Emails/LDA_topics.rds')
```

Next, we'll take a look at the topics.

```{r}
library(stm)
topics <- readRDS('/media/Data/Data/Enron_Emails/LDA_topics.rds')
out <- readRDS('/media/Data/Data/Enron_Emails/pre-LDA.rds')
labelTopics(topics)
```

These are rather noisy due to running such a small LDA model.  Instead of doing a deep dive into the topics, we will just stick to a high level analysis.

We'll combine the topic data with our metadata on each email.  This will allow us to examine various aspects of the emails, such as what each executive at Enron most frequently emailed about.

```{r}
out$meta$Person_wrote <- factor(out$meta$Person_wrote)
out$meta$Person_wrote <- addNA(out$meta$Person_wrote)

doc_index = data.frame(document=names(out$documents))
doc_index = cbind(doc_index, out$meta)

doc_topics = data.frame(document=names(out$documents),
                        topic=1,
                        weight=topics$theta[,1])
for (i in 2:10) {
  temp = data.frame(document=names(out$documents),
                    topic=i,
                    weight=topics$theta[,i])
  doc_topics = rbind(doc_topics, temp)
}

doc_topics <- doc_topics %>% left_join(doc_index)

# Proporitional topics (%)
doc_topics <- doc_topics %>%
  group_by(document) %>%
  mutate(topic_prop = weight / sum(weight)) %>%
  ungroup()
```

Our first analysis is topics by executive:

```{r}
doc_topics %>%
  group_by(Person_wrote, topic) %>%
  mutate(topic_prop = mean(topic_prop)) %>%
  slice(1) %>%
  ungroup() %>%
  ggplot(aes(x=factor(topic), y=topic_prop, fill=factor(topic))) + 
  geom_col() + facet_wrap(~Person_wrote) +
  theme(axis.text.x=element_blank(),axis.ticks.x = element_blank())
```

Above, we see that the three executives talk about rather different topics from the rest of the employees at Enron.  In particular, we see a lot more mentions of topic 6 across all three of them, and also a bit more on topic 9 for the CFO and COO.  For the CEO, we see a high concentration of topic 3 as well.

Next, let's see which topics contain our words of interest:

```{r}
# Tidytext quickly pulls out word's weights
library(tidytext)
topics %>%
  tidy() %>%
  mutate(keyword = ifelse(term == 'jedi', 'jedi', ifelse(term == 'spv', 'spv', ifelse(term == 'chewco', 'chewco', 'other')))) %>%
  group_by(keyword, topic) %>%
  mutate(beta_mean = mean(beta)) %>%
  slice(1) %>%
  ungroup() %>%
  ggplot(aes(x=factor(topic), y=beta_mean, fill=factor(topic))) + 
  geom_col() + facet_wrap(~keyword) +
  theme(axis.ticks.x = element_blank())
```

Here, we see that two of the words we are most interested in are concentrated on topic 6, much like the discussion by the executives.  This indicates that further investigation should dig deeper into the discussions by the executives, as they seem to discuss matters revolving around SPVs and jedi much more than everyone else.

