CODEWITHSUNDEEP
rexcel
Mike Hofer
Mike Hofer

Reputation: 17002

R & Excel: Creating Pivot Tables & Slicers

Our business users would like us to create exports of the data behind our analyses. The AVP would like to know if we can throw a slicer on the data so the users can filter the data any way they want. Excel slicers are backed by pivot tables, so I'd have to be able to create a pivot table in the Excel workbook first.

I can easily export the data to Excel from R. I can't seem to find an R package that supports the creation of either pivot tables or slicers. Does such a package exist? Or is this simply beyond the scope of any available package at this time?

Upvotes: 4

Views: 2109

Answers (3)

icj
icj

Reputation: 749

This is an old question, but in case somebody ends up here and still needs an answer to the core question:

I can easily export the data to Excel from R. I can't seem to find an R package that supports the creation of either pivot tables or slicers [in Excel]. Does such a package exist?

Yes, there is thanks to Chris Bailiss. Check out the package pivottabler, and there's a great vignette walk-through here.

Upvotes: 2

Yogesh
Yogesh

Reputation: 1432

Hi Mike I have created a Dataset and see how the pivot can be generated with reshape package. This can be downloaded as excel/csv. Hope this helps you

ID<-c(2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023)

type<-c('Spices','Spices','Fruit','Fruit','Fruit','Spices','Spices','Spices','Spices','Spices','Fruit','Fruit','Spices','Fruit','Fruit','Fruit','Fruit','Spices','Fruit','Fruit')

state<-c('New Jersey','New Jersey','New Jersey','New Jersey','New Jersey','New Jersey','Florida','Michigan','Michigan','New Jersey','New Jersey','New Jersey','New Jersey','New Jersey','New Jersey','Florida','Michigan', 'Michigan','Florida','Florida')

price<-c (1442,639,71,22,1185,508,434,24,528,8,41,82,2994,385,545,1850,2966,31,536,42)

data=data.frame(ID,type,state,price)

library(reshape)

data.m=melt(data, id=c(1:4), measure=c(4))

df1 <-cast(data.m, ID~type+value, sum)

#for adding grand total

df2<- cast(data.m, ID~type+value, sum, margins=c("grand_row"))

#for adding filter

df3 <- cast(data.m, ID~type+value, sum, margins=c("grand_row"),subset=state=="Florida")

df1, df2 and df3 are the pivots.

Upvotes: 0

Yogesh
Yogesh

Reputation: 1432

There are few package in R which provide these functionality. I suggest you to use rpivotTable and dplyr. These are the two packages I often use.

You can study these in detail.

https://cran.r-project.org/web/packages/rpivotTable/rpivotTable.pdf

Upvotes: 0

Related Questions