Alphabetically sort list in each table row

[da.thor]

Hello!

Is it possible to sort every single row within a data table (alphabetically)?

The first column shows different combinations of items while the second column contains the quantity of each item (which obviously shouldn't get lost in the sorting process).

My goal is to join duplicate variations in a second step.

The output should look something like this (in column 1):
1: c("Invisible Socks", "Invisible Socks")
2: c("Boxershorts", "Invisible Socks")
3: c("Boxershorts", "Invisible Socks")
and so on...

I'm really looking forward to any helpful answer!

[valeri]

Hi @da.thor,

Could you post a reprex for your data so that it is easy to recreate. That will make it much easier for anyone to help you - this is not a very standard data frame structure so it is not clear how you got it and how we can recreate it.

[da.thor]

That may be the worst approach ever. I'm still learning..

Hope that helps though.

EDIT: since the image seems not to be working right.. here's the code

multipleCarts <- data %>%
  select('Name', 'Paid at', 'Lineitem quantity', 'Lineitem name') %>%
  mutate(`Lineitem name` = case_when(
    str_detect(`Lineitem name`, "Boxershorts") ~ "Boxershorts",
    str_detect(`Lineitem name`, "Business Socken") ~ "Business Socks",
    str_detect(`Lineitem name`, "Business Socks") ~ "Business Socks",
    str_detect(`Lineitem name`, "Low Business Socks") ~ "Low Business Socks",
    str_detect(`Lineitem name`, "Invisible Socks") ~ "Invisible Socks",
    str_detect(`Lineitem name`, "Retro") ~ "Retro Socks",
    str_detect(`Lineitem name`, "Ankle") ~ "Ankle Socks",
    str_detect(`Lineitem name`, "Crew Socks") ~ "Crew Socks",
    TRUE ~ `Lineitem name`
  )
  ) %>%
  filter(`Lineitem name` == "Boxershorts" |
           `Lineitem name` == "Business Socks" |
           `Lineitem name` == "Low Business Socks" |
           `Lineitem name` == "Invisible Socks" |
           `Lineitem name` == "Retro Socks" |
           `Lineitem name` == "Ankle Socks" |
           `Lineitem name` == "Crew Socks"
  ) %>%
  group_by(`Name`) %>%
  replace(`.`, is.na(`.`), "2001-01-01") %>%
  mutate(`Paid at` = str_replace(`Paid at`, "\\s[^ ]+$", "")) %>%
  mutate(`Paid at` = str_replace(`Paid at`, "\\s[^ ]+$", "")) %>% # look for a better solution
  mutate(`Paid at` = ymd(`Paid at`)) %>% 
  mutate_at(vars(`Paid at`), funs(year, month, day)) %>%
  filter(`year` == 2019 & `month` > 6 | 
           `year` == 2001 & `month` == 1) %>%
  group_by(`Name`) %>%
  filter(n() > 1) %>% 
  summarise(`Lineitem name` = paste(`Lineitem name`, collapse=", ")) %>%
  mutate(`Name` = str_replace(`Name`, "#", ""))

multipleCarts$`Name` <- as.numeric(multipleCarts$`Name`)

multipleCartsSum <- multipleCarts %>%
  filter(`Name` > 20338) %>%
  group_by(`Lineitem name`) %>%
  summarise(n = n()) %>% 
  arrange(-n)  %>%
  mutate(`Lineitem name` = str_split(`Lineitem name`, ","))

[Yarnabrina]

Hi! I appreciate your effort, but it is not reproducible. We don't have access to data. Please check out the following FAQ.

I've tried to generate a fake dataset and have a solution, which I don't like myself, but I hope that will give others a chance to share their solutions.

set.seed(seed = 42079)

some_letters <- sample(x = letters[1:5],
                       size = 25,
                       replace = TRUE)

some_groups <- sample.int(n = 10,
                          size = 25,
                          replace = TRUE)

column_of_interest <- split(x = some_letters,
                            f = some_groups)

some_other_column <- sample.int(n = 10)

fake_dataset <- as.data.frame(x = cbind(some_other_column,
                                        column_of_interest))

within(data = fake_dataset,
       expr =
           {
               modified_column <- lapply(X = column_of_interest,
                                         FUN = sort)
           })
#>    some_other_column column_of_interest modified_column
#> 1                  3                  d               d
#> 2                  1               e, c            c, e
#> 3                  9            a, e, c         a, c, e
#> 4                  7                  c               c
#> 5                  4               a, c            a, c
#> 6                 10      e, a, a, d, b   a, a, b, d, e
#> 7                  8            d, a, b         a, b, d
#> 8                  5               c, d            c, d
#> 9                  6            d, e, b         b, d, e
#> 10                 2            e, d, d         d, d, e

^{Created on 2019-10-12 by the reprex package (v0.3.0)}

[da.thor]

Somehow the code isn't working with my dataset. I exactly copied it but the output looks something like this:

As you can see it only flips the list items around but does not order them alphabetically