Replace value with previous row value

Question

I have a data frame of overnight interest swaps that look like this

Date           DSWP10
07/01/2015     2.1
06/01/2015     .
05/01/2015     .
04/01/2015     1.99
03/01/2015     1.98
02/01/2015     .
01/01/2015     1.95

I am looking to replace the values that are a full stop (.) with the value form the previous day so that the data frame looks like

Date           DSWP10
07/01/2015     2.1
06/01/2015     1.99
05/01/2015     1.99
04/01/2015     1.99
03/01/2015     1.98
02/01/2015     1.95
01/01/2015     1.95

Any idea how to get this done?

Likely duplicate: [Replacing NAs with latest non-NA value](https://stackoverflow.com/questions/7735647/replacing-nas-with-latest-non-na-value) — Ian Campbell, May 28 '21 at 14:54

score 6 · Accepted Answer · answered May 28 '21 at 14:56

6

Does this work:

library(dplyr)
library(tidyr)
df %>% mutate(DSWP10 = as.numeric(na_if(DSWP10, '.'))) %>%  fill(DSWP10, .direction = 'up')
# A tibble: 7 x 2
  Date       DSWP10
  <chr>       <dbl>
1 07/01/2015   2.1 
2 06/01/2015   1.99
3 05/01/2015   1.99
4 04/01/2015   1.99
5 03/01/2015   1.98
6 02/01/2015   1.95
7 01/01/2015   1.95

answered May 28 '21 at 14:56

Karthik S

11,348
2
11
25

Indeed it does thank you, I'll mark the question as correct once the time limit has passed! – LukeAJN May 28 '21 at 15:00

score 1 · Answer 2 · answered May 28 '21 at 15:04

We can make use of na.locf function from package zoo. It carries the most recent non-NA value forward and replace all NAs on the way. For this purpose I had to arrange Date values in ascending order so that all values are replaced by the previous Date value.

library(dplyr)
library(zoo)

df %>%
  mutate(Date = mdy(Date),
         DSWP10 = as.numeric(DSWP10)) %>%
  arrange(Date) %>%
  mutate(across(DSWP10, ~ na.locf(.x))) %>%
  arrange(desc(Date))

        Date DSWP10
1 2015-07-01   2.10
2 2015-06-01   1.99
3 2015-05-01   1.99
4 2015-04-01   1.99
5 2015-03-01   1.98
6 2015-02-01   1.95
7 2015-01-01   1.95

score 0 · Answer 3 · answered May 28 '21 at 15:14

library(data.table)
library(magrittr)
library(zoo)

df <- read.table(
  text = "Date           DSWP10
07/01/2015     2.1
06/01/2015     .
05/01/2015     .
04/01/2015     1.99
03/01/2015     1.98
02/01/2015     .
01/01/2015     1.95",
  header = T
)
setDT(df)[DSWP10 == ".", DSWP10 := NA_character_] %>%
  .[, DSWP10 := zoo::na.locf(DSWP10, fromLast = TRUE)] %>%
  .[]
#>          Date DSWP10
#> 1: 07/01/2015    2.1
#> 2: 06/01/2015   1.99
#> 3: 05/01/2015   1.99
#> 4: 04/01/2015   1.99
#> 5: 03/01/2015   1.98
#> 6: 02/01/2015   1.95
#> 7: 01/01/2015   1.95

^{Created on 2021-05-28 by the reprex package (v2.0.0)}

Replace value with previous row value

3 Answers3