Make a relationship between variables based on one criterion in a loop and store multiple variables
I have a dataframe with 3 trap sessions in each season of the year for 3 years (the real database has over 100 seasons and 800 capture seasons). For each trap season, I have 3 binomial variables ("Non_Breeder", "Potential_Breeder" and "Breeding").
# example
Year <- c(rep(2000,12), rep(2001,12), rep(2002,12))
Season <- c(rep (seq(1:4), each=3,3))
Trap_Session <- seq(1:36)
Non_Breeder <- (rbinom(36, 1, prob=0.5))
Potential_Breeder <- (rbinom(36, 1, prob=0.8))
Breeding <- (rbinom(36, 1, prob=0.4))
Month <- sample(12, 36, replace = TRUE)
db <- cbind (Year, Season, Trap_Session, Non_Breeder, Potential_Breeder, Breeding)
db <- as.data.frame (db)
I would like to calculate "(Potential_Breeder + Breeding) / (Non_Breeder + Potential_Breeder + Breeding)" for each season keeping the variables Year, Season, and Ratio.
I tried to use the function table
, but I don't know how to automate the creation of a cycle for each season and save the Year, Season and Ratio variables.
For example: If I have the following data:
Year Season Trap_Session Non_Breeder Potential_Breeder Breeding
1 2000 1 1 1 1 0
2 2000 1 2 1 1 0
3 2000 1 3 0 1 0
4 2000 2 4 0 1 1
5 2000 2 5 1 1 1
6 2000 2 6 1 1 1
I would like to get:
Year Season Ratio
2000 1 0.6 # (3/5)
2000 2 0.75 # (6/8)
#Explanation of the calculation
# 2000 Season 1
(3 Potential_Breeder / 5 (3Potential_Breeder+2 Non_Breeder)
# 2000 Season 2
(3Potential_Breeder + 2Breeding / 2Non_Breeder + 3Potential_Breeder +2Breeding)
Does anyone know how to do this?
source to share
try this:
library(data.table)
setDT(db)[ , .("Ratio" = sum(Potential_Breeder, Breeding) /
sum(Non_Breeder, Potential_Breeder, Breeding)), by = .(Year, Season)]
this adds a variable called "Ratio" (call it whatever you like) to your existing data grouping by year and season,
the same with dplyr:
library(dplyr)
group_by(db, Year, Season) %>% summarise("Ratio" = sum(Potential_Breeder, Breeding) /
sum(Non_Breeder, Potential_Breeder, Breeding))
which gives the following result, given the db in your OP:
Year Season Ratio
1: 2000 1 0.8000000
2: 2000 2 0.5000000
3: 2000 3 0.6000000
4: 2000 4 0.8000000
5: 2001 1 0.6666667
6: 2001 2 0.8000000
7: 2001 3 0.8000000
8: 2001 4 0.6000000
9: 2002 1 1.0000000
10: 2002 2 0.5000000
11: 2002 3 0.8571429
12: 2002 4 0.6666667
source to share
The month is missing from your data construct! Still one solution:
# Columns you want to group by
grp_cols <- names(db)[-c(3,4,5,6)]
# Convert character vector to list of symbols
dots <- lapply(grp_cols, as.symbol)
db %>%
group_by_(.dots = dots) %>%
summarise(SumNB = sum(Non_Breeder), SumB = sum(Breeding), SumPB = sum(Potential_Breeder)) %>%
mutate(Ratio = (SumPB + SumB) / (SumNB + SumPB + SumB))
Should do it.
EDIT: As per your 3rd comment on grrgrrblas' answer, this script will sum all the counts for B, NB and PB and then calculate the coefficient.
source to share