How adjust pivot_longer in a R code for no specific column name-CodePudding

The code below makes a correlation between Methods 2, 3 and 4 with Method 1. The method with the highest correlation is chosen. However, I would like some code changes.

See I do pivot_longer(cols = starts_with('METHOD')). However, I have an example (df2 dataframe) where the columns do not start with METHOD but are random names, for example the order of the columns is: Alternatives, DISTANCE, TOPSIS, PROMETHE, VIKOR. So, this code below is no longer valid for my example. How to adjust this, i.e. to be valid for both, I believe you need to adjust this pivot_longer question. Also, I believe there must be some code shorter than the one I made to generate the same desired result.

df<-structure(list(Alternatives = c(3, 4, 5, 6, 7, 8, 9, 10, 11, 12), 
    METHOD1 = c(1L, 10L, 7L, 8L, 9L, 6L, 5L, 3L, 4L, 2L),  METHOD2 = c(1L, 
    8L, 7L, 6L, 10L, 9L, 4L, 3L, 2L, 5L),  METHOD3 = c(1L, 10L, 
    7L, 8L, 9L, 6L, 4L, 2L, 3L, 5L),  METHOD4 = c(1, 9, 7, 6, 10, 
    8, 5, 4, 3, 2)), class = "data.frame", row.names = c(NA, 
-10L))

   Alternatives METHOD1 METHOD2 METHOD3 METHOD4
1             3       1       1       1       1
2             4      10       8      10       9
3             5       7       7       7       7
4             6       8       6       8       6
5             7       9      10       9      10
6             8       6       9       6       8
7             9       5       4       4       5
8            10       3       3       2       4
9            11       4       2       3       3
10           12       2       5       5       2


df %>%
  summarise(across(METHOD2:METHOD4,  ~cor.test(., METHOD1, method = "spearman")$estimate)) %>%
  as.data.frame() %>%
  pivot_longer(cols = starts_with('METHOD')) %>%
  slice_head(n=1) %>%
  slice_max(value) %>%
  pull(name)-> selected_method

df[(colnames(df) %in% c(selected_method))]

   METHOD2
1        1
2        8
3        7
4        6
5       10
6        9
7        4
8        3
9        2
10       5

df2

  df2<-structure(list(Alternatives = c(3, 4, 5, 6, 7, 8, 9, 10, 11, 12), 
    DISTANCE = c(1L, 10L, 7L, 8L, 9L, 6L, 5L, 3L, 4L, 2L),  TOPSIS = c(1L, 
    8L, 7L, 6L, 10L, 9L, 4L, 3L, 2L, 5L),  PROMETHE = c(1L, 10L, 
    7L, 8L, 9L, 6L, 4L, 2L, 3L, 5L),  VIKOR = c(1, 9, 7, 6, 10, 
    8, 5, 4, 3, 2)), class = "data.frame", row.names = c(NA, 
-10L))

CodePudding user response：

Based on the example code with 'df', pivot_longer is using all the columns in the dataset, thus can use select-helper everything()

library(dplyr)
library(tidyr)
df2 %>% 
   summarise(across(TOPSIS:VIKOR,   ~cor.test(., DISTANCE, 
       method = "spearman")$estimate)) %>%
  pivot_longer(cols = everything()) %>%
  slice_head(n=1) %>%
  slice_max(value) %>%
  pull(name) %>%
  select(df2, .)
 TOPSIS
1       1
2       8
3       7
4       6
5      10
6       9
7       4
8       3
9       2
10      5