Making Tables

October 16, 2024

Why Tables?


  • Summary statistics
  • Precise values (for small number of cases)
  • Regression tables

Awesome Table


Mean Household Income of Quintiles, 2021
Seven Representative U.S. States
lowest second third fourth highest top 5%
District of Columbia $12,971 $51,060 $94,478 $157,803 $375,792 $670,768
Minnesota $18,936 $48,127 $78,073 $117,959 $250,361 $441,274
Nevada $15,024 $40,468 $66,247 $101,591 $224,480 $410,161
Alaska $19,344 $51,030 $81,169 $122,652 $242,097 $394,694
Montana $14,811 $36,927 $60,644 $93,340 $205,462 $370,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234

Source: US Census Bureau, American Community Survey

When Not to Use Tables?

  • To show a trend
  • Display a relationship
  • Lots of values
  • Few values but column chart is better

The Grammar of Tables

Prep

  • Do the prework for the module
  • Get a census API key

Set API key


library(tidycensus)
census_api_key("YOUR API KEY") # enter your census api key here in quotes

Load Variables


Search for “quintile”…


v21 <- load_variables(2021, "acs5", cache = TRUE)

#View(v21)

Clean the Data

library(stringr)
library(dplyr)

quintiles <- get_acs(geography = "state", 
                      variables = c(q1 = "B19081_001",
                                    q2 = "B19081_002",
                                    q3 = "B19081_003",
                                    q4 = "B19081_004",
                                    q5 = "B19081_005",
                                    top5 = "B19081_006"),
                      year = 2021,
                      output = "wide") |>
                      select(
                        !ends_with("M"), # eliminate margin of error
                        -GEOID) |> # eliminate geo id
                      rename(name = NAME) |>
                      rename_with(~str_remove(., 'E'))
    

glimpse(quintiles)
10:00

kableExtra Table

name q1 q2 q3 q4 q5 top5
District of Columbia 12971 51060 94478 157803 375792 670768
Connecticut 17417 48870 84042 133488 319533 602707
New York 14054 42220 75647 123318 302676 574063
New Jersey 18458 52339 90337 142858 319140 562886
Massachusetts 16812 50519 89602 142491 316447 558616
California 17433 49234 84658 134560 309857 555007
Maryland 19946 55165 91725 140353 293979 503597
Washington 19367 50803 82817 127003 277165 487950
Virginia 17922 48359 81072 127411 280299 486006
Illinois 15102 42688 72900 114531 258373 466713

kableExtra Table


top_10 <- quintiles |>
  slice_max(top5, n = 10)

kable(top_10)

Issues with this table?

name q1 q2 q3 q4 q5 top5
District of Columbia 12971 51060 94478 157803 375792 670768
Connecticut 17417 48870 84042 133488 319533 602707
New York 14054 42220 75647 123318 302676 574063
New Jersey 18458 52339 90337 142858 319140 562886
Massachusetts 16812 50519 89602 142491 316447 558616
California 17433 49234 84658 134560 309857 555007
Maryland 19946 55165 91725 140353 293979 503597
Washington 19367 50803 82817 127003 277165 487950
Virginia 17922 48359 81072 127411 280299 486006
Illinois 15102 42688 72900 114531 258373 466713

Let’s make a gt table

Create states data frame…

# lowest 
state_min <- quintiles |> 
  slice_min(q1) 

# highest
state_max <- quintiles |> 
  slice_max(top5) 

# randomly select five more
five_more <- quintiles |>
   slice_sample(n = 5) 

states <- bind_rows(state_min, state_max, five_more) |>
  arrange(desc(top5))
05:00

Let’s make a gt table


library(gt)
gt(states)
name q1 q2 q3 q4 q5 top5
District of Columbia 12971 51060 94478 157803 375792 670768
Minnesota 18936 48127 78073 117959 250361 441274
Nevada 15024 40468 66247 101591 224480 410161
Alaska 19344 51030 81169 122652 242097 394694
Montana 14811 36927 60644 93340 205462 370234
Puerto Rico 2906 12144 22163 38397 99043 187234
Puerto Rico 2906 12144 22163 38397 99043 187234

Let’s make a good gt table


Mean Household Income of Quintiles, 2021
Seven Representative U.S. States
lowest second third fourth highest top 5%
District of Columbia $12,971 $51,060 $94,478 $157,803 $375,792 $670,768
Minnesota $18,936 $48,127 $78,073 $117,959 $250,361 $441,274
Nevada $15,024 $40,468 $66,247 $101,591 $224,480 $410,161
Alaska $19,344 $51,030 $81,169 $122,652 $242,097 $394,694
Montana $14,811 $36,927 $60,644 $93,340 $205,462 $370,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234

Source: US Census Bureau, American Community Survey

Let’s make a good gt table

goodtable <- gt(states) |> 
  tab_header(
    title = "Mean Household Income of Quintiles, 2021",
    subtitle = "Seven Representative U.S. States"
  ) |> 
  cols_label(
    name = "",
    q1 = "lowest",
    q2 = "second",
    q3 = "third",
    q4 = "fourth",
    q5 = "highest",
    top5 = "top 5%"
  ) |> 
  fmt_currency(
    columns = c(q1:top5),
    currency = "USD", 
    use_subunits = FALSE
  ) |>
  # note that you can use markdown (md) to format the source note for html documents
  tab_source_note(source_note = md("**Source**: US Census Bureau, American Community Survey"))

goodtable
10:00

Change Colum Width

Mean Household Income of Quintiles, 2021
Seven Representative U.S. States
lowest second third fourth highest top 5%
District of Columbia $12,971 $51,060 $94,478 $157,803 $375,792 $670,768
Minnesota $18,936 $48,127 $78,073 $117,959 $250,361 $441,274
Nevada $15,024 $40,468 $66,247 $101,591 $224,480 $410,161
Alaska $19,344 $51,030 $81,169 $122,652 $242,097 $394,694
Montana $14,811 $36,927 $60,644 $93,340 $205,462 $370,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234

Source: US Census Bureau, American Community Survey

Change Colum Width

vgoodtable <- goodtable |>
  cols_width(c(q1:top5) ~ px(90))

vgoodtable

Change Font

Mean Household Income of Quintiles, 2021
Seven Representative U.S. States
lowest second third fourth highest top 5%
District of Columbia $12,971 $51,060 $94,478 $157,803 $375,792 $670,768
Minnesota $18,936 $48,127 $78,073 $117,959 $250,361 $441,274
Nevada $15,024 $40,468 $66,247 $101,591 $224,480 $410,161
Alaska $19,344 $51,030 $81,169 $122,652 $242,097 $394,694
Montana $14,811 $36,927 $60,644 $93,340 $205,462 $370,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234

Source: US Census Bureau, American Community Survey

Change Font

greattable <- vgoodtable |>
  opt_table_font(font = "verdana")

greattable

Center

Mean Household Income of Quintiles, 2021
Seven Representative U.S. States
lowest second third fourth highest top 5%
District of Columbia $12,971 $51,060 $94,478 $157,803 $375,792 $670,768
Minnesota $18,936 $48,127 $78,073 $117,959 $250,361 $441,274
Nevada $15,024 $40,468 $66,247 $101,591 $224,480 $410,161
Alaska $19,344 $51,030 $81,169 $122,652 $242,097 $394,694
Montana $14,811 $36,927 $60,644 $93,340 $205,462 $370,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234

Source: US Census Bureau, American Community Survey

Center

vgreattable <- greattable |>
  cols_align(
  align = "center",
  columns = q1:top5
)

vgreattable

Add Borders and Lines

Mean Household Income of Quintiles, 2021
Seven Representative U.S. States
lowest second third fourth highest top 5%
District of Columbia $12,971 $51,060 $94,478 $157,803 $375,792 $670,768
Minnesota $18,936 $48,127 $78,073 $117,959 $250,361 $441,274
Nevada $15,024 $40,468 $66,247 $101,591 $224,480 $410,161
Alaska $19,344 $51,030 $81,169 $122,652 $242,097 $394,694
Montana $14,811 $36,927 $60,644 $93,340 $205,462 $370,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234
Puerto Rico $2,906 $12,144 $22,163 $38,397 $99,043 $187,234

Source: US Census Bureau, American Community Survey

Add Borders and Lines

awesometable <- vgreattable |>
  tab_options(
    table.border.top.color = "black", 
    table.border.bottom.color = "black",
    heading.border.bottom.color = "black", 
    column_labels.border.bottom.color = "black", 
    table_body.border.bottom.color = "black"
  )

awesometable

Export Table

gtsave(awesometable, "awesometable.png")