CODEWITHSUNDEEP
rdataframeformattidyversekable
Simon Woodward
Simon Woodward

Reputation: 2026

Format numeric data table rowwise in R using tidyverse for kable output

I have a table of values that I want to save as a kable() table. Each row of the table is a variable and each column is a value of that variable (e.g., a mean, minimum, maximum, etc.). You can apply the format() function to columns of a data frame but applying it across rows seems very awkward. I finally achieved my goal with this code, but would be interested if there is a tidier way of doing it!

library(tidyverse)
library(broom)
library(kableExtra)

# test data
all <- tibble(PARAMETER=c("A","B", "C"), 
              Value1=c(0.0123, 1230, NA), 
              Value2=c(0.0234, 2340, 1.23), 
              Units=c("m", "Pa", "ha"), 
              Description=c("Length", "Pressure", "Area"))

# my formatting function
my_format <- function(x){
  y <- format(x, digits=3, scientific=FALSE, TRIM=TRUE)
  y[is.na(x)] <- ""
  y
}

# format values by row
all_formatted <- all %>%
  `row.names<-`(.$PARAMETER) %>% # set row names for transpose
  select(-PARAMETER, -Units, -Description) %>% # only numeric columns
  t() %>% # transpose
  tidy() %>% # convert to tibble (creates .rownames column)
  modify(my_format) %>% # apply format function to each column of values in place
  `row.names<-`(.$.rownames) %>% # set row names for transpose
  select(-.rownames) %>% # drop rownames column
  t() %>% # transpose
  tidy() %>% # convert to tibble (creates .rownames column)
  select(-.rownames) %>% # drop rownames
  add_column(PARAMETER=all$PARAMETER, .before=1) %>% # add back nonnumeric columns
  add_column(UNITS=all$Units, 
             DESCRIPTION=all$Description)

# print formatted table
all_formatted %>%
  kable() %>%
  kable_styling(
    bootstrap_options = c("condensed", "striped", "hover"),
    full_width=FALSE, position="left", font_size=12) %>%
  save_kable(file="temp.html", self_contained=TRUE) # very slow

enter image description here

Upvotes: 1

Views: 1003

Answers (1)

mysteRious
mysteRious

Reputation: 4294

Not sure how attached you are to kable, but flextable is one of my favorite things and it usually renders to HTML and .doc the same way. The trick is to do it by column, not row... just specify the characteristics of the numbers you want to format differently:

library(flextable)
all %>% regulartable() %>% align(align="center",part="all") %>% 
    set_formatter(Value1 = function(x) 
                     ifelse(x > 1, sprintf("%.0f", x), sprintf("%.03f", x) ),
                  Value2 = function(x) 
                     ifelse(x > 1, sprintf("%.0f", x), sprintf("%.03f", x) ))

enter image description here

Hope this helps :)

Upvotes: 1

Related Questions