How to replace values in a dataframe with values in another dataframe based on certain condition?

Question

I want to replace the values of columns "q1_body" and "q2_body" of dataframe "result" with the values of "body" of the same id in dataframe "df", and the code is like:

def replace_body(x):
    id1 = result.loc[x].qid1
    result.loc[x].q1_body = df[df["qid"]==id1]["body"]
    id2 = result.loc[x].qid2
    result.loc[x].q2_body = df[df["qid"]==id2]["body"]

result.index.map(lambda x: replace_body(x))

When I run the code I got the following reminder in my ipython console and the program just stuck here:

//anaconda/lib/python3.6/site-packages/pandas/core/generic.py:3110:
SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
self[name] = value

Hope anyone can tell me what is wrong here.

Suppose the two dataframe are:

result:

qid1 q1_body qid2 q2_body
 1a    abc    2a    bcd
 1a    abc    3a    cde
 2a    bcd    3a    cde

df:

qid body
1a sfgaks
2a shdfjk
3a adjkwf

And the expected output is like:

result:

qid1 q1_body qid2 q2_body
 1a  sfgaks   2a  shdfjk
 1a  sfgaks   3a  adjkwf
 2a  shdfjk   3a  adjkwf

jezrael · Accepted Answer · 2017-07-27 04:59:58Z

2

You need map by Series created by set_index:

s = df.set_index('qid')['body']
result['q1_body'] = result['qid1'].map(s)
result['q2_body'] = result['qid2'].map(s)
print (result)
  qid1 q1_body qid2 q2_body
0   1a  sfgaks   2a  shdfjk
1   1a  sfgaks   3a  adjkwf
2   2a  shdfjk   3a  adjkwf

answered Jul 27, 2017 at 4:59

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

9 Comments

Kartik Over a year ago

Why map? loc would do, as I have written in my answer. Only thing, I forgot to set the index on df... Oops! :-P

jezrael Over a year ago

map is better and more obvious way for mapping data.

Kartik Over a year ago

Slower than .loc. Let's do a time test, shall we?

Kartik Over a year ago

Need to edit it. I forgot so many things about Pandas. Not been using it, these days.

jezrael Over a year ago

Yes, and dont forget add output to your answer.

|

Kartik · Accepted Answer · 2017-07-27 05:28:50Z

1

Here:

# Set index and get body as a series
s = df.set_index(qid)['body']
result['q1_body'] = s.loc[result['qid1']].values
result['q2_body'] = s.loc[result['qid2']].values

Result:

  qid1 q1_body qid2 q2_body
0   1a  sfgaks   2a  shdfjk
1   1a  sfgaks   3a  adjkwf
2   2a  shdfjk   3a  adjkwf

Timing (10k rows, using auto-generated Lorem):

My method

@Jezareal's method

edited Jul 27, 2017 at 5:28

answered Jul 27, 2017 at 4:41

Kartik

8,73345 silver badges78 bronze badges

1 Comment

Sara_Hsu Over a year ago

Thank you. I tried your solution but got this error: "KeyError: 'None of [0 1\n1 1\n2 2\nName: qid1, dtype: object] are in the [index]'"

Collectives™ on Stack Overflow

How to replace values in a dataframe with values in another dataframe based on certain condition?

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related