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>Suppose after processing the CSV file you find the list of items from the CSV file to be:</p>

<pre><code>&gt;&gt;&gt; items=['Cheese,Cookie,Pie', 'Cheese,Cookie,Pie', 'Cake,Cookie,Cheese', 
... 'Cheese,Mousetrap,Pie', 'Cheese,Jam','Cheese','Cookie,Cheese,Mousetrap']
</code></pre>

<p>First determine all possible pairs:</p>

<pre><code>&gt;&gt;&gt; from itertools import combinations
&gt;&gt;&gt; all_pairs={frozenset(t) for e in items for t in combinations(e.split(','),2)}
</code></pre>

<p>Then you can do:</p>

<pre><code>from collections import Counter
pair_counts=Counter()
for s in items:
    for pair in {frozenset(t) for t in combinations(s.split(','), 2)}:
        pair_counts.update({tuple(pair):1})

&gt;&gt;&gt; pair_counts
Counter({('Cheese', 'Cookie'): 4, ('Cheese', 'Pie'): 3, ('Cookie', 'Pie'): 2, ('Cheese', 'Mousetrap'): 2, ('Cookie', 'Mousetrap'): 1, ('Cheese', 'Jam'): 1, ('Mousetrap', 'Pie'): 1, ('Cake', 'Cheese'): 1, ('Cake', 'Cookie'): 1})
</code></pre>

<p>Which can be extended to a more general case:</p>

<pre><code>max_n=max(len(e.split(',')) for e in items)
for n in range(max_n, 1, -1):
    all_groups={frozenset(t) for e in items for t in combinations(e.split(','),n)}
    group_counts=Counter()
    for s in items:
        for group in {frozenset(t) for t in combinations(s.split(','), n)}:
            group_counts.update({tuple(group):1})      
    print 'group length: {}, most_common: {}'.format(n, group_counts.most_common())     
</code></pre>

<p>Prints:</p>

<pre><code>group length: 3, most_common: [(('Cheese', 'Cookie', 'Pie'), 2), (('Cheese', 'Mousetrap', 'Pie'), 1), (('Cheese', 'Cookie', 'Mousetrap'), 1), (('Cake', 'Cheese', 'Cookie'), 1)]
group length: 2, most_common: [(('Cheese', 'Cookie'), 4), (('Cheese', 'Pie'), 3), (('Cookie', 'Pie'), 2), (('Cheese', 'Mousetrap'), 2), (('Cookie', 'Mousetrap'), 1), (('Cheese', 'Jam'), 1), (('Mousetrap', 'Pie'), 1), (('Cake', 'Cheese'), 1), (('Cake', 'Cookie'), 1)]
</code></pre>

python regex csv