Arranging summarized NAs in descending order after calculating them in each column in tidyverse

user_7484093 0
Viewed 166

I have a dataframe with 100+ columns. Many of them contain NAs. I want not only to calculate NAs in each column but also to arrange them in descending order.

Numbers in the second column are the numbers of NAs in each column.

Rows: 1
Columns: 5
$ EXT_SOURCE_1                 <int> 173378
$ EXT_SOURCE_2                 <int> 660
$ EXT_SOURCE_3                 <int> 60965
$ APARTMENTS_AVG               <int> 156061
$ BASEMENTAREA_AVG             <int> 179943

Using df %>% summarise_all(funs(sum(is.na(.)))) %>% glimpse() does a perfect job, calculating NAs in each column and putting everything in two columns, but I cannot use arrange(desc). Hence, it is tough to compare multiple columns.

Expected output:

Rows: 1
Columns: 5
$ BASEMENTAREA_AVG             <int> 179943
$ EXT_SOURCE_1                 <int> 173378
$ APARTMENTS_AVG               <int> 156061
$ EXT_SOURCE_3                 <int> 60965
$ EXT_SOURCE_2                 <int> 660

I tried to used group_by() and summarise, but it works for values, not NAs.

dplyr tidyverse na r
2 Answers

I think the issue is caused by the summarise function returning a single column ("col_name : value") instead of two columns ("col_name" and "variable"). One potential solution is to use the pivot_longer() tidyverse function to split the output into two columns, e.g.

library(tidyverse)

data(airquality)
dat1 <- airquality
dat1 %>%
  summarise(across(everything(), ~ sum(is.na(.)))) %>% 
  pivot_longer(cols = everything(), names_to = "names", values_to = "values") %>% 
  arrange(desc(values))
#> # A tibble: 6 x 2
#>   names   values
#>   <chr>    <int>
#> 1 Ozone       37
#> 2 Solar.R      7
#> 3 Wind         0
#> 4 Temp         0
#> 5 Month        0
#> 6 Day          0

Created on 2021-07-21 by the reprex package (v2.0.0)

user_129573400

In base R, you can use colSums and is.na to count number of NA values in each column, sort the result in decreasing order to get a named vector.

res <- sort(colSums(is.na(airquality)), decreasing = TRUE)
res

#  Ozone Solar.R    Wind    Temp   Month     Day 
#     37       7       0       0       0       0

If you need a dataframe stack it.

stack(res)

#  values     ind
#1     37   Ozone
#2      7 Solar.R
#3      0    Wind
#4      0    Temp
#5      0   Month
#6      0     Day
user_39629140
Related