<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/cleaning-up-tables/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://statbandit.wordpress.com/2018/05/16/cleaning-up-tables/"> R – Stat Bandit</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<p>This post is re-published from my <a rel="nofollow" href="https://webbedfeet.netlify.com/post/cleaning-up-tables/" target="_blank">blog</a></p>
<hr>
<h2>Context</h2>
<p>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.</p>
<p>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 <a rel="nofollow" href="https://github.com/tidyverse/tidyr/issues/296" target="_blank">neat trick</a> pointed out <a rel="nofollow" href="https://www.daeconomist.com/post/2018-05-15-spread/" target="_blank">here</a> gave me an idea for a more elegant solution. It also meant I had to use the <a rel="nofollow" href="https://dplyr.tidyverse.org/articles/programming.html" target="_blank">tidyeval</a> paradigm a bit, which I figured I should at least become familiar with.</p>
</div>
<div>
<h2>Here’s what I want to do</h2>
<p>Take a table like this:</p>
<table>
<thead>
<tr>
<th align="left">Location</th>
<th align="left">Gender</th>
<th align="right">values</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">Rural</td>
<td align="left">Male</td>
<td align="right">32.74</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Female</td>
<td align="right">25.18</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Male</td>
<td align="right">40.48</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Female</td>
<td align="right">25.28</td>
</tr>
</tbody>
</table>
<p>to something like this:</p>
<table>
<thead>
<tr>
<th align="left">Location</th>
<th align="left">Gender</th>
<th align="right">values</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">Rural</td>
<td align="left">Male</td>
<td align="right">32.74</td>
</tr>
<tr>
<td align="left"></td>
<td align="left">Female</td>
<td align="right">25.18</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Male</td>
<td align="right">40.48</td>
</tr>
<tr>
<td align="left"></td>
<td align="left">Female</td>
<td align="right">25.28</td>
</tr>
</tbody>
</table>
<p>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.</p>
<blockquote>
<p>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.</p>
</blockquote>
</div>
<div>
<h2>A solution</h2>
<p>I created this simple function to do this for a single column within a <code>magrittr</code> pipeline:</p>
<pre>
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)
}
</pre>
<p>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 <a rel="nofollow" href="https://edwinth.github.io/blog/dplyr-recipes/" target="_blank">excellent blog</a>.</p>
<p>The second thing was the use of the dummy <code>rown</code> column to identify the first row of each cluster of rows defined by the variable <code>colvar</code>. This was inspired by <a rel="nofollow" href="https://www.daeconomist.com/post/2018-05-15-spread/" target="_blank">this blog</a> I read through <a rel="nofollow" href="http://www.r-bloggers.com/" target="_blank">R-Bloggers</a> yesterday. This trick allowed me to easily “blank out” the appropriate cells in the <code>colvar</code> column.</p>
</div>
<div>
<h2>Desirable updates</h2>
<p>There are two directions I want to take this, but I don’t understand <code>tidyeval</code> 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</p>
<table>
<thead>
<tr>
<th align="left">Location</th>
<th align="left">Gender</th>
<th align="left">AgeGrp</th>
<th align="right">DeathRate</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">Rural</td>
<td align="left">Male</td>
<td align="left">50-54</td>
<td align="right">11.7</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Male</td>
<td align="left">55-59</td>
<td align="right">18.1</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Male</td>
<td align="left">60-64</td>
<td align="right">26.9</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Male</td>
<td align="left">65-69</td>
<td align="right">41.0</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Male</td>
<td align="left">70-74</td>
<td align="right">66.0</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Female</td>
<td align="left">50-54</td>
<td align="right">8.7</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Female</td>
<td align="left">55-59</td>
<td align="right">11.7</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Female</td>
<td align="left">60-64</td>
<td align="right">20.3</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Female</td>
<td align="left">65-69</td>
<td align="right">30.9</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Female</td>
<td align="left">70-74</td>
<td align="right">54.3</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Male</td>
<td align="left">50-54</td>
<td align="right">15.4</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Male</td>
<td align="left">55-59</td>
<td align="right">24.3</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Male</td>
<td align="left">60-64</td>
<td align="right">37.0</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Male</td>
<td align="left">65-69</td>
<td align="right">54.6</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Male</td>
<td align="left">70-74</td>
<td align="right">71.1</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Female</td>
<td align="left">50-54</td>
<td align="right">8.4</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Female</td>
<td align="left">55-59</td>
<td align="right">13.6</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Female</td>
<td align="left">60-64</td>
<td align="right">19.3</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Female</td>
<td align="left">65-69</td>
<td align="right">35.1</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Female</td>
<td align="left">70-74</td>
<td align="right">50.0</td>
</tr>
</tbody>
</table>
<p>to</p>
<table>
<thead>
<tr>
<th align="left">Location</th>
<th align="left">Gender</th>
<th align="left">AgeGrp</th>
<th align="right">DeathRate</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">Rural</td>
<td align="left">Male</td>
<td align="left">50-54</td>
<td align="right">11.7</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">55-59</td>
<td align="right">18.1</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">60-64</td>
<td align="right">26.9</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">65-69</td>
<td align="right">41.0</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">70-74</td>
<td align="right">66.0</td>
</tr>
<tr>
<td align="left">Rural</td>
<td align="left">Female</td>
<td align="left">50-54</td>
<td align="right">8.7</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">55-59</td>
<td align="right">11.7</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">60-64</td>
<td align="right">20.3</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">65-69</td>
<td align="right">30.9</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">70-74</td>
<td align="right">54.3</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Male</td>
<td align="left">50-54</td>
<td align="right">15.4</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">55-59</td>
<td align="right">24.3</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">60-64</td>
<td align="right">37.0</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">65-69</td>
<td align="right">54.6</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">70-74</td>
<td align="right">71.1</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Female</td>
<td align="left">50-54</td>
<td align="right">8.4</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">55-59</td>
<td align="right">13.6</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">60-64</td>
<td align="right">19.3</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">65-69</td>
<td align="right">35.1</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">70-74</td>
<td align="right">50.0</td>
</tr>
</tbody>
</table>
<p>The second extension would be to create truly nested row labels, like this:</p>
<table>
<thead>
<tr>
<th align="left">Location</th>
<th align="left">Gender</th>
<th align="left">AgeGrp</th>
<th align="right">DeathRate</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">Rural</td>
<td align="left">Male</td>
<td align="left">50-54</td>
<td align="right">11.7</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">55-59</td>
<td align="right">18.1</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">60-64</td>
<td align="right">26.9</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">65-69</td>
<td align="right">41.0</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">70-74</td>
<td align="right">66.0</td>
</tr>
<tr>
<td align="left"></td>
<td align="left">Female</td>
<td align="left">50-54</td>
<td align="right">8.7</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">55-59</td>
<td align="right">11.7</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">60-64</td>
<td align="right">20.3</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">65-69</td>
<td align="right">30.9</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">70-74</td>
<td align="right">54.3</td>
</tr>
<tr>
<td align="left">Urban</td>
<td align="left">Male</td>
<td align="left">50-54</td>
<td align="right">15.4</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">55-59</td>
<td align="right">24.3</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">60-64</td>
<td align="right">37.0</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">65-69</td>
<td align="right">54.6</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">70-74</td>
<td align="right">71.1</td>
</tr>
<tr>
<td align="left"></td>
<td align="left">Female</td>
<td align="left">50-54</td>
<td align="right">8.4</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">55-59</td>
<td align="right">13.6</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">60-64</td>
<td align="right">19.3</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">65-69</td>
<td align="right">35.1</td>
</tr>
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left">70-74</td>
<td align="right">50.0</td>
</tr>
</tbody>
</table>
<p>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.</p>
<h3>Update</h3>
<p>I can do the first example with the following code (based on Edwin Thoen’s blog, again):</p>
<pre>
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)
}
</pre>
<h3>Update 2</h3>
<p>The second example can be solved by extracting elements of quosures, which are essentially a list:</p>
<pre>
clean_cols = function(x, ...){
  colvs = quos(...)
  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 the dummies
  return(x)
}
</pre>
</div>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://statbandit.wordpress.com/2018/05/16/cleaning-up-tables/"> R – Stat Bandit</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/Jx_L-W3X1aU" height="1"></a></div>