我有一个代码,看起来像这样
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我希望它看起来像这样
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或“最不完整”的项,如在较少填充的列中。
发布于 2021-09-03 18:28:14
也许这会起作用,我做了rowSums(is.na())来按行列出缺少的项目数量,然后按ID、Month、Color分组,并过滤到缺少数量最少的行:
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发布于 2021-09-03 18:50:50
我们可以使用order来选择按感兴趣的列分组后的第一个非NA元素
library(dplyr)
dat %>%
group_by(Month, Day, Year) %>%
summarise(across(everything(), ~ first(.[order(is.na(.))])), .groups = 'drop')-output
# 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数据
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))发布于 2021-09-03 18:52:29
使用data.table库,如果您的数据已经在j中:
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将根据您的需要保存数据。向你致敬,米格尔
https://stackoverflow.com/questions/69049003
复制相似问题