I am trying to merge some data and have been unable to get the results I've been looking for. I have two data frames with two columns each: CID and Notional. DF1 has every CID and DF2 has just some of the CIDs. I want to take DF2's data and merge it with DF1 so that IF DF2's data exists it will overwrite DF1, and if not DF1 will retain it's data.
I have tried using pd.merge and I end up with a DataFrame that has columns CID, Notional_X, Notional_Y; I have tried 'update' but it just replaces all old DataFrame data.
Here's an example of what I'm looking for:
#Example of Data (couldn't find a better way to explain this)
df1 = pd.DataFrame({'CID':[1,25,100], 'Notional': [1000, 2500, 5500]})
df2 = pd.DataFrame({'CID':[25], 'Notional': [0]})
the output would return a DataFrame that looks like this:
pd.DataFrame({'CID': [1,25,100], 'Notional': [1000,0,5500]})
(not that the merge reduced CID 25 to 0 which is found in df2 without changing anything else)
The documentation suggests that 'merge' should accomplish it but it just... doesn't.
test = df1.merge(df1, df2, how = 'left', on = 'CID')
This seems to merge the dataframes without merging the data (it just appends a column on the end)
Any help would be greatly appreciated. Thank you.
In your case, when both the left and right tables of the join also have the same data column ("Notional") that is not part of the merge key ("CID"), there is no method in the merge function to decide which value to use for Notional.
You can add one more line of code though to take care of this.
import pandas as pd
import numpy as np
# make the data
df1 = pd.DataFrame({'CID':[1,25,100], 'Notional': [1000, 2500, 5500]})
df2 = pd.DataFrame({'CID':[25], 'Notional': [0]})
# merge the data
test = df1.merge(df2, how='left', on='CID')
# If Notional from df2 was not missing, then use it, else use df1's Notional
test['Notional'] = np.where(test['Notional_y'].isna(), test['Notional_x'], test['Notional_y'])
You could then drop Notional_x and Notional_y from the dataframe, leaving your newly created Notional.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments