0

I have a dataset similar to the dataset described in this post and attempting for a solution also as described in the post using R. However, I am not able to create the desired table without aggregating the data.

Day <-    c(3, 3, 3, 3, 3, 3, 5, 5, 5, 5, 5, 5)
Treatment <- c("M1", "M1", "M2", "M2", "M3", "M3", "M1", "M1", "M2", "M2", "M3", "M3")
Values <- c(2, 3, 5, 7, 7, 9, 3, 3, 5, 5, 7, 2)

tx <- data.frame(Day,Treatment, Values)

    Day Treatment Values
1:    3        M1      2
2:    3        M1      3
3:    3        M2      5
4:    3        M2      7
5:    3        M3      7
6:    3        M3      9
7:    5        M1      3
8:    5        M1      3
9:    5        M2      5
10:   5        M2      5
11:   5        M3      7
12:   5        M3      2

I tried the following solution.

library(data.table)

dcast(setDT(tx), Day~Treatment, value.var='Values')

   Day M1 M2 M3
1:   3  2  2  2
2:   5  2  2  2

spread(tx, Treatment, Values)

Error: Each row of output must be identified by a unique combination of keys.
Keys are shared for 12 rows:

I would like to create the following output:

       Day   M1    M2    M3
 #1     3     2     5     7
 #2     3     3     7     9
 #3     5     3     5     7
 #4     5     3     5     2
RanonKahn
  • 853
  • 10
  • 34

1 Answers1

2

For each Day and Treatment create a unique ID column and cast the data to wide format.

library(dplyr)
library(tidyr)

tx %>%
  group_by(Day, Treatment) %>%
  mutate(row = row_number()) %>%
  pivot_wider(names_from = Treatment, values_from = Values) %>%
  ungroup %>%
  select(-row)

#    Day    M1    M2    M3
#  <dbl> <dbl> <dbl> <dbl>
#1     3     2     5     7
#2     3     3     7     9
#3     5     3     5     7
#4     5     3     5     2
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213