R - merge information of the same elements in one row

Question

Say I have the following data.frame. The students had up to 3 attempts at a quiz.

df <- data.frame(id = c(1, 1, 2, 3, 3, 3), points = c(3, 4, 5, 2, 6, 8), value = c(5, 5, 6, 8, 6, 2))
df

#>   student points time
#> 1       1      3    5
#> 2       1      4    5
#> 3       2      5    6
#> 4       3      2    8
#> 5       3      6    6
#> 6       3      8    2

Now I want the data.frame to look like that

#> id points-a time-a points-b time-b  points-c time-c
#>  1        3      5        4      5        NA     NA
#>  2        5      6       NA     NA        NA     NA
#>  3        2      8        6      6         8      2

Just one row for every student with the different points and times in columns. How can I achieve that?

The problem is that I don't have a variable like the variable `numbers` in this question which I could use for reshaping. I've got a lot more data than in my example and the number of attempts of every student differ, so sometimes there is just one row of a specific student and sometimes there are 4. I'm sure this may be solved easily, but I'm quite new to R. — bettina m., Jul 14 '21 at 08:39

Tech Commodities · Answer 1 · 2021-07-14T13:41:13.340

This gives the required output:

 df %>% 
  group_by(student) %>% 
  mutate(head = letters[row_number()]) %>%
  pivot_wider(id_cols = student, names_from = head, names_sep = "-", values_from = c(points, time)) %>% 
  pivot_longer(-student) %>% 
  arrange(str_sub(name, -1)) %>% 
  pivot_wider(student)

The group_by() and mutate() provides the necessary column headings, then the pivot_wider() does the work.

The last three lines gives the right ordering, though probably better ways of doing this directly.

score 2 · Accepted Answer · answered Jul 14 '21 at 09:01

A base R option using reshape

reshape(
    transform(
        df,
        q = ave(id, id, FUN = seq_along)
    ),
    idvar = "id",
    timevar = "q",
    direction = "wide"
)

gives

  id points.1 value.1 points.2 value.2 points.3 value.3
1  1        3       5        4       5       NA      NA
3  2        5       6       NA      NA       NA      NA
4  3        2       8        6       6        8       2

R - merge information of the same elements in one row

2 Answers2