How can I edit the iteration order in pivot_wider in R

Question

I converted df into a wide format (df_wide), specifying that the names from the 'moderator_cat' column in df should come first when creating the new column names on the df_wide data frame.

# create df
df <- data.frame(LIWC_name=c('rc_WC', 'rc_WC', 'rc_WC', 'rc_WC'),
                   OCEAN = c('O', 'O', 'E', 'E'),
                   moderator_cat = c('version', 'sample', 'version', 'sample'),
                   group1 = c(-.02, -.12, .34, .04),
                   group2 = c(-.13, .001, .12, .08),
                   group3 = c(NA, -.09, NA, .33))

# create wide version of df
df_wide <- pivot_wider(
  data = df,
  names_from = moderator_cat,
  names_sep = "_",
  values_from = c("group1":"group3"),
  names_glue = "{moderator_cat}_{.value}"
)

So far everything works, but I'd like the columns in df_wide to first go over all 'groups' (i.e., 'version_group1', 'version_group2', 'version_group3') and then do the same with the 'sample' variable (i.e., 'sample_group1', 'sample_group2', 'sample_group3'), instead of the order they have now (version_group1, sample_group1, version_group2, sample_group2, etc.). Is there a way to do this?

Couldn't you just reordner the columns afterwards with `select()`? — Taufi, Apr 28 '21 at 17:41
This is an open issue [#839](https://github.com/tidyverse/tidyr/issues/839) yet to be resolved. — AnilGoyal, Apr 28 '21 at 17:47

score 0 · Accepted Answer · answered Apr 28 '21 at 17:44

We can use str_sort

library(dplyr)
library(stringr)
df_wide %>%
   select(LIWC_name, OCEAN, str_sort(names(.)[-(1:2)], numeric = TRUE))

Or another option is select-helpers

df_wide %>%
  select(LIWC_name, OCEAN, starts_with('version'), starts_with('sample'))

-output

# A tibble: 2 x 8
#  LIWC_name OCEAN version_group1 version_group2 version_group3 sample_group1 sample_group2 sample_group3
#  <chr>     <chr>          <dbl>          <dbl>          <dbl>         <dbl>         <dbl>         <dbl>
#1 rc_WC     O              -0.02          -0.13             NA         -0.12         0.001         -0.09
#2 rc_WC     E               0.34           0.12             NA          0.04         0.08           0.33

How can I edit the iteration order in pivot_wider in R

1 Answers1