如何合并2个数据框,其中一个数据框以逗号分隔值?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (29)

我有两个像这样的数据

df1 <- data.frame(Colors = c("Yellow","Pink","Green","Blue","White","Red"
                            ,"Cyan","Brown","Violet","Orange","Gray"))

df2 <- data.frame(Colors = c("Yellow,Pink","Green","Gold","White","Red,Cyan,Brown",
                             "Violet","Magenta","Gray"))

我的期望输出

          Colors
     Yellow,Pink
           Green
           White
  Red,Cyan,Brown
          Violet
            Gray

如果我做了df <- inner_join(df2,df1),那我就不懂行了Yellow,Pink&Red,Cyan,Brown

我在这里错过了什么?有人能给我指明正确的方向吗?

提问于
用户回答回答于

在每个拆分项上R使用的基本解决方案pmatch

split_list <- strsplit(as.character(df2$Colors),",")
keep_lgl   <- sapply(split_list,function(x) !anyNA(pmatch(x,df1$Colors)))
df2[keep_lgl,,drop=FALSE]

#           Colors
# 1    Yellow,Pink
# 2          Green
# 4          White
# 5 Red,Cyan,Brown
# 6         Violet
# 8           Gray

注意:只有在所有颜色都可用时,我才匹配一系列颜色df1

一些tidyverse方法:

library(tidyverse)
df2 %>% mutate(keep=Colors) %>%
  separate_rows(Colors) %>%
  add_count(keep) %>%
  inner_join(df1) %>%
  add_count(keep) %>% # doesn't do anything here but important in general
  filter(n==nn)   %>% # same
  distinct(keep)  %>%
  rename(Colors=keep)

# # A tibble: 6 x 1
# Colors
# <fctr>
# 1    Yellow,Pink
# 2          Green
# 3          White
# 4 Red,Cyan,Brown
# 5         Violet
# 6           Gray

df2 %>% mutate(keep=Colors) %>%
  separate_rows(Colors) %>%
  left_join(df1 %>% mutate(Colors2=Colors,.)) %>%
  group_by(keep) %>%
  summarize(filt=anyNA(Colors2)) %>%
  filter(!filt) %>%
  select(-2)

# # A tibble: 6 x 1
#             keep
#           <fctr>
# 1           Gray
# 2          Green
# 3 Red,Cyan,Brown
# 4         Violet
# 5          White
# 6    Yellow,Pink
用户回答回答于

可以使用regex_inner_joinfuzzyjoin包加入df1df2。最后,从df2列中选择唯一的行。

library(dplyr)
library(fuzzyjoin)

regex_inner_join(df2, df1, by=c(Colors = "Colors")) %>%
  select(Colors = Colors.x) %>% distinct()

#           Colors
# 1    Yellow,Pink
# 2          Green
# 3          White
# 4 Red,Cyan,Brown
# 5         Violet
# 6           Gray

# Just to demonstrate, result of joined tables using regex_inner_join. One,
# can work-out to convert data in desired format afterwards.

regex_inner_join(df2, df1, by=c(Colors = "Colors")) 
#         Colors.x Colors.y
# 1    Yellow,Pink   Yellow
# 2    Yellow,Pink     Pink
# 3          Green    Green
# 4          White    White
# 5 Red,Cyan,Brown      Red
# 6 Red,Cyan,Brown     Cyan
# 7 Red,Cyan,Brown    Brown
# 8         Violet   Violet
# 9           Gray     Gray

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励