Coffee and Code and Calendars and R

I have what you might call a conflicted relationship with caffeine.



Long story short: I found it necessary to cut down on caffeine, limiting myself to two cups a day, preferrably before noon, and, as sort of a measure of accountability, and as a way to gain more skill with SQL and R, I wrote tools that store when I drink coffee and, at the end of the work day, tweeting the image.

I forget exactly where I found the calendar heatmap code, but it was several years ago, and was one of the first instances of ggplot2 that I found and put into service. I chose a brown-to-black color scheme because, well, obviously it needed to look like coffee.

This image, with "Dave has had n cups of coffee today" is autotweeted every day at 6pm Eastern. Recently, it has drawn interest.

@hoelzro It's very cargo-cult on my end. I should blog or gist it.

Thank @hadleywickham , I just put data into it.

— 'Dev' in Creole (@JacobyDave) May 6, 2017

So here it is, both blogged and gisted.

	#!/usr/bin/Rscript
	# Cairo - for creating images outside of X
	# ggplot2 - the MAGIC
	# RMySQL - for interacting with the MySQL database
	# yaml - for storing database credentials
	suppressPackageStartupMessages( require( "Cairo" , quietly=TRUE ) )
	suppressPackageStartupMessages( require( "ggplot2" , quietly=TRUE ) )
	suppressPackageStartupMessages( require( "RMySQL" , quietly=TRUE ) )
	suppressPackageStartupMessages( require( "yaml" , quietly=TRUE ) )
	# administrivia
	my.cnf = yaml.load_file( '~/.my.yaml' )
	database = my.cnf$clients$itap
	quote <- "'"
	newline <- "\n"
	# the query
	coffee_sql <- "
	SELECT
	YEAR(d.datestamp) year ,
	IF(
	WEEKDAY(d.datestamp) > 5 ,
	1 ,
	2 + WEEKDAY(d.datestamp)
	) wday ,
	WEEK(d.datestamp) week ,
	COUNT(c.cups) cups ,
	DATE(d.datestamp) date
	FROM day_list d
	LEFT OUTER JOIN coffee_intake c
	ON DATE(c.datestamp) = DATE(d.datestamp)
	WHERE DATE(d.datestamp) > '2012-11-01'
	AND DATE(d.datestamp) <= DATE(NOW())
	GROUP BY DATE(d.datestamp)
	ORDER BY DATE(d.datestamp)
	"
	con <- dbConnect(
	MySQL(),
	user=database$user ,
	password=database$password,
	dbname=database$database,
	host=database$host
	)
	coffee.data <- dbGetQuery( con , coffee_sql )
	CairoPNG(
	filename = "/home/jacoby/www/coffee.png" ,
	height = 800 ,
	width = 800 ,
	pointsize = 12
	)
	ggplot(
	coffee.data,
	aes( week, wday, fill = cups ) ) +
	geom_tile(colour = "#191412") +
	ggtitle( "Dave Jacoby's Coffee Intake" ) +
	xlab( 'Week of the Year' ) +
	ylab( 'Day of the Week' ) +
	scale_fill_gradientn(
	colours = c(
	"#d1c3be",
	"#a78b83",
	"#4b3a35"
	)
	) +
	facet_wrap(~ year, ncol = 1)

view raw coffee.R hosted with ❤ by GitHub

I started doing that thing with YAML in Perl, to keep database keys out of programs. I'm reasonably okay with my SQL skills, I think, but I am clear that my R code is largely cargo-cult. It'd be good to replace 2,4,6 with the days of the week,  and I am reasonably sure that it's reversed from the way you'd normally expect weekdays to go. Some day, I'll have the knowledge required to make those changes.

If you have questions and comments about this, I'd be glad to take them on, but I'm very much the learner when it comes to R.