Cleaning up tables

Context

One of things I have to do quite often is create tables for papers and presentations. Often the “Table 1” of a paper has descriptives about the study, broken down by subgroups. For presentation purposes, it doesn’t look good (to me, at least) that the name of each subgroup be repeated down one column of the table.

One way to deal with this is, of course, by hand. Save the table as a CSV or Excel file, open it up in your favorite spreadsheet program, and prettify things. But, of course, this being a R blog, I wanted to create a function that would fix this. I’ve created hack-y functions for this before, but a neat trick pointed out here gave me an idea for a more elegant solution. It also meant I had to use the tidyeval paradigm a bit, which I figured I should at least become familiar with.

Here’s what I want to do

Take a table like this:

to something like this:

The point is that the first column has repeating values, and I just want the first row of the cluster of rows corresponding to Rural and Urban to have text, the rest being blank. I find this a cleaner look and more typical of tables I see in papers.

This is purely for presentation purposes.I would never do this for data frames I’ll still analyze, since the blank cells screw up things. Of course this could be fixed easily using last value carried forward imputation on the column.

A solution

I created this simple function to do this for a single column within a magrittr pipeline:

clean_col <- function(x, colvar){
  require(dplyr)
  colv <- enquo(colvar)
  x %>% 
    group_by(!!colv) %>% 
      mutate(rown = row_number()) %>% 
    ungroup() %>% 
    mutate_at(vars(!!colv), funs(ifelse(rown > 1, '', .))) %>% 
    select (-rown)

The first thing to note here is that I’m using quosures and quasiquotation to allow the pipeline to work with the function’s inputs, specifically the column name, which is provided as an unquoted name. Admittedly this was done without much understanding, following examples on Edwin Thoen’s excellent blog.

The second thing was the use of the dummy rown column to identify the first row of each cluster of rows defined by the variable colvar. This was inspired by this blog I read through R-Bloggers yesterday. This trick allowed me to easily “blank out” the appropriate cells in the colvar column.

Desirable updates

There are two directions I want to take this, but I don’t understand tidyeval or functions with variable numbers of arguments well enough yet to do it. The simpler extension is to do the same process using two or more columns, rather than one column. For example, taking

to

The second extension would be to create truly nested row labels, like this:

I can create these on a case-by-case basis, but I’m not sure how to do this in a function, yet. Looking forward to comments.

clean_col <- function(x, ...){
  require(dplyr)
  colvs <- quos(...)
  x %>% group_by(!!!colvs) %>% mutate(rown = row_number()) %>% ungroup() %>% 
    mutate_at(vars(!!!colvs), funs(ifelse(rown > 1, '', .))) %>% 
    select (-rown)
}

clean_cols <- function(x, ...){
  colvs = quos(...)
  x <- x %>% 
    mutate_at(vars(!!!colv),as.character) %>% # make sure columns are characters
    arrange(!!!colv) # Ensure right order of columns
  for(i in 1:length(colvs)){
    rowvar <-  rlang::sym(paste0('rown',i)) # Create dummy
    x <- x %>% group_by(!!!colvs[1:i]) %>% mutate(!!rowvar := row_number()) %>% 
      ungroup()
  }
  for(i in 1:length(colvs)){
    rowvar <- rlang::sym(paste0('rown',i))
    x <- x %>% mutate_at(vars(!!colvs[[i]]), funs(ifelse(!!rowvar > 1, '', .)))
  }
  x <- x %>% select(-starts_with('rown')) # remove dummies
  return(x)
}

clean_cols <- function(x, ...){
  colv = quos(...)
  x <- x %>% 
    mutate_at(vars(!!!colv),as.character) %>% # make sure columns are characters
    arrange(!!!colv) # Ensure right order of columns
  for(ii in rev(seq_along(colv))){
    indx = duplicated(select(x, !!!colv[ii:1]))
    x <- x %>% mutate_at(vars(!!colv[[ii]]), funs(ifelse(indx, '', .)))
  }
  return(x)
}