Sorting columns according to rows' values

Andrzej · February 5, 2023, 1:51pm

Hi,
Here is my df:

df <- structure(list(
  Levels_of_LS = c(
    "not_applicable", "not_stresful",
    "slightly_stresful", "moderately_sresful", "very_ stresful"
  ),
  DES1 = c(69, 73, 49, 37, 15), DES10 = c(10, 54, 84, 59, 36), DES11 = c(78, 77, 48, 27, 13), DES12 = c(
    5, 11, 49, 67,
    111
  ), DES13 = c(197, 26, 12, 3, 5), DES14 = c(
    9, 71, 68,
    49, 46
  ), DES15 = c(5, 67, 85, 50, 36), DES16 = c(
    167, 49,
    14, 11, 2
  ), DES17 = c(5, 41, 75, 66, 56), DES18 = c(
    12, 94,
    71, 46, 20
  ), DES19 = c(13, 94, 70, 44, 22), DES2 = c(
    15,
    83, 65, 49, 31
  ), DES40 = c(31, 13, 50, 58, 91), DES5 = c(
    13,
    51, 75, 56, 48
  ), DES6 = c(8, 84, 78, 48, 25)
), class = c(
  "tbl_df",
  "tbl", "data.frame"
), row.names = c(NA, -5L))

I would like to group_by I think by " Levels_of_LS" and sort this df(columns) in descending order according to DES values in particular rows contributing to eg. not_applicable, not_stresful and so on. Basically I would like to know which DES contributes the most(MAX) and the least(MIN) to each level of Levels_of_LS variable ? I would be grateful for any help regarding this matter. Later I would like to present those results on the plot.

andresrcs · February 5, 2023, 3:13pm

I think there is no logical way to order columns taking each row into account, different "Levels_of_LS" have different DES contributions, how would you solve those discrepancies to define the column order for the whole data frame? Take a look at this to clarify my point:

library(tidyverse)

sample_df <- structure(list(
    Levels_of_LS = c(
        "not_applicable", "not_stresful",
        "slightly_stresful", "moderately_sresful", "very_ stresful"
    ),
    DES1 = c(69, 73, 49, 37, 15), DES10 = c(10, 54, 84, 59, 36), DES11 = c(78, 77, 48, 27, 13), DES12 = c(
        5, 11, 49, 67,
        111
    ), DES13 = c(197, 26, 12, 3, 5), DES14 = c(
        9, 71, 68,
        49, 46
    ), DES15 = c(5, 67, 85, 50, 36), DES16 = c(
        167, 49,
        14, 11, 2
    ), DES17 = c(5, 41, 75, 66, 56), DES18 = c(
        12, 94,
        71, 46, 20
    ), DES19 = c(13, 94, 70, 44, 22), DES2 = c(
        15,
        83, 65, 49, 31
    ), DES40 = c(31, 13, 50, 58, 91), DES5 = c(
        13,
        51, 75, 56, 48
    ), DES6 = c(8, 84, 78, 48, 25)
), class = c(
    "tbl_df",
    "tbl", "data.frame"
), row.names = c(NA, -5L))

sample_df %>% 
    pivot_longer(cols = starts_with("DES"), names_to = "DES", values_to = "Value") %>% 
    group_by(Levels_of_LS) %>% 
    filter(Value == max(Value) | Value == min(Value)) %>% 
    mutate(type = if_else(Value == max(Value), "Max", "Min"))
#> # A tibble: 13 × 4
#> # Groups:   Levels_of_LS [5]
#>    Levels_of_LS       DES   Value type 
#>    <chr>              <chr> <dbl> <chr>
#>  1 not_applicable     DES12     5 Min  
#>  2 not_applicable     DES13   197 Max  
#>  3 not_applicable     DES15     5 Min  
#>  4 not_applicable     DES17     5 Min  
#>  5 not_stresful       DES12    11 Min  
#>  6 not_stresful       DES18    94 Max  
#>  7 not_stresful       DES19    94 Max  
#>  8 slightly_stresful  DES13    12 Min  
#>  9 slightly_stresful  DES15    85 Max  
#> 10 moderately_sresful DES12    67 Max  
#> 11 moderately_sresful DES13     3 Min  
#> 12 very_ stresful     DES12   111 Max  
#> 13 very_ stresful     DES16     2 Min

^{Created on 2023-02-05 with reprex v2.0.2}

Andrzej · February 5, 2023, 4:11pm

This is I think impossible, what I did was taking each/row / level of Levels_of_LS and then DES.... columns and sort those DES columns in descending order:

and then I have made a simple plot:
obraz

in order to know which DESes are contributing the most to the particular level (here: not_applicable) and which are contributing less or minimal.
Now I want to do it in R for all levels and create aplot with ggplot if possible.

andresrcs · February 5, 2023, 5:02pm

Something like this?

library(tidyverse)
library(tidytext)

sample_df <- structure(list(
    Levels_of_LS = c(
        "not_applicable", "not_stresful",
        "slightly_stresful", "moderately_sresful", "very_ stresful"
    ),
    DES1 = c(69, 73, 49, 37, 15), DES10 = c(10, 54, 84, 59, 36), DES11 = c(78, 77, 48, 27, 13), DES12 = c(
        5, 11, 49, 67,
        111
    ), DES13 = c(197, 26, 12, 3, 5), DES14 = c(
        9, 71, 68,
        49, 46
    ), DES15 = c(5, 67, 85, 50, 36), DES16 = c(
        167, 49,
        14, 11, 2
    ), DES17 = c(5, 41, 75, 66, 56), DES18 = c(
        12, 94,
        71, 46, 20
    ), DES19 = c(13, 94, 70, 44, 22), DES2 = c(
        15,
        83, 65, 49, 31
    ), DES40 = c(31, 13, 50, 58, 91), DES5 = c(
        13,
        51, 75, 56, 48
    ), DES6 = c(8, 84, 78, 48, 25)
), class = c(
    "tbl_df",
    "tbl", "data.frame"
), row.names = c(NA, -5L))

sample_df %>% 
    pivot_longer(cols = starts_with("DES"), names_to = "DES", values_to = "Value") %>% 
    group_by(Levels_of_LS) %>% 
    arrange(Levels_of_LS, desc(Value)) %>% 
    mutate(importance = rep(1:3, each = 5)) %>% 
    ungroup() %>% 
    ggplot(aes(x = reorder_within(DES, by = desc(Value), Levels_of_LS), y = Value)) +
        geom_col(aes(fill = factor(importance)), show.legend = FALSE) +
        scale_x_reordered() +
        facet_wrap(vars(Levels_of_LS), scales = "free", ncol = 1) +
    labs(x = "DES")

^{Created on 2023-02-05 with reprex v2.0.2}

Andrzej · February 5, 2023, 5:27pm

Absolutely fantastic, I am very grateful to you, thank you very much indeed for your time and effort.
A few questions;
if I want to change blue with green colors - what should I do ?
If I want to change all three colors to different ones, what do I do ?
If I want to change order of levels so not_applicable goes up and moderately_stressful goes down just before very_stresful, what do I do, please ? That order is set in first dataframe.

andresrcs · February 5, 2023, 6:34pm

Use a manual scale to define the colors you want

Define the order with the levels of the factor variable

sample_df %>%
    mutate(Levels_of_LS = fct_inorder(Levels_of_LS)) %>% 
    pivot_longer(cols = starts_with("DES"), names_to = "DES", values_to = "Value") %>% 
    group_by(Levels_of_LS) %>% 
    arrange(Levels_of_LS, desc(Value)) %>% 
    mutate(importance = factor(rep(1:3, each = 5))) %>% 
    ungroup() %>% 
    ggplot(aes(x = reorder_within(DES, by = desc(Value), Levels_of_LS), y = Value)) +
    geom_col(aes(fill = importance), show.legend = FALSE) +
    scale_x_reordered() +
    scale_fill_manual(values = c('1' = 'red', '2' = 'blue', '3' = 'green')) +
    facet_wrap(vars(Levels_of_LS), scales = "free", ncol = 1) +
    labs(x = "DES")

Andrzej · February 5, 2023, 6:40pm

Thank you very much, this is exactly what I wanted.

One additional question only, how to put values on these color rectangles on the plot ?

andresrcs · February 5, 2023, 6:53pm

Use geom_text()

Andrzej · February 5, 2023, 6:55pm

Thank you I will read it.

Andrzej · February 5, 2023, 7:06pm

I have added this line to your code:

+ geom_text(aes(y=Value, label=Value))

and it works:

all the best to you.

andresrcs · February 5, 2023, 7:10pm

Play with the vjust parameter to better place the text over (or inside) the bars

Andrzej · February 6, 2023, 7:14am

This is a bit strange, when I loaded the following libraries:

library(tidyverse)
library(dplyr)
library(sjPlot)
library(sjmisc)
library(parameters)
library(HH)
library(likert)
require(grid)
require(lattice)
require(latticeExtra)
library(Hmisc)
library(labelled)
library(expss)
library(foreign)
library(easystats)
library(tidytext)

I got this error:

Error in eval(variables_names, envir = envir, enclos = baseenv()) : object 'Levels_of_LS' not found

I eliminated one by one and turned out that culprit was expss package. I don't know what expss has got to do with not finding 'Levels_of_LS' variable ? When I unloaded it everything worked fine.

andresrcs · February 6, 2023, 11:57am

Can you provide a REPRoducible EXample (reprex) for this?

nirgrahamuk · February 6, 2023, 12:05pm

The issue is that expss defines a vars function that would mask tidyverse.
so one could choose to load expss first; or else fully namespace the required vars , i.e. dplyr::vars()
or not use vars; but use the tilde instead

  facet_wrap(~Levels_of_LS, scales = "free", ncol = 1)

Andrzej · February 6, 2023, 6:05pm

Thank you, this is exactly what solves the problem.

>getAnywhere(vars)
2 differing objects matching ‘vars’ were found
in the following places
  package:expss
  package:dplyr
  package:ggplot2
  namespace:ggplot2
  namespace:dplyr
  namespace:expss
Use [] to view one of them

For the sake of completeness that following code can be used to prepare a reprex:

library(tidyverse)
library(dplyr)
library(sjPlot)
library(sjmisc)
library(parameters)
library(HH)
library(likert)
require(grid)
require(lattice)
require(latticeExtra)
library(Hmisc)
library(labelled)
library(expss)
library(foreign)
library(easystats)
library(tidytext)

sample_df <- structure(list(
    Levels_of_LS = c(
        "not_applicable", "not_stresful",
        "slightly_stresful", "moderately_sresful", "very_ stresful"
    ),
    DES1 = c(69, 73, 49, 37, 15), DES10 = c(10, 54, 84, 59, 36), DES11 = c(78, 77, 48, 27, 13), DES12 = c(
        5, 11, 49, 67,
        111
    ), DES13 = c(197, 26, 12, 3, 5), DES14 = c(
        9, 71, 68,
        49, 46
    ), DES15 = c(5, 67, 85, 50, 36), DES16 = c(
        167, 49,
        14, 11, 2
    ), DES17 = c(5, 41, 75, 66, 56), DES18 = c(
        12, 94,
        71, 46, 20
    ), DES19 = c(13, 94, 70, 44, 22), DES2 = c(
        15,
        83, 65, 49, 31
    ), DES40 = c(31, 13, 50, 58, 91), DES5 = c(
        13,
        51, 75, 56, 48
    ), DES6 = c(8, 84, 78, 48, 25)
), class = c(
    "tbl_df",
    "tbl", "data.frame"
), row.names = c(NA, -5L))

sample_df %>%
    mutate(Levels_of_LS = fct_inorder(Levels_of_LS)) %>% 
    pivot_longer(cols = starts_with("DES"), names_to = "DES", values_to = "Value") %>% 
    group_by(Levels_of_LS) %>% 
    arrange(Levels_of_LS, desc(Value)) %>% 
    mutate(importance = factor(rep(1:3, each = 5))) %>% 
    ungroup() %>% 
    ggplot(aes(x = reorder_within(DES, by = desc(Value), Levels_of_LS), y = Value)) +
    geom_col(aes(fill = importance), show.legend = FALSE) +
    scale_x_reordered() +
    scale_fill_manual(values = c('1' = 'red', '2' = 'blue', '3' = 'green')) +
    facet_wrap(vars(Levels_of_LS), scales = "free", ncol = 1) +
    labs(x = "DES")

system · February 13, 2023, 6:05pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.