tidyverse spread with duplicate keys and fill NAs based on unique keys

user_258662 0
Viewed 625

Consider the minimal example:

library(tidyverse)
ex <-tribble(
  ~id, ~property, ~value,
  1,    "A",      9,
  1,    "A",      8,
  1,    "B",      7,
  2,    "A",      6,
  2,    "B",      5
)

My goal is to spread the property into columns to get this table:

tribble(
  ~id, ~A, ~B,
  1,  9,  7,
  1,  8,  7,
  2,  6,  5
)

Grouping by id and property and adding a key gets close but leaves NA:

## almost but not quite
ex %>% 
  group_by(id, property) %>%
  mutate(key = row_number()) %>%
  spread(property, value) %>% 
  select(-key) -> X
X

Gives: 

     id     A     B
1     1     9     7
2     1     8    NA
3     2     6     5

I can solve this in the minimal example by splitting out the above by each property, dropping NAs, and joining back by id:

inner_join(
  na.omit(select(X, id, A)),
  na.omit(select(X, id, B))
)

but clearly that does not generalize to arbitrary set of properties. What is a better tidyverse strategy to do this?

NOTE: Several previous questions speak to the first half of this, e.g. constructing the key column so that spread does not fail, but couldn't see something addressing the NAs.

tidyr dplyr tidyverse r
1 Answers

You can use fill from tidyr:

library(dplyr)
library(tidyr)

ex %>% 
  group_by(id, property) %>%
  mutate(key = row_number()) %>%
  spread(property, value) %>% 
  select(-key) %>%
  group_by(id) %>%
  fill(-id)

Result:

# A tibble: 3 x 3
# Groups:   id [2]
     id     A     B
  <dbl> <dbl> <dbl>
1     1     9     7
2     1     8     7
3     2     6     5
user_51506290
Related