I have a table of orders where we have many of orders, each order is an item. And in excel, you have 2 columns: OrderID and ItemName. So if u have 4 items in one order, you will have 4 row have the same orderId but different ItemName, look like this:
When I'm using this data for Associate-rule in Orange3, to find which Item you will buy when you buy this/these item. For sample: you buy beard, there are high chance you will buy milk or egg. Or when you buy a cake and a Coke, you have high chance to buy cups and dishes. But when I done it, in the end, there are no rule be found.I found two ways for Orange3 read the right data is
- Same as the sample data 'FoodMart 2000', where each row is a list items in one orderID. I tried to convert the origanal data to list by using 'duplicate merge' in Excel, like this:
But still not right, there is no rules out put. Am I missing something?
- I find somewhere that you have to convert data into Boolean value - different table like a columsns is ID, the rest columns is Items, and the value is: 1 -have, 0 - Don't have. like this:
How do I convert the origanal data to be like above? how do we convert the excel data into the data that Orange can read and find rules? And I want do it by using tools/function available in Orange3 or Excel, minimun realate code.
EDIT1: After I add Quanity, underscore before merge. Input data as Excel(.xlsx)
Data table in Orange read as this:
After this, I remove BillNo column (like FoodMarket200)
And Linked to Associate rules:
As you can see, Frequent Item read seperated item.