Apply and Practice Activity

ARCOS: Learn to use the gt package to create a table of CO opioid data

Introduction

For this activity you will work with the WAPO compiled data repository on opioid pills that you studied in the Module 4 Handout on Data Wrangling. We will work with the county level data for Colorado. Each county in the US has up to nine rows of data in this data frame, one for each year from 2006 to 2014. The table below describes the variables that we will utilize:

Variable Description
fips A 5 digit code that identifies each county
county County name
state State abbreviation
year Corresponding year for data
number_pills The number of Oxycodone & Hydrocodone pills purchased by pharmacies in the county
population The total number of people living in the county

Step by step directions

Step 1

Let’s get started. Navigate to the Posit Cloud and enter the foundations project.

Navigate to the apply_and_practice folder in the programs folder of the foundations project. Open up the file called arcos_table.qmd.

To ensure you are working in a fresh session, close any other open tabs (save them if needed). Click the down arrow beside the Run button toward the top of your screen then click Restart R and Clear Output.

Once the .qmd file is open, add your name to the author section of the YAML metadata.

Step 2

First we need to load the packages that are needed for this activity. There are two new packages that we will explore here: gt and gtExtras. These two packages allow us to create useful, flexible and beautiful tables of data in R.

Find the code chunk labeled: Load packages in the .qmd file. Then load these packages:

library(gt)
library(gtExtras)
library(here)   
library(tidyverse)

Once entered, click run on the Load packages code chunk. Now, the packages are ready for you to use.

Step 3

Under the first level header

# Import data

Import the opioid counties data frame that we worked with in Module 4 (it’s in the data folder of the course project — and called opioid_counties.Rds). Call the data frame co, and filter it to include only data for CO. You should have 526 observations once filtered.

Step 4

Under the first level header

# Prepare the data

Create a new version of the co data frame, call it co_wide. In a pipe do the following:

  1. Create a variable called pills_per_capita which is calculated as the number of pills divided by the population size.

  2. Select the variables fips, county, year, and pills_per_capita.

  3. Pivot the data from long to wide using the pivot_wider() function. Use the names prefix: “pills_per_capita_in_”. Your resulting data frame should have one row of data for each of the 61 counties in CO, and 10 variables (the county name and 9 variables to represent pills per capita (one for each year from 2006-2014).

Step 5

Under the first level header

# Create a table for CO counties

Create a basic table. Copy and paste the code below to do so, and run the code chunk to view the result.

co_wide |> 
  gt()
fips county pills_per_capita_in_2006 pills_per_capita_in_2007 pills_per_capita_in_2008 pills_per_capita_in_2009 pills_per_capita_in_2010 pills_per_capita_in_2011 pills_per_capita_in_2012 pills_per_capita_in_2013 pills_per_capita_in_2014
08001 ADAMS 19.17808522 20.88308727 23.41608988 26.42413319 28.71118896 31.031304 30.8073527 26.8825742 27.87428666
08003 ALAMOSA 63.62595420 72.38932806 78.98169935 89.79026900 90.33871706 105.703150 115.2546032 100.2177870 80.22220843
08005 ARAPAHOE 20.47032367 22.84911602 24.56720565 26.93638417 27.58157942 28.954789 29.0301676 27.2742490 27.22745205
08007 ARCHULETA 13.49585323 14.89969010 15.47755102 18.37218814 18.41793013 19.404213 22.1322983 23.9739037 25.44510386
08009 BACA 17.64556318 20.56389033 20.31003678 16.84429426 21.34098617 26.845285 21.6494845 23.1161925 27.28246319
08011 BENT 14.20492349 19.02012248 22.92854747 23.64556136 27.78122449 35.772226 37.5484496 30.1253867 28.51666667
08013 BOULDER 20.70401030 23.41570759 25.72198213 25.36802426 27.73863538 28.371549 27.4387285 25.2613661 23.28868550
08014 BROOMFIELD 23.89505223 26.48837480 27.70162276 36.74028970 43.95105160 60.444754 64.2026005 61.8177048 56.16345062
08015 CHAFFEE 36.25563294 38.26091961 42.99550447 45.43094250 48.09407070 53.699102 54.5953353 50.2148037 47.68335081
08017 CHEYENNE 15.05753138 16.36070853 20.25518341 25.89395808 28.44120328 25.233645 24.8353222 25.1256281 29.59591268
08019 CLEAR CREEK 17.98274718 23.35008741 24.23943970 26.15349989 29.46742958 28.551139 18.3165912 14.9535809 14.85626509
08021 CONEJOS 33.27277132 34.54423756 31.91463415 31.08091260 30.91240876 35.452115 42.7812159 41.1700581 27.98453359
08025 CROWLEY 24.75365274 26.81629260 27.25744073 27.34907305 35.42479227 35.356290 37.0280146 35.6368804 30.46059734
08027 CUSTER 0.22533801 0.24319066 NA 0.05558644 0.12823801 0.259538 0.2614068 0.4475854 0.17313992
08029 DELTA 35.97368156 42.91690183 50.05730659 49.57104734 45.99941047 48.748451 49.1383914 42.4142992 43.58614787
08031 DENVER 13.89459593 16.03725759 17.33534125 18.99888745 20.69347693 22.279617 22.6510418 20.5489499 19.45092675
08035 DOUGLAS 16.15717822 18.15362101 19.25473684 21.46338295 22.03324312 22.545618 23.1072598 21.4243688 20.17853926
08037 EAGLE 20.43225514 22.14390699 24.38702031 27.10149366 28.62362924 29.050353 30.5237030 27.7631110 27.95742155
08041 EL PASO 22.63232980 25.46928251 27.53450885 30.67860468 32.08055828 34.539237 34.9975803 32.8719797 32.61158699
08039 ELBERT 8.58047540 9.55718239 9.61385526 10.91898534 14.83488905 16.069382 16.6016133 15.5741575 17.09067743
08043 FREMONT 30.65090777 34.84017973 42.49671373 44.76019665 43.46520100 48.190901 49.1477684 45.6326574 42.73491329
08045 GARFIELD 29.08361437 34.01767101 35.03796281 36.41764061 35.79350268 37.177805 36.2675306 32.1579904 32.13711100
08047 GILPIN 6.77657178 6.98957695 4.49661759 6.81862082 8.21303160 15.073459 11.5154909 4.1446047 1.69156019
08049 GRAND 18.48989502 19.98182581 41.05731090 26.20369004 24.46647391 25.755091 26.4717963 24.0454076 20.87995579
08051 GUNNISON 15.29755435 15.98916440 18.13573770 20.43487001 21.63385307 22.928506 23.9792005 21.7862006 19.92904599
08053 HINSDALE 0.49079755 0.81437126 1.80929095 3.06859206 2.65848671 1.778386 0.8860759 0.8652658 1.14810563
08055 HUERFANO 41.61638838 37.90242519 44.23158490 45.80578245 55.91537133 64.387172 64.4041605 56.9056604 43.11603053
08059 JEFFERSON 30.76396594 34.91007047 37.59499474 38.67976750 40.01293571 42.599065 44.1925778 41.0469992 39.48797243
08061 KIOWA 23.21428571 33.75886525 39.58333333 37.16545012 40.17041996 42.245370 44.7954056 47.6458187 53.09168443
08063 KIT CARSON 12.84483835 14.98617048 16.97928160 18.55797820 17.94997548 16.434336 18.6358100 17.2182196 16.79389313
08067 LA PLATA 23.62794911 25.44685163 24.30703307 25.11524216 25.36919978 27.487997 28.6858270 26.0099156 25.40449502
08065 LAKE 23.12448475 24.02597403 23.34228188 24.18691589 30.71459014 41.069900 36.9370591 30.6737589 27.19190261
08069 LARIMER 24.67531963 27.73466335 29.76008915 31.42446459 33.41167460 36.079053 36.1891515 33.9584628 33.59840737
08071 LAS ANIMAS 54.16321062 37.91979153 43.95053900 45.56089541 50.09250399 51.351855 55.9610010 62.4207188 73.82040706
08073 LINCOLN 27.73960217 32.18453683 39.59400146 45.37369915 50.45653762 45.386305 46.6103252 48.5667034 38.86854632
08075 LOGAN 26.69891835 29.09127826 28.64741504 34.29998080 31.89693868 32.007405 36.2007009 34.4782589 36.27278358
08077 MESA 30.79116326 35.28703186 37.01428522 38.89598851 38.09774817 40.743027 43.4102632 39.2495523 38.13746958
08079 MINERAL NA NA NA NA 0.09803922 NA NA NA NA
08081 MOFFAT 26.19138391 30.27344921 33.17114464 42.70158613 49.04208891 55.820698 53.6532074 44.9245185 45.18590998
08083 MONTEZUMA 30.95962832 34.72592121 39.47149314 39.90800000 42.99220697 45.026013 47.1306639 43.4336783 45.13277797
08085 MONTROSE 22.89352698 28.85337635 30.87313690 35.16092919 35.85431878 39.937028 40.4571401 39.3400780 38.41506665
08087 MORGAN 21.50776531 23.91421878 26.87628605 29.85740138 33.23886640 37.361414 37.6297872 35.5305921 35.73071766
08089 OTERO 42.49343556 48.68263856 56.30118552 63.80650985 76.81359533 87.562118 92.6102922 68.9429771 59.28548525
08091 OURAY 23.61858191 19.78823529 21.22587968 22.79265324 25.27436907 29.718600 30.7716535 31.1284916 26.13195057
08093 PARK 6.35243376 5.98966251 6.88666585 6.49273290 8.23529412 8.598315 8.3003464 7.1136321 3.99344627
08095 PHILLIPS 12.48300861 10.09593422 10.46217521 10.80598348 13.65498407 20.951947 18.9497717 27.1047228 22.74344356
08097 PITKIN 15.33797730 14.32597698 13.89631700 15.65103827 13.11611447 13.447244 13.2641176 13.1730041 12.85268451
08099 PROWERS 30.09900990 35.50251256 37.28974177 42.76086627 47.24359981 49.782006 54.9445729 45.8935300 44.12169269
08101 PUEBLO 45.35013748 52.45389083 59.88459183 69.31131106 74.61496121 86.142954 89.8095831 72.9599870 66.80010824
08103 RIO BLANCO 22.34455959 18.99105602 19.24256363 10.57738962 20.37265168 26.254534 31.3168847 30.3988183 19.83973883
08105 RIO GRANDE 24.56494640 19.36382045 22.59106239 26.98288342 29.80379004 35.322757 47.3526205 46.6633090 45.52533784
08107 ROUTT 27.34068347 28.74038504 27.47784742 28.98935409 29.88793404 30.084996 28.7385076 27.1352898 25.50153662
08109 SAGUACHE 0.09641652 0.01626545 0.01618385 0.24534565 0.09738679 0.081103 0.1131039 0.2903226 0.06440187
08111 SAN JUAN 0.12326656 NA NA NA NA NA NA NA NA
08113 SAN MIGUEL 9.51774855 9.80606392 10.26337225 9.97968856 11.79613646 10.998239 10.7508073 10.6243330 10.18296696
08115 SEDGWICK 24.29264349 26.83333333 31.14415531 32.84272498 37.28441128 53.179916 60.4730013 50.0420875 41.73361522
08117 SUMMIT 23.46035589 26.62143367 25.74049514 27.25615332 26.78823095 28.081721 27.9180629 24.5689011 24.16965101
08119 TELLER 26.69090256 30.80107456 38.12101210 46.34337014 50.30629683 60.905578 69.0294867 61.3876955 58.44711724
08121 WASHINGTON 8.92602405 8.17120623 8.45158598 NA NA NA NA NA NA
08123 WELD 21.57509872 23.75780422 26.62625595 29.61587092 32.20172116 35.521110 36.1798369 33.0527475 30.55042895
08125 YUMA 16.14881439 18.51246424 20.82698364 23.55347871 22.51414713 26.421687 27.5618022 30.0208065 30.70378048

The function gt() converts the data frame into a gt table object. This creates the general structure of the table — but it’s not particularly well-formatted. Let’s take a few steps to enhance it.

Step 6

We will create a spanning column that indicates that the 9 rows of per capita data refer to Pills per Capita for years 2006-2014. We’ll also provide informative labels for the columns rather than relying on the variable names, add a title, and add a theme.

Here’s a full explanation of the additional code:

  1. Adding a Column Spanner (tab_spanner):

    • tab_spanner(label = "Opioid Pills Per Capita from 2006-2014", columns = starts_with("pills_per_capita_in_")): This code adds a spanner (a label that spans multiple columns) to the table.

      • label: This sets the text for the spanner, which is “Opioid Pills Per Capita from 2006-2014”.

      • columns: This specifies which columns the spanner should cover. The starts_with("pills_per_capita_in_") selects all columns that start with the string “pills_per_capita_in_”, which corresponds to the columns for each year from 2006 to 2014.

  2. Renaming Columns (cols_label):

    • cols_label(...): This code is used to rename the columns in the table for better readability.

      • county = "County": Renames the county column to “County”.

      • pills_per_capita_in_2006 = "2006": Renames the pills_per_capita_in_2006 column to “2006”. And, the same treatment is applied to the other years.

  3. Add a title and subtitle:

    • The tab_header() function is used to add a title and subtitle to the table.

  4. Add a theme:

    • The gtExtra package has several built in themes. You can check them out here. To demonstrate, here we apply the FiveThirtyEight theme via the function gt_theme_538().

Update your code in the # Create a table for CO counties code chunk with the code below, and then run the code chunk to view the results.

co_wide |> 
  select(county, starts_with("pills_per_capita_in")) |> 
  gt() |> 
  tab_spanner(
    label = "Opioid Pills Per Capita from 2006-2014",
    columns = starts_with("pills_per_capita_in_")
  ) |> 
  cols_label(
    county = "County",
    pills_per_capita_in_2006 = "2006",
    pills_per_capita_in_2007 = "2007",
    pills_per_capita_in_2008 = "2008",
    pills_per_capita_in_2009 = "2009",
    pills_per_capita_in_2010 = "2010",
    pills_per_capita_in_2011 = "2011",
    pills_per_capita_in_2012 = "2012",
    pills_per_capita_in_2013 = "2013",
    pills_per_capita_in_2014 = "2014"
  ) |> 
  tab_header(
    title = "How deeply did Opioid pills flood Colorado counties",
    subtitle = "Data from the Washington Post (https://wpinvestigative.github.io/arcos/)"
  ) |> 
   gt_theme_538()
How deeply did Opioid pills flood Colorado counties
Data from the Washington Post (https://wpinvestigative.github.io/arcos/)
County
Opioid Pills Per Capita from 2006-2014
2006 2007 2008 2009 2010 2011 2012 2013 2014
ADAMS 19.17808522 20.88308727 23.41608988 26.42413319 28.71118896 31.031304 30.8073527 26.8825742 27.87428666
ALAMOSA 63.62595420 72.38932806 78.98169935 89.79026900 90.33871706 105.703150 115.2546032 100.2177870 80.22220843
ARAPAHOE 20.47032367 22.84911602 24.56720565 26.93638417 27.58157942 28.954789 29.0301676 27.2742490 27.22745205
ARCHULETA 13.49585323 14.89969010 15.47755102 18.37218814 18.41793013 19.404213 22.1322983 23.9739037 25.44510386
BACA 17.64556318 20.56389033 20.31003678 16.84429426 21.34098617 26.845285 21.6494845 23.1161925 27.28246319
BENT 14.20492349 19.02012248 22.92854747 23.64556136 27.78122449 35.772226 37.5484496 30.1253867 28.51666667
BOULDER 20.70401030 23.41570759 25.72198213 25.36802426 27.73863538 28.371549 27.4387285 25.2613661 23.28868550
BROOMFIELD 23.89505223 26.48837480 27.70162276 36.74028970 43.95105160 60.444754 64.2026005 61.8177048 56.16345062
CHAFFEE 36.25563294 38.26091961 42.99550447 45.43094250 48.09407070 53.699102 54.5953353 50.2148037 47.68335081
CHEYENNE 15.05753138 16.36070853 20.25518341 25.89395808 28.44120328 25.233645 24.8353222 25.1256281 29.59591268
CLEAR CREEK 17.98274718 23.35008741 24.23943970 26.15349989 29.46742958 28.551139 18.3165912 14.9535809 14.85626509
CONEJOS 33.27277132 34.54423756 31.91463415 31.08091260 30.91240876 35.452115 42.7812159 41.1700581 27.98453359
CROWLEY 24.75365274 26.81629260 27.25744073 27.34907305 35.42479227 35.356290 37.0280146 35.6368804 30.46059734
CUSTER 0.22533801 0.24319066 NA 0.05558644 0.12823801 0.259538 0.2614068 0.4475854 0.17313992
DELTA 35.97368156 42.91690183 50.05730659 49.57104734 45.99941047 48.748451 49.1383914 42.4142992 43.58614787
DENVER 13.89459593 16.03725759 17.33534125 18.99888745 20.69347693 22.279617 22.6510418 20.5489499 19.45092675
DOUGLAS 16.15717822 18.15362101 19.25473684 21.46338295 22.03324312 22.545618 23.1072598 21.4243688 20.17853926
EAGLE 20.43225514 22.14390699 24.38702031 27.10149366 28.62362924 29.050353 30.5237030 27.7631110 27.95742155
EL PASO 22.63232980 25.46928251 27.53450885 30.67860468 32.08055828 34.539237 34.9975803 32.8719797 32.61158699
ELBERT 8.58047540 9.55718239 9.61385526 10.91898534 14.83488905 16.069382 16.6016133 15.5741575 17.09067743
FREMONT 30.65090777 34.84017973 42.49671373 44.76019665 43.46520100 48.190901 49.1477684 45.6326574 42.73491329
GARFIELD 29.08361437 34.01767101 35.03796281 36.41764061 35.79350268 37.177805 36.2675306 32.1579904 32.13711100
GILPIN 6.77657178 6.98957695 4.49661759 6.81862082 8.21303160 15.073459 11.5154909 4.1446047 1.69156019
GRAND 18.48989502 19.98182581 41.05731090 26.20369004 24.46647391 25.755091 26.4717963 24.0454076 20.87995579
GUNNISON 15.29755435 15.98916440 18.13573770 20.43487001 21.63385307 22.928506 23.9792005 21.7862006 19.92904599
HINSDALE 0.49079755 0.81437126 1.80929095 3.06859206 2.65848671 1.778386 0.8860759 0.8652658 1.14810563
HUERFANO 41.61638838 37.90242519 44.23158490 45.80578245 55.91537133 64.387172 64.4041605 56.9056604 43.11603053
JEFFERSON 30.76396594 34.91007047 37.59499474 38.67976750 40.01293571 42.599065 44.1925778 41.0469992 39.48797243
KIOWA 23.21428571 33.75886525 39.58333333 37.16545012 40.17041996 42.245370 44.7954056 47.6458187 53.09168443
KIT CARSON 12.84483835 14.98617048 16.97928160 18.55797820 17.94997548 16.434336 18.6358100 17.2182196 16.79389313
LA PLATA 23.62794911 25.44685163 24.30703307 25.11524216 25.36919978 27.487997 28.6858270 26.0099156 25.40449502
LAKE 23.12448475 24.02597403 23.34228188 24.18691589 30.71459014 41.069900 36.9370591 30.6737589 27.19190261
LARIMER 24.67531963 27.73466335 29.76008915 31.42446459 33.41167460 36.079053 36.1891515 33.9584628 33.59840737
LAS ANIMAS 54.16321062 37.91979153 43.95053900 45.56089541 50.09250399 51.351855 55.9610010 62.4207188 73.82040706
LINCOLN 27.73960217 32.18453683 39.59400146 45.37369915 50.45653762 45.386305 46.6103252 48.5667034 38.86854632
LOGAN 26.69891835 29.09127826 28.64741504 34.29998080 31.89693868 32.007405 36.2007009 34.4782589 36.27278358
MESA 30.79116326 35.28703186 37.01428522 38.89598851 38.09774817 40.743027 43.4102632 39.2495523 38.13746958
MINERAL NA NA NA NA 0.09803922 NA NA NA NA
MOFFAT 26.19138391 30.27344921 33.17114464 42.70158613 49.04208891 55.820698 53.6532074 44.9245185 45.18590998
MONTEZUMA 30.95962832 34.72592121 39.47149314 39.90800000 42.99220697 45.026013 47.1306639 43.4336783 45.13277797
MONTROSE 22.89352698 28.85337635 30.87313690 35.16092919 35.85431878 39.937028 40.4571401 39.3400780 38.41506665
MORGAN 21.50776531 23.91421878 26.87628605 29.85740138 33.23886640 37.361414 37.6297872 35.5305921 35.73071766
OTERO 42.49343556 48.68263856 56.30118552 63.80650985 76.81359533 87.562118 92.6102922 68.9429771 59.28548525
OURAY 23.61858191 19.78823529 21.22587968 22.79265324 25.27436907 29.718600 30.7716535 31.1284916 26.13195057
PARK 6.35243376 5.98966251 6.88666585 6.49273290 8.23529412 8.598315 8.3003464 7.1136321 3.99344627
PHILLIPS 12.48300861 10.09593422 10.46217521 10.80598348 13.65498407 20.951947 18.9497717 27.1047228 22.74344356
PITKIN 15.33797730 14.32597698 13.89631700 15.65103827 13.11611447 13.447244 13.2641176 13.1730041 12.85268451
PROWERS 30.09900990 35.50251256 37.28974177 42.76086627 47.24359981 49.782006 54.9445729 45.8935300 44.12169269
PUEBLO 45.35013748 52.45389083 59.88459183 69.31131106 74.61496121 86.142954 89.8095831 72.9599870 66.80010824
RIO BLANCO 22.34455959 18.99105602 19.24256363 10.57738962 20.37265168 26.254534 31.3168847 30.3988183 19.83973883
RIO GRANDE 24.56494640 19.36382045 22.59106239 26.98288342 29.80379004 35.322757 47.3526205 46.6633090 45.52533784
ROUTT 27.34068347 28.74038504 27.47784742 28.98935409 29.88793404 30.084996 28.7385076 27.1352898 25.50153662
SAGUACHE 0.09641652 0.01626545 0.01618385 0.24534565 0.09738679 0.081103 0.1131039 0.2903226 0.06440187
SAN JUAN 0.12326656 NA NA NA NA NA NA NA NA
SAN MIGUEL 9.51774855 9.80606392 10.26337225 9.97968856 11.79613646 10.998239 10.7508073 10.6243330 10.18296696
SEDGWICK 24.29264349 26.83333333 31.14415531 32.84272498 37.28441128 53.179916 60.4730013 50.0420875 41.73361522
SUMMIT 23.46035589 26.62143367 25.74049514 27.25615332 26.78823095 28.081721 27.9180629 24.5689011 24.16965101
TELLER 26.69090256 30.80107456 38.12101210 46.34337014 50.30629683 60.905578 69.0294867 61.3876955 58.44711724
WASHINGTON 8.92602405 8.17120623 8.45158598 NA NA NA NA NA NA
WELD 21.57509872 23.75780422 26.62625595 29.61587092 32.20172116 35.521110 36.1798369 33.0527475 30.55042895
YUMA 16.14881439 18.51246424 20.82698364 23.55347871 22.51414713 26.421687 27.5618022 30.0208065 30.70378048

Step 7

Notice that the values for pills per capita have too many decimal places displayed. Displaying one decimal place is sufficient for clarity and readability. Let’s update our code to format these values to show only one digit after the decimal.

Copy and paste this updated code in the code chunk under # Create a table for CO counties, which uses the fmt_number() function from gt to format numbers in a table. The arguments to the function are simply the columns that we want to affect, and the number of decimal places desired for the printing of the number.

Study the output, and then write a few sentences to describe what you see and your thoughts.

co_wide |> 
  select(county, starts_with("pills_per_capita_in")) |> 
  gt() |> 
  tab_spanner(
    label = "Opioid Pills Per Capita from 2006-2014",
    columns = starts_with("pills_per_capita_in_")
  ) |> 
  cols_label(
    county = "County",
    pills_per_capita_in_2006 = "2006",
    pills_per_capita_in_2007 = "2007",
    pills_per_capita_in_2008 = "2008",
    pills_per_capita_in_2009 = "2009",
    pills_per_capita_in_2010 = "2010",
    pills_per_capita_in_2011 = "2011",
    pills_per_capita_in_2012 = "2012",
    pills_per_capita_in_2013 = "2013",
    pills_per_capita_in_2014 = "2014"
  ) |> 
  tab_header(
    title = "How deeply did Opioid pills flood Colorado counties",
    subtitle = "Data from the Washington Post (https://wpinvestigative.github.io/arcos/)"
  ) |>
  gt_theme_538() |> 
  fmt_number(
    columns = starts_with("pills_per_capita_in_"),
    decimals = 1)
How deeply did Opioid pills flood Colorado counties
Data from the Washington Post (https://wpinvestigative.github.io/arcos/)
County
Opioid Pills Per Capita from 2006-2014
2006 2007 2008 2009 2010 2011 2012 2013 2014
ADAMS 19.2 20.9 23.4 26.4 28.7 31.0 30.8 26.9 27.9
ALAMOSA 63.6 72.4 79.0 89.8 90.3 105.7 115.3 100.2 80.2
ARAPAHOE 20.5 22.8 24.6 26.9 27.6 29.0 29.0 27.3 27.2
ARCHULETA 13.5 14.9 15.5 18.4 18.4 19.4 22.1 24.0 25.4
BACA 17.6 20.6 20.3 16.8 21.3 26.8 21.6 23.1 27.3
BENT 14.2 19.0 22.9 23.6 27.8 35.8 37.5 30.1 28.5
BOULDER 20.7 23.4 25.7 25.4 27.7 28.4 27.4 25.3 23.3
BROOMFIELD 23.9 26.5 27.7 36.7 44.0 60.4 64.2 61.8 56.2
CHAFFEE 36.3 38.3 43.0 45.4 48.1 53.7 54.6 50.2 47.7
CHEYENNE 15.1 16.4 20.3 25.9 28.4 25.2 24.8 25.1 29.6
CLEAR CREEK 18.0 23.4 24.2 26.2 29.5 28.6 18.3 15.0 14.9
CONEJOS 33.3 34.5 31.9 31.1 30.9 35.5 42.8 41.2 28.0
CROWLEY 24.8 26.8 27.3 27.3 35.4 35.4 37.0 35.6 30.5
CUSTER 0.2 0.2 NA 0.1 0.1 0.3 0.3 0.4 0.2
DELTA 36.0 42.9 50.1 49.6 46.0 48.7 49.1 42.4 43.6
DENVER 13.9 16.0 17.3 19.0 20.7 22.3 22.7 20.5 19.5
DOUGLAS 16.2 18.2 19.3 21.5 22.0 22.5 23.1 21.4 20.2
EAGLE 20.4 22.1 24.4 27.1 28.6 29.1 30.5 27.8 28.0
EL PASO 22.6 25.5 27.5 30.7 32.1 34.5 35.0 32.9 32.6
ELBERT 8.6 9.6 9.6 10.9 14.8 16.1 16.6 15.6 17.1
FREMONT 30.7 34.8 42.5 44.8 43.5 48.2 49.1 45.6 42.7
GARFIELD 29.1 34.0 35.0 36.4 35.8 37.2 36.3 32.2 32.1
GILPIN 6.8 7.0 4.5 6.8 8.2 15.1 11.5 4.1 1.7
GRAND 18.5 20.0 41.1 26.2 24.5 25.8 26.5 24.0 20.9
GUNNISON 15.3 16.0 18.1 20.4 21.6 22.9 24.0 21.8 19.9
HINSDALE 0.5 0.8 1.8 3.1 2.7 1.8 0.9 0.9 1.1
HUERFANO 41.6 37.9 44.2 45.8 55.9 64.4 64.4 56.9 43.1
JEFFERSON 30.8 34.9 37.6 38.7 40.0 42.6 44.2 41.0 39.5
KIOWA 23.2 33.8 39.6 37.2 40.2 42.2 44.8 47.6 53.1
KIT CARSON 12.8 15.0 17.0 18.6 17.9 16.4 18.6 17.2 16.8
LA PLATA 23.6 25.4 24.3 25.1 25.4 27.5 28.7 26.0 25.4
LAKE 23.1 24.0 23.3 24.2 30.7 41.1 36.9 30.7 27.2
LARIMER 24.7 27.7 29.8 31.4 33.4 36.1 36.2 34.0 33.6
LAS ANIMAS 54.2 37.9 44.0 45.6 50.1 51.4 56.0 62.4 73.8
LINCOLN 27.7 32.2 39.6 45.4 50.5 45.4 46.6 48.6 38.9
LOGAN 26.7 29.1 28.6 34.3 31.9 32.0 36.2 34.5 36.3
MESA 30.8 35.3 37.0 38.9 38.1 40.7 43.4 39.2 38.1
MINERAL NA NA NA NA 0.1 NA NA NA NA
MOFFAT 26.2 30.3 33.2 42.7 49.0 55.8 53.7 44.9 45.2
MONTEZUMA 31.0 34.7 39.5 39.9 43.0 45.0 47.1 43.4 45.1
MONTROSE 22.9 28.9 30.9 35.2 35.9 39.9 40.5 39.3 38.4
MORGAN 21.5 23.9 26.9 29.9 33.2 37.4 37.6 35.5 35.7
OTERO 42.5 48.7 56.3 63.8 76.8 87.6 92.6 68.9 59.3
OURAY 23.6 19.8 21.2 22.8 25.3 29.7 30.8 31.1 26.1
PARK 6.4 6.0 6.9 6.5 8.2 8.6 8.3 7.1 4.0
PHILLIPS 12.5 10.1 10.5 10.8 13.7 21.0 18.9 27.1 22.7
PITKIN 15.3 14.3 13.9 15.7 13.1 13.4 13.3 13.2 12.9
PROWERS 30.1 35.5 37.3 42.8 47.2 49.8 54.9 45.9 44.1
PUEBLO 45.4 52.5 59.9 69.3 74.6 86.1 89.8 73.0 66.8
RIO BLANCO 22.3 19.0 19.2 10.6 20.4 26.3 31.3 30.4 19.8
RIO GRANDE 24.6 19.4 22.6 27.0 29.8 35.3 47.4 46.7 45.5
ROUTT 27.3 28.7 27.5 29.0 29.9 30.1 28.7 27.1 25.5
SAGUACHE 0.1 0.0 0.0 0.2 0.1 0.1 0.1 0.3 0.1
SAN JUAN 0.1 NA NA NA NA NA NA NA NA
SAN MIGUEL 9.5 9.8 10.3 10.0 11.8 11.0 10.8 10.6 10.2
SEDGWICK 24.3 26.8 31.1 32.8 37.3 53.2 60.5 50.0 41.7
SUMMIT 23.5 26.6 25.7 27.3 26.8 28.1 27.9 24.6 24.2
TELLER 26.7 30.8 38.1 46.3 50.3 60.9 69.0 61.4 58.4
WASHINGTON 8.9 8.2 8.5 NA NA NA NA NA NA
WELD 21.6 23.8 26.6 29.6 32.2 35.5 36.2 33.1 30.6
YUMA 16.1 18.5 20.8 23.6 22.5 26.4 27.6 30.0 30.7

Step 8

This table is quite nice, but we can enhance it with one bonus step. Let’s examine how to add a sparkline. A sparkline is a small, simple chart or graph embedded within text or a table, designed to give a quick visual representation of data trends or variations. They are often used to show trends over time or to display small sets of data, such as monthly sales figures, daily temperatures, or other similar data frames. Sparklines are particularly useful because they can provide a visual summary of data without taking up much space. Here we’ll add a sparkline that shows the change in opioid pills per capita from 2006 to 2014 using a line graph. I will mark the highest score for the county with a red dot, and I will also provide shading so that the range in opioid pills per capita from the bottom of the y-axis to the sparkline for the county is easily observed.

For this table, we will exclude counties with missing data.

There are two new elements to the code:

First, we need to create a new column in this data frame that holds all these yearly values together in a compact form for each county. Essentially, we need to create a small time series for each county within a single column.

Here’s how we can achieve that:

  1. Start with the Original Data (co_wide):

  2. Set Up for Row-wise Operations:

    • Use rowwise() to tell R that we want to perform operations on each row (each county) individually rather than on the entire data frame at once.

  3. Create a New Column with Time Series Data:

    • Within mutate(), we can create a new column called pills_per_capita_sparkline.

    • For each row (county), use c_across(starts_with("pills_per_capita_in_")) to gather all columns that start with “pills_per_capita_in_”. This function collects all the yearly values of pills per capita for that county.

    • Wrap these collected values in list(), which stores them as a list within each cell of the new column. Now, each cell in the pills_per_capita_sparkline column contains a small list of values representing the pills per capita over the years for that county.

  4. Return to Regular Data Frame:

    • Use ungroup() to revert the data frame back to its normal state after the row-wise operation.

Second, we need to add the sparkline to the table using the gt_plt_sparkline() function.

Let’s break down the arguments to this function, full details for using this function are included here.

  1. column = pills_per_capita_sparkline specifies the column in the gt table where the sparklines should be added. pills_per_capita_sparkline is expected to be a list column where each cell contains a vector of numerical values representing the sparkline data (this is what we created in the first new element for this Step).

  2. type = "shaded" defines the type of sparkline to be displayed. "shaded" indicates that the area under the line of the sparkline will be shaded.

  3. fig_dim = c(25, 25) specifies the dimensions of the sparkline figures. c(25, 25) sets the width and height of each sparkline to 25 pixels.

  4. palette = c("black", "transparent", "transparent", "#EC2049", "#D3D3D3") defines the color palette for the sparkline. This should be a character string with 5 elements indicating the colors of various components. Order matters: palette = sparkline color, final value color, range color low, range color high, and ‘type’ color (e.g., shading or reference lines).

    • Sparkline color ("black"): This color is used to set the color of the line.
    • Final value color ("transparent"): By specifying "transparent", no final value is printed.
    • Range color low ("transparent"): This color is used for the lowest value point in the series. By specifying "transparent", this point is not visually highlighted.
    • Range color high ("#EC2049"): This color is used for the highest value point in the series — red is chosen here.
    • Type color ("#D3D3D3): This color is used for special features like shading or reference lines — grey is chosen here.

  5. same_limit = TRUE indicates whether the same y-axis limits should be applied to all sparklines in the column.

  6. label = FALSE specifies whether to include labels on the sparklines. FALSE means no labels will be added to the sparklines, keeping them simple and uncluttered. If label were set to TRUE, labels would be printed next to the sparklines, showing the final value of the series.

Find the second level header titled ## Add a sparkline and copy and paste the code below into the code chunk. Press play on the code chunk to view the graph. Study the output, and then write a few sentences to describe what you see and your thoughts.

co_wide_sparkline <- 
  co_wide |>
  rowwise() |>
  mutate(
    pills_per_capita_sparkline = list(c_across(starts_with("pills_per_capita_in_")))) |>
  ungroup() 

# Create the gt table
co_wide_sparkline |>
  select(county, starts_with("pills_per_capita")) |>
  drop_na() |> 
  gt() |>
  tab_spanner(
    label = "Opioid Pills Per Capita from 2006-2014",
    columns = starts_with("pills_per_capita_in_")
  ) |>
  cols_label(
    county = "County",
    pills_per_capita_in_2006 = "2006",
    pills_per_capita_in_2007 = "2007",
    pills_per_capita_in_2008 = "2008",
    pills_per_capita_in_2009 = "2009",
    pills_per_capita_in_2010 = "2010",
    pills_per_capita_in_2011 = "2011",
    pills_per_capita_in_2012 = "2012",
    pills_per_capita_in_2013 = "2013",
    pills_per_capita_in_2014 = "2014",
    pills_per_capita_sparkline = "Sparkline"
  ) |>
  gt_theme_538() |> 
  fmt_number(
    columns = starts_with("pills_per_capita_in_"),
    decimals = 1
  ) |>
  gt_plt_sparkline(
    column = pills_per_capita_sparkline,
    type = "shaded",
    fig_dim = c(25, 25),
    palette = c("black", "transparent", "transparent", "#EC2049", "#D3D3D3"),
    same_limit = TRUE,
    label = FALSE
  )
County
Opioid Pills Per Capita from 2006-2014
Sparkline
2006 2007 2008 2009 2010 2011 2012 2013 2014
ADAMS 19.2 20.9 23.4 26.4 28.7 31.0 30.8 26.9 27.9
ALAMOSA 63.6 72.4 79.0 89.8 90.3 105.7 115.3 100.2 80.2
ARAPAHOE 20.5 22.8 24.6 26.9 27.6 29.0 29.0 27.3 27.2
ARCHULETA 13.5 14.9 15.5 18.4 18.4 19.4 22.1 24.0 25.4
BACA 17.6 20.6 20.3 16.8 21.3 26.8 21.6 23.1 27.3
BENT 14.2 19.0 22.9 23.6 27.8 35.8 37.5 30.1 28.5
BOULDER 20.7 23.4 25.7 25.4 27.7 28.4 27.4 25.3 23.3
BROOMFIELD 23.9 26.5 27.7 36.7 44.0 60.4 64.2 61.8 56.2
CHAFFEE 36.3 38.3 43.0 45.4 48.1 53.7 54.6 50.2 47.7
CHEYENNE 15.1 16.4 20.3 25.9 28.4 25.2 24.8 25.1 29.6
CLEAR CREEK 18.0 23.4 24.2 26.2 29.5 28.6 18.3 15.0 14.9
CONEJOS 33.3 34.5 31.9 31.1 30.9 35.5 42.8 41.2 28.0
CROWLEY 24.8 26.8 27.3 27.3 35.4 35.4 37.0 35.6 30.5
DELTA 36.0 42.9 50.1 49.6 46.0 48.7 49.1 42.4 43.6
DENVER 13.9 16.0 17.3 19.0 20.7 22.3 22.7 20.5 19.5
DOUGLAS 16.2 18.2 19.3 21.5 22.0 22.5 23.1 21.4 20.2
EAGLE 20.4 22.1 24.4 27.1 28.6 29.1 30.5 27.8 28.0
EL PASO 22.6 25.5 27.5 30.7 32.1 34.5 35.0 32.9 32.6
ELBERT 8.6 9.6 9.6 10.9 14.8 16.1 16.6 15.6 17.1
FREMONT 30.7 34.8 42.5 44.8 43.5 48.2 49.1 45.6 42.7
GARFIELD 29.1 34.0 35.0 36.4 35.8 37.2 36.3 32.2 32.1
GILPIN 6.8 7.0 4.5 6.8 8.2 15.1 11.5 4.1 1.7
GRAND 18.5 20.0 41.1 26.2 24.5 25.8 26.5 24.0 20.9
GUNNISON 15.3 16.0 18.1 20.4 21.6 22.9 24.0 21.8 19.9
HINSDALE 0.5 0.8 1.8 3.1 2.7 1.8 0.9 0.9 1.1
HUERFANO 41.6 37.9 44.2 45.8 55.9 64.4 64.4 56.9 43.1
JEFFERSON 30.8 34.9 37.6 38.7 40.0 42.6 44.2 41.0 39.5
KIOWA 23.2 33.8 39.6 37.2 40.2 42.2 44.8 47.6 53.1
KIT CARSON 12.8 15.0 17.0 18.6 17.9 16.4 18.6 17.2 16.8
LA PLATA 23.6 25.4 24.3 25.1 25.4 27.5 28.7 26.0 25.4
LAKE 23.1 24.0 23.3 24.2 30.7 41.1 36.9 30.7 27.2
LARIMER 24.7 27.7 29.8 31.4 33.4 36.1 36.2 34.0 33.6
LAS ANIMAS 54.2 37.9 44.0 45.6 50.1 51.4 56.0 62.4 73.8
LINCOLN 27.7 32.2 39.6 45.4 50.5 45.4 46.6 48.6 38.9
LOGAN 26.7 29.1 28.6 34.3 31.9 32.0 36.2 34.5 36.3
MESA 30.8 35.3 37.0 38.9 38.1 40.7 43.4 39.2 38.1
MOFFAT 26.2 30.3 33.2 42.7 49.0 55.8 53.7 44.9 45.2
MONTEZUMA 31.0 34.7 39.5 39.9 43.0 45.0 47.1 43.4 45.1
MONTROSE 22.9 28.9 30.9 35.2 35.9 39.9 40.5 39.3 38.4
MORGAN 21.5 23.9 26.9 29.9 33.2 37.4 37.6 35.5 35.7
OTERO 42.5 48.7 56.3 63.8 76.8 87.6 92.6 68.9 59.3
OURAY 23.6 19.8 21.2 22.8 25.3 29.7 30.8 31.1 26.1
PARK 6.4 6.0 6.9 6.5 8.2 8.6 8.3 7.1 4.0
PHILLIPS 12.5 10.1 10.5 10.8 13.7 21.0 18.9 27.1 22.7
PITKIN 15.3 14.3 13.9 15.7 13.1 13.4 13.3 13.2 12.9
PROWERS 30.1 35.5 37.3 42.8 47.2 49.8 54.9 45.9 44.1
PUEBLO 45.4 52.5 59.9 69.3 74.6 86.1 89.8 73.0 66.8
RIO BLANCO 22.3 19.0 19.2 10.6 20.4 26.3 31.3 30.4 19.8
RIO GRANDE 24.6 19.4 22.6 27.0 29.8 35.3 47.4 46.7 45.5
ROUTT 27.3 28.7 27.5 29.0 29.9 30.1 28.7 27.1 25.5
SAGUACHE 0.1 0.0 0.0 0.2 0.1 0.1 0.1 0.3 0.1
SAN MIGUEL 9.5 9.8 10.3 10.0 11.8 11.0 10.8 10.6 10.2
SEDGWICK 24.3 26.8 31.1 32.8 37.3 53.2 60.5 50.0 41.7
SUMMIT 23.5 26.6 25.7 27.3 26.8 28.1 27.9 24.6 24.2
TELLER 26.7 30.8 38.1 46.3 50.3 60.9 69.0 61.4 58.4
WELD 21.6 23.8 26.6 29.6 32.2 35.5 36.2 33.1 30.6
YUMA 16.1 18.5 20.8 23.6 22.5 26.4 27.6 30.0 30.7

Step 9

Now that you’ve completed all tasks, to help ensure reproducibility, click the down arrow beside the Run button toward the top of your screen then click Restart R and Clear Output. Scroll through your notebook and see that all of the output is now gone. Now, click the down arrow beside the Run button again, then click Restart R and Run All Chunks. Scroll through the file and make sure that everything ran as you would expect. You will find a red bar on the side of a code chunk if an error has occurred. Taking this step ensures that all code chunks are running from top to bottom, in the intended sequence, and producing output that will be reproduced the next time you work on this project.

Now that all code chunks are working as you’d like, click Render. This will create an .html output of your report. Scroll through to make sure everything is correct. The .html output file will be saved along side the corresponding .qmd notebook file.

Step 10

Follow the directions on Canvas for the Apply and Practice Assignment entitled “ARCOS gt Table Apply and Practice Activity” to get credit for completing this assignment.