R Code tips and tricks
R ODBC/SQL access
Connecting
require(RODBC)
conn -odbcConnect(
"ahs0", # System DSN name
uid= winDialogString("Database Username:",""), # Username
pwd=.rs.askForPassword("Please enter your database password")) #Password
NOTE: The ODBC system or user DSN must be set up on the machine for this to work.
Querying
# If stringsAsFactors=F is not specified, then strings will be converted to factors.
result - sqlQuery(conn, "select * from mydatabase.dbo.mytable", stringsAsFactors=F);
To search a particular column across tables:
tabs - sqlQuery(conn, "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM clinics.information_schema.columns
WHERE column_name LIKE '%whatever%'")
apply(tabs, 1, function(row){
sqlQuery(conn, paste0("select ", row[4]," from ", paste0(row[1:3], collapse="."), " where ", row[4], " = _some_value_"))
})
R File Processing
Zip files
R can process zip files directly. Example code for loading a specific file within a zip file:
mydata = read.table(file = unz("mydata.zip", "mydata.txt"))
Additionally, if a single file is compressed, R can usually detect this transparently to the user:
mydata = read.table(file = "mydata.csv.bzip2")
R Dates
This will return today's date as a string in the format YYYY-MM-DD:
> format(Sys.time(), "%Y-%m-%d")
[1] "2015-10-20"
R Studio
Vim tricks
Search and Replace w/ group matching references
If vim editor mode is enabled, you can wrap portions of a search string in parentheses and refer to them using \$1, \$2, $3,...
Example:
:s/^(\w+)/conf['$1']/g
replaces
first second third
with
conf['first'] second third
Compiling w/ Rtools
Often R cannot find the Rcpp/Rtools files. This can generally be remedied by running devtools:find_rtools().
Shiny
shinyFiles
Common Packages
install.packages(c("dplyr","magrittr")) # Basic
install.packages(c("shiny", "DT")) # Apps - TODO: Add shinyfiles?
install.packages(c("testthat", "assertthat", "covr")) # Devel