grouping related data in csv excel file
<p>this is a csv excel file</p>

<pre><code>   Receipt Name    Address      Date       Time    Total
    25007   A      ABC pte ltd   3/7/2016   10:40   12.30
    25008   A      ABC ptd ltd   3/7/2016   11.30   6.70
    25009   B      CCC ptd ltd   4/7/2016   07.35   23.40
    25010   A      ABC pte ltd   4/7/2016   12:40   9.90
</code></pre>

<p>how do i retrieve the dates and time and group them to respectively company A and B such that the output would be something like: (A, 3/7/2016, 10:40, 11.30, 4/7/2016 12:40), (B, 4/7/2016, 07:35)</p>

<p>My existing code is:</p>

<pre><code>datePattern = re.compile(r"(\d+/\d+/\d+)\s+(\d+:\d+)")  
dateDict =dict()    

for i, line in enumerate(open('sample_data.csv')):
    for match in re.finditer(datePattern,line):
        if match.group(1) in dateDict:
            dateDict[match.group(1)].append(match.group(2))
        else:
            dateDict[match.group(1)] = [match.group(2),]
</code></pre>

<p>However it only works for grouping date and time but now i want to include name as part of the grouping as well. *Using csv module would be preferred</p>

<p>Presuming your data actually looks like:</p>

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

<p>then it is pretty trivial to group:</p>

<pre><code>from collections import defaultdict
from csv import reader
with open("test.csv") as f:
    next(f) # skip header
    group_dict = defaultdict(list)
    for _, name, _, dte, time, _ in reader(f):
        group_dict[name].append((dte, time))

from  pprint import pprint as pp

pp(dict(group_dict))
</code></pre>

<p>which would give you:</p>

<pre><code>'A': [('4/7/2016', '10:40'), ('4/7/2016', '11:30'), ('4/7/2016', '12:40')],
 'B': [('4/7/2016', '07:35')]}
</code></pre>

<p>If you don't want the date repeating, then also group on that:</p>

<pre><code>with open("test.csv") as f:
    next(f) # skip header
    group_dict = defaultdict(list)
    for _, name, _, dte, time, _ in reader(f):
        group_dict[name, dte].append(time)

from  pprint import pprint as pp

pp(dict(group_dict))
</code></pre>

<p>Which would give you:</p>

<pre><code>{('A', '4/7/2016'): ['10:40', '11:30', '12:40'], ('B', '4/7/2016'): ['07:35']}
</code></pre>

python regex group