In an R dataframe, how do I broadcast columns corresponding to dimensions?
aggregate, dataframe, merge, r
Solution
`merge` will do what you intend your `broadcast` function to do.
A simple:
bcastSimPrice <- within(merge(simPrice, simVol), revenue <- volume * price)
should do the trick. Here I've wrapped merge in `within` to also add the column giving the revenue (`volume` x `price`).
Then, if you need to group rows (e.g. if there are multiple instances of farm A for a given crop and simId), then you can use `aggregate`:
aggregate(revenue ~ simId + crop + farm, sum, data=bcastSimPrice)
Problem
I have an R dataframe: ``` # here just define it directly, but it comes from a simulation simPrice <- data.frame(simId=c(1,1,2,2), crop=rep(c('apple','pear'),2), mean=rep(c(10,22),2), sd=rep(c(2,4),2), price=c(9,21,12,18)) simId crop mean sd price 1 1 apple 10 2 9 2 1 pear 22 4 21 3 2 apple 10 2 12 4 2 pear 22 4 18 ``` This is the price of fruit (apples and pears) in two different iterations of a simulation. In general, I may have any number of fruit or iterations. Crucially, I may also have other columns (e.g. varieties, date sold, location sold, etc). I have another dataframe giving the volume of fruit grown at a number of farms: ``` # here just define it directly, but it comes from a simulation simVol <- data.frame(simId=c(1,1,1,1,2,2,2,2), farm=rep(c('farm A', 'farm A', 'farm B', 'farm B'),2), crop=rep(c('apple','pear'),4), mean=rep(c(10,22),4), sd=rep(c(2,4),4), volume=c(9,21,12,18,10,22,11,19)) simId farm crop mean sd volume 1 1 farm A apple 10 2 9 2 1 farm A pear 22 4 21 3 1 farm B apple 10 2 12 4 1 farm B pear 22 4 18 5 2 farm A apple 10 2 10 6 2 farm A pear 22 4 22 7 2 farm B apple 10 2 11 8 2 farm B pear 22 4 19 ``` Now I want to multiply these together. I assume that to do this, I have to first "broadcast" `simPrice` over `farm`s so that the two dataframes have exactly the same order. My solution is this: ``` broadcast <- function(origDf, broadcast_dimList) { newDimDf <- do.call(expand.grid, broadcast_dimList); nReps <- nrow(newDimDf); # replicate each line of the original dataframe in place result <- origDf[sort(rep(row.names(origDf), nReps)), 1:ncol(origDf)] # add the new dimensions, repeated for each simId result <- cbind(newDimDf, result); # rename rows sequentially row.names(result)<-NULL; return(result); } bcastSimPrice <- broadcast(simPrice, list(farm=c('farm A','farm B'))) farm simId crop mean sd price 1 farm A 1 apple 10 2 9 2 farm B 1 apple 10 2 9 3 farm A 1 pear 22 4 21 4 farm B 1 pear 22 4 21 5 farm A 2 apple 10 2 12 6 farm B 2 apple 10 2 12 7 farm A 2 pear 22 4 18 8 farm B 2 pear 22 4 18 ``` This works, but it leaves me with the problem of now trying to match up the rows of `bcastSimPrice` (farms incrementing before crops) with the rows of `simVol` (the other way around). Is there another way to approach this problem? Thanks!