Adding column if it does not exist

Question

I have a bunch of data frames with different variables. I want to read them into R and add columns to those that are short of a few variables so that they all have a common set of standard variables, even if some are unobserved.

In other words... Is there a way to add columns of NA in the tidyverse when a column does not exist? My current attempt works for adding new variables where the column doesn't exist (top_speed) but fails when the column already exists (mpg) - it sets all observations to the first value Mazda RX4.

library(tidyverse)
mtcars %>%
  as_tibble() %>%
  rownames_to_column("car") %>%
  mutate(top_speed = ifelse("top_speed" %in% names(.), top_speed, NA),
         mpg = ifelse("mpg" %in% names(.), mpg, NA)) %>%
  select(car, top_speed, mpg, everything())

# # A tibble: 32 x 13
#                  car top_speed   mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#                <chr>     <lgl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#  1         Mazda RX4        NA    21     6 160.0   110  3.90 2.620 16.46     0     1     4     4
#  2     Mazda RX4 Wag        NA    21     6 160.0   110  3.90 2.875 17.02     0     1     4     4
#  3        Datsun 710        NA    21     4 108.0    93  3.85 2.320 18.61     1     1     4     1
#  4    Hornet 4 Drive        NA    21     6 258.0   110  3.08 3.215 19.44     1     0     3     1
#  5 Hornet Sportabout        NA    21     8 360.0   175  3.15 3.440 17.02     0     0     3     2
#  6           Valiant        NA    21     6 225.0   105  2.76 3.460 20.22     1     0     3     1
#  7        Duster 360        NA    21     8 360.0   245  3.21 3.570 15.84     0     0     3     4
#  8         Merc 240D        NA    21     4 146.7    62  3.69 3.190 20.00     1     0     4     2
#  9          Merc 230        NA    21     4 140.8    95  3.92 3.150 22.90     1     0     4     2
# 10          Merc 280        NA    21     6 167.6   123  3.92 3.440 18.30     1     0     4     4

I found this answer more helpful: https://stackoverflow.com/questions/48206514/find-whether-a-column-exists-in-r-data-frame-and-if-not-then-create-it/48206590 — Joshua Gubler, Aug 25 '21 at 18:58

Joris C. · Accepted Answer · 2019-08-26T06:00:43.617

47

Another option that does not require creating a helper function (or an already complete data.frame) using tibble's add_column:

library(tibble)

cols <- c(top_speed = NA_real_, nhj = NA_real_, mpg = NA_real_)

add_column(mtcars, !!!cols[setdiff(names(cols), names(mtcars))])

edited Aug 26 '19 at 06:00

answered Dec 09 '18 at 11:37

Joris C.

5,721
3
12
27

24

I like the simplicity of this. I wanted to use it in a dplyr chain, and did so by slightly modifying: `%>% add_column(!!!cols[!names(cols) %in% names(.)])` – Chris Umphlett May 03 '19 at 14:17
but @ChrisUmphlett that results in "Error in names(cols) %in% names(.) : object '.' not found" – Tino Kanngießer Nov 26 '21 at 18:00
That was a couple years ago so it’s possible there have been changes to the packages that make this not work. Or something else is different. Make a new question and @me in a comment and I’ll look at it – Chris Umphlett Nov 26 '21 at 19:58

score 20 · Answer 2 · edited Aug 24 '17 at 21:35

20

We could create a helper function to create the column

fncols <- function(data, cname) {
  add <-cname[!cname%in%names(data)]

  if(length(add)!=0) data[add] <- NA
  data
}
fncols(mtcars, "mpg")
fncols(mtcars, c("topspeed","nhj","mpg"))

edited Aug 24 '17 at 21:35

Onyambu

67,392
3
24
53

answered Aug 24 '17 at 09:35

akrun

874,273
37
540
662

2

Many thanks... think it works the fastest out of the answers so far... I am going to use it in a chain with multiple (~15) variables... `mtcars %>% fncols("mpg") %>% fncols("top_speed")` – guyabel Aug 24 '17 at 10:04
3

instead of using it in a chain, look at the edited version – Onyambu Aug 24 '17 at 17:14

score 14 · Answer 3 · answered Aug 24 '17 at 09:37

You can use the rowwise function like this :

library(tidyverse)
mtcars %>%
  tbl_df() %>%
  rownames_to_column("car") %>%
  rowwise() %>%
  mutate(top_speed = ifelse("top_speed" %in% names(.), top_speed, NA),
         mpg = ifelse("mpg" %in% names(.), mpg, NA)) %>%
  select(car, top_speed, mpg, everything())

score 7 · Answer 4 · answered Aug 24 '17 at 16:58

If you had an empty dataframe that contains all the names to check for, you can use bind_rows to add columns.

I used purrr:map_dfr to make the empty tibble with the appropriate column names.

columns = c("top_speed", "mpg") %>%
     map_dfr( ~tibble(!!.x := logical() ) )

# A tibble: 0 x 2
# ... with 2 variables: top_speed <lgl>, mpg <lgl>

bind_rows(columns, mtcars)

# A tibble: 32 x 12
   top_speed   mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
       <lgl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1        NA  21.0     6 160.0   110  3.90 2.620 16.46     0     1     4     4
 2        NA  21.0     6 160.0   110  3.90 2.875 17.02     0     1     4     4
 3        NA  22.8     4 108.0    93  3.85 2.320 18.61     1     1     4     1

colej1390 · Answer 5 · 2023-08-25T16:09:04.127

5

Per Hadley (2023), we should be using dplyr::bind_rows() with an empty tibble.

# Case when column exists
dplyr::tibble(x='a',y='b') %>% dplyr::bind_rows(dplyr::tibble(y=character()))
# Case when column does not exist
dplyr::tibble(x='a') %>% dplyr::bind_rows(dplyr::tibble(y=character()))

Original answer (outdated as of dplyr_1.1.0): Or, you can use dplyr::union_all() with an empty tibble.

# Case when column exists
dplyr::tibble(x='a',y='b') %>% dplyr::union_all(dplyr::tibble(y=character()))
# Case when column does not exist
dplyr::tibble(x='a') %>% dplyr::union_all(dplyr::tibble(y=character()))

edited Aug 25 '23 at 16:09

answered Feb 25 '21 at 13:38

colej1390

157
4
11

Does not work with dplyr version 1.1.0 any more. – mirirai Feb 22 '23 at 11:37
1

[Per Hadley](https://github.com/tidyverse/dplyr/issues/6692#issuecomment-1420929833), we should be using `dplyr::bind_rows()` instead of `union_all()`. – colej1390 Feb 23 '23 at 14:42

score 3 · Answer 6 · answered Aug 24 '17 at 10:05

Try the following,

library(tidyverse)

mtcars %>%
  tbl_df() %>%
  rownames_to_column("car") %>%
  mutate(top_speed = if ("top_speed" %in% names(.)){return(top_speed)}else{return(NA)},
         mpg = if ("mpg" %in% names(.)){return(mpg)}else{return(NA)}) %>%
  select(car, top_speed, mpg, everything())
# A tibble: 32 x 13
                 car top_speed   mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
               <chr>     <lgl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1         Mazda RX4        NA  21.0     6 160.0   110  3.90 2.620 16.46     0     1     4     4
 2     Mazda RX4 Wag        NA  21.0     6 160.0   110  3.90 2.875 17.02     0     1     4     4
 3        Datsun 710        NA  22.8     4 108.0    93  3.85 2.320 18.61     1     1     4     1
 4    Hornet 4 Drive        NA  21.4     6 258.0   110  3.08 3.215 19.44     1     0     3     1
 5 Hornet Sportabout        NA  18.7     8 360.0   175  3.15 3.440 17.02     0     0     3     2
 6           Valiant        NA  18.1     6 225.0   105  2.76 3.460 20.22     1     0     3     1
 7        Duster 360        NA  14.3     8 360.0   245  3.21 3.570 15.84     0     0     3     4
 8         Merc 240D        NA  24.4     4 146.7    62  3.69 3.190 20.00     1     0     4     2
 9          Merc 230        NA  22.8     4 140.8    95  3.92 3.150 22.90     1     0     4     2
10          Merc 280        NA  19.2     6 167.6   123  3.92 3.440 18.30     1     0     4     4
# ... with 22 more rows

I think the ifelse() doesn't inherit the class from the object.

score 1 · Answer 7 · answered Oct 03 '18 at 14:07

If you already have a dataframe with all the required columns, say

library(tidyverse)  

df_with_required_columns = 
      mtcars %>% 
      mutate(top_speed = NA_real_) %>%
      select(top_speed, mpg)

then you can simply bind_rows filtering out all the rows:

mtcars %>%
  rownames_to_column("car") %>%
  bind_rows( df_with_required_columns %>% filter(F) ) %>%
  select(car, top_speed, mpg, everything())

Note that missing columns will take the type from df_with_required_columns.

score 0 · Answer 8 · answered Aug 24 '17 at 09:39

You can bind columns of the new data.frame with a fake complete data.frame filled with NA, rename the duplicated columns, and then filter only the original names.

# your default complete vector of col names
standard.variables = names(mtcars)
# prep
default=mtcars %>% mutate_all(.funs=function(x) NA)
# treat with a data.frame missing 3 columns
test=mtcars %>% select(-mpg, -disp, -am)
bind_cols(test, default) %>% setNames(make.names(names(.), unique=TRUE)) %>% 
  select_(.dots=standard.variables) %>% head(2)
####    mpg cyl disp  hp drat    wt  qsec vs am gear carb
#### 1  NA   6   NA 110  3.9 2.620 16.46  0 NA    4    4
#### 2  NA   6   NA 110  3.9 2.875 17.02  0 NA    4    4

score 0 · Answer 9 · answered Jun 23 '22 at 11:16

Building on @Joris C. answer. This is a pipe friendly solution that does not require to create the cls vector upfront.

mtcars %>%
  # an inline anonymous function to add the needed columns
  (function(.df){
    cls <- c("top_speed", "nhj") # columns I need
    # adding cls columns with NAs if not present in the piped data.frame
    .df[cls[!(cls %in% colnames(.df))]] = NA
    return(.df)
  })

Adding column if it does not exist

9 Answers9

Linked