Can I combine rows in a wide data set so there is one per 'site'?

[Alexs]

Hi all, I have a data set of average species covers in 19 different sites. However when grouping and summarising, I have ended up with multiple rows for the same site and NA's in between, see photo below.

Is there a way I can combine the rows so each site only has one row, with each column containing a value for species cover? I understand that there will still be NA's, as not every species was found at every site, but collapsing rows into one per site, and then changing NA's to 0's after will allow me to perform NMDS ordination.

Thanks so much!

[FJCC]

If I understand your data structure correctly, I think you want to make the data frame longer, filter out the NA values and then make it wide again.

library(tidyr)
library(dplyr, warn.conflicts = FALSE)
DF <- data.frame(Site = rep(c("X", "Y", "Z"), each = 4),
                 A = c(NA, 1, NA, NA, NA, NA, NA, NA, 2, NA, NA, NA),
                 B = c(NA, NA, NA, NA, NA, NA, 5, NA, NA, 6, NA, NA), 
                 C = c(NA, NA, NA, 3, NA, NA, NA, NA, NA, NA, NA, 4))
DF
#>    Site  A  B  C
#> 1     X NA NA NA
#> 2     X  1 NA NA
#> 3     X NA NA NA
#> 4     X NA NA  3
#> 5     Y NA NA NA
#> 6     Y NA NA NA
#> 7     Y NA  5 NA
#> 8     Y NA NA NA
#> 9     Z  2 NA NA
#> 10    Z NA  6 NA
#> 11    Z NA NA NA
#> 12    Z NA NA  4
DF2 <- DF %>% pivot_longer(cols = A:C, names_to = "species", values_to = "Value") %>% 
  filter(!is.na(Value)) %>% 
  pivot_wider(names_from = species, values_from = Value)
DF2
#> # A tibble: 3 x 4
#>   Site      A     C     B
#>   <chr> <dbl> <dbl> <dbl>
#> 1 X         1     3    NA
#> 2 Y        NA    NA     5
#> 3 Z         2     4     6

^{Created on 2020-08-27 by the reprex package (v0.3.0)}

[Alexs]

Thanks so much! Sorted!!