CODEWITHSUNDEEP
rif-statementdplyrgrouping
Blundering Ecologist
Blundering Ecologist

Reputation: 1315

Calculating rates when data is in long form

A sample of my data is available here.

I am trying to calculate the growth rate (change in weight (wt) over time) for each squirrel.

When I have my data in wide format:

squirrel    fieldBirthDate  date1       date2       date3   date4   date5   date6   age1    age2    age3    age4    age5    age6    wt1     wt2     wt3 wt4 wt5 wt6 litterid
22922         2017-05-13    2017-05-14  2017-06-07  NA      NA      NA      NA      1       25      NA      NA      NA      NA      12      52.9    NA  NA  NA  NA  7684
22976         2017-05-13    2017-05-16  2017-06-07  NA      NA      NA      NA      3       25      NA      NA      NA      NA      15.5    50.9    NA  NA  NA  NA  7692
22926         2017-05-13    2017-05-16  2017-06-07  NA      NA      NA      NA      0       25      NA      NA      NA      NA      10.1    48      NA  NA  NA  NA  7719

I am able to calculate growth rate with the following code:

library(dplyr)

#growth rate between weight 1 and weight 3, divided by age when weight 3 is recorded
growth <- growth %>%
    mutate (g.rate=((wt3-wt1)/age3))

#growth rate between weight 1 and weight 2, divided by age when weight 2 is recorded
merge.growth <- merge.growth %>%
    mutate (g.rate=((wt2-wt1)/age2))

However, when the data is in long format (a format needed for the analysis I am running afterwards):

squirrel    litterid    date    age wt
22922       7684    2017-05-13  0   NA
22922       7684    2017-05-14  1   12
22922       7684    2017-06-07  25  52.9
22976       7692    2017-05-13  1   NA
22976       7692    2017-05-16  3   15.5
22976       7692    2017-06-07  25  50.9
22926       7719    2017-05-14  0   10.1
22926       7719    2017-06-08  25  48

I cannot use the mutate function I used above. I am hoping to create a new column that includes growth rate as follows:

squirrel    litterid    date    age wt      g.rate
22922       7684    2017-05-13  0   NA      NA
22922       7684    2017-05-14  1   12      NA
22922       7684    2017-06-07  25  52.9    1.704
22976       7692    2017-05-13  1   NA      NA
22976       7692    2017-05-16  3   15.5    NA
22976       7692    2017-06-07  25  50.9    1.609
22926       7719    2017-05-14  0   10.1    NA
22926       7719    2017-06-08  25  48      1.516
22758       7736    2017-05-03  0   8.8     NA  
22758       7736    2017-05-28  25  43      1.368
22758       7736    2017-07-05  63  126     1.860
22758       7736    2017-07-23  81  161     1.879
22758       7736    2017-07-26  84  171     1.930

I have been calculating the growth rates (growth between each wt and the first time it was weighed) in excel, however I would like to do the calculations in R instead since I have a large number of squirrels to work with. I suspect if else loops might be the way to go here, but I am not well versed in that sort of coding. Any suggestions or ideas are welcome!

Upvotes: 1

Views: 79

Answers (2)

Julien Massardier
Julien Massardier

Reputation: 1466

alternative using data.table and its function "shift" that takes the previous row

library(data.table)
df= data.table(df)

df[,"growth":=(wt-shift(wt,1))/age,by=.(squirrel)]

enter image description here

Upvotes: 1

C. Braun
C. Braun

Reputation: 5191

You can use group_by to calculate this for each squirrel:

group_by(df, squirrel) %>% 
    mutate(g.rate = (wt - nth(wt, which.min(is.na(wt)))) / 
                    (age - nth(age, which.min(is.na(wt)))))

That leaves NaNs where the age term is zero, but you can change those to NAs if you want with df$g.rate[is.nan(df$g.rate)] <- NA.

Upvotes: 4

Related Questions