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>If you don't want to use Pandas, this is a possible solution. It's not the most elegant since your csv format is relatively clunky to parse. If you can change the format to use a non-whitespace field separator, using a proper csv parsing library (like <code>pandas</code> or Python's built-in <code>csv</code> module) would be preferable.</p>

<pre><code>import re

datePattern = re.compile(r"(\d+/\d+/\d+)\s+(\d+[:.]\d+)")
companyPattern = re.compile(r"^\s+\d+\s+(\w+)")
companyDict = {}

for i, line in enumerate(open('sample_data.csv')):
    # skip csv header
    if i == 0:
        continue

    timestampMatch = datePattern.search(line)
    companyMatch   = companyPattern.search(line)

    # filter out any malformed lines which don't match
    if timestampMatch is None or companyMatch is None:
        continue

    date = timestampMatch.group(1)
    time = timestampMatch.group(2)
    company = companyMatch.group(1)

    companyDict.setdefault(company, []).append("{} {}".format(date, time))
</code></pre>

<p>Note that the time field is inconsistent as to whether it uses <code>.</code> or <code>:</code> for the hour/minute delimiter so I've taken this into account.</p>

<p>Running this on your sample data results in the following value for <code>companyDict</code>:</p>

<pre><code>{'A': ['3/7/2016 10:40', '3/7/2016 11.30', '4/7/2016 12:40'], 'B': ['4/7/2016 07.35']} 
</code></pre>

python regex group