Updating a column in a dataframe with values from the same column another dataframe

Question

I have a larger dataframe in which there's a column that I would like to update with new values from a smaller dataframe only where row values for another column match. A left join has the correct rows but two columns of the values I want "updated" (left and right). I have tried a left merge with drops but of course that wipes out the values in the column I'm trying to update wherever the matching column doesn't match. I've also tried variations of this:

tips.loc[tips['tip'] < 2, 'tip'] = 2

but I get an error about series lengths needing to match because the table I'm updating from is smaller. I looked at DataFrame.combine_first() also but the docs don't show it takes any options and doesn't produce what I'm looking for either.

Here's an example:

df = pd.DataFrame({'CAT': ['A', 'B', 'C', 'D', 'E'], 'Value': [12, 34, np.NaN, 43, 21]})
  CAT  Value
0   A     12
1   B     34
2   C    NaN
3   D     43
4   E     21

df2 = pd.DataFrame({'CAT': ['C', 'F', 'G'], 'Value': [22, 99, 44]})
  CAT  Value
0   C     22
1   F     99
2   G     44

the result should look like this:

 CAT  Value
0   A     12
1   B     34
2   C     22
3   D     43
4   E     21

Though the left frame in this example has NaN being updated, I'm not looking exclusively for this case. I want to overwrite whatever value is there. Any help? Thx.

Vishnu Kunchur · Accepted Answer · 2018-09-30 21:07:27Z

2

It looks like you want to replace Value from df with the corresponding Value in df2, if the value exists. I.e., assuming you had a CAT F that had a corresponding value of 36 in df, you would want that to be replaced by 99 (from df2).

Using merge:

df= df.merge(df2, on = 'CAT', how = 'left')
df['Value'] = df[['Value_x', 'Value_y']].apply(lambda x: np.where(df['Value_y'].isna(), df['Value_x'], df['Value_y'])).drop(columns = ['Value_y'])
df.drop(columns = ['Value_x', 'Value_y'])

Output:

  CAT  Value
0   A   12.0
1   B   34.0
2   C   22.0
3   D   43.0
4   E   21.0
5   F   99.0

edited Sep 30, 2018 at 21:07

answered Sep 30, 2018 at 20:18

Vishnu Kunchur

1,73611 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Updating a column in a dataframe with values from the same column another 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