How to Make Publication-Quality Excel Pivot Tables with R

Written by Matt Dancho on October 14, 2020


This article is part of a R-Tips Weekly, a weekly video tutorial that shows you step-by-step how to do common R coding tasks.

Let’s learn how to make Excel pivot tables with R.


(Click image to play tutorial)


Excel Pivot Tables with R Tutorial

The biggest thing I missed when I transititioned from Excel to R was… PIVOT TABLES!

Seriously - Pivot Tables are so useful. You can summarize and reshape (aka Pivot) data so easily with them in Excel. Why not in R???

Here’s the Publication-Quality Pivot Table that we are going to make today. 👇

Pivot Table


First, collect your data using tidyquant, an awesome package I created for importing & working with Financial Data.

Get Data

Stock Table


Next, make a pivot_table() from the stock data. The resulting data summarized using a percentage change from 1st to last in each pivot cell.

Percent Change by Year

Pivot Table


And with the GT Package, we can make the pivoted data into a PUBLICATION-QUALITY Pivot Table with conditional formatting applied to highlight years with positive stock returns. (Table code partially shown. Get the GitHub code to reproduce the table.)

Pivot Charts


And, now you have a publication-quality stock performance report! 👇

Stock Returns


You’ve just made a publication-quality Pivot Table in R.

Boom! 💥💥💥

Learn something? Awesome!

But you need to learn a TON to become an R Samurai Master.


SETUP R-TIPS WEEKLY PROJECT

  1. Sign Up to Get the R-Tips Weekly (You’ll get email notifications of NEW R-Tips as they are released): https://mailchi.mp/business-science/r-tips-newsletter

  2. Set Up the GitHub Repo: https://github.com/business-science/free_r_tips

  3. Check out the setup video (https://youtu.be/F7aYV0RPyD0). Or, Hit Pull in the Git Menu to get the R-Tips Code

Once you take these actions, you’ll be set up to receive R-Tips with Code every week. =)