Get access to all lessons in this course.
Making Beautiful Tables with R
- Get Data Into the Right Format to Create your First Table
- Use better column names and a title
- Align columns
- Use groups instead of repetitive columns
- Format your table's numbers
- Add summaries
- Add additional horizontal lines
- Add background colors
- Change the text appearance
- Change cell properties
- Export Your Tables
- Heat map columns
- Adding Charts with Flextable
- Add your own ggplot
- Case Study
Get Data Into the Right Format to Create your First Table
This lesson is locked
This lesson is called Get Data Into the Right Format to Create your First Table, part of the Making Beautiful Tables with R course. This lesson is called Get Data Into the Right Format to Create your First Table, part of the Making Beautiful Tables with R course.
Click on the transcript to go to that point in the video. Please note that transcripts are auto generated and may contain minor inaccuracies.
This lesson is about getting the data to the right format before we can even begin to build the table. Regardless of what package you use to create the table, it will just take your data frame and put it into a table as it is. Thus, you need to rearrange that data in your data frame before even calling, say,
The most powerful function to get this job done are
pivot_longer(), they are able to transition the data frame from long format to white format and vice versa.
library(tidyverse) library(flextable) selected_countries_vector <- c( 'Egypt', 'Sierra Leone', 'Nicaragua', 'Jamaica', 'Syria', 'Singapore', 'Netherlands', 'United Kingdom', 'New Zealand', 'Australia' ) selected_countries <- gapminder::gapminder |> janitor::clean_names() |> filter(country %in% selected_countries_vector, str_ends(year, "7")) |> select(continent, country, year, life_exp) |> pivot_wider(names_from = 'year', names_prefix = 'year', values_from = 'life_exp') |> arrange(continent, country) selected_countries |> flextable()
In this exercise, we want to rearrange the data in the data set
gapminder from the
gapminder package. This data set contains life expectancies for different countries all over the world from different points in time. We want to consider only a couple of years and a couple of countries from this data set. Take the full
gapminder data set, i.e. use:
gapminder::gapminder |> janitor::clean_names() # optional but makes nicer column names
You should get this:
## # A tibble: 1,704 × 6 ## country continent year life_exp pop gdp_percap ## <fct> <fct> <int> <dbl> <int> <dbl> ## 1 Afghanistan Asia 1952 28.8 8425333 779. ## 2 Afghanistan Asia 1957 30.3 9240934 821. ## 3 Afghanistan Asia 1962 32.0 10267083 853. ## 4 Afghanistan Asia 1967 34.0 11537966 836. ## 5 Afghanistan Asia 1972 36.1 13079460 740. ## 6 Afghanistan Asia 1977 38.4 14880372 786. ## 7 Afghanistan Asia 1982 39.9 12881816 978. ## 8 Afghanistan Asia 1987 40.8 13867957 852. ## 9 Afghanistan Asia 1992 41.7 16317921 649. ## 10 Afghanistan Asia 1997 41.8 22227415 635. ## # ℹ 1,694 more rows
From there, extract the correct data and rearrange it so that you get a data set like this:
# A tibble: 10 × 8 continent country year1957 year1967 year1977 year1987 year1997 year2007 <fct> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> 1 Africa Egypt 44.4 49.3 53.3 59.8 67.2 71.3 2 Africa Sierra Leone 31.6 34.1 36.8 40.0 39.9 42.6 3 Americas Jamaica 62.6 67.5 70.1 71.8 72.3 72.6 4 Americas Nicaragua 45.4 51.9 57.5 62.0 68.4 72.9 5 Asia Singapore 63.2 67.9 70.8 73.6 77.2 80.0 6 Asia Syria 48.3 53.7 61.2 67.0 71.5 74.1 7 Europe Netherlands 73.0 73.8 75.2 76.8 78.0 79.8 8 Europe United Kingd… 70.4 71.4 72.8 75.0 77.2 79.4 9 Oceania Australia 70.3 71.1 73.5 76.3 78.8 81.2 10 Oceania New Zealand 70.3 71.5 72.2 74.3 77.6 80.2
Once you’ve done that you can create your first basic table with
flextable(). Hint: In this exercise, you can use this vector as a helpful tool (so that you don’t have to type out the countries).
selected_countries_vector <- c( 'Egypt', 'Sierra Leone', 'Nicaragua', 'Jamaica', 'Syria', 'Singapore', 'Netherlands', 'United Kingdom', 'New Zealand', 'Australia' )