How to Make Publication-Quality Excel Pivot Tables with R
Written by Matt Dancho
![](/assets/2020-10-14-pivot-tables-with-r/pivot-tables-r-cover.png)
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](/assets/2020-10-14-pivot-tables-with-r/publication-quality-pivot-table.jpg)
First, collect your data using tidyquant
, an awesome package I created for importing & working with Financial Data.
![Get Data](/assets/2020-10-14-pivot-tables-with-r/get-data.jpg)
![Stock Table](/assets/2020-10-14-pivot-tables-with-r/stock-table.jpg)
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](/assets/2020-10-14-pivot-tables-with-r/percent-change-by-year.jpg)
![Pivot Table](/assets/2020-10-14-pivot-tables-with-r/pivot-table.jpg)
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](/assets/2020-10-14-pivot-tables-with-r/pivot-charts.jpg)
And, now you have a publication-quality stock performance report! π
![Stock Returns](/assets/2020-10-14-pivot-tables-with-r/stock-returns.jpg)
Youβve just made a publication-quality Pivot Table in R.
Boom! π₯π₯π₯
![](/assets/2020-10-14-pivot-tables-with-r/boom.gif)
Learn something? Awesome!
But you need to learn a TON to become an R Samurai Master.
![](/assets/2020-10-14-pivot-tables-with-r/wizard.gif)
SETUP R-TIPS WEEKLY PROJECT
-
Get the Code
-
Check out the R-Tips Setup Video.
Once you take these actions, youβll be set up to receive R-Tips with Code every week. =)