时间戳

首页 > 解决方案 > 将 NA 替换为前一天的退货值

r - 将 NA 替换为前一天的退货值

问题描述

我想计算以下数据框中的指数和份额的回报。索引中有 NA，我通常用df <- na.locf(df). 但我无法让它在我的功能中工作。我希望 NA 采用前一天的值。

      Underlying       Date ClosingPrice MDAXClosing
1   DE0005089031 04.01.2016      49.5010    20256.14
2   DE0005089031 05.01.2016      49.7855    20228.06
3   DE0005089031 06.01.2016      49.0595    19989.88
4   DE0005089031 07.01.2016      47.7785    19537.39
5   DE0005089031 08.01.2016      47.7435    19321.93
6   DE0005089031 09.01.2016      47.8160          NA
7   DE0005089031 10.01.2016      47.7770          NA
8   DE0005089031 11.01.2016      48.8095    19219.43
9   DE0005089031 12.01.2016      48.9545    19627.76
10  DE0005089031 13.01.2016      48.0195    19587.69
11  DE0005089031 14.01.2016      47.1460    19296.48
12  DE0005089031 15.01.2016      43.5580    18789.76
13  DE0005089031 16.01.2016      43.4000          NA
14  DE0005089031 17.01.2016      43.4000          NA
15  DE0005089031 18.01.2016      44.4815    18662.69
16  DE0005089031 19.01.2016      45.6485    19029.23
17  DE0005089031 20.01.2016      44.8300    18322.99

为了获得我的回报，我使用以下代码

library(tidyverse)
df %>%
  dplyr::mutate(Date = as.Date(Date, format = "%d.%m.%Y")) %>%
  dplyr::mutate(week = cut.Date(Date, breaks = "1 week", labels = FALSE)) %>%
  dplyr::group_by(Underlying, week) %>%
  dplyr::summarise(Stockreturn = log(ClosingPrice[1] / ClosingPrice[n()])) %>%
  dplyr::summarise(MDAXreturn = log(MDAXClosing[1] / MDAXClosing[n()]))

这就是没有 MDAX 的输出的样子

   Underlying    week Stockreturn
   <fct>        <int>       <dbl>
 1 DE0005089031     1     0.0354 
 2 DE0005089031     2     0.117  
 3 DE0005089031     3    -0.0542 
 4 DE0005089031     4    -0.0162 
 5 DE0005089031     5     0.0622 
 6 DE0005089031     6    -0.0349 
 7 DE0005089031     7    -0.0303 
 8 DE0005089031     8    -0.00208
 9 DE0005089031     9     0.00361
10 DE0005089031    10    -0.0165

在这里，除了 Stockreturn 之外，我还想添加我的 MDAXreturn。有谁知道如何将其添加到代码中？我尝试添加dplyr::mutate(purchase = zoo::na.locf(MDAXClosing, na.rm = FALSE))，但没有结果。

标签： rdplyrtidyversena

解决方案

您可以使用以下方法替换NAs管道开头的先前值fill()：

library(tidyverse)
df %>%
  fill(MDAXClosing) %>% 
  dplyr::mutate(Date = as.Date(Date, format = "%d.%m.%Y"),
                week = cut.Date(Date, breaks = "1 week", labels = FALSE)) %>%
  dplyr::group_by(Underlying, week) %>%
  dplyr::summarise(Stockreturn = log(ClosingPrice[1] / ClosingPrice[n()]),
                   MDAXreturn = log(MDAXClosing[1] / MDAXClosing[n()]))

# A tibble: 3 x 4
# Groups:   Underlying [1]
  Underlying    week Stockreturn MDAXreturn
  <chr>        <int>       <dbl>      <dbl>
1 DE0005089031     1     0.0354      0.0472
2 DE0005089031     2     0.117       0.0226
3 DE0005089031     3    -0.00780     0.0184

MDAXreturnsummarise可以通过在相同的语句中计算它来计算Stockreturn

数据

df <- tibble::tribble(
           ~Underlying,        ~Date, ~ClosingPrice, ~MDAXClosing,
        "DE0005089031", "04.01.2016",        49.501,     20256.14,
        "DE0005089031", "05.01.2016",       49.7855,     20228.06,
        "DE0005089031", "06.01.2016",       49.0595,     19989.88,
        "DE0005089031", "07.01.2016",       47.7785,     19537.39,
        "DE0005089031", "08.01.2016",       47.7435,     19321.93,
        "DE0005089031", "09.01.2016",        47.816,           NA,
        "DE0005089031", "10.01.2016",        47.777,           NA,
        "DE0005089031", "11.01.2016",       48.8095,     19219.43,
        "DE0005089031", "12.01.2016",       48.9545,     19627.76,
        "DE0005089031", "13.01.2016",       48.0195,     19587.69,
        "DE0005089031", "14.01.2016",        47.146,     19296.48,
        "DE0005089031", "15.01.2016",        43.558,     18789.76,
        "DE0005089031", "16.01.2016",          43.4,           NA,
        "DE0005089031", "17.01.2016",          43.4,           NA,
        "DE0005089031", "18.01.2016",       44.4815,     18662.69,
        "DE0005089031", "19.01.2016",       45.6485,     19029.23,
        "DE0005089031", "20.01.2016",         44.83,     18322.99
        )

推荐阅读