Generate pairs based on measurement and grouped by ID

Question

I have a dataset that includes various measurements of toys, with Piece_ID being a unique toy set and Colour being the colour of a specific toy in a set (let's use Lego pieces as an example).

I need to generate a new column, Pairs, based on Length_mm measurements by unique Piece_ID and Colour groups. Essentially, I want to consider Lego pieces a pair if they are in the same set, are the same colour, and the length of both pieces are within 2 mm of each other. However, if the colour is listed as "unknown", I do not want these to be paired, as this could be pairing two different colours. Pairs can only be of two.

The goal is to have a new column, Pair, where pairs are numbered sequentially in each piece set.

Here is an example of my dataset:

table <- "Piece_ID Width_mm   Length_mm  Colour
1  A     1.68 3.19 Unknown
2  A     1.47 2.88 Blue
3  A     1.64 2.90 Blue
4  A     1.80 3.20 Unknown
5  B     1.76 3.12 Red
6  B     1.61 3.11 Red
7  B     1.57 3.51 Blue
8  B     1.48 3.54 Blue
9  A     1.46 4.05 Green
10 A     1.83 4.03 Green
11 A     1.83 4.11 Green
12 A     1.83 4.51 Green
13 A     1.83 4.12 Green
14 A     1.83 3.55 Blue
15 A     1.83 3.57 Blue
16 A     1.83 3.55 Blue"

#Create a dataframe with the above table
df <- read.table(text=table, header = TRUE)
df

In this case, the output would be:


table2 <- "Piece_ID Width_mm   Length_mm  Colour Pair
1  A     1.68 3.19 Unknown 1
2  A     1.47 2.88 Blue 2
3  A     1.64 2.90 Blue 2
4  A     1.80 3.20 Unknown 3
5  B     1.76 3.12 Red 1
6  B     1.61 3.11 Red 1
7  B     1.57 3.51 Blue 2
8  B     1.48 3.54 Blue 3
9  A     1.46 4.05 Green 4
10 A     1.83 4.03 Green 4
11 A     1.83 4.11 Green 5
12 A     1.83 4.51 Green 6
13 A     1.83 4.12 Green 5
14 A     1.83 3.55 Blue 6
15 A     1.83 3.57 Blue 7
16 A     1.83 3.55 Blue 6"

#Create a dataframe with the above table
pairs <- read.table(text=table2, header = TRUE)
pairs

I thought an ifelse statement may work here, but I'm not sure how to exclude unknowns and how to consecutively number "pairs", where even unmatched pairs are given a number.

Edit: Added more data to show discrepancies.

Nir Graham · Answer

table <- "Piece_ID Width_mm   Length_mm  Colour
1  A     1.68 3.19 Unknown
2  A     1.47 2.88 Blue
3  A     1.64 2.90 Blue
4  A     1.80 3.20 Unknown
5  B     1.76 3.12 Red
6  B     1.61 3.11 Red
7  B     1.57 3.51 Blue
8  B     1.48 3.54 Blue
9  A     1.46 4.05 Green
10 A     1.83 4.03 Green"

#Create a dataframe with the above table
df <- read.table(text=table, header = TRUE)

df$pair <- Inf
df$pair[[1]] <- 1
df$abs_diff <- NA 
seen_vec <- character(0)
for(i in seq_len(nrow(df))){

 if(i!=1) {
   df$abs_diff[[i]] <- round(abs(df$Length_mm[[i]]-df$Length_mm[[i-1]]),2)
   if(! df$Piece_ID[[i]] %in% seen_vec) {
     df$pair[[i]] <- 1
     seen_vec <- c(seen_vec,df$Piece_ID[[i]])
   } else 
   if(
      df$Colour[[i]]!=df$Colour[[i-1]] 
    ){
     df$pair[[i]] <-    df$pair[[i-1 ]]  + 1 
   } else if( df$abs_diff[[i]]>0.02){
     df$pair[[i]] <-    df$pair[[i-1 ]]  + 1 
   } else 
     {
     df$pair[[i]] <-    df$pair[[i-1 ]]
   }

 } else {
   seen_vec <- df$Piece_ID[[i]]
 }
}
df

Generate pairs based on measurement and grouped by ID

1 Answers