How get sum of values in another dataframe based column value in first dataframe?

Question

I have a data frame

df = pd.DataFrame({'Color': 'Red Red Blue'.split(), 'Value': [100, 150, 50]})
>>> df
  Color  Value
0   Red    100
1   Red    150
2  Blue     50

I have second data frame dfmain

dfmain = pd.DataFrame({'Color': ["Red","Blue","Yellow"]})
>>> dfmain
    Color
0     Red
1    Blue
2  Yellow

i want to get result data frame with sum of each colors my expected result is

>>> result
    Color  sum
0     Red  250
1    Blue   50
2  Yellow    0

Now i am using loop. its getting slow when run for large data set . I would like to get typical pandas(or numpy) solution for this

jezrael · Accepted Answer · 2016-11-15 06:22:09Z

2

You can use groupby with aggregating sum with reindex:

df = df.groupby('Color')['Value'].sum().reindex(dfmain.Color, fill_value=0).reset_index()
print (df)

    Color  Value
0     Red    250
1    Blue     50
2  Yellow      0

answered Nov 15, 2016 at 6:22

jezrael

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

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

Collectives™ on Stack Overflow

How get sum of values in another dataframe based column value in first dataframe?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related