Aggregate and summarise character object with R

user_14735559 0
Viewed 61

I have a breeding productivity dataset:

df1
    # Nest.box Obs.type individual.number Clutch Chick.status
    # 1 Nest1 Egg 1 First NA
    # 2 Nest1 Egg 2 First NA
    # 3 Nest1 Egg 3 First NA
    # 4 Nest2 Egg 1 First NA
    # 5 Nest2 Egg 2 First NA
    # 6 Nest2 Egg 1 First NA
    # 7 Nest1 Chick 1 First Dead
    # 8 Nest1 Chick 2 First Fledged
    # 9 Nest2 Chick 1 First Fledged
    # 10 Nest2 Chick 2 First Fledged
    # 11 Nest2 Chick 1 Second Fledged
    # 12 Nest2 Chick 2 Second UNK

I want to summarise these data by aggregating by Nest.box and Clutch (shows the number of "Fledged" by nest.box, by clutch)

The wanted output would be something like this:

output
        # Nest.box Clutch Fledged
        # 1 Nest1 First 1
        # 2 Nest2 First 2
        # 3 Nest2 Second 1
summarize tidyr r
4 Answers

Another option is to filter then use count:

library(tidyverse)

df %>% 
  filter(Chick.status == "Fledged") %>% 
  count(Nest.box, Clutch)

Output

  Nest.box Clutch n
1    Nest1  First 1
2    Nest2  First 2
3    Nest2 Second 1

Data

df <- structure(list(Nest.box = c("Nest1", "Nest1", "Nest1", "Nest2", 
"Nest2", "Nest2", "Nest1", "Nest1", "Nest2", "Nest2", "Nest2", 
"Nest2"), Obs.type = c("Egg", "Egg", "Egg", "Egg", "Egg", "Egg", 
"Chick", "Chick", "Chick", "Chick", "Chick", "Chick"), individual.number = c(1L, 
2L, 3L, 1L, 2L, 1L, 1L, 2L, 1L, 2L, 1L, 2L), Clutch = c("First", 
"First", "First", "First", "First", "First", "First", "First", 
"First", "First", "Second", "Second"), Chick.status = c(NA, NA, 
NA, NA, NA, NA, "Dead", "Fledged", "Fledged", "Fledged", "Fledged", 
"UNK")), class = "data.frame", row.names = c("1", "2", "3", "4", 
"5", "6", "7", "8", "9", "10", "11", "12"))
user_152931910 
library(dplyr)    
df2 <- df1 %>%
  distinct() %>%
  group_by(Next.box, Clutch) %>%
  tally() %>%
  ungroup()
user_76230550

Here is a potential solution:

library(dplyr)

df <- read.table(text = "Nest.box Obs.type individual.number Clutch Chick.status
1 Nest1 Egg 1 First NA
2 Nest1 Egg 2 First NA
3 Nest1 Egg 3 First NA
4 Nest2 Egg 1 First NA
5 Nest2 Egg 2 First NA
6 Nest2 Egg 1 First NA
7 Nest1 Chick 1 First Dead
8 Nest1 Chick 2 First Fledged
9 Nest2 Chick 1 First Fledged
10 Nest2 Chick 2 First Fledged
11 Nest2 Chick 1 Second Fledged
12 Nest2 Chick 2 Second UNK", header = TRUE)

df %>%
  group_by(Nest.box, Clutch) %>%
  summarise(Fledged = sum(Chick.status == "Fledged", na.rm = TRUE))

#> # A tibble: 3 × 3
#> # Groups:   Nest.box [2]
#>   Nest.box Clutch Fledged
#>   <chr>    <chr>    <int>
#> 1 Nest1    First        1
#> 2 Nest2    First        2
#> 3 Nest2    Second       1

Created on 2022-04-04 by the reprex package (v2.0.1)

user_129573400

Using data.table:

library(data.table)
setDT(df)[Chick.status=="Fledged", .N, by=.(Nest.box, Clutch)]

##    Nest.box Clutch N
## 1:    Nest1  First 1
## 2:    Nest2  First 2
## 3:    Nest2 Second 1

This converts df to a data.table (setDT(df)), filters on Chick.status=='Fledged', and counts (.N) the rows grouped by Nest.box and Clutch.

user_29850070
Related