title: R: multiply columns by rows to create country-specific index
body: <p>I am trying to create a country-specific index based on the import share of certain food commodities.</p>
  
  <p>I have the following data: <em>Prices</em> contains time-series data on commodity prices for a number of food commodities. <em>Weights</em> contains data on the country-specific import shares for the relevant commodities (see mock data).   </p>
  
  <p>What I want to do is to create a country-specific food-price index which is the sum of the price-series of imported commodities multiplied by the import share. </p>
  
  <p>So in the example data the food-price index for Australia will be:</p>
  
  <pre><code>FOODct = 0.12 * WHEATt + 0.08 * SUGARt
  </code></pre>
  
  <p>Where <em>c</em> indicates country and <em>t</em> time. </p>
  
  <p>So basically my question is: How do I multiply the columns by the rows for each country?</p>
  
  <p>I have some experience with R but trying to solve this I seem to be punching above my weight. I also haven't found any useful pointers elsewhere so I was hoping that maybe anyone of you might have good suggestions. </p>
  
  <pre><code>## Code to create mock data:
  
  ## Generate data on country weights
  country&lt;-c(rep("Australia",2),rep("Zimbabwe",3))
  item&lt;-c("Wheat","Sugar","Wheat","Sugar","Soybeans")
  itemcode&lt;-c(1,2,1,2,3)
  share&lt;-c(0.12,0.08,0.16,0.08,0.03)
  weights&lt;-data.frame(country,item,itemcode,share)
  
  ## Generate data on price index
  date&lt;-seq(as.Date("2005/1/1"),by="month",length.out=12)
  Wheat&lt;-runif(12,80,160)
  Sugar&lt;-runif(12,110,230)
  Soybeans&lt;-runif(12,60,130)
  prices&lt;-data.frame(date,Wheat,Sugar,Soybeans)
  </code></pre>
  
  <p><strong>EDIT: Solution</strong></p>
  
  <p>Thanks to alexwhan for his suggestion ( I can't upvote unfortunately due to lack of stackoverflow street cred). And dnlbrky for the solution which was easiest to implement with the original data.  </p>
  
  <pre><code>## Load data.table package
  require(data.table)
  
  ## Convert data to data table
  prices&lt;-data.table(prices)
  weights&lt;-data.table(weights,key="item")
  
  ## Extract names for all the food commodities
  vars&lt;-names(prices)[!names(prices) %in% "date"] 
  
  ## Unstack items to create table in long format
  prices&lt;-data.table(date=prices[,date], stack(prices,vars),key="ind")
  
  ## Rename the columns
  setnames(prices,c("values","ind"),c("price","item"))
  
  ## Calculate the food price index
  priceindex&lt;-weights[prices,allow.cartesian=T][,list(index=sum(share*price)),
                                by=list(country,date)]
  
  ## Order food price index if not done automatically
  priceindex&lt;-priceindex[order(priceindex$country,priceindex$date),]
  </code></pre>