MySQL -> R

Here is how you pull SQL data into R.

	require( sqlutils )
	require( RMySQL )
	library( Cairo )
	library( yaml )
	my.cnf = yaml.load_file( '~/.my.yaml' )
	default = my.cnf$clients$default
	p_cols <- c( "blue" , "red" , "orange" , "green" )
	con <- dbConnect(
	MySQL(),
	user=default$user ,
	password=default$password,
	dbname=default$database,
	host=default$host
	)
	rs <- dbSendQuery(
	con ,
	'
	SELECT TIME_FORMAT( time , "%l %p" ) time
	, AVG( temp_f ) temperature
	FROM weather
	WHERE HOUR( TIMEDIFF( SYSDATE() , TIME ) ) < 24
	GROUP BY HOUR( time )
	ORDER BY HOUR( TIMEDIFF( SYSDATE() , TIME ) )
	DESC;
	'
	)
	fields = dbColumnInfo(rs)
	data_frame = fetch(rs, n = 24 )
	time = data_frame[[ "time" ]]
	temp = data_frame[[ "temperature" ]]
	CairoPNG(
	filename="/path/to/where/i/write/test.png" ,
	width = 500 ,
	height = 500 ,
	pointsize = 9
	)
	plot ( temp
	, main="Temperatures for the last 24 Hours"
	, sub="West Lafayette, IN - Temperatures in Fahrenheit"
	, col=p_cols[2]
	, type="l"
	, xlab="Time"
	, ylab="Temperature(F)"
	, xaxt='n'
	)
	axis( 1 , las=2 , at=1:24 , lab=time )
	box()

view raw gistfile1.r hosted with ❤ by GitHub

Cool, isn't it?

Except there's a password in plain text. Which means I have to modify it when I want to show it off.

I'll have to work on that.

This is how I'm stupid

I wrote a tool about four months ago, which is long enough that I barely remembered writing it, much less what I had done in it. It connects the data coming out of one of our instruments with the metadata about the samples we fed into it. Part 1 of this is to create a comment field which is easily pushed onto the instrument's data output. The newest part takes the data images from this and combines them with the sample metadata to be published into our electronic lab notebook.

One of the cool bits about this is that, if you are creating plate records that can take 11 samples, but you have 38 samples, it will create four plate records; three with all 11 samples filled and one with just five samples.  

This is where we get into problems. Those four plate records are logically joined, with the thought that you putting them together means they go together. So, the samples are numbered between 1 and 38, in this case. Problem is, samples coming off the instrument are sample 1 through sample 11. So, sample 12 and on just fall on the floor.

I am also stupid because I put the fact that this plate can hold 11 samples into the comment field block but not independently into the database table. 

For four months, nobody who grouped several plates together like this used the tool to publish them to the lab notebook, so nobody found how I was stupid. 

Yes, I am saying I was stupid. Stupid is as stupid does, as Forrest Gump's mama said, and this is two stupid things I've done. I'll throw in "You didn't test it enough" as a third.

It isn't helpful to just say "You're stupid". It also isn't useful to say "You're stupid for using (Tool A which I don't like) instead of (Tool B which I do like)" because people do stupid things in the best of languages and do smart things in the worst. I believe it is helpful to identify the places where you are stupid, like I do above, because those are actionable. I can now add a column to the table to store the number of samples on a plate, which I might do. Right now, I'm using a regular expression to pull the number of samples out of the comment, then using modulus to find the adjusted sample number, and then saying sample number = number of samples if sample number == zero. Yeah, I'll have to add a "sample number on plate" column into the sample database table, too.

I'm glad to know these things. I'm glad they're now patched, and I plan to fix these things better. Mostly, I'm glad to know where I'm stupid because now I know how to be smart next time.