<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/should-old-acquaintance-be-forgot-tidying-up-mac-mail/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://joachim-gassen.github.io/2018/12/should-old-acquaintance-be-forgot-tidying-up-mac-mail/"> An Accounting and Data Science Nerd's Corner</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>As the year is closing down, why not spend some of the free time to explore your email data using R and the tidyverse? When I learned that Mac OS Mail stores its internal data in a SQLite database file I was hooked. A quick dive in your email archive might uncover some of your old acquaintances. Let’s take a peak.</p>
<p>Obviously, the below is only applicable when you are a regular user of the Mail app for Mac OS. As a first step, you need to locate the file <code>Envelope Index</code> that tends to be located in <code>~/Library/Mail/V6/MailData/</code>. Copy this file somewhere and adjust the path provided in <code>mail_db</code> to point to this copy. Do not work with the original file.</p>
<pre><code>library(DBI)
library(tidyverse)
library(lubridate)
library(ExPanDaR)
library(ggridges)

mail_db &lt;- &quot;data/EI&quot;

con &lt;- dbConnect(RSQLite::SQLite(), mail_db)</code></pre>
<p>Now you have established a database connection to your copy of Mac OS Mail’s internal data. I you receive an error message, check whether you have the required packages (including <code>RSQLite</code>) installed. With this established connection, we can now see what the database has in store for us.</p>
<pre><code>kable(dbListTables(con), col.names = &quot;List of Tables&quot;)</code></pre>
<table>
<thead>
<tr>
<th align="left">List of Tables</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">action_ews_messages</td>
</tr>
<tr>
<td align="left">action_imap_messages</td>
</tr>
<tr>
<td align="left">action_labels</td>
</tr>
<tr>
<td align="left">action_messages</td>
</tr>
<tr>
<td align="left">addresses</td>
</tr>
<tr>
<td align="left">attachments</td>
</tr>
<tr>
<td align="left">duplicates_unread_count</td>
</tr>
<tr>
<td align="left">events</td>
</tr>
<tr>
<td align="left">ews_copy_action_messages</td>
</tr>
<tr>
<td align="left">ews_folders</td>
</tr>
<tr>
<td align="left">imap_copy_action_messages</td>
</tr>
<tr>
<td align="left">imap_labels</td>
</tr>
<tr>
<td align="left">imap_messages</td>
</tr>
<tr>
<td align="left">labels</td>
</tr>
<tr>
<td align="left">last_spotlight_check_date</td>
</tr>
<tr>
<td align="left">local_message_actions</td>
</tr>
<tr>
<td align="left">mailbox_actions</td>
</tr>
<tr>
<td align="left">mailboxes</td>
</tr>
<tr>
<td align="left">messages</td>
</tr>
<tr>
<td align="left">properties</td>
</tr>
<tr>
<td align="left">recipients</td>
</tr>
<tr>
<td align="left">sqlite_sequence</td>
</tr>
<tr>
<td align="left">subjects</td>
</tr>
<tr>
<td align="left">threads</td>
</tr>
</tbody>
</table>
<p>Hey, this is an impressive list of tables. For this bog post, I am mostly interested in exploring the development of my email activity in terms of senders and receivers over time. Thus, I focus on the relations <code>messages</code>, <code>addresses</code> and <code>recipients</code>.</p>
<pre><code>messages &lt;- dbListFields(con, &quot;messages&quot;)
recipients &lt;- dbListFields(con, &quot;recipients&quot;)
addresses &lt;- dbListFields(con, &quot;addresses&quot;)
max_members &lt;- max(length(messages),
                   length(recipients),
                   length(addresses))
length(messages) &lt;- max_members
length(recipients) &lt;- max_members
length(addresses) &lt;- max_members
df &lt;- data.frame(messages, recipients, addresses, stringsAsFactors = FALSE)
df[is.na(df)] &lt;- &quot;&quot;
kable(df)</code></pre>
<table>
<thead>
<tr>
<th align="left">messages</th>
<th align="left">recipients</th>
<th align="left">addresses</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">ROWID</td>
<td align="left">ROWID</td>
<td align="left">ROWID</td>
</tr>
<tr>
<td align="left">message_id</td>
<td align="left">message_id</td>
<td align="left">address</td>
</tr>
<tr>
<td align="left">document_id</td>
<td align="left">type</td>
<td align="left">comment</td>
</tr>
<tr>
<td align="left">in_reply_to</td>
<td align="left">address_id</td>
<td align="left"></td>
</tr>
<tr>
<td align="left">remote_id</td>
<td align="left">position</td>
<td align="left"></td>
</tr>
<tr>
<td align="left">sender</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">subject_prefix</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">subject</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">date_sent</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">date_received</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">date_created</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">date_last_viewed</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">mailbox</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">remote_mailbox</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">flags</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">read</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">flagged</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">size</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">color</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">type</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">conversation_id</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">snippet</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">fuzzy_ancestor</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">automated_conversation</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">root_status</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">conversation_position</td>
<td align="left"></td>
<td align="left"></td>
</tr>
<tr>
<td align="left">deleted</td>
<td align="left"></td>
<td align="left"></td>
</tr>
</tbody>
</table>
<p>OK. These relations provide enough data to play. Let’s create a table that is organized by message and contains sender and receiver info as well as sending/receiving time.</p>
<pre><code>sql &lt;- paste(&quot;SELECT messages.ROWID as message_id, date_sent,&quot;,
             &quot;date_received, a1.address as sender_address,&quot;,
             &quot;a1.comment as sender_comment, a2.address as recipient_address,&quot;,
             &quot;a2.comment as recipient_comment, snippet&quot;,
             &quot;FROM messages left join addresses AS a1 on messages.sender = a1.ROWID&quot;,
             &quot;LEFT JOIN recipients on recipients.message_id = messages.ROWID&quot;,
             &quot;LEFT JOIN addresses AS a2 on recipients.address_id = a2.ROWID&quot;)
res &lt;- dbSendQuery(con, sql)
df &lt;- dbFetch(res)
dbClearResult(res)
dbDisconnect(con)
df[,c(&quot;date_sent&quot;, &quot;date_received&quot;)] &lt;- lapply(df[,c(&quot;date_sent&quot;, &quot;date_received&quot;)],
                                               function(x) as.POSIXct(x, origin = &quot;1970-01-01&quot;))</code></pre>
<p>The resulting data frame contains all messages, including messages sent to multiple recipients (with me on the sending or receiving end). To limit the messages to the ones where I am involved, I match <code>sender_address</code> and <code>receiver_address</code> to a vector <code>my_adresses</code> containing my email addresses (not disclosed here for privacy reasons).</p>
<pre><code>df %>%
  filter(tolower(sender_address) %in% my_addresses | 
           tolower(recipient_address) %in% my_addresses) %>%
  distinct(.keep_all = TRUE) %>%
  arrange(date_received) -> emails</code></pre>
<p>In addition, I prepare a panel dataset that contains data at email address year level.</p>
<pre><code>emails %>%
  filter(!tolower(sender_address) %in% my_addresses) %>%
  mutate(address = tolower(sender_address),
         year = year(date_received)) %>%

  group_by(year, address) %>%
  summarise(emails_received_from = n()) -> emails_received

emails %>%
  filter(!tolower(recipient_address) %in% my_addresses) %>%
  mutate(address = tolower(recipient_address),
         year = year(date_received)) %>%
  group_by(year, address) %>%
  summarise(emails_sent_to = n()) -> emails_sent

panel &lt;- full_join(emails_received, emails_sent) %>%
  replace_na(list(emails_sent_to = 0, emails_received_from = 0)) %>%
  arrange(year, -emails_sent_to, -emails_received_from)</code></pre>
<p>Time for a first analysis. How does my email in- and outflow develop over the years?</p>
<pre><code>panel %>%
  group_by(year) %>%
  summarise(sent_mails = sum(emails_sent_to),
            received_mails = sum(emails_received_from)) %>%
  gather(key = &quot;var&quot;, value = &quot;count&quot;, -year) %>%
  ggplot(aes(year, count)) +
  geom_bar(aes(fill = var), position = &quot;dodge&quot;, stat=&quot;identity&quot;) + 
  scale_fill_discrete(name=&quot;Direction&quot;,
                      labels=c(&quot;Emails received&quot;, &quot;Emails sent&quot;)) +
  xlab(&quot;Year&quot;) +
  ylab(&quot;Number of emails&quot;) +
  theme_minimal() +
  theme(legend.position=c(.3, .8))</code></pre>
<p><img src="https://i1.wp.com/joachim-gassen.github.io/post/2018-12-27-tidy_up_mac_mail_files/figure-html/emails_by_year-1.png?w=450&ssl=1" data-recalc-dims="1"></p>
<p>Hmm. I have no interest in extrapolating this time trend… Just for fun and giggles: When do I send emails (by time of day)?</p>
<pre><code>emails %>%
  filter(sender_address %in% my_addresses) %>%
  mutate(sent_tod = hour(date_sent)) %>%
  ggplot() +
  geom_histogram(aes(sent_tod), binwidth=1, fill = &quot;#00376C&quot;) +
  xlab(&quot;Time of day [24 hours]&quot;)  +
  ylab(&quot;Number of emails&quot;) +
  theme_minimal()</code></pre>
<p><img src="https://i1.wp.com/joachim-gassen.github.io/post/2018-12-27-tidy_up_mac_mail_files/figure-html/emails_sent_by_tod-1.png?w=450&ssl=1" data-recalc-dims="1"></p>
<p>No real surprises here (at least for me), besides that I seem to take a dip in the early afternoon. Does this sending behavior exhibit any interesting time trends?</p>
<pre><code>emails %>%
  filter(sender_address %in% my_addresses) %>%
  mutate(sent_tod = hour(date_sent) + minute(date_sent)/60 + second(date_sent)/3600,
         year = as.factor(year(date_sent))) %>%
  ggplot(aes(x = sent_tod, y = year, fill = ..x..)) +
  geom_density_ridges_gradient(scale =2, rel_min_height = 0.01)  +
  ylab(&quot;Year&quot;) +
  xlab(&quot;Time of day [24 hours]&quot;) +
  theme_minimal() +
  theme(legend.position = &quot;none&quot;) </code></pre>
<p><img src="https://i0.wp.com/joachim-gassen.github.io/post/2018-12-27-tidy_up_mac_mail_files/figure-html/emails_sent_tod_by_year-1.png?w=450&ssl=1" data-recalc-dims="1"></p>
<p>2002 and 2003 stand out (I was based in the U.S. for most of this period) and it seems as if I am gradually becoming more of an early starter.</p>
<p>But enough on this. This is supposed to be about old acquaintances to fit this special end-of-the-year mood. Let’s dive more into the personal sphere. For this, I define an “email contact” as an email address with an email exchange in a given year, meaning that I was both, at the receiving and sending end, at least once.</p>
<pre><code>panel %>%
  group_by(year) %>%
  filter(emails_sent_to > 0,
         emails_received_from > 0) %>%
  summarise(nr_email_contacts = n()) %>%
  ggplot(aes(year, nr_email_contacts)) +
  geom_bar(stat=&quot;identity&quot;, fill = &quot;#00376C&quot;) +
    xlab(&quot;Year&quot;) +
    ylab(&quot;Head count email contacts&quot;) +
    theme_minimal()</code></pre>
<p><img src="https://i0.wp.com/joachim-gassen.github.io/post/2018-12-27-tidy_up_mac_mail_files/figure-html/email_contacts_by_year-1.png?w=450&ssl=1" data-recalc-dims="1"></p>
<p>I would never have never guessed that I am exchanging emails with that many people (No, I am not a spammer …, I hope). Who are my “top contacts”?</p>
<pre><code>panel %>%
  filter(emails_sent_to > 0,
         emails_received_from > 0) %>%
  group_by(address) %>%
  summarise(email_contacts = sum(emails_sent_to) + sum(emails_received_from),
            sent_rec_ratio = sum(emails_sent_to) / sum(emails_received_from),
            emails_sent_to = sum(emails_sent_to),
            emails_received_from = sum(emails_received_from)) %>%
  arrange(-email_contacts) -> email_contacts

# head(email_contacts, 50)</code></pre>
<p>I am not including this output here for obvious privacy reasons. If you are interested to follow your email contacts over time, you can use my ExPanD shiny app for quick exploration.</p>
<pre><code>panel %>%
  filter(emails_sent_to > 0,
         emails_received_from > 0) %>%
  ExPanD(cs_id = &quot;address&quot;, ts_id = &quot;year&quot;, components = c(missing_values = FALSE, 
                                                           by_group_violin_graph = FALSE, 
                                                           by_group_bar_graph = FALSE))</code></pre>
<p>Using ExPanD, I encountered a reasonable amount of my “old acquaintances” when focusing on earlier years of the panel. To do this a little bit more systematically, I prepare a linking table, linking the most prominent email contact addresses in the panel file to real people names in an n(email addresses) to 1(person) look-up table. This table has the name <code>email_contacts_names</code> and the fields <code>address</code> and <code>name</code>. Based on this data I produce a new panel sample containing only those email contacts linked to actual persons that I care about.</p>
<pre><code>panel %>%
  left_join(email_contacts_names) %>%
  filter(!is.na(name)) %>%
  group_by(year, name) %>%
  summarise(emails_sent_to = sum(emails_sent_to),
            emails_received_from = sum(emails_received_from)) %>%
  filter(emails_sent_to > 0,
         emails_received_from > 0) %>%
  mutate(email_contacts = emails_sent_to + emails_received_from,
         sent_rec_ratio = emails_sent_to / emails_received_from) %>%
  arrange(year, -email_contacts) -> panel_email_contacts</code></pre>
<p>Using this data, you can now easily explore how your contacts changed over time. As one example I prepared a graph highlighting the (relative) number of email contacts with a selected group of people over time. The vector <code>people</code> contains the names of these people.</p>
<pre><code>single_out_people &lt;- function(df, names, relative = FALSE) {
  df$relative &lt;-  relative
  palette &lt;-  function(n) {
    hues = seq(15, 375, length = n)
    c(&quot;#B0B0B0&quot;, hcl(h = hues, l = 65, c = 100)[1:n])
  }
  df %>%
    mutate(name = ifelse(name %in% names, name, &quot;Other&quot;)) %>%
    group_by(year) %>%
    mutate(val = ifelse(relative,
                        email_contacts/sum(email_contacts),
                        email_contacts)) %>%
    select(year, name, val) %>%
    group_by(year, name) %>%
    summarise(val = sum(val)) %>%
    spread(key = name, value = val, fill = 0) %>%
    gather(key = name, value = val, -year) %>%
    mutate(name = factor(name, levels = c(&quot;Other&quot;, rev(names)))) %>%
    arrange(year, name) %>%
    ggplot(aes(x = year, y = val, fill = name)) +
    geom_area() +
    scale_fill_manual(values = palette(length(names))) +
    xlab(&quot;Year&quot;) +
    ylab(ifelse(relative,
           &quot;Share of annual email contacts&quot;,
           &quot;Annual email contacts&quot;)) +
    theme_minimal() -> p
  if (relative) p + scale_y_continuous(labels = scales::percent) else p 
}

single_out_people(panel_email_contacts, people, FALSE) + theme(legend.position=&quot;none&quot;)</code></pre>
<p><img src="https://i2.wp.com/joachim-gassen.github.io/post/2018-12-27-tidy_up_mac_mail_files/figure-html/single_out_people-1.png?w=450&ssl=1" data-recalc-dims="1"></p>
<pre><code>single_out_people(panel_email_contacts, people, TRUE) + theme(legend.position=&quot;none&quot;)</code></pre>
<p><img src="https://i2.wp.com/joachim-gassen.github.io/post/2018-12-27-tidy_up_mac_mail_files/figure-html/single_out_people-2.png?w=450&ssl=1" data-recalc-dims="1"></p>
<p>The grayish areas marks “Other”. With legends excluded for privacy reasons, you can follow old and new friends through time with this little display.</p>
<p>While the Envelope Index database seems to offer much more data that is worth exploring, I will it leave it here. Happy New Year everybody and if you are in the mood: <a rel="nofollow" href="https://upload.wikimedia.org/wikipedia/commons/e/e3/Auld_Lang_Syne.ogg" target="_blank">Auld Lang Syne</a>.</p>
<p>Enjoy!</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://joachim-gassen.github.io/2018/12/should-old-acquaintance-be-forgot-tidying-up-mac-mail/"> An Accounting and Data Science Nerd's Corner</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/YrfQ9CF47yQ" height="1"></a></div>