Copy a column value from another dataframe based on a condition

user_2751530 0
Viewed 9093

Let us say I have two dataframes: df1 and df2. Assume the following initial values.

df1=pd.DataFrame({'ID':['ASX-112','YTR-789','ASX-124','UYT-908','TYE=456','ERW-234','UUI-675','GHV-805','NMB-653','WSX-123'],
'Costperlb':[4515,5856,3313,9909,8980,9088,6765,3456,9012,1237]})
df2=df1[df1['Costperlb']>4560]

As you can see, df2 is a proper subset of df1 (it was created from df1 by imposing a condition on selection of rows).

I added a column to df2, which contains certain values based on a calculation. Let us call this df2['grade'].

df2['grade']=[1,4,3,5,1,1]

df1 and df2 contain one column named 'ID' which is guaranteed to be unique in each dataframe.

I want to:

  1. Create a new column in df1 and initialize it to 0. Easy. df1['grade']=0.
  2. Copy df2['grade'] values over to df1['grade'], ensuring that df1['ID']=df2['ID'] for each such copy.

The result should be the grade values for the corresponding IDs copied over.

Step 2 is what is perplexing me a bit. A naive df1['grade']=df2['grade'].values does not work obviously as the lengths of the two dataframes is different.

Now, if I think hard enough, I could possibly come up with a monstrosity like:

df1['grade'].loc[(df1['ID'].isin(df2)) & ...] but I am uncomfortable with doing that.

I am a newbie with python, and furthermore, the indices of df1 are being used elsewhere after this assignment, and I do not want drop indices, reset indices as some of the solutions are suggested in some of the search results I found.

I just want to find out rows in df1 where the 'ID' row matches the 'ID' row in df2, and then copy the 'grade' column value in that specific row over. How do I do this?

pandas dataframe list
1 Answers

Your code:

df1=pd.DataFrame({'ID':['ASX-112','YTR-789','ASX-124','UYT-908','TYE=456','ERW-234','UUI-675','GHV-805','NMB-653','WSX-123'],
'Costperlb':[4515,5856,3313,9909,8980,9088,6765,3456,9012,1237]})
df2=df1[df1['Costperlb']>4560]
df2['grade']=[1,4,3,5,1,1]

You can use merge with "left". In this way the indexing of df1 is preserved:

new_df = df1.merge(df2[["ID","grade"]], on="ID", how="left")
new_df["grade"] = new_df["grade"].fillna(0)
new_df

Output:

    ID   Costperlb grade
0   ASX-112 4515    0.0
1   YTR-789 5856    1.0
2   ASX-124 3313    0.0
3   UYT-908 9909    4.0
4   TYE=456 8980    3.0
5   ERW-234 9088    5.0
6   UUI-675 6765    1.0
7   GHV-805 3456    0.0
8   NMB-653 9012    1.0
9   WSX-123 1237    0.0

Here I called the merged dataframe new_df, but you can simply change it to df1.

EDIT

If instead of 0 you want to replace the NaN with a string, try this:

new_df = df1.merge(df2[["ID","grade"]], on="ID", how="left")
new_df["grade"] = new_df["grade"].fillna("No transaction possible")
new_df

Output:

    ID    Costperlb       grade
0   ASX-112 4515    No transaction possible
1   YTR-789 5856    1
2   ASX-124 3313    No transaction possible
3   UYT-908 9909    4
4   TYE=456 8980    3
5   ERW-234 9088    5
6   UUI-675 6765    1
7   GHV-805 3456    No transaction possible
8   NMB-653 9012    1
9   WSX-123 1237    No transaction possible
user_133280100
Related