如何在R中将一个字符串拆分成同一数据库中的多行？

Question

我有一个问题。这是我的初始示例数据库：

nam <- c("Marco", "Clara")
code <- c("The liquidations code for Marco are: 51-BMR05, 74-VAD08, 176-VNF09.",
          "The liquidations code for Clara are: 88-BMR05, 90-VAD08, 152-VNF09.")

df <- data.frame(name,code)

这看起来像这样：

这样

所以我希望冒号后面的代码能够被分离并成为一个具有相同名称的记录。也就是说，数据库是这样转换和完成的:

我需要知道在R中是否有任何方法可以帮助我简化和加速这项工作。我已经在Excel中做了一些例子。提前感谢大家的帮助。

- Josué Aguilar

Answer 1

我们可以使用str_extract_all从list中提取所有的代码，然后使用unnest使其展开。

library(dplyr)
library(stringr)
library(tidyr)
df %>% 
  mutate(code = str_extract_all(code, "\\d+-[A-Z0-9]+")) %>% 
  unnest(code)

-输出

# A tibble: 6 × 2
  name  code     
  <chr> <chr>    
1 Marco 51-BMR05 
2 Marco 74-VAD08 
3 Marco 176-VNF09
4 Clara 88-BMR05 
5 Clara 90-VAD08 
6 Clara 152-VNF09

- akrun

---

哇，谢谢兄弟！我很感激！ - Josué Aguilar

Answer 2

这里有一个简洁的解决方案：

library(tidyverse)

df %>% 
  # Remove text and trailing dot
  mutate(
    code = stringr::str_remove(
      string = code, 
      pattern = "The liquidations code for .* are: "
    ),
    code = stringr::str_remove(
      string = code, 
      pattern = "\\.$"
    )
  ) %>%
  # Split the codes (results in list column)
  mutate(code = stringr::str_split(code, ", ")) %>% 
  # Turn list column into new rows
  unnest(code)


#> # A tibble: 6 × 2
#>   name  code     
#>   <chr> <chr>    
#> 1 Marco 51-BMR05 
#> 2 Marco 74-VAD08 
#> 3 Marco 176-VNF09
#> 4 Clara 88-BMR05 
#> 5 Clara 90-VAD08 
#> 6 Clara 152-VNF09

^{2022年03月28日由reprex包（v2.0.1）创建}

---

数据

与原帖相同的代码，但将nam更正为name：

name <- c("Marco", "Clara")
code <- c("The liquidations code for Marco are: 51-BMR05, 74-VAD08, 176-VNF09.",
          "The liquidations code for Clara are: 88-BMR05, 90-VAD08, 152-VNF09.")
df <- data.frame(name,code)

- jpiversen

Answer 3

我用以下代码实现了输出：

我用以下代码实现了输出：

df1 <- separate_rows(df, code, sep=', ', convert = TRUE)
df1$liquidation_code <- gsub('The liquidations code for Marco are: |The liquidations code for Clara are: |\\.','',df1$code)
df1 <- df1[ , !(colnames(df1) %in% c('code'))]

df1

- stefan_aus_hannover

Answer 4

使用来自 stringr 的正则表达式

#build your df
name <- c("Marco", "Clara")
code <- c("The liquidations code for Marco are: 51-BMR05, 74-VAD08, 176-VNF09.",
          "The liquidations code for Clara are: 88-BMR05, 90-VAD08, 152-VNF09.")
df <- data.frame(name,code)

# Processing
codes = stringr::str_extract_all(df$code, "([:alnum:]+-[:alnum:]+)")
names(codes) <- c(df$name)
liquidation_code = unlist(codes)
fix_names = substr(names(liquidation_code),1,nchar(names(liquidation_code))-1)
fix_df = data.frame(names = fix_names,liquidation_code) 


       names liquidation_code
Marco1 Marco         51-BMR05
Marco2 Marco         74-VAD08
Marco3 Marco        176-VNF09
Clara1 Clara         88-BMR05
Clara2 Clara         90-VAD08
Clara3 Clara        152-VNF09

- VYago

Answer 5

类似这样的东西应该会有所帮助。

library(tidyverse)
#> Warning: package 'tidyr' was built under R version 4.1.3
#> Warning: package 'readr' was built under R version 4.1.3
#> Warning: package 'dplyr' was built under R version 4.1.3

name <- c("Marco", "Clara")
code <- c("The liquidations code for Marco are: 51-BMR05, 74-VAD08, 176-VNF09.",
          "The liquidations code for Clara are: 88-BMR05, 90-VAD08, 152-VNF09.")

df_example <- data.frame(name,code)

df_example |> 
  mutate(codes = str_extract(code,pattern = "(?<=:).+") |> 
           str_split(',')) |> 
  unnest(codes) |> 
  mutate(codes = codes |> str_squish() |> str_remove('\\.'))
#> # A tibble: 6 x 3
#>   name  code                                                               codes
#>   <chr> <chr>                                                              <chr>
#> 1 Marco The liquidations code for Marco are: 51-BMR05, 74-VAD08, 176-VNF0~ 51-B~
#> 2 Marco The liquidations code for Marco are: 51-BMR05, 74-VAD08, 176-VNF0~ 74-V~
#> 3 Marco The liquidations code for Marco are: 51-BMR05, 74-VAD08, 176-VNF0~ 176-~
#> 4 Clara The liquidations code for Clara are: 88-BMR05, 90-VAD08, 152-VNF0~ 88-B~
#> 5 Clara The liquidations code for Clara are: 88-BMR05, 90-VAD08, 152-VNF0~ 90-V~
#> 6 Clara The liquidations code for Clara are: 88-BMR05, 90-VAD08, 152-VNF0~ 152-~

^{本文由 reprex package (v2.0.1) 创建于2022年3月28日。}

- Bruno

---

什么遗漏了？ - Bruno

你是指代码列吗？最好将其存储以备后用，如果他不需要它，只需用select删除即可。这不是一个编写代码的网站，我提供了解决他问题的方法，不需要完全匹配他的要求。 - Bruno