CODEWITHSUNDEEP
pythonpython-2.7pandas
user2242044
user2242044

Reputation: 9233

Adding Grouped Dataframes

I have two dataframes. I like the to add add the values in the columns together if the grouping is the same. Doing this with a simple addition works great as long as both group values are in each table. If they are not, it returns nan. I am assuming because you can't add nan and an int, but not sure how to work around this.

import pandas as pd
df = pd.DataFrame(data=[['A', 4],
                        ['A', 1],
                        ['B', 1],
                        ['B', 5]],
                   columns=['Person', 'Days'])

df1 = pd.DataFrame(data=[['A', 5],
                        ['A', 0],
                        ['C', 3],
                        ['C', 5]],
                   columns=['Person', 'Days'])

df['Days'] = df['Days'] <= 3
df1['Days'] = df1['Days'] <= 3

df = df.groupby('Person').agg(['count'])
df1 = df1.groupby('Person').agg(['count'])

print df + df1

Actual Output:

        Days
       count
Person      
A          4
B        NaN
C        NaN

Desired Output:

        Days
       count
Person      
A          4
B          2
C          2

Upvotes: 2

Views: 50

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210882

UPDATE:

In [40]: funcs = ['count','sum']

In [41]: df.groupby('Person').agg(funcs).add(df1.groupby('Person').agg(funcs), fill_value=0)
Out[41]:
        Days
       count sum
Person
A        4.0   2
B        2.0   1
C        2.0   1

Old answer:

In [14]: df.groupby('Person').size().to_frame('count').add(
   ....:  df1.groupby('Person').size().to_frame('count'), fill_value=0)
Out[14]:
        count
Person
A         4.0
B         2.0
C         2.0

PS I worked with original DFs - i didn't execute this code :

df = df.groupby('Person').agg(['count'])
df1 = df1.groupby('Person').agg(['count'])

Upvotes: 2

Related Questions