How to move elements from one column to another conditionally

user_19243957 0
Viewed 60

I have a data frame that groups people by region and lists the number of people per income category in the region:

  Region Income1 Income2 Income3 
      A     100      12      44
      B      32       2      11
      C      18      41      55
      D       3      48       6

My goal is to move any elements with an insufficient population (less than 10), to an adjacent column with the lowest population.

For example, Region B's Income 2 only has a population of 2, so I'd like to set it to 0 and move its value to whichever adjacent income level in Region B has the smallest population. In this case, Income 3 has a smaller population than income 1, so Region B Income 2 would become 0, and Income 3 would become 13.

The resulting data frame should look something like this:

  Region Income1 Income2 Income3 
      A     100      12      44
      B      32       0      13
      C      18      41      55
      D       0      57       0

My thought is to filter in the regions that have a column with a sub-10 value and perform some sort of per-row function to accomplish this. Here is my code thusfar:

income %>% filter(if_any(Income1:Income3, ~. < 10)) %>% rowwise() %>% # some function to move all sub-10 values to the smallest adjacent column
r
1 Answers

This is kind of tricky logic, but this seems to work:

# get the rowsums of each value under 10
fill <- rowSums(replace(dat[-1], dat[-1] >= 10, 0))
# replace the rows under 10 with 0
dat[-1][dat[-1] < 10] <- 0

# save the (row/column) index of the minimum value in each row that isn't now 0
sel <- cbind(seq_len(nrow(dat)), max.col(-replace(dat[-1], dat[-1]==0, Inf)))
# add the rowsums of the under 10 values to the stored minimum values in `sel`
dat[-1][sel] <- dat[-1][sel] + fill

##  Region Income1 Income2 Income3
##1      A     100      12      44
##2      B      32       0      13
##3      C      18      41      55
##4      D       0      57       0
user_4968030
Related