Map dataframe with multiple columns as keys pandas

Question

>>> p1.head()           
   StreamId            Timestamp    SeqNum
0         3  1490250116391063414  1158
1         3  1490250116391348339  3600
2         3  1490250116391542829  3600
3         3  1490250116391577184  1437
4         3  1490250116392819426  1389


>>> oss.head()
   OrderID    Symbol  Stream     SeqNo
0  5000000  AXBANK       3      1158
1  5000001  AXBANK       6      1733
2  5000002  AXBANK       6      1244
3  5000003  AXBANK       6      1388
4  5000004  AXBANK       3      1389

How can this be merged using 2 attributes as key (SeqNum and StreamId)

>>> merge
   OrderID    Symbol  Stream     SeqNo    Timestamp
0  5000000  AXBANK       3      1158      1490250116391063414
1  5000001  AXBANK       6      1733      NaN
2  5000002  AXBANK       6      1244      NaN
3  5000003  AXBANK       6      1388      NaN
4  5000004  AXBANK       3      1389      1490250116392819426

I tried using

oss['Time1'] = oss['SeqNo'].map.((p1.set_index('SeqNum')['Timestamp']))

But I need to include both (SeqNum-SeqNo & Stream-StreamId)as keys I know this can be easy if I rename column names same in both dataframes and use merge but I want to avoid that. I should rather use something generic like (take THIS dataframe, map THESE columns to THOSE columns IN ANOTHER DATAFRAME and fetch required coulmns)

piRSquared · Accepted Answer · 2017-03-28 08:20:36Z

5

Using join

oss.join(p1.set_index(['StreamId', 'SeqNum']), on=['Stream', 'SeqNo'])

   OrderID  Symbol  Stream  SeqNo     Timestamp
0  5000000  AXBANK       3   1158  1.490250e+18
1  5000001  AXBANK       6   1733           NaN
2  5000002  AXBANK       6   1244           NaN
3  5000003  AXBANK       6   1388           NaN
4  5000004  AXBANK       3   1389  1.490250e+18

answered Mar 28, 2017 at 8:20

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

Comments

jezrael · Accepted Answer · 2017-03-28 08:21:57Z

2

I think you need merge with drop:

print (pd.merge(oss, p1, left_on=['Stream','SeqNo'], 
                         right_on=['StreamId','SeqNum'],how='left')
          .drop(['StreamId','SeqNum'], axis=1))

   OrderID  Symbol  Stream  SeqNo     Timestamp
0  5000000  AXBANK       3   1158  1.490250e+18
1  5000001  AXBANK       6   1733           NaN
2  5000002  AXBANK       6   1244           NaN
3  5000003  AXBANK       6   1388           NaN
4  5000004  AXBANK       3   1389  1.490250e+18

Another solution with rename columns:

d = {'Stream':'StreamId','SeqNo':'SeqNum'}
print (pd.merge(oss.rename(columns=d), p1, how='left'))
   OrderID  Symbol  StreamId  SeqNum     Timestamp
0  5000000  AXBANK         3    1158  1.490250e+18
1  5000001  AXBANK         6    1733           NaN
2  5000002  AXBANK         6    1244           NaN
3  5000003  AXBANK         6    1388           NaN
4  5000004  AXBANK         3    1389  1.490250e+18

edited Mar 28, 2017 at 8:21

answered Mar 28, 2017 at 8:16

jezrael

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

2 Comments

pythonRcpp Over a year ago

Is there a way to keep timestamp as it is and not in e

jezrael Over a year ago

I think there is only one way - convert before to str - p1.Timestamp = p1.Timestamp.astype(str). Because is impossible have int valus together with float - int are always converted to float - see docs

Collectives™ on Stack Overflow

Map dataframe with multiple columns as keys pandas

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related