Aggregation when combining two data frames in R
The ultimate goal is to sum the total value ( transact_data$qty
) for each entry in product_info
, where transact_data$productId
exists in product_info
, and where transact_data$date
is between product_info$beg_date
and product_info$end_date
.
Below is the data:
product_info <- data.frame(productId = c("A", "B", "A", "C","C","B"),
old_price = c(0.5,0.10,0.11,0.12,0.3,0.4),
new_price = c(0.7,0.11,0.12,0.11,0.2,0.3),
beg_date = c("2014-05-01", "2014-06-01", "2014-05-01", "2014-06-01","2014-05-01", "2014-06-01"),
end_date = c("2014-05-31", "2014-06-31", "2014-05-31", "2014-06-31","2014-05-31", "2014-06-31"), stringsAsFactors=FALSE)
transact_data <- data.frame(productId=c('A', 'B','A', 'C','A', 'B','C', 'B','A', 'C','A', 'B'),
date=c("2014-05-05", "2014-06-22", "2014-07-05", "2014-08-31","2014-05-03", "2014-02-22",
"2014-05-21", "2014-06-19", "2014-03-09", "2014-06-22","2014-04-03", "2014-07-08"),
qty =c(12,15,5,21,13,17,2,5,11,9,6,4), stringsAsFactors=FALSE)
My first step was to combine both dataframes using productId:
sku_transact_merge <-merge(x=product_info, y=transact_data, by = c("productId"))
The next step was to calculate the sum of the sum:
sku_transact_merge$total_qty <- ifelse(sku_transact_merge$date >= sku_transact_merge$beg_date &
sku_transact_merge$date <= sku_transact_merge$end_date,
aggregate(qty ~ productId+beg_date+end_date,
data= sku_transact_merge, sum), 0)
The result is not what I want and I get the error
An object(list) cannot be coerced to type 'double'
Any pointers on how to properly execute this logic would be much appreciated!
source to share
This could be another way to do it with dplyr()
(this should be efficient if your dataset is huge)
library(dplyr)
df = subset(sku_transact_merge, date > beg_date & date < end_date)
df = subset(df, select= -c(date))
out = unique(df %>% group_by(productId,old_price) %>% mutate(qty = sum(qty)))
#> out
#Source: local data frame [6 x 6]
#Groups: productId, old_price
#productId old_price new_price beg_date end_date qty
#1 A 0.50 0.70 2014-05-01 2014-05-31 25
#2 A 0.11 0.12 2014-05-01 2014-05-31 25
#3 B 0.10 0.11 2014-06-01 2014-06-31 20
#4 B 0.40 0.30 2014-06-01 2014-06-31 20
#5 C 0.12 0.11 2014-06-01 2014-06-31 9
#6 C 0.30 0.20 2014-05-01 2014-05-31 2
otherwise you can use data.table
library(data.table)
out = setDT(df)[, list(qtynew = sum(qty)), by = list(productId, old_price)]
#> out
# productId old_price qtynew
#1: A 0.50 25
#2: A 0.11 25
#3: B 0.10 20
#4: B 0.40 20
#5: C 0.12 9
#6: C 0.30 2
source to share
One approach would be to iterate over the items in product_info
, identifying all the corresponding products in, transact_data
and summing their values:
sapply(seq(nrow(product_info)), function(x) {
d <- product_info[x,]
sum(transact_data$qty[transact_data$productId == d$productId &
transact_data$date >= d$beg_date &
transact_data$date <= d$end_date])
})
# [1] 25 20 25 9 2 20
You can add this as a new column in product_info
if you like.
source to share
product_info$total_qty <- aggregate(col~row,which(outer(product_info$productId,transact_data$productId,`==`)&outer(product_info$beg_date,transact_data$date,`<=`)&outer(product_info$end_date,transact_data$date,`>=`),arr.ind=T),function(x) sum(transact_data$qty[x]))$col;
product_info;
## productId old_price new_price beg_date end_date total_qty
## 1 A 0.50 0.70 2014-05-01 2014-05-31 25
## 2 B 0.10 0.11 2014-06-01 2014-06-31 20
## 3 A 0.11 0.12 2014-05-01 2014-05-31 25
## 4 C 0.12 0.11 2014-06-01 2014-06-31 9
## 5 C 0.30 0.20 2014-05-01 2014-05-31 2
## 6 B 0.40 0.30 2014-06-01 2014-06-31 20
Explanation
First, a logical matrix is created for each of the three matching criteria, using outer()
to compare each entry in product_info
with each entry in transact_data
. These three logical matrices are logical-AND-AND together to form the final logical matrix representing which combinations of entries match.
outer(product_info$productId,transact_data$productId,`==`)
&outer(product_info$beg_date,transact_data$date,`<=`)
&outer(product_info$end_date,transact_data$date,`>=`)
## [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12]
## [1,] TRUE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [2,] FALSE TRUE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE
## [3,] TRUE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
## [4,] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE
## [5,] FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE
## [6,] FALSE TRUE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE
Then, the row and column indices c are TRUE
determined by calling which()
c arr.ind=T
. Row indices are comparable records from product_info
(since they were to the left of the calls outer()
) and column indices are comparable records from transact_data
.
which(...,arr.ind=T)
## row col
## [1,] 1 1
## [2,] 3 1
## [3,] 2 2
## [4,] 6 2
## [5,] 1 5
## [6,] 3 5
## [7,] 5 7
## [8,] 2 8
## [9,] 6 8
## [10,] 4 10
Since we want to sum the values qty
from transact_data
for each record in product_info
, we can group the indices by writing a custom aggregation function to index with the indices and return one value for each .aggregate()
col
row
transact_data$qty
col
sum()
row
aggregate(col~row,...,function(x) sum(transact_data$qty[x]))
## row col
## 1 1 25
## 2 2 20
## 3 3 25
## 4 4 9
## 5 5 2
## 6 6 20
Finally, we can assign the result directly product_info$total_qty
to complete the solution.
product_info$total_qty <- ...$col;
I'm not entirely sure if this is a guarantee that aggregate()
it will always return its result ordered by the grouping columns. I just asked about this in Does aggregate () provide the result to be ordered by the grouping columns? ...
Also, I just realized that direct assignment would fail if not all records in product_info
had at least one matching record in transact_data
.
If any of these assumptions are violated, the decision can be fixed as follows:
product_info$total_qty <- with(aggregate(col~row,which(outer(product_info$productId,transact_data$productId,`==`)&outer(product_info$beg_date,transact_data$date,`<=`)&outer(product_info$end_date,transact_data$date,`>=`),arr.ind=T),function(x) sum(transact_data$qty[x])),col[match(1:nrow(product_info),row)]);
product_info;
## productId old_price new_price beg_date end_date total_qty
## 1 A 0.50 0.70 2014-05-01 2014-05-31 25
## 2 B 0.10 0.11 2014-06-01 2014-06-31 20
## 3 A 0.11 0.12 2014-05-01 2014-05-31 25
## 4 C 0.12 0.11 2014-06-01 2014-06-31 9
## 5 C 0.30 0.20 2014-05-01 2014-05-31 2
## 6 B 0.40 0.30 2014-06-01 2014-06-31 20
Now, instead of the last dereferencing step $col
, we have to construct a full vector of length equal to the number of strings in product_info
, and the match()
sum qty
(which are inside col
) to their respective indices (inside row
), with a little help with()
.
product_info$total_qty <- with(...,col[match(1:nrow(product_info),row)]);
source to share