Merging multiple rows into one row by several conditions in R

Question

I am trying to merge multiple rows into one row. I want all samples from Group.1 with its corresponding Group.2 and cqs data merged into one row by lot_number.

Important detail: I have three different sample types in the Group.1 column. I think what I'm having such a hard time with is that I need to merge three different sample types into one row by lot_number. A lot of the tools I'm aware of only merge two data tables or frames.

What I have:

Group.1	Group.2	cqs	lot_number
1xLOD_2234567	MS2	39	2234567
NC_2234567	MS2	37	2234567

What I need:

Group.1	Group.2	cqs	lot_number	(new col)	(new col)	(new col)
1xLOD_2234567	MS2	39	2234567	NC_2234567	MS2	37

These are different concentration levels of solutions that I want matched up by lot number. I have tried ideas like:

aggtest2 <- reshape(aggtest2, idvar = "lot_number", timevar = "Group.2", direction = "wide"

and

aggtest0 <- reshape(aggtest2, idvar = c("lot_number","Group.2"), direction = "wide")

and I have also tried the recommendations in the two comments below. I'm just not able to come up with a solution that preserves all of the data and keeps it all in its own cell. Any help is greatly appreciated.

Answer 1

We can use pivot_wider

library(dplyr)
library(tidyr)
library(data.table)
aggtest2 %>%
   mutate(rn = rowid(Group.2, lot_number)) %>% 
   pivot_wider(names_from = rn, values_from = c(Group.1, cqs))
# A tibble: 1 x 6
#  Group.2 lot_number Group.1_1     Group.1_2  cqs_1 cqs_2
#  <chr>        <int> <chr>         <chr>      <int> <int>
#1 MS2        2234567 1xLOD_2234567 NC_2234567    39    37

data

aggtest2 <- structure(list(Group.1 = c("1xLOD_2234567", "NC_2234567"),
Group.2 = c("MS2", 
"MS2"), cqs = c(39L, 37L), lot_number = c(2234567L, 2234567L)),
class = "data.frame", row.names = c(NA, 
-2L))

Answer 2

Hope this is the think you are after

reshape(
  transform(
    df,
    q = ave(1:nrow(df), lot_number, cqs, Group.2, FUN = seq_along)
  ),
  direction = "wide",
  idvar = c("lot_number", "cqs", "Group.2"),
  timevar = "q"
)

which gives

  Group.2 cqs lot_number     Group.1.1  Group.1.2
1     MS2  39    2234567 1xLOD_2234567 NC_2234567