Help to merge multiple rows from single patient into one

[peter_lambros]

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..

https://www.dropbox.com/s/6d5mfaofqlvcity/Combining%20multiple%20rows%20into%20single%20row%20grouped%20by%20a%20specific%20value.pdf?dl=0

Kind regards
Peter

[ganapap1]

Hi
Hope it helps

library(tibble)

#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]
repeat{
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)){
break
}else{
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
)
break
}
}
}
}

here is the output

[peter_lambros]

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.