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.
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. 👇
First, collect your data using
tidyquant, an awesome package I created for importing & working with Financial Data.
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.
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.)
And, now you have a publication-quality stock performance report! 👇
You’ve just made a publication-quality Pivot Table in R.
Learn something? Awesome!
But you need to learn a TON to become an R Samurai Master.
SETUP R-TIPS WEEKLY PROJECT
Once you take these actions, you’ll be set up to receive R-Tips with Code every week. =)
👇 Top R-Tips Tutorials you might like:
- mmtable2: ggplot2 for tables
- ggdist: Make a Raincloud Plot to Visualize Distribution in ggplot2
- ggside: Plot linear regression with marginal distributions
- DataEditR: Interactive Data Editing in R
- openxlsx: How to Automate Excel in R
- officer: How to Automate PowerPoint in R
- DataExplorer: Fast EDA in R
- esquisse: Interactive ggplot2 builder
- gghalves: Half-plots with ggplot2
- rmarkdown: How to Automate PDF Reporting
- patchwork: How to combine multiple ggplots
- Geospatial Map Visualizations in R
Want these tips every week? Join R-Tips Weekly.