3

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!!

Giorgos Synetos
  • 457
  • 3
  • 6
  • 13
  • You can use `pd.DataFrame.to_dict` (like [this](http://stackoverflow.com/questions/18012505/python-pandas-dataframe-columns-convert-to-dict-key-and-value)) and `pd.Series.map` (like [this](http://stackoverflow.com/questions/35561517/mapping-values-into-a-new-dataframe-column)). – p-robot Feb 23 '17 at 15:04

2 Answers2

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
Community
  • 1
  • 1
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • i try to implement your code and i get NaN in the joint1_pt and joint2_pt columns – Giorgos Synetos Feb 23 '17 at 15:31
  • 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 Feb 23 '17 at 16:15
  • Same in dict and in columns joint1 and join2. – jezrael Feb 23 '17 at 16:18
  • 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!! – Giorgos Synetos Feb 23 '17 at 16:33
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
Shijo
  • 9,313
  • 3
  • 19
  • 31
  • 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 Feb 23 '17 at 15:41
  • 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? – Giorgos Synetos Feb 23 '17 at 15:55
  • 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 – Shijo Feb 23 '17 at 15:58