Finding the exact match in the values in the categorical variables

Question

I wanted to find an exact match in the values between all three columns (rg1,rg2,rg3).Below is my dataframe.

For instance - first row has a combination of (70,71,72) , if this same combination appears in the remaining rows for the rest of the user ids , then, keep only those users and delete rest.

To describe it further - first row has (70,71,72) and say , if row 10 had the same values in B,C,D column, then I just want to display row 1 and row 10.(using R)

I tried doing clustering on this - kmodes. But I'm not getting the expected results.The current code is grouping all the rgs but it's kind of validating only a single Rg that has appeared most frequently in the data frame(above is my dataframe) and ranking them accordingly.

Can someone please guide me on this?Is there any better way to do this?

kmodes <- klaR::kmodes(mapped_df, modes= 5, iter.max = 10, weighted = FALSE)
 #Add these clusters to the main dataframe
final <- mapped_df %>%
  mutate(cluster = kmodes$cluster)

Answer 1

You can sort across the columns, then look for duplicates.

set.seed(1234)

df <- tibble(Userids = 1:20,
             rg_1 = sample(1:20, 20, TRUE),
             rg_2 = sample(1:20, 20, TRUE),
             rg_3 = sample(1:20, 20, TRUE)) 

df[4, -1] <- rev(df[15, -1])

# sort across the columns
df_sorted <- t(apply(df[-1], 1, sort))

# return the duplicated rows
df[duplicated(df_sorted) | duplicated(df_sorted, fromLast = TRUE), ]

This will give you a data frame with all the duplicated values. Once you have the sorted data frame, it should be easy enough to find what you need.

  Userids  rg_1  rg_2  rg_3
    <int> <int> <int> <int>
1       4    16    17     6
2      15     6    17    16

Answer 2

I still do not understand what are you precisely looking for. Besides, it is always recomended to include the data frame you are refering.

I could suggest a solution, which implies the use of a threshold value. So, for each row, if some of the differences (between rg1-rg2, rg1-rg3 and rg2-rg3) is higher than the threshold, it will not be consider.

threshold <- 5

index <- mapped_df %>% 
  
  tibble(g1_g2 = abs(rg1 - rg2),
         g1_g3 = abs(rg1 - rg3),
         g2_g3 = abs(rg2 - rg3)) %>% 
  
  apply(1, function(x, threshold) all(x <= threshold), 
        threshold = threshold)

mapped_df[index]

Answer 3

Maybe you're (just) after some filtering?

library(tidyverse)

data <- tibble(Userids = 1:10,
               rg1 = c(70,1:8,70),
               rg2 = c(71,11:18,71),
               rg3 = c(72,21:28,72))

data |>
    filter(rg1 == 70,
           rg2 == 71,
           rg3 == 72)

data |> 
    filter(rg1 == rg1[row_number()==1],
           rg2 == rg2[row_number()==1],
           rg3 == rg3[row_number()==1])

Output:

# A tibble: 2 × 4
  Userids   rg1   rg2   rg3
    <int> <dbl> <dbl> <dbl>
1       1    70    71    72
2      10    70    71    72

Or combine them for ease:

data |>
    unite(rg, starts_with("rg")) |>
    filter(rg == rg[row_number()==1])

Output:

# A tibble: 2 × 2
  Userids rg      
    <int> <chr>   
1       1 70_71_72
2      10 70_71_72