Reverse the order of non-NA values in an r dataframe

I am looking to reverse the order of numeric values in multiple columns of an R dataframe (so that the highest number becomes the lowest, and so forth), whilst leaving the NA values as they are.

An example of my dataframe:

my_data <- data.frame (animal  = c("fox", "rabbit", "cow", "sheep", "pig", "mole"),
                        x = c("1", "2", "1", "3", "NA", 'NA'),
                       y = c('NA','NA','1','3','2','NA'),
                       z = c('1','2','3','4','NA','5'),
                       area = c("field","field","farm","farm","farm","farm"))

and then, what I am trying to achieve:

my_ideal_data <- data.frame (animal  = c("fox", "rabbit", "cow", "sheep", "pig", "mole"),
                             x = c("3", "2", "3", "1", "NA", 'NA'),
                             y = c('NA','NA','3','1','2','NA'),
                             z = c('5','4','3','2','NA','1'),
                             area = c("field","field","farm","farm","farm","farm"))

The 'animal' and 'area' column remain the same, as do all of the NAs - but I need the values in x, y and z to be placed in reverse order for each of the columns.

Any help would be greatly appreciated!

Thank you

In these data, you could simply subtract 6 from the z column after converting to numeric:

my_data$z <- 6 - as.numeric(my_data$z)  

#> my_data
#  animal  x  y  z  area
#1    fox  3 NA  5 field
#2 rabbit  2 NA  4 field
#3    cow  3  3  3  farm
#4  sheep  1  1  2  farm
#5    pig NA  2 NA  farm
#6   mole NA NA  1  farm

An alternative if these sample data are too simplified would be to index the non-NA values using grep, then sort by decreasing value using gtools::mixedsort(), then replace those values using [indexing]. This might be a little more scalable, and you dont have to convert to numeric.

idx <- grep("\\d+", my_data$z)
vals <- gtools::mixedsort(my_data$z[idx], decreasing = TRUE)
my_data$z[idx] <- vals

#  animal  x  y  z  area
#1    fox  3 NA  5 field
#2 rabbit  2 NA  4 field
#3    cow  3  3  3  farm
#4  sheep  1  1  2  farm
#5    pig NA  2 NA  farm
#6   mole NA NA  1  farm

If you wanted to apply it to multiple columns, you could wrap it in a functioning use lapply:

myfun <- function(x){
  a <-  grep("\\d+", x)
  x[a] <- gtools::mixedsort(x[a], decreasing = TRUE)
  x
}

my_data[c("x", "y", "z")] <- lapply(my_data[c("x", "y", "z")], myfun)

You might use a for loop here.

First replace all "NA" characters into real NA.

my_data[my_data == "NA"] <- NA

Then define a vector containing the columns that you want to sort.

target_col <- c("x", "y", "z")

And use a for loop to go over the target columns and perform replacement by deducting the column values by the max values + 1.

my_data[my_data == "NA"] <- NA

target_col <- c("x", "y", "z")
for (i in target_col) {
  my_data[!is.na(my_data[,i]),i] <- as.integer(max(my_data[,i], na.rm = T)) + 1 - as.integer(my_data[!is.na(my_data[,i]),i])
}

  animal    x    y    z  area
1    fox    3 <NA>    5 field
2 rabbit    2 <NA>    4 field
3    cow    3    3    3  farm
4  sheep    1    1    2  farm
5    pig <NA>    2 <NA>  farm
6   mole <NA> <NA>    1  farm