Calculating 5 Year Moving Average in R with inflation-adjusted exchange rates

I tried to calculate a 5 Year Moving Average of Exchange Rate in R but I can't get the code to work properly. I want to calculate a Moving Average similar to Atlas Conversion Factor used by World Bank for GNI estimates with some minor modifications.

Example:

For the target year 1965: before taking a mean/median all the exchange rates (1961,1962,1963,1964,1965) have to be to be adjusted for inflation that occurred between those years and 1965.
For the target year 1966: before taking a mean/median all the exchange rates (1962,1963,1964,1965,1966) have to be to be adjusted for inflation that occurred between those years and 1966.
... and so on.

# Installing and loading required packages.
install.packages("WDI")
library(WDI)


# Getting GDP Deflator “NY.GDP.DEFL.ZS” and Exchange Rates “PA.NUS.ATLS”. 
WDI <- WDI(indicator=c("NY.GDP.DEFL.ZS","PA.NUS.ATLS"))

# Showing part of the Dataframe.
WDI |>

tibble()

                        country iso2c iso3c year NY.GDP.DEFL.ZS PA.NUS.ATLS
1                   Afghanistan    AF   AFG 1960             NA    45.00000
2                   Afghanistan    AF   AFG 1961             NA    45.00000
3                   Afghanistan    AF   AFG 1962             NA    45.00000
4                   Afghanistan    AF   AFG 1963             NA    45.00000
5                   Afghanistan    AF   AFG 1964             NA    45.00000
6                   Afghanistan    AF   AFG 1965             NA    45.00000
7                   Afghanistan    AF   AFG 1966             NA    45.00000
8                   Afghanistan    AF   AFG 1967             NA    45.00000
9                   Afghanistan    AF   AFG 1968             NA    45.00000
10                  Afghanistan    AF   AFG 1969             NA    45.00000
11                  Afghanistan    AF   AFG 1970             NA    45.00000
12                  Afghanistan    AF   AFG 1971             NA    45.00000
13                  Afghanistan    AF   AFG 1972             NA    45.00000
14                  Afghanistan    AF   AFG 1973             NA    45.00000
15                  Afghanistan    AF   AFG 1974             NA    45.00000


WDI |>

mutate (Atlas= median(PA.NUS.ATLS*(NY.GDP.DEFL.ZS/NY.GDP.DEFL.ZS/NY.GDP.DEFL.ZS[iso3c=="USA"]/NY.GDP.DEFL.ZS[iso3c=="USA"]),
                      
lag(PA.NUS.ATLS,n=1)*(NY.GDP.DEFL.ZS/lag(NY.GDP.DEFL.ZS,n=1)/NY.GDP.DEFL.ZS[iso3c=="USA"]/lag(NY.GDP.DEFL.ZS[iso3c=="USA"],n=1)), 

lag(PA.NUS.ATLS,n=2)*(NY.GDP.DEFL.ZS/lag(NY.GDP.DEFL.ZS,n=2)/NY.GDP.DEFL.ZS[iso3c=="USA"]/lag(NY.GDP.DEFL.ZS[iso3c=="USA"],n=2)), 

lag(PA.NUS.ATLS,n=3)*(NY.GDP.DEFL.ZS/lag(NY.GDP.DEFL.ZS,n=3)/NY.GDP.DEFL.ZS[iso3c=="USA"]/lag(NY.GDP.DEFL.ZS[iso3c=="USA"],n=3)), 

lag(PA.NUS.ATLS,n=4)*(NY.GDP.DEFL.ZS/lag(NY.GDP.DEFL.ZS,n=4)/NY.GDP.DEFL.ZS[iso3c=="USA"]/lag(NY.GDP.DEFL.ZS[iso3c=="USA"],n=4)) ,

lag(PA.NUS.ATLS,n=5)*(NY.GDP.DEFL.ZS/lag(NY.GDP.DEFL.ZS,n=5)/NY.GDP.DEFL.ZS[iso3c=="USA"]/lag(NY.GDP.DEFL.ZS[iso3c=="USA"],n=5)),na.rm=FALSE), by= c("country","year"))

The code above results in this error:

Error in `mutate()`:
ℹ In argument: `by = c("country", "year")`.
Caused by error:
! `by` must be size 17024 or 1, not 2.
Run `rlang::last_trace()` to see where the error occurred.

Solution

You have by instead of .by. Otherwise, mutate will think you want another column called "by", which is length 2 and doesn't equal the length of the resulting tibble. (tibbles have stricter rules for allowing recycling, unlike data.frames).

After the extra information was given in the comments (link to Excel file with the correct formula), a few other problems were found.

You need to put the USA values in another column, aligned with the years,
Don't group by year, since you need the lags,
We need to calculate the lags first, then the rowwise medians,
You had some missing brackets.

inner_join(WDI, 
           filter(WDI, iso3c=="USA") |>
             rename(NY.GDP.DEFL.ZS.USA=NY.GDP.DEFL.ZS) |>
             select(year, NY.GDP.DEFL.ZS.USA), by='year') |>
  mutate(a2=lag(PA.NUS.ATLS,n=4)*(NY.GDP.DEFL.ZS/lag(NY.GDP.DEFL.ZS,n=4)/(NY.GDP.DEFL.ZS.USA/lag(NY.GDP.DEFL.ZS.USA,n=4))),
         a3=lag(PA.NUS.ATLS,n=3)*(NY.GDP.DEFL.ZS/lag(NY.GDP.DEFL.ZS,n=3)/(NY.GDP.DEFL.ZS.USA/lag(NY.GDP.DEFL.ZS.USA,n=3))),
         a4=lag(PA.NUS.ATLS,n=2)*(NY.GDP.DEFL.ZS/lag(NY.GDP.DEFL.ZS,n=2)/(NY.GDP.DEFL.ZS.USA/lag(NY.GDP.DEFL.ZS.USA,n=2))),
         a5=lag(PA.NUS.ATLS,n=1)*(NY.GDP.DEFL.ZS/lag(NY.GDP.DEFL.ZS,n=1)/(NY.GDP.DEFL.ZS.USA/lag(NY.GDP.DEFL.ZS.USA,n=1))),
         a6=    PA.NUS.ATLS     *(NY.GDP.DEFL.ZS/    NY.GDP.DEFL.ZS     /(NY.GDP.DEFL.ZS.USA/    NY.GDP.DEFL.ZS.USA)), .by="iso3c") |>
  rowwise() |>
  mutate(Atlas=median(c_across(a2:a6), na.rm=FALSE)) |>
  select(-c(a2:a6, NY.GDP.DEFL.ZS.USA)) |>
  filter(!is.na(Atlas))

# A tibble: 10,179 × 7
# Rowwise: 
   country     iso2c iso3c  year NY.GDP.DEFL.ZS PA.NUS.ATLS Atlas
   <chr>       <chr> <chr> <int>          <dbl>       <dbl> <dbl>
 1 Afghanistan AF    AFG    2004           46.5        47.8  53.1
 2 Afghanistan AF    AFG    2005           51.5        49.7  57.1
 3 Afghanistan AF    AFG    2006           55.2        49.9  53.5
 4 Afghanistan AF    AFG    2007           67.7        49.8  61.6
 5 Afghanistan AF    AFG    2008           69.1        50.9  59.7
 6 Afghanistan AF    AFG    2009           67.6        49.3  49.5
 7 Afghanistan AF    AFG    2010           70.2        45.8  50.5
 8 Afghanistan AF    AFG    2011           81.8        47.8  56.8
 9 Afghanistan AF    AFG    2012           87.8        51.7  55.1
10 Afghanistan AF    AFG    2013           92.0        56.5  56.5
# ℹ 10,169 more rows
# ℹ Use `print(n = ...)` to see more rows