CODEWITHSUNDEEP
pythonpandasvlookup
LearningSlowly
LearningSlowly

Reputation: 9431

Python Pandas VLookup with multiple columns equivalent

I have some simulation results that I wish to pair with some static information I hold for their particular coordinates.

I am using pandas and the key dataframe looks like this:

Orig_lat    Orig_lng    Dest_lat    Dest_lng    Site    Lane_1
51.4410925  -0.0913334  51.4431736  -0.0681643  6   E
51.4431736  -0.0681643  51.4410925  -0.0913334  6   W
51.6300955  -0.0781079  51.6489284  -0.0602954  7   N
51.648917   -0.0600521  51.6299841  -0.0779832  7   S
51.4648078  -0.301316   51.4573656  -0.3219232  9   S
51.4573656  -0.3219232  51.4649063  -0.3013827  9   N
51.412392   0.0743042   51.4088694  0.0800096   11  S
51.4088694  0.0800096   51.412392   0.0743042   11  N
51.4728599  -0.0235216  51.4804927  -0.0231821  14  N

The results dataframe looks like this:

distance    duration    duration_in_traffic Orig_lat    Orig_lng    Dest_lat    Dest_lng    
1456736402  1670    186 337 51.4431736  -0.0681643  51.4410925  -0.0913334
1456736416  508 73  73  51.4380877  -0.2131928  51.4417083  -0.2168077
1456736416  508 71  71  51.4417083  -0.2168077  51.4380877  -0.2131928
1456736417  578 83  82  51.5229177  -0.4402988  51.5180086  -0.4391647
1456736417  578 79  79  51.5180086  -0.4391647  51.5229177  -0.4402988
1456736417  894 148 155 51.489123   -0.3015009  51.4886771  -0.2894982
1456736418  894 170 163 51.4886771  -0.2894982  51.489123   -0.3015009
1456736418  410 88  88  51.5294107  0.107865    51.5296292  0.1019929
1456736418  410 91  90  51.5296292  0.1019929   51.5294107  0.107865
1456736419  821 90  102 51.6043935  -0.340337   51.6038698  -0.3521945
1456736419  821 96  121 51.6038698  -0.3521945  51.6043935  -0.340337
1456736419  263 48  47  51.3718957  -0.0471616  51.3741868  -0.0480754
1456736420  263 48  48  51.3741868  -0.0480754  51.3718957  -0.0471616
1456736421  426 59  58  51.5122705  -0.2177689  51.5086821  -0.2156843
1456736421  426 55  70  51.5086821  -0.2156843  51.5122705  -0.2177689
1456736421  471 57  57  51.3782746  -0.1864154  51.3800551  -0.1916053

I wish to harvest the Site and Lane_1 columns from the key and join these to the results dataframe using Orig_lat, Orig_lng, Dest_lat, Dest_lng, giving:

distance    duration    duration_in_traffic Orig_lat    Orig_lng    Dest_lat    Dest_lng, Site, Lane_1    
    1456736402  1670    186 337 51.4431736  -0.0681643  51.4410925  -0.0913334
    1456736416  508 73  73  51.4380877  -0.2131928  51.4417083  -0.2168077  41  N
    1456736416  508 71  71  51.4417083  -0.2168077  51.4380877  -0.2131928  41  S
    1456736417  578 83  82  51.5229177  -0.4402988  51.5180086  -0.4391647  42  S
    1456736417  578 79  79  51.5180086  -0.4391647  51.5229177  -0.4402988  42  N
    1456736417  894 148 155 51.489123   -0.3015009  51.4886771  -0.2894982  43  E
    1456736418  894 170 163 51.4886771  -0.2894982  51.489123   -0.3015009  43  W
    1456736418  410 88  88  51.5294107  0.107865    51.5296292  0.1019929   45  W
    1456736418  410 91  90  51.5296292  0.1019929   51.5294107  0.107865    45  E
    1456736419  821 90  102 51.6043935  -0.340337   51.6038698  -0.3521945  46  W
    1456736419  821 96  121 51.6038698  -0.3521945  51.6043935  -0.340337   46  E
    1456736419  263 48  47  51.3718957  -0.0471616  51.3741868  -0.0480754  48  N
    1456736420  263 48  48  51.3741868  -0.0480754  51.3718957  -0.0471616  48  S
    1456736421  426 59  58  51.5122705  -0.2177689  51.5086821  -0.2156843  54  S
    1456736421  426 55  70  51.5086821  -0.2156843  51.5122705  -0.2177689  54  N
    1456736421  471 57  57  51.3782746  -0.1864154  51.3800551  -0.1916053  58  W

How would I use merge to achieve this?

Upvotes: 4

Views: 11103

Answers (1)

jezrael
jezrael

Reputation: 862601

IIUC use merge on columns Orig_lat, Orig_lng, Dest_lat, Dest_lng:

print pd.merge(dataframe, key, on=['Orig_lat','Orig_lng','Dest_lat','Dest_lng'])
   distance  duration  duration_in_traffic   Orig_lat  Orig_lng   Dest_lat  \
0      1670       186                  337  51.443174 -0.068164  51.441092   

   Dest_lng  Site Lane_1  
0 -0.091333     6      W

Upvotes: 5

Related Questions