Format values in a table for publication

You are making a table for publication, and you want to format values in your table to look like times, fractions, currency values, scientific notation and more.

Step 1 - Start with a data frame that contains the values that you want to display in the arrangement that you want to display them.

Step 2 - Use gt::gt() to turn your data frame into an editable, professional looking table.

Step 3 - Use gt’s fmt_*() functions to format the values in your table. Specify the columns to format and then how to format them. Each function provides its own formatting options.

data %>% 
  gt() %>% 
  fmt_currency(
    columns = col_dollar, 
    currency = "USD",
    decimals = 2
  )

gt formatting functions

Function	Format values as
fmt_bytes()	bytes
fmt_currency()	currencies
fmt_date()	dates
fmt_datetime()	date-times
fmt_engineering()	engineering notation
fmt_fraction()	mixed fractions
fmt_integer()	integers
fmt_markdown()	Markdown text
fmt_number()	numeric values (with a decimal place)
fmt_partsper()	parts-per quantities
fmt_passthrough()	text pattern
fmt_percent()	percentage
fmt_scientific()	scientific notation
fmt_time()	time

Each function has a unique set of arguments. Learn more at its help page.

Example

quaker_df describes various Quaker oatmeal products sold across multiple stores in one week.

quaker_df

# A tibble: 4 × 4
  product                    avg_price total_sold avg_sold
  <chr>                          <dbl>      <int>    <dbl>
1 Quaker Instant Grits 12 oz      1.87         35     11.7
2 Quaker Kids Choice 14.3 oz      3.09         21      7  
3 Quick Quaker Oats 18 oz         1.89        129     43  
4 Quick Quaker Oats 42 oz         2.05        193     64.3

For publication, we want to display the avg_price column as USD and truncate the avg_sold column to one digit past the decimal place.

We first load the gt package, and convert our data frame to a gt table with gt(). We then use two gt formatting functions to change how we display the values in the avg_price and avg_sold columns.

library(gt)
quaker_df %>% 
  gt() %>% 
  fmt_currency(
    columns = avg_price,
    currency = "USD"
    ) %>% 
  fmt_number(
    columns = avg_sold,
    decimals = 1
  )

product	avg_price	total_sold	avg_sold
Quaker Instant Grits 12 oz	$1.87	35	11.7
Quaker Kids Choice 14.3 oz	$3.09	21	7.0
Quick Quaker Oats 18 oz	$1.89	129	43.0
Quick Quaker Oats 42 oz	$2.05	193	64.3

Formatting values in SAS tables

Tables for publication can be created in SAS using either the TABULATE or REPORT procedure.

In R, you create the table and then convert it to publication quality. However, in SAS you create the table and make it look nice all in the same step.

In SAS:

PROC TABULATE DATA = quaker_sales;
  CLASS product;
  VAR price units_sold;
  TABLE product, 
        (price*MEAN *FORMAT=DOLLAR10.2
         units_sold*SUM 
         units_sold* MEAN *FORMAT=COMMA8.1 );
RUN;

or

PROC REPORT DATA = quaker_sales;
    COLUMN product price=avg_price units_sold=total_units units_sold=avg_sold;
    DEFINE product / GROUP;
    DEFINE avg_price / ANALYSIS MEAN "avg_price" FORMAT=DOLLAR10.2 ;
    DEFINE units_sold / ANALYSIS SUM "units_sold" ;
    DEFINE avg_sold / ANALYSIS MEAN "avg_sold" FORMAT=COMMA9.1 ;
RUN;

In R:

quaker_sales %>% 
  group_by(product) %>% 
  summarize(
    avg_price = mean(price),
    total_sold = sum(units_sold),
    avg_sold = mean(units_sold)
  ) %>% 
  gt() %>% 
  fmt_currency(
    columns = avg_price,
    decimals = 2
  ) %>% 
  fmt_number(
    columns = avg_sold,
    decimals = 1
  )