Conditional Sums based on another DataFrame column value

Question

I'm trying to sum all the value where date1 matches date2 for each row in my dataframe, which is similar to this question.

id        value       date1       date2   sum
A         150      4/8/2014    3/8/2014   nan
B         100      5/8/2014    2/8/2014   nan
B         200      7/8/2014    5/8/2014   100
A         200      4/8/2014    3/8/2014   nan
A         300      6/8/2014    4/8/2014   350

I've tried the following but keep getting 'Lengths must match to compare' error.

grp = df.groupby('id')
df['sum'] = grp.apply(lambda x: x[x['date1'] == df['date2'].values]['value'].sum())

Would appreciate any advice!

Problem is with: x['date1'] == df['date2'], Series have different lengths. — jezrael
– jezrael, Commented Dec 16, 2015 at 8:29
IIUC doesn't the following give you what you want: df1 = df.set_index('id') df1.loc[df1['date1'].isin(df1['date2']),'value'].sum(level=0)? — EdChum
– EdChum, Commented Dec 16, 2015 at 9:04

jezrael · Accepted Answer · 2015-12-17 08:35:30Z

1

You can apply function to groupby where use another apply with replace 0 to NaN:

print df

#  id  value      date1      date2  sum
#0  A    150 2014-04-08 2014-03-08  NaN
#1  B    100 2014-05-08 2014-02-08  NaN
#2  B    200 2014-07-08 2014-05-08  100
#3  A    200 2014-04-08 2014-03-08  NaN
#4  A    300 2014-06-08 2014-04-08  350


def f(x):
    x['sum1'] = x.date2.apply(lambda y:  x[x.date1 == y].value.sum()).replace(0, np.nan)
    return x

df = df.groupby('id').apply(f)

print df

#  id  value      date1      date2  sum  sum1
#0  A    150 2014-04-08 2014-03-08  NaN   NaN
#1  B    100 2014-05-08 2014-02-08  NaN   NaN
#2  B    200 2014-07-08 2014-05-08  100   100
#3  A    200 2014-04-08 2014-03-08  NaN   NaN
#4  A    300 2014-06-08 2014-04-08  350   350

edited Dec 17, 2015 at 8:35

answered Dec 16, 2015 at 9:09

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

3 Comments

tbk Over a year ago

thanks, but actually I'm trying to achieve the sum column in my example. sorry if that isn't clear.

tbk Over a year ago

thanks, but this ignores the id, hence not exactly what I need. but thanks for the prompt responses!

tbk Over a year ago

it works! thanks jezrael, appreciate your helpfulness!

Ioana Veronica Chelu · Accepted Answer · 2015-12-16 09:51:14Z

0

You can use this apply function:

def checkSum(record):
    date2 = record
    sum = df[df["date1"] == date2]["value"].sum()
    if sum == 0:
        return float('nan')
    else:
        return sum

df['sum2'] = df["date2"].apply(checkSum)

answered Dec 16, 2015 at 9:51

Ioana Veronica Chelu

564 bronze badges

1 Comment

tbk Over a year ago

thanks, but this ignores the id field too. I tried df['sum2'] = grp["date2"].apply(checkSum) but got the Series lengths must match to compare error.

Collectives™ on Stack Overflow

Conditional Sums based on another DataFrame column value

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related