Association Rule mining using excel; Count how many rows have two searched for numbers

DoctorofMadness

New Member
Joined
Mar 13, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I'm a college student working on a report currently and am having an issue with a problem I've recently run into and it's really throwing me for a loop. I thought I found a solution in this post, but couldn't get it to output anything but 0.
[[ Count how many times in total two values appear together in a row for all the rows ]]

To begin the project I was given a dataset of a little under 18000 transactions, with 19 different items that may have been purchased. The goal of the project is to draw what item types have associations with each other; for instance, if someone buys product 17, what is the confidence we can have that they would also purchase product 4 in the same transaction. The way I have the sheet looks as follows (first 20 transactions)

Transaction_IDCustomer_IDItem_TypesItem Types
1​
15107144​
17,8,5,4,17854
2​
15107169​
17,9,8,1798
3​
15120097​
13,8,138
4​
15128454​
9,9,8,998
5​
15128488​
10,10,7,10107
6​
15131912​
2,2
7​
15134734​
3,2,2,322
8​
15500173​
13,10,8,7,5,2,2,1,1310875221
9​
15502484​
5,5
10​
15507087​
7,7
11​
15508887​
8,8
12​
15510149​
12,12
13​
15513135​
12,12
14​
15514612​
13,13
15​
15518225​
13,10,1,13101
16​
15518985​
8,8,3,883
17​
15520494​
13,5,135
18​
15523811​
17,17
19​
15524504​
14,11,10,5,5,2,1,1411105521
20​
15529982​
12,12

On the right side of the spreadsheet, the data is just pulled out of the Item_Types column using Textsplit() to remove the commas.

I thought I was onto something with the thread I linked above but cannot get it to output anything except 0, but I also have two theories onto why it doesn't work; each transaction might see the same item precedent or antecedent ( first item or second item ) more than once (see Transaction_ID 19, #5 appears twice).

I also decided to write the formula with 100 columns in the mmult() part, since I thought the large amount of blank space wouldn't affect anything since the way I am interpreting it to work.

=SUM((MMULT(--('Transaction IDs + Item Types'!D2:CY17918=L3),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})>0)
*(MMULT(--('Transaction IDs + Item Types'!D2:CY17918=O3),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})>0))


Let me know if you have any ideas. I am trying to figure a way to write a formula that will check if a row contains two values I am looking for anywhere in the row, while allowing the same number(s) to potentially appear more than once. I also know there are different ways this could be done, one of which using powerpivot, others using RStudio, I just haven't used those tools before and would rather try and do it on software I understand before resorting to learning new tools.
 
If you want to return the transaction numbers for two matches you could do something like the following:

Book1
ABCDEFGHIJKL
1Transaction_IDCustomer_IDItem_TypesMatch 1Match 2Transaction Numbers
211510714417,8,5,17,4,10181519
321510716917,9,8,17,5818
431512009713,8,1013815
54151284549,9,8,12819
651512848810,10,7,
76151319122,
87151347343,2,2,
981550017313,10,8,7,5,2,2,1,
109155024845,
1110155070877,
1211155088878,
13121551014912,
14131551313512,
15141551461213,
16151551822513,10,1,
1716155189858,8,3,
18171552049413,5,
19181552381117,
20191552450414,11,10,5,5,2,1,
21201552998212,
Sheet1
Cell Formulas
RangeFormula
G2:I2,G3:H5G2=TRANSPOSE(FILTER($A$2:$A$21,(ISNUMBER(SEARCH($E2&",",$C$2:$C$21,1))*ISNUMBER(SEARCH($F2&",",$C$2:$C$21,1)))=1))
Dynamic array formulas.


You will not need to do the TEXTSPLIT or have any issues if a customer bought more than one of any item.

Doug
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sorry, I forgot to mention that if you want a count rather than the Transaction_IDs, in the formula, change TRANSPOSE to COUNT.

Doug
 
Upvote 0
What I had ended up doing was replacing all of the numbers with a string definition of what that item type was;

Item DescItem Type# of Purchases containingTotal ratio
eggs12234113.07%
snack17210611.75%
cook8180610.08%
butter316879.42%
cereal514458.06%
ice cream1314077.85%
bbq212717.09%
Chemicals2012587.02%
Pet Products2111636.49%
*******s910966.12%
coffee78985.01%
bacon15272.94%
soft193812.13%
pizza162621.46%
nuts141580.88%


I then used a string search to perform the count on the "transaction IDs + Item Types" table, where we had the textsplit function.

=COUNTIF('Transaction IDs + Item Types'!C:C,F25&"*")

This formula went into the 3rd column of this pasted table, and as far as I know, should be giving an accurate result.


"Transaction IDs + Item Types" now looks like;
Transaction_IDCustomer_IDItem_TypesItem Types
1​
15107144​
pet products,cereal,cook,snack,pet productscerealcooksnack
2​
15107169​
cook,*******s,snack,cook*******ssnack
3​
15120097​
ice cream,cook,ice creamcook
4​
15128454​
cook,*******s,*******s,cook*******s
5​
15128488​
coffee,chemicals,chemicals,coffeechemicals
6​
15131912​
bbq,bbq
7​
15134734​
bbq,butter,bbq,bbqbutter
8​
15500173​
coffee,cook,bacon,bbq,chemicals,cereal,ice cream,bbq,coffeecookbaconbbqchemicalscerealice cream
9​
15502484​
cereal,cereal
10​
15507087​
coffee,coffee

And my confidence and support sheet now looks like this;

Confidence
Antecedent1Consequence1# of Customers buying Antecedent# of Customers buying Consequence# of Customers buying bothSupportAssociation, Antecedent = ConsequenceAssociation, Consequence = Antecedent
snackeggs-->2106234111720.263548460.5565052230.500640752
snackcook-->2106180610100.2581799590.4795821460.559246955
snackbutter-->2106168710020.2641708410.4757834760.593953764
snackcereal-->2106144510340.2911855820.4909781580.715570934
snackice cream-->210614076610.1881582690.3138651470.469793888
snackbbq-->210612718930.264435890.4240265910.702596381
snackchemicals-->210612588670.2577288940.4116809120.689189189
snackpet products-->210611636380.1951667180.302943970.548581255
snack*******s-->210610967830.2445346660.3717948720.714416058
snackcoffee-->21068984100.1364846870.1946818610.456570156
snackbacon-->21065274080.1549563240.1937321940.774193548
snacksoft-->21063813050.1226377160.1448243110.800524934
snackpizza-->21062622510.1059966220.1191832860.958015267
snacknuts-->21061581550.0684628980.073599240.981012658


Let me know if you think there might be anything at fault with the countif() by string function I had wrote. Its my first time using it since a brief example in my freshman year excel class but I think it works okay?

Thanks,
 
Upvote 0
Oh I had also used your advice with the updated textsplit with unique(), hence why transaction ID 5 only says chemicals once, when it was bought twice
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top