R - Is there at least one match between comma separated lists?

Question

Here is the data I'm currently working with:

    structure(list(PortfolioID = c("z036527321", "z036527321", "z036527321", 
"z036526431", "z036526421", "z036526421", "z036526301", "z036525571", 
"z036525571", "z036525571", "z036525391", "z036525391", "z036525391", 
"z036525391", "z036525391", "z036525391", "z036525391", "z036525341", 
"z036525341", "z036524141", "z036524061", "z036523701", "z036523701", 
"z036523701", "z036523701", "z036523701", "z036523701", "z036523701", 
"z036523701", "z036523701", "z036522711", "z036521591", "z036521591", 
"z036521541", "z036521541", "z036521431", "z036521431", "z036521431", 
"z036521431", "z036521431", "z036521431", "z036521431", "z036521431", 
"z036521431", "z036521431", "z036520791", "z036520791", "z036520791", 
"z036520791", "z036520671", "z036520671", "z036520671", "z036520671", 
"z036520671", "z036520051", "z036520041", "z036519831", "z036519831", 
"z036519831", "z036519831", "z036519221", "z036519191", "z036518281", 
"z036518281", "z036517381", "z036516621", "z036516571", "z036516571", 
"z036516571", "z036516571", "z036516571", "z036516571", "z036516571", 
"z036516571", "z036516541", "z036516541", "z036516541", "z036515311", 
"z036515311", "z036515311", "z036515311", "z036515301", "z036515301", 
"z036515301", "z036515301", "z036515301", "z036515301", "z036515301", 
"z036515301", "z036515301", "z036515301", "z036515301", "z036515301", 
"z036515301", "z036515301", "z036515301", "z036515301", "z036515301", 
"z036515301", "z036515301"), `Enterprise Customer Party ID` = c("100600263, 100782628, 100753805, 100770731, 101360754, 103126734", 
"100600263, 100782628, 100753805, 100770731, 101360754, 103126734", 
"100600263, 100782628, 100753805, 100770731, 101360754, 103126734", 
"100175650, 100434367, 100600263, 103507556, 101519578, 103134259, 103134510", 
"100022778, 100570834, 100600263, 103514678, 101514778", "100022778, 100570834, 100600263, 103514678, 101514778", 
"100189878, 100600263, 101199515, 100746736", "101065177, 103502728, 101392726, 101404531", 
"101065177, 103502728, 101392726, 101404531", "101491312, 101404531, 103493687", 
"100363017, 100379794, 100600263, 100616003, 101296443, 103133788", 
"100363017, 100379794, 100600263, 100616003, 101296443", "100363017, 100379794, 100600263, 100616003, 101296443, 103130494", 
"100363017, 100379794, 100600263, 100616003, 101296443, 103130494", 
"100363017, 100379794, 100600263, 100616003, 101296443, 103130494", 
"100379794, 100363017, 100600263, 100616003, 101296443", "100379794, 100363017, 100600263, 100616003, 101296443, 103130494", 
"100175650, 100219612, 100434367, 100600263, 103510210, 103124599", 
"100175650, 100219612, 100600263, 103510210, 103124599, 103124680", 
"100189878, 100386081, 100556993, 100951842, 103521916", "100255427, 100599889, 100600263, 100787593, 101318358, 101466804", 
"100541378, 100600263, 100917340, 100991230", "100600263, 100640734, 100917340, 100991230, 103135413, 103489659", 
"100600263, 100640734, 100917340, 100991230, 103489659", "100600263, 100640734, 100917340, 100991230, 103489659", 
"100541378, 100600263, 100917340, 100991230", "100600263, 100640734, 100917340, 100991230, 103135413, 103489659", 
"100541378, 100600263, 100917340, 100991230", "100600263, 100640734, 100917340, 100991230, 103489659", 
"100600263, 100640734, 100917340, 100991230, 103489659", "100600263, 100987563, 101475026, 101513471, 101558213", 
"100180069, 100600263, 100714430, 101380893", "100180069, 100600263, 100693475, 100714430, 101380893", 
"100600263, 100647941, 100714430, 101497006, 101380893, 103663428", 
"100600263, 100693475, 100714430, 101497006, 101380893", "100513394, 100600263, 101250322, 100888261, 101484512", 
"100513394, 100600263, 101250322, 101488513", "100513394, 100600263, 100680615, 101250322", 
"100513394, 100600263, 100748241, 101250322", "100259186, 100513394, 100600263, 100647941, 101250322", 
"100325289, 100513394, 100600263, 101250322", "100513394, 100600263, 101250322, 101270920", 
"100358051, 100513394, 100600263, 101250322", "100513394, 100600263, 100845917, 101250322", 
"100513394, 100566608, 100600263, 101250322", "100154267, 100219612, 100600263, 101213228, 103498394, 103147666", 
"100154267, 100219612, 100600263, 101213228, 103498394, 103147666", 
"100154267, 100219612, 100600263, 101213228, 103498394, 103147666", 
"100154267, 100219612, 100600263, 101213228, 103498394, 103147666", 
"100386081, 100600263, 100746736, 100856914", "100386081, 100600263, 100746736, 100856914", 
"100386081, 100600263, 100746736, 100856914", "100386081, 100600263, 100746736, 100856914", 
"100386081, 100600263, 100746736, 100856914", "100482176, 100600263, 100615160, 100784263, 101374469", 
"100600263, 101360754, 103124701, 101436015", "100600263, 100655349, 100684621, 100823164, 101254987, 100770731, 100896421", 
"100600263, 100655349, 100684621, 100823164, 101254987, 100896421", 
"100600263, 100655349, 100684621, 100823164, 101254987, 100770731, 100896421", 
"100600263, 100684621, 100655349, 100823164, 101254987, 100770731, 100896421", 
"100513394, 100569469, 100600263, 100693475, 101344508", "100513394, 100600263, 101250322, 103124606, 103143455", 
"100178006, 103513498, 101404531", "100178006, 103513498, 101404531", 
"100532433, 100600263, 100685774, 100787593, 101344508, 103524479", 
"100110952, 100600263, 100879526, 103147348, 103147682, 103147890", 
"100086400, 100600263, 100991230, 103524505, 103125230, 103135888", 
"100086400, 100600263, 100991230, 103524505, 103125230, 103135888", 
"100086400, 100600263, 100991230, 103524505, 103125230, 103135888", 
"100086400, 100600263, 100991230, 103524505, 103125230, 103135888", 
"100086400, 100600263, 100991230, 103524505, 103125230, 103135888", 
"100086400, 100600263, 100991230, 103524505, 103125230, 103135888", 
"100086400, 100600263, 100991230, 103524505, 103125230, 103135888", 
"100086400, 100600263, 100991230, 103524505, 103125230, 103135888", 
"100566667, 100600263, 100603186, 103130050", "100566667, 100600263, 100603186, 103531703, 103128007", 
"100566667, 100600263, 100603186, 103531703, 103128007", "100505868, 100600263, 100879526, 101436015, 103139491, 103482495", 
"100505868, 100600263, 100879526, 103139491, 101436015, 103482495", 
"100505868, 100600263, 100879526, 101436015, 103139491, 103482495", 
"100505868, 100600263, 100879526, 101436015, 103139491, 103482495", 
"100386081, 100600263, 100746736, 103145053", "100386081, 100600263, 101050657, 100746736", 
"100386081, 100600263, 101050657, 100746736", "100386081, 100600263, 100746736, 101050657", 
"100386081, 100600263, 100746736, 103145053", "100386081, 100600263, 100746736, 101381649", 
"100386081, 100600263, 101050657, 100746736", "100386081, 100600263, 100746736, 103145053", 
"100386081, 100600263, 101050657, 100746736", "100386081, 100600263, 100746736, 103145053", 
"100019889, 100386081, 100587086, 100600263, 100746736, 103516226, 103629951, 101540702, 103143165", 
"100386081, 100600263, 100746736, 101381649", "100386081, 100600263, 100746736, 101381649", 
"100386081, 100587086, 100600263, 100746736, 101540702, 103147108", 
"100386081, 100600263, 100746736, 101381649", "100019889, 100386081, 100587086, 100600263, 100746736, 103516226, 103629951, 101540702, 103143165", 
"100386081, 100600263, 100746736, 101381649", "100019889, 100386081, 100587086, 100600263, 100746736, 103516226, 101540702, 103629951, 103143165", 
"100189878, 100386081, 100951842, 101381649")), row.names = c(NA, 
-100L), class = c("tbl_df", "tbl", "data.frame"))

I am needing to know if there is a way to find out if there is at least one matching value in column B amongst all of the same portfolioID's. For example, for PortfolioID z036527321 we can see that there definitely is one match among the lists (100600263, and plenty of others). There will be some PortfolioID's where there isn't one match between them all. All I'm needing is an indicator of some sort. Put a 1 if all portfolios have one matching value and 0 if not. I really appreciate the help ahead of time. Thank you!

user_3732271 · Answer

After grouping by 'PortfolioID', split the Enterprise Customer Party ID column, get the intersect of the list elements with reduce, check its length and convert to logical with is_greater_than

library(dplyr)
library(purrr)
library(magrittr)
df1 %>% 
    group_by(PortfolioID) %>% 
    mutate(new = reduce(strsplit(`Enterprise Customer Party ID`, 
           ",\s+"), intersect) %>%
        length %>%
        is_greater_than(0)) %>%
    ungroup

-output

# A tibble: 100 x 3
   PortfolioID `Enterprise Customer Party ID`                                              new  
                                                                                 
 1 z036527321  100600263, 100782628, 100753805, 100770731, 101360754, 103126734            TRUE 
 2 z036527321  100600263, 100782628, 100753805, 100770731, 101360754, 103126734            TRUE 
 3 z036527321  100600263, 100782628, 100753805, 100770731, 101360754, 103126734            TRUE 
 4 z036526431  100175650, 100434367, 100600263, 103507556, 101519578, 103134259, 103134510 TRUE 
 5 z036526421  100022778, 100570834, 100600263, 103514678, 101514778                       TRUE 
 6 z036526421  100022778, 100570834, 100600263, 103514678, 101514778                       TRUE 
 7 z036526301  100189878, 100600263, 101199515, 100746736                                  TRUE 
 8 z036525571  101065177, 103502728, 101392726, 101404531                                  TRUE 
 9 z036525571  101065177, 103502728, 101392726, 101404531                                  TRUE 
10 z036525571  101491312, 101404531, 103493687                                             TRUE 
# … with 90 more rows

user_2498085 · Answer

I copied the data you provided into a data frame called foo.

In this solution a new column is created (called 'any_match') that shows TRUE if there are duplicate Enterprise Customer Party IDs for the same PortfolioID. It returns a FALSE if there are no duplicates.

The only instances of FALSE that are showing up in this solution is when there is only 1 row for the PortfolioID. So obviously there will be no duplicates because only 1 instance of the PortfolioID exists.

library(tidyverse)

foo %>%
  split(foo$PortfolioID) %>%
  map_dfr(function(pID) {
    
    pID_ecpid <- str_split(pID$`Enterprise Customer Party ID`, ', ') 
    
    match_check <- rep(NA, length(pID_ecpid))
    
    for (i in 1:length(pID_ecpid)) {
      check_with <- pID_ecpid[[i]]
      check_against <- unlist(pID_ecpid[-i])
      match_check[i] <- any(check_with %in% check_against)
    }
    
    pID <- pID %>%
      mutate(any_match = all(match_check))
    
    return(pID)
    
  })

user_12505251 · Answer

You could use

library(stringr)
library(dplyr)
library(tidyr)

data %>% 
  add_count(PortfolioID) %>%
  mutate(`Enterprise Customer Party ID` = str_split(`Enterprise Customer Party ID`, ", ")) %>% 
  unnest(`Enterprise Customer Party ID`) %>% 
  group_by(PortfolioID) %>%
  add_count(`Enterprise Customer Party ID`) %>% 
  summarise(match = any(n == nn)) %>% 
  right_join(data, by = "PortfolioID")

which returns

# A tibble: 100 x 3
   PortfolioID match `Enterprise Customer Party ID`            
                                                
 1 z036515301  TRUE  100386081, 100600263, 100746736, 103145053
 2 z036515301  TRUE  100386081, 100600263, 101050657, 100746736
 3 z036515301  TRUE  100386081, 100600263, 101050657, 100746736
 4 z036515301  TRUE  100386081, 100600263, 100746736, 101050657
 5 z036515301  TRUE  100386081, 100600263, 100746736, 103145053
 6 z036515301  TRUE  100386081, 100600263, 100746736, 101381649
 7 z036515301  TRUE  100386081, 100600263, 101050657, 100746736
 8 z036515301  TRUE  100386081, 100600263, 100746736, 103145053
 9 z036515301  TRUE  100386081, 100600263, 101050657, 100746736
10 z036515301  TRUE  100386081, 100600263, 100746736, 103145053
# ... with 90 more rows

user_9203158 · Answer

Base R

# Function to split the strings into a vector:
# strsplit_to_vec => function() 
strsplit_to_vec <- function(vec, pattern){
  return(unlist(strsplit(vec, pattern)))
}

# Apply the function on each element of the 
# multi-valued attribute Enterprise Customer Party ID
# grouped by PortfolioId: 
# split_str_list => list of character vectors
split_str_list <- lapply(
  split(
    df$`Enterprise Customer Party ID`,
    df$PortfolioID
  ), 
  strsplit_to_vec, 
  ",\s+"
)

# Determine if any other portfolio contains thos ecpids:
# matching_ecpid => numeric vector
matching_ecpid <- setNames(
  vapply(
    seq_along(split_str_list),
    function(i){
      tmp_vec <- do.call(c, split_str_list[-i])
      if(length(intersect(split_str_list[[i]], tmp_vec)) > 0){
        1
      }else{
        0
      }
    }, 
    numeric(1)
  ),
  names(split_str_list)
)

# Assign the result back to the data.frame: 
# ecpid_match => numeric vector
df$ecpid_match <- matching_ecpid[
  match(
    df$PortfolioID,
    names(matching_ecpid)
  )
]

R - Is there at least one match between comma separated lists?

4 Answers