How can I calculate the percentage change within a group for multiple columns in R?

Question

I have a data frame with an ID column, a date column (12 months for each ID), and I have 23 numeric variables. I would like to obtain the percentage change by month within each ID. I am using the quantmod package in order to obtain the percent change.

Here is an example with only three columns (for simplicity):

ID Date V1 V2 V3
1  Jan   2  3  5
1  Feb   3  4  6
1  Mar   7  8  9
2  Jan   1  1  1
2  Feb   2  3  4
2  Mar   7  8   8

I tried to use dplyr and the summarise_each function, but that was unsuccessful. More specifically, I tried the following (train is the name of the data set):

library(dplyr)
library(quantmod)

group1<-group_by(train,EXAMID)

foo<-function(x){
  return(Delt(x))
}

summarise_each(group1,funs(foo))

I also tried to use the do function in dplyr, but I was not successful with that either (having a bad night I guess!).

I think that the issue is the Delt function. When I replace Delt with the sum function:

foo<-function(x){
      return(sum(x))
    }
summarise_each(group1,funs(foo))

The result is that every variable is summed across the date for each ID. So how can about the percentage change month-over-month for each ID?

Miner

Answer 1

How about using pct <- function(x) x/lag(x)? (or (x/lag(x)-1)*100, or however you wish to specify pct change exactly) e.g.,

pct(1:3)
[1]  NA 2.0 1.5

Edit: Adding Frank's suggestion

pct <- function(x) {x/lag(x)}

dt %>% group_by(ID) %>% mutate_each(funs(pct), c(V1, V2, V3))

ID Date       V1       V2  V3
1  Jan       NA       NA  NA
1  Feb 1.500000 1.333333 1.2
1  Mar 2.333333 2.000000 1.5
2  Jan       NA       NA  NA
2  Feb 2.000000 3.000000 4.0
2  Mar 3.500000 2.666667 2.0

Willumsen

I get the following error: Error: expecting a single value That is a very good idea though. – Miner

@cwh_UCF Use mutate instead of summarise (which is designed to return a single value): DF %>% group_by(ID) %>% mutate_each(funs(pct),c(V1,V2,V3)) – Berlinda

@Berlinda shouldnt this be an answer instead of a comment. Just asking :) – Gens

To get the percent change as it was asket for 1 has to be subtracted. The pct - function should be changed like so: pct <- function(x) x/lag(x) -1 – Layne

@Willumsen I have not been able to get this to work. I get an error: Error in sqliteSendQuery(conn, statement) : error in statement: no such function: PCT – Rumania

@Rumania note that pct() is defined in the first line. – Willumsen

@Willumsen Yeah, I used it. pct isn't the same thing as PCT. I think the issue is with sqlite. It doesn't have support for window functions I think, so I think the problem is actually with lag. I won't be surprised if it works fine if I migrate to postgresql or mysql. – Rumania

@Rumania yes I see, I would think so too, comment if you try and it does – Willumsen

@dzelter I can confirm that it works with src_postgres (though I am having an issue with the log return). – Rumania

Nevermind, I just noticed that the natural log is ln in postgres instead of log like in R. – Rumania

I keep getting this error: Error: .vars must be a character/numeric vector or a vars() object, not list even though the variable is in my data frame. y implementation pct <- function(x) {x / lag(x) - 1} df_vertical_growth %>% group_by(YEAR, VERTICAL) %>% mutate_at(funs(pct), Profit) – Claypoole

Answer 2

The issue you are running into is because your data is not formatted in a "tidy" way. You have observations (V1:V3) that are in columns creating a "wide" data frame. The "tidyverse" works best with long format. The good news is with the gather() function you can get exactly what you need. Here's a solution using the "tidyverse".

library(tidyverse)

# Recreate data set
df <- tribble(
    ~ID, ~Date, ~V1, ~V2, ~V3,
    1,  "Jan",   2,  3,  5,
    1,  "Feb",   3,  4,  6,
    1,  "Mar",   7,  8,  9,
    2,  "Jan",   1,  1,  1,
    2,  "Feb",   2,  3,  4,
    2,  "Mar",   7,  8,  8
)
df
#> # A tibble: 6 × 5
#>      ID  Date    V1    V2    V3
#>   <dbl> <chr> <dbl> <dbl> <dbl>
#> 1     1   Jan     2     3     5
#> 2     1   Feb     3     4     6
#> 3     1   Mar     7     8     9
#> 4     2   Jan     1     1     1
#> 5     2   Feb     2     3     4
#> 6     2   Mar     7     8     8

# Gather and calculate percent change
df %>%
    gather(key = key, value = value, V1:V3) %>%
    group_by(ID, key) %>%
    mutate(lag = lag(value)) %>%
    mutate(pct.change = (value - lag) / lag)
#> Source: local data frame [18 x 6]
#> Groups: ID, key [6]
#> 
#>       ID  Date   key value   lag pct.change
#>    <dbl> <chr> <chr> <dbl> <dbl>      <dbl>
#> 1      1   Jan    V1     2    NA         NA
#> 2      1   Feb    V1     3     2  0.5000000
#> 3      1   Mar    V1     7     3  1.3333333
#> 4      2   Jan    V1     1    NA         NA
#> 5      2   Feb    V1     2     1  1.0000000
#> 6      2   Mar    V1     7     2  2.5000000
#> 7      1   Jan    V2     3    NA         NA
#> 8      1   Feb    V2     4     3  0.3333333
#> 9      1   Mar    V2     8     4  1.0000000
#> 10     2   Jan    V2     1    NA         NA
#> 11     2   Feb    V2     3     1  2.0000000
#> 12     2   Mar    V2     8     3  1.6666667
#> 13     1   Jan    V3     5    NA         NA
#> 14     1   Feb    V3     6     5  0.2000000
#> 15     1   Mar    V3     9     6  0.5000000
#> 16     2   Jan    V3     1    NA         NA
#> 17     2   Feb    V3     4     1  3.0000000
#> 18     2   Mar    V3     8     4  1.0000000

Theda

Update, in 2021 gather is "superseded" by pivot_longer. – Blazonry