Conditional Replacement

[RG_Series]

Hi everyone,

Below I am illustrating an example where I am wanting to replace my NA values with corresponding scores. I have provided a second table of what I am looking to accomplish. Risk 4 demonstrates where I have all NA values, which will not get replaced. Risk numbers 5 and 6 show that my data might not have five years for every risk. Risk 7 is a very rare example where I would be fine if we only develop a solution to the other risk numbers. Thanks.
Solution
Risk Year Score Risk Year Score
1 2015 867 1 2015 867
1 2016 994 1 2016 994
1 2017 634 1 2017 634
1 2018 696 1 2018 696
1 2019 NA 1 2019 696
2 2015 NA 2 2015 964
2 2016 NA 2 2016 964
2 2017 964 2 2017 964
2 2018 863 2 2018 863
2 2019 997 2 2019 997
3 2015 NA 3 2015 777
3 2016 NA 3 2016 777
3 2017 777 3 2017 777
3 2018 856 3 2018 856
3 2019 NA 3 2019 856
4 2015 NA 4 2015 NA
4 2016 NA 4 2016 NA
4 2017 NA 4 2017 NA
4 2018 NA 4 2018 NA
4 2019 NA 4 2019 NA
5 2017 803 5 2017 803
5 2018 929 5 2018 929
5 2019 797 5 2019 797
6 2016 950 6 2016 950
6 2017 NA 6 2017 950
7 2015 871 7 2015 871
7 2016 NA 7 2016 871
7 2017 NA 7 2017 871
7 2018 699 7 2018 699
7 2019 620 7 2019 620

[williaml]

Sorry, where is the second table?

Can you provide a reproducible example?

FAQ: What's a reproducible example (`reprex`) and how do I create one?

Why reprex? Getting unstuck is hard. Your first step here is usually to create a reprex, or reproducible example. The goal of a reprex is to package your code, and information about your problem so that others can run it and feel your pain. Then, hopefully, folks can more easily provide a solution. What's in a Reproducible Example? Parts of a reproducible example: background information - Describe what you are trying to do. What have you already done? complete set up - include any library() calls and data to reproduce your issue. data for a reprex: Here's a discussion on setting up data for a reprex make it run - include the minimal code required to reproduce your error on the data…

[RG_Series]

Sorry, my first post did not go as expected. Please try the following.

Original_Table <- data.frame(Risk = c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,5,5,5,6,6,7,7,7,7,7),
Year = c(2015,2016,2017,2018,2019,2015,2016,2017,2018,2019,2015,2016,2017,2018,2019,2015,2016,2017,2018,2019,2017,2018,2019,2016,2017,2015,2016,2017,2018,2019),
Score = c(867,994,634,696,"NA","NA","NA",964,863,997,"NA","NA",777,856,"NA","NA","NA","NA","NA","NA",803,929,797,950,"NA",871,"NA","NA",699,620))

Solution_Table <- data.frame(Risk = c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,5,5,5,6,6,7,7,7,7,7),
Year = c(2015,2016,2017,2018,2019,2015,2016,2017,2018,2019,2015,2016,2017,2018,2019,2015,2016,2017,2018,2019,2017,2018,2019,2016,2017,2015,2016,2017,2018,2019),
Score = c(867,994,634,696,696,964,964,964,863,997,777,777,777,856,856,"NA","NA","NA","NA","NA",803,929,797,950,950,871,871,871,699,620))

[andresrcs]

This produces your desired output

library(tidyverse)

Original_Table <- data.frame(
        Risk = c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,
                 4,4,4,4,4,5,5,5,6,6,7,7,7,7,7),
        Year = c(2015,2016,2017,2018,2019,2015,2016,
                 2017,2018,2019,2015,2016,2017,2018,2019,2015,2016,2017,
                 2018,2019,2017,2018,2019,2016,2017,2015,2016,2017,
                 2018,2019),
       Score = c(867,994,634,696,NA,NA,NA,964,863,
                 997,NA,NA,777,856,NA,NA,NA,NA,NA,NA,803,929,797,
                 950,NA,871,NA,NA,699,620)
)

Original_Table %>% 
    group_by(Risk) %>% 
    fill(Score, .direction = "downup")
#> # A tibble: 30 x 3
#> # Groups:   Risk [7]
#>     Risk  Year Score
#>    <dbl> <dbl> <dbl>
#>  1     1  2015   867
#>  2     1  2016   994
#>  3     1  2017   634
#>  4     1  2018   696
#>  5     1  2019   696
#>  6     2  2015   964
#>  7     2  2016   964
#>  8     2  2017   964
#>  9     2  2018   863
#> 10     2  2019   997
#> # … with 20 more rows

^{Created on 2021-01-25 by the reprex package (v0.3.0.9001)}