Adding column values as long as a value in other column matches

Question

How do I add column values as long as a value in the other column is the same?

e.g. from the following list:

Time   Value
10       a
20       b
10       c
10       d
20       f

I want to obtain the following result:

Time      Value
10       (a+c+d)
20        (b+f)

Welcome to StackOverflow! Please have a look at [How to ask a good question](https://stackoverflow.com/help/how-to-ask). — ndrwnaguib, Jan 01 '18 at 18:32
What is this indeed? How does your data look like in python? — Anton vBR, Jan 01 '18 at 18:49
Joe Iddon , Anton vBR am importing from a text file. I have seven columns of string data, I need 5 columns to group by being summed according to Time occurrence. — Musa Ndiaye, Jan 03 '18 at 03:40

score 1 · Answer 1 · answered Jan 01 '18 at 18:55

1

Here is a pandas solution if you have a dataframe:

import pandas as pd

data ='''\
Time   Value
10       a
20       b
10       c
10       d
20       f'''

df = pd.read_csv(pd.compat.StringIO(data), sep='\s+')

newdf = df.groupby('Time')['Value']\
        .apply(lambda x: '({})'.format('+'.join(x)))\
        .reset_index()

print(newdf)

Returns:

   Time    Value
0    10  (a+c+d)
1    20    (b+f)

answered Jan 01 '18 at 18:55

Anton vBR

18,287
5
40
46

I need the sum of a,c and d not a concatenation – Musa Ndiaye Jan 01 '18 at 21:06
@MusaNdiaye In that case it is `newdf = df.groupby('Time')['Value'].apply(sum).reset_index()`. This however smells like a dupe, a pretty basic operation. Joe Iddon has the answer too. – Anton vBR Jan 01 '18 at 21:30

score 0 · Answer 2 · answered Jan 01 '18 at 18:48

0

from collections import defaultdict

dd = defaultdict(list)
L = [10, 'a', 20, 'b', 10, 'c', 10, 'd', 20, 'f']
x = 0
while x < len(L):
  dd[L[x]].append(L[x+1]);
  x = x+ 2

for key, val in dd.iteritems():
  print key, reduce(lambda a, b: a+b, val)

print dd

answered Jan 01 '18 at 18:48

what is **L** ? we never have something like this in real programs – Arash Hatami Jan 01 '18 at 18:56

score 0 · Answer 3 · answered Jan 01 '18 at 18:50

0

data = {
    'a': 10,
    'b': 20,
    'c': 10,
    'd': 10,
    'f': 20
}

v = {}

for key, value in sorted(data.iteritems()):
    v.setdefault(value, []).append(key)

print v

And the resault :

{10: ['a', 'c', 'd'], 20: ['b', 'f']}

answered Jan 01 '18 at 18:50

Arash Hatami

5,297
5
39
59

score 0 · Accepted Answer · answered Jan 01 '18 at 19:08

You can use groupby, then select the Value elements (so you don't sum the Times), then finally apply sum.

>>> d = {'Time': [10,20,10,10,20], 'Value': ['a', 'b', 'c', 'd', 'f']}
>>> df = pd.DataFrame(d)
>>> df
   Time Value
0    10     a
1    20     b
2    10     c
3    10     d
4    20     f
>>> df.groupby(['Time'])['Value'].apply(sum).reset_index()
   Time Value
0    10   acd
1    20    bf

Adding column values as long as a value in other column matches

4 Answers4