CODEWITHSUNDEEP
r
Big_Red
Big_Red

Reputation: 31

Make a table with nested columns in R

I have a table in R that is built using this data frame:

Year <- c('2016','2017','2018','2016','2017','2018')
D <- c(0.6,0.57,.56,.41,.45,.85)
C <- c(.8,.9,.65,.8,.9,.65)
var <- c('a','a','a','b','b','b')
dat <- cbind.data.frame(var,Year,D,C)

My goal create a summary table with a header row for each year, then nest(apologies if this not the correct word here) fields D and E under said year: desired output

Upvotes: 1

Views: 1560

Answers (3)

Onyambu
Onyambu

Reputation: 79358

Using base R, it is not quite true that you can produce the desired output. R dataframes do not allow for MultiIndex unless you use ftables. The closses you could get is using dataframes is provided by the below solutions. If you dont mind using ftables you could do:

# creating data table
Year <- as.factor(c('2016','2017','2018','2016','2017','2018'))
D <- c(0.6,0.57,.56,.41,.45,.85)
C <- c(.8,.9,.65,.8,.9,.65)
var <- c('a','a','a','b','b','b')
dat <- cbind.data.frame(var,Year,D,C)


# reformatting
tm <- c("D", "C") # the columns to change
a <- reshape(dat, list(tm), idvar = c("var", "Year"), dir = "long", times = tm))
ftable(xtabs(D~., a), row.vars = 1)

    Year 2016      2017      2018     
    time    C    D    C    D    C    D
var                                   
a        0.80 0.60 0.90 0.57 0.65 0.56
b        0.80 0.41 0.90 0.45 0.65 0.85

EDIT: since you are using the KableExtra package, You could do:

nm <- setNames(rep(1:2, c(2, length(unique(dat$Year)))), c(rep(" ", 2), levels(dat$Year)))

library(kableExtra)
dat %>%
  reshape(idvar = "var", timevar = "Year", direction = "wide") %>%
  setNames(gsub("[0-9.]+", "", names(.))) %>%
  kable() %>%
  kable_styling("striped") %>%
  add_header_above(nm)

The result:

enter image description here

Upvotes: 1

akrun
akrun

Reputation: 887981

An option with pivot_wider

library(dplyr)
library(tidyr)
dat %>% 
   pivot_wider(names_from = Year, values_from = c(D, C))
# A tibble: 2 x 7
#  var   D_2016 D_2017 D_2018 C_2016 C_2017 C_2018
#  <chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
#1 a       0.6   0.570   0.56    0.8    0.9   0.65
#2 b       0.41  0.45    0.85    0.8    0.9   0.65

Or using dcast from data.table

library(data.table)
dcast(setDT(dat), var ~ Year, value.var = c("D", "C"))
#   var D_2016 D_2017 D_2018 C_2016 C_2017 C_2018
#1:   a   0.60   0.57   0.56    0.8    0.9   0.65
#2:   b   0.41   0.45   0.85    0.8    0.9   0.65

Upvotes: 0

Eyayaw
Eyayaw

Reputation: 1081

You can't have a multi-row index in R. This might work:

reshape(dat, idvar = "var", timevar = "Year", direction = "wide")

    var D.2016 C.2016 D.2017 C.2017 D.2018 C.2018
1   a   0.60    0.8   0.57    0.9   0.56   0.65
4   b   0.41    0.8   0.45    0.9   0.85   0.65

Upvotes: 0

Related Questions