How to stack values in one column by factor name in another column in R-CodePudding

I have a data frame with 4 columns, here is the structure:

     Abundance DayNight   peak        Family
1         2      Day      Peak      Pangasiidae
2         1      Night    Non-Peak  Cyprinidae
3         1      Day      Peak      Botiidae
4         1      Night   Non-Peak   Botiidae
5         1      Day      Peak      Botiidae
6         4      Night    Peak      Cyprinidae

What I'm trying to do is stack the data so the names in the 'Family' column become column names and the corresponding values in 'Abundance' are stacked in the column with their corresponding family name. The 'peak' and 'DayNight' column need to be stacked with their corresponding 'Abundance" value. I have found a way to do this ~75% of the way, but it adds all the Abundance values in each Family by the peak and DayNight factor levels.

Here is the code I have and the output it has given me:

fish_t<- dcast(fish_com,  peak   DayNight ~ Family, value.var="Abundance")

and the output:

peak DayNight Ambassidae Anabantidae Ariidae Bagridae Belonidae Botiidae Butidae Channidae Cichlidae Clariidae Clupeidae Cobitidae Cynoglossidae Cyprinidae Danionidae
1 Non-Peak      Day         21           1       1       14         3       29       1         0         1         0        23        16            16        236         15
2 Non-Peak    Night         16           0       0        9         2       28       0         1         0         0        16        13            12        157         14
3     Peak      Day          9           2       0        3         2       64       0         0         0         1        46        16            14        304         15
4     Peak    Night         12           0       0        4         0       35       0         0         0         0        31        10            17        201         18

I just need to get the original dataframe stacked without it being summed by the 4 combinations of 'Peak' and "DayNight'.

Here is the reprex structure with more rows.

structure(list(Abundance = c(2L, 1L, 1L, 1L, 1L, 4L, 4L, 1L, 
1L, 6L, 3L, 14L, 81L, 90L, 4L, 2L, 1L, 1L, 32L, 1L, 1L, 16L, 
4L, 15L, 50L), DayNight = c("Day", "Day", "Day", "Day", "Day", 
"Day", "Day", "Day", "Day", "Day", "Day", "Day", "Day", "Day", 
"Day", "Day", "Day", "Day", "Day", "Day", "Day", "Day", "Day", 
"Day", "Day"), peak = c("Peak", "Peak", "Peak", "Peak", "Peak", 
"Peak", "Peak", "Peak", "Peak", "Peak", "Peak", "Peak", "Peak", 
"Peak", "Peak", "Peak", "Peak", "Peak", "Peak", "Peak", "Peak", 
"Peak", "Peak", "Peak", "Peak"), Family = c("Pangasiidae", "Cyprinidae", 
"Botiidae", "Botiidae", "Botiidae", "Cyprinidae", "Cyprinidae", 
"Pangasiidae", "Siluridae", "Engraulidae", "Ambassidae", "Cyprinidae", 
"Xenocyprididae", "Cyprinidae", "Sciaenidae", "Polynemidae", 
"Serrasalmidae", "Gyrinocheilidae", "Cobitidae", "Danionidae", 
"Siluridae", "Clupeidae", "Cynoglossidae", "Clupeidae", "Cyprinidae"
)), row.names = c(NA, 25L), class = "data.frame")

CodePudding user response：

I'm not sure about your expected output, but you may try

library(tidyverse)
df %>%
  group_by(DayNight, peak) %>%
  rownames_to_column() %>%
  pivot_wider(names_from = Family, values_from = Abundance) %>%
  select(-rowname)


   DayNight peak  Pangasiidae Cyprinidae Botiidae Siluridae Engraulidae Ambassidae Xenocyprididae
   <chr>    <chr>       <int>      <int>    <int>     <int>       <int>      <int>          <int>
 1 Day      Peak            2         NA       NA        NA          NA         NA             NA
 2 Day      Peak           NA          1       NA        NA          NA         NA             NA
 3 Day      Peak           NA         NA        1        NA          NA         NA             NA
 4 Day      Peak           NA         NA        1        NA          NA         NA             NA
 5 Day      Peak           NA         NA        1        NA          NA         NA             NA
 6 Day      Peak           NA          4       NA        NA          NA         NA             NA
 7 Day      Peak           NA          4       NA        NA          NA         NA             NA
 8 Day      Peak            1         NA       NA        NA          NA         NA             NA
 9 Day      Peak           NA         NA       NA         1          NA         NA             NA
10 Day      Peak           NA         NA       NA        NA           6         NA             NA