How to convert rpivotTable result in to dataframe

Question

How to convert rpivotTable result in to dataframe. I want to create new datafrmae with the result of rpivotTable,

Is it possible do in R?

Data Set Like

User     Order_Bin
a          PPL
b          CCD
c          CCD
d          OLP
a          OLP
c          PPL
b          OLP
a          PPL
a          OLP
b          PPL
c          CCD
d          CCD
d          OLP
c          OLP
b          OLP
b          CCD

How to get result of below code as data.frame

library(rpivotTable)
rpivotTable(
  inventory,
  aggregatorName = "Count",
  cols = "Order_Bin",
  rows = "User",
  rendererName = "Heatmap",
  width = "100%",
  height = "1000px")

Did you search this site? https://stackoverflow.com/questions/56410154/convert-pivot-table-generated-from-pivottabler-package-to-dataframe — Edward, Mar 05 '20 at 11:48
@Edward: The question https://stackoverflow.com/q/56410154/3817004 you have linked to uses _another package_ `pivottabler` which is different to `rpivotTable` which is used in this question. — Uwe, Mar 05 '20 at 13:44

Uwe · Accepted Answer · 2020-03-05T12:28:45.443

1

According to the documention of rpivotTable, there is no export facility.

So, you have to aggregate on your own. One possibility is

reshape2::dcast(inventory, User ~ Order_Bin, length, margins = TRUE)

which returns

   User CCD OLP PPL (all)
1     a   0   2   2     4
2     b   2   2   1     5
3     c   2   1   1     4
4     d   1   2   0     3
5 (all)   5   7   4    16

For comparison, here is the output of the pivotTable() call:

Please, note the Totals row and column.

edited Mar 05 '20 at 12:28

answered Mar 05 '20 at 11:46

Uwe

41,420
11
90
134

Use of pivotTable not working for me in community lots of people facing same issue, its Stop flexdashboard, And thanks for the help, dcast working fine. – Nirav Prajapati Mar 05 '20 at 13:38

Edward · Answer 2 · 2020-03-05T14:58:28.550

The data frame is stored in the list, along with the row and col variable names, and the aggregatorName.

library(rpivotTable)
pv <- rpivotTable(
  df,
  aggregatorName = "Count",
  cols = "Order_Bin",
  rows = "User",
  rendererName = "Heatmap",
  width = "100%",
  height = "1000px")

class(pv)
str(pv)
List of 8
 $ x            :List of 4
  ..$ data     :'data.frame':   16 obs. of  2 variables:
  .. ..$ User     : chr [1:16] "a" "b" "c" "d" ...
  .. ..$ Order_Bin: chr [1:16] "PPL" "CCD" "CCD" "OLP" ...
  ..$ params   :List of 4
  .. ..$ rows          :List of 1
  .. .. ..$ : chr "User"
  .. ..$ cols          :List of 1
  .. .. ..$ : chr "Order_Bin"
  .. ..$ aggregatorName:List of 1
  .. .. ..$ : chr "Count"  # etc. ...

This information can then be used to create the data frame you asked for containing the results of the pivotTable:

pv$x$data %>%
  group_by(Order_Bin, User) %>%
  count() %>% 
  pivot_wider(id_cols=User, names_from=Order_Bin, values_from=n, 
              values_fill=list(n=0))

# A tibble: 4 x 4
  User    CCD   OLP   PPL
  <chr> <int> <int> <int>
1 b         2     2     1
2 c         2     1     1
3 d         1     2     0
4 a         0     2     2

Edit: To get row percentages:

pv$x$data %>%
  group_by(Order_Bin, User) %>%
  count() %>% 
  group_by(User) %>%
  mutate(n=n/sum(n)) %>%
  pivot_wider(id_cols=User, names_from=Order_Bin, values_from=n, 
              values_fill=list(n=0))

# A tibble: 4 x 4
# Groups:   User [4]
  User    CCD   OLP   PPL
  <chr> <dbl> <dbl> <dbl>
1 b     0.4   0.4    0.2 
2 c     0.5   0.25   0.25
3 d     0.333 0.667  0   
4 a     0     0.5    0.5

Well, yes that's the input data but without the margins (totals). — Uwe, Mar 05 '20 at 12:30
This is the same as expected but i also want to do, For this code `pv1 <-rpivotTable(sub_data, aggregatorName = "Count as Fraction of Rows", rows = "User", cols = c("Order_Bin"), rendererName = "Heatmap")` it not working.I want to apply rbind for join both the tables. is it possible? — Nirav Prajapati, Mar 05 '20 at 14:08
I want to displayed result with percentage under the CCD, OLP, PPL Like for User C 2 1 1 so percentage will be 50% 25% 25% total 100% which divided in Bin. By using `pv1 <-rpivotTable(sub_data, aggregatorName = "Count as Fraction of Rows", rows = "User", cols = c("Order_Bin"), rendererName = "Heatmap")` i get values of percentage but i want this values in datafram — Nirav Prajapati, Mar 05 '20 at 14:33

How to convert rpivotTable result in to dataframe

2 Answers2