adding data from different rows in a csv belonging to a common variable
<p>this is my csv excel file information:</p>

<pre><code>  Receipt merchant    Address      Date    Time    Total price
    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 is it possible to add the 'Total Price' of each line together only if they belong to the same 'merchant', 'date' and 'time' then grouping them together in a list or dict, example: {['A','3/7/2016', '19.0'], ['A',4/7/2016, '9.90'],..}
My previous code does what i wanted except that i lack the code to count the total price for each same date and merchant. </p>

<pre><code>from collections import defaultdict
from csv import reader
with open("assignment_info.csv") as f:
    next(f) 
    group_dict = defaultdict(list)
    for rec, name, _, dte, time, price in reader(f):
        group_dict[name, dte].extend(time)
    for v in group_dict.values():v.sort()    

from  pprint import pprint as pp
print 'Sales tracker:'
pp(dict(group_dict))
</code></pre>

<p>As the other answer points out, <code>pandas</code> is an excellent library for this kind of data manipulation. My answer won't use <code>pandas</code> though.</p>

<p>A few issues:</p>

<ul>
<li>In your problem description, you state that you want to group by <em>three</em> columns, but in your example cases you are only grouping by two. Since the former makes more sense, I am only grouping by <code>name</code> and <code>date</code></li>
<li>You are looping and sorting each value, but for the life of me I can't figure out why.</li>
<li>You declare the default type of the <code>defaultdict</code> a list and then <code>extend</code> with a string, which ends up giving you a (sorted!) list of characters. You don't really want to do this.</li>
<li>Your example uses the syntax of a set: <code>{ [a,b,c], [d,e,f] }</code> but the syntax of a dict makes more sense: <code>{ (a, b): c, }</code>. I have changed the output to the latter.</li>
</ul>

<p>Here is a working example:</p>

<pre><code>from collections import defaultdict
from csv import reader

with open("assignment_info.csv") as f:
    next(f) 
    group_dict = defaultdict(float)
    for rec, name, _, dte, time, price in reader(f):
        group_dict[name, dte] += float(price)
</code></pre>

<p><code>group_dict</code> is now:</p>

<pre><code>{('A', '3/7/2016'): 19.0, ('A', '4/7/2016'): 9.9, ('B', '4/7/2016'): 23.4}
</code></pre>

<p>I removed extra columns which aren't in your example: here's the file I worked with:</p>

<pre>Receipt,merchant,Address,Date,Time,Total price
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</pre>

python csv pandas design-patterns group