Formatting data (transposing)

Question

I am new to the R environment, and I would like to improve my "Manipulation data" skills. I have this dataset:

    structure(list(taxon = c("Acroloxus lacustris", "Ancylus fluviatilis", 
"Ancylus fluviatilis", "Asellus aquaticus", "Asellus aquaticus", 
"Asellus aquaticus", "Bithynia tentaculata", "Bryozoa Gen. sp.", 
"Chironomidae Gen. sp.", "Ephydatia fluviatilis", "Erpobdella octoculata", 
"Erpobdella octoculata", "Erpobdella octoculata", "Glossiphonia complanata", 
"Physella acuta", "Plumatella fungosa", "Plumatella fungosa", 
"Plumatella repens", "Radix balthica/labiata", "Radix balthica/labiata", 
"Radix balthica/labiata", "Sphaerium corneum", "Spongilla lacustris", 
"Spongillidae Gen. sp.", "Tubificidae Gen. sp."), year = c(1971, 
1969, 1971, 1968, 1969, 1971, 1971, 1971, 1969, 1971, 1968, 1969, 
1971, 1971, 1971, 1968, 1971, 1971, 1968, 1969, 1971, 1971, 1969, 
1971, 1971), abundance = c(12.5714285714286, 6, 15.5, 1, 13, 
100.333333333333, 2.11111111111111, 13, 6, 7, 20, 42.5, 22.875, 
1, 1, 20, 3.5, 2.66666666666667, 20, 42.5, 17.5789473684211, 
65, 6, 42.5, 1)), class = c("grouped_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -25L), groups = structure(list(taxon = c("Acroloxus lacustris", 
"Ancylus fluviatilis", "Asellus aquaticus", "Bithynia tentaculata", 
"Bryozoa Gen. sp.", "Chironomidae Gen. sp.", "Ephydatia fluviatilis", 
"Erpobdella octoculata", "Glossiphonia complanata", "Physella acuta", 
"Plumatella fungosa", "Plumatella repens", "Radix balthica/labiata", 
"Sphaerium corneum", "Spongilla lacustris", "Spongillidae Gen. sp.", 
"Tubificidae Gen. sp."), .rows = structure(list(1L, 2:3, 4:6, 
    7L, 8L, 9L, 10L, 11:13, 14L, 15L, 16:17, 18L, 19:21, 22L, 
    23L, 24L, 25L), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -17L), .drop = TRUE))

I would like to reorder this data set to the this format:

columns : Species1, species 2, Species 3 .... Rows: years ( 1968, 1969 and 1971, i.e. 3 rows) As you can note not all species appear for all years Looks like the next:

          Sp1     Sp2...
1968       1       0
1969       0       45
1971       10      0

But I don't know how to reorder it. I assume that %>% t() or mutate() could be useful... All help its appreciate, thanks for your time.

I loaded this, assigned it to a variable, and converted it to a dataframe without errors. You should look at the `tidyr` package, specifically the `spread`, `gather`, `separate`, `unite` functions. This is a good introduction: https://rstudio-education.github.io/tidyverse-cookbook/tidy.html — , Dec 23 '21 at 19:05
@LSM-DAT_Linux However, note that `pivot_wider` and `pivot_longer` are the preferred options to `spread` and `gather` now (which have been superseded). — AndrewGB, Dec 23 '21 at 19:11
@Andrew Gillreath-Brown not if I alias them ;) I much prefer spread, gather as verbs. pivot_wider, pivot_longer sound like they were named by an Excel cabal. — , Dec 23 '21 at 19:56

PaulS · Accepted Answer · 2021-12-23T18:59:21.017

Something like the following?

library(tidyverse)

df %>% 
  pivot_wider(id_cols = year, names_from = taxon, values_from = abundance) %>%
  arrange(year)

#> # A tibble: 3 × 18
#>    year `Acroloxus lacustris` `Ancylus fluvia… `Asellus aquati… `Bithynia tenta…
#>   <dbl>                 <dbl>            <dbl>            <dbl>            <dbl>
#> 1  1968                  NA               NA                 1             NA   
#> 2  1969                  NA                6                13             NA   
#> 3  1971                  12.6             15.5             100.             2.11
#> # … with 13 more variables: Bryozoa Gen. sp. <dbl>,
#> #   Chironomidae Gen. sp. <dbl>, Ephydatia fluviatilis <dbl>,
#> #   Erpobdella octoculata <dbl>, Glossiphonia complanata <dbl>,
#> #   Physella acuta <dbl>, Plumatella fungosa <dbl>, Plumatella repens <dbl>,
#> #   Radix balthica/labiata <dbl>, Sphaerium corneum <dbl>,
#> #   Spongilla lacustris <dbl>, Spongillidae Gen. sp. <dbl>,
#> #   Tubificidae Gen. sp. <dbl>

score 0 · Answer 2 · answered Dec 23 '21 at 18:59

0

data.table option

library(data.table)

dcast(setDT(df), year~taxon, value.var='abundance')

answered Dec 23 '21 at 18:59

AndrewGB

16,126
5
18
49

Formatting data (transposing)

2 Answers2