Pandas dataframe lookup a value in different dataframe and assign a value

Question

I have 2 different dataframes. The first one looks like:

     joint  label     x    z      y    pt
0        1    NaN  50.4  0.0  -8.40    10
1        2  shell  52.2  0.0  -8.40    20
2        3  shell  54.0  0.0  -8.40    30
3        4  shell  55.8  0.0  -8.40    40
4        5  shell  57.6  0.0  -8.40    50

and my second dataframe looks like:

     member  joint1  joint2        joint1_pt        joint2_pt
0         1       1       2                0                0
1         2       2       3                0                0
2         3       3       4                0                0
3         4       4       5                0                0

I would like take use the pt value that corresponds on a specific jointe and use it on the second dataframe so it will look like the following:

     member  joint1  joint2        joint1_pt        joint2_pt
0         1       1       2                10              20
1         2       2       3                20              30
2         3       3       4                30              40
3         4       4       5                40              50

can you please help me with an example/idear on how should i approach this? Thank you in advance!!

You can use pd.DataFrame.to_dict (like this) and pd.Series.map (like this). — p-robot
– p-robot, Commented Feb 23, 2017 at 15:04

Community · Accepted Answer · 2017-05-23 12:25:47Z

8

You need map by dict created from Series with set_index and to_dict as pointed in P-robot in comments:

d = df1.set_index('joint')['pt'].to_dict()
#mapping by Series works, but a bit slowier
#d = df1.set_index('joint')['pt']
print (d)
{1: 10, 2: 20, 3: 30, 4: 40, 5: 50}

df2['joint1_pt'] = df2['joint1'].map(d)
df2['joint2_pt'] = df2['joint2'].map(d)
print (df2)
   member  joint1  joint2  joint1_pt  joint2_pt
0       1       1       2         10         20
1       2       2       3         20         30
2       3       3       4         30         40
3       4       4       5         40         50

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Feb 23, 2017 at 15:06

jezrael

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

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

4 Comments

Giorgos Synetos Over a year ago

i try to implement your code and i get NaN in the joint1_pt and joint2_pt columns

jezrael Over a year ago

Check dtypes, maybe need convert to str or int like df2['joint1_pt'] = df2['joint1'].astype(str).map(d) or df2['joint1_pt'] = df2['joint1'].astype(int).map(d) because need same.

jezrael Over a year ago

Same in dict and in columns joint1 and join2.

Giorgos Synetos Over a year ago

do you know why the first line of your code in my case returns string type for the keys of the dicts? as a second line i add this line d = {int(k):int(v) for k,v in d.items()} and it works now. but curious why in yours return integer type for your keys. Thank you very much!!

Shijo · Accepted Answer · 2017-02-23 15:19:49Z

4

you can use merge, after merging ,assign pt to joint1_pt & joint2_pt , finally drop unwanted columns.

df= pd.merge(df2,df1[['joint','pt']], right_on='joint',left_on='joint1',how='left')
df= pd.merge(df,df1[['joint','pt']], right_on='joint',left_on='joint2',how='left')
df[['joint1_pt','joint2_pt']] =df[['pt_x','pt_y']] 
df=df[['member','joint1','joint2','joint1_pt','joint2_pt']]
print df

Output

   member  joint1  joint2  joint1_pt  joint2_pt
0       1       1       2         10         20
1       2       2       3         20         30
2       3       3       4         30         40
3       4       4       5         40         50

edited Feb 23, 2017 at 15:19

answered Feb 23, 2017 at 15:02

Shijo

9,7913 gold badges23 silver badges31 bronze badges

3 Comments

Giorgos Synetos Over a year ago

the df1['joint','pt'] seems not to work. I get a KeyError: ('joint', 'pt'). Even when i try to print this it doesnt work. But when i try to print them seperately it works. Any idea?

Giorgos Synetos Over a year ago

thanks for your assistance! It works without giving and error but do you have any idea why it returns a NaN for all entries in the joint1_pt and joint2_pt columns?

Shijo Over a year ago

with the sample data, it works fine, It may be possible that 'joint' values are not matching to joint1 & joint2 in those cases it may be Nan

Collectives™ on Stack Overflow

Pandas dataframe lookup a value in different dataframe and assign a value

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related