Reshape Data Frame from Long to Wide with Condensed Columns

Question

Say I have (fake) patient data from their visits:

## Create a fake dataframe  
foo <- data.frame(PatientNumber=c(11,11,11,22,22,33,33,33,44,55,55), 
      VisitDate=c("11/03/07","11/03/07","11/20/07","12/20/08", 
      "12/30/09","09/20/12","09/20/12","10/25/07","05/09/08","06/09/13","06/09/13"), 
       ICD9=c(10,15,10,30,30,25,60,25,14,40,13))

Which gives:

   PatientNumber VisitDate ICD9
1             11  11/03/07   10
2             11  11/03/07   15
3             11  11/20/07   10
4             22  12/20/08   30
5             22  12/30/09   30
6             33  09/20/12   25
7             33  09/20/12   60
8             33  10/25/07   25
9             44  05/09/08   14
10            55  06/09/13   40
11            55  06/09/13   13

I would like to have a unique row for each patient at a given visit date. If the patient has multiple codes for a date, I would like a new column for all ICD code given at that visit. This is what it would look like:

WhatIWant <- data.frame(PatientNumber=c(11,11,22,22,33,33,44,55), 
                    VisitDate=c("11/03/07", "11/20/07", "12/20/08", "12/30/09", "09/20/12","10/25/07","05/09/08","06/09/13"), 
                    ICD9_1=c(10,10,30,30,25,25,14,40), 
                    ICD9_2= c(15,NA,NA,NA,60,NA,NA,13))




> WhatIWant
  PatientNumber VisitDate ICD9_1 ICD9_2
1            11  11/03/07     10     15
2            11  11/20/07     10     NA
3            22  12/20/08     30     NA
4            22  12/30/09     30     NA
5            33  09/20/12     25     60
6            33  10/25/07     25     NA
7            44  05/09/08     14     NA
8            55  06/09/13     40     13

I've tried reshape, but it seems to add all the ICD9 codes in a column and add the value in a column if they have a value or not (as shown below).I will end up with something like 200 columns, I would only like 3 (the max # of codes per patient per visit in the data set I have, ie ICD9_1, ICD9_2, ICD9_3).

test <- reshape(foo, idvar = c("VisitDate"), timevar = c("PatientNumber"), direction = "wide")

> test
    VisitDate ICD9.11 ICD9.22 ICD9.33 ICD9.44 ICD9.55
1  0007-11-03      10      NA      NA      NA      NA
3  0007-11-20      10      NA      NA      NA      NA
4  0008-12-20      NA      30      NA      NA      NA
5  0009-12-30      NA      30      NA      NA      NA
6  0012-09-20      NA      NA      25      NA      NA
8  0007-10-25      NA      NA      25      NA      NA
9  0008-05-09      NA      NA      NA      14      NA
10 0013-06-09      NA      NA      NA      NA      40

Sorry if the title isn't as specific as it could be, I'm not really sure how to exactly title what I am looking for.

Thanks in advance for your help!

akrun · Answer 1 · 2014-06-29T16:34:37.267

7

Also,

library(dplyr)
library(tidyr) # See below on how to get tidyr

foo %>% 
  group_by(PatientNumber, VisitDate) %>%
  mutate(n=paste("ICD9",row_number(), sep="_")) %>%
  spread(n, ICD9)

 #Source: local data frame [8 x 4]

#  PatientNumber VisitDate ICD9_1 ICD9_2
#1            11  11/03/07     10     15
#2            11  11/20/07     10     NA
#3            22  12/20/08     30     NA
#4            22  12/30/09     30     NA
#5            33  09/20/12     25     60
#6            33  10/25/07     25     NA
#7            44  05/09/08     14     NA
#8            55  06/09/13     40     13

Package tidyr is not available on CRAN yet. Install it like this (see tidyr git):

# install.packages("devtools")
devtools::install_github("hadley/tidyr")

edited Jun 29 '14 at 16:34

answered Jun 28 '14 at 15:48

akrun

874,273
37
540
662

+1 for the excellent use of `n()` inside `mutate`! I had no idea it would work outside of `summarise`! – AndrewMacDonald Jun 28 '14 at 17:03
I made a tiny edit, and changed `seq` to `seq_len`, just because it is faster and a bit more readable IMHO. – AndrewMacDonald Jun 28 '14 at 17:04
I copied/pasted "devtools::install_github("hadley/tidyr") into R and it wouldn't download? – James Jun 28 '14 at 18:05
`AndrewMacDonald`: Thanks for the edit. `James`: Are you getting any error messages while downloading? I am using R 3.1.0 on a linux platform. – akrun Jun 29 '14 at 02:31
Even better, you could use `row_number()` – hadley Jun 29 '14 at 16:27
It says "error in loadNamespace(name) : there is no packaged called 'devtools'. Mac Mavericks. RStudio v 0.98.953 @AndrewMacDonald – James Jun 30 '14 at 01:46
@James. Seems like you have to install package 'devtools'. If you have errors in installing that package, please check this link. – akrun Jun 30 '14 at 02:48

score 6 · Accepted Answer · answered Jun 28 '14 at 15:27

The basic problem for reshape in this case is that it doesn't have a real "time" variable. That's easy to create with ave:

foo$time <- with(foo, ave(rep(1, nrow(foo)), 
                          PatientNumber, VisitDate, 
                          FUN = seq_along))

Then, you can use reshape as follows:

reshape(foo, direction = "wide", 
        idvar=c("PatientNumber", "VisitDate"), 
        timevar="time")
#    PatientNumber VisitDate ICD9.1 ICD9.2
# 1             11  11/03/07     10     15
# 3             11  11/20/07     10     NA
# 4             22  12/20/08     30     NA
# 5             22  12/30/09     30     NA
# 6             33  09/20/12     25     60
# 8             33  10/25/07     25     NA
# 9             44  05/09/08     14     NA
# 10            55  06/09/13     40     13

Of course, once you have that "time" variable, you can also use dcast from "reshape2".

library(reshape2)
dcast(foo, PatientNumber + VisitDate ~ time, value.var="ICD9")

alexis_laz · Answer 3 · 2014-06-29T07:43:50.570

You could use aggregate:

max_visits = 2
aggregate(ICD9 ~ PatientNumber + VisitDate, foo, 
          function(x) x[seq_len(max_visits)])  #note that output is 3 columns
#  PatientNumber VisitDate ICD9.1 ICD9.2
#1            44  05/09/08     14     NA
#2            55  06/09/13     40     13
#3            33  09/20/12     25     60
#4            33  10/25/07     25     NA
#5            11  11/03/07     10     15
#6            11  11/20/07     10     NA
#7            22  12/20/08     30     NA
#8            22  12/30/09     30     NA

If you don't know the maximum possible visits ("max_visits"), you could:

max_visits = max(ave(foo[["ICD9"]], 
                     foo[["PatientNumber"]], foo[["VisitDate"]],
                     FUN = length))
max_visits
#[1] 2

EDIT:

As noted by @AnandaMahto in the comments you could turn your 3-column aggregated "foo" (say "aggfoo") to 4 columns with something like:

dim(aggfoo)
#[1] 8 3
dim(do.call(data.frame, aggfoo))
#[1] 8 4
dim(data.frame(unclass(aggfoo)))
#[1] 8 4

That's not necessary, though, as even with 3 columns it's still convenient to call each "ICD9" column: aggfoo$ICD9[, 1] and aggfoo$ICD9[, 2] instead of aggfoo$ICD9.1 and aggfoo$ICD9.2.

+1. Interesting approach. I've posted a variant that uses `ave` + `reshape`. — A5C1D2H2I1M1N2O1R2T1, Jun 28 '14 at 15:28
Oh, and to "flatten" that output from `aggregate` and have four columns instead of three that look like four, you could wrap it in a `do.call(data.frame, ...)`. — A5C1D2H2I1M1N2O1R2T1, Jun 28 '14 at 15:32

Reshape Data Frame from Long to Wide with Condensed Columns

3 Answers3