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")

Answer 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.

Answer 2

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