Search code examples
rpivottidyverse

Pivot data by values separated by underscrores

I would like to turn data that looks like this:

library(dplyr)  

Data <- tibble(
      UID = c("VD3_HC45_T4839374", "KT6_TU45_T7837047","DE45_BH3_T9477277"),
      Merged = c("HB2_JN5", "HY38_BV3_NM43_JH56","WQ5_PI4"))

to this:

Output <- tibble(
      UID = c("VD3_HC45_T4839374", "VD3_HC45_T4839374", "KT6_TU45_T7837047", "KT6_TU45_T7837047", "KT6_TU45_T7837047", "KT6_TU45_T7837047","DE45_BH3_T9477277","DE45_BH3_T9477277"),
      Unmerged = c("HB2", "JN5", "HY38","BV36","NM43","JH56","WQ5","PI4"))

I've had a look at pivot_longer, but can't find a solution that separates the merged values by the underscores

Solution

  • We can use separate_rows

    library(tidyr)
separate_rows(Data, Merged)