0

I am looking to melt a table, but some of the variables are similarly named and I'm having trouble using the patterns function.

Here is an example of what my data looks like:

Date ID X_a X_ab X_c X_d Y_a Y_ab Y_c Y_d
3/18/2022 10:57:17 PM 001 1 0 0 1 1 0 1 2

This is how I would like it to look like:

Date ID Group a ab c d
3/18/2022 10:57:17 PM 001 X 1 0 0 1
3/18/2022 10:57:17 PM 001 Y 1 0 1 2

When I use melt, first it has an issue because date is a different type than the remaining variables (character vs. integers) and secondly when I tried without date, the patterns uses "a" and finds additionally ab. Any ideas on how to get this done, either by modifying this method or using a totally different one? It's important to note that this should be something which can be run on large amounts of data.

Wayne
  • 15
  • 1
  • 7

1 Answers1

5
library(tidyverse)
pivot_longer(df, -c(Date, ID), names_to = c('Group', '.value'), names_sep = '_')

# A tibble: 2 x 7
  Date                     ID Group     a    ab     c     d
  <chr>                 <int> <chr> <int> <int> <int> <int>
1 3/18/2022 10:57:17 PM     1 X         1     0     0     1
2 3/18/2022 10:57:17 PM     1 Y         1     0     1     2
Onyambu
  • 67,392
  • 3
  • 24
  • 53