finding pattern within csv file
<p>I have a CSV Excel file example:</p>

<pre><code>Receipt Name    Address      Date       Time    Items
25007   A      ABC pte ltd   4/7/2016   10:40   Cheese, Cookie, Pie
.
.
25008   B      CCC pte ltd   4/7/2016   12:40   Cheese, Cookie
</code></pre>

<p>What is a simple way to compare the 'Items' column and find out the most common pattern of the items people buy together and display the top combinations?
In this case the similar pattern is Cheese, Cookie.</p>

<p>Presuming you have comma separated values, you can use a <em>frozenset</em> of the pairings and use a <em>Counter</em> dict to get the counts:</p>

<pre><code>from collections import Counter
import csv

with open("test.csv") as f:
    next(f)
    counts = Counter(frozenset(tuple(row[-1].split(",")))
                     for row in csv.reader(f))
    print(counts.most_common())
</code></pre>

<p>If you want all combinations or pairs as per your updated input:</p>

<pre><code>from collections import Counter
from itertools import combinations

def combs(s):
    return  combinations(s.split(","), 2)

import csv
with open("test.csv") as f:
    next(f)
    counts = Counter(frozenset(t)
                     for row in csv.reader(f)
                            for t in combs(row[-1]))
    # counts -&gt; Counter({frozenset(['Cheese', 'Cookie']): 2, frozenset(['Cheese', 'Pie']): 1, frozenset(['Cookie', 'Pie']): 1})
    print(counts.most_common())
</code></pre>

<p>The order of the pairings is irrelevant as <code>frozenset([1,2])</code> and <code>frozenset([2,1])</code> would be considered the same.</p>

<p>If you want to consider all combinations from <code>2-n</code>:</p>

<pre><code>def combs(s):
    indiv_items = s.split(",")
    return chain.from_iterable(combinations(indiv_items, i) for i in range(2, len(indiv_items) + 1))


import csv

with open("test.csv") as f:
    next(f)
    counts = Counter(frozenset(t)
                     for row in csv.reader(f)
                         for t in combs(row[-1]))
    print(counts)
    print(counts.most_common())
</code></pre>

<p>Which for:</p>

<pre><code>Receipt,Name,Address,Date,Time,Items
25007,A,ABC,pte,ltd,4/7/2016,10:40,"Cheese,Cookie,Pie"
25008,B,CCC,pte,ltd,4/7/2016,12:40,"Cheese,Cookie"
25009,B,CCC,pte,ltd,4/7/2016,12:40,"Cookie,Cheese,pizza"
25010,B,CCC,pte,ltd,4/7/2016,12:40,"Pie,Cheese,pizza"
</code></pre>

<p>would give you:</p>

<pre><code>Counter({frozenset(['Cheese', 'Cookie']): 3, frozenset(['Cheese', 'pizza']): 2, frozenset(['Cheese', 'Pie']): 2, frozenset(['Cookie', 'Pie']): 1, frozenset(['Cheese', 'Cookie', 'Pie']): 1, frozenset(['Cookie', 'pizza']): 1, frozenset(['Pie', 'pizza']): 1, frozenset(['Cheese', 'Cookie', 'pizza']): 1, frozenset(['Cheese', 'Pie', 'pizza']): 1})
[(frozenset(['Cheese', 'Cookie']), 3), (frozenset(['Cheese', 'pizza']), 2), (frozenset(['Cheese', 'Pie']), 2), (frozenset(['Cookie', 'Pie']), 1), (frozenset(['Cheese', 'Cookie', 'Pie']), 1), (frozenset(['Cookie', 'pizza']), 1), (frozenset(['Pie', 'pizza']), 1), (frozenset(['Cheese', 'Cookie', 'pizza']), 1), (frozenset(['Cheese', 'Pie', 'pizza']), 1)]
</code></pre>

python regex csv