I am trying to tackle a problem for a while now. I have a dataset that has species and their abundances grouped per year and per site. There are three years and seven sites. Data now looks like this (example):
Year Site Spec Abundance
2010 1 INHET 12
2012 1 INHET 45
2016 1 INHET 2
2010 2 INLEP 6
2012 2 INLEP 15
2016 2 INLEP 18
I want it to look like this:
Year Site INHET INLEP
2010 1 12 0
2010 2 0 6
2012 1 45 0
2012 2 0 15
2016 1 2 0
2016 2 0 18
I have been trying all sorts of things, but can't figure out how to transform the dataset to the desired format. I dont want to use excel to do it.
the dataset:
structure(list(Jaar = c(2010L, 2012L, 2016L, 2012L, 2010L, 2012L,
2016L, 2010L, 2012L, 2012L, 2010L, 2012L, 2016L, 2010L, 2012L,
2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L,
2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L,
2016L, 2010L, 2012L, 2016L, 2012L, 2016L, 2010L, 2010L, 2010L,
2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L,
2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L,
2012L, 2016L, 2012L, 2012L, 2010L, 2012L, 2016L, 2010L, 2016L,
2010L, 2012L, 2016L, 2010L, 2012L, 2012L, 2012L, 2010L, 2012L,
2016L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L,
2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2010L,
2012L, 2016L, 2010L, 2012L, 2012L, 2010L, 2012L, 2016L, 2010L,
2012L, 2012L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L,
2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L,
2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2012L,
2016L, 2012L, 2016L, 2012L, 2016L, 2010L, 2012L, 2016L, 2012L,
2016L, 2010L, 2012L, 2010L, 2012L, 2010L, 2012L, 2016L, 2010L,
2012L, 2016L, 2010L, 2012L, 2010L, 2010L, 2012L, 2016L, 2010L,
2012L, 2012L, 2016L, 2010L, 2012L, 2016L, 2016L, 2010L, 2012L,
2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2016L,
2010L, 2012L, 2016L, 2016L, 2010L, 2012L, 2016L, 2010L, 2016L,
2010L, 2012L, 2010L, 2012L, 2010L, 2012L, 2012L, 2012L, 2016L,
2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L,
2010L, 2012L, 2010L, 2012L, 2012L, 2016L, 2010L, 2012L, 2010L,
2010L, 2016L, 2010L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L,
2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L,
2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2012L, 2016L, 2016L,
2010L, 2012L, 2010L, 2012L, 2016L, 2010L, 2012L, 2016L, 2010L,
2012L, 2016L, 2010L, 2012L, 2010L, 2012L, 2016L, 2010L, 2012L,
2016L), Meetobject_Code = structure(c(1L, 1L, 1L, 2L, 3L, 3L,
3L, 4L, 4L, 5L, 6L, 6L, 6L, 7L, 7L, 7L, 1L, 1L, 1L, 2L, 2L, 2L,
3L, 3L, 3L, 4L, 4L, 4L, 5L, 5L, 5L, 6L, 6L, 6L, 7L, 7L, 7L, 1L,
2L, 6L, 7L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 4L, 5L,
5L, 5L, 6L, 6L, 6L, 7L, 7L, 7L, 6L, 7L, 1L, 1L, 1L, 2L, 2L, 3L,
3L, 3L, 4L, 4L, 5L, 6L, 7L, 7L, 7L, 1L, 1L, 2L, 2L, 2L, 3L, 3L,
3L, 4L, 4L, 4L, 5L, 5L, 5L, 6L, 6L, 7L, 7L, 7L, 1L, 1L, 2L, 3L,
3L, 3L, 4L, 4L, 3L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 4L,
4L, 5L, 5L, 5L, 6L, 6L, 6L, 7L, 7L, 7L, 1L, 1L, 1L, 2L, 2L, 3L,
3L, 4L, 4L, 5L, 5L, 5L, 6L, 6L, 7L, 7L, 1L, 1L, 2L, 2L, 2L, 3L,
3L, 3L, 4L, 4L, 5L, 6L, 6L, 6L, 7L, 7L, 1L, 1L, 2L, 2L, 2L, 3L,
4L, 4L, 4L, 5L, 5L, 5L, 6L, 6L, 6L, 7L, 7L, 1L, 1L, 1L, 2L, 3L,
3L, 3L, 4L, 4L, 5L, 5L, 6L, 6L, 7L, 7L, 1L, 1L, 1L, 2L, 2L, 2L,
3L, 3L, 3L, 4L, 4L, 4L, 5L, 5L, 6L, 6L, 7L, 7L, 2L, 3L, 4L, 5L,
5L, 6L, 7L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 4L, 5L, 5L,
5L, 6L, 6L, 6L, 7L, 7L, 7L, 1L, 1L, 3L, 1L, 1L, 2L, 2L, 2L, 3L,
3L, 3L, 4L, 4L, 4L, 5L, 5L, 6L, 6L, 6L, 7L, 7L, 7L), .Label = c("KRW11_01",
"KRW11_02", "KRW11_03", "KRW11_05", "KRW11_06", "KRW11_07", "KRW11_10"
), class = "factor"), GROUP = structure(c(1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L,
3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L,
4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 5L, 5L, 6L, 6L, 6L, 6L, 6L,
6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 7L, 7L, 7L, 7L, 7L, 7L,
7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 8L, 8L, 8L,
8L, 8L, 8L, 8L, 8L, 9L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L,
10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L,
11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L,
11L, 11L, 11L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 12L, 12L, 12L, 12L, 13L, 13L, 13L, 13L, 13L, 13L, 13L,
13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 14L, 14L, 14L,
14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 14L, 15L,
16L, 16L, 16L, 16L, 16L, 16L, 16L, 16L, 16L, 16L, 16L, 16L, 16L,
16L, 16L, 16L, 16L, 17L, 17L, 17L, 17L, 17L, 17L, 17L, 18L, 18L,
18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 18L,
18L, 18L, 18L, 18L, 18L, 19L, 19L, 19L, 20L, 20L, 20L, 20L, 20L,
20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L,
20L), .Label = c("APHIR", "APOLI", "APTUR", "ARACH", "COLMB",
"CRAMP", "CRISO", "CRMYS", "CRREM", "IDCHI", "IDREM", "INCOL",
"INEPH", "INHET", "INLEP", "INODO", "INREM", "INTRI", "MOBIV",
"MOGAS"), class = "factor"), `as.integer(Waarde_Berekend)` = c(1L,
436L, 138L, 1L, 39L, 39L, 2L, 40L, 1L, 38L, 39L, 2L, 1L, 95L,
93L, 39L, 115L, 33L, 334L, 197L, 146L, 13L, 1L, 1L, 278L, 35L,
95L, 83L, 192L, 55L, 59L, 4L, 38L, 144L, 2L, 55L, 15L, 102L,
1L, 39L, 64L, 40L, 633L, 5L, 182L, 418L, 295L, 43L, 152L, 41L,
587L, 271L, 319L, 282L, 339L, 117L, 291L, 550L, 130L, 41L, 122L,
83L, 1L, 1L, 38L, 177L, 55L, 55L, 38L, 33L, 39L, 58L, 81L, 108L,
93L, 55L, 125L, 196L, 76L, 27L, 130L, 2L, 144L, 49L, 65L, 2L,
81L, 197L, 43L, 67L, 5L, 39L, 1L, 177L, 86L, 161L, 174L, 1L,
76L, 22L, 38L, 37L, 39L, 64L, 81L, 55L, 1L, 290L, 267L, 289L,
614L, 411L, 163L, 1L, 290L, 367L, 203L, 299L, 250L, 418L, 148L,
91L, 244L, 253L, 231L, 281L, 329L, 100L, 114L, 39L, 25L, 2L,
55L, 1L, 38L, 55L, 1L, 71L, 49L, 1L, 55L, 38L, 1L, 66L, 55L,
254L, 2L, 2L, 91L, 88L, 1L, 1L, 162L, 38L, 77L, 39L, 116L, 1L,
4L, 39L, 261L, 145L, 106L, 84L, 39L, 54L, 55L, 65L, 172L, 2L,
174L, 126L, 82L, 263L, 86L, 1L, 52L, 39L, 271L, 235L, 56L, 66L,
38L, 153L, 2L, 1L, 1L, 41L, 1L, 40L, 1L, 3L, 38L, 90L, 78L, 2L,
1L, 60L, 1L, 2L, 12L, 82L, 78L, 38L, 1L, 2L, 76L, 102L, 134L,
2L, 1L, 1L, 70L, 1L, 1L, 38L, 55L, 298L, 5L, 223L, 464L, 140L,
1L, 182L, 1L, 70L, 302L, 237L, 120L, 42L, 252L, 210L, 70L, 105L,
112L, 2L, 187L, 1L, 56L, 77L, 66L, 1032L, 96L, 113L, 122L, 495L,
2L, 1L, 408L, 42L, 3L, 105L, 29L, 302L, 210L, 57L, 588L, 258L,
143L)), row.names = c(NA, -259L), class = "data.frame")
As @duckmayr suggests, pivot_wider would be appropriate for this.
Based on your example data (calling the data frame df):
library(tidyr)
pivot_wider(df, id_cols = c(Jaar, Meetobject_Code), names_from = GROUP, values_from = Waarde_Berekend)
Output
# A tibble: 21 x 22
Jaar Meetobject_Code APHIR APOLI APTUR ARACH COLMB CRAMP CRISO CRMYS CRREM IDCHI IDREM INCOL INEPH INHET INLEP INODO INREM INTRI MOBIV MOGAS
<int> <fct> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1 2010 KRW11_01 1 115 NA 40 NA 38 NA 76 NA 290 114 55 NA 39 NA NA NA NA NA 66
2 2012 KRW11_01 436 33 102 633 NA 177 27 22 NA 267 39 254 261 271 38 90 NA 298 1 1032
3 2016 KRW11_01 138 334 NA 5 NA 55 130 NA NA 289 25 NA 145 235 NA 78 NA 5 56 NA
4 2012 KRW11_02 1 146 NA 418 NA NA 144 38 NA 411 2 2 84 NA NA 1 NA 464 NA 113
5 2010 KRW11_03 39 1 NA 43 NA 33 65 37 NA 1 NA 88 NA 66 NA 1 NA 1 NA 495
6 2012 KRW11_03 39 1 NA 152 NA 39 2 39 1 290 1 1 NA 38 NA 2 1 182 NA 2
7 2016 KRW11_03 2 278 NA 41 NA 58 81 64 NA 367 38 1 54 153 NA 12 NA 1 77 1
8 2010 KRW11_05 40 35 NA 587 NA 81 197 81 NA 203 NA 162 55 2 NA 82 70 70 NA 408
9 2012 KRW11_05 1 95 NA 271 NA 108 43 55 NA 299 55 38 65 NA NA 78 NA 302 NA 42
10 2012 KRW11_06 38 55 NA 339 NA 93 39 NA NA 148 49 NA 174 41 NA 2 NA 42 NA 29
# … with 11 more rows
Note: Column name Waarde_Berekend used instead of as.integer(Waarde_Berekend).