0

I have the following data.table:

    CASEID VISIT AVWEIGHT med.corrected DLYDOSE DLYFREQ
 1:   1004    10     55.5    LISINOPRIL   20.00       2
 2:   1004    20     53.9    LISINOPRIL   10.00       1
 3:   1004    30     60.4    LISINOPRIL   10.00       1
 4:   1004    40     61.3    LISINOPRIL   10.00       1
 5:   1044    10     24.7    LISINOPRIL    2.50       1
 6:   1044    20     28.1    LISINOPRIL    2.50       1
 7:   1072    10     17.3    AMLODIPINE    2.50       1
 8:   1072    20     18.3   CANDESARTAN    2.00       1
 9:   1072    20     18.3    AMLODIPINE    1.25       1
10:   1072    30     20.9   CANDESARTAN    4.00       1
11:   1072    30     20.9    AMLODIPINE    2.50       1
12:   1072    40       NA   CANDESARTAN    4.00       1
13:   1072    40       NA    AMLODIPINE    2.50       1
14:   1072    60     29.6   CANDESARTAN    4.00       1
15:   1072    60     29.6    AMLODIPINE    2.50       1
16:   1072    70     34.1   CANDESARTAN    4.00       1
17:   1072    70     34.1    AMLODIPINE    2.50       1
18:   1072    80     42.0    LISINOPRIL    2.50       1
19:   1072    80     42.0    AMLODIPINE    2.50       1
20:   1072    90     49.8    AMLODIPINE    2.50       1
21:   1078    10     68.1    LISINOPRIL   20.00       1
22:   1092    10    108.4    LISINOPRIL   40.00       1
23:   1092    20    120.5    LISINOPRIL   40.00       1
24:   1092    30    131.5    LISINOPRIL   40.00       1
25:   1092    40    123.1    LISINOPRIL   40.00       1
26:   1096    10    129.3    AMLODIPINE   15.00       1
27:   1100    10     56.3    LISINOPRIL   10.00       1
28:   1100    20     72.8    LISINOPRIL   10.00       1
29:   1132    10     52.2    LISINOPRIL    5.00       1
30:   1132    20     52.3    LISINOPRIL    5.00       1

Note that for some CASEID/VISIT/AVWEIGHT combinations, there are multiple different medications (med.corrected) and each has their corresponding DLYDOSE and DLYFREQ (see lines 8 and 9, for example). I know that in all the data, there are about 800 unique CASEIDs and there are about 20 different medications of interest.

I would like to rearrange this into a data.table with headings that looks like the one below. The key is that each row should represent all medications and their dosing info for a given CASEID at a given VISIT:

CASEID VISIT AVWEIGHT med.corrected_1 med.corrected_2  med.corrected_3 ... med.corrected_20

The values of DLYDOSE for each medication should be in the columns for med.corrected_1 through med.corrected_20.

It may be obvious but most patients will have NA for most of the medications on the columns above, as they will likely only be taking 1 or 2 medications. Nevertheless, for my analysis, I would like to arrange it as above. I am relatively new to R but have checked out a few tutorials and questions of which I think the closest to my problem is listed here: Using melt / cast with variables of uneven length in R

I have tried using cast and melt but not successful.

dt.m1=melt(dt, id=c("CASEID", "VISIT", "AVWEIGHT"))

then...

dt.c1=dcast(dt.m1, CASEID + VISIT ~ variable, value.var="value")

and several variations of these functions, but none have come close to creating the additional columns and organizing the data as needed.

I would appreciate any help.

UseR10085
  • 7,120
  • 3
  • 24
  • 54
attambomb
  • 1
  • 1

1 Answers1

0

Here is a solution using tidyverse:

library(tidyverse)

> data <- data.frame(
+   CASEID =c(1004,1004,1004,1004,1004,1004,1004,1072,1072,1072),
+   VISIT =c(19,20,30,40,10,20,10,20,20,30),
+   AVWEIGHT =c(5 .... [TRUNCATED] 

> spread(data, med.corrected, DLYDOSE)
  CASEID VISIT AVWEIGHT DLYFREQ AMLODIPINE CANDESARTAN LISINOPRIL
1   1004    10     17.3       1       2.50          NA         NA
2   1004    10     24.7       1         NA          NA        2.5
3   1004    19     55.5       2         NA          NA       20.0
4   1004    20     28.1       1         NA          NA        2.5
5   1004    20     53.9       1         NA          NA       10.0
6   1004    30     60.4       1         NA          NA       10.0
7   1004    40     61.3       1         NA          NA       10.0
8   1072    20     18.3       1       1.25           2         NA
9   1072    30     20.9       1         NA           4         NA
>
Retired Data Munger
  • 1,395
  • 8
  • 9
  • Thank you and sorry for the delay. I solved the problem using cast by noting that there were several rows where a single combination of caseid, visit and med.corrected were not unique. That is, some caseid and visit combinations had multiple rows for the same med.corrected. This could be when a patient takes 100mg labetalol in the morning and 150mg at night. Therefore,I solved this by combining these into a single row with a total dose. The cast function worked after that. – attambomb Sep 09 '18 at 02:34