spore234 spore234 - 10 months ago 83
R Question

reshape wide to long with indicator vector

I have a data frame like this:

df <- data.frame(v11=rnorm(1), v12=rnorm(1), v21=rnorm(1), v31=rnorm(1), v41=rnorm(1), v42=rnorm(1), v43=rnorm(1))


this data frame has only one row

> df
v11 v12 v21 v31 v41 v42 v43
1 -0.4425873 0.6062714 -0.3816921 0.2477926 0.1292103 0.2801346 0.4290997


some columns are repeated observations for the same subject, for example
v11
and
v12
are two observations from the same subject.
v21
and
v32
are only single observations,
v4x
has three observations.

I want to reshape the data frame to look like this:

v11 v21 v31 v41

v12 NA NA v42

NA NA NA v43


Note that it should contain the values of the variables not their names.

I have a indicator vector that marks which observation is what:

v <- c(1,2,1,1,1,2,3)


Each "1" marks the start of a new column.

My first idea was to build each row on its own:

row1 <- df[, v == 1]
row2 <- df[, v == 2]
row3 <- df[, v == 3]


but this does not contain the NAs.
There must be an easier way.

Note: A solution should not have to depend on the original column names
v11
,
v41
, etc, but only on the vector
v
.

Answer Source

Here is a solution with dplyr + tidyr:

library(dplyr)
library(tidyr)

v <- c(1,2,1,1,1,2,3)

df %>%
  gather(var, value) %>%
  mutate(row_num = v,
         col_num = paste0("v", cumsum(v==1))) %>%
  select(-var) %>%
  spread(col_num, value)

Result:

  row_num         v1       v2         v3        v4
1       1 -0.5604756 1.558708 0.07050839 0.1292877
2       2 -0.2301775       NA         NA 1.7150650
3       3         NA       NA         NA 0.4609162

Data:

df = structure(list(v11 = -0.560475646552213, v12 = -0.23017748948328, 
    v21 = 1.55870831414912, v32 = 0.070508391424576, v41 = 0.129287735160946, 
    v42 = 1.71506498688328, v43 = 0.460916205989202), .Names = c("v11", 
"v12", "v21", "v32", "v41", "v42", "v43"), row.names = c(NA, 
-1L), class = "data.frame")
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download