-4

I have the following data frame,

Input

For all observations where Month > tenor, the last value of the rate column should be retained for each account for the remaining months. Eg:- Customer 1 has tenor = 5, so for all months greater than 5, the last rate value is retained.

I am using the following code

df$rate <- ifelse(df$Month > df$tenor,tail(df$rate, n=1),df$rate)

But here, the last value is NA so it does not work

Expected output is

Output

Sotos
  • 51,121
  • 6
  • 32
  • 66
narayanpr23
  • 11
  • 5
  • 6
    Please add data using `dput` and not as images. Read about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). – Ronak Shah Sep 22 '20 at 12:58
  • 4
    What you have shown us is _not_ your data frame. It is a _picture_ of your data frame. This means if anyone wants to try to use your sample data to create an answer they will have to laboriously transcribe your picture by hand. You are more likely to get a useful answer if you post your data as text that people can copy and paste. See the links that @RonakShah has provided to show you how to do that easily. Thank you. – Allan Cameron Sep 22 '20 at 13:00
  • Possible duplicate https://stackoverflow.com/q/23340150/680068 – zx8754 Sep 22 '20 at 13:08
  • Does this answer your question? [Replace missing values (NA) with most recent non-NA by group](https://stackoverflow.com/questions/23340150/replace-missing-values-na-with-most-recent-non-na-by-group) – iago Sep 23 '20 at 09:20

3 Answers3

0

One option is:

library(dplyr)
library(tidyr)
df %>%
    group_by(cus_no) %>%
    fill(rate, .direction = "down") %>%
    ungroup()
# A tibble: 20 x 4
   customer Month tenor  rate
      <dbl> <dbl> <dbl> <dbl>
 1        1     1     5   0.2
 2        1     2     5   0.3
 3        1     3     5   0.4
 4        1     4     5   0.5
 5        1     5     5   0.6
 6        1     6     5   0.6
 7        1     7     5   0.6
 8        1     8     5   0.6
 9        1     9     5   0.6
10        1    10     5   0.6
11        2     1     3   0.1
12        2     2     3   0.2
13        2     3     3   0.3
14        2     4     3   0.3
15        2     5     3   0.3
16        2     6     3   0.3
17        2     7     3   0.3
18        2     8     3   0.3
19        2     9     3   0.3
20        2    10     3   0.3
iago
  • 2,990
  • 4
  • 21
  • 27
0

this will work, but please have a reproducible example. Others want to help you, not do your homework.

df <- data.frame(
    customer = c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2),
    Month = c(1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10),
    tenor = c(5,5,5,5,5,5,5,5,5,5,3,3,3,3,3,3,3,3,3,3),
    rate = c(0.2,0.3,0.4,0.5,0.6,NA,NA,NA,NA,NA,0.1,0.2,0.3,NA,NA,NA,NA,NA,NA,NA)
)


fn <- function(cus, mon, ten, rat){
    if (mon > ten & is.na(rat)){
        return(dplyr::filter(df, customer == cus, Month == ten, tenor == ten)$rate)
    }
        return(rat)
}


df2 <- mutate(df, 
    newrate = Vectorize(fn)(customer, Month, tenor, rate)
)
dspn
  • 314
  • 2
  • 9
-1

I can't replicate your data frame so this is a guess right now. I think dplyr should be the solution:-

library(dplyr)

df%>%
group_by(Month)%>%
replace_na(last(rate))

should work

metaltoaster
  • 380
  • 2
  • 15