R: converting vertical data to horizontal format with conditionals

Question

I am trying to convert a data frame with a vertical format into a horizontal format. I'm familiar with the dcast function. However, I'd like to update the left hand side of the dcast equation with a conditional based on the fields that will become a row. Here is a specific example:

df = data.frame(ID=c(3,3,3,3,3,3,3,3,4,4,4,4),
                Field=rep(c("Color","Height","Weight","Condition"),3),
                Values=c("blue",72,140,"ON","blue",72,180,"OFF","green",80,162,"OFF"))

You'll notice that if we aggregate only based on ID, there will be multiple matches because there are multiple values when Field is "Condition" (that is, ID 3 has both an ON and OFF condition for Condition). Thus, I'd like to first start by pulling that out as a column, and then applying dcast, to result in a dataframe like so:

desiredDF = data.frame(ID=c(3,3,4),
                       Condition=c("ON","OFF","OFF"),
                       Color=c("blue","blue","green"),
                       Height=c(72,72,80),
                       Weight=c(140,180,162))

Any ideas?

Answer 1

You can use chop to nest Values corresponding to each (ID, Field) tuple, spread Fields and Values, and unnest() aftewards:

library(tidyverse)

df %>%
  chop(Values) %>%
  spread(Field, Values) %>%
  unnest(-1)

# # A tibble: 3 x 5
#      ID Color Condition Height Weight
#   <dbl> <fct> <fct>     <fct>  <fct> 
# 1     3 blue  ON        72     140   
# 2     3 blue  OFF       72     180   
# 3     4 green OFF       80     162