# Creating Summary Tables in R with tidyquant and dplyr

**Steve's Data Tips and Tricks**, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

# Introduction

Creating summary tables is a key part of data analysis, allowing you to see trends and patterns in your data. In this post, we’ll explore how to create these tables using **tidyquant** and **dplyr** in R. These packages make it easy to manipulate and summarize your data.

# Examples

## Using tidyquant for Summary Tables

**tidyquant** is a versatile package that extends the tidyverse for financial and time series analysis. It simplifies working with data by integrating tidy principles.

### Example: Calculating Average Price by Month

Here’s an example of how to calculate the average price by month using tidyquant:

# Load necessary libraries library(tidyquant) library(dplyr) # Sample data: Daily stock prices data <- tibble( date = seq(as.Date('2023-01-01'), as.Date('2023-06-30'), by = 'day'), price = runif(181, 100, 200) ) # Create a summary table with average closing price by month summary_table <- data |> mutate(month = floor_date(date, "month")) |> pivot_table( .rows = month, .values = ~ mean(price, na.rm = TRUE) ) |> setNames(c("date", "avg_price")) print(summary_table)

# A tibble: 6 × 2 date avg_price <date> <dbl> 1 2023-01-01 149. 2 2023-02-01 162. 3 2023-03-01 151. 4 2023-04-01 151. 5 2023-05-01 145. 6 2023-06-01 149.

In this example:

**tidyquant**and**tibble**are loaded to handle data manipulation.- We create a sample dataset with daily stock prices.
- The
`mutate`

function adds a new column`month`

, which extracts the month from each date. `pivot_table`

calculates the average price for each month.- Finally, we rename the columns for clarity.

## Using dplyr for Summary Tables

**dplyr** is a core tidyverse package known for its powerful data manipulation functions. It helps streamline the process of filtering, summarizing, and mutating data.

### Example: Calculating Average Closing Price by Month

Here’s a similar example using dplyr:

# Load necessary libraries library(dplyr) library(lubridate) # Sample data: Daily stock prices data <- tibble( date = seq(as.Date('2023-01-01'), as.Date('2023-06-30'), by = 'day'), price = runif(181, 100, 200) ) # Create a summary table with average closing price by month summary_table <- data %>% mutate(month = floor_date(date, "month")) %>% group_by(month) %>% summarise(avg_close = mean(price)) print(summary_table)

# A tibble: 6 × 2 month avg_close <date> <dbl> 1 2023-01-01 149. 2 2023-02-01 140. 3 2023-03-01 147. 4 2023-04-01 146. 5 2023-05-01 147. 6 2023-06-01 151.

In this dplyr example:

- We load
**dplyr**and**lubridate**for data manipulation and date handling. - The dataset creation process is the same.
- The
`mutate`

function is used to add a`month`

column. - We group the data by month using
`group_by`

and then calculate the average closing price for each group using`summarise`

.

# Your Turn!

Using packages like **tidyquant** and **dplyr** simplifies data analysis tasks, making it easier to work with large datasets. These examples show just one way to create summary tables; there are many other functions and methods to explore. Give these examples a try with your own data and see how you can summarize and gain insights from your datasets.

Happy coding!

**leave a comment**for the author, please follow the link and comment on their blog:

**Steve's Data Tips and Tricks**.

R-bloggers.com offers

**daily e-mail updates**about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.