Create new column: divides current year quarter by last year quarter minus 1

user_8385528 0
Viewed 43

I want to get percentage difference between a quarter compare to the same quarter a year earlier. This is my df

df <- data.frame(
  x0 = c('2010Q1', '2010Q2', '2010Q3', '2010Q4', '2011Q1', '2011Q2', 
         '2011Q3', '2011Q4', '2012Q1', '2012Q2', '2012Q3', '2012Q4', 
         '2013Q1', '2013Q2', '2013Q3', '2013Q4', '2014Q1', '2014Q2'),
  x1 = c(14.0, 13.4, 13.8, 14.4, 14.2, 14.3, 14.0, 14.1, 14.6, 14.3, 
         14.0, 13.6, 13.5, 12.9, 13.2, 13.2,12.7, 13.6),
  x2 = c(13.0, 13.3, 13.4, 13.7, 13.7, 13.9, 14.0, 13.9, 13.9, 14.0, 
         14.1, 13.8, 13.7, 13.8, 13.8, 13.8, 13.6, 13.9)
)

I want to calculate 2012Q1 / 2011Q1 minus 1, and for the rest of quarters. to get a df like this below: 

df <- data.frame(
   x0 = c('2010Q1', '2010Q2', '2010Q3', '2010Q4', '2011Q1', '2011Q2', 
          '2011Q3', '2011Q4', '2012Q1', '2012Q2', '2012Q3', '2012Q4', 
          '2013Q1', '2013Q2', '2013Q3', '2013Q4', '2014Q1', '2014Q2'),
   x1 = c(14.0, 13.4, 13.8, 14.4, 14.2, 14.3, 14.0, 14.1, 14.6, 14.3, 
          14.0, 13.6, 13.5, 12.9, 13.2, 13.2,12.7, 13.6),
   x1_div = c(NA, NA, NA, NA, 0.018, 0.063, 0.009, -0.015, 0.031, 
              0.002, 0.004, -0.036, -0.081, -0.099, -0.059, -0.031, 
             -0.054, 0.057),
   x2 = c(13.0, 13.3, 13.4, 13.7, 13.7, 13.9, 14.0, 13.9, 13.9, 14.0, 
          14.1, 13.8, 13.7, 13.8, 13.8, 13.8, 13.6, 13.9),
   x2_div = c(NA, NA, NA, NA, 0.058, 0.051, 0.044, 0.013, 0.008, 0.006, 
              0.004, -0.008, -0.012, -0.017, -0.016, -0.005, -0.005, 
              0.007)
)
calculated-columns division r
2 Answers

We can create a grouping column by extracting the quarter part and then with mutate_at, divide the lag of the columns by the column values and subtract from 1.

library(dplyr)
library(stringr)
df %>%
    group_by(grp = str_extract(x0, "Q\\d")) %>% 
    mutate_at(vars('x1', 'x2'), funs(div = round(1- lag(.)/., 2))) %>%
    ungroup %>%
    select(-grp)
user_37322710

With time series data these operations are easier if you use a time series class in the first place. First create a zoo object, z, having a "yearqtr" time index and then use diff.zoo to create the returns, ret. This could be converted back to data frame using fortify.zoo(ret) but might not be necessary. At the end we use autoplot.zoo to create a ggplot2 plot of the returns as an example of further processing. (Remove facet=NULL to get a multi-panel plot.)

library(zoo)

z <- read.zoo(df, FUN = as.yearqtr)
ret <- diff(z, 4, arithmetic = FALSE) - 1

library(ggplot2)
autoplot(ret, facet = NULL) + scale_x_yearqtr()

enter image description here

user_5165480
Related