R: new column of row difference from max value of another column according to group

user_9291652 0
Viewed 75

The title of the question may be unclear but I hope these codes will clearly demonstrate my problem.

I have a data frame with three columns. $sensor (A and B); $hour of the day (0-4); and the $value taken by the temperature (1-5).

 new.df <- data.frame(
 sensor = c("A", "A", "A", "A", "A", "B", "B", "B", "B", "B"), 
 hour_day = c(0:4, 0:4),
 value = c(1, 1, 3, 1, 2, 1, 3, 4, 5, 2)

new.df

   sensor hour_day value
1       A        0     1
2       A        1     1
3       A        2     3
4       A        3     1
5       A        4     2
6       B        0     1
7       B        1     3
8       B        2     4
9       B        3     5
10      B        4     2

I want to make a new column that indicates the difference in hour from the hour with maximum value according to the sensor.

Desired result

   sensor value hour_day hour_from_max_hour
1       A     1        0                 -2
2       A     1        1                 -1
3       A     3        2                  0
4       A     1        3                  1
5       A     2        4                  2
6       B     1        0                 -3
7       B     3        1                 -2
8       B     4        2                 -1
9       B     5        3                  0
10      B     2        4                  1

Note that for sensor A (max = hour 2), and sensor B (max = hour 3). I just want a new column that tells me how many hour different is that sensor-value group is from the max sensor-value.

Thank you in advance and please let me know if I can provide more information.

EDIT Previous answer were very helpful, I forgot that there is one more variable (day) in this problem. Also, some times there is more than one maximum in a column. When this is the case, I would like to base the difference on the first maximum.

df_add <- data.frame(
  sensor = c("A", "A", "A", "A", "A", "B", "B", "B", "B", "B",
               "A", "A", "A", "A", "A", "B", "B", "B", "B", "B"), 
    hour_day = c(0:4, 0:4, 0:4, 0:4),
    value = c(1, 1, 3, 3, 2,
              3, 2, 4, 4, 1,
              1, 5, 6, 6, 2,
              2, 1, 3, 3, 1),
    day =   c(1, 1, 1, 1, 1, 
              1, 1, 1, 1, 1,
              2, 2, 2, 2, 2,
              2, 2, 2, 2, 2)
  
)

df_add

   sensor hour_day value day
1       A        0     1   1
2       A        1     1   1
3       A        2     3   1
4       A        3     3   1
5       A        4     2   1
6       B        0     3   1
7       B        1     2   1
8       B        2     4   1
9       B        3     4   1
10      B        4     1   1
11      A        0     1   2
12      A        1     5   2
13      A        2     6   2
14      A        3     6   2
15      A        4     2   2
16      B        0     2   2
17      B        1     1   2
18      B        2     3   2
19      B        3     3   2
20      B        4     1   2
dplyr max r
3 Answers

A simple pipe can do it. All you have to do is to get max(value) in the mutate instruction.

new.df %>%
    group_by(sensor) %>%
    mutate(hour_from_max_hour = hour_day - hour_day[which(value == max(value))[1]])
## A tibble: 10 x 4
## Groups:   sensor [2]
#   sensor hour_day value hour_from_max_hour
#   <fct>     <int> <dbl>              <int>
# 1 A             0    1.                 -2
# 2 A             1    1.                 -1
# 3 A             2    3.                  0
# 4 A             3    1.                  1
# 5 A             4    2.                  2
# 6 B             0    1.                 -3
# 7 B             1    3.                 -2
# 8 B             2    4.                 -1
# 9 B             3    5.                  0
#10 B             4    2.                  1
user_82454060 
library(dplyr)

new.df.2 <- 
  # First get the hours with the max values
  new.df %>%
  group_by(sensor) %>%
  filter(value == max(value)) %>%
  ungroup() %>%
  select(sensor, max_hour = hour_day) %>%   # This renames hour_day as max_hour
  # Now join that to the original table and make the calculation
  right_join(new.df) %>%
  mutate(hour_from_max_hour = hour_day - max_hour)

Result:

new.df.2
# A tibble: 10 x 5
   sensor max_hour hour_day value hour_from_max_hour
   <fct>     <int>    <int> <dbl>              <int>
 1 A             2        0     1                 -2
 2 A             2        1     1                 -1
 3 A             2        2     3                  0
 4 A             2        3     1                  1
 5 A             2        4     2                  2
 6 B             3        0     1                 -3
 7 B             3        1     3                 -2
 8 B             3        2     4                 -1
 9 B             3        3     5                  0
10 B             3        4     2                  1
user_68518250

This is probably how I would do it:

library(plyr)
dd = ddply(new.df, .(sensor), summarize, 
           max.value = max(value),
           hour.of.max = hour_day[which.max(value)])
new.df = merge(new.df, dd, all.x=T, by='sensor')
new.df$hour_from_max_hour = new.df$hour_day - new.df$hour.of.max

Gave you a couple extra columns, but you can delete them:

   sensor hour_day value max.value hour.of.max hour_from_max_hour
1       A        0     1         3           2                 -2
2       A        1     1         3           2                 -1
3       A        2     3         3           2                  0
4       A        3     1         3           2                  1
5       A        4     2         3           2                  2
6       B        0     1         5           3                 -3
7       B        1     3         5           3                 -2
8       B        2     4         5           3                 -1
9       B        3     5         5           3                  0
10      B        4     2         5           3                  1
user_89296980
Related