# Sorting columns according to rows' values

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.

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

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:

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.

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

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.

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")
``````

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 ?

Use `geom_text()`

Thank you I will read it.

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

and it works:

all the best to you.

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

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.

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

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)``

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")``````

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.