How do I offset a dataframe with values in another dataframe?-CodePudding

I have two dataframes. One is the basevales (df) and the other is an offset (df2). How do I create a third dataframe that is the first dataframe offset by matching values (the ID) in the second dataframe?

This post doesn't seem to do the offset... Update only some values in a dataframe using another dataframe

import pandas as pd
  
# initialize list of lists
data = [['1092', 10.02], ['18723754', 15.76], ['28635', 147.87]]
df = pd.DataFrame(data, columns = ['ID', 'Price'])
  

offsets = [['1092', 100.00], ['28635', 1000.00], ['88273', 10.]]
df2 = pd.DataFrame(offsets, columns = ['ID', 'Offset'])


print (df)
print (df2)



>>> print (df)
         ID   Price
0      1092   10.02
1  18723754   15.76     # no offset to affect it
2     28635  147.87    

>>> print (df2)
      ID   Offset
0   1092   100.00
1  28635  1000.00
2  88273    10.00    # < no match

This is want I want to produce: The price has been offset by matching

         ID    Price
0      1092   110.02
1  18723754    15.76
2     28635  1147.87

I've also looked at Pandas Merging 101

I don't want to add columns to the dataframe, and I don;t want to just replace column values with values from another dataframe.

What I want is to add (sum) column values from the other dataframe to this dataframe, where the IDs match.

The closest I come is df_add=df.reindex_like(df2) df2 but the problem is that it sums all columns - even the ID column.

CodePudding user response：

Try this :

df['Price'] = pd.merge(df, df2, on=["ID"], how="left")[['Price','Offset']].sum(axis=1)