How to reorder variables from (descending->ascending) ?

[rodneyp]

Hello,
I have a dataset where there are places visited listed in a descending order (variables A_1/2/3 in my example).
I would like to find an automated way to rearrange this order to be in an ascending order (variables B_1/2/3 in my example).

A_3 <- c("a", "b", "c")
A_2 <- c("d", "e")
A_1 <- c("f")
B_1 <- c("f", "e", "c")
B_2 <- c("d", "b")
B_3 <- c("a")

length(A_1)<-3; length(A_2)<-3; length(A_3)<-3;length(B_1)<-3; length(B_2)<-3; length(B_3)<-3
df <- as.data.frame (m <-cbind (A_3,A_2,A_1, B_1, B_2, B_3))
print (df)

How can I do that ? I can't figure it out
Many thanks
Best regards

[nirgrahamuk]

first I will reshare your starting data more concisely, also I use tidyverse so load that library

library(tidyverse)
df <- tibble(
  A_3 = c("a", "b", "c"),
  A_2 = c("d", "e", NA),
  A_1 = c("f", NA, NA),
  B_1 = c("f", "e", "c"),
  B_2 = c("d", "b", NA),
  B_3 = c("a", NA, NA)
)

my attempt to solve

(
long_fix <- pivot_longer(df %>% mutate(rn = row_number()),
  -rn,
  names_sep = "_",
  names_to = c("class", "num")
) %>%
  mutate(
    num = parse_integer(num)
  ) %>%
  arrange(rn, class, num) %>%
  group_by(rn, class) %>%
  mutate(new_num = max(num) + 1 - num) %>%
    ungroup) 

df2 <- pivot_wider(long_fix %>% select(-num),
            id_cols = rn,
            values_from = value,
            names_from = c(class,new_num),
            names_sep="_") %>% select(-rn)

(ndf2 <- sort(names(df2)))

(df_result <- df2 %>% select(all_of(ndf2)))

[rodneyp]

Hello,
Sadly it doesn't work

There is a difference between your results and the B1/2/3 column I'm trying to get (see my example data)

But thanks for trying and your answer !
Best regards

[nirgrahamuk]

Could you think of a different way to describe your requirements?
its quite confusing...
if I sort the df you provide.

df %>% select(sort(names(df)))

   A_1  A_2 A_3 B_1  B_2  B_3
1    f    d   a   f    d    a
2 <NA>    e   b   e    b <NA>
3 <NA> <NA>   c   c <NA> <NA>

how should this be understood. what is being rearranged to what in descending order?
is the first row to be unchanged, and only the second and third rows show difference ?

are we just sliding non missing values to the left ?

[rodneyp]

@nirgrahamuk

are we just sliding non missing values to the left ?

That's it !
I'm sorry if this is confusing. I'm having trouble to express what I'm trying to do in english

[nirgrahamuk]

This solution relies on assumption that the NA's/values across a row are contigious. i.e. NA's wont be to the right of any values on the starting dataframe.

library(tidyverse)

start_df <- data.frame(
  stringsAsFactors = FALSE,
  A_1 = c("f", NA, NA),
  A_2 = c("d", "e", NA),
  A_3 = c("a", "b", "c")
)

(goal_df <- data.frame(
  stringsAsFactors = FALSE,
  A_1 = c("f", "e", "c"),
  A_2 = c("d", "b", NA),
  A_3 = c("a", NA, NA)
))


(solved_df <- pivot_longer(start_df %>% 
                             mutate(rn = row_number()),
  -rn,
  names_sep = "_",
  names_to = c("class", "num")
) %>% na.omit() %>% 
    group_by(rn) %>% 
    mutate(num2 = row_number()) %>% 
    ungroup() %>% pivot_wider(
  id_cols = "rn",
  values_from = "value",
  names_from = c("class", "num2"),
  names_sep = "_"
) %>% select(-rn))