#rbloggers

[alwaysobsess]

Wishing all you beauties a Happy New Year!🍾 #newyearseve #bubbles #popopenthebubbly #celebrate #rejoice #together #drinkies #femaleblogger #networking #blogrequest #blogr #rblog #rbloggers #champagne #ukblogger #alwaysobsess #obsessed

[invadingmars]

I'm already nekkid 🤷🏽‍♀️ #question #foodie #qotd #rbloggers #relationshipblogger

[redtrades]

Lol. #saturday Can you spot the CBD local cluster?!🤓😜#gitSum #github #SocialNetworkAnalysis #DCTech #dataviz #DataVizDC #python #datascience @datascihub #jupyter #numpy #SciPy #rstats #rbloggers #SageMath (at Manassas Park, Virginia)

[appliedfunctions]

Using dplyr to back up a MySQL database

This summer I started developing a MySQL database for our lab. It’s my first experience working with MySQL, phpMyAdmin, MS Access, and dplyr’s remote data capabilities. Because I don’t know SQL at all but am a ninja at dplyr, I’ve been developing a helper R package to automate some tasks with my lab’s database. I’d like to share how I handle backing up the database into a portable set of minimally documented csvs.

(Caveat: I’m sure there are more idiomatic or efficient ways of performing these tasks by working with SQL directly but I’m going to stubbornly play to my strengths.)

Back up each table

I’d like to be able to back up the whole database with a single function call. Backing up the individual tables is easy. Here’s my function to download a table and write it to a csv.

# Download a tbl from a db connection and write to a csv backup_tbl <- function(tbl_name, src, output_dir) { # Try to download the tbl, defaulting to an empty data-frame try_tbl <- failwith(data_frame(), tbl) df <- collect(try_tbl(src, tbl_name)) output_file <- file.path(output_dir, paste0(tbl_name, ".csv")) message("Writing ", output_file) readr::write_csv(df, output_file) df }

Now, in another function, I just have to apply backup_tbl to each table in the database.

# `this_backup_dir` is a timestamped directory created a few lines earlier tbls <- src_tbls(src) dfs <- lapply(tbls, backup_tbl, src = src, output_dir = this_backup_dir) names(dfs) <- tbls # `dfs` returned later to provide a copy of the dl'd data to the R session

This code is a really good start, but what good is a bunch of csvs without documentation?

Back up the metadata as well

One feature I appreciate in MySQL are the optional table and field comments which allow me to write a brief description of each table and each column in a table. The screenshot below from phpMyAdmin shows fully commented fields for a table of scores from a vocabulary test.

By downloading these descriptions and bundling them with backed up tables, I can generate a minimal codebook to accompany the csvs. So I create a function called describe_tbl that centers around the two following lines:

# Get the table description this_query <- sprintf("SHOW FULL COLUMNS FROM %s", tbl_name) info <- DBI::dbGetQuery(src, statement = this_query)

Which allows me to grab the metadata depicted in the earlier screenshot:

describe_tbl(my_db, "PPVT") #> Table Field Index DataType DefaultValue NullAllowed #> 1 PPVT ChildStudyID UNI int(11) <NA> NO #> 2 PPVT PPVTID PRI int(11) <NA> NO #> 3 PPVT PPVT_Timestamp datetime CURRENT_TIMESTAMP NO #> 4 PPVT PPVT_Form enum('A','B') <NA> YES #> 5 PPVT PPVT_Completion date <NA> YES #> 6 PPVT PPVT_Raw int(11) <NA> YES #> 7 PPVT PPVT_Standard int(11) <NA> YES #> 8 PPVT PPVT_GSV int(11) <NA> YES #> 9 PPVT PPVT_Age int(3) <NA> YES #> 10 PPVT PPVT_Note varchar(255) <NA> YES #> Description #> 1 Child-Study ID (uniquely defines a Child-Study pairing) #> 2 PPVT Administration ID #> 3 When each record (row) was last edited #> 4 PPVT test form used. A, B or NULL (if unknown) #> 5 Date PPVT was completed #> 6 Raw score (number of words) #> 7 Standard score #> 8 Growth scale value #> 9 Age in months (rounded down) when PPVT was completed #> 10 Notes on test administration

Terrific. Now the final ingredient is to get the comments attached to each table. As above, I create a function called describe_db to wrap a single query:

# Get the table description info <- DBI::dbGetQuery(src, statement = "SHOW TABLE STATUS")

This query grabs lots of backend information about each table in the database (DB engine, collation, average row length, etc.), but for my codebook, I keep just the number of rows and comment columns from each table status. Here’s what the function returns:

# Look at just the documented, in-use tables describe_db(my_db) %>% filter(Description != "", Rows != 0) #> Database Table Rows #> 1 l2t BRIEF 224 #> 2 l2t Child 224 #> 3 l2t EVT 224 #> 4 l2t LENA_Admin 182 #> 5 l2t LENA_Hours 2968 #> 6 l2t MinPair_Admin 190 #> 7 l2t MinPair_Responses 7674 #> 8 l2t PPVT 224 #> Description #> 1 Scores from Behvr Rating Inventory of Exec Func (Preschool) #> 2 Unique IDs and demographics of children in database #> 3 Scores on Expressive Vocabulary Test 2 #> 4 LENA recordings #> 5 Stats from LENA recordings by hour-of-day #> 6 Administrations of the Minimal Pairs experiment #> 7 Trials and responses from the Minimal Pairs experiment #> 8 Scores on Peabody Picture Vocabulary Test 4

Finally, I assemble these three bits of functionality (back up each table, download field comments from each table, and download table status) together into a function called l2t_backup. This function writes all of these bits of information to a timestamped directory. Note that the final two messages refer to the metadata csvs.

l2t_backup(my_db, "inst/backup") #> Writing inst/backup/2015-08-19_09-51/BRIEF.csv #> Writing inst/backup/2015-08-19_09-51/Caregivers.csv #> Writing inst/backup/2015-08-19_09-51/Child.csv #> Writing inst/backup/2015-08-19_09-51/ChildStudy.csv #> Writing inst/backup/2015-08-19_09-51/EVT.csv #> Writing inst/backup/2015-08-19_09-51/FruitStroop.csv #> Writing inst/backup/2015-08-19_09-51/LENA_Admin.csv #> Writing inst/backup/2015-08-19_09-51/LENA_Hours.csv #> Writing inst/backup/2015-08-19_09-51/Literacy.csv #> Writing inst/backup/2015-08-19_09-51/MinPair_Admin.csv #> Writing inst/backup/2015-08-19_09-51/MinPair_Responses.csv #> Writing inst/backup/2015-08-19_09-51/PPVT.csv #> Writing inst/backup/2015-08-19_09-51/SES.csv #> Writing inst/backup/2015-08-19_09-51/Scores_TimePoint1.csv #> Writing inst/backup/2015-08-19_09-51/Study.csv #> Writing inst/backup/2015-08-19_09-51/StudyTask.csv #> Writing inst/backup/2015-08-19_09-51/VerbalFluency.csv #> Writing inst/backup/2015-08-19_09-51/metadata/field_descriptions.csv #> Writing inst/backup/2015-08-19_09-51/metadata/table_descriptions.csv

[opexxx]

From Twitter: Data anonymization in R http://bit.ly/1zLZFSJ #rstats— R-bloggers (@Rbloggers) December 21, 2014

↬@Rbloggers

[opexxx]

From Twitter: Hassle-free data from HTML tables with the htmltable package http://bit.ly/1sY46dV #rstats— R-bloggers (@Rbloggers) December 21, 2014

↬@Rbloggers

[opexxx]

From Twitter: How to conduct a tombola with R http://bit.ly/1zLZCGD #rstats— R-bloggers (@Rbloggers) December 21, 2014

↬@Rbloggers

[opexxx]

From Twitter: Using R: barplot with ggplot2: (This article was first published on There is grandeur in this vie... http://bit.ly/1d1mTwt #rstats— R bloggers website (@Rbloggers) March 19, 2014

↬@Rbloggers