-1

I have a dataset of patient info, 25 DX codes, and 20 Procedure codes. Each code is in its own column so my table is 45 columns wide just between diagnosis and procedure codes. I need to get the dx codes into two columns DX1-25, and the the code, and Procedure code into and Proc1-proc20 and the codes.

Parsed down original data for ease of use

MRN <- c(1,2,3,4)
DX1 <- c('12','14','16','m78.2')
DX2 <- c('m46.2', 'z98.0', 'z86.711', 'm10.6')
DX3 <- c('m10.7', 'Z86.711', 'M45.1', 'K21.9') 
PROC1 <- c(06030, 06020, 06047, 22585)
PROC2 <- c(63020, 63030, 63047, 63030)
PROC3 <- c(22551, 22558, 22528, 22558)
spine_pt_3 <- as.data.frame(cbind(MRN, DX1, DX2, DX3, PROC1,PROC2, PROC3))

Code attempted to get data in desired format

spine3 <-  melt(setDT(spine_pt_3),
               id = 1,
               measure1 = list(2:4),
               measure2 = list (5:7),
               Variable1= "DX",
               variable2 = "Proc"
)

My goal is to get my data to look like this. I'm not sure if this is possible or when I'm going wrong.

enter image description here

r2evans
  • 141,215
  • 6
  • 77
  • 149
regents
  • 600
  • 6
  • 15

1 Answers1

1

data.table

melt(as.data.table(spine_pt_3), id.vars = "MRN",
     measure.vars = patterns(DX = "^DX", PROC = "^PROC"),
     variable.factor = FALSE)
#        MRN variable      DX   PROC
#     <char>   <char>  <char> <char>
#  1:      1        1      12   6030
#  2:      2        1      14   6020
#  3:      3        1      16   6047
#  4:      4        1   m78.2  22585
#  5:      1        2   m46.2  63020
#  6:      2        2   z98.0  63030
#  7:      3        2 z86.711  63047
#  8:      4        2   m10.6  63030
#  9:      1        3   m10.7  22551
# 10:      2        3 Z86.711  22558
# 11:      3        3   M45.1  22528
# 12:      4        3   K21.9  22558

tidyr

tidyr::pivot_longer(spine_pt_3, -MRN,
    names_pattern = "(DX|PROC)(.*)",
    names_to = c(".value", "codenum"))
# # A tibble: 12 x 4
#    MRN   codenum DX      PROC 
#    <chr> <chr>   <chr>   <chr>
#  1 1     1       12      6030 
#  2 1     2       m46.2   63020
#  3 1     3       m10.7   22551
#  4 2     1       14      6020 
#  5 2     2       z98.0   63030
#  6 2     3       Z86.711 22558
#  7 3     1       16      6047 
#  8 3     2       z86.711 63047
#  9 3     3       M45.1   22528
# 10 4     1       m78.2   22585
# 11 4     2       m10.6   63030
# 12 4     3       K21.9   22558
r2evans
  • 141,215
  • 6
  • 77
  • 149