CODEWITHSUNDEEP
rdataframedplyrreshape2tidyr
spore234
spore234

Reputation: 3640

reshape different observations to wide

I have data like this:

dat <- data.frame(id=c(1,1,1,2,2,2), 
                  v1=factor(c("name","sex","age",
                              "name","sex","age")),
                  v2=factor(c("a","m","50","b","f","40")))
>dat
     id   v1  v2
   1  1 name  a
   2  1  sex  m
   3  1  age 50
   4  2 name  b
   5  2  sex  f
   6  2  age 40

how can I reshape this to a wide table where every id only has one row. Like this:

id    name    sex    age
 1       a      m     50
 2       b      f     40

In a next step, assume my data looks like this, i.e. the name is missing for the second id

dat2 <- data.frame(id=c(1,1,1,2,2), 
                  v1=factor(c("name","sex","age",
                              "sex","age")),
                  v2=factor(c("a","m","50","f","40")))

The table should then look like this (contain NA):

id    name    sex    age
 1       a      m     50
 2      NA      f     40

Not that my real data set may contain a mix of factors and numeric variables. Also the number of entries each id has can be very different.

In a next case, V1 may occur multiple times, like this

dat3 <- data.frame(id=c(1,1,1,2,2), 
                  v1=factor(c("value","value","obs",
                               "value", "obs")),
                  v2=factor(c("5","3","5","6","8")))

the table should then look like this

id    value1   value2    obs    
 1         5        3      5
 2         6       NA      8

I would also like to see a solution where the mean (or max,min,..) is computed when there are multiple values for each id, like this

id    value    obs    
 1        4      5      # mean(c(3,5)==4
 2        6      8

thanks

Upvotes: 0

Views: 75

Answers (1)

jeremycg
jeremycg

Reputation: 24945

Let's use tidyr and dplyr:

library(tidyr)
library(dplyr)

first problem:

spread(dat, v1, v2)

  id age name sex
1  1  50    a   m
2  2  40    b   f

Second problem is the same - spread automatically uses NA when data is missing:

spread(dat2, v1, v2)

  id age name sex
1  1  50    a   m
2  2  40 <NA>   f

Third problem, we will use dplyr to summarise, then spread, after we turn v2 to numeric:

dat3 %>% mutate(v2 = as.numeric(as.character(v2))) %>%
         group_by(id, v1) %>%
         summarise(mean = mean(v2)) %>%
         spread(v1, mean)

Source: local data frame [2 x 3]

  id obs value
1  1   5     4
2  2   8     6

and for the wider version, we can use unite:

dat3 %>% group_by(id, v1) %>%
         mutate(v2 = as.numeric(as.character(v2)), id2=row_number())  %>%
         unite(v3, c(v1,id2)) %>%
         spread(v3, v2)

Source: local data frame [2 x 4]

  id obs_1 value_1 value_2
1  1     5       5       3
2  2     8       6      NA

Upvotes: 1

Related Questions