将列拆分为它所包含的数字，后面是作为列名的单词。

Question

我很抱歉这个问题的标题很难理解，我将尝试用一些示例数据来解释我的意思。数据框架中有一个文本列，其行如下所示：

2,413 European ancestry cases, 2,392 European ancestry controls, 810 African American
2,731 European ancestry cases, 10,747 European ancestry controls
8,918 European ancestry individuals, 3,947 Indian Asian ancestry individuals
175 Han Chinese ancestry cases, 175 Han Chinese ancestry controls

最好，我想把这一列变成多个数字列，列名是每个数字后面的单词。因此，以上四行的结果如下：

European ancestry cases, European ancestry controls, African American, European ancestry individuals, Indian Asian ancestry individuals, Han Chinese ancestry cases, Han Chinese ancestry controls
2413, 2392, 810 , NA, NA, NA, NA
2731, 10747, NA, NA, NA, NA, NA
NA, NA, NA, 8918, 3947, NA, NA
NA, NA, NA, NA, NA, 175, 175

对于如何在R中实现这一点，有什么想法吗？

Answer 1

这里有一种方法。我首先使用tstrplit在,上拆分，然后使用melt long，从前导数字中删除逗号，将前导数字和文本拆分为单独的列(称为num和value)，并将dcast返回到wide格式。

library(stringr)
library(data.table)

df = setDT(df)
dcast(
  melt(df[,tstrsplit(s,", ")][, i:=.I], id="i")[
    , (c("value","num")):={value=gsub(',','',value);num=str_extract(value,"^\\d+");value=gsub("^\\d+ ","",value);list(value,num)}][
      !is.na(num)],
  i~value, value.var="num"
)

输出：

   i African American European ancestry cases European ancestry controls European ancestry individuals Han Chinese ancestry cases
1: 1              810                    2413                       2392                          <NA>                       <NA>
2: 2             <NA>                    2731                      10747                          <NA>                       <NA>
3: 3             <NA>                    <NA>                       <NA>                          8918                       <NA>
4: 4             <NA>                    <NA>                       <NA>                          <NA>                        175
   Han Chinese ancestry controls Indian Asian ancestry individuals
1:                          <NA>                              <NA>
2:                          <NA>                              <NA>
3:                          <NA>                              3947
4:                           175                              <NA>

输入：

structure(list(s = c("2,413 European ancestry cases, 2,392 European ancestry controls, 810 African American", 
"2,731 European ancestry cases, 10,747 European ancestry controls", 
"8,918 European ancestry individuals, 3,947 Indian Asian ancestry individuals", 
"175 Han Chinese ancestry cases, 175 Han Chinese ancestry controls"
)), class = "data.frame", row.names = c(NA, -4L))

Answer 2

1. str_remove_all()删除数字中的逗号，以避免与comma-delimiters.
2. separate_rows()混淆，将折叠的名称和值分隔为多个rows.
3. extract()，将名称和值分隔为各自的列。

library(tidyverse)

df %>%
  mutate(id = 1:n(), txt = str_remove_all(txt, '(?<=\\d),(?=\\d)')) %>%
  separate_rows(txt, sep = ',') %>%
  extract(txt, c('val', 'col'), regex = "(\\d+)\\s+(.+)", convert = TRUE) %>%
  pivot_wider(names_from = col, values_from = val)

# # A tibble: 4 × 8
#      id `European ancestry cases` `European ance…` `African Ameri…` `European ance…` `Indian Asian …` `Han Chinese a…` `Han Chinese a…`
#   <int>                     <int>            <int>            <int>            <int>            <int>            <int>            <int>
# 1     1                      2413             2392              810               NA               NA               NA               NA
# 2     2                      2731            10747               NA               NA               NA               NA               NA
# 3     3                        NA               NA               NA             8918             3947               NA               NA
# 4     4                        NA               NA               NA               NA               NA              175              175

数据

df <- data.frame(
  txt = c("2,413 European ancestry cases, 2,392 European ancestry controls, 810 African American",
          "2,731 European ancestry cases, 10,747 European ancestry controls",
          "8,918 European ancestry individuals, 3,947 Indian Asian ancestry individuals",
          "175 Han Chinese ancestry cases, 175 Han Chinese ancestry controls")
)