Creating wide data for counts when each row is year/site/zone

user_1009823 0
Viewed 59

I'm working with data in the following format

Year  Site Zone Species
2010    A   a   cat
2010    A   a   dog
2010    A   b   cat
2010    B   a   rabbit
2010    B   a   cat
2010    B   b   cat
2011    A   a   dog
2011    A   a   cat
2011    A   b   rabbit
2011    B   a   cat
2011    B   b   dog
2011    B   b   cat

and I would like to obtain:


Year    Site Zone   cat dog rabbit
2010    A     a     1   1   0
2010    A     b     1   0   0
2010    B     a     1   0   1
2010    B     b     1   0   0
2011    A     a     1   1   0
2011    A     b     0   0   1
2011    B     a     1   0   0
2011    B     b     1   1   0

What is the best way to do this in R with dplyr (or otherwise) ?

Here is a dput of the data in long format:

structure(list(Year = c(2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
2011L, 2011L, 2011L, 2011L, 2011L, 2011L), Site = c("A", "A", 
"A", "B", "B", "B", "A", "A", "A", "B", "B", "B"), Zone = c("a", 
"a", "b", "a", "a", "b", "a", "a", "b", "a", "b", "b"), Species = c("cat", 
"dog", "cat", "rabbit", "cat", "cat", "dog", "cat", "rabbit", 
"cat", "dog", "cat")), class = "data.frame", row.names = c(NA, 
-12L))
dplyr r
4 Answers

Using pivot_wider -

tidyr::pivot_wider(df, names_from = Species, values_from = Species, 
                   values_fn = length, values_fill = 0)

#   Year Site  Zone    cat   dog rabbit
#  <int> <chr> <chr> <int> <int>  <int>
#1  2010 A     a         1     1      0
#2  2010 A     b         1     0      0
#3  2010 B     a         1     0      1
#4  2010 B     b         1     0      0
#5  2011 A     a         1     1      0
#6  2011 A     b         0     0      1
#7  2011 B     a         1     0      0
#8  2011 B     b         1     1      0

Or with data.table -

library(data.table)
dcast(setDT(df), Year + Site + Zone ~ Species, fun.aggregate = length)
user_39629140

Here is another working solution:

library(tidyr)
library(dplyr)
df %>% 
  group_by(Year, Site, Zone) %>% 
  add_count(Species) %>% 
  pivot_wider(
    names_from = Species, 
    values_from = n, 
  ) %>% 
  mutate(across(everything(), .fns = ~replace_na(.,0)))

Output

   Year Site  Zone    cat   dog rabbit
  <int> <chr> <chr> <dbl> <dbl>  <dbl>
1  2010 A     a         1     1      0
2  2010 A     b         1     0      0
3  2010 B     a         1     0      1
4  2010 B     b         1     0      0
5  2011 A     a         1     1      0
6  2011 A     b         0     0      1
7  2011 B     a         1     0      0
8  2011 B     b         1     1      0
user_133216470

You can also use the following solution, albeit not much different from the ones posted. I learned this from the great David Robinson in one of his tidytuesday sessions:

library(dplyr)
library(tidyr)

df %>%
  mutate(id = 1) %>%
  pivot_wider(names_from = Species, values_from = id, 
              values_fill = 0)

# A tibble: 8 x 6
   Year Site  Zone    cat   dog rabbit
  <int> <chr> <chr> <dbl> <dbl>  <dbl>
1  2010 A     a         1     1      0
2  2010 A     b         1     0      0
3  2010 B     a         1     0      1
4  2010 B     b         1     0      0
5  2011 A     a         1     1      0
6  2011 A     b         0     0      1
7  2011 B     a         1     0      0
8  2011 B     b         1     1      0
user_143145200

Using acast from reshape2

library(reshape2)
acast(df1, ... ~ Species, length)

Or use table from base R

table(do.call(paste0, df1[1:3]), df1$Species)
user_37322710
Related