Assign ID to column with NA's

user_13321647 0
Viewed 54

This must be easy but my brain is blocked!

I have this dataframe:

   col1 
   <chr>
 1 A    
 2 B    
 3 NA   
 4 C    
 5 D    
 6 NA   
 7 NA   
 8 E    
 9 NA   
10 F    

df <- structure(list(col1 = c("A", "B", NA, "C", "D", NA, NA, "E", 
NA, "F")), row.names = c(NA, -10L), class = c("tbl_df", "tbl", 
"data.frame"))

I want to add a column with uniqueID only for values that are not NA with tidyverse.

Expected output:

   col1  uniqueID
   <chr>    <dbl>
 1 A            1
 2 B            2
 3 NA          NA
 4 C            3
 5 D            4
 6 NA          NA
 7 NA          NA
 8 E            5
 9 NA          NA
10 F            6

I have tried: n(), row_number(), cur_group_id ....

dplyr tidyverse na r
4 Answers

We could do this easily in data.table. Specify the condition in i i.e. non-NA elements in 'col1', create the column 'uniqueID' with the sequence of elements by assignment (:=)

library(data.table)
setDT(df)[!is.na(col1), uniqueID := seq_len(.N)]

-output

df
    col1 uniqueID
 1:    A        1
 2:    B        2
 3: <NA>       NA
 4:    C        3
 5:    D        4
 6: <NA>       NA
 7: <NA>       NA
 8:    E        5
 9: <NA>       NA
10:    F        6

In dplyr, we can use replace

library(dplyr)
df %>%
     mutate(uniqueID = replace(col1, !is.na(col1), 
             seq_len(sum(!is.na(col1)))))

-output

# A tibble: 10 x 2
   col1  uniqueID
   <chr> <chr>   
 1 A     1       
 2 B     2       
 3 <NA>  <NA>    
 4 C     3       
 5 D     4       
 6 <NA>  <NA>    
 7 <NA>  <NA>    
 8 E     5       
 9 <NA>  <NA>    
10 F     6
user_37322710

Another approach:

library(dplyr)
df %>% 
  mutate(UniqueID = cumsum(!is.na(col1)), 
         UniqueID = if_else(is.na(col1), NA_integer_, UniqueID))

# A tibble: 10 x 2
   col1  UniqueID
   <chr>    <int>
 1 A            1
 2 B            2
 3 NA          NA
 4 C            3
 5 D            4
 6 NA          NA
 7 NA          NA
 8 E            5
 9 NA          NA
10 F            6
user_107227520

A base R option using match + na.omit + unique

transform(
  df,
  uniqueID = match(col1, na.omit(unique(col1)))
)

gives

   col1 uniqueID
1     A        1
2     B        2
3  <NA>       NA
4     C        3
5     D        4
6  <NA>       NA
7  <NA>       NA
8     E        5
9  <NA>       NA
10    F        6
user_121587570

A weird tidyverse solution:

library(dplyr)

df %>%
  mutate(id = ifelse(is.na(col1), 0, 1),
         id = cumsum(id == 1), 
         id = ifelse(is.na(col1), NA, id))


# A tibble: 10 x 2
   col1     id
   <chr> <int>
 1 A         1
 2 B         2
 3 NA       NA
 4 C         3
 5 D         4
 6 NA       NA
 7 NA       NA
 8 E         5
 9 NA       NA
10 F         6
user_143145200
Related