Mazda 3 Price Data - Tabulation

Tabulation of car price data.

This notebook looks at methods of tabulating the data in the car price data dataset (see Mazda 3 Price Data - Preparation). The aim is to look at ways of presenting simple one or two dimensional summary tables with as little code as possible. All of the manipulation of data will be done using functions from the dplyr or tidyr packages, both of which are part of the tidyverse umbrella package.

I feel sure that there should be a simple R package for creating simple tables, with aggregated values, from a dataframe. But, at the time of writing, I could not find one that was simple or mature enough to persuade me to use it. Hopefully that will change soon.

In the meantime, I demonstrate one technique that uses the tidyverse packages to build a two-dimensional table that handles the calculation and display of aggregated data and includes the correct calculation of aggregate values for row, column and grand totals.

Note

In this notebook where we want to talk about the rows or columns of a displayed table we will use the terms row dimension and column dimension. Using dimension, rather than variable or column, will help make it clear that we are talking about the horizontal or vertical dimension of a displayed table rather than a property of a dataframe or underlying model.

The technique for creating a table is to build a dataframe that holds the entire table structure and then to display it using the appropriate mechanism. Output in this notebook is by means of the knitr::kable function.

We start by reading in the cars tibble that we created and saved.

library(tidyverse)
cars <- readRDS("../01.preparation/mazda3.rds")

For the purposes of this notebook we ensure that we have a value of monthly depreciation for all vehicles by setting it to 250 (a high value) for all vehicles for which we do not already have a value.

cars <-
  cars |>
  mutate(depreciation_per_month = ifelse(new_used == "New", 250, depreciation_per_month))

Suppose that we would like to create a simple ordered, one-dimensional listing that shows the count and median monthly depreciation for each combination of variant, and engine.

We choose the columns that we are interested in using the select function.

Then we use group_by followed by summarise to reduce the data. Our output will contain a row for each combination of the columns named in the call to group_by - these columns will be shown in the output.

In addition, any values that we define in the call to summarize will also show up in the output in columns in the listing immediately following the group-by columns. Their values will be calculated for each of the groups - each of the combinations of the group-by columns. The aggregating functions can make use of any of the columns that we have selected from the dataframe. The special function n() returns the current group size.

The arrange function orders the output into ascending or descending order based on the values of one or more columns.

cars |>
  select(depreciation_per_month, variant, engine) |>
  group_by(variant, engine) |>
  summarise(n=n(), median_dpm = median(depreciation_per_month)) |>
  arrange(median_dpm) |>
  knitr::kable(digits=0)

variant	engine	n	median_dpm
SE-L	e-Skyactiv G	41	161
SE-L Lux	e-Skyactiv G	51	188
Sport Lux	e-Skyactiv G	114	197
GT Sport	e-Skyactiv G	53	214
SE-L Lux	e-Skyactiv X	6	222
GT Sport	e-Skyactiv X	28	233
Sport Lux	e-Skyactiv X	29	239
GT Sport Tech	e-Skyactiv X	15	249
GT Sport Tech	e-Skyactiv G	42	250

If we want to create a two-dimensional table we need to use the pivot_wider function to declare which column of the dataframe will appear at the table column dimension and what values will be displayed in the cells of the table.

cars |>
  select(variant, engine, dpm=depreciation_per_month) |>
  group_by(variant, engine) |>
  summarise( dpm = median(dpm)) |>
  pivot_wider(names_from=engine, values_from=dpm) |>
  knitr::kable(digits=0)

variant	e-Skyactiv G	e-Skyactiv X
SE-L	161	NA
SE-L Lux	188	222
Sport Lux	197	239
GT Sport	214	233
GT Sport Tech	250	249

We have seen that without using pivot_wider we get a listing output that includes our two group-by columns (variant and engine). We use the names_from argument to pivot_wider to declare the dataframe column that will provide the values for the column dimension of our table. We use the values_from argument to pivot_wider to declare the dataframe column whose values will be displayed in the cells of the table. The remaining group-by column will provide the values for the row dimension of the table.

cars |>
  select(variant, engine, new_used, dpm=depreciation_per_month) |>
  group_by(variant, engine, new_used) |>
  summarise( dpm = median(dpm)) |>
  pivot_wider(names_from=engine, values_from=dpm) |>
  knitr::kable(digits=0)

variant	new_used	e-Skyactiv G	e-Skyactiv X
SE-L	Used	161	NA
SE-L Lux	Used	187	213
SE-L Lux	New	250	250
Sport Lux	Used	194	229
Sport Lux	New	250	250
GT Sport	Used	207	233
GT Sport	New	250	250
GT Sport Tech	Used	223	249
GT Sport Tech	New	250	NA

In the above table we have reduced the dataframe to a variant value on the rows, an engine name on the columns and the median monthly depreciation values for each combination of row value and column value in each of the cells.

In general, the procedure is to group the data by the two variables that are to make up the row and column values. Then, summarise the group by the statistic that you want to display in the table cells.

Specify the variable that you want to use to supply the column values of the table in the names_from argument to pivot_wider and the summary statistic that you want to use to supply the cell values of the table in the values_from argument to pivot_wider.

By default, missing values will be shown in the table as NA. If you want them to be replaced by an alternative value then the values_fill argument can supply the replacement.

If you need to represent more than two dimensions in the table you can combine two into the columns by providing them both to names_from but this only really works if the number of values are relatively small and the length of the values of the columns are also relatively short.

cars |>
  select(variant, engine, new_used, dpm=depreciation_per_month) |>
  group_by(variant, engine, new_used) |>
  summarise(n = n()) |>
  pivot_wider(
    names_from=c(engine,new_used), values_from=n, values_fill=0
  ) |>
  knitr::kable(digits=0)

variant	e-Skyactiv G_Used	e-Skyactiv G_New	e-Skyactiv X_Used	e-Skyactiv X_New
SE-L	41	0	0	0
SE-L Lux	50	1	5	1
Sport Lux	109	5	26	3
GT Sport	50	3	27	1
GT Sport Tech	39	3	15	0

A better solution is to use the longer variables as table rows and use the smallest and shortest variable as the table columns.

cars |>
  select(variant, engine, new_used, dpm=depreciation_per_month) |>
  group_by(variant, engine, new_used) |>
  summarise(n = n()) |>
  pivot_wider(names_from=new_used, values_from=n, values_fill=0) |>
  knitr::kable(digits=0)

variant	engine	Used	New
SE-L	e-Skyactiv G	41	0
SE-L Lux	e-Skyactiv G	50	1
SE-L Lux	e-Skyactiv X	5	1
Sport Lux	e-Skyactiv G	109	5
Sport Lux	e-Skyactiv X	26	3
GT Sport	e-Skyactiv G	50	3
GT Sport	e-Skyactiv X	27	1
GT Sport Tech	e-Skyactiv G	39	3
GT Sport Tech	e-Skyactiv X	15	0

If none of the arrangements looks good then revert to using the one-dimensional listing format with one or more summarised values that we started this notebook with.

A simple form of table caption can be provided in the call to knitr::kable.

cars |>
  select(variant, engine, new_used, dpm=depreciation_per_month) |>
  group_by(engine, new_used) |>
  summarise(n = n()) |>
  pivot_wider(names_from=new_used, values_from=n, values_fill=0) |>
  knitr::kable(digits=0, caption="A Count of New/Used by Engine")

A Count of New/Used by Engine

engine	Used	New
e-Skyactiv G	289	12
e-Skyactiv X	73	5

Row Totals - Counts

The mechanism can be extended to include row totals. The technique is to create a dataframe for the body of the table and another dataframe for the rowtotal and then to combine them with bind_rows.

Note

The terminology of row-totals and column-totals can be a little confusing. In this notebook a row-total is a row that appears beneath the main body of a table and a column-total is a column that appears to the right of the main body of a table.

body <- cars |>
  select(engine, new_used) |>
  group_by(engine, new_used) |>
  summarise(n = n()) |>
  pivot_wider(names_from=new_used, values_from=n, values_fill=0) 

rowtotals <- body |>
  group_by() |>
  summarise(
    across(where(is.factor), ~"Total"),
    across(where(is.numeric), sum)
  ) 
bind_rows(body, rowtotals) |>
knitr::kable(digits=0)

engine	Used	New
e-Skyactiv G	289	12
e-Skyactiv X	73	5
Total	362	17

This is fine for simple additive counts, but for aggregates we need something better.

Row Totals - Aggregates

The problem with tables containing aggregates is that the aggregate of a column of aggregates is not the same as the aggregate you would obtain had you not previously grouped on the rows. An easy way to see this is to imagine dividing a collection of values up into two unequally sized groups. You can calculate the mean of each group but the mean of the whole collection is not equal in value to the mean of the two group means (it would be if the two groups were equally sized).

To calculate aggregates correctly for row totals we need to start with the cars dataframe and perform a different grouping for each kind of aggregate. Instead of grouping and summarising the body dataframe as we did above, we obtain row totals by grouping the cars dataframe across the table columns. This allows us to calculate an aggregate value for each group (table column) and our rowtotal is constructed as a single row dataframe that contains an aggregate value in each column and a blank value or text label in any column for which no aggregate can be calculated. We populate these blank or label columns using the mutate function.

body <- cars |>
  select(engine, new_used, dpm=depreciation_per_month) |>
  group_by(engine, new_used) |>
  summarise(dpm = median(dpm, na.rm=T)) |>
  pivot_wider(names_from=new_used, values_from=dpm, values_fill=0) 

rowtotals <- cars |>
  select(new_used, dpm=depreciation_per_month) |>
  group_by(new_used) |>
  summarise(dpm = median(dpm, na.rm=T)) |>
  pivot_wider(names_from=new_used, values_from=dpm, values_fill=0) |>
  mutate(engine = "Median")

bind_rows(body, rowtotals) |>
knitr::kable(digits=0)

engine	Used	New
e-Skyactiv G	197	250
e-Skyactiv X	233	250
Median	202	250

You will notice that the aggregate value for the Used column is no longer the sum of the numbers above it - it is not even the mean or median of them. It is the median of the values of the dataframe Used column but not grouped by engine.

Row, Column and Grand Totals - Aggregates

To create a table with the full set of totals we reduce (group-by and summarise) the cars dataframe to build each of the totals, so that we handle aggregates correctly, and then we stitch them together into a table.

body <- cars |>
  select(engine, new_used, dpm=depreciation_per_month) |>
  group_by(engine, new_used) |>
  summarise(dpm = median(dpm, na.rm=T)) |>
  pivot_wider(names_from=new_used, values_from=dpm, values_fill=0) 

rowtotals <- cars |>
  select(new_used, dpm=depreciation_per_month) |>
  group_by(new_used) |>
  summarise(dpm = median(dpm, na.rm=T)) |>
  pivot_wider(names_from=new_used, values_from=dpm, values_fill=0) |>
  mutate(engine = "Median")

coltotals <- cars |>
  select(engine, dpm=depreciation_per_month) |>
  group_by(engine) |>
  summarise(Median = median(dpm, na.rm=T))

grandtotal <- cars |>
  select(dpm=depreciation_per_month) |>
  summarise(engine="Median", Median = median(dpm, na.rm=T)) 

all <-inner_join(
  bind_rows(body, rowtotals),
  bind_rows(coltotals, grandtotal)
)
knitr::kable(all, digits=0)

engine	Used	New	Median
e-Skyactiv G	197	250	197
e-Skyactiv X	233	250	238
Median	202	250	202

This seems like quite a lot of work for a 3-row by 4-column table but it is quite flexible. If you can see from the above code fragment how the table is constructed there is no need to read the next section which simply shows a breakdown of each stage of the table construction.

Assembly of the Complete Table - Bit by Bit

We begin by creating the body of the table.

Show the code

body <- cars |>
  select(engine, new_used, dpm=depreciation_per_month) |>
  group_by(engine, new_used) |>
  summarise(dpm = median(dpm, na.rm=T)) |>
  pivot_wider(names_from=new_used, values_from=dpm, values_fill=0) 
knitr::kable(body, digits=0)

engine	Used	New
e-Skyactiv G	197	250
e-Skyactiv X	233	250

Then we create the row totals as a single row dataframe with a value for each table column. It does not matter that the engine column appears as the last column in the dataframe.

Show the code

rowtotals <- cars |>
  select(new_used, dpm=depreciation_per_month) |>
  group_by(new_used) |>
  summarise(dpm = median(dpm, na.rm=T)) |>
  pivot_wider(names_from=new_used, values_from=dpm, values_fill=0) |>
  mutate(engine = "Median")
knitr::kable(rowtotals, digits=0)

Used	New	engine
202	250	Median

We create the column totals as a dataframe with a row for each row in the main body of the table. Each row contains a field (or fields) that allows us to find the table row with which to associate it, and a value to use as the column total.

Show the code

coltotals <- cars |>
  select(engine, dpm=depreciation_per_month) |>
  group_by(engine) |>
  summarise(Median = median(dpm, na.rm=T))
knitr::kable(coltotals, digits=0)

engine	Median
e-Skyactiv G	197
e-Skyactiv X	238

The grand total is a dataframe with a single row and a column that will link it to the rowtotal row of the table and a value to use as the grand total.

Show the code

grandtotal <- cars |>
  select(dpm=depreciation_per_month) |>
  summarise(engine="Median", Median = median(dpm, na.rm=T)) 
knitr::kable(grandtotal, digits=0)

engine	Median
Median	202

We bind the body and the rowtotals together into a single dataframe. The rowtotals appear as the final row of the dataframe. The values in the engine column are going to be used to position the column totals and the grand total correctly later.

Show the code

bind_rows(body, rowtotals) |>
knitr::kable(digits=0)

engine	Used	New
e-Skyactiv G	197	250
e-Skyactiv X	233	250
Median	202	250

So we now have the main body of the table and the row totals in a single dataframe. Next we need to create a similar dataframe containing the column totals and the grand total.

We bind the coltotals and the grandtotal together into a single dataframe.

Show the code

bind_rows(coltotals, grandtotal) |>
knitr::kable(digits=0)

engine	Median
e-Skyactiv G	197
e-Skyactiv X	238
Median	202

Finally, we glue the two dataframes together by joining them on their common engine column. This is the column that contains values of the row dimension of the table.

The join ensures that the columns from the body and rowtotals appear before the columns from the coltotals and grandtotal. So the effect that we get is that the Median column appears as the last table column.

Show the code

all <-inner_join(
  bind_rows(body, rowtotals),
  bind_rows(coltotals, grandtotal)
)
knitr::kable(all, digits=0)

engine	Used	New	Median
e-Skyactiv G	197	250	197
e-Skyactiv X	233	250	238
Median	202	250	202

And that is our complete table.

The deconstruction makes is sound like more work than it really is.