Group By Row Value Difference

Question

I have a table that has structure like: Year, Month, ValueA, ValueB, ValueC, etc.. I want to group the table by Year and Month, but aggregate based on the difference in column values.

Year Month ValueA ValueB ValueC
2016  1      40     53     49
2017  2      29     31     26
2016  1      25     20     31
2017  2      22     30     29

I want to output a table that looks like:

Year Month ValueA ValueB ValueC
2016  1      15     33     18
2017  2      7       1      3

How would I go about this? Any help is much appreciated.

score 4 · Accepted Answer · answered Feb 24 '17 at 16:03

4

We can use base R aggregate and group by Year and Month to calculate the difference between the two rows.

abs(aggregate(.~Year + Month, df, diff))

#  Year Month ValueA ValueB ValueC
#1 2016     1     15     33     18
#2 2017     2      7      1      3

answered Feb 24 '17 at 16:03

Ronak Shah

377,200
20
156
213

score 2 · Answer 2 · answered Feb 24 '17 at 16:02

Here is a way using the dplyr package:

library(tidyverse)
df <- data.frame(Year = c(2016, 2017, 2016, 2017),
             Month = c(1, 2, 1, 2),
             ValueA = c(40, 29, 25, 22),
             ValueB = c(53, 31, 20, 30),
             ValueC = c(49, 26, 31, 29))

df1 <- df %>%
  group_by(Year, Month) %>%
  summarize(ValueA = abs(diff(ValueA)), ValueB = abs(diff(ValueB)), ValueC = abs(diff(ValueC)))

score 1 · Answer 3 · edited May 23 '17 at 12:16

1

You can use approach described in this thread using plyr:

ddply(df, .(Year, Month), numcolwise(diff))

edited May 23 '17 at 12:16

Community

1
1

answered Feb 24 '17 at 16:09

Robert Eckhaus

151
6

Group By Row Value Difference

3 Answers3