R - Replace the value of a specific position where there is a NA value-CodePudding

Given this data set:

df <- data.frame(a = c(NA, NA, NA, NA, 10, 10, 16, 21),
                 b = c(NA, NA, NA, NA, NA, NA, 2, 4))

a	b
NA	NA
NA	NA
NA	NA
NA	NA
10	NA
10	NA
16	2
21	4

Calculate the differences of values between the previous row:

df <- df%>%
  mutate(a_diff = a - lag(a, default = first(a)),
         b_diff = b - lag(b, default = first(b)))

a	b	a_diff	b_diff
NA	NA	NA	NA
NA	NA	NA	NA
NA	NA	NA	NA
NA	NA	NA	NA
10	NA	NA	NA
10	NA	0	NA
16	2	6	NA
21	4	5	2

But the result I need would be the following:

a	b	a_diff	b_diff
NA	NA	NA	NA
NA	NA	NA	NA
NA	NA	NA	NA
NA	NA	NA	NA
10	NA	10	NA
10	NA	0	NA
16	2	6	2
21	4	5	2

That is, I need to replace the last 'NA' value of column 'a_diff' with the value corresponding to the row number of column 'a'. And the same with the 'b_diff' column.

Thank you in advance for reading my question and I would appreciate any clue you can give me to solve my problem.

CodePudding user response：

Maybe this is what you are looking for. Using max(which(is.na())) with your "diff" value will get the position of the last NA in that column. You can substitute with values for that particular row as below.

library(tidyverse)

df %>%
  mutate(a_diff = a - lag(a, default = first(a)),
         b_diff = b - lag(b, default = first(b))) %>%
  mutate(a_diff = ifelse(row_number() == max(which(is.na(a_diff))), a, a_diff),
         b_diff = ifelse(row_number() == max(which(is.na(b_diff))), b, b_diff))

Output

   a  b a_diff b_diff
1 NA NA     NA     NA
2 NA NA     NA     NA
3 NA NA     NA     NA
4 NA NA     NA     NA
5 10 NA     10     NA
6 10 NA      0     NA
7 16  2      6      2
8 21  4      5      2

CodePudding user response：

Perfect use case for coalesce:

library(dplyr)

df%>%
  mutate(a_diff = a - lag(a, default = first(a)),
         b_diff = b - lag(b, default = first(b))) %>% 
  mutate(a_diff = coalesce(a_diff, a),
         b_diff = coalesce(b_diff, b))

  a  b a_diff b_diff
1 NA NA     NA     NA
2 NA NA     NA     NA
3 NA NA     NA     NA
4 NA NA     NA     NA
5 10 NA     10     NA
6 10 NA      0     NA
7 16  2      6      2
8 21  4      5      2