R: pivot columns into one create a new column with corresponding names values-CodePudding

I have the following data

structure(list(gid = c(100468, 100468, 100468, 100468, 100468, 
100468, 100468, 100468, 100468, 100468, 100468, 100468, 100468, 
100468, 100468, 100468, 100468, 100468, 100468, 100468), Year = c(1981, 
1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989, 1990, 1991, 1992, 
1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000), vci.mean.gs = c(NA, 
40.8501562362505, 17.2775675296783, 27.1988922405243, 37.3906853040059, 
40.1524439048767, 3.15680910547574, 6.4961281478405, 30.3797322034836, 
16.717754483223, 23.582529964447, 15.2226583766937, 21.9147656393051, 
15.8156257059425, 36.5401436090469, 27.6441525602341, 21.2505938911438, 
53.632869644165, 28.6508311843872, 9.82323584884405), tci.mean.gs = c(NA, 
"29.4991391064669", "23.1215536618233", "45.1472105026245", "42.5787233352661", 
"17.6468825054169", "34.7647177791596", "40.2009972000122", "7.28406430669129", 
"20.6270530033112", "12.1565699648857", "25.0925471115112", "44.2691400718689", 
"64.0467380523682", "24.3653798103333", "3.18143320083618", "15.5491060853004", 
"12.4735373210907", "34.3269439125061", "26.5852192985515"), 
    spei01.old.mean.gs = c(-1.08677153587341, 0.382637619972228, 
    -0.511927305161953, 0.0359220325946804, -0.32017160654068, 
    -0.272369506955147, -0.237179812043906, -0.377008521556855, 
    0.476858854293825, 0.0326932758092878, 0.315470723807812, 
    -0.413374470174312, 0.095124852657318, 0.113422942161558, 
    -1.25062608718872, -1.47046777009964, 0.118628397583962, 
    -0.174064328568056, -0.318255767226219, 0.467556968331337
    )), class = c("grouped_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -20L), groups = structure(list(Year = c(1981, 
1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989, 1990, 1991, 1992, 
1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000), .rows = structure(list(
    1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 
    15L, 16L, 17L, 18L, 19L, 20L), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -20L), .drop = TRUE))

I would like to combine the values from vci.mean.gs, tci.mean.gs and spei01.old.mean.gs into one column "Values" but at the same time create a new factor column 'Variable' that would indicate if the corresponding row value is VCI, TCI or SPEI.

CodePudding user response：

We may reshape to long with pivot_longer after changing the class of columns (as some "mean" columns are not numeric)

library(dplyr)
library(tidyr)
 df1 %>% 
   ungroup %>%
   type.convert(as.is = TRUE) %>% 
   pivot_longer(cols = contains("mean"), names_to = 'Variable', 
        values_to = "Values", values_drop_na = TRUE)