Hi I have a dataframe with three main columns: company, product_type and product_ID. The first two column is self-explanined and the third column has ID that is global and is unique for every item.
input_example <- data.frame(company = c(rep("companyA", 4), rep("companyB", 6)), product_type = c(rep("shirts", 3), "pants", rep("shirts",4), rep("pants", 2)), product_ID = seq(1,10))
> input_example
company product_type product_ID
1 companyA shirts 1
2 companyA shirts 2
3 companyA shirts 3
4 companyA pants 4
5 companyB shirts 5
6 companyB shirts 6
7 companyB shirts 7
8 companyB shirts 8
9 companyB pants 9
10 companyB pants 10
What I want is all unique combinations between shirts and pants within each company, and has the unique product_ID associated with them. For example of desired output:
>output
company product_type.x product_ID.x product_type.y product_ID.y
1 companyA shirts 1 pants 4
2 companyA shirts 2 pants 4
3 companyA shirts 3 pants 4
4 companyB shirts 5 pants 9
5 companyB shirts 5 pants 10
6 companyB shirts 6 pants 9
7 companyB shirts 6 pants 10
8 companyB shirts 7 pants 9
9 companyB shirts 7 pants 10
10 companyB shirts 8 pants 9
11 companyB shirts 8 pants 10
I achived this by combing split and inner_join:
output <- split(input_case, input_case$company) %>% lapply(., function(x){
x %>% inner_join(x, by=c("company")) %>% filter(product_type.x != product_type.y)
}) %>% do.call(rbind,.) %>% filter(product_ID.x < product_ID.y) %>% remove_rownames()
Although my code can deal with this simple example, my real case data has around 100million rows of total product. I'm wondering if there is any method to accelerate this. I'm thinking parallel the lapply function, but it is still slow.
Thanks for any suggestions.