Calculate difference between values by group and matched for time

Question

For each individual bird, I would like to calculate the difference between average hourly body temperature (Tb) measurements taken on different days (Tb_Periods). My goal is to be able to compare the change in Tb of BirdX from 0900 PreI to 09:00 DayI, 10:00 PreI to 10:00 PostI etc. The Tb_Period represents the time before manipulation(PreI), day-of-manipulation(DayI), and post-manipulation(PostI). My initial df:

    Date_Time           Bird_ID  Tb   Hour  Treatment  Tb_Period
    2018-04-04 11:01:39   3282   42.2  11    Control     PreI
    2018-04-04 12:31:51   3282   41.2  12    Control     PreI
    ....
    2018-04-05 09:16:54   3282   41.9   9    Control     DayI
    ....
    2018-04-06 08:09:57   3282   41.4   8    Control     PostI

What I have done so far: Each bird has body temperature measurements taken every 10 minutes over a timespan of 48hrs, so I first calculated the average Tb of each bird for each hour using dplyr:

    Tb_Averages <- TbData %>% group_by(Tb_Period, Hour, Bird_ID, Treatment)%>% 
                          summarize(meanHourTb = mean(Tb))

Resulting df:

         Tb_Period  Hour  Bird_ID  Treatment  meanHourTb
         PreI        9      3500       LPS    41.55000
         PreI        10     3500       LPS    41.75000       
         ...
         DayI        9      3500       LPS    40.88182
         DayI        10     3500       LPS    41.24000

Now what I would like is a df that looks like this:

         Bird_ID  Hour  Treatment  Tb_Diff 
          3500     9      LPS        -.67 (40.88-41.55)
          3282     9      LPS         .5 (e.g.)

Based on an answer from Calculate difference between values in consecutive rows by group, I have tried variations (with dplyrs arrange function) of:

           Tb_Averages <- Tb_Averages %>%
           group_by(Tb_Period, Bird_ID, Hour) %>%
           mutate(Tb_Diff = c(NA, diff(meanHourTb))))

but keeping getting NAs for the Tb_Diff column. What is the best approach to solve this problem (ideally using dplyr)?

How many levels does Tb_Period have, and what is their correct order? — jdobres
I can think of two dplyr-based ways, one with spreading the data and one with leads. Can you post a larger sample of your data from dput? — camille

jdobres jdobres · Accepted Answer · 2018-05-02T20:32:53

You're nearly there! The key is to convert Tb_Period to an ordered factor, such that PreI is treated as "less than" DayI, which is in turn less than PostI. Once this is established, we can group by each bird and hour, and sort by Tb_Period to ensure that differences are calculated in the correct order:

df <- read.table(text = 'Tb_Period  Hour  Bird_ID  Treatment  meanHourTb
PreI        9      3500       LPS    41.55000
PreI        10     3500       LPS    41.75000       
DayI        9      3500       LPS    40.88182
DayI        10     3500       LPS    41.24000', header = T, stringsAsFactors = F)

df <- df %>% 
  mutate(Tb_Period = factor(Tb_Period, c('PreI', 'DayI', 'PostI'), ordered = T)) %>% 
  group_by(Bird_ID, Hour) %>% 
  mutate(diff = meanHourTb - lag(meanHourTb, 1))

# A tibble: 4 x 6
# Groups:   Bird_ID, Hour [2]
  Tb_Period  Hour Bird_ID Treatment meanHourTb     diff
      <ord> <int>   <int>     <chr>      <dbl>    <dbl>
1      PreI     9    3500       LPS   41.55000       NA
2      PreI    10    3500       LPS   41.75000       NA
3      DayI     9    3500       LPS   40.88182 -0.66818
4      DayI    10    3500       LPS   41.24000 -0.51000

Calculate difference between values by group and matched for time

1 Answers