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

user_7939268 0
Viewed 228

I have a dataset with the following layout:

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(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 this:

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(ABC1_1, ABC1_2, ABC2_1, ABC2_2)

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

tidyverse r
4 Answers

You could also use pivot_longer:

df %>%
  rename_with(~str_replace(.x, "(.)(_\\d)", "\\2:\\1")) %>%
  pivot_longer(everything(), names_sep = ':', names_to = c(".value", "group")) %>%
  arrange(group)

# A tibble: 20 x 5
   group ABC1_1 ABC1_2 ABC2_1 ABC2_2
   <chr>  <dbl>  <dbl>  <dbl>  <dbl>
 1 a          1      4      2      2
 2 a          5      5      5      5
 3 a          3      5      3      5
 4 a          4      4      5      1
 5 a          3      2      3      2
 6 a          4      5      4      1
 7 a          5      5      5      5
 8 a          2      1      3      1
 9 a          2      2      2      3
10 a          1      4      3      4
11 b          4      2      1      2
12 b          2      3      2      3
13 b          1      3      2      3
14 b          1      2      4      2
15 b          5      2      5      1
16 b          3      3      3      3
17 b          2      2      2      1
18 b          1      1      4      1
19 b          1      4      1      2
20 b          5      2      4      2

If you desire to go the Base R way, you could do:

reshape(df, split(names(df), sub("._", "_", names(df))), dir="long")
     time ABC1a_1 ABC1a_2 ABC2a_1 ABC2a_2 id
1.1     1       1       4       2       2  1
2.1     1       5       5       5       5  2
3.1     1       3       5       3       5  3
4.1     1       4       4       5       1  4
5.1     1       3       2       3       2  5
6.1     1       4       5       4       1  6
7.1     1       5       5       5       5  7
8.1     1       2       1       3       1  8
9.1     1       2       2       2       3  9
10.1    1       1       4       3       4 10
1.2     2       4       2       1       2  1
2.2     2       2       3       2       3  2
3.2     2       1       3       2       3  3
4.2     2       1       2       4       2  4
5.2     2       5       2       5       1  5
6.2     2       3       3       3       3  6
7.2     2       2       2       2       1  7
8.2     2       1       1       4       1  8
9.2     2       1       4       1       2  9
10.2    2       5       2       4       2 10

Then you can change the names.

If you care about the names from the very beginning:

df1 <- setNames(df, gsub("(.)(_\\d)", "\\2.\\1", names(df)))
reshape(df1, names(df1), dir = "long")

   time ABC1_1 ABC1_2 ABC2_1 ABC2_2 id
1     a      1      4      2      2  1
2     a      5      5      5      5  2
3     a      3      5      3      5  3
4     a      4      4      5      1  4
5     a      3      2      3      2  5
6     a      4      5      4      1  6
7     a      5      5      5      5  7
8     a      2      1      3      1  8
9     a      2      2      2      3  9
10    a      1      4      3      4 10
11    b      4      2      1      2  1
12    b      2      3      2      3  2
13    b      1      3      2      3  3
14    b      1      2      4      2  4
15    b      5      2      5      1  5
16    b      3      3      3      3  6
17    b      2      2      2      1  7
18    b      1      1      4      1  8
19    b      1      4      1      2  9
20    b      5      2      4      2 10
user_83802720

A base R solution to collapse it:

res <- as.data.frame(lapply(split.default(df, sub('._', '_', names(df))), unlist))
rownames(res) <- NULL
# >res
#    ABC1_1 ABC1_2 ABC2_1 ABC2_2
# 1       1      4      2      2
# 2       5      5      5      5
# 3       3      5      3      5
# 4       4      4      5      1
# 5       3      2      3      2
# 6       4      5      4      1
# 7       5      5      5      5
# 8       2      1      3      1
# 9       2      2      2      3
# 10      1      4      3      4
# 11      4      2      1      2
# 12      2      3      2      3
# 13      1      3      2      3
# 14      1      2      4      2
# 15      5      2      5      1
# 16      3      3      3      3
# 17      2      2      2      1
# 18      1      1      4      1
# 19      1      4      1      2
# 20      5      2      4      2

identical(df2, res)
# [1] TRUE

Using rowSums as the function to combine column values would be better I guess:

> as.data.frame(lapply(split.default(df, sub('._', '_', names(df))), rowSums))
   ABC1_1 ABC1_2 ABC2_1 ABC2_2
1       5      6      3      4
2       7      8      7      8
3       4      8      5      8
4       5      6      9      3
5       8      4      8      3
6       7      8      7      4
7       7      7      7      6
8       3      2      7      2
9       3      6      3      5
10      6      6      7      6
user_39265430

You could do:

library(tidyverse)

map_dfr(c("a", "b"), 
        ~df %>%
          select(contains(.x, ignore.case = FALSE)) %>%
          rename_all(funs(str_remove_all(., .x))))

#ABC1_1 ABC1_2 ABC2_1 ABC2_2
#1       1      4      2      2
#2       5      5      5      5
#3       3      5      3      5
#4       4      4      5      1
# ..

Depending on your actual data, you could replace c("a", "b") with letters[1:2] or unique(str_extract(colnames(df), "[a-z]")).

user_61620110 
library(tidyr)
library(dplyr)

df %>% 
  pivot_longer(everything()) %>% 
  arrange(name) %>% 
  mutate(name = gsub("[a-z]_", "_", name)) %>% 
  pivot_wider(values_fn = list) %>% 
  unchop(everything())

pivot_longer will put all the column names into a single column name that you can then edit by removing the lowercase letter preceding the underscore.

Then when you pivot back to a wide format the columns will automatically group. The output of pivot_wider are list-columns, unchop will convert these lists into a longer dataframe.

Output

   ABC1_1 ABC1_2 ABC2_1 ABC2_2
    <dbl>  <dbl>  <dbl>  <dbl>
 1      1      4      2      2
 2      5      5      5      5
 3      3      5      3      5
 4      4      4      5      1
 5      3      2      3      2
 6      4      5      4      1
 7      5      5      5      5
 8      2      1      3      1
 9      2      2      2      3
10      1      4      3      4
11      4      2      1      2
12      2      3      2      3
13      1      3      2      3
14      1      2      4      2
15      5      2      5      1
16      3      3      3      3
17      2      2      2      1
18      1      1      4      1
19      1      4      1      2
20      5      2      4      2
user_63824340
Related