Pivot tables in R – pivoting rows to columns

Posted on: September 29, 2017 Posted by: Guy Manova Comments: 0

Pivot tables in R – pivoting rows to columns

Pivoting rows to columns – How to pivot a categorical attribute column into columns in R

in this short tutorial we’ll see how¬†pivot rows to columns in R – replicating moving a categorical attribute from a pivot table row to a pivot table column (as you would do it in Excel).

follow the step by step below in R studio or download the R file from github.

to see how the pivot was built the you can take a look at the previous posts here and here:

# clean up the environment

rm(list = ls())

# we’ll need dplyr and tidyr for this, by far easier than with base R

library(dplyr)
library(tidyr)

# read in the Canadian SuperStore dataset from this dropbox address:

df <- read.csv(“https://www.dropbox.com/s/kj9yioc24iq4pdb/superstore.csv?dl=1”)
write.csv(df, “superstore.csv”)

Let’s have a quick look in the excel:

pivot rows to columns R

pivot rows to columns R

pivot rows to columns excel

# in a previous tutorial we created a summary table with this dplyr code, summarising
# sales by product category, region and customer segment:

pivot2 <- df %>%
select(Product.Category, Region, Customer.Segment, Sales) %>%
group_by(Product.Category, Region, Customer.Segment) %>%
summarise(TotalSales = sum(Sales))

head(pivot2)

pivot rows to columns R

# let’s emulate an Excel pivoting and pivot rows to columns in R, by spreading out the regions to
# seperate columns.
# we use the TidyR “spread” function to summarize the region in columns:

pivot2 %>%
spread(Region, TotalSales)

calculated field in R pivot table

Leave a Reply:

Your email address will not be published. Required fields are marked *