how to merge data in python

Question

I just learn python for not long. And I had try my best to represent my data looks better just like showing before. Now I have some tuple data type which look like this:

('John', '5', 'Coke')
('Mary', '1', 'Pie')
('Jack', '3', 'Milk')
('Mary', '2', 'Water') 
('John', '3', 'Coke')

And I wanna count how many items that each one had bought.

Assume that the different name is different person.

So how could I do in order to get some information like this below:

 John: 8 Coke 
 Mary: 1 Pie 
 Mary: 2 Water 
 Jack: 3 Milk

I have no idea how could I do now. I can't come up with any method even the stupid one.

That tuples are a tuple of tuples? Or there are different tuples? — Avión
– Avión, Commented Jan 15, 2016 at 8:34

bereal · Accepted Answer · 2016-01-17 16:43:07Z

8

I'd suggest using name and drink as a key for collections.Counter:

from collections import Counter
count = Counter()
for name, amount, drink in tuples:
    key = name, drink
    count.update({key: int(amount)})  # increment the value

# represent the aggregated data
for (name, drink), amount in count.items():
    print('{}: {} {}'.format(name, amount, drink))

Update I made some simple measurements, and figured out that

count[name, drink] += value

is not only more readable, but much faster than calling update, which should not be a surprise. Moreover, defaultdict(int) is even faster (about twice) than that (presumably, because Counter performs some ordering additionally.)

edited Jan 17, 2016 at 16:43

answered Jan 15, 2016 at 8:44

bereal

34.7k8 gold badges65 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Avión Over a year ago

is tuples a list of tuples? If it's, this doesnt work (2.7))

Avión Over a year ago

count.update({key: amount}) -> TypeError: unsupported operand type(s) for +: 'int' and 'str'. You are using the numbers as int, and the OP's are strings.

bereal Over a year ago

@Borja I see, that's because amount is string in the input. Fixed, thanks.

WenT Over a year ago

Thanks guys. It works and it's really helpful for me. :)

bereal Over a year ago

@WenT I slightly improved the answer, count[name, drink] += value is more readable.

|

dhke · Accepted Answer · 2016-01-15 09:05:34Z

Re-arranging the order of your data might help:

John: 8 Coke 
Mary: 1 Pie 
Mary: 2 Water 
Jack: 3 Milk

might be more insightful, when written as

(John, Coke) : 8
(Mary, Pie)  : 1
(Mary, Water): 2
(Jack, Milk) : 3

If you know SQL, this is more or less equivalent to groupby(name, dish) together with sum(count).

So, in Python, you can create a dictionary for that pair:

data = [
  ('John', '5', 'Coke'),
  ('Mary', '1', 'Pie'),
  ('Jack', '3', 'Milk'),
  ('Mary', '2', 'Water'), 
  ('John', '3', 'Coke'),
]

orders = {}
for name, count, dish in data:
    if (name, dish) in orders:
        orders[(name, dish)] += int(count)
    else:
        # first entry
        orders[(name, dish)] = int(count)

Even more pythonic, use collections.defaultdict:

orders = defaultdict(int)
for name, count, dish in data:
    orders[(name, dish)] += int(count)

or collections.Counter as noted by @bereal.

Format data as you like.

Luis González · Accepted Answer · 2016-01-15 08:46:22Z

1

Assuming you have a list of tuples

tuples = [('John', '5', 'Coke'),
('Mary', '1', 'Pie'),
('Jack', '3', 'Milk'),
('Mary', '2', 'Water'), 
('John', '3', 'Coke')]

memory = {}

# First, we calculate the amount for each pair
for tuple in tuples:

    # I define a generated key through the names. For example John-Cake, Mary-Pie, Jack-Milk,...
    key = (tuple[0],tuple[2])

    number = int(tuple[1])
    if key in memory:
        memory[key] += number
    else:
        memory[key] = number

# After, we format the information
list = []
for key in memory:
    list.append((key[0],memory[key],key[1]))

edited Jan 15, 2016 at 8:46

answered Jan 15, 2016 at 8:40

Luis González

3,6591 gold badge29 silver badges48 bronze badges

1 Comment

dhke Over a year ago

Tuples are hashtable, i.e. ('John', 'Coke') is a valid dictionary key. No need for the string magic.

Collectives™ on Stack Overflow

how to merge data in python

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related