Flextables examples
Alex Thomson - Stats4SD
04/06/2020
Flextable Examples
Basic Tables
To create a basic table just need to set up a data frame/tibble with
all the data, rows and columns you wish to display. Can simply just
assign a new object using flextable() if you wish to use
all columns in the data frame. Alternatively you can add the argument
col_keys to select only certain columns from the data.
imdb_animation_avgs <- imdb %>%
filter(type != "videoGame")%>%
group_by(type, animation)%>%
summarise(Mean.Votes = mean(numVotes, na.rm = TRUE),
Mean.Rating = mean(averageRating, na.rm = TRUE),
Number.Of.Entries = n())
t <- flextable(imdb_animation_avgs) #left assign a new flextable object from your data frame
ttype | animation | Mean.Votes | Mean.Rating | Number.Of.Entries |
|---|---|---|---|---|
movie | FALSE | 17,337.709 | 6.254202 | 41,085 |
movie | TRUE | 36,393.630 | 6.656642 | 1,197 |
short | FALSE | 1,576.763 | 6.823500 | 1,183 |
short | TRUE | 1,822.737 | 7.363987 | 933 |
tvMiniSeries | FALSE | 5,779.093 | 7.452980 | 906 |
tvMiniSeries | TRUE | 2,829.885 | 7.411458 | 96 |
tvMovie | FALSE | 1,857.997 | 6.093077 | 3,019 |
tvMovie | TRUE | 2,162.584 | 7.100000 | 125 |
tvSeries | FALSE | 12,953.661 | 7.208266 | 4,682 |
tvSeries | TRUE | 7,238.361 | 7.269250 | 1,174 |
tvShort | FALSE | 1,929.167 | 7.422917 | 48 |
tvShort | TRUE | 2,902.484 | 7.179121 | 91 |
tvSpecial | FALSE | 1,830.817 | 7.427378 | 431 |
tvSpecial | TRUE | 5,492.600 | 7.820000 | 5 |
video | FALSE | 2,556.080 | 5.559368 | 1,425 |
video | TRUE | 5,036.685 | 6.431738 | 397 |
Include a title and a subtitle
Adding a title and subtitle requires a little bit of logic to
remember. unfortunately you can’t just add them directly like other
packages. They are both added by using add_header_lines
function for which you just need to specify text. However, you want to
add the subtitle first as the header line always goes to the very top so
you need to start at the bottom of the header and work upwards.
In this example we have also changed the font size of the main title
to make it stand out from the subtitle. In flextable notation
i means row numbers or names and j means
column names or numbers. You also want to specify the part of the table
as the rows and columns within the header, the body of the table and the
footer are treated separately. So in this case the title is row 1 of the
header.
top_20_movies <- imdb %>%
filter(type == "movie" & numVotes > 99999)%>%
arrange(desc(averageRating))%>%
select(title, year, averageRating, numVotes)%>%
slice(1:20)
t <- flextable(top_20_movies)
t <- add_header_lines(t, values = "IMDB Entries with at least 100,000 votes") # add subtitle
t <- add_header_lines(t, values = "Top 20 Movies of all time") # add title
t <- fontsize(t, i = 1, size = 14, part = "header") #increase text size of the title
t <- autofit(t) # autofit the width of the table and columns
tTop 20 Movies of all time | |||
|---|---|---|---|
IMDB Entries with at least 100,000 votes | |||
title | year | averageRating | numVotes |
The Shawshank Redemption | 1,994 | 9.3 | 2,138,866 |
The Godfather | 1,972 | 9.2 | 1,468,315 |
The Godfather: Part II | 1,974 | 9.0 | 1,021,652 |
The Dark Knight | 2,008 | 9.0 | 2,102,907 |
The Mountain II | 2,016 | 9.0 | 101,445 |
12 Angry Men | 1,957 | 8.9 | 610,262 |
Schindler's List | 1,993 | 8.9 | 1,109,973 |
Pulp Fiction | 1,994 | 8.9 | 1,678,715 |
The Lord of the Rings: The Return of the King | 2,003 | 8.9 | 1,520,719 |
The Good, the Bad and the Ugly | 1,966 | 8.8 | 635,197 |
Forrest Gump | 1,994 | 8.8 | 1,646,150 |
The Lord of the Rings: The Fellowship of the Ring | 2,001 | 8.8 | 1,536,315 |
Fight Club | 1,999 | 8.8 | 1,709,679 |
Inception | 2,010 | 8.8 | 1,875,664 |
One Flew Over the Cuckoo's Nest | 1,975 | 8.7 | 845,858 |
Star Wars: Episode V - The Empire Strikes Back | 1,980 | 8.7 | 1,070,168 |
Goodfellas | 1,990 | 8.7 | 924,279 |
The Matrix | 1,999 | 8.7 | 1,540,221 |
The Lord of the Rings: The Two Towers | 2,002 | 8.7 | 1,375,568 |
It's a Wonderful Life | 1,946 | 8.6 | 363,682 |
change column labels and include number formatting
Changing the labels of columns is quite simple and takes the same
format as the rename function from dplyr. You simply write
variable.name = "new column name".
You can set the formatting of a numeric column using
colformat_num by specifying the column you wish to format
(j = 4 in this example). You can then set the number of
decimal places, the big mark, a string for missing values, and also add
any prefixing or suffixing if you wish to specify the units
directly.
t <- flextable(top_20_movies)
t <- set_header_labels(t,
title = "Movie",
year = "Year Released",
averageRating = "Rating",
numVotes = "Total Votes") # written in same fashion as dplyr::rename (variable.name = "new column name")
t <- colformat_num(t,
j = 4, # column number 4
digits = 0, # no decimal places
big.mark = ",") # use comas when dealing with large numbers
t <- add_header_lines(t, values = "IMDB Entries with at least 100,000 votes") # add subtitle
t <- add_header_lines(t, values = "Top 20 Movies of all time") # add title
t <- fontsize(t, i = 1, size = 14, part = "header") #increase text size of the title
t <- autofit(t)
tTop 20 Movies of all time | |||
|---|---|---|---|
IMDB Entries with at least 100,000 votes | |||
Movie | Year Released | Rating | Total Votes |
The Shawshank Redemption | 1,994 | 9.3 | 2,138,866 |
The Godfather | 1,972 | 9.2 | 1,468,315 |
The Godfather: Part II | 1,974 | 9.0 | 1,021,652 |
The Dark Knight | 2,008 | 9.0 | 2,102,907 |
The Mountain II | 2,016 | 9.0 | 101,445 |
12 Angry Men | 1,957 | 8.9 | 610,262 |
Schindler's List | 1,993 | 8.9 | 1,109,973 |
Pulp Fiction | 1,994 | 8.9 | 1,678,715 |
The Lord of the Rings: The Return of the King | 2,003 | 8.9 | 1,520,719 |
The Good, the Bad and the Ugly | 1,966 | 8.8 | 635,197 |
Forrest Gump | 1,994 | 8.8 | 1,646,150 |
The Lord of the Rings: The Fellowship of the Ring | 2,001 | 8.8 | 1,536,315 |
Fight Club | 1,999 | 8.8 | 1,709,679 |
Inception | 2,010 | 8.8 | 1,875,664 |
One Flew Over the Cuckoo's Nest | 1,975 | 8.7 | 845,858 |
Star Wars: Episode V - The Empire Strikes Back | 1,980 | 8.7 | 1,070,168 |
Goodfellas | 1,990 | 8.7 | 924,279 |
The Matrix | 1,999 | 8.7 | 1,540,221 |
The Lord of the Rings: The Two Towers | 2,002 | 8.7 | 1,375,568 |
It's a Wonderful Life | 1,946 | 8.6 | 363,682 |
Add borders
Adding borders requires the addition of another package called
officer. Using this package you create what is known as a border
properties object by setting its colour, style and width
e.g. border_h = fp_border(color="blue", style = "dotted", width = 3)
will create a a thick blue dotted border that can be used within your
flextable
border_h = fp_border(color="blue", style = "dotted", width = 3)
border_o = fp_border(color = "red", style = "solid", width = 2)
t <- border_inner(t, part = "body", border = border_h) #SET BORDER FOR EVERY ROW AND COLUMN INSIDE THE TABLE
t <- border_outer(t, border = border_o, part = "body") # SET OUTER BORDER
#t <- border_inner_h(t, part = "body", border = border_h) ONLY HORIZONTAL BORDERS WITHIN THE BODY OF THE TABLE
#t <- border_inner_v(t, part = "body", border = border_h) ONLY SET VERTICAL BORDERS WITHIN THE BODY OF THE TABLE
#t <- hline(t, i = ..., j = ..., part = ..., border = ...) SET HORIZONTAL BORDERS ON SPECIFIC ROWS/COLUMNS (BOTTOM OF CELL)
#t <- vline(t, i = ..., j = ..., part = ..., border = ...) SET VERTICAL BORDERS ON SPECIFIC ROWS/COLUMNS (RIGHT OF CELL)
tTop 20 Movies of all time | |||
|---|---|---|---|
IMDB Entries with at least 100,000 votes | |||
Movie | Year Released | Rating | Total Votes |
The Shawshank Redemption | 1,994 | 9.3 | 2,138,866 |
The Godfather | 1,972 | 9.2 | 1,468,315 |
The Godfather: Part II | 1,974 | 9.0 | 1,021,652 |
The Dark Knight | 2,008 | 9.0 | 2,102,907 |
The Mountain II | 2,016 | 9.0 | 101,445 |
12 Angry Men | 1,957 | 8.9 | 610,262 |
Schindler's List | 1,993 | 8.9 | 1,109,973 |
Pulp Fiction | 1,994 | 8.9 | 1,678,715 |
The Lord of the Rings: The Return of the King | 2,003 | 8.9 | 1,520,719 |
The Good, the Bad and the Ugly | 1,966 | 8.8 | 635,197 |
Forrest Gump | 1,994 | 8.8 | 1,646,150 |
The Lord of the Rings: The Fellowship of the Ring | 2,001 | 8.8 | 1,536,315 |
Fight Club | 1,999 | 8.8 | 1,709,679 |
Inception | 2,010 | 8.8 | 1,875,664 |
One Flew Over the Cuckoo's Nest | 1,975 | 8.7 | 845,858 |
Star Wars: Episode V - The Empire Strikes Back | 1,980 | 8.7 | 1,070,168 |
Goodfellas | 1,990 | 8.7 | 924,279 |
The Matrix | 1,999 | 8.7 | 1,540,221 |
The Lord of the Rings: The Two Towers | 2,002 | 8.7 | 1,375,568 |
It's a Wonderful Life | 1,946 | 8.6 | 363,682 |
Special Features
There are many special features of FlexTable that may not be necessarily needed most of the time but are worth highlighting though they are certainly for someone wishing to do something a bit more advanced.
Most of this revolves around the compose() function when
used in combination with as_paragraph(). This allows you to
manually edit text within the table as well as change who the data is
visualised including by adding mini bars, line ranges and even adding
image files.
border_h = fp_border(color="blue")
t <- flextable(random_movies,
col_keys = c("title", "year", "length", "numVotes", "averageRating", "director")) # use col keys to sepcify columns to plot
t <- add_header_lines(t, values = "IMDB Entries with at least 100,000 votes") # add subtitle
t <- add_header_lines(t, values = "20 Movies") # add title
t <- flextable::align(t, i = 1:2, align = "center", part = "header") # centre align the title and sub title
t <- fontsize(t, size = 12, part = "header") # increase text size of header
t <- colformat_int(t, j = 4, big.mark = ",") # format numbers
t <- flextable::compose(t,
j = 5,
value = as_paragraph(
linerange(value = averageRating)), # default minimum = minum of value and maximum = maximum of value
part = "body") # turn average rating into a line range visual data representation
t <- flextable::compose(t,
j = 3,
value = as_paragraph(minibar(value = length, max = max(length))), # default minimum = minum of value and maximum = maximum of value
part = "body") # turn length into a mini bar data respresentation
# SET DIRECTORS TO BE IMAGE FILES RATHER THAN THE TEXT
t <- flextable::compose(t,
i = ~ title == "Chef", # can conditionally set columns and row indexes as well e.g. row where title == "Chef"
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/jon favreau.jpg", width = 2, height = 2))) # additionally specify as_image
# provide a file link to the image
# set width and height of the image
t <- flextable::compose(t,
i = c(1,8),
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/joel schumacher.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 2,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Jay_Roach.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 3,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Gabor_Csupo.png", width = 2, height = 2)))
t <- flextable::compose(t,
i = 5,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Ryan_Coogler.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 6,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Alex_Proyas.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 7,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Todd_Phillips.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 9,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Terry_Gilliam.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = c(10, 15),
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Guillermo-del-Toro-2017.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 11,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Eric_Brevig.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 12,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/JUDDPORTRAIT.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 13,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/220px-Dan_Scanlon.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 14,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Nicolas.png", width = 2, height = 2)))
t <- flextable::compose(t,
i = 16,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Wes_Craven.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 17,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Nancy_Meyers.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 18,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Phillip_Noyce.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 19,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Edward_Zwick.jpg", width = 2, height = 2)))
t <- flextable::compose(t,
i = 20,
j = 6,
value = as_paragraph(flextable::as_image(src = "directors/Iain_Softley.jpg", width = 2, height = 2)))
t <- width(t, width = 2) # manually set column width
t<-border_inner_h(t, part="body", border = border_h ) # set an interanal border
t20 Movies | |||||
|---|---|---|---|---|---|
IMDB Entries with at least 100,000 votes | |||||
title | year | length | numVotes | averageRating | director |
8MM | 1,999 |
| 116,986 |
|
|
Austin Powers in Goldmember | 2,002 |
| 186,300 |
|
|
Bridge to Terabithia | 2,007 |
| 133,561 |
|
|
Chef | 2,014 |
| 178,322 |
|
|
Creed | 2,015 |
| 229,491 |
|
|
Dark City | 1,998 |
| 179,019 |
|
|
Due Date | 2,010 |
| 307,343 |
|
|
Falling Down | 1,993 |
| 157,763 |
|
|
Fear and Loathing in Las Vegas | 1,998 |
| 245,293 |
|
|
Hellboy | 2,004 |
| 293,763 |
|
|
Journey to the Center of the Earth | 2,008 |
| 106,058 |
|
|
Knocked Up | 2,007 |
| 335,238 |
|
|
Monsters University | 2,013 |
| 297,753 |
|
|
Only God Forgives | 2,013 |
| 101,803 |
|
|
Pacific Rim | 2,013 |
| 448,115 |
|
|
Scream 2 | 1,997 |
| 146,336 |
|
|
Something's Gotta Give | 2,003 |
| 104,880 |
|
|
The Bone Collector | 1,999 |
| 144,080 |
|
|
The Last Samurai | 2,003 |
| 374,818 |
|
|
The Skeleton Key | 2,005 |
| 100,935 |
|
|
Cotton Farming example
The cotton farming example shown in the seminar showed off how to
group rows together with a title for this sub group. This can be done so
long as you have a column in your data frame which defines these groups.
You can then sue the as_grouped_data() function before you
create a flextable to set up this grouped data structure which will then
create these groupings within the data frame. Think of it like the
group_by function in dplyr but much more visual. If you
look at your data frame after running this function you will see what it
does exactly.
A <- as_grouped_data(x = A,
groups = c("Group")) # group our data on the Group column
A[is.na(A)] = " " #repalce NAs with an emmpty string
border_h = fp_border(color="gray") # create a border object
t<-flextable(A)
t<-set_header_labels(t, values = list(Group = "Group",
`Sub-Group` = "Sub-group",
`Mean (Farm` = str_wrap("Farm Area (Mean)",7),
`Median (Farm)` = str_wrap("Farm Area (Median)",7),
`Mean (Cottton)` = str_wrap("Cotton Area (Mean)",7),
`Median (Cotton)` = str_wrap("Cotton Area (Median)",7),
`Mean (%Cotton)` = str_wrap("Cotton Area as % of Farm Area (Mean)",12),
`Median (%Cotton)` = str_wrap("Cotton Area as % of Farm Area (Median)",12),
N = "N"))
t<-fontsize(t, size = 9, part = "all")
t<-width(t, width = 0.90)
t<-add_header_lines(t, values = "Farm Area (Acre), Cotton Area (Acre), Cotton Area as % of Farm Area")
t <- border_inner_h(t, part="body", border = border_h )
tFarm Area (Acre), Cotton Area (Acre), Cotton Area as % of Farm Area | ||||||||
|---|---|---|---|---|---|---|---|---|
Group | Sub-group | Mean (Farm) | Farm | Cotton | Cotton | Cotton Area | Cotton Area | N |
State |
|
|
|
|
|
|
|
|
| Gujarat | 4.97 | 5 | 4.72 | 4.5 | 95.3 | 100 | 100 |
| Madhya Pradesh | 4.24 | 4 | 3.32 | 3 | 77.9 | 83.8 | 100 |
| Maharashtra | 2.12 | 1 | 2.04 | 1 | 98.5 | 100 | 100 |
| Rajasthan | 4.8 | 4.3 | 2.01 | 1.5 | 43.1 | 48.2 | 50 |
Partner |
|
|
|
|
|
|
|
|
| 1 | 5.31 | 5.05 | 4.87 | 5 | 91.6 | 100 | 50 |
| 2 | 4.64 | 4.05 | 4.57 | 4 | 99 | 100 | 50 |
| 3 | 1 | 1 | 1 | 1 | 100 | 100 | 50 |
| 4 | 3.24 | 3 | 3.08 | 2.25 | 97.1 | 100 | 50 |
| 5 | 4.69 | 5.05 | 3.91 | 3 | 82.9 | 100 | 50 |
| 6 | 3.79 | 4 | 2.72 | 3 | 72.9 | 75 | 50 |
| 7 | 4.8 | 4.3 | 2.01 | 1.5 | 43.1 | 48.2 | 50 |
Overall |
|
|
|
|
|
|
|
|
| Total | 3.92 | 4 | 3.16 | 3 | 83.8 | 100 | 350 |
Modelling Examples
It is additionally not too difficult to create modelling tables using
flextable. You could combine it with the very useful function
tidy() from the broom packages which turns model summaries
into a convenient tidy data fame that can be used as a table. This tidy
function can be used for numerous different types of statistical
models.
Additionally however, with any glm, lm and some statistical tests you
can directly pipe in the as_flextable() function and this
will automatically create a model table that includes significance codes
and footnotes on various model characteristics such as the R-squared and
F-statistic.
movies <- imdb%>%
filter(type == "movie")%>%
mutate(num_Votes_10000 = numVotes/10000)
model1 <- aov(averageRating ~ length + num_Votes_10000 + animation, movies)
m1 <- broom::tidy(model1)
t <- flextable(m1)
t <- set_header_labels(t, values = list(
term = "Term",
df = "Df",
sumsq = "SS",
meansq = "MSS",
statistic = "F - Statistic",
p.value = "P.Value"
))
t <- colformat_double(t, j = c(3:6), digits = 2, na_str = " ")
t <- autofit(t)
tTerm | Df | SS | MSS | F - Statistic | P.Value |
|---|---|---|---|---|---|
length | 1 | 3,709.75 | 3,709.75 | 2,834.57 | 0.00 |
num_Votes_10000 | 1 | 1,169.75 | 1,169.75 | 893.79 | 0.00 |
animation | 1 | 339.00 | 339.00 | 259.03 | 0.00 |
Residuals | 42,278 | 55,331.32 | 1.31 |
|
|
lm(averageRating ~ length + num_Votes_10000 + animation, movies) %>%
as_flextable()Estimate | Standard Error | t value | Pr(>|t|) | ||
|---|---|---|---|---|---|
(Intercept) | 4.925 | 0.026 | 187.775 | 0.0000 | *** |
length | 0.012 | 0.000 | 49.998 | 0.0000 | *** |
num_Votes_10000 | 0.023 | 0.001 | 28.857 | 0.0000 | *** |
animationTRUE | 0.544 | 0.034 | 16.094 | 0.0000 | *** |
Signif. codes: 0 <= '***' < 0.001 < '**' < 0.01 < '*' < 0.05 | |||||
Residual standard error: 1.144 on 42278 degrees of freedom | |||||
Multiple R-squared: 0.08619, Adjusted R-squared: 0.08612 | |||||
F-statistic: 1329 on 42278 and 3 DF, p-value: 0.0000 | |||||
t.test(averageRating ~ fantasy, data = movies)%>%
as_flextable()estimate | estimate1 | estimate2 | statistic | p.value | parameter | conf.low | conf.high | method | alternative |
|---|---|---|---|---|---|---|---|---|---|
0.3 | 6.3 | 6.0 | 10.4 | 0.0000*** | 2,372.1 | 0.2 | 0.3 | Welch Two Sample t-test | two.sided |
Signif. codes: 0 <= '***' < 0.001 < '**' < 0.01 < '*' < 0.05 | |||||||||