Creating Tables With The gt( ) Package

This lesson will walk you through how to create tables using the gt() package in R. The gt() package is a powerful tool for creating and customizing tables in R. It allows you to create tables that are easy to read and visually appealing. It is a part of the tidyverse collection of packages.

First, make sure that the tidyverse package is downloaded and installed. If needed, you can install it using the following command:

install.packages("tidyverse")
library(tidyverse)

Get the Data

Next, we will read in a dataset to work with. For this example, we will use a bikeshare dataset. This is a data set from Kaggle that contains information about the bike share industry.

You can download the dataset from here. Note that you will have to have a Kaggle account to download the data.

After we put the CSV file in the appropriate directory, we can read in the data using the read_csv() function from the readr package, which is part of the tidyverse.

df <- read_csv("capitalbikeshare-complete.csv")

According to the documentation, The dataset is inspired by the past Bike Sharing Demand Competition. The data is hourly from 2018.1.1 to 2021.08.31. Data in 2020 April is missing since Capital Bikeshare does not provide the information on its website.

datetime: Date and hour of the bike share data
count: Count of total rentals
holiday: Whether it is a holiday
workingday: Whether it is a working day (Mon-Fri except holiday)
temp: Temperature in Celsius
feels_like: “Feels like” Temperature in Celsius
temp_min: Lowest temperature
temp_max: Highest temperature
pressure: Air pressure
humidity: Relative humidity
wind_speed: Wind speed
wind_deg: Wind direction degree
rain_1h: Precipitation in the last 1 hour (missing when it did not rain)
snow_1h: Snow volume for the last 1 hour (missing when it did not snow)
cloud_all: Cloudiness in percentage
weather_main: Weather types (Rain, Snow, Extreme etc.)

Let’s check the structure of the data using the head() function to get a quick look at the data to make sure it matches our expectations.

head(df)

# A tibble: 6 × 16
  datetime            count holiday workingday  temp feels_like temp_min
  <dttm>              <dbl>   <dbl>      <dbl> <dbl>      <dbl>    <dbl>
1 2018-01-01 00:00:00    34       1          0 -7.17      -12.7    -8.56
2 2018-01-01 01:00:00    49       1          0 -7.35      -13.8    -9.03
3 2018-01-01 02:00:00    37       1          0 -7.88      -14.0    -9.03
4 2018-01-01 03:00:00     9       1          0 -8.1       -14.3    -9.36
5 2018-01-01 04:00:00    12       1          0 -8.19      -14.4    -9.46
6 2018-01-01 05:00:00     8       1          0 -8.24      -14.5    -9.56
# ℹ 9 more variables: temp_max <dbl>, pressure <dbl>, humidity <dbl>,
#   wind_speed <dbl>, wind_deg <dbl>, rain_1h <dbl>, snow_1h <dbl>,
#   clouds_all <dbl>, weather_main <chr>

If we want a better look at the structure of the data, we can use the glimpse() function.

glimpse(df)

Rows: 33,379
Columns: 16
$ datetime     <dttm> 2018-01-01 00:00:00, 2018-01-01 01:00:00, 2018-01-01 02:…
$ count        <dbl> 34, 49, 37, 9, 12, 8, 9, 11, 19, 31, 61, 97, 104, 116, 11…
$ holiday      <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
$ workingday   <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
$ temp         <dbl> -7.17, -7.35, -7.88, -8.10, -8.19, -8.24, -8.91, -8.91, -…
$ feels_like   <dbl> -12.73, -13.81, -14.05, -14.32, -14.43, -14.50, -13.71, -…
$ temp_min     <dbl> -8.56, -9.03, -9.03, -9.36, -9.46, -9.56, -10.03, -10.03,…
$ temp_max     <dbl> -7.09, -7.15, -7.69, -7.89, -8.09, -8.15, -8.69, -8.69, -…
$ pressure     <dbl> 1030, 1030, 1031, 1031, 1031, 1031, 1031, 1031, 1032, 103…
$ humidity     <dbl> 53, 49, 52, 49, 49, 49, 52, 56, 56, 61, 61, 65, 58, 58, 5…
$ wind_speed   <dbl> 3.6, 4.6, 4.1, 4.1, 4.1, 4.1, 2.6, 2.6, 3.1, 3.1, 3.6, 3.…
$ wind_deg     <dbl> 310, 310, 310, 310, 330, 340, 310, 300, 300, 290, 320, 30…
$ rain_1h      <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N…
$ snow_1h      <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N…
$ clouds_all   <dbl> 20, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 20, 1, 1, 40, 75,…
$ weather_main <chr> "Clouds", "Clear", "Clear", "Clear", "Clear", "Clear", "C…

Create a basic table with gt( )

Now that we have a good understanding of the data, we can start creating tables using the gt() package.

Let us create a table that does the following :

Examine the average temperature and total rentals for each weather type found in the weather_main column.
Arrange the table in descending order based on total rentals.

Here are the steps we will use :

Store the result in the variable summary_table.
Group the data by weather_main using the group_by() function.
Summarise the data to calculate the average temperature (avg_temp) and total rentals (’total_rentals) using the summarise() function to calculate these for us.
Arrange the data in descending order based on total rentals using the arrange(desc( )) function.
Create the table by piping this data to the gt() function.
Create the table header using the tab_header() function. We will make a title and subtitle for the table.
Format the numbers in the table using the fmt_number() function. We will format the average temperature to 2 decimal places and the total rentals to 0 decimal places.
Label the columns we want to include by using the cols_label() function to give more descriptive names
Center align the data
Finally, we will display the table.

summary_table <- df %>%                             # Step 1
  group_by(weather_main) %>%                        # Step 2
  summarise(                                        # Step 3
    avg_temp = mean(temp, na.rm = TRUE),
    total_rentals = sum(count, na.rm = TRUE)
  ) %>%
  arrange(desc(total_rentals)) %>%                  # Step 4
  gt() %>%                                          # Step 5
  tab_header(                                       # Step 6
    title = "Bike Share Summary by Weather Type",
    subtitle = "Average Temperature and Total Rentals"
  ) %>%
  fmt_number(                                       # Step 7
    columns = c(avg_temp), 
    decimals = 2
  ) %>%
  fmt_number(
    columns = c(total_rentals), 
    decimals = 0,
    use_seps = TRUE            # This adds separators (commas) for thousands
  ) %>%
  cols_label(                                       # Step 8 
    weather_main = "Weather Type",
    avg_temp = "Average Temperature (°C)",
    total_rentals = "Total Rentals"
  ) %>%
  cols_align(                                       # Step 9
    align = "center",
    columns = c(weather_main, avg_temp, total_rentals) # Specify columns to center
  )

summary_table                                       # Step 10

Bike Share Summary by Weather Type
Average Temperature and Total Rentals
Weather Type	Average Temperature (°C)	Total Rentals
Clouds	15.97	8,115,545
Rain	16.44	1,575,784
Clear	10.85	807,465
Mist	10.16	371,598
Thunderstorm	25.53	75,673
Drizzle	9.89	64,638
Fog	10.74	35,567
Haze	16.49	30,622
Snow	−0.44	28,071
Smoke	19.57	14,898
Squall	29.64	12

Customize the table - Change Row Colors

If we want to get fancier, we can change the colors in the table. For example, we can highlight rows where the total rentals are between 30,000 and 40,000 by changing the background color of those rows. We can do this by using the tab_style() function along with the cell_fill() function

summary_table <- df %>%                             
  group_by(weather_main) %>%                       
  summarise(                                        
    avg_temp = mean(temp, na.rm = TRUE),
    total_rentals = sum(count, na.rm = TRUE)
  ) %>%
  arrange(desc(total_rentals)) %>%                  
  gt() %>%                                         
  tab_header(                                       
    title = "Bike Share Summary by Weather Type",
    subtitle = "Average Temperature and Total Rentals"
  ) %>%
  fmt_number(                                       
    columns = c(avg_temp), # Use `c(...)` instead of `vars(...)`
    decimals = 2
  ) %>%
  fmt_number(
    columns = c(total_rentals), # Use `c(...)` instead of `vars(...)`
    decimals = 0,
    use_seps = TRUE            # This adds separators (commas) for thousands
  ) %>%
  cols_label(                                     
    weather_main = "Weather Type",
    avg_temp = "Average Temperature (°C)",
    total_rentals = "Total Rentals"
  ) %>%
  cols_align(                                       
    align = "center",
    columns = c(weather_main, avg_temp, total_rentals) 
  ) %>%                                            
  tab_style(                                       
    style = cell_fill(color = "#F9E79F"),          # Specify a background color (light yellow)
    locations = cells_body(                        # Specify the rows
      rows = total_rentals >= 30000 & total_rentals <= 40000 
    )
  )

summary_table

Bike Share Summary by Weather Type
Average Temperature and Total Rentals
Weather Type	Average Temperature (°C)	Total Rentals
Clouds	15.97	8,115,545
Rain	16.44	1,575,784
Clear	10.85	807,465
Mist	10.16	371,598
Thunderstorm	25.53	75,673
Drizzle	9.89	64,638
Fog	10.74	35,567
Haze	16.49	30,622
Snow	−0.44	28,071
Smoke	19.57	14,898
Squall	29.64	12

Customize the table - Change Cell and Font Colors

If we want to highlight a certain cell by changing the color of the font or the background color, we could do this :

I want to highlight the average temperature for the weather type “Rain” by changing the background color to light blue and the font color to dark red and use bold text.

summary_table <- df %>%                             
  group_by(weather_main) %>%                        
  summarise(                                        
    avg_temp = mean(temp, na.rm = TRUE),
    total_rentals = sum(count, na.rm = TRUE)
  ) %>%
  arrange(desc(total_rentals)) %>%                  
  gt() %>%                                          
  tab_header(                                       
    title = "Bike Share Summary by Weather Type",
    subtitle = "Average Temperature and Total Rentals"
  ) %>%
  fmt_number(                                       
    columns = c(avg_temp), 
    decimals = 2
  ) %>%
  fmt_number(
    columns = c(total_rentals), 
    decimals = 0,
    use_seps = TRUE           
  ) %>%
  cols_label(                                      
    weather_main = "Weather Type",
    avg_temp = "Average Temperature (°C)",
    total_rentals = "Total Rentals"
  ) %>%
  cols_align(                                      
    align = "center",
    columns = c(weather_main, avg_temp, total_rentals) 
  ) %>%                                            
  tab_style(                                      
    style = list(                                   # Apply both font and background color
      cell_fill(color = "#D6EAF8"),                 # Light blue background color
      cell_text(color = "darkred", weight = "bold") # Dark red font and bold text
    ),
    locations = cells_body(
      columns = c(avg_temp),                      # Target a specific column (e.g., `avg_temp`)
      rows = weather_main == "Rain"               # Target a specific row (e.g., Weather Type "Rain")
    )
  )

summary_table

Bike Share Summary by Weather Type
Average Temperature and Total Rentals
Weather Type	Average Temperature (°C)	Total Rentals
Clouds	15.97	8,115,545
Rain	16.44	1,575,784
Clear	10.85	807,465
Mist	10.16	371,598
Thunderstorm	25.53	75,673
Drizzle	9.89	64,638
Fog	10.74	35,567
Haze	16.49	30,622
Snow	−0.44	28,071
Smoke	19.57	14,898
Squall	29.64	12

Customize the table - Add Borders

If we wanted to add a red border around a certain row, we could do this by using the tab_style() function along with the cell_borders() function. For example, we could add a red border around the row for the weather type “Snow”.

summary_table <- df %>%                             
  group_by(weather_main) %>%                        
  summarise(                                        
    avg_temp = mean(temp, na.rm = TRUE),
    total_rentals = sum(count, na.rm = TRUE)
  ) %>%
  arrange(desc(total_rentals)) %>%                  
  gt() %>%                                          
  tab_header(                                       
    title = "Bike Share Summary by Weather Type",
    subtitle = "Average Temperature and Total Rentals"
  ) %>%
  fmt_number(                                       
    columns = c(avg_temp), 
    decimals = 2
  ) %>%
  fmt_number(
    columns = c(total_rentals), 
    decimals = 0,
    use_seps = TRUE           
  ) %>%
  cols_label(                                      
    weather_main = "Weather Type",
    avg_temp = "Average Temperature (°C)",
    total_rentals = "Total Rentals"
  ) %>%
  cols_align(                                      
    align = "center",
    columns = c(weather_main, avg_temp, total_rentals) 
  ) %>%                                            
  tab_style(                                       
    style = cell_borders(
      sides = "all",                               # Apply border to all sides
      color = "red",                               # Border color
      weight = px(2)                              # Border thickness
    ),
    locations = cells_body(
      rows = weather_main == "Snow"               # Target the row for Weather Type "Snow"
    )
  )

summary_table

Bike Share Summary by Weather Type
Average Temperature and Total Rentals
Weather Type	Average Temperature (°C)	Total Rentals
Clouds	15.97	8,115,545
Rain	16.44	1,575,784
Clear	10.85	807,465
Mist	10.16	371,598
Thunderstorm	25.53	75,673
Drizzle	9.89	64,638
Fog	10.74	35,567
Haze	16.49	30,622
Snow	−0.44	28,071
Smoke	19.57	14,898
Squall	29.64	12

Customize the table - Change Cell Borders and Background Color

If we want to do something similar for a single cell, we could do this by specifying the column as well. For example, we could add a green border around the cell for the average temperature for the weather type “Clear”, as well as change the color of the cell to light yellow.

summary_table <- df %>%                             
  group_by(weather_main) %>%                        
  summarise(                                        
    avg_temp = mean(temp, na.rm = TRUE),
    total_rentals = sum(count, na.rm = TRUE)
  ) %>%
  arrange(desc(total_rentals)) %>%                  
  gt() %>%                                          
  tab_header(                                       
    title = "Bike Share Summary by Weather Type",
    subtitle = "Average Temperature and Total Rentals"
  ) %>%
  fmt_number(                                       
    columns = c(avg_temp), 
    decimals = 2
  ) %>%
  fmt_number(
    columns = c(total_rentals), 
    decimals = 0,
    use_seps = TRUE           
  ) %>%
  cols_label(                                      
    weather_main = "Weather Type",
    avg_temp = "Average Temperature (°C)",
    total_rentals = "Total Rentals"
  ) %>%
  cols_align(                                      
    align = "center",
    columns = c(weather_main, avg_temp, total_rentals) 
  ) %>%                                            
  tab_style(                                       
    style = list(
      cell_fill(color = "#F9E79F"),                 # Light yellow background color
      cell_borders(                                # Add border to the cell
        sides = "all",                             # Apply border to all sides
        color = "green",                           # Border color
        weight = px(2)                            # Border thickness
      )
    ),
    locations = cells_body(
      columns = c(avg_temp),                      # Target a specific column (e.g., `avg_temp`)
      rows = weather_main == "Clear"              # Target a specific row (e.g., Weather Type "Clear")
    )
  )

summary_table

Bike Share Summary by Weather Type
Average Temperature and Total Rentals
Weather Type	Average Temperature (°C)	Total Rentals
Clouds	15.97	8,115,545
Rain	16.44	1,575,784
Clear	10.85	807,465
Mist	10.16	371,598
Thunderstorm	25.53	75,673
Drizzle	9.89	64,638
Fog	10.74	35,567
Haze	16.49	30,622
Snow	−0.44	28,071
Smoke	19.57	14,898
Squall	29.64	12

You are now ready to create and customize tables using the gt() package in R! Here are some practice problems using this same data set.

Practice Problems

Create a table that shows the total rentals and average wind speed for each month. Arrange the table in descending order based on total rentals. Format the numbers appropriately and add a title and subtitle to the table.

Hint

You first need to extract the months. You can use this command to do this :

mutate(month = format(as.Date(datetime), "%B"))

Now that you have the month variable, you can use group_by() for the months.
Use the summarise command to calculate the sum of the total rentals (total_rentals) and the average wind speed (avg_wind_speed). Don’t forget to remove the NA values.
Use the arrange command to list the total rentals in a descending order.
Send this data to the gt( ) command via a pipe
Create a table header with a title and subtitle.
Format the numbers output for total_rentals (0 decimals) and average_wind_speed (2 decimals).
Create the three column labels “Month”, “Total Rentals”, and “Average Wind Speed (m/s)”.
Make the table center aligned and make a list of the columns to center.
Display the table

Solution

Solution

summary_table <- df %>%
  mutate(month = format(as.Date(datetime), "%B")) %>%  # Extract month names
  group_by(month) %>%                                 # Group by month
  summarise(
    total_rentals = sum(count, na.rm = TRUE),         # Calculate total rentals
    avg_wind_speed = mean(wind_speed, na.rm = TRUE)   # Calculate average wind speed
  ) %>%
  arrange(desc(total_rentals)) %>%                    # Arrange by descending total rentals
  gt() %>%
  tab_header(                                         # Add title and subtitle
    title = "Bike Share Summary by Month",
    subtitle = "Total Rentals and Average Wind Speed"
  ) %>%
  fmt_number(
    columns = c(total_rentals),                      # Format total rentals
    decimals = 0,
    use_seps = TRUE                                  # Add thousands separators
  ) %>%
  fmt_number(
    columns = c(avg_wind_speed),                     # Format average wind speed
    decimals = 2                                     # 2 decimal places for wind speed
  ) %>%
  cols_label(                                        # Rename column headers
    month = "Month",
    total_rentals = "Total Rentals",
    avg_wind_speed = "Average Wind Speed (m/s)"
  ) %>%
  cols_align(                                        # Center-align columns
    align = "center",
    columns = c(month, total_rentals, avg_wind_speed)
  )

# Print the table
summary_table

Bike Share Summary by Month
Total Rentals and Average Wind Speed
Month	Total Rentals	Average Wind Speed (m/s)
July	1,323,939	1.77
August	1,316,051	1.59
June	1,267,053	1.83
May	1,155,739	1.91
September	964,809	1.94
October	936,189	1.87
April	911,437	2.06
March	852,148	1.98
January	645,657	1.73
February	643,464	1.56
November	621,685	2.17
December	481,702	1.69

Create a table that shows the average temperature and total rentals for each hour of the day. Arrange the table in descending order based on average temperature. Format the numbers appropriately and add a title and subtitle to the table. Highlight the row for hour 12 by changing the background color to light green.

Hint

You first need to extract the hours. You can use this command to do this :

mutate(hour = as.numeric(format(as.POSIXct(datetime), "%H")))

Now that you have the hour variable, you can use group_by() for the hours.
Use the summarise command to calculate the sum of the average temperature (avg_temp) and the total rentals (total_rentals). Don’t forget to remove the NA values.
Use the arrange command to list the average temperature in a descending order.
Send this data to the gt( ) command via a pipe
Create a table header with a title and subtitle.
Format the numbers output for avg_temp (2 decimals) and total_rentals (0 decimals).
Create the three column labels “Hour of the Day”, “Average Temperature (C)”, and Total Rentals”.
Make the table center aligned and make a list of the columns to center.
Use tab_style( ) to change the cell_fill to light green (#D5F5E3) and highlight the row for hour 12 using rows = hour == 12

11.Display the table

Solution

Solution

summary_table <- df %>%
  mutate(hour = as.numeric(format(as.POSIXct(datetime), "%H"))) %>% # Extract hour of the day
  group_by(hour) %>%                                               # Group by hour
  summarise(
    avg_temp = mean(temp, na.rm = TRUE),                           # Calculate average temperature
    total_rentals = sum(count, na.rm = TRUE)                       # Calculate total rentals
  ) %>%
  arrange(desc(avg_temp)) %>%                                      # Arrange by descending average temperature
  gt() %>%
  tab_header(                                                      # Add title and subtitle
    title = "Bike Share Summary by Hour of the Day",
    subtitle = "Average Temperature and Total Rentals"
  ) %>%
  fmt_number(
    columns = c(avg_temp),                                         # Format average temperature
    decimals = 2
  ) %>%
  fmt_number(
    columns = c(total_rentals),                                    # Format total rentals
    decimals = 0,
    use_seps = TRUE                                                # Add thousands separators
  ) %>%
  cols_label(                                                      # Rename columns
    hour = "Hour of the Day",
    avg_temp = "Average Temperature (°C)",
    total_rentals = "Total Rentals"
  ) %>%
  cols_align(                                                      # Center-align columns
    align = "center",
    columns = c(hour, avg_temp, total_rentals)
  ) %>%
  tab_style(
    style = cell_fill(color = "#D5F5E3"),                          # Light green background color
    locations = cells_body(
      rows = hour == 12                                            # Highlight row for hour 12
    )
  )

# Print the table
summary_table

Bike Share Summary by Hour of the Day
Average Temperature and Total Rentals
Hour of the Day	Average Temperature (°C)	Total Rentals
20	18.76	473,836
19	18.62	686,235
21	18.58	340,644
18	18.19	971,779
22	17.95	253,374
17	17.59	1,159,455
23	17.20	169,437
16	16.66	868,172
0	16.40	98,734
15	15.75	729,874
1	15.54	55,691
2	14.96	35,229
14	14.70	661,051
3	14.38	19,314
4	14.03	20,988
5	13.64	75,055
13	13.47	661,282
6	13.37	226,625
7	13.05	515,182
8	12.80	832,723
12	12.61	655,499
9	12.50	560,739
10	12.31	481,335
11	12.25	567,620

Create a table that shows the total rentals and average humidity for each day of the week. Arrange the table in descending order based on total rentals. Format the numbers appropriately and add a title and subtitle to the table. Add a blue border around the cell for average humidity for the day “Saturday”.

Hint

You first need to extract the days of the week. You can use this command to do this :

mutate(day_of_week = weekdays(as.Date(datetime)))

Now that you have the day_of_week variable, you can use group_by() for them.
Use the summarise command to calculate the sum of the total rentals (total_rentals) and the average humidity (avg_humidity). Don’t forget to remove the NA values.
Use the arrange command to list the total rentals in a descending order.
Send this data to the gt( ) command via a pipe
Create a table header with a title and subtitle.
Format the numbers output for total_rentals (0 decimals) and average_humidity (2 decimals).
Create the three column labels “Days of the Week”, “Total Rentals”, and “Average Humidity (%)”.
Make the table center aligned and make a list of the columns to center.
In the tab_style( ) command,

Set the style to cell_borders( )
- Inside cell_borders( ), set the sides to c("top", "bottom", "left", "right"), the color to "blue", and the weight to px(2).
In the locations argument, use cells_body( ) to specify
- the column as avg_humidity and the row as day_of_week == "Saturday".

Display the table

Solution

Solution

summary_table <- df %>%
  mutate(day_of_week = weekdays(as.Date(datetime))) %>%      # Extract day of the week
  group_by(day_of_week) %>%                                  # Group by day of the week
  summarise(
    total_rentals = sum(count, na.rm = TRUE),                # Calculate total rentals
    avg_humidity = mean(humidity, na.rm = TRUE)              # Calculate average humidity
  ) %>%
  arrange(desc(total_rentals)) %>%                           # Arrange by descending total rentals
  gt() %>%
  tab_header(                                                # Add title and subtitle
    title = "Bike Share Summary by Day of the Week",
    subtitle = "Total Rentals and Average Humidity"
  ) %>%
  fmt_number(
    columns = c(total_rentals),                              # Format total rentals
    decimals = 0,
    use_seps = TRUE                                          # Add thousands separators
  ) %>%
  fmt_number(
    columns = c(avg_humidity),                               # Format average humidity
    decimals = 2                                             # Set 2 decimal places
  ) %>%
  cols_label(                                                # Rename column headers
    day_of_week = "Day of the Week",
    total_rentals = "Total Rentals",
    avg_humidity = "Average Humidity (%)"
  ) %>%
  cols_align(                                                # Center-align columns
    align = "center",
    columns = c(day_of_week, total_rentals, avg_humidity)
  ) %>%
  tab_style(
    style = cell_borders(                                    # Add a blue border around the cell
      sides = c("top", "bottom", "left", "right"),           # Borders on all sides
      color = "blue",                                        # Border color: blue
      weight = px(2)                                         # Border weight: 2px
    ),
    locations = cells_body(
      columns = c(avg_humidity),                             # Target the "avg_humidity" column
      rows = day_of_week == "Saturday"                       # Target the row for "Saturday"
    )
  )

# Print the table
summary_table

Bike Share Summary by Day of the Week
Total Rentals and Average Humidity
Day of the Week	Total Rentals	Average Humidity (%)
Saturday	1,752,168	66.69
Friday	1,638,480	68.00
Tuesday	1,589,994	68.23
Thursday	1,584,070	67.29
Wednesday	1,581,243	67.04
Monday	1,492,825	68.50
Sunday	1,481,093	68.94

Create a table that shows the average temperature and total rentals for each weather type. Arrange the table in descending order based on average temperature. Format the numbers appropriately and add a title and subtitle to the table. Highlight the cell for average temperature for the weather type “Clouds” by changing the font color to dark blue and using bold text.

Hint

Use group_by() for the months.weather_main` variable.
Use the summarise command to calculate the average temperature (avg_temp) and the total rental (total_rentals). Don’t forget to remove the NA values.
Use the arrange command to list the average temperature in a descending order.
Send this data to the gt( ) command via a pipe
Create a table header with a title and subtitle.
Format the numbers output for avg_temp (2 decimals) and total_rentals (0 decimals).
Create the three column labels “Weather Type”, “Average Temperature (C)”, and “Total Rentals”.
Make the table center aligned and make a list of the columns to center.
In the tab_style( ) command,

Set the style to text( )
- Inside cell_text( ), set the color to "darkblue" and the weight to "bold".
In the locations argument, use cells_body( ) to specify
- the column as avg_humidity and the row as weather_main == "Clouds".

Display the table

Solution

Solution

summary_table <- df %>%
  group_by(weather_main) %>%                                # Group by weather type
  summarise(
    avg_temp = mean(temp, na.rm = TRUE),                   # Calculate average temperature
    total_rentals = sum(count, na.rm = TRUE)               # Calculate total rentals
  ) %>%
  arrange(desc(avg_temp)) %>%                              # Arrange by descending average temperature
  gt() %>%
  tab_header(                                              # Add title and subtitle
    title = "Bike Share Summary by Weather Type",
    subtitle = "Average Temperature and Total Rentals"
  ) %>%
  fmt_number(
    columns = c(avg_temp),                                 # Format average temperature
    decimals = 2                                           # 2 decimal places
  ) %>%
  fmt_number(
    columns = c(total_rentals),                            # Format total rentals
    decimals = 0,
    use_seps = TRUE                                        # Add thousands separators
  ) %>%
  cols_label(                                              # Rename column headers
    weather_main = "Weather Type",
    avg_temp = "Average Temperature (°C)",
    total_rentals = "Total Rentals"
  ) %>%
  cols_align(                                              # Center-align columns
    align = "center",
    columns = c(weather_main, avg_temp, total_rentals)
  ) %>%
  tab_style(
    style = cell_text(                                     # Style for the font
      color = "darkblue",                                  # Font color: dark blue
      weight = "bold"                                      # Make the text bold
    ),
    locations = cells_body(
      columns = c(avg_temp),                               # Target the `avg_temp` column
      rows = weather_main == "Clouds"                     # Target the row for weather type "Clouds"
    )
  )

# Print the table
summary_table

Bike Share Summary by Weather Type
Average Temperature and Total Rentals
Weather Type	Average Temperature (°C)	Total Rentals
Squall	29.64	12
Thunderstorm	25.53	75,673
Smoke	19.57	14,898
Haze	16.49	30,622
Rain	16.44	1,575,784
Clouds	15.97	8,115,545
Clear	10.85	807,465
Fog	10.74	35,567
Mist	10.16	371,598
Drizzle	9.89	64,638
Snow	−0.44	28,071

Create a table that shows the total rentals and average wind speed for each month. Arrange the table in descending order based on average wind speed. Format the numbers appropriately and add a title and subtitle to the table. Add a red border around the row for the month of “July”.

Hint

You first need to extract the months. You can use this command to do this :

mutate(month = format(as.Date(datetime), "%B"))

Now that you have the month variable, you can use group_by() for the months.
Use the summarise command to calculate the sum of the total rentals (total_rentals) and the average wind speed (avg_wind_speed). Don’t forget to remove the NA values.
Use the arrange command to list the average wind speed in a descending order.
Send this data to the gt( ) command via a pipe
Create a table header with a title and subtitle.
Format the numbers output for total_rentals (0 decimals) and average_wind_speed (2 decimals).
Create the three column labels “Month”, “Total Rentals”, and “Average Wind Speed (m/s)”.
Make the table center aligned and make a list of the columns to center.
In the tab_style( ) command,

Set the style to cell_borders( )
- Inside cell_borders( ), set the sides to c("top", "bottom", "left", "right"), the color to "red", and the weight to px(2).
In the locations argument, use cells_body( ) to specify
- the rows as month == "July".

Display the table

Solution

Solution

summary_table <- df %>%
  mutate(month = format(as.Date(datetime), "%B")) %>%        # Extract month names
  group_by(month) %>%                                        # Group data by month
  summarise(
    total_rentals = sum(count, na.rm = TRUE),                # Calculate total rentals
    avg_wind_speed = mean(wind_speed, na.rm = TRUE)          # Calculate average wind speed
  ) %>%
  arrange(desc(avg_wind_speed)) %>%                          # Sort by descending wind speed
  gt() %>%
  tab_header(                                                # Add title and subtitle
    title = "Bike Share Summary by Month",
    subtitle = "Total Rentals and Average Wind Speed"
  ) %>%
  fmt_number(
    columns = c(total_rentals),                              # Format total rentals
    decimals = 0,
    use_seps = TRUE                                          # Add thousands separators
  ) %>%
  fmt_number(
    columns = c(avg_wind_speed),                             # Format average wind speed
    decimals = 2                                             # Format with 2 decimals
  ) %>%
  cols_label(                                                # Rename column headers
    month = "Month",
    total_rentals = "Total Rentals",
    avg_wind_speed = "Average Wind Speed (m/s)"
  ) %>%
  cols_align(                                                # Center-align columns
    align = "center",
    columns = c(month, total_rentals, avg_wind_speed)
  ) %>%
  tab_style(
    style = cell_borders(                                    # Add red border around the row
      sides = c("top", "bottom", "left", "right"),           # Apply borders to all sides
      color = "red",                                         # Border color: red
      weight = px(2)                                         # Border thickness: 2px
    ),
    locations = cells_body(
      rows = month == "July"                                 # Apply border to row for "July"
    )
  )

# Print the table
summary_table

Bike Share Summary by Month
Total Rentals and Average Wind Speed
Month	Total Rentals	Average Wind Speed (m/s)
November	621,685	2.17
April	911,437	2.06
March	852,148	1.98
September	964,809	1.94
May	1,155,739	1.91
October	936,189	1.87
June	1,267,053	1.83
July	1,323,939	1.77
January	645,657	1.73
December	481,702	1.69
August	1,316,051	1.59
February	643,464	1.56