I have a long form dataframe that have multiple entries for same date and person.
jj <- data.frame(month=rep(1:3,4),
student=rep(c("Amy", "Bob"), each=6),
A=c(9, 7, 6, 8, 6, 9, 3, 2, 1, 5, 6, 5),
B=c(6, 7, 8, 5, 6, 7, 5, 4, 6, 3, 1, 5))
I want to convert it to wide form and make it like this:
month Amy.A Bob.A Amy.B Bob.B
1
2
3
1
2
3
1
2
3
1
2
3
My question is very similar to this. I have used the given code in the answer :
kk <- jj %>%
gather(variable, value, -(month:student)) %>%
unite(temp, student, variable) %>%
spread(temp, value)
but it gives following error:
Error: Duplicate identifiers for rows (1, 4), (2, 5), (3, 6), (13, 16), (14, 17), (15, 18), (7, 10), (8, 11), (9, 12), (19, 22), (20, 23), (21, 24)
Thanks in advance. Note: I don't want to delete multiple entries.
If we create a unique sequence, then we can the output in the correct format with
pivot_wider
data
The issue is the two columns for both
A
andB
. If we can make that one value column, we can spread the data as you would like. Take a look at the output forjj_melt
when you use the code below.I won't mark this as a duplicate since the other question did not summarize by
sum
, but thedata.table
answer could help with one additional argument,fun=sum
:If you would like to use the
tidyr
solution, combine it withdcast
to summarize bysum
.Edit
Based on your new requirements, I have added an activity column.
The other solutions can also be used. Here I added an optional expression to arrange the final output by activity number:
The
data.table
syntax is compact because it allows for multiplevalue.var
columns and will take care of the spread for us. We can then skip themelt -> cast
process.Your answer was missing mutate id! Here is the solution using dplyr packge only.
Since tidyr 1.0.0
pivot_wider
is the recommended replacement ofspread
and you could do the following :Created on 2019-09-14 by the reprex package (v0.3.0)
The twist in this problem is that month is not unique by student, to solve this :
values_fn = list(A= list, B= list))
puts the multiple values in a listunchop(everything())
unnest the lists vertically, you can useunnest
as well here