R:逐行乘列以创建特定于国家的索引。

Question

我正试图根据某些食品的进口份额建立一个特定国家的指数。

我有以下数据:价格包含了一些食品商品价格的时间序列数据。权数包含有关大宗商品特定国家进口份额的数据(见模拟数据)。

我想要做的是建立一个特定国家的食品价格指数，它是进口商品价格系列乘以进口份额的总和。

因此，在这个例子中，澳大利亚的食品价格指数是：

FOODct = 0.12 * WHEATt + 0.08 * SUGARt

其中c表示国家和时间。

因此，基本上我的问题是:我如何将每个国家的列乘以行数？

我有一些R的经验，但试图解决这个问题，我似乎是打我的重量。我也没有在其他地方找到任何有用的建议，所以我希望你们中的任何人都能有好的建议。

## Code to create mock data:

## Generate data on country weights
country<-c(rep("Australia",2),rep("Zimbabwe",3))
item<-c("Wheat","Sugar","Wheat","Sugar","Soybeans")
itemcode<-c(1,2,1,2,3)
share<-c(0.12,0.08,0.16,0.08,0.03)
weights<-data.frame(country,item,itemcode,share)

## Generate data on price index
date<-seq(as.Date("2005/1/1"),by="month",length.out=12)
Wheat<-runif(12,80,160)
Sugar<-runif(12,110,230)
Soybeans<-runif(12,60,130)
prices<-data.frame(date,Wheat,Sugar,Soybeans)

编辑:解决方案

感谢阿莱克惠恩的建议(不幸的是，由于缺乏堆积如山的街道信誉，我无法投票)。以及最容易用原始数据实现的解决方案。

## Load data.table package
require(data.table)

## Convert data to data table
prices<-data.table(prices)
weights<-data.table(weights,key="item")

## Extract names for all the food commodities
vars<-names(prices)[!names(prices) %in% "date"] 

## Unstack items to create table in long format
prices<-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<-weights[prices,allow.cartesian=T][,list(index=sum(share*price)),
                              by=list(country,date)]

## Order food price index if not done automatically
priceindex<-priceindex[order(priceindex$country,priceindex$date),]

Answer 1

我会打开/重塑权重表中的项目，然后使用data.table加入价格和权重。

## Generate data table for country weights:
weights<-data.table(country=c(rep("Australia",2),rep("Zimbabwe",3)),
                    item=c("Wheat","Sugar","Wheat","Sugar","Soybeans"),
                    itemcode=c(1,2,1,2,3),
                    share=c(0.12,0.08,0.16,0.08,0.03),
                    key="item")

## Generate data table for price index:
prices<-data.table(date=seq(as.Date("2005/1/1"),by="month",length.out=12),
                   Wheat=runif(12,80,160),
                   Sugar=runif(12,110,230),
                   Soybeans=runif(12,60,130))

## Get column names of all the food types:
vars<-names(prices)[!names(prices) %in% "date"]    

## Unstack the items and create a "long" table:
prices<-data.table(date=prices[,date], stack(prices,vars),key="ind")

## Rename the columns:
setnames(prices,c("values","ind"),c("price","item"))

prices[1:5]
##          date     price     item
## 1: 2005-01-01  88.25818 Soybeans
## 2: 2005-02-01  71.61261 Soybeans
## 3: 2005-03-01  77.91082 Soybeans
## 4: 2005-04-01 129.05806 Soybeans
## 5: 2005-05-01  74.63005 Soybeans

## Join the weights and prices tables, multiply the share by the price, and sum by country and date:
weights[prices,allow.cartesian=T][,list(index=sum(share*price)),by=list(country,date)]

##       country       date    index
##  1:  Zimbabwe 2005-01-01 27.05711
##  2:  Zimbabwe 2005-02-01 34.72842
##  3:  Zimbabwe 2005-03-01 35.23615
##  4:  Zimbabwe 2005-04-01 39.05027
##  5:  Zimbabwe 2005-05-01 39.48388
##  6:  Zimbabwe 2005-06-01 33.43677
##  7:  Zimbabwe 2005-07-01 32.55172
##  8:  Zimbabwe 2005-08-01 34.86790
##  9:  Zimbabwe 2005-09-01 33.29748
## 10:  Zimbabwe 2005-10-01 38.31180
## 11:  Zimbabwe 2005-11-01 31.29709
## 12:  Zimbabwe 2005-12-01 40.70930
## 13: Australia 2005-01-01 21.07165
## 14: Australia 2005-02-01 27.47660
## 15: Australia 2005-03-01 27.03025
## 16: Australia 2005-04-01 29.34917
## 17: Australia 2005-05-01 31.95188
## 18: Australia 2005-06-01 26.22890
## 19: Australia 2005-07-01 24.58945
## 20: Australia 2005-08-01 27.44728
## 21: Australia 2005-09-01 27.02199
## 22: Australia 2005-10-01 31.58282
## 23: Australia 2005-11-01 24.42326
## 24: Australia 2005-12-01 31.70109

Answer 2

这里有一个选择。绝对会有一个更整洁的方式来做这件事，但它应该会让你走。

首先，我将把weights转换成宽格式，以便更容易地用于我们的目的：

library(reshape2)
weights.c <- dcast(weights, country~item)
#     country Soybeans Sugar Wheat
# 1 Australia       NA  0.08  0.12
# 2  Zimbabwe     0.03  0.08  0.16

然后，我使用apply遍历每一行weights.c并计算“食品价格指数”(告诉我，如果计算不正确，我认为我正确地遵循了这个示例.)。

FOOD <- as.data.frame(apply(weights.c, 1, function(x) 
  as.numeric(x[3]) * prices$Soybeans + 
  as.numeric(x[3])*prices$Sugar + as.numeric(x[4])*prices$Wheat))

添加国家和日期标识符：

colnames(FOOD) <- weights.c$country
FOOD$date <- prices$date
FOOD
#    Australia Zimbabwe       date
# 1   35.04337 39.99131 2005-01-01
# 2   38.95579 44.72377 2005-02-01
# 3   33.45708 38.50418 2005-03-01
# 4   30.42181 34.04647 2005-04-01
# 5   36.03443 39.90905 2005-05-01
# 6   46.21269 52.29347 2005-06-01
# 7   41.88694 48.15334 2005-07-01
# 8   34.47848 39.83654 2005-08-01
# 9   36.32498 40.60091 2005-09-01
# 10  33.74768 37.17185 2005-10-01
# 11  38.84855 44.87495 2005-11-01
# 12  36.45119 40.11678 2005-12-01

希望这足够接近你想要的..。