我的论文数据遇到了以下问题。我有一个数据框,在第一列"id"之后有水平无组织的字符串单元格。我想在行内组织字符串,以便所有以相同的前 4 个字符开头的字符串将保留在同一列中。
由于相关类别的数量有限(少于 20 个(,我可以手动执行此操作,首先针对"Arra",然后针对"Comm"等。我用grepl
尝试了这个,但未能返回原始的单元格字符串。我只得到真/假。我将非常感谢您的帮助!
我当前的数据如下所示。(我将NA单元格留空(
id col2 col3 col4 col5
3 Commitment 100 Lead Mgmt 15 Arranger 50
8 Arrangement 20 Front-end 80
16 Lead mgmt 40 Commitmnt 20
17
20 Arranger 50
这是它应该的样子:
id Arra Comm Fron Lead
3 Arranger 50 Commitment 100 Lead Mgmt 15
8 Arrangement 20 Front-end 80
16 Commitmnt 20 Lead mgmt 40
17
20 Arranger 50
这是一种可能的方法:
library(data.table)
dcast(melt(as.data.table(mydf), "id", na.rm = TRUE)[value != ""][
, ind := substr(value, 1, 4)], id ~ ind, value.var = "value", fill = "")
# id Arra Comm Fron Lead
# 1: 3 Arranger 50 Commitment 100 Lead Mgmt 15
# 2: 8 Arrangement 20 Front-end 80
# 3: 16 Commitmnt 20 Lead mgmt 40
# 4: 20 Arranger 50
并且,以类似的逻辑,在"整洁"中:
library(tidyverse)
mydf[is.na(mydf)] <- ""
mydf %>%
gather(var, val, starts_with("col")) %>%
filter(val != "") %>%
mutate(ind = substr(val, 1, 4)) %>%
select(-var) %>%
spread(ind, val)
# id Arra Comm Fron Lead
# 1 3 Arranger 50 Commitment 100 <NA> Lead Mgmt 15
# 2 8 Arrangement 20 <NA> Front-end 80 <NA>
# 3 16 <NA> Commitmnt 20 <NA> Lead mgmt 40
# 4 20 Arranger 50 <NA> <NA> <NA>
示例数据:
mydf <- structure(list(id = c(3L, 8L, 16L, 17L, 20L), col2 = c("Commitment 100",
"Arrangement 20", "Lead mgmt 40", "", "Arranger 50"), col3 = c("Lead Mgmt 15",
"Front-end 80", "Commitmnt 20", "", ""), col4 = c("Arranger 50",
"", "", "", ""), col5 = c(NA, NA, NA, NA, NA)), .Names = c("id",
"col2", "col3", "col4", "col5"), row.names = c(NA, 5L), class = "data.frame")
如果原始数据中有重复的存根,例如,第 1 行中的"col5"具有另一个"合约"值:
mydf$col5[1] <- "Commitment 99"
你可以试试这样的事情:
dcast(melt(as.data.table(mydf), "id", na.rm = TRUE)[value != ""][
, ind := substr(value, 1, 4)],
id ~ ind + rowid(id, ind), value.var = "value", fill = "")
# id Arra_1 Comm_1 Comm_2 Fron_1 Lead_1
# 1: 3 Arranger 50 Commitment 100 Commitment 99 Lead Mgmt 15
# 2: 8 Arrangement 20 Front-end 80
# 3: 16 Commitmnt 20 Lead mgmt 40
# 4: 20 Arranger 50
或者这个:
dcast(melt(as.data.table(mydf), "id", na.rm = TRUE)[value != ""][
, ind := substr(value, 1, 4)],
id ~ ind, value.var = "value", fun = function(x) x[1], fill = "")
# id Arra Comm Fron Lead
# 1: 3 Arranger 50 Commitment 100 Lead Mgmt 15
# 2: 8 Arrangement 20 Front-end 80
# 3: 16 Commitmnt 20 Lead mgmt 40
# 4: 20 Arranger 50
取决于您想要的输出。