I have a data looks like this:
Id Time Type location count no_activities id
1 2014/10/11 A level1 20 10 1
2 2014/10/11 B level1 20 10 2
3 2014/10/11 C level1 10 9 3
4 2014/10/11 A level2 5 12 4
5 2014/10/11 B level2 6 5 5
6 2014/10/11 C level2 7 8 6
7 2014/10/11 A level3 7 8 7
8 2014/10/11 B level3 9 5 8
9 2014/10/11 C level3 20 23 9
...
...
Time Type location count no_activities id
101 2014/11/27 A level33 20 10 101
102 2014/11/27 B level33 30 10 102
103 2014/11/27 C level33 10 9 103
104 2014/11/27 A level34 10 12 104
105 2014/11/27 B level34 16 5 105
106 2014/11/27 C level34 5 8 106
107 2014/11/27 A level35 3 8 107
108 2014/11/27 B level35 1 5 108
109 2014/11/27 C level35 10 23 109
After grouping them by date and location, I want to spread the "Type"
column and combine the "count"
of the same group(based on time and location) into the same row, like this:
Id Time location A B C
1 2014/10/11 level1 20 20 10
4 2014/10/11 level2 5 6 7
7 2014/10/11 level3 7 9 20
10 2014/10/11 level4 ...
13 2014/10/11 level5 ...
16 2014/10/11 level6
19 2014/10/11 level7
22 2014/10/11 level8
25 2014/10/11 level9
...
I have read some similar questions but none seem to be solving my problem in this case. I tried these code:
df %>% dplyr::group_by(Time, location) %>% summarise_all(funs(toString(na.omit(.))))
which managed to group the observations by date and level but replaced whatever is missing in the columns with NA
rather than combining the
rows of same time and level into one:
Id Time location count no_activities id A B C
1 2014/10/11 level1 20 10 1 20 NA NA
2 2014/10/11 level1 20 10 2 NA 20 NA
3 2014/10/11 level1 10 9 3 NA NA 10
4 2014/10/11 level2 5 12 4 5 NA NA
5 2014/10/11 level2 6 5 5 NA 6 NA
6 2014/10/11 level2 7 8 6 NA NA 7
7 2014/10/11 level3 7 8 7 7 NA NA
8 2014/10/11 level3 9 5 8 NA 9 NA
9 2014/10/11 level3 20 23 9 NA NA 20
...
I also tried
df %>% reshape(df, v.names= c("A", "B", "C"), idvar=Id, timevar ="Time", direction="wide")
and
df %>%
group_by(Time, location) %>%
spread(Type, count)
none of them worked. Any help is appreciated. Thanks.
Further to the discussion in the comments above, do you mean something like this?
df %>%
group_by(Time, location) %>%
select(Time, location, Type, count) %>%
spread(Type, count) %>%
ungroup()
## A tibble: 6 x 5
# Time location A B C
# <fct> <fct> <int> <int> <int>
#1 2014/10/11 level1 20 20 10
#2 2014/10/11 level2 5 6 7
#3 2014/10/11 level3 7 9 20
#4 2014/11/27 level33 20 30 10
#5 2014/11/27 level34 10 16 5
#6 2014/11/27 level35 3 1 10
df <- read.table(text =
"Id Time Type location count no_activities id
1 '2014/10/11' A level1 20 10 1
2 '2014/10/11' B level1 20 10 2
3 '2014/10/11' C level1 10 9 3
4 '2014/10/11' A level2 5 12 4
5 '2014/10/11' B level2 6 5 5
6 '2014/10/11' C level2 7 8 6
7 '2014/10/11' A level3 7 8 7
8 '2014/10/11' B level3 9 5 8
9 '2014/10/11' C level3 20 23 9
101 '2014/11/27' A level33 20 10 101
102 '2014/11/27' B level33 30 10 102
103 '2014/11/27' C level33 10 9 103
104 '2014/11/27' A level34 10 12 104
105 '2014/11/27' B level34 16 5 105
106 '2014/11/27' C level34 5 8 106
107 '2014/11/27' A level35 3 8 107
108 '2014/11/27' B level35 1 5 108
109 '2014/11/27' C level35 10 23 109", header = T)