How to get two columns from pivot_longer

user_15864414 0
Viewed 45

I have a dataframe that contains mean and sd. The dataframe look likes

Model_Name Dataset_Number Result_1_Mean Result_1_sd Result_2_Mean Result_2_sd Result_3_Mean Result_3_sd
   <chr>      <fct>                  <dbl>       <dbl>         <dbl>       <dbl>         <dbl>       <dbl>
 1 M_1        dataset_1             56.3         1.61         35.6         1.09        17.4         0.606 
 2 M_1        dataset_2             66.6         0.931        36.9         0.490       16.0         0.200 
 3 M_2        dataset_1             52.7         1.48         33.2         1.07        17.2         0.440 
 4 M_2        dataset_2             64.7         0.890        35.8         0.541       15.7         0.198 
 5 M_3        dataset_1             39.5         1.63         31.0         0.485       17.3         0.555 
 6 M_3        dataset_2             59.4         0.681        35.0         0.362       15.9         0.203 
 7 M_5        dataset_1             39.5         1.63         31.0         0.485       17.3         0.555

Now I want to convert the dataframe into a pivot_longer. I am using the below code

make_pivot_longer <- df_1 %>% gather(Result_Name, Result_Value, Result_1_Mean, Result_1_sd,Result_2_Mean, Result_2_sd,Result_3_Mean, Result_3_sd)

The output I am getting is like

   Model_Name Dataset_Number Result_Name   Result_Value
   <chr>      <fct>          <chr>                <dbl>
 1 M_1        dataset_1      Result_1_Mean         56.3
 2 M_1        dataset_2      Result_1_Mean         66.6
 3 M_2        dataset_1      Result_1_Mean         52.7
 4 M_2        dataset_2      Result_1_Mean         64.7
 5 M_3        dataset_1      Result_1_Mean         39.5
 6 M_3        dataset_2      Result_1_Mean         59.4
 7 M_5        dataset_1      Result_1_Mean         39.5
 8 M_5        dataset_2      Result_1_Mean         59.4
 9 M_4        dataset_1      Result_1_Mean         57.5
10 M_4        dataset_2      Result_1_Mean         31.4

I am getting mean and sd both in the Result_Name columns. But, I want the mean and sd in the different column.

Say, the expected output looks like

   Model_Name Dataset_Number Result_Name   Result_Mean   Result_Sd
   <chr>      <fct>          <chr>                <dbl>      <dbl>
 1 M_1        dataset_1      Result_1         56.3          1.20
 2 M_1        dataset_2      Result_1         66.6          2.03
 3 M_2        dataset_1      Result_1         52.7          0.05
 4 M_2        dataset_2      Result_1         64.7          5.06

Reproducible Data

df_1 <- structure(list(Model_Name = c("M_1", "M_1", "M_2", "M_2", "M_3", 
"M_3", "M_5", "M_5", "M_4", "M_4", "M_6", "M_6", "M_7", "M_7", 
"M_8", "M_8"), Dataset_Number = structure(c(1L, 2L, 1L, 2L, 
1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L), .Label = c("dataset_1", 
"dataset_2"), class = "factor"), Result_1_Mean = c(56.2825438620055, 
66.6442239087732, 52.7325534968645, 64.6535342402335, 39.5479141226974, 
59.4138479126739, 39.5479141226974, 59.4138479126739, 57.5402174648061, 
31.3552087504645, 63.6320734135852, 70.882815479768, 83.6930861233382, 
22.5230231229633, 5.11163337250294, 0.110342848845767), Result_1_sd = c(1.60530512109249, 
0.930828089504724, 1.48389734600799, 0.889887988854293, 1.63347517285651, 
0.681334694767627, 1.63347517285651, 0.681334694767627, 1.96753965756147, 
1.18730114529279, 1.68110682564507, 0.903672723329879, 6.3026995179373, 
1.82894252597019, 5.59105212339757, 0.169074977761456), Result_2_Mean = c(35.6378424218613, 
36.8592532979545, 33.1746900931854, 35.800341437544, 30.9550233556993, 
35.0144576861987, 30.9550233556993, 35.0144576861987, 41.6685970371373, 
39.2537436373919, 39.5005999891646, 38.587835397129, 60.4172142037466, 
15.1509806996773, 3.25980177679993, 0.145325630169788), Result_2_sd = c(1.08728309030594, 
0.490115202879507, 1.06924934395385, 0.541301806217695, 0.485420897674829, 
0.362244937017463, 0.485420897674829, 0.362244937017463, 1.23960313293375, 
0.49648969857036, 1.43134355059874, 0.523353969587384, 5.2848636398466, 
1.52505871922224, 3.09202665660649, 0.137804194536823), Result_3_Mean = c(17.4284724341723, 
15.9738121683694, 17.1757392425061, 15.7253225028994, 17.3476420027052, 
15.9230629844388, 17.3476420027052, 15.9230629844388, 17.2942924490357, 
15.904978122944, 17.4328632135356, 15.9738121683694, 39.5331576314268, 
9.06560167264891, 2.25437625433477, 0.086710964578538), Result_3_sd = c(0.605894566069689, 
0.199504674029123, 0.439534402691696, 0.197779898953328, 0.555136470391021, 
0.202516350391391, 0.555136470391021, 0.202516350391391, 0.646620888123093, 
0.201243691395992, 0.612605993579786, 0.199504674029123, 2.53065263183837, 
0.883972961967627, 1.37145910054188, 0.0444745905757077)), row.names = c(NA, 
-16L), groups = structure(list(Model_Name = c("M_1", "M_2", "M_3", 
"M_4", "M_5", "M_6", "SP_2", "SP_3"), .rows = structure(list(
    1:2, 3:4, 5:6, 9:10, 7:8, 11:12, 13:14, 15:16), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), row.names = c(NA, -8L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))
dplyr tidyverse r
2 Answers

This one could help too:

library(dplyr)
library(tidyr)

df1 %>%
  pivot_longer(!c(Model_Name, Dataset_Number), 
               names_to = c("Result_Name", ".value"),
               names_pattern = c("([[:alnum:]]+_\\d)_([[:alpha:]]+)")) %>%
  rename_with(~ paste("Result_", ., sep = ""), !contains("_"))

# A tibble: 48 x 5
# Groups:   Model_Name [8]
   Model_Name Dataset_Number Result_Name Result_Mean Result_sd
   <chr>      <fct>          <chr>             <dbl>     <dbl>
 1 M_1        dataset_1      Result_1           56.3     1.61 
 2 M_1        dataset_1      Result_2           35.6     1.09 
 3 M_1        dataset_1      Result_3           17.4     0.606
 4 M_1        dataset_2      Result_1           66.6     0.931
 5 M_1        dataset_2      Result_2           36.9     0.490
 6 M_1        dataset_2      Result_3           16.0     0.200
 7 M_2        dataset_1      Result_1           52.7     1.48 
 8 M_2        dataset_1      Result_2           33.2     1.07 
 9 M_2        dataset_1      Result_3           17.2     0.440
10 M_2        dataset_2      Result_1           64.7     0.890
# ... with 38 more rows
user_143145200

The data is grouped, which can be removed (ungroup), then change the column name substring by rearrangment to keep the digits part at the end using str_replace and use pivot_longer by specifying the '.value' part and use names_pattern to capture the substring of column name of interest that the first word and second word separated by _

library(dplyr)
library(tidyr)
library(stringr)
df_1 %>% 
   ungroup %>% 
   rename_with(~ str_replace(., "_(\\d+)_(\\w+)$",  "_\\2_\\1"), 
          starts_with('Result')) %>% 
   pivot_longer(cols = starts_with("Result"), 
      names_to = ".value", names_pattern = "(\\w+_\\w+)_\\d+")

-output

# A tibble: 48 x 4
   Model_Name Dataset_Number Result_Mean Result_sd
   <chr>      <fct>                <dbl>     <dbl>
 1 M_1        dataset_1             56.3     1.61 
 2 M_1        dataset_1             35.6     1.09 
 3 M_1        dataset_1             17.4     0.606
 4 M_1        dataset_2             66.6     0.931
 5 M_1        dataset_2             36.9     0.490
 6 M_1        dataset_2             16.0     0.200
 7 M_2        dataset_1             52.7     1.48 
 8 M_2        dataset_1             33.2     1.07 
 9 M_2        dataset_1             17.2     0.440
10 M_2        dataset_2             64.7     0.890
# … with 38 more rows

If we need the suffix stub, the names_to as a vector of two and subsequently capture the \\d+ at the end as well

df_1 %>% 
   ungroup %>% 
   rename_with(~ str_replace(., "_(\\d+)_(\\w+)$",  "_\\2_\\1"), 
          starts_with('Result')) %>% 
   pivot_longer(cols = starts_with("Result"), 
      names_to = c(".value", "Result_Name"), names_pattern = "(\\w+_\\w+)_(\\d+)")  %>%
   mutate(Result_Name = str_c('Result_', Result_Name))
# A tibble: 48 x 5
   Model_Name Dataset_Number Result_Name Result_Mean Result_sd
   <chr>      <fct>          <chr>             <dbl>     <dbl>
 1 M_1        dataset_1      Result_1           56.3     1.61 
 2 M_1        dataset_1      Result_2           35.6     1.09 
 3 M_1        dataset_1      Result_3           17.4     0.606
 4 M_1        dataset_2      Result_1           66.6     0.931
 5 M_1        dataset_2      Result_2           36.9     0.490
 6 M_1        dataset_2      Result_3           16.0     0.200
 7 M_2        dataset_1      Result_1           52.7     1.48 
 8 M_2        dataset_1      Result_2           33.2     1.07 
 9 M_2        dataset_1      Result_3           17.2     0.440
10 M_2        dataset_2      Result_1           64.7     0.890
# … with 38 more rows
user_37322710
Related