Transforming dataframes in R using apply

Question

I am attempting to convert my dataframe from one format to another I stared with:

>dfst
    cat1 cat2 cat3
    a    x    1
    b    x    2
    c    x    3
    a    y    9
    b    y    8
    c    y    7

I am trying to make:

    x     y
a   1     9
b   2     8
c   3     7

currently my code looks something like this:

#make the new df
df <- data.frame(matrix(ncol = length(unique(df$cat1)),
                        nrow = length(unique(df$cat2))))

row.names(df) <- unique(df$cat2)
colnames(df) <- unique(df$cat1)


f <- function(x, out= df){
  val.row <- x[[1]]
  val.col <- x[[3]]
  out[val.row, val.col] <- x[[2]]
}

apply(dfst, 1, f, out = df)

Print statements give me the right values for val.row and val.col, it will even find values I manually load in with print(out[val.row, val.col]) but it will not load x[[2]].

As far as I can tell out["a", "x"] <- dfst[1, 3] also works.

any advice?

`tidyr::spread(dfst, cat2, cat3)` – Ronak Shah Aug 24 '19 at 03:02 — Ronak Shah, Aug 24 '19 at 03:02

Onyambu · Accepted Answer · 2019-08-23T23:05:36.843

4

You could use reshape from base R

reshape(dfst,timevar = "cat2",idvar = "cat1",dir="wide")

  cat1 cat3.x cat3.y
1    a      1      9
2    b      2      8
3    c      3      7

or you could do:

data.table::dcast(dfst,cat1~cat2)

edited Aug 23 '19 at 23:05

answered Aug 23 '19 at 22:59

Onyambu

67,392
3
24
53

score 3 · Answer 2 · answered Aug 23 '19 at 23:08

Another option is xtabs from base R

xtabs(cat3~ cat1 + cat2, dfst)
#    cat2
#cat1 x y
#   a 1 9
#   b 2 8
#   c 3 7

data

dfst <- structure(list(cat1 = c("a", "b", "c", "a", "b", "c"), cat2 = c("x", 
"x", "x", "y", "y", "y"), cat3 = c(1L, 2L, 3L, 9L, 8L, 7L)),
class = "data.frame", row.names = c(NA, 
-6L))

Transforming dataframes in R using apply

2 Answers2

data