If value is equal to NA, then estimate based on share in R

user_12073743 0
Viewed 50

Consider the two following data frames:

df  <- data.frame(REGION   = c("REG01","REG02","REG03","REGSUM"),
                  INDUSTRY = c("INDU01","INDU01","INDU01","INDU01"),
                  VALUE    = c(NA,10,NA,30))

and:

df2 <- data.frame(REGION   = c("REG01","REG02","REG03","REGSUM"),
                  INDUSTRY = c("INDU01","INDU01","INDU01","INDU01"),
                  VALUE    = c(5,15,20,40))

I want to do the following calculation: If the value is equal to NA in df, then I want to estimate it based on the shares from df2. Because I know the sum in df, I know that I have to distribute the value df[REGSUM,INDU01] - df[REG02,INDU01] = 30 - 10 = 20 between the two elements with NA in df.

Then it should divide the same elements in df2 with the sum of the elements with NA:

df2_share[REG01,INDU01] = 5  / (5 + 20) = 0.2
df2_share[REG03,INDU01] = 20 / (5 + 20) = 0.8

This shares should be used to estimate the NA in df1. So I will end up with the following data frame:

    REGION  INDUSTRY   VALUE
1   REG01   INDU01     0.2 * 20 = 4 
2   REG02   INDU01     10   
3   REG03   INDU01     0.8 * 20 = 16    
4   REGSUM  INDU01     30

Can I do that in R (I have a lot of regions and industries in my data frame).

dataframe if-statement division r
3 Answers

Here is a way.
Sum the values that are not NA nor "REGSUM" in df. Use this value to compute the total to assign to the NA values. Then get the values in df2 corresponding to the NA entries and compute the proportions to assign.

not_na_values <- sum(df$VALUE[df$REGION != "REGSUM"], na.rm = TRUE)
to_assign <- df$VALUE[df$REGION == "REGSUM"] - not_na_values

na <- is.na(df$VALUE)
numer <- df2$VALUE[na]
denom <- sum(numer)
df$VALUE[na] <- numer/denom * to_assign

df
#  REGION INDUSTRY VALUE
#1  REG01   INDU01     4
#2  REG02   INDU01    10
#3  REG03   INDU01    16
#4 REGSUM   INDU01    30

The function below generalises the code above for data.frames with many industries. It works by splitting the input data.frames by industry and lapplying the previous code, written as a function to each split list member. In the end it reassembles these sub-dataframes and returns to caller.

assign_na_values <- function(x, y,
                             region_col = "REGION", 
                             industry_col = "INDUSTRY",
                             value_col = "VALUE", 
                             regsum = "REGSUM") {
  f <- function(x, y, region_col, value_col, regsum){
    i <- x[[region_col]] != regsum
    not_na_values <- sum(x[[value_col]][ i ], na.rm = TRUE)
    to_assign <- x[[value_col]][ !i ] - not_na_values
    
    na <- is.na(x[[value_col]])
    numer <- y[[value_col]][na]
    denom <- sum(numer)
    x[[value_col]][na] <- numer/denom * to_assign
    x
  }
  sp_x <- split(x, x[[industry_col]])
  sp_y <- split(y, y[[industry_col]])
  res <- lapply(seq_along(sp_x), function(i){
    f(sp_x[[i]], sp_y[[i]], region_col, value_col, regsum)
  })
  res <- do.call(rbind, res)
  row.names(res) <- NULL
  res
}

assign_na_values(df, df2)
#  REGION INDUSTRY VALUE
#1  REG01   INDU01     4
#2  REG02   INDU01    10
#3  REG03   INDU01    16
#4 REGSUM   INDU01    30
#5  REG01   INDU02    30
#6  REG02   INDU02     6
#7  REG03   INDU02     4
#8 REGSUM   INDU02    40

New test data

df <- data.frame(
  REGION = c("REG01","REG02","REG03","REGSUM","REG01","REG02","REG03","REGSUM"),    
  INDUSTRY = c("INDU01","INDU01","INDU01","INDU01","INDU02","INDU02","INDU02","INDU02"), 
  VALUE = c(NA,10,NA,30,30,NA,NA,40)
)

df2 <- data.frame(
  REGION = c("REG01","REG02","REG03","REGSUM","REG01","REG02","REG03","REGSUM"), 
  INDUSTRY = c("INDU01","INDU01","INDU01","INDU01","INDU02","INDU02","INDU02","INDU02"), 
  VALUE = c(5,15,20,40,10,30,20,60)
)
user_82454060

Here is another base R solution

idx <- which(REGION == "REGSUM")
df <- transform(
  df,
  VALUE = replace(
    VALUE,
    is.na(VALUE),
    prop.table(df2$VALUE[is.na(VALUE)]) * (VALUE[idx] - sum(VALUE[-idx], na.rm = TRUE))
  )
)

which gives

  REGION INDUSTRY VALUE
1  REG01   INDU01     4
2  REG02   INDU01    10
3  REG03   INDU01    16
4 REGSUM   INDU01    30
user_121587570

If there multiple 'INDUSTRY', we could do a join and use group by operations

library(dplyr)
df %>% 
  left_join(df2, by = c("REGION", "INDUSTRY")) %>% 
  group_by(INDUSTRY) %>%
  transmute(REGION, INDUSTRY, VALUE = case_when(is.na(VALUE.x) ~ 
    VALUE.y/sum(VALUE.y[is.na(VALUE.x)]) * (VALUE.x[n()] - 
         sum(VALUE.x[-n()], na.rm = TRUE)), TRUE ~ VALUE.x)) %>%
  ungroup

-output

# A tibble: 4 x 3
#  REGION INDUSTRY VALUE
#  <chr>  <chr>    <dbl>
#1 REG01  INDU01       4
#2 REG02  INDU01      10
#3 REG03  INDU01      16
#4 REGSUM INDU01      30
user_37322710
Related