首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >根据多个列删除重复项,但通过最少NA's选择重复项的“最完整”版本

问根据多个列删除重复项,但通过最少NA's选择重复项的“最完整”版本
EN

Stack Overflow用户
提问于 2021-09-03 18:01:52
回答 3查看 31关注 0票数 0

我有一个代码,看起来像这样

代码语言:javascript
复制
  Month|  Day|   Year| Color|   Weather|Location|Transporation|ID
  Jan     Tue    2020   Blue    Warm    Hospital    NA         1
  Jan     Tue    2020   Blue    Warm     NA         NA         1
  Jan     Tue    2020   Blue    NA       NA         NA         1
  Feb     Thu    2020   Red     NA       NA         NA         2
  Feb     Thu    2020   Red     Warm     NA         NA         2
  Feb     Thu    2020   Red     Warm    Garden      Run        2
  Mar     Thu    2020   Red     Cold    Desk        Bus        3

我希望它看起来像这样

代码语言:javascript
复制
Month|   Day|  Year|   Color|  Weather|Location|  Transporation|ID
Jan      Tue   2020    Blue    Warm    Hospital   NA            1
Feb      Thu   2020     Red    Warm    Garden     Run           2
Mar      Thu   2020     Red    Cold    Desk       Bus           3

基本上,我希望通过选择三个c(ID,Month,Color)来确定一个列是否重复。一旦确定了重复项,我希望它删除具有最多NA或“最不完整”的项,如在较少填充的列中。

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2021-09-03 18:28:14

也许这会起作用,我做了rowSums(is.na())来按行列出缺少的项目数量,然后按ID、Month、Color分组,并过滤到缺少数量最少的行:

代码语言:javascript
复制
library(dplyr)
dat<-data.frame("Month" = c("Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Mar"),
                "Day" = c("Tue", "Tue", "Tue", "Thu", "Thu", "Thu", "Thu"),
                "Year" = rep(2020,7),
                "Color" = c(rep("Blue", 3), rep("Red", 4)),
                "Weather" = c("Warm", "Warm", NA, NA, "Warm", "Warm", "Cold"),
                "Location" = c("Hospital", rep(NA, 4), "Garden", "Desk"),
                "Transporation" = c(rep(NA, 5), "Run", "Bus"),
                "ID" = c(1, 1, 1, 2, 2, 2, 3)
)%>%
  mutate(Missing = rowSums(is.na(.)))%>% #Making a sum of how many missing items per row
  group_by(ID, Month, Color)%>%
  filter(Missing == min(Missing))%>% #Filtering to the least amount of missing
  ungroup()%>%
  select(-Missing) #Removing the missing column as it was only used to filter
票数 1
EN

Stack Overflow用户

发布于 2021-09-03 18:50:50

我们可以使用order来选择按感兴趣的列分组后的第一个非NA元素

代码语言:javascript
复制
library(dplyr)
dat %>%
    group_by(Month, Day, Year) %>% 
    summarise(across(everything(), ~ first(.[order(is.na(.))])), .groups = 'drop')

-output

代码语言:javascript
复制
# A tibble: 3 x 8
  Month Day    Year Color Weather Location Transporation    ID
  <chr> <chr> <dbl> <chr> <chr>   <chr>    <chr>         <dbl>
1 Feb   Thu    2020 Red   Warm    Garden   Run               2
2 Jan   Tue    2020 Blue  Warm    Hospital <NA>              1
3 Mar   Thu    2020 Red   Cold    Desk     Bus               3

数据

代码语言:javascript
复制
dat <- structure(list(Month = c("Jan", "Jan", "Jan", "Feb", "Feb", "Feb", 
"Mar"), Day = c("Tue", "Tue", "Tue", "Thu", "Thu", "Thu", "Thu"
), Year = c(2020, 2020, 2020, 2020, 2020, 2020, 2020), Color = c("Blue", 
"Blue", "Blue", "Red", "Red", "Red", "Red"), Weather = c("Warm", 
"Warm", NA, NA, "Warm", "Warm", "Cold"), Location = c("Hospital", 
NA, NA, NA, NA, "Garden", "Desk"), Transporation = c(NA, NA, 
NA, NA, NA, "Run", "Bus"), ID = c(1, 1, 1, 2, 2, 2, 3)), class = "data.frame", row.names = c(NA, 
-7L))
票数 1
EN

Stack Overflow用户

发布于 2021-09-03 18:52:29

使用data.table库,如果您的数据已经在j中:

代码语言:javascript
复制
j <- as.data.table(your_data)
j

    Month    Day  Year  Color Weather Location Transporation    ID
   <char> <char> <int> <char>  <char>   <char>        <char> <int>
1:    Jan    Tue  2020   Blue    Warm Hospital          <NA>     1
2:    Jan    Tue  2020   Blue    Warm     <NA>          <NA>     1
3:    Jan    Tue  2020   Blue    <NA>     <NA>          <NA>     1
4:    Feb    Thu  2020    Red    <NA>     <NA>          <NA>     2
5:    Feb    Thu  2020    Red    Warm     <NA>          <NA>     2
6:    Feb    Thu  2020    Red    Warm   Garden           Run     2
7:    Mar    Thu  2020    Red    Cold     Desk           Bus     3

j$n_na  <- apply(j, MARGIN = 1, function(x) sum(is.na(x)))
setorder(j,n_na)
k <- unique(j,by=c("ID","Month","Color"))
setorder(k,ID)
k

    Month    Day  Year  Color Weather Location Transporation    ID  n_na
   <char> <char> <int> <char>  <char>   <char>        <char> <int> <int>
1:    Jan    Tue  2020   Blue    Warm Hospital          <NA>     1     1
2:    Feb    Thu  2020    Red    Warm   Garden           Run     2     0
3:    Mar    Thu  2020    Red    Cold     Desk           Bus     3     0

毕竟,k将根据您的需要保存数据。向你致敬,米格尔

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/69049003

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档