Annual percentage change dataframe in R

user_14403266 0
Viewed 61

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?

time-series dataframe r
1 Answers

--- 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])
}
user_159802840
Related