Title: adding data from different rows in a csv belonging to a common variable
Tags: python;csv;pandas;design-patterns;pandas-groupby
Question: this is my csv excel file information:
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
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.
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))
Comment: the code i have is grouping different time to the same merchant and date. what i wish to do is adding the total price belonging to the same date and merchant then grouping it to its merchant and date.

Comment: think about moving to normal databases

Comment: Are you grouping by merchant and date, or merchant, date, AND time? In your example case, you are doing the former.


Here is another answer: import pandas as pd
df = pd.read_csv('assignment_info.csv')
df = df.groupby(['merchant', 'Date', 'Time']).sum().reset_index()
Comment for this answer: hoping to find an alternative method without pandas module thanks!

Here is another answer: As the other answer points out, pandas is an excellent library for this kind of data manipulation. My answer won't use pandas though.
A few issues:
In your problem description, you state that you want to group by three columns, but in your example cases you are only grouping by two. Since the former makes more sense, I am only grouping by name and date
You are looping and sorting each value, but for the life of me I can't figure out why.
You declare the default type of the defaultdict a list and then extend with a string, which ends up giving you a (sorted!) list of characters. You don't really want to do this.
Your example uses the syntax of a set: { [a,b,c], [d,e,f] } but the syntax of a dict makes more sense: { (a, b): c, }. I have changed the output to the latter.
Here is a working example:
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)
group_dict is now:
{('A', '3/7/2016'): 19.0, ('A', '4/7/2016'): 9.9, ('B', '4/7/2016'): 23.4}
I removed extra columns which aren't in your example: here's the file I worked with:
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
Comment for this answer: everything is fine, just that my output has to incude the time of sales also for each group of merchant and date such as {('A', '3/7/2016'): 19.0, 10:30,11:30} pls advice

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2016:00:10/13/16', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<python><csv><pandas><design-patterns><pandas-groupby>', 'Title': 'adding data from different rows in a csv belonging to a common variable', 'ViewCount': 33, 'id': 40028405, 'user_AboutMe': None, 'user_AccountId': 9303902.0, 'user_DisplayName': 'Darryl Dan', 'user_Reputation': 75.0, 'user_Views': 13.0, 'user_id': 6908374.0}}