The first time that I knitted an R file with a table on it (as a printed table) was years ago, but I still remember not entirely liking the look of it. The main reason was it displayed several tables and they were really ugly - so much so that I felt it almost undermined the credibility of the analysis. Because of that, I wanted to create as professional looking a table as possible.
Data for this table was collected from the following source:
library(readxl)
library(dplyr)
library(knitr)
library(kableExtra)
library(dplyr)commodityPrices <- read_excel('C:/Users/Jeffrey/Desktop/My Documents/Grad School/Classes/Fall 2022/Staa 566/HW4/Commodity Prices.xlsx')
commodityPrices <- data.frame(commodityPrices)The main goal for the funcationality and formatting of this table was to appear professional and be as useful as possible. To maintain the professional look,after trying to get it to work and make it look good, I decided against adding additional color. I also wanted to keep the index commodities and single commodities clearly separated. To do this I added a border between them, after grouping the indexes and the individual commodity prices together. I felt separating the date with a border also improved the table.
While maintaining the professional look, I still wanted to add some functionality you could only get with a computer. The most needed thing was a scroll bar, because the data was so long. I then made it so the row the mouse is over highlighted gray. Furthermore I added code to create popovers for the index columns so that users could read the full name of the index that was being shown.
df <- commodityPrices %>%
select(Date, BeveragePriceIndex, IndustrialInputsPriceIndex, AgriculturalRawMaterialsIndex, MetalsPriceIndex, Beef, Coal, Wheat) %>%
kbl(
caption = "Commodity Prices Over Time",
col.names = c("Date", "Bvrg.", "Ind.", "Agr.", "Metals", "Beef", "Coal", "Wheat")
, centering = TRUE
, align = c("l","c","c","c","c","c","c","c")
, digits = 2
) %>%
add_header_above(c(" ", "Indexes" = 4, "Specific Commodities" = 3)) %>%
kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive")) %>%
column_spec(1, border_right = TRUE) %>%
column_spec(2, popover = "Beverage Price") %>%
column_spec(3, popover = "IndustrialI nputs Price") %>%
column_spec(4, popover = "Agricultural Raw Materials") %>%
column_spec(5, popover = "Metals Price") %>%
column_spec(5, border_right = TRUE) %>%
scroll_box(width = "700px", height = "300px", fixed_thead = T)
df | Date | Bvrg. | Ind. | Agr. | Metals | Beef | Coal | Wheat |
|---|---|---|---|---|---|---|---|
| 2015-12-01 | 172.47 | 106.89 | 109.42 | 105.06 | 167.11 | 55.85 | 163.79 |
| 2015-11-01 | 174.19 | 109.30 | 109.51 | 109.14 | 177.07 | 56.33 | 157.74 |
| 2015-10-01 | 174.34 | 115.85 | 112.92 | 117.97 | 184.55 | 56.05 | 165.39 |
| 2015-09-01 | 172.94 | 117.10 | 112.28 | 120.58 | 202.41 | 58.66 | 163.83 |
| 2015-08-01 | 175.30 | 116.01 | 111.59 | 119.20 | 212.00 | 62.76 | 173.47 |
| 2015-07-01 | 179.42 | 121.08 | 118.07 | 123.25 | 204.41 | 63.35 | 199.20 |
| 2015-06-01 | 176.93 | 129.77 | 125.06 | 133.17 | 195.05 | 63.04 | 199.82 |
| 2015-05-01 | 169.15 | 133.85 | 125.93 | 139.57 | 199.21 | 64.71 | 193.15 |
| 2015-04-01 | 165.17 | 130.88 | 126.85 | 133.79 | 214.43 | 61.94 | 195.90 |
| 2015-03-01 | 165.14 | 131.75 | 127.81 | 134.60 | 207.80 | 64.41 | 202.68 |
| 2015-02-01 | 173.34 | 134.51 | 130.65 | 137.29 | 209.88 | 65.79 | 201.71 |
| 2015-01-01 | 172.65 | 136.11 | 130.28 | 140.32 | 232.02 | 66.54 | 210.61 |
| 2014-12-01 | 174.37 | 141.64 | 131.87 | 148.70 | 239.59 | 66.90 | 232.97 |
| 2014-11-01 | 177.73 | 147.32 | 134.71 | 156.42 | 261.50 | 67.02 | 215.95 |
| 2014-10-01 | 187.78 | 148.12 | 136.02 | 156.86 | 266.93 | 68.26 | 212.78 |
| 2014-09-01 | 183.83 | 150.97 | 136.42 | 161.47 | 272.30 | 70.65 | 211.16 |
| 2014-08-01 | 185.70 | 155.10 | 136.95 | 168.21 | 258.86 | 73.86 | 229.10 |
| 2014-07-01 | 180.25 | 156.60 | 139.77 | 168.76 | 227.50 | 73.66 | 238.53 |
| 2014-06-01 | 178.33 | 152.85 | 140.39 | 161.85 | 200.79 | 76.59 | 261.59 |
| 2014-05-01 | 179.99 | 155.08 | 142.00 | 164.52 | 194.55 | 78.95 | 287.85 |
| 2014-04-01 | 184.58 | 158.48 | 143.26 | 169.48 | 191.23 | 78.02 | 273.47 |
| 2014-03-01 | 182.98 | 156.46 | 144.65 | 164.99 | 197.76 | 78.58 | 275.57 |
| 2014-02-01 | 169.40 | 158.64 | 140.21 | 171.96 | 190.25 | 81.74 | 242.76 |
| 2014-01-01 | 151.29 | 160.80 | 139.20 | 176.40 | 187.46 | 87.44 | 228.21 |
| 2013-12-01 | 149.86 | 163.69 | 142.31 | 179.13 | 185.95 | 90.36 | 243.84 |
| 2013-11-01 | 143.19 | 161.94 | 139.80 | 177.93 | 183.31 | 88.12 | 259.10 |
| 2013-10-01 | 144.60 | 161.27 | 136.91 | 178.87 | 178.07 | 85.08 | 271.99 |
| 2013-09-01 | 144.64 | 159.54 | 134.45 | 177.66 | 175.60 | 83.16 | 259.74 |
| 2013-08-01 | 145.84 | 161.17 | 133.99 | 180.79 | 176.58 | 82.46 | 258.79 |
| 2013-07-01 | 143.51 | 157.53 | 136.59 | 172.66 | 176.58 | 82.78 | 257.36 |
| 2013-06-01 | 141.46 | 157.66 | 141.04 | 169.66 | 174.25 | 88.67 | 266.80 |
| 2013-05-01 | 150.05 | 159.89 | 137.04 | 176.40 | 179.59 | 93.97 | 277.06 |
| 2013-04-01 | 149.36 | 162.28 | 132.83 | 183.55 | 191.68 | 94.03 | 264.04 |
| 2013-03-01 | 149.92 | 166.25 | 132.48 | 190.64 | 191.79 | 97.48 | 263.21 |
| 2013-02-01 | 151.73 | 175.03 | 133.27 | 205.19 | 194.28 | 101.72 | 279.02 |
| 2013-01-01 | 155.01 | 173.55 | 133.68 | 202.34 | 195.39 | 99.40 | 288.07 |
| 2012-12-01 | 157.51 | 167.55 | 132.68 | 192.74 | 195.62 | 99.51 | 302.85 |
| 2012-11-01 | 161.71 | 160.82 | 131.33 | 182.12 | 192.02 | 92.03 | 319.40 |
| 2012-10-01 | 166.67 | 161.91 | 132.15 | 183.40 | 181.54 | 87.70 | 318.38 |
| 2012-09-01 | 171.90 | 160.17 | 132.84 | 179.92 | 180.25 | 95.31 | 316.57 |
| 2012-08-01 | 168.69 | 154.97 | 130.70 | 172.49 | 183.13 | 97.50 | 315.38 |
| 2012-07-01 | 168.18 | 161.72 | 132.06 | 183.15 | 180.36 | 94.54 | 309.73 |
| 2012-06-01 | 160.38 | 163.36 | 132.45 | 185.69 | 183.60 | 93.42 | 241.91 |
| 2012-05-01 | 161.87 | 170.00 | 137.70 | 193.34 | 187.30 | 102.67 | 231.14 |
| 2012-04-01 | 165.98 | 176.75 | 139.74 | 203.49 | 192.10 | 110.99 | 228.56 |
| 2012-03-01 | 171.01 | 177.95 | 137.83 | 206.92 | 194.36 | 115.14 | 243.49 |
| 2012-02-01 | 176.30 | 177.72 | 137.05 | 207.10 | 194.10 | 125.38 | 244.96 |
| 2012-01-01 | 178.25 | 172.61 | 131.86 | 202.04 | 190.93 | 124.78 | 241.07 |
| 2011-12-01 | 177.49 | 165.88 | 129.57 | 192.11 | 190.09 | 119.53 | 238.02 |
| 2011-11-01 | 186.39 | 168.64 | 134.54 | 193.28 | 186.68 | 121.91 | 241.30 |
| 2011-10-01 | 189.95 | 175.55 | 140.45 | 200.90 | 174.28 | 127.92 | 251.59 |
| 2011-09-01 | 204.16 | 192.74 | 149.32 | 224.10 | 175.93 | 131.88 | 275.64 |
| 2011-08-01 | 209.55 | 198.52 | 151.03 | 232.83 | 180.85 | 128.71 | 290.29 |
| 2011-07-01 | 209.96 | 207.14 | 158.56 | 242.23 | 177.76 | 129.38 | 271.46 |
| 2011-06-01 | 208.74 | 204.43 | 161.12 | 235.71 | 178.36 | 128.67 | 285.27 |
| 2011-05-01 | 214.91 | 206.69 | 161.31 | 239.46 | 184.11 | 127.63 | 300.71 |
| 2011-04-01 | 216.63 | 217.07 | 171.37 | 250.08 | 193.00 | 131.25 | 308.85 |
| 2011-03-01 | 221.99 | 213.00 | 169.80 | 244.21 | 187.72 | 135.14 | 288.59 |
| 2011-02-01 | 221.05 | 215.54 | 159.20 | 256.24 | 183.72 | 137.53 | 311.03 |
| 2011-01-01 | 205.72 | 207.86 | 155.78 | 245.48 | 185.62 | 141.94 | 297.11 |
| 2010-12-01 | 192.58 | 197.11 | 146.61 | 233.58 | 173.03 | 126.74 | 265.90 |
| 2010-11-01 | 185.24 | 188.28 | 140.70 | 222.65 | 156.50 | 114.81 | 241.75 |
| 2010-10-01 | 180.71 | 181.35 | 132.99 | 216.28 | 154.75 | 104.41 | 234.47 |
| 2010-09-01 | 179.75 | 170.02 | 125.43 | 202.24 | 152.50 | 101.68 | 239.18 |
| 2010-08-01 | 181.08 | 166.37 | 122.54 | 198.02 | 152.50 | 96.19 | 219.82 |
| 2010-07-01 | 179.12 | 154.84 | 120.88 | 179.37 | 145.39 | 102.84 | 182.65 |
| 2010-06-01 | 173.92 | 157.61 | 122.64 | 182.88 | 144.81 | 105.20 | 149.20 |
| 2010-05-01 | 167.00 | 166.36 | 121.68 | 198.64 | 157.75 | 107.28 | 158.68 |
| 2010-04-01 | 169.63 | 179.73 | 122.91 | 220.78 | 164.75 | 107.30 | 158.06 |
| 2010-03-01 | 164.30 | 164.85 | 117.59 | 198.98 | 152.10 | 101.12 | 156.31 |
| 2010-02-01 | 167.48 | 154.50 | 114.72 | 183.23 | 141.75 | 100.92 | 161.53 |
| 2010-01-01 | 173.97 | 158.23 | 112.60 | 191.18 | 133.88 | 103.93 | 166.47 |
The main goal of the display is to simply display data in professional looking manner. To make a table that would build confidence in the reader’s mind that what data is being displayed is trustworthy and accurate. Beyond this, I wanted to be able to display lots of price numbers, in a condensed space, but still make the table pleasing to look at and most importantly still useful.