1

I am using the pivot function from the lessR package, to create an Excel-like pivot table with two categorical variables that make up the vertical and horizontal categories, and a mean in each cell. (Hope this makes sense).

I followed the code that the documentation (https://cran.r-project.org/web/packages/lessR/vignettes/pivot.html) gives. Let's follow their example:

d <- Read("Employee")
a <- pivot(d, mean, Salary, Dept, Gender)

The data d is like this:

                    Years Gender Dept    Salary JobSat Plan Pre Post
Ritchie, Darnell        7      M ADMN  53788.26    med    1  82   92
Wu, James              NA      M SALE  94494.58    low    1  62   74
Hoang, Binh            15      M SALE 111074.86    low    3  96   97
Jones, Alissa           5      F <NA>  53772.58   <NA>    1  65   62
Downs, Deborah          7      F FINC  57139.90   high    2  90   86
Afshari, Anbar          6      F ADMN  69441.93   high    2 100  100
Knox, Michael          18      M MKTG  99062.66    med    3  81   84
Campagna, Justin        8      M SALE  72321.36    low    1  76   84
Kimball, Claire         8      F MKTG  61356.69   high    2  93   92

The pivottable a is a nice table, exactly as I want it to look in terms of cell contents, etc. It appears to be a knitr_kable.

 Gender  F          M               
 Dept                        
-------  ---------  ---------
   ACCT   63237.16   59626.20
   ADMN   81434.00   80963.35
   FINC   57139.90   72967.60
   MKTG   64496.02   99062.66
   SALE   64188.25   86150.97

Next, I would like to make a dataframe out of this, for easier manipulation in my code and for copying it to the clipboard. However, I don't know how to convert a knitr_kable to a dataframe. Here is my code and the error it results in:

as.data.frame(a)

Error in as.data.frame.default(a) : 
  cannot coerce class ‘"knitr_kable"’ to a data.frame

The knitr-documentation does not say anything about this conversion - it is only about converting a dataframe to a knitr_kable, which is the opposite of what I want.

I have also tried pivottabler, but this has similar issues: the resulting class cannot be coerced to a dataframe either.

Erjen
  • 97
  • 7
  • Why not just work with the data that the kable was made from? – camille Sep 16 '21 at 15:34
  • Yes, I'd suggest `library(tidyverse); d %>% group_by(Dept, Gender) %>% summarize(value = mean(Salary), .groups = "drop") %>% pivot_wider(names_from = Gender, values_from = value)` – Jon Spring Sep 16 '21 at 15:38
  • Thanks Jon, this is what I will end up using, or maybe the tidyr option that was suggested below. Both deliver what I need! The difference is that you added .groups="drop", what does that do? – Erjen Sep 16 '21 at 17:08

1 Answers1

2

Here are two potential answers:

Most direct: Wrangle the data yourself

If you're open to a tidyverse-style approach, it only takes a few lines to do the wrangling and summarising yourself. That will give you a datatable output that you can work with right away.

# load packages
library(lessR)
library(dplyr)
library(tidyr)

# load data
d <- Read("Employee")

# use tidyverse-style code to pivot and summarise the data yourself
d %>%
  group_by(Gender, Dept) %>%
  summarise(Salary_mean = mean(Salary)) %>%
  pivot_wider(names_from= "Gender", values_from = "Salary_mean")

Read the knitr::kable() markdown output into a data frame

If you prefer to work backwards from a knitr::kable() output to a dataframe, this is addressed in this SO question: Markdown table to data frame in R

Christopher Belanger
  • 556
  • 2
  • 9