Calculating daily dose using multiple dose entries from same date

Question

I would appreciate anyone's help and advice with this question

The 1st dataframe contains drugs given to patients along with the dates administered. I need to run through this dataframe and calculate the daily dose for each drug per id so that the dataframe outputted resembles 2nd dataframe.

1st dataframe

structure(list(id = c(1010001, 1010001, 1010001, 1010001, 1010001, 
1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 
1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 
1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 
1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 
1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 1010002, 
1010002, 1010002, 1010002, 1010002, 1010002), drug = c("Furosemide", 
"Furosemide", "Furosemide", "Furosemide", "Furosemide", "Magnesium Sulfate", 
"Methylprednisolone", "Methylprednisolone", "Morphine", "Acetaminophen", 
"Acetaminophen", "Acetaminophen", "Acetaminophen", "Cefazolin", 
"Cefazolin", "Furosemide", "Ganciclovir", "Ganciclovir", "Methylprednisolone", 
"Methylprednisolone", "Methylprednisolone", "Methylprednisolone", 
"Methylprednisolone", "Methylprednisolone", "Methylprednisolone", 
"Mycophenolate Mofetil", "Mycophenolate Mofetil", "Nystatin Mouthwash", 
"Nystatin Mouthwash", "Nystatin Mouthwash", "Nystatin Mouthwash", 
"Nystatin Mouthwash", "Nystatin Mouthwash", "Nystatin Mouthwash", 
"Nystatin Mouthwash", "Ranitidine", "Ranitidine", "Ranitidine", 
"Ranitidine", "Furosemide", "Lorazepam", "Magnesium Sulfate", 
"Sodium Phosphate", "Acetaminophen", "Dimenhydrinate", "Dimenhydrinate", 
"Dimenhydrinate", "Dimenhydrinate", "Fentanyl", "Fentanyl", "Hydralazine", 
"Hydralazine", "Hydralazine", "Hydralazine", "Hydralazine", "Hydralazine", 
"Nifedipine", "Nifedipine", "Nifedipine", "Nifedipine", "Nifedipine", 
"Nifedipine", "Nifedipine", "Nifedipine", "Ondansetron", "Ondansetron", 
"Ondansetron", "Ondansetron", "Ondansetron", "Ondansetron", "Ondansetron", 
"CMV Immune Globulin", "CMV Immune Globulin", "Cefazolin", "Cefazolin", 
"Cefazolin", "Cefazolin", "Cefazolin", "Cefazolin", "Cefazolin", 
"Cefazolin", "Cefazolin", "Cefazolin", "Cefazolin", "Cefazolin", 
"Furosemide", "Ganciclovir", "Ganciclovir", "Ganciclovir", "Ganciclovir", 
"Ganciclovir", "Ganciclovir", "Ganciclovir", "Ganciclovir", "Ganciclovir", 
"Ganciclovir", "Methylprednisolone", "Methylprednisolone", "Methylprednisolone", 
"Methylprednisolone", "Methylprednisolone", "Methylprednisolone", 
"Methylprednisolone", "Methylprednisolone", "Methylprednisolone", 
"Methylprednisolone", "Methylprednisolone", "Methylprednisolone", 
"Methylprednisolone", "Methylprednisolone", "Methylprednisolone", 
"Methylprednisolone", "Methylprednisolone", "Methylprednisolone", 
"Mycophenolate Mofetil", "Mycophenolate Mofetil", "Mycophenolate Mofetil", 
"Mycophenolate Mofetil", "Mycophenolate Mofetil", "Mycophenolate Mofetil", 
"Mycophenolate Mofetil", "Mycophenolate Mofetil", "Mycophenolate Mofetil", 
"Nystatin Mouthwash", "Nystatin Mouthwash", "Nystatin Mouthwash", 
"Nystatin Mouthwash", "Nystatin Mouthwash", "Nystatin Mouthwash", 
"Nystatin Mouthwash", "Nystatin Mouthwash", "Nystatin Mouthwash", 
"Nystatin Mouthwash", "Nystatin Mouthwash", "Nystatin Mouthwash", 
"Nystatin Mouthwash", "Nystatin Mouthwash", "Nystatin Mouthwash", 
"Ranitidine", "Ranitidine", "Ranitidine", "Ranitidine", "Ranitidine", 
"Ranitidine", "Ranitidine", "Ranitidine", "Ranitidine", "Ranitidine", 
"Ranitidine", "Ranitidine", "Ranitidine", "Ranitidine", "Tacrolimus", 
"Tacrolimus", "Tacrolimus", "Tacrolimus", "Tacrolimus", "Tacrolimus", 
"Tacrolimus", "Tacrolimus", "Tacrolimus", "Furosemide", "Lorazepam", 
"Midazolam", "Midazolam", "Propofol", "Propofol", "Acetaminophen", 
"Midazolam", "Midazolam", "Midazolam", "Ondansetron", "Propofol", 
"Propofol", "Propofol", "Calcium Carbonate", "Calcium Carbonate", 
"Calcium Carbonate", "Cotrimoxazole", "Cotrimoxazole", "Magnesium Hydroxide", 
"Mycophenolate Mofetil", "Mycophenolate Mofetil", "Mycophenolate Mofetil", 
"Mycophenolate Mofetil", "Omeprazole", "Omeprazole", "Omeprazole", 
"Omeprazole", "Oxybutynin", "Oxybutynin", "Oxybutynin", "Oxybutynin", 
"Prednisone", "Prednisone", "Prednisone", "Tacrolimus", "Tacrolimus", 
"Tacrolimus"), date = structure(c(1145404800, 1145404800, 1145404800, 
1145491200, 1145491200, 1145318400, 1145318400, 1145404800, 1145318400, 
1145404800, 1145404800, 1145491200, 1145491200, 1145404800, 1145491200, 
1145491200, 1145318400, 1145404800, 1145404800, 1145404800, 1145404800, 
1145491200, 1145491200, 1145491200, 1145491200, 1145404800, 1145491200, 
1145318400, 1145404800, 1145404800, 1145404800, 1145404800, 1145491200, 
1145491200, 1145491200, 1145404800, 1145404800, 1145491200, 1145491200, 
1212105600, 1212019200, 1211932800, 1212364800, 1211932800, 1211932800, 
1212019200, 1212105600, 1212278400, 1211932800, 1211932800, 1212105600, 
1212192000, 1212192000, 1212192000, 1212192000, 1212278400, 1212192000, 
1212278400, 1212278400, 1212278400, 1212278400, 1212278400, 1212364800, 
1212364800, 1211932800, 1212019200, 1212105600, 1212105600, 1212192000, 
1212278400, 1212364800, 1212019200, 1212105600, 1211932800, 1212019200, 
1212105600, 1212105600, 1212192000, 1212192000, 1212192000, 1212278400, 
1212278400, 1212278400, 1212364800, 1212364800, 1212105600, 1212019200, 
1212105600, 1212105600, 1212105600, 1212105600, 1212192000, 1212278400, 
1212278400, 1212364800, 1212364800, 1211932800, 1212019200, 1212019200, 
1212019200, 1212105600, 1212105600, 1212105600, 1212105600, 1212192000, 
1212192000, 1212192000, 1212192000, 1212278400, 1212278400, 1212278400, 
1212364800, 1212364800, 1212364800, 1212105600, 1212105600, 1212105600, 
1212192000, 1212192000, 1212192000, 1212278400, 1212278400, 1212364800, 
1212019200, 1212105600, 1212105600, 1212105600, 1212105600, 1212192000, 
1212192000, 1212192000, 1212192000, 1212278400, 1212278400, 1212278400, 
1212278400, 1212364800, 1212364800, 1211932800, 1212019200, 1212019200, 
1212105600, 1212105600, 1212192000, 1212192000, 1212192000, 1212278400, 
1212278400, 1212278400, 1212364800, 1212364800, 1212364800, 1212105600, 
1212105600, 1212105600, 1212105600, 1212192000, 1212192000, 1212278400, 
1212278400, 1212364800, 1212192000, 1223942400, 1224028800, 1224028800, 
1224028800, 1224028800, 1224115200, 1224028800, 1224028800, 1224028800, 
1224115200, 1224028800, 1224028800, 1224028800, 1224028800, 1224028800, 
1224115200, 1224028800, 1224115200, 1224028800, 1224028800, 1224028800, 
1224028800, 1224115200, 1224028800, 1224028800, 1224028800, 1224115200, 
1224028800, 1224028800, 1224028800, 1224115200, 1224028800, 1224028800, 
1224115200, 1224028800, 1224028800, 1224115200), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), dose = c(40, 10, 12, 20, 20, 6, 46, 
46, 60, 500, 500, 500, 500, 1000, 1000, 20, 60, 60, 23, 23, 23, 
23, 23, 23, 23, 500, 500, 1e+05, 1e+05, 1e+05, 1e+05, 1e+05, 
1e+05, 1e+05, 1e+05, 50, 50, 50, 50, 5, 2, 10, 10, 500, 20, 20, 
20, 20, 50, 50, 5, 10, 10, 10, 10, 10, 5, 5, 5, 5, 5, 5, 5, 5, 
4, 4, 4, 4, 4, 4, 4, 7500, 7500, 1000, 1000, 1000, 1000, 1000, 
1000, 1000, 1000, 1000, 1000, 1000, 1000, 10, 56, 56, 112, 112, 
112, 112, 112, 112, 112, 112, 45, 45, 23, 23, 23, 23, 23, 23, 
20, 23, 20, 23, 20, 20, 20, 20, 20, 20, 400, 400, 400, 400, 400, 
400, 400, 400, 400, 1e+05, 1e+05, 1e+05, 1e+05, 1e+05, 1e+05, 
1e+05, 1e+05, 1e+05, 1e+05, 1e+05, 1e+05, 1e+05, 1e+05, 1e+05, 
50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 1, 1, 
4, 4, 1, 1.3, 1.3, 1.3, 1.3, 5, 4, 4, 4, 25, 25, 650, 4, 4, 4, 
4, 25, 25, 25, 200, 200, 200, 80, 80, 130, 750, 750, 750, 750, 
20, 20, 20, 20, 5, 5, 5, 5, 12.5, 12.5, 12.5, 4, 4, 4), units = c("mg", 
"mg", "mg", "mg", "mg", "mmol", "mg", "mg", "mcg", "mg", "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", NA, "mg", 
"mg", "mg", "mg", "mg", "mg", NA, "U", "U", "U", "U", "U", "U", 
"U", "mg", "mg", "mg", "mg", "mg", "mg", "mmol", "mmol", "mg", 
"mg", "mg", "mg", "mg", "mcg", "mcg", "mg", "mg", "mg", "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", NA, "mg", "mg", "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", 
"mg", "mg", "mg", NA, "mg", "mg", "mg", "mg", "mg", "mg", "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", NA, "mg", 
"mg", "mg", "mg", "U", "U", "U", "U", "U", "U", "U", "U", "U", 
"U", "U", "U", "U", "U", "U", "mg", "mg", "mg", "mg", "mg", "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "ng", "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", 
NA, "mg", "mg", NA, "mg", "mg", "mg", "mg", "mg", "mg", NA, "mg", 
"mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg", "mg")), row.names = c(NA, 
-199L), class = c("tbl_df", "tbl", "data.frame"))

2nd dataframe

structure(list(id = c(1010001, 1010001, 1010001, 1010001, 1010001, 
1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 1010001, 
1010001), drug = c("Furosemide", "Furosemide", "Magnesium Sulfate", 
"Methylprednisolone", "Methylprednisolone", "Morphine", "Acetaminophen", 
"Acetaminophen", "Cefazolin", "Cefazolin", "Ganciclovir", "Ganciclovir", 
"Methylprednisolone"), date = structure(c(1145404800, 1145491200, 
1145318400, 1145318400, 1145404800, 1145318400, 1145404800, 1145491200, 
1145404800, 1145491200, 1145318400, 1145404800, 1145404800), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), dailydose = c(62, 60, 6, 46, 46, 60, 
1000, 1000, 1000, 1000, 60, 60, 69), units = c("mg", "mg", "mmol", 
"mg", "mg", "mcg", "mg", "mg", "mg", "mg", "mg", "mg", "mg")), row.names = c(NA, 
-13L), class = c("tbl_df", "tbl", "data.frame"))

If any more information is required please feel free to leave a comment and I will get back to you.

```library(data.table); setDT(dt1)[, dailydose := sum(dose), by=list(id,date,drug,units)][]``` — M--, Sep 04 '19 at 23:01

score 2 · Accepted Answer · answered Sep 04 '19 at 22:55

2

library(dplyr)

df1 %>% 
  group_by(id, drug, date) %>% 
  summarise(dailydose = sum(dose, na.rm = T),
            units = first(units))

answered Sep 04 '19 at 22:55

M--

25,431
8
61
93

score 0 · Answer 2 · answered May 08 '20 at 00:19

I don't think that your second dataframe (desired result) is complete. But this can be done in base with aggregate:

> df2 <- aggregate(dose ~ id + drug + date + units, data=df1, FUN=sum)
> head(df2)

       id               drug       date units dose
1 1010001           Morphine 2006-04-18   mcg   60
2 1010002           Fentanyl 2008-05-28   mcg  100
3 1010001        Ganciclovir 2006-04-18    mg   60
4 1010001 Methylprednisolone 2006-04-18    mg   46
5 1010001      Acetaminophen 2006-04-19    mg 1000
6 1010001          Cefazolin 2006-04-19    mg 1000

Calculating daily dose using multiple dose entries from same date

2 Answers2