How to add values to a column and still keep some NA?

user_13282354 0
Viewed 51

I have a data frame containing three columns: ID, year, growth. The last one contains data of growth in milimeters for each year.

Example:

df <- data.frame(ID=rep(c("CHC01", "CHC02", "CHC03"), each=6), 
         year=rep(2013:2018, 3), 
         growth=c(NA, NA, NA, 2.3, 2.1, 3.0, NA, NA, NA, NA, 1.1, 4.8, 1.0, 3.2, 4.2, 2.3, 2.1, 1.2))

In another data frame, I have other three columns: ID, missing_length, missing_years. Missing length relates to the estimated length missed in the measurements. Missing years relates to the number of missing years in df

estimate <- data.frame(ID=c("CHC01", "CHC02", "CHC03"), 
               missing_length=c(1.0, 4.4, 0), 
               missing_years=c(1,3,0))

For calculating the growth for each missing year, I tried:

missing <- rep(estimate$missing_length / estimate$missing_years, estimate$missing_years)

It is important to note that not all NA from df will be replaced by estimated values.

Here is an example of the data frame I trying to get:

    ID   year growth
1  CHC01 2013     NA
2  CHC01 2014     NA
3  CHC01 2015   1.00
4  CHC01 2016   2.30
5  CHC01 2017   2.10
6  CHC01 2018   3.00
7  CHC02 2013     NA
8  CHC02 2014   1.47
9  CHC02 2015   1.47
10 CHC02 2016   1.47
11 CHC02 2017   1.10
12 CHC02 2018   4.80
13 CHC03 2013   1.00
14 CHC03 2014   3.20
15 CHC03 2015   4.20
16 CHC03 2016   2.30
17 CHC03 2017   2.10
18 CHC03 2018   1.20

Does anyone have any idea of how to deal with this problem?

Thank you very much!

dplyr na dataframe vector r
1 Answers

We can use which to get the position index and then subset that position with tail with the missing_years in replace to replace those missing values with the ratio of 'missing_length' an 'missing_years' after doing a left_join with the 'estimate'

library(dplyr)
df %>%
    left_join(estimate) %>%
    group_by(ID) %>% 
    transmute(year, growth = replace(growth, 
          tail(which(is.na(growth)), first(missing_years)), 
              first(missing_length)/first(missing_years)))
# A tibble: 18 x 3
# Groups:   ID [3]
#   ID     year growth
#   <fct> <int>  <dbl>
# 1 CHC01  2013  NA   
# 2 CHC01  2014  NA   
# 3 CHC01  2015   1   
# 4 CHC01  2016   2.3 
# 5 CHC01  2017   2.1 
# 6 CHC01  2018   3   
# 7 CHC02  2013  NA   
# 8 CHC02  2014   1.47
# 9 CHC02  2015   1.47
#10 CHC02  2016   1.47
#11 CHC02  2017   1.1 
#12 CHC02  2018   4.8 
#13 CHC03  2013   1   
#14 CHC03  2014   3.2 
#15 CHC03  2015   4.2 
#16 CHC03  2016   2.3 
#17 CHC03  2017   2.1 
#18 CHC03  2018   1.2
user_37322710
Related