Annual percentage change dataframe in R

Question

I have a time series dataframe in R, lets call it df, that looks like this:

Date	City_1	City_2	City_3	.....	City_n
1980-01-01	1	4	6	...	2
1980-01-02	2	7	6	...	5
1980-01-03	3	7	1	...	1
1980-01-04	8	1	8	...	1
1980-01-05	4	3	5	...	0
...	...	...	...	...	7
2020-08-20	3	1	8	...	2
1980-08-21	7	8	3	...	2
1980-08-22	8	6	5	...	0
1980-08-23	9	8	2	...	1

Here is a reproducible part of my data:

df <- structure(c(17.86, 18.65, 18.42, 19.21, 21.34, 11.35, 10.01, 
9.67, 10.12, 10.81, 20.77, 20.99, 20.88, 20.91, 20.5, 13.8, 13.63, 
13.6, 13.02, 13.87, 22.07, 22.78, 23.24, 23.33, 24.06), .Dim = c(5L, 
5L), class = c("xts", "zoo"), index = structure(c(315532800, 
315619200, 315705600, 315792000, 315878400), tzone = "UTC", tclass = "Date"), 
.Dimnames = list(NULL, c("Sao Paulo", "Mexico City", "Lima", "Bogota", 
"Rio de Janeiro")))

And I want to create a new dataframe that, in each city column, for each date, has the percentage change with respect to the same date the previous year, given by the following formula:

change = [x(t) -x(t-1)]/x(t)

To get a final dataframe, df2 that will look like this:

Date	City_1	City_2	City_3	.....	City_n
1981-01-01	a	j	s	...	b
1981-01-02	b	k	t	...	c
1981-01-03	c	l	u	...	d
1981-01-04	d	m	v	...	t
1981-01-05	e	n	w	...	e
...	...	...	...	...	f
2020-08-20	f	o	x	...	h
1980-08-21	g	p	y	...	s
1980-08-22	h	q	z	...	y
1980-08-23	i	r	a	...	u

Where all the letters are the percentage change of each day, respect last year.

I'm really new at R, but I think that I would need a function. Can you guys help me out?

Answer 1

--- Update ---

mock data

df <- structure(c(17.86, 18.65, 18.42, 19.21, 21.34, 11.35, 10.01, 
              9.67, 10.12, 10.81, 20.77, 20.99, 20.88, 20.91, 20.5, 13.8, 13.63, 
              13.6, 13.02, 13.87, 22.07, 22.78, 23.24, 23.33, 24.06), .Dim = c(5L, 
                                                                               5L), class = c("xts", "zoo"), index = structure(c(315532800, 
                                                                                                                                 315619200, 315705600, 315792000, 315878400), tzone = "UTC", tclass = "Date"), 
            .Dimnames = list(NULL, c("Sao Paulo", "Mexico City", "Lima", "Bogota", 
                                     "Rio de Janeiro")))

functions

 findyear <- function(year2search,dates){
      idx <- which(year2search == dates)
      if (length(idx) == 0) idx <- NA
      return(idx)
    }

change <- function(x1, x2){
  return( (x1 - x2)/x1)
}

Using base R

 date <- as.Date(anytime::anytime(attributes(df)$index,asUTC = T))

year2search <- date - lubridate::years(1)
idx <- sapply(1:nrow(df), function(x) {findyear(year2search[x],date)})
res <- lapply(colnames(df), function(city) { print(city); df[, (which(colnames(df)==city))] <- change(df[, (which(colnames(df)==city))], df[, (which(colnames(df)==city))][idx])})
df_res <- matrix(unlist(res), ncol=ncol(df), nrow=nrow(df))
colnames(df_res) <- colnames(df)

--Solution for data.frame--

df <- data.frame(city1=c(17.86, 18.65, 18.42, 19.21, 21.34),
                 city2=c(11.35, 10.01,11.35, 10.01, 10.56),
                 city3=c(20.77, 20.99, 20.88, 20.91, 20.5),
                 date = seq(as.Date("1980/01/01"), as.Date("1984/01/01"), by="year"))

First solution using base R

year2search <- df$date - lubridate::years(1)
idx <- sapply(1:nrow(df), function(x) {findyear(year2search[x],df$date)})
res <- lapply(names(df)[1:3], function(city) { print(city); df[[city]] <- change(df[[city]], df[[city]][idx])})
df_res <- matrix(unlist(res), ncol=(ncol(df)-1), nrow=nrow(df))
colnames(df_res) <- names(df)[1:3]

Alternative solution using dplyr

df <- df %>% mutate(year2search = date - lubridate::years(1)) %>% 
  rowwise %>% mutate(idx = findyear(year2search,df$date))

for (city in names(df)[1:3]){
  df[[city]] <- change(df[[city]], df[[city]][df$idx])
}