How do I replace a portion of NA's in a column with the mean of a grouping variable?

user_14023487 0
Viewed 59

I have a dataset with Length and size variables. I have found the mean lengths of the size variables; spat=29.5, small=59.35, and market=97.0. I have also found the proportions of measured values spat=11%, small=38%, and market=50% for each of the size groupings.

I would like to fill in the un measured (na) lengths in the data set based on the proportions given above and assign each proportion a length based on the means given above.

for example 11% of the na's will be replaced with 29.5 length, 38% will be replaced with 59.35, and 50% will be replaced with 97.0

Does anyone know the code to make this work?

I'm sorry if I'm missing something, this is my first time asking a question.

     Length   size 
    NA        NA
    68         Small    
   NA         NA  
    84        Market    
    NA        NA  
    75        Small    
    81        Market    
    NA        NA   
     32        Spat    
     28        Spat    
     18        Spat    
      NA      NA   
      21       Spat    
      30       Spat    
      NA      NA
missing-data na r replace
2 Answers

This is a little long, but it should do the work.

sizes = unique(size)[!is.na(unique(size))]
props = c(1:length(sizes))
for (i in 1:length(sizes)) props[i] = length(Length[which(size == sizes[i])]) / length(Length[!is.na(Length)])
means = c(1:length(sizes))
for (i in 1:length(sizes)) means[i] = mean(Length[which(size == sizes[i])])

idx = round(cumsum(props) * sum(is.na(size)))
nass = c()
nals = c()
for (i in 1:length(idx)) nass = append(nass, rep(sizes[i], (idx[i] - length(nass))))
for (i in 1:length(idx)) nals = append(nals, rep(means[i], (idx[i] - length(nals))))
size[is.na(size)] = nass
Length[is.na(Length)] = nals

Let me explain what I do here. The following line gets all unique sizes into an array:

sizes = unique(size)[!is.na(unique(size))]

The following loop calculates the proportion of sizes that are not null.

props = c(1:length(sizes))
for (i in 1:length(sizes)) props[i] = length(Length[which(size == sizes[i])]) / length(Length[!is.na(Length)])

The following loop calculates the means for each size.

means = c(1:length(sizes))
for (i in 1:length(sizes)) means[i] = mean(Length[which(size == sizes[i])])

The following line calculates the number of missing (NA) cases that we need to fill proportionate to the non missing size values.

idx = round(cumsum(props) * sum(is.na(size)))

The following two loops creates the new values that we will input to the original dataset.

nass = c()
nals = c()
for (i in 1:length(idx)) nass = append(nass, rep(sizes[i], (idx[i] - length(nass))))
for (i in 1:length(idx)) nals = append(nals, rep(means[i], (idx[i] - length(nals))))

Finally we paste these new values to the original vectors (i.e., size and Length)

size[is.na(size)] = nass
Length[is.na(Length)] = nals
user_139023090

The following function does what the question asks for.
The format the values to be assigned is in is not clear, I assume a named vector.

The result is a named list, with members x, the new values, and groups, the new group variable values.

fill_perc <- function(x, groups, prob, values){
  stopifnot(length(prob) == length(values))
  prob <- prob/sum(prob)
  i <- which(is.na(x))
  j <- sample(length(values), size = length(i), prob = prob, replace = TRUE)
  x[i] <- values[j]
  groups[i] <- names(values)[j]
  list(x = x, groups = groups)
}

P <- c(11.8, 38, 50)
V <- setNames(c(29.5, 59.35, 97), c("Spat", "Small", "Market"))

set.seed(2020)
fill_perc(Length, size, P, V)
#$x
# [1]  81.00  66.00  44.00  59.35  29.00  24.00  68.00  97.00  92.00  21.00
#[11]  28.00  25.00  59.35  97.00  34.00  91.00  97.00  65.00  58.00 110.00
#[21]  52.00  48.00  96.00  95.00  54.00  40.00  98.00  63.00 138.00  30.00
#[31] 110.00
#
#$groups
# [1] "Market" "Small"  "Small"  "Small"  "Spat"   "Spat"   "Small"  "Market"
# [9] "Market" "Spat"   "Spat"   "Spat"   "Small"  "Market" "Spat"   "Market"
#[17] "Market" "Small"  "Small"  "Market" "Small"  "Small"  "Market" "Market"
#[25] "Small"  "Small"  "Market" "Small"  "Market" "Spat"   "Market"
user_82454060
Related