Apply a custom function to each cell of a dataframe-CodePudding

I have a dataframe that looks as follows:

> tail(RD_exp)
   ISIN FR0000121014 CH0038863350 NL0010273215 CH0012032048 FR0000120321 GB00BP6MXD84 CH0012005267 GB0009895292 DK0060534915
15 2016       111000      1596405       639493      9107599       849800       918704      7638916      5715795      1828103
16 2017       130000      1608219      1155900      9693157       877100       809387      7603263      4869596      1785748
17 2018       130000      1441758      1239800      9441933       914400       836828      7247722      4458881      1822380
18 2019       140000      1483751      1553700     10373832       985300       860653      7391073      4633743      1806029
19 2020       139000      1449832      1579900     11178244       964400       790587      7285164      5564538      1835092
20 2021       147000      1544082      1861600     12673490      1028700       690794      7647094      6493563      2188763

t <- nrow(RD_exp)
t
[1] 20

I want to apply to each column the following formula which is a example for the last year of column 3:

RD_Cap_t <- RD_exp[t-2,3]   0.8*RD_exp[t-3,3]   0.6*RD_exp[t-4,3]   0.4*RD_exp[t-5,3]   0.2*RD_exp[t-6,3]

Furthermore I would like to not just calculate RD_Cap for t but also for all other years before t.

Is there a possibility to do this?

Thanks in advance!

CodePudding user response：

One option is to use mutate and across of the tidyverse. I've repeated the provided data twice, so that there are enough rows to make use of the required 6 lags in the target function. And I did not apply the function to column ISIN. If it should indeed be applied to all columns, simply specify across(everything(), ...) instead.

library(tidyverse)

RD_exp <- read.table(text = 
"   ISIN FR0000121014 CH0038863350 NL0010273215 CH0012032048 FR0000120321 GB00BP6MXD84 CH0012005267 GB0009895292 DK0060534915
15 2016       111000      1596405       639493      9107599       849800       918704      7638916      5715795      1828103
16 2017       130000      1608219      1155900      9693157       877100       809387      7603263      4869596      1785748
17 2018       130000      1441758      1239800      9441933       914400       836828      7247722      4458881      1822380
18 2019       140000      1483751      1553700     10373832       985300       860653      7391073      4633743      1806029
19 2020       139000      1449832      1579900     11178244       964400       790587      7285164      5564538      1835092
20 2021       147000      1544082      1861600     12673490      1028700       690794      7647094      6493563      2188763"
)

#make table long enough for 6 lags
RD_exp2 <- bind_rows(RD_exp, RD_exp) %>%
  dplyr::mutate(ISIN = 2010:2021)
rownames(RD_exp2) <- NULL
                                        
RD_exp2 %>%
  dplyr::mutate(
    across(-ISIN,
           function(x) {
             lag(x, 2)   0.8 * lag(x, 3)   0.6 * lag(x, 4)   0.4 * lag(x, 5)   0.2 * lag(x, 6)
           })
  )

   ISIN FR0000121014 CH0038863350 NL0010273215 CH0012032048 FR0000120321
1  2010           NA           NA           NA           NA           NA
2  2011           NA           NA           NA           NA           NA
3  2012           NA           NA           NA           NA           NA
4  2013           NA           NA           NA           NA           NA
5  2014           NA           NA           NA           NA           NA
6  2015           NA           NA           NA           NA           NA
7  2016       403200      4464456      4156999     30841252      2822080
8  2017       420200      4492545      4784840     33555789      2932580
9  2018       394000      4583422      3946153     31991257      2828400
10 2019       390600      4688475      3727154     31129394      2756980
11 2020       387200      4593775      3608836     29966063      2730320
12 2021       395800      4549467      3867197     29921010      2788740