pivoting for messy data

user_6499643 0
Viewed 37

I have data that looks like this

structure(list(V1 = c("ABT", "AK", "AT", "AZ", "BG", "BT"), V2.1 = c("MLC_7", "MLC_07", "MLC_04", "MLC_07", "0", "0"), V2.2 = c("MLC_8", "0", "MLC_05", "RND_01", "0", "0"), V2.3 = c("0", "0", "0", "0", "0", "0"), V2.4 = c("0", "0", "0", "0", "0", "0"), V2.5 = c("0", "0", "0", "0", "0", "0")), row.names = c(NA, 6L), class = "data.frame")

enter image description here

i need work with variable in V2.1:V2.5 so that it becomes

V1 MLC_RND ID
ABT MLC 7
ABT MLC 8
AK MLC 7
AT MLC 4
AT MLC 5

How can i do this? i tried pivot_longer/wider but getting really confused....

Please help!

pivot r
2 Answers

We can use pivot_longer to reshape to 'long' format, then filter the 'MLC' column where the values are not '0' and separate into columns

library(dplyr)
library(tidyr)
df1 %>%
  pivot_longer(cols = V2.1:V2.5, values_to = 'MLC') %>% 
  dplyr::filter(MLC != "0") %>%
  separate(MLC, into = c("MLC_RND", "ID"))
user_37322710

Here is a data.table option

type.convert(
  na.omit(setDT(df)[
    ,
    .(X = unlist(.SD)), V1
  ][
    ,
    c(.(V1), setNames(tstrsplit(X, "_"), c("MLC_RND", "ID")))
  ]),
  as.is = TRUE
)

which gives

    V1 MLC_RND ID
1: ABT     MLC  7
2: ABT     MLC  8
3:  AK     MLC  7
4:  AT     MLC  4
5:  AT     MLC  5
6:  AZ     MLC  7
7:  AZ     RND  1
user_121587570
Related