selecting values of one dataframe based on partial string in another dataframe

Question

I have two dataframes (DF1 and DF2)

DF1 <- as.data.frame(c("A, B","C","A","C, D"))
names(DF1) <- c("parties")

DF1

 parties
    A, B
       C
       A
    C, D

.

B <- as.data.frame(c(LETTERS[1:10]))
C <- as.data.frame(1:10)
DF2 <- bind_cols(B,C)
names(DF2) <- c("party","party.number")

. DF2

   party party.number
      A            1
      B            2
      C            3
      D            4
      E            5
      F            6
      G            7
      H            8
      I            9
      J           10

The desired result should be an additional column in DF1 which contains the party numbers taken from DF2 for each row in DF1.

Desired result (based on DF1):

  parties party.numbers
    A, B          1, 2
       C             3
       A             1
    C, D          3, 4

I strongly suspect that the answer involves something like str_match(DF1$parties, DF2$party.number) or a similar regular expression, but I can't figure out how to put two (or more) party numbers into the same row (DF2$party.numbers).

akrun · Accepted Answer · 2018-03-21 17:08:19Z

1

One option is gsubfn by matching the pattern as upper-case letter, as replacement use a key/value list

library(gsubfn)
DF1$party.numbers <- gsubfn("[A-Z]", setNames(as.list(DF2$party.number), 
           DF2$party), as.character(DF1$parties))
DF1
#   parties party.numbers
#1    A, B          1, 2
#2       C             3
#3       A             1
#4    C, D          3, 4

edited Mar 21, 2018 at 17:08

answered Mar 21, 2018 at 16:38

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AntoniosK · Accepted Answer · 2018-03-21 16:53:54Z

1

An alternative solution using tidyverse. You can reshape DF1 to have one string per row, then join DF2 and then reshape back to your initial form:

library(tidyverse)

DF1 <- as.data.frame(c("A, B","C","A","C, D"))
names(DF1) <- c("parties")

B <- as.data.frame(c(LETTERS[1:10]))
C <- as.data.frame(1:10)
DF2 <- bind_cols(B,C)
names(DF2) <- c("party","party.number")


DF1 %>%
  group_by(id = row_number()) %>%
  separate_rows(parties) %>%
  left_join(DF2, by=c("parties"="party")) %>%
  summarise(parties = paste(parties, collapse = ", "),
            party.numbers = paste(party.number, collapse = ", ")) %>%
  select(-id)

# # A tibble: 4 x 2
#   parties party.numbers
#   <chr>   <chr>        
# 1 A, B    1, 2         
# 2 C       3            
# 3 A       1            
# 4 C, D    3, 4

edited Mar 21, 2018 at 16:53

answered Mar 21, 2018 at 16:46

AntoniosK

16.1k2 gold badges21 silver badges34 bronze badges

Collectives™ on Stack Overflow

selecting values of one dataframe based on partial string in another dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related