The recast fun to spread the rows is omitting key columns in R?

user_6820344 0
Viewed 39

I am trying to spread my data such that months are the columns associated with both site and spx. I tried to use recast but I lose the informaton about species. What do I do to get the expected output (attached)?

set.seed(111)
month <- rep(c("J","F","M"), each = 6)
site <- rep(c(1,2,3,4,5,6), times = 3)
spA <- rnorm(18,0,2)
spB <- rnorm(18,0,2)
spC <- rnorm(18,0,2)
spD <- rnorm(18,0,2)

df <- data.frame(month, site, spA, spB, spC, spD)

df.test <- reshape2::recast(df, site ~  month)
Here is what I am getting. 

  site F J M
1    1 5 5 5
2    2 5 5 5
3    3 5 5 5
4    4 5 5 5
5    5 5 5 5
6    6 5 5 5

#Expected output (It's dummy data)

site sp  J  F  M
1    A   5  6   7
1    B   2  3   4
..
6    D   1  2   3
reshape transpose dataframe r
1 Answers

If the intention is not to aggregate, but just transpose, then we can use pivot_longer to reshape to long and then reshape back to wide with pivot_wider

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(cols = starts_with('sp'), names_prefix = 'sp',
    names_to = 'sp') %>% 
  pivot_wider(names_from = month, values_from = value)

-output

# A tibble: 24 × 5
    site sp         J      F      M
   <dbl> <chr>  <dbl>  <dbl>  <dbl>
 1     1 A      0.470 -2.99   3.69 
 2     1 B     -2.39   0.653 -6.23 
 3     1 C     -0.232 -2.72   4.97 
 4     1 D      0.350 -0.433  0.405
 5     2 A     -0.661 -2.02   0.788
 6     2 B      0.728  1.20  -1.88 
 7     2 C      0.669  0.962  3.92 
 8     2 D     -1.69   2.89  -1.61 
 9     3 A     -0.623 -1.90   1.60 
10     3 B      0.723 -3.68   2.80 
# … with 14 more rows

Or using recast - specify the id.var and then include the variable also in the formula

library(reshape2)
 reshape2::recast(df, site + variable  ~  month, id.var = c("month", "site"))
   site variable           F          J          M
1     1      spA -2.99485331  0.4704414  3.6912725
2     1      spB  0.65309848 -2.3872179 -6.2264346
3     1      spC -2.72380897 -0.2323101  4.9713231
4     1      spD -0.43285732  0.3501913  0.4046144
5     2      spA -2.02037684 -0.6614717  0.7881082
6     2      spB  1.19650840  0.7283735 -1.8827148
7     2      spC  0.96224916  0.6685120  3.9199634
8     2      spD  2.89295633 -1.6945355 -1.6123984
9     3      spA -1.89695121 -0.6232476  1.5950570
10    3      spB -3.68306860  0.7233249  2.8005176
11    3      spC  1.48394325 -1.2417162  0.3833268
12    3      spD  0.81941960  1.9564633  0.5892684
13    4      spA -0.98792443 -4.6046913 -3.1333307
14    4      spB  5.43611120  0.6939287 -3.2409401
15    4      spC  0.05564925 -2.6196898  3.1050885
16    4      spD  1.82183314  3.6117365  2.8097662
...
user_37322710
Related