Adding a column if it does not exist

Question

I have a set of data frames with different variables. I want to read them in R and add columns to those that lack several variables, so that they all have a common set of standard variables, even if some of them are unobservable.

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

 library(tidyverse) mtcars %>% tbl_df() %>% 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 

Answer 1

We could create a helper function to create a 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")) 

Answer 2

Another option that does not require creating a helper function (or already completed data.frame) using tibble 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))]) 

Answer 3

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 ifelse () does not inherit a class from an object.

Answer 4

If you had an empty framework containing all the names to check, you can use bind_rows to add columns.

I used purrr:map_dfr to make an empty tibble with the corresponding 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 

Answer 5

You can use the rowwise function as follows:

 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()) 

Answer 6

You can associate the columns of the new data.frame with a fake full 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 

Answer 7

If you already have a data frame with all the necessary columns, say

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

then you can just bind_rows filter out all the lines:

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

Note that missing columns will be of type df_with_required_columns .