CODEWITHSUNDEEP
rdplyrstringr
Jason
Jason

Reputation: 932

uniting multiple columns that contain empty NA values

I have a dataset where multiple columns were created, but the data is the same (boxID). I'd like to unite the columns so that I only have the boxID (an alpha-numeric code: two-letter state abbreviation and 2 numbers) and not NA values, which is what happens now when I use the unite() function from dplyr. Is there a similar function that can do this, or would I need to extract the boxIDs based on pattern-matching with stringr?

dat <- structure(list(boxId = c("CA04", "CA04", "CA01", "CA02", "CA04", 
"CA02", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), boxId__1 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "NM01", "NM14", "NM15", 
"NM16", "NM17", "NM18", "NM19", "NM20", "NM02", "NM03", "NM04", 
"NM05", "NM06", "NM07", "NM08", "NM09", "NM10", "NM11", "NM12", 
"NM13"), boxId__2 = c(NA, NA, NA, NA, NA, NA, "FL01", "FL02", 
"FL03", "FL09", "FL08", "FL07", "FL04", "FL05", "FL06", "FL10", 
"FL11", "FL13", "FL12", "FL20", "FL19", "FL18", "FL17", "FL16", 
"FL14", "FL15", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), boxID = c(NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), boxID__1 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
    boxID__2 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA), boxID__3 = c(NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, "IN05", NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
    ), boxID__4 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA), boxID__5 = c(NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
    boxID__6 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA), boxID__7 = c(NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
    boxID__8 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA), boxID__9 = c(NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, "WA11", NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
    )), row.names = c(NA, -48L), class = c("tbl_df", "tbl", "data.frame"
))

Here's what the data looks like:

# A tibble: 48 x 13
   boxId boxId__1 boxId__2 boxID boxID__1 boxID__2 boxID__3 boxID__4 boxID__5 boxID__6
   <chr> <chr>    <chr>    <lgl> <lgl>    <lgl>    <chr>    <lgl>    <lgl>    <lgl>   
 1 CA04  NA       NA       NA    NA       NA       NA       NA       NA       NA      
 2 CA04  NA       NA       NA    NA       NA       NA       NA       NA       NA      
 3 CA01  NA       NA       NA    NA       NA       NA       NA       NA       NA      
 4 CA02  NA       NA       NA    NA       NA       NA       NA       NA       NA      
 5 CA04  NA       NA       NA    NA       NA       NA       NA       NA       NA      
 6 CA02  NA       NA       NA    NA       NA       NA       NA       NA       NA      
 7 NA    NA       FL01     NA    NA       NA       NA       NA       NA       NA      
 8 NA    NA       FL02     NA    NA       NA       NA       NA       NA       NA      
 9 NA    NA       FL03     NA    NA       NA       NA       NA       NA       NA      
10 NA    NA       FL09     NA    NA       NA       NA       NA       NA       NA      
# … with 38 more rows, and 3 more variables: boxID__7 <lgl>, boxID__8 <lgl>, boxID__9 <chr>

When I use unite(), it looks like this:

dat %>%
  unite('newID')

And I'm stuck with these NA values:

# A tibble: 48 x 1
   newID                                   
   <chr>                                   
 1 CA04_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA
 2 CA04_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA
 3 CA01_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA
 4 CA02_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA
 5 CA04_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA
 6 CA02_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA
 7 NA_NA_FL01_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA
 8 NA_NA_FL02_NA_NA_NA_NA_NA_NA_NA_NA_NA_NA

Upvotes: 1

Views: 155

Answers (3)

Aur&#232;le
Aur&#232;le

Reputation: 12819

With coalesce:

dat %>% 
  mutate_all(as.character) %>% 
  transmute(newID = coalesce(!!! syms(names(.))))

# # A tibble: 48 x 1
#    newID
#    <chr>
#  1 CA04 
#  2 CA04 
#  3 CA01 
#  4 CA02 
#  5 CA04 
#  6 CA02 
#  7 FL01 
#  8 FL02 
#  9 FL03 
# 10 FL09 
# # … with 38 more rows

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388982

A base R approach would be to unlist all the values from the dataframe and select only the non-NA value to create a new dataframe with one column. 

x <- unlist(dat)
data.frame(new_id = x[!is.na(x)])

#           new_id
#boxId1       CA04
#boxId2       CA04
#boxId3       CA01
#boxId4       CA02
#boxId5       CA04
#boxId6       CA02
#boxId__129   NM01
#boxId__130   NM14
#boxId__131   NM15
#......

Upvotes: 2

dipetkov
dipetkov

Reputation: 3700

Do you know that there is only one non-NA in each row? If yes, then it you can pivot the table and drop all the NAs. You will get a table with the same number of rows as the original one.

library("tidyverse")

dat %>%
  # Adding row ID for clarity
  mutate(row = row_number()) %>%
  gather(box, name, - row) %>%
  drop_na()
#> # A tibble: 48 x 3
#>      row box      name 
#>    <int> <chr>    <chr>
#>  1     1 boxId    CA04 
#>  2     2 boxId    CA04 
#>  3     3 boxId    CA01 
#>  4     4 boxId    CA02 
#>  5     5 boxId    CA04 
#>  6     6 boxId    CA02 
#>  7    29 boxId__1 NM01 
#>  8    30 boxId__1 NM14 
#>  9    31 boxId__1 NM15 
#> 10    32 boxId__1 NM16 
#> # ... with 38 more rows

Created on 2019-03-11 by the reprex package (v0.2.1)

Upvotes: 2

Related Questions