How fill NA by condition?

July 8, 2022

I have sales data by years and models. Here sales of J model in each year is missing. Now I want the following condition:

Fill NA of J model with a maximum value of sales in each year + 100. For instance, max sale in 2015 was 984, so J has to be 984+100 in 2015

df <- data.frame (model  = c("A","B","C","D","E","F","G","H","I","J","A","B","C","D","E","F","G","H","I","J","A","B","C","D","E","F","G","H","I","J","A","B","C","D","E","F","G","H","I","J","A","B","C","D","E","F","G","H","I","J","A","B","C","D","E","F","G","H","I","J"),
                  Year = c(2015,2015,2015,2015,2015,2015,2015,2015,2015,2015,2016,2016,2016,2016,2016,2016,2016,2016,2016,2016,2017,2017,2017,2017,2017,2017,2017,2017,2017,2017,2018,2018,2018,2018,2018,2018,2018,2018,2018,2018,2019,2019,2019,2019,2019,2019,2019,2019,2019,2019,2020,2020,2020,2020,2020,2020,2020,2020,2020,2020),
                  sales = c(450,678,456,344,984,456,234,244,655,"NA",234,567,234,567,232,900,1005,1900,450,"NA",567,235,456,345,144,333,555,777,111,"NA",222,223,445,776,331,788,980,1003,456,"NA",345,2222,3456,456,678,8911,4560,4567,4566,"NA",6666,7777,8888,1233,1255,5677,3411,2344,6122,"NA"))

>Solution :

You may try(NA is "NA" so it needed to be as numeric)

library(dplyr)
df %>%
  group_by(Year) %>%
  mutate(sales = as.numeric(sales)) %>%
  mutate(sales = ifelse(is.na(sales) & (model == "J"), max(sales, na.rm = T) + 100, sales))

  model  Year sales
   <chr> <dbl> <dbl>
 1 A      2015   450
 2 B      2015   678
 3 C      2015   456
 4 D      2015   344
 5 E      2015   984
 6 F      2015   456
 7 G      2015   234
 8 H      2015   244
 9 I      2015   655
10 J      2015  1084
# … with 50 more rows