Help to merge multiple rows from single patient into one

Hi support, please view attached document with an explanation and examples in what I am trying to accomplish. Any help/assistance would be greatly appreciated. I am currently using Crystal reports to achieve something similar, but it is not very pretty and has limitations in the resulting output, which is why I have turned to R Studio to hopefully accomplish this task in a more "professional" manner.

I tried to attach PDF document with illustrations but received error message that new users can not upload attachments???? So I have included my DropBox link for this document..

Kind regards

Hope it helps


#building Dataframe
Customer_ID <- c("CG-12520", "CG-12520", "DV-13045", "SO-20335", "SO-20335", "BH-11710", "BH-11710", "BH-11710", "BH-11710", "BH-11710", "BH-11710", "BH-11710", "AA-10480")
Customer_Name <- c("Claire Gute", "Claire Gute", "Darrin Van Huff", "Sean O'Donnell", "Sean O'Donnell", "Brosina Hoffman", "Brosina Hoffman", "Brosina Hoffman", "Brosina Hoffman", "Brosina Hoffman", "Brosina Hoffman", "Brosina Hoffman", "Andrew Allen")
Product_ID <- c("FUR-BO-10001798", "FUR-CH-10000454", "OFF-LA-10000240", "FUR-TA-10000577", "OFF-ST-10000760", "FUR-FU-10001487", "OFF-AR-10002833", "TEC-PH-10002275", "OFF-BI-10003910", "OFF-AP-10002892", "FUR-TA-10001539", "TEC-PH-10002033", "OFF-PA-10002365")
Sales_Amt <- c(261.96, 731.94, 14.62, 957.5775, 22.368, 48.86, 7.28, 907.152, 18.504, 114.9, 1706.184, 911.424, 15.552)

df <- data.frame(Customer_ID,Customer_Name,Product_ID,Sales_Amt)

Creating a temporary Dataframe to store data, with the structure of your output

tempdf <- data.frame(
Customer_ID = as.character(),
Customer_Name= as.character(),
Product_ID = as.character(),
Sales_Amt = as.numeric()

#using While loop and repeat to add records to tempdf
i <- 1
while (i <= nrow(df)) {
mpotxt <- ""
msum <- 0
mcusid <- df$Customer_ID[i]
mcusid <- df$Customer_ID[i]
mpotxt <- paste(mpotxt,df$Product_ID[i],";")
msum <- msum + df$Sales_Amt[i]
i <- i+1
if (i>nrow(df)){
if (df$Customer_ID[i] != mcusid){
tempdf <- tempdf%>%add_row(
Customer_ID = mcusid,
Customer_Name = df$Customer_Name[i-1],
Product_ID = mpotxt,
Sales_Amt = msum

here is the output

Thank you so much for your prompt and detailed response and examples. Worked perfectly!! I had to play around with my date fields and set those to character variable right at the end of the while loop.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.