基于内容的数据单元合并
基于内容的数据单元合并
提问于 2020-10-20 03:00:45
我继承了一份丑陋的数据，如下所示：
data.bad <- data.frame(
'NameId' = c('350-8D6A','BC2-85E2','426-C0FA','615-8E09','651-8D6F','DE8-3D0F','2B6-D039','5E9-EE00','38F-75E4','B02-FBBC','B7A-821E','95A-B349','A8C-4A7B','3F6-90A1'),
'Tribe' = c('Heliantheae','Heliantheae','Cichorieae','Cichorieae','Cichorieae','Senecioneae','Vernonieae','Tageteae','Vernonieae','Vernonieae','Millerieae','Inuleae','Astereae','Cardueae'),
'NameFull' = c('Wedelia mexicana (Sch.Bip.) McVaugh','Wedelia modesta Baker','Youngia multiflora (Thunb.) DC.','Youngia napifera DC. ex Wight','Scorzonera mucida Rech.f. Aellen & Esfand.','Senecio hualtaranensis Petenatti Ariza & Del Vitto','Baccharoides tolypophora (Mattf.) Isawumi El-Ghazaly & B.Nord.','Bajacalia crassifolia (S.Watson) Loockerman B.L.Turner & R.K.Jansen','Vernonia westermanii Ekman & Dusen','Vernonia westiniana Less.','Oteiza scandens Panero Villaseeor & Medina','Pulicaria hesperia Maire Weiller & Wilczek','Aster chusanensis Y.S.Lim','Cheirolophus mansanetianus Stubing'),
'Status' = c('Accepted','Accepted','Synonym','Synonym','Accepted','Accepted','Synonym','Synonym','Unknown','Unknown','Unknown','Unknown','Hyun','J.B.Peris'),
'AcceptedName' = c('Wedelia mexicana (Sch.Bip.) McVaugh','Wedelia modesta Baker','Youngia japonica (L.) DC.','Youngia japonica (L.) DC.','','','','','NULL','NULL','','','Y.D.Kim & H.C.Shin','Olivares & J.Marten')
)- 列1:阿尔法数字标识符--精细
- 列2:更高级别的分类、单个名称--精细
- 列3:表示物种名称和作者(S)的可变长度字符串--有时会遗漏一些已拆分为col(s) 4(有时甚至5)的文本--它看起来就像在某个点上用逗号留下，但在数据中不再存在。
- 第4列:名称作为单个单词的有效性--应该是“接受”，“同义词”，或者"NULL“，但在某些情况下是空的，在最坏的情况下，文本应该在col 3中。
- 列5:可接受的名称-应该包含有效的二项式和作者(类似于第3列)。第5列的内容应该是:如果第4列=“接受”，则与第3列相同；如果“同义词”，则与"NULL“不同；如果”未知“，则与”NULL“不同，但有时包含第3列的文本片段，有时是空的(不能从这里的其他列推断)。
行1-4,9,10是正确格式化的例子。行13、14混乱，需要将cols 4和5的内容合并为col3(中间有逗号)，新空的cols 4和5填充了“标志”，以向我表明它们需要注意。行5-8,11,12是col 5中缺少的数据，可以从col 4中推断(“接受”= col 3的内容；“同义词”=“标志”(向我表示丢失的数据)；“未知”= "NULL")。
理想的结果是：
data.best <- data.frame(
'NameId' = c('350-8D6A','BC2-85E2','426-C0FA','615-8E09','651-8D6F','DE8-3D0F','2B6-D039','5E9-EE00','38F-75E4','B02-FBBC','B7A-821E','95A-B349','A8C-4A7B','3F6-90A1'),
'Tribe' = c('Heliantheae','Heliantheae','Cichorieae','Cichorieae','Cichorieae','Senecioneae','Vernonieae','Tageteae','Vernonieae','Vernonieae','Millerieae','Inuleae','Astereae','Cardueae'),
'NameFull' = c('Wedelia mexicana (Sch.Bip.) McVaugh','Wedelia modesta Baker','Youngia multiflora (Thunb.) DC.','Youngia napifera DC. ex Wight','Scorzonera mucida Rech.f. Aellen & Esfand.','Senecio hualtaranensis Petenatti Ariza & Del Vitto','Baccharoides tolypophora (Mattf.) Isawumi El-Ghazaly & B.Nord.','Bajacalia crassifolia (S.Watson) Loockerman B.L.Turner & R.K.Jansen','Vernonia westermanii Ekman & Dusen','Vernonia westiniana Less.','Oteiza scandens Panero Villaseeor & Medina','Pulicaria hesperia Maire Weiller & Wilczek','Aster chusanensis Y.S.Lim, Hyun, Y.D.Kim & H.C.Shin','Cheirolophus mansanetianus Stubing, J.B.Peris, Olivares & J.Marten'),
'Status' = c('Accepted','Accepted','Synonym','Synonym','Accepted','Accepted','Synonym','Synonym','Unknown','Unknown','Unknown','Unknown','FLAG','FLAG'),
'AcceptedName' = c('Wedelia mexicana (Sch.Bip.) McVaugh','Wedelia modesta Baker','Youngia japonica (L.) DC.','Youngia japonica (L.) DC.','Scorzonera mucida Rech.f. Aellen & Esfand.','Senecio hualtaranensis Petenatti Ariza & Del Vitto','FLAG','FLAG','NULL','NULL','NULL','NULL','FLAG','FLAG')
)这对我来说有太多的移动部件了。我可以找到的所有示例都合并了两个单独的数据文件，但并不取决于现有dataframe中的元素。
注:一个部分的解决方案，只是合并错误的文本在科尔4和5将是一个非常好的开始(以下)，但显然会喜欢一个整洁的完整解决方案！
data.good <- data.frame(
'NameId' = c('350-8D6A','BC2-85E2','426-C0FA','615-8E09','651-8D6F','DE8-3D0F','2B6-D039','5E9-EE00','38F-75E4','B02-FBBC','B7A-821E','95A-B349','A8C-4A7B','3F6-90A1'),
'Tribe' = c('Heliantheae','Heliantheae','Cichorieae','Cichorieae','Cichorieae','Senecioneae','Vernonieae','Tageteae','Vernonieae','Vernonieae','Millerieae','Inuleae','Astereae','Cardueae'),
'NameFull' = c('Wedelia mexicana (Sch.Bip.) McVaugh','Wedelia modesta Baker','Youngia multiflora (Thunb.) DC.','Youngia napifera DC. ex Wight','Scorzonera mucida Rech.f. Aellen & Esfand.','Senecio hualtaranensis Petenatti Ariza & Del Vitto','Baccharoides tolypophora (Mattf.) Isawumi El-Ghazaly & B.Nord.','Bajacalia crassifolia (S.Watson) Loockerman B.L.Turner & R.K.Jansen','Vernonia westermanii Ekman & Dusen','Vernonia westiniana Less.','Oteiza scandens Panero Villaseeor & Medina','Pulicaria hesperia Maire Weiller & Wilczek','Aster chusanensis Y.S.Lim, Hyun, Y.D.Kim & H.C.Shin','Cheirolophus mansanetianus Stubing, J.B.Peris, Olivares & J.Marten'),
'Status' = c('Accepted','Accepted','Synonym','Synonym','Accepted','Accepted','Synonym','Synonym','Unknown','Unknown','Unknown','Unknown','',''),
'AcceptedName' = c('Wedelia mexicana (Sch.Bip.) McVaugh','Wedelia modesta Baker','Youngia japonica (L.) DC.','Youngia japonica (L.) DC.','','','','','NULL','NULL','','','','')
)希望这是有意义的，很乐意澄清。任何帮助都非常感谢！
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-10-20 19:29:53
正如我所说，这个问题不需要合并。此外，我建议使用data.table包来解决您的问题，一旦根据条件对特定行执行修改变得更简单了。
使用data.table的解决方案是：
# Load data.table
library(data.table)
# Set data.bad as data.table
setDT(data.bad)
# Define Valid Status
valid_status <- c("Accepted", "Synonym", "Unknown")
# Join invalid status to NameFull column
data.bad[!Status %in% valid_status, NameFull := paste(NameFull, Status, sep = ", ")]
# then flag invalid status
data.bad[!Status %in% valid_status, Status := "FLAG"]
# Join AcceptedName to NameFull if Status == FLAG (and Accepted Name is not NULL)
data.bad[Status == "FLAG" & AcceptedName != "NULL", NameFull := paste(NameFull, AcceptedName, sep = ", ")]
# Fix column 5
data.bad[Status == "Accepted", AcceptedName := NameFull]
data.bad[Status == "Synonym" & AcceptedName == "", AcceptedName := "FLAG"]
data.bad[Status == "Unknown", AcceptedName := "NULL"]
data.bad[Status == "FLAG" & !AcceptedName %in% c("FLAG", "NULL"), AcceptedName := "FLAG"]
# Now, data.bad is data.good
data.good <- copy(data.bad)如果要将数据设置为data.frame类，只需键入setDF(data.good)。
使用R基：
# Define Valid Status
valid_status <- c("Accepted", "Synonym", "Unknown")
# Join invalid status to NameFull column
idx <- !data.bad$Status %in% valid_status # set invalid status indexes
data.bad$NameFull[idx] = paste(data.bad$NameFull[idx], data.bad$Status[idx], sep = ", ")
# then flag invalid status
data.bad$Status[idx] <- "FLAG"
# Join AcceptedName to NameFull if Status == FLAG (and Accepted Name is not NULL)
idx <- data.bad$Status == "FLAG" & data.bad$AcceptedName != "NULL" # get indexes with status == "FLAG"
data.bad$NameFull[idx] <- paste(data.bad$NameFull[idx], data.bad$AcceptedName[idx], sep = ", ")
# Fix column 5
data.bad$AcceptedName[data.bad$Status == "Accepted"] <- data.bad$NameFull[data.bad$Status == "Accepted"]
data.bad$AcceptedName[data.bad$Status == "Synonym" & data.bad$AcceptedName == ""] <- "FLAG"
data.bad$AcceptedName[data.bad$Status == "Unknown"] <- "NULL"
data.bad$AcceptedName[data.bad$Status == "FLAG" & !data.bad$AcceptedName %in% c("FLAG", "NULL")] <- "FLAG"页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64437852
复制相关文章
相似问题
腾讯云开发者
