Note

For those who want to shadow this process, you may need two packages, tidyverse and flextable. This memo has been created under R version 3.5.1 (2018-07-02).




1. Overview

You may be sick and tired of broken table by kable() in knitr package when outputting your report to Microsoft Word format. Here is my alternative to kable() function using flextable package. The example below used mtcars dataset.




2. Fixing automatic rowname drop problem

Unlike kable() function, table formats in the flextable package automatically drops your rownames of your dataframe. In order to prevent this, we can use colkey = option in regulartable() or flextable() function. It is convenient when you create a dataset. However, it is not when you just format your data frame with long rownames. My suggestion is to save the rownames as a new column before formatting.



dat <- mtcars[, 1:6] %>% 
    mutate(model = rownames(mtcars)) %>%
    select(ncol(.), 1:(ncol(.)-1))




3. Formatting the table

  • Basic theme: regulartable(), flextable()
  • Automatic column width adjustment by autofit()



First of all, let’s start with the basic theme functions in the flextable package: regulartable() and flextable().



dat %>% head() %>% regulartable()

model

mpg

cyl

disp

hp

drat

wt

Mazda RX4

21.000

6.000

160.000

110.000

3.900

2.620

Mazda RX4 Wag

21.000

6.000

160.000

110.000

3.900

2.875

Datsun 710

22.800

4.000

108.000

93.000

3.850

2.320

Hornet 4 Drive

21.400

6.000

258.000

110.000

3.080

3.215

Hornet Sportabout

18.700

8.000

360.000

175.000

3.150

3.440

Valiant

18.100

6.000

225.000

105.000

2.760

3.460

dat %>% head() %>% flextable()

model

mpg

cyl

disp

hp

drat

wt

Mazda RX4

21.000

6.000

160.000

110.000

3.900

2.620

Mazda RX4 Wag

21.000

6.000

160.000

110.000

3.900

2.875

Datsun 710

22.800

4.000

108.000

93.000

3.850

2.320

Hornet 4 Drive

21.400

6.000

258.000

110.000

3.080

3.215

Hornet Sportabout

18.700

8.000

360.000

175.000

3.150

3.440

Valiant

18.100

6.000

225.000

105.000

2.760

3.460



In case the width of your table needs adjusting, make use of autofit() function. It will automatically adjust your table’s column width.



head(dat) %>%
    regulartable() %>%
    autofit()

model

mpg

cyl

disp

hp

drat

wt

Mazda RX4

21.000

6.000

160.000

110.000

3.900

2.620

Mazda RX4 Wag

21.000

6.000

160.000

110.000

3.900

2.875

Datsun 710

22.800

4.000

108.000

93.000

3.850

2.320

Hornet 4 Drive

21.400

6.000

258.000

110.000

3.080

3.215

Hornet Sportabout

18.700

8.000

360.000

175.000

3.150

3.440

Valiant

18.100

6.000

225.000

105.000

2.760

3.460




4. Add a footnote at the bottom of your table

  • Add a footer (footnote) at the bottom of the table: add_footer()
  • Merge the bottom row for footer space: merge_at()



When you want to attach a footnote at the bottom of your table, you can use add_footer() function in the flextable package. Howver, there is some tricky part to understand how this function works. First of all, you have to specify the place where your footnote begins by column name.



  • add_footer([your dataframe], [column name] = “text”)



Moreover, your footnote will goes to the second line easily, if you only apply this function in adition to your previous code.



head(dat) %>%
    regulartable() %>%
    autofit() %>%
    add_footer(., model = "* The data was extracted from the 1974 Motor Trend US magazine, and comprises fuel consumption and 10 aspects of automobile design and performance for 32 automobiles (1973–74 models).")

model

mpg

cyl

disp

hp

drat

wt

Mazda RX4

21.000

6.000

160.000

110.000

3.900

2.620

Mazda RX4 Wag

21.000

6.000

160.000

110.000

3.900

2.875

Datsun 710

22.800

4.000

108.000

93.000

3.850

2.320

Hornet 4 Drive

21.400

6.000

258.000

110.000

3.080

3.215

Hornet Sportabout

18.700

8.000

360.000

175.000

3.150

3.440

Valiant

18.100

6.000

225.000

105.000

2.760

3.460

* The data was extracted from the 1974 Motor Trend US magazine, and comprises fuel consumption and 10 aspects of automobile design and performance for 32 automobiles (1973–74 models).



This happens because there are invisible compartments between columns at the footnote row. In order to fix this problem, we need to merge the footnote’s row by using merge_at() function in the flextable package.



head(dat) %>%
    regulartable() %>%
    autofit() %>%
    add_footer(., model = "* The data was extracted from the 1974 Motor Trend US magazine, and comprises fuel consumption and 10 aspects of automobile design and performance for 32 automobiles (1973–74 models).") %>%
    merge_at(., i=1, j = 1:ncol(dat), part = "footer")

model

mpg

cyl

disp

hp

drat

wt

Mazda RX4

21.000