multiplying two data frames

Question

In R, i have 2 data frames "df1" and "df2". The df1 and df2 are as follows.

>df1
  date      value
1 1990-10-10  3
2 1990-10-11  2.3
3 1990-10-12  2.5

>df2
  date      value
1 1990-10-10  3
2 1990-10-11  2
3 1990-10-12  2

I need a third data frame "df3", that contains the same column names as df1 and df2. But the value field should be the product of values in df1 and df2. I am expecting the following output

>df3
  date      value
1 1990-10-10  9
2 1990-10-11  4.6
3 1990-10-12  4

Is it possible in R?

Answer 1

If all the dates are identical, then you can simply use:

df3 <- df1
df3$value <- as.numeric(as.character(df3$value))
df3$value <- df3$value * as.numeric(as.character(df2$value))

If the dates are NOT identical in the two data.frames, please use @MatthewLundberg answer below

Answer 2

"Merge" the long way with rbind, and use aggregate to produce the products:

aggregate(value ~ date, data=rbind(df1,df2), FUN=prod)
##         date value
## 1 1990-10-10   9.0
## 2 1990-10-11   4.6
## 3 1990-10-12   5.0

If you have mode factor for the value columns of the data frames, you'll have to convert to character then to numeric to extract the value:

df1$value <- as.factor(df1$value)
df2$value <- as.factor(df2$value)

aggregate(as.numeric(as.character(value)) ~ date, data=rbind(df1,df2), FUN=prod)
##         date as.numeric(as.character(value))
## 1 1990-10-10                             9.0
## 2 1990-10-11                             4.6
## 3 1990-10-12                             5.0

You can also convert with as.numeric(levels(value))[value]. See ?factor for details.

Answer 3

Perhaps you can merge the two data.frames first and then proceed with transform:

> temp <- merge(df1, df2, by = "date")
> temp
        date value.x value.y
1 1990-10-10     3.0       3
2 1990-10-11     2.3       2
3 1990-10-12     2.5       2
> transform(temp, Prod = value.x * value.y)
        date value.x value.y Prod
1 1990-10-10     3.0       3  9.0
2 1990-10-11     2.3       2  4.6
3 1990-10-12     2.5       2  5.0

Here, since both data.frames have the same column names, I've specified that we want to merge only by the "date" variable so that both "value" variables would be present in the "temp" data.frame.

Answer 4

After you fix you problems with df1 and df2 having "values" of type factor which is really someting you should do before attempting this, you can do just this:

df3 <- data.frame( data=df1$date, value=df1[ ,"value"]*df2[ ,"value"])

The conversion of factors that should be numeric is covered in the R-FAQ: FAQ 7.10