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.
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 Envelope Index that tends to be located in ~/Library/Mail/V6/MailData/. Copy this file somewhere and adjust the path provided in mail_db to point to this copy. Do not work with the original file.
library(DBI)
library(tidyverse)
library(lubridate)
library(ExPanDaR)
library(ggridges)
mail_db %
arrange(year, -emails_sent_to, -emails_received_from)
Time for a first analysis. How does my email in- and outflow develop over the years?
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))
Hmm. I have no interest in extrapolating this time trend… Just for fun and giggles: When do I send emails (by time of day)?
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()
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?
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;) 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.
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.
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()
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”?
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)
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.
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))
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 email_contacts_names and the fields address and name. Based on this data I produce a new panel sample containing only those email contacts linked to actual persons that I care about.
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
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 people contains the names of these people.
single_out_people %
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;)
single_out_people(panel_email_contacts, people, TRUE) + theme(legend.position=&quot;none&quot;)
The grayish areas marks “Other”. With legends excluded for privacy reasons, you can follow old and new friends through time with this little display.
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: Auld Lang Syne.
Enjoy!