Select Empty Columns

tidyverse
code
R
Author

Ravi Chowdhury

Published

December 12, 2023

This post is about selecting empty columns using R and tidyverse, specifically dplyr. There might be a need where you want to select only empty columns from a table for quality check. For example you expect a column to have data and if the column is empty in the table you can perform checks.

With the janitor package, using the remove_empty function, you can remove empty columns but I am not sure if you can get a list of columns that are empty or have no data.

Read data

billboard
# A tibble: 317 x 79
   artist track date.ent~1   wk1   wk2   wk3   wk4   wk5   wk6   wk7   wk8   wk9
   <chr>  <chr> <date>     <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1 2 Pac  Baby~ 2000-02-26    87    82    72    77    87    94    99    NA    NA
 2 2Ge+h~ The ~ 2000-09-02    91    87    92    NA    NA    NA    NA    NA    NA
 3 3 Doo~ Kryp~ 2000-04-08    81    70    68    67    66    57    54    53    51
 4 3 Doo~ Loser 2000-10-21    76    76    72    69    67    65    55    59    62
 5 504 B~ Wobb~ 2000-04-15    57    34    25    17    17    31    36    49    53
 6 98^0   Give~ 2000-08-19    51    39    34    26    26    19     2     2     3
 7 A*Tee~ Danc~ 2000-07-08    97    97    96    95   100    NA    NA    NA    NA
 8 Aaliy~ I Do~ 2000-01-29    84    62    51    41    38    35    35    38    38
 9 Aaliy~ Try ~ 2000-03-18    59    53    38    28    21    18    16    14    12
10 Adams~ Open~ 2000-08-26    76    76    74    69    68    67    61    58    57
# ... with 307 more rows, 67 more variables: wk10 <dbl>, wk11 <dbl>,
#   wk12 <dbl>, wk13 <dbl>, wk14 <dbl>, wk15 <dbl>, wk16 <dbl>, wk17 <dbl>,
#   wk18 <dbl>, wk19 <dbl>, wk20 <dbl>, wk21 <dbl>, wk22 <dbl>, wk23 <dbl>,
#   wk24 <dbl>, wk25 <dbl>, wk26 <dbl>, wk27 <dbl>, wk28 <dbl>, wk29 <dbl>,
#   wk30 <dbl>, wk31 <dbl>, wk32 <dbl>, wk33 <dbl>, wk34 <dbl>, wk35 <dbl>,
#   wk36 <dbl>, wk37 <dbl>, wk38 <dbl>, wk39 <dbl>, wk40 <dbl>, wk41 <dbl>,
#   wk42 <dbl>, wk43 <dbl>, wk44 <dbl>, wk45 <dbl>, wk46 <dbl>, wk47 <dbl>, ...

We will create an empty column in this table to reference it and find it.

Create empty column

billboard_modified <- billboard |> 
  mutate(
    empty_col = NA_character_ #empty column
  )

Show / select just the blank or empty columns

billboard_modified |> 
  select(where( ~ all(is.na(.))))
# A tibble: 317 x 12
   wk66  wk67  wk68  wk69  wk70  wk71  wk72  wk73  wk74  wk75  wk76  empty_col
   <lgl> <lgl> <lgl> <lgl> <lgl> <lgl> <lgl> <lgl> <lgl> <lgl> <lgl> <chr>    
 1 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
 2 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
 3 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
 4 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
 5 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
 6 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
 7 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
 8 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
 9 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
10 NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA    <NA>     
# ... with 307 more rows

We will use colnames() function to show the list of columns since this table has multiple empty columns.

billboard_modified |> 
  select(where( ~ all(is.na(.)))) |> 
  colnames()
 [1] "wk66"      "wk67"      "wk68"      "wk69"      "wk70"      "wk71"     
 [7] "wk72"      "wk73"      "wk74"      "wk75"      "wk76"      "empty_col"

As you can see it is very easy to find empty columns in a table.