Increasing a value during merge in pandas

user_8913983 0
Viewed 45

I have 2 dataframes

df1

product_id      value       name
abc             10          a
def             20          b
ggg             10          c

df2 Which I get after using df2.groupby(['prod_id'])['code'].count().reset_index()

prod_id         code
abc             10
def             20
ggg             10
ooo             5
hhh             1

I want to merge values from df2 to df1 left on product_id, right on prod_id.

To get:

product_id      value       name
abc             20          a
def             40          b
ggg             20          c

I tried:

pd.merge(df1, df2.groupby(['prod_id'])['code'].count().reset_index(), 
         left_on='product_id', right_on='prod_id', how='left')

Which returns df1 with 2 additional columns prod_id and code with the code column holding the amount by which I would like to increase value in df1. Now I can just add those 2 columns but I would like to avoid that.

pandas list numpy dictionary python
2 Answers

Here’s one alternative:

df1['value'] = df1.product_id.map(dict(df2.values)).fillna(0).add(df1.value)

Complete example:

df1 = pd.DataFrame({'product_id': {0: 'abc', 1: 'def', 2: 'ggg'},
                    'value': {0: 10, 1: 20, 2: 10},
                    'name': {0: 'a', 1: 'b', 2: 'c'}})

df2 = pd.DataFrame({'prod_id': {0: 'abc', 1: 'def', 2: 'ggg', 3: 'ooo', 4: 'hhh'},
                    'code': {0: 10, 1: 20, 2: 10, 3: 5, 4: 1}})

df1['value'] = df1.product_id.map(dict(df2.values)).fillna(0).add(df1.value)

OUTPUT:

  product_id  value name
0        abc     20    a
1        def     40    b
2        ggg     20    c
user_154380330

you could use reindex on df2 with the order of df1 product_id, after the groupby.count (without the reset_index). like

df1['value'] += (
    df2.groupby(['prod_id'])
    ['code'].count()
    .reindex(df1['product_id'], fill_value=0)
    .to_numpy()
)
user_92747320
Related