How to collapse dataframe columns by character in the middle of column name with varying column names?

user_7939268 0
Viewed 38

This is similar to a question I asked earlier, but I left out a couple of important pieces: an ID column and the XYZ variables.

I have a dataset with the following layout (strange column titles, I know):

ID <- c(1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 2.0)
XYZ1_a <- c(1, 2, 1, 2, 1, 2, 4, 2, 5, 1)
XYZ1_b <- c(2, 1, 1, 1, 2, 2, 4, 2, 1, 5)
ABC1a_1 <- c(1, 5, 3, 4, 3, 4, 5, 2, 2, 1)
ABC1b_1 <- c(4, 2, 1, 1, 5, 3, 2, 1, 1, 5)
ABC1a_2 <- c(4, 5, 5, 4, 2, 5, 5, 1, 2, 4)
ABC1b_2 <- c(2, 3, 3, 2, 2, 3, 2, 1, 4, 2)
ABC2a_1 <- c(2, 5, 3, 5, 3, 4, 5, 3, 2, 3)
ABC2b_1 <- c(1, 2, 2, 4, 5, 3, 2, 4, 1, 4)
ABC2a_2 <- c(2, 5, 5, 1, 2, 1, 5, 1, 3, 4)
ABC2b_2 <- c(2, 3, 3, 2, 1, 3, 1, 1, 2, 2)

df <- data.frame(ID, XYZ1_a, XYZ1_b, ABC1a_1, ABC1b_1, ABC1a_2, ABC1b_2, ABC2a_1, ABC2b_1, ABC2a_2, ABC2b_2)

I want to collapse all of the ABC[N][x]_[n] variables into a single ABC[N]_[n] variable like the below, but I also need to do the same for the columns with the XYZ naming convention:

ID <- c(1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 2.0, 
        1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 2.0)
XYZ1 <- c(1, 2, 1, 2, 1, 2, 4, 2, 5, 1, 2, 1, 1, 1, 2, 2, 4, 2, 1, 5)
ABC1_1 <- c(1, 5, 3, 4, 3, 4, 5, 2, 2, 1, 4, 2, 1, 1, 5, 3, 2, 1, 1, 5)
ABC1_2 <- c(4, 5, 5, 4, 2, 5, 5, 1, 2, 4, 2, 3, 3, 2, 2, 3, 2, 1, 4, 2)
ABC2_1 <- c(2, 5, 3, 5, 3, 4, 5, 3, 2, 3, 1, 2, 2, 4, 5, 3, 2, 4, 1, 4)
ABC2_2 <- c(2, 5, 5, 1, 2, 1, 5, 1, 3, 4, 2, 3, 3, 2, 1, 3, 1, 1, 2, 2)

df2 <- data.frame(ID, XYZ1, ABC1_1, ABC1_2, ABC2_1, ABC2_2)

What's the best way to achieve this, ideally with a tidyverse solution?

tidyverse r
1 Answers

We can use rearrange the substrings in those column names that starts_with 'ABC' by capturing the letter as a group followed by the underscore and one or more digits (\\d+) as second group, in the replacement specify the backreference in reverse while adding a new _. In pivot_longer, specify the sep to match the _ that precedes a letter

library(dplyr)
library(stringr)
library(tidyr)
df %>% 
   rename_with(~ str_replace(., "([a-z])_(\\d+)$", "_\\2_\\1"), 
      starts_with('AB')) %>%
   pivot_longer(cols = -ID, names_to = c(".value", "grp"), 
      names_sep = "_(?=[a-z])", values_drop_na = TRUE) %>%
   select(-grp)

-output

# A tibble: 20 x 6
      ID  XYZ1 ABC1_1 ABC1_2 ABC2_1 ABC2_2
   <dbl> <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1   1.1     1      1      4      2      2
 2   1.1     2      4      2      1      2
 3   1.2     2      5      5      5      5
 4   1.2     1      2      3      2      3
 5   1.3     1      3      5      3      5
 6   1.3     1      1      3      2      3
 7   1.4     2      4      4      5      1
 8   1.4     1      1      2      4      2
 9   1.5     1      3      2      3      2
10   1.5     2      5      2      5      1
11   1.6     2      4      5      4      1
12   1.6     2      3      3      3      3
13   1.7     4      5      5      5      5
14   1.7     4      2      2      2      1
15   1.8     2      2      1      3      1
16   1.8     2      1      1      4      1
17   1.9     5      2      2      2      3
18   1.9     1      1      4      1      2
19   2       1      1      4      3      4
20   2       5      5      2      4      2

In the older version use rename_at

df %>% 
   rename_at(vars(starts_with('AB')), 
        ~ str_replace(., "([a-z])_(\\d+)$", "_\\2_\\1")) %>%
   pivot_longer(cols = -ID, names_to = c(".value", "grp"), 
      names_sep = "_(?=[a-z])", values_drop_na = TRUE) %>%
   select(-grp)

-output

# A tibble: 20 x 6
      ID  XYZ1 ABC1_1 ABC1_2 ABC2_1 ABC2_2
   <dbl> <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1   1.1     1      1      4      2      2
 2   1.1     2      4      2      1      2
 3   1.2     2      5      5      5      5
 4   1.2     1      2      3      2      3
 5   1.3     1      3      5      3      5
 6   1.3     1      1      3      2      3
 7   1.4     2      4      4      5      1
 8   1.4     1      1      2      4      2
 9   1.5     1      3      2      3      2
10   1.5     2      5      2      5      1
11   1.6     2      4      5      4      1
12   1.6     2      3      3      3      3
13   1.7     4      5      5      5      5
14   1.7     4      2      2      2      1
15   1.8     2      2      1      3      1
16   1.8     2      1      1      4      1
17   1.9     5      2      2      2      3
18   1.9     1      1      4      1      2
19   2       1      1      4      3      4
20   2       5      5      2      4      2
user_37322710
Related