Daniel Daniel - 3 months ago 7
R Question

Change long structure to wide structure in R

I have data in this format:

enter image description here

How can I re-organize the data with R in the following format?

enter image description here

In other words: Create a new column for every single observation and paste a simple count if the observation occurs for the specific group.

Answer
dat <- data.frame(letter = c("A", "A", "A", "A",
                             "B", "B", "B", "C",
                             "C", "C", "C", "D"),
                  number = c(2, 3, 4,5, 4, 5, 6, 1, 3, 5, 7, 1))

I would like to provide an R base solution (maybe just for fun...), based on matrix indexing.

lev <- unique(dat[[1L]]); k <- length(lev)  ## unique levels
x <- dat[[2L]]; p <- max(x)  ## column position
z <- matrix(0L, nrow = k, ncol = p, dimnames = list(lev, seq_len(p)))  ## initialization
z[cbind(match(dat[[1L]], lev), dat[[2L]])] <- 1L  ## replacement
z   ## display

#  1 2 3 4 5 6 7
#A 0 1 1 1 1 0 0
#B 0 0 0 1 1 1 0
#C 1 0 1 0 1 0 1
#D 1 0 0 0 0 0 0