CODEWITHSUNDEEP
rdataframeunique
grapestory
grapestory

Reputation: 183

Create data.frame based on unique column values in R?

I have a data.frame of observations with columns of metadata and I would like to create a new data.frame with the same columns but the rows each representing an unique combination of every column value. Here is an example: 

# what I have
df <- data.frame("Color" = c("Red", "Blue", "Green", "Green"), 
                 "Size" = c("Large", "Large", "Large", "Small"), 
                 "Value" = c(0, 1, 1, 1))
> df
  Color  Size Value
1   Red Large     0
2  Blue Large     1
3 Green Large     1
4 Green Small     1

# what I want
ideal_df <- data.frame("Color" = c("Red", "Red", "Red", "Red", "Blue", "Blue", "Blue", "Blue", "Green", "Green", "Green", "Green"), 
                       "Size" = c("Large", "Large", "Small", "Small", "Large", "Large", "Small", "Small", "Large", "Large", "Small", "Small"), 
                       "Value" = c(0,1,0,1,0,1,0,1,0,1,0,1))
> ideal_df
   Color  Size Value
1    Red Large     0
2    Red Large     1
3    Red Small     0
4    Red Small     1
5   Blue Large     0
6   Blue Large     1
7   Blue Small     0
8   Blue Small     1
9  Green Large     0
10 Green Large     1
11 Green Small     0
12 Green Small     1

I have tried using a for loop however my data is much larger than this example and it hangs. I tried to search this question but couldn't find something quite like it. I am happy to look at other threads if this has already been answered though! Thank you for your time.

Upvotes: 2

Views: 861

Answers (2)

coffeinjunky
coffeinjunky

Reputation: 11514

Just to add a base R solution:

new_df <- expand.grid(Color = unique(df$Color)
                   , Size = unique(df$Size)
                   , Value = unique(df$Value))

In case performance is a question, here a benchmark comparison:

sandy <- function(){
  expand(df, Color, Size, Value)
}

cj <- function(){
  expand.grid(Color = unique(df$Color)
              , Size = unique(df$Size)
              , Value = unique(df$Value))
}

library(microbenchmark)
microbenchmark(sandy(), cj())
Unit: microseconds
    expr      min       lq      mean   median       uq      max neval
 sandy() 1382.524 1494.675 1693.1749 1562.084 1736.524 7352.916   100
    cj()  138.914  152.746  204.8588  173.321  191.910 2889.398   100

Upvotes: 1

Sandy AB
Sandy AB

Reputation: 221

This is a job for expand() from the tidyr package:

library(tidyr)

new_df <- df %>% expand(Color, Size, Value)

Upvotes: 1

Related Questions