I’ve been using CartoDB quite frequently in my teaching recently, as it is a great way to make both simple and sophisticated web maps. CartoDB is powered by PostgreSQL/PostGIS, which means that you can do fairly advanced data manipulation in it using SQL; however, I prefer to do my data wrangling in R so I can take advantage of the Hadleyverse packages.

With this workflow, I would export my data out of R, then drag and drop using the CartoDB web GUI to upload to my CartoDB account. However, as is common in data visualization, I would frequently observe something on my map that I would want to change, so I would have to go back to R and repeat the process.

As such, I wanted to streamline all of this and upload directly to CartoDB from R. Fortunately, CartoDB has an import API that uses curl to import data. I wanted to leverage this in my own work in R. While there appears to be an existing R interface to CartoDB, it hasn’t been updated in a couple years, and does not appear to support Spatial* data frames.

This led me to develop my own function, which I am calling r2cartodb. This function, with one line of R, will upload a spatial or non-spatial data frame to your CartoDB account. The function has four parameters:

  1. obj: An R object, of class data.frame or Spatial* data frame. If the data frame has detectable XY coordinates, CartoDB will convert it into a spatial table.
  2. layer_name: The name you want to give your layer/table, which will be the name of the table in your CartoDB account.
  3. account_id: Your CartoDB account ID.
  4. api_key: Your CartoDB API key, which you can get when logged in to your account.

The function depends on the rgdal and httr packages, and requires that you have Rtools installed if you want to upload Spatial* data frames (and that Rtools is in your PATH if you are using Windows). As it writes a file and then uploads it, you’ll need to first set your working directory. Also, the function is available from my very new personal R package, which can be installed with devtools::install_github('walkerke/kwgeo') if you’d like.

Here is the function code:

### r2cartodb: a function to import data to your CartoDB account.  

# Set your working directory before calling the function.  
# Also, the function depends on the rgdal and httr packages, and requires that you have Rtools installed 
# (and that you have Rtools in your PATH if you are on Windows) if you want to upload spatial
# data frames.  

library(rgdal)
library(httr)

# Advisable to make the layer name different from your imported data, if applicable.

r2cartodb <- function(obj, layer_name, account_id, api_key) {   
  
  dir <- getwd()
  
  cartodb_url <- paste0("https://", 
                        account_id, 
                        ".cartodb.com/api/v1/imports/?api_key=", 
                        api_key)
  
  if (class(obj) == "data.frame") { 
    
    # Will import a basic table or a spatial table if it can detect long/lat columns.
    # The function first writes a CSV then uploads it.  
    
    csv_name <- paste0(layer_name, ".csv")
    
    write.csv(obj, csv_name)
    
    POST(cartodb_url, 
         encode = "multipart", 
         body = list(file = upload_file(csv_name)), 
         verbose())  
    
  } else if (grep("Spatial", class(obj)) == 1) {  
    
    # Assuming here that you're using a Spatial*DataFrame, which can be written to a shapefile.  The
    # function will first write to a shapefile and then upload.  
    
    writeOGR(obj = obj, 
             dsn = dir, 
             layer = layer_name, 
             driver = "ESRI Shapefile", 
             overwrite_layer = TRUE)
    
    pattern = paste0(layer_name, "\\.*")
    
    files <- list.files(pattern = pattern)
    
    zip_name <- paste0(layer_name, ".zip")
    
    zip(zipfile = zip_name, files = files)
    
    POST(cartodb_url, 
         encode = "multipart", 
         body = list(file = upload_file(zip_name)), 
         verbose())  
    
  } else {
    
      stop("The r2cartodb function requires a data frame or spatial data frame")
    
    }
}

Now, let’s test it out! I’m going to use some data I recently used for a lab assignment for my students, in which they used QGIS to map Starbucks in Chicago.

library(DT)

my_account_id <- "my_account_id"  # Input your ID here

my_api_key <- "my_api_key"  # Input your api key here

df <- read.csv('chicago_starbucks.csv')

datatable(df, class = 'cell-border stripe', options = list(pageLength = 5) )

As I have long/lat coordinates, CartoDB will create a spatial table. I’ll call the table sbchi in this example. I now call the r2cartodb function:

r2cartodb(df, "sbchi", my_account_id, my_api_key)

Once I log in to CartoDB, my file is there, formatted as a CartoDB table with geometry defined in the the_geom column.

image_here

Now, I can visualize my data with one of CartoDB’s many visualization options, such as a hexbin map:

This has made my R to CartoDB workflow much easier. Let me know if you find this useful!