I'm trying to find the minimum value of a dataframe based on multiple columns. I'm able to do this successfully using the aggregate function below. However, the result does NOT contain combinations of factors where there is no data in the input data frame.
What I've got:
# all possibilities of fruits, cities, and vegetables:
fruits<-c('apple','banana','grape')
cities<-c('new york','chicago','los angeles')
vegetables<-c('cucumber','mushroom')
#my input (ie, a sample from a test:
inputdf<-data.frame(fruit=c('apple','apple','apple','banana','banana','banana','grape','grape','grape'),city=c('new york','new york','new york','new york','chicago','los angeles','chicago','chicago','chicago'),vegetable=c('cucumber','cucumber','mushroom','cucumber','mushroom','mushroom','cucumber','cucumber','cucumber'),value=c(5,3,4,6,5,7,2,7,4))
#my aggregation:
outdf<-aggregate(value ~ fruit + city + vegetable,inputdf,function(x) min(x))
The output I get is:
fruit city vegetable value
grape chicago cucumber 2
apple new york cucumber 3
banana new york cucumber 6
banana chicago mushroom 5
banana los angeles mushroom 7
apple new york mushroom 4
This is correct, however, I also want the rows that correspond to the combinations of columns that didnt exist at all in the input df:
fruit city vegetable value
apple new york cucumber 3
apple new york mushroom 4
apple chicago cucumber NA
apple chicago mushroom NA
apple los angeles cucumber NA
apple los angeles mushroom NA
banana new york cucumber 6
banana new york mushroom NA
banana chicago cucumber NA
banana chicago mushroom 5
banana los angeles cucumber NA
banana los angeles mushroom 7
grape new york cucumber NA
grape new york mushroom NA
grape chicago cucumber 2
grape chicago mushroom NA
grape los angeles cucumber NA
grape los angeles mushroom NA
I'd like to be able to do this for any number of columns on which to combine. is there a simple way to do that? The reason I want that output is because I then need to transform the NAs to a specific value and average those values over the same subsets again. Thanks!