VLOOKUP in Python and Pandas using .map() or .merge()

  • by
vlookup in python
  • Save

In previous articles, we’ve talked about how wonderful VLOOKUP can be in Excel – but how do you accomplish this in Python? We can do this quite easily using Pandas. In this post, we’ll explore VLOOKUP in Python and Pandas using .map() or .merge(), using either method depending on whether or not your lookup data exists in a separate dataframe or not.

What do I need for VLOOKUP in Python?

To get started, import pandas and your dataframe. We’ll generate one here so that you can follow along more easily. If you want to see other ways of generating dataframes, check out our Python pivot table tutorial.

import pandas as pd
data_dict = {
    'customer': ['Jim', 'Mel', 'Jim', 'Mike', 'Matthew', 'Mel', 'Jim', 'Matthew', 'Matthew', 'Mel', 'Jen', 'Jim', 'Jim', 'Mike', 'Matthew', 'Mike', 'Mike', 'Matthew', 'Jen', 'Matthew', 'Mike', 'Mel', 'Ari', 'Matthew', 'Ari', 'Jim', 'Jim', 'Ari', 'Jen', 'Matthew', 'Matthew', 'Jim', 'Matthew', 'Ari', 'Jen', 'Ari', 'Matthew', 'Jim', 'Mel', 'Jen', 'Ari', 'Mel', 'Jim', 'Jen', 'Mel', 'Matthew', 'Jim', 'Jim', 'Mel', 'Jim', 'Mel', 'Mike', 'Matthew', 'Ari', 'Mike', 'Matthew', 'Mel', 'Matthew', 'Jen', 'Jen', 'Matthew', 'Jen', 'Ari', 'Mike', 'Mel', 'Matthew', 'Matthew', 'Ari', 'Jen', 'Ari', 'Ari', 'Jim', 'Matthew', 'Jim', 'Ari', 'Mike', 'Ari', 'Matthew', 'Ari', 'Jim', 'Jim', 'Mel', 'Mike', 'Matthew', 'Matthew', 'Jim', 'Mel', 'Ari', 'Mel', 'Matthew', 'Mike', 'Ari', 'Jim', 'Jen', 'Mike', 'Matthew', 'Ari', 'Matthew', 'Matthew', 'Jim', 'Jim', 'Ari', 'Matthew', 'Mel', 'Jen', 'Ari', 'Jen', 'Mike', 'Mel', 'Jen', 'Mike', 'Mike', 'Jen', 'Mel', 'Ari', 'Ari', 'Ari', 'Jim', 'Matthew', 'Mel', 'Mike', 'Jim', 'Mel', 'Matthew', 'Jim', 'Matthew', 'Jen', 'Jen', 'Jim', 'Jim', 'Jen', 'Jim', 'Mike', 'Jen', 'Jen', 'Jim', 'Jen', 'Mel', 'Ari', 'Matthew', 'Jen', 'Mel', 'Jen', 'Ari', 'Mike', 'Ari', 'Matthew', 'Jim', 'Jim', 'Mel', 'Mel', 'Jim', 'Matthew', 'Jen', 'Mel', 'Jen', 'Mel', 'Jen', 'Ari', 'Jim', 'Ari', 'Jim', 'Mike', 'Mike', 'Ari', 'Matthew', 'Jen', 'Jen', 'Mike', 'Ari', 'Mel', 'Matthew', 'Ari', 'Jen', 'Jim', 'Jen', 'Jim', 'Ari', 'Matthew', 'Jim', 'Mike', 'Jim', 'Mike', 'Jen', 'Jen', 'Jen', 'Mike', 'Mel', 'Mike', 'Ari', 'Mel', 'Mel', 'Ari', 'Mike', 'Jen', 'Mel', 'Mike', 'Matthew', 'Ari', 'Mel', 'Jen', 'Jen', 'Mel', 'Matthew', 'Mike', 'Ari', 'Mike', 'Ari', 'Mike', 'Mike', 'Jim', 'Mel', 'Mike', 'Mel', 'Matthew', 'Jim', 'Jen', 'Mel', 'Mel', 'Mel', 'Mike', 'Mike', 'Jen', 'Jim', 'Mel', 'Jim', 'Mike', 'Ari', 'Matthew', 'Jim', 'Ari', 'Jen', 'Jim', 'Mike', 'Jim', 'Matthew', 'Jim', 'Mel', 'Jen', 'Mel', 'Matthew', 'Ari', 'Jim', 'Matthew', 'Jim', 'Jen', 'Mike', 'Ari', 'Mel', 'Jim', 'Matthew', 'Ari', 'Mel', 'Mike', 'Mel', 'Jim', 'Mike', 'Jim', 'Jen', 'Mike', 'Mel', 'Mike', 'Ari', 'Matthew', 'Mike', 'Mel', 'Jim', 'Mel', 'Jen', 'Ari', 'Matthew', 'Ari', 'Mel', 'Jen', 'Jen', 'Mike', 'Mel', 'Mike', 'Jen', 'Jim', 'Ari', 'Jen', 'Matthew', 'Jim', 'Matthew', 'Ari', 'Matthew', 'Jen', 'Ari', 'Mel', 'Mel', 'Mike', 'Mike', 'Jim', 'Matthew', 'Mel', 'Mel', 'Mike', 'Mel', 'Mike', 'Mel', 'Jim', 'Jim', 'Ari', 'Mike', 'Jim', 'Ari', 'Matthew', 'Jen', 'Mike', 'Matthew', 'Jim', 'Jim', 'Ari', 'Matthew', 'Mike', 'Jen', 'Jen', 'Ari', 'Ari', 'Matthew', 'Ari', 'Jen', 'Mel', 'Ari', 'Matthew', 'Ari', 'Matthew', 'Jim', 'Jim', 'Mike', 'Mel', 'Mel', 'Mel', 'Jen', 'Matthew', 'Ari', 'Jim', 'Mel', 'Mel', 'Jen', 'Mike', 'Jim', 'Ari', 'Matthew', 'Mike', 'Jen', 'Mike', 'Matthew', 'Ari', 'Ari', 'Matthew', 'Mike', 'Mel', 'Ari', 'Mike', 'Matthew', 'Mel', 'Matthew', 'Mike', 'Mike', 'Mel', 'Matthew', 'Mel', 'Mike', 'Matthew', 'Ari', 'Mel', 'Mike', 'Jim', 'Ari', 'Ari', 'Jen', 'Jen', 'Mel', 'Jim', 'Matthew', 'Jen', 'Jen', 'Jen', 'Jim', 'Ari', 'Jen', 'Jim', 'Jim', 'Matthew', 'Jen', 'Mike', 'Mike', 'Matthew', 'Mike', 'Jen', 'Matthew', 'Ari', 'Jen', 'Mike', 'Mel', 'Jim', 'Jen', 'Jen', 'Ari', 'Ari', 'Mike', 'Jen', 'Mike', 'Mel', 'Mel', 'Mike', 'Matthew', 'Mike', 'Mike', 'Ari', 'Jen', 'Mike', 'Mel', 'Jen', 'Jen', 'Mel', 'Mike', 'Matthew', 'Jen', 'Jen', 'Jen', 'Mike', 'Jim', 'Jen', 'Matthew', 'Jim', 'Jen', 'Mike', 'Mike', 'Ari', 'Matthew', 'Jen', 'Ari', 'Jim', 'Jim', 'Jim', 'Jim', 'Jen', 'Mike', 'Matthew', 'Jim', 'Jim', 'Ari', 'Jen', 'Ari', 'Jen', 'Ari', 'Ari', 'Matthew', 'Mike', 'Ari', 'Ari', 'Ari', 'Jen', 'Mike', 'Mike', 'Jen', 'Ari', 'Ari', 'Ari', 'Mike', 'Ari', 'Ari', 'Mel', 'Mel', 'Ari', 'Mike', 'Mike', 'Ari', 'Matthew', 'Mike', 'Jen', 'Jen', 'Jim', 'Jim', 'Mel', 'Jim', 'Matthew', 'Ari', 'Mike', 'Mike', 'Ari', 'Mel', 'Matthew', 'Jen', 'Mel', 'Matthew', 'Jim', 'Mike', 'Jim', 'Ari', 'Mike', 'Ari', 'Matthew', 'Mel', 'Ari', 'Mike', 'Jim', 'Mike', 'Jen', 'Matthew', 'Mel', 'Mike', 'Ari', 'Jen', 'Mike', 'Ari', 'Jim', 'Matthew', 'Jen', 'Ari', 'Jim', 'Jen', 'Jim', 'Mel', 'Mel', 'Jen', 'Matthew', 'Mel', 'Mel', 'Mike', 'Mike', 'Mel', 'Mel', 'Mel', 'Mel', 'Mike', 'Jen', 'Matthew', 'Ari', 'Jen', 'Mel', 'Jen', 'Mel', 'Ari', 'Mel', 'Jim', 'Jim', 'Jen', 'Matthew', 'Ari', 'Mike', 'Ari', 'Mel', 'Matthew', 'Matthew', 'Jim', 'Mel', 'Mike', 'Mike', 'Matthew', 'Jim', 'Jen', 'Jim', 'Jen', 'Matthew', 'Ari', 'Ari', 'Mel', 'Mel', 'Jen', 'Matthew', 'Matthew', 'Mel', 'Matthew', 'Matthew', 'Matthew', 'Matthew', 'Jim', 'Mel', 'Mel', 'Matthew', 'Ari', 'Matthew', 'Matthew', 'Jen', 'Jim', 'Ari', 'Jen', 'Mel', 'Ari', 'Mel', 'Ari', 'Mel', 'Mike', 'Jen', 'Matthew', 'Mel', 'Mel', 'Jen', 'Jim', 'Ari', 'Ari', 'Jen', 'Matthew', 'Mel', 'Ari', 'Matthew'],
    'month': [2, 2, 1, 3, 2, 2, 3, 1, 4, 2, 3, 1, 4, 1, 2, 4, 2, 2, 1, 1, 3, 4, 3, 3, 2, 3, 2, 2, 3, 1, 1, 4, 4, 2, 2, 3, 1, 4, 1, 1, 2, 4, 2, 3, 4, 1, 3, 2, 4, 4, 1, 4, 1, 2, 4, 4, 4, 2, 3, 4, 2, 4, 1, 4, 2, 3, 2, 1, 3, 3, 2, 1, 3, 3, 4, 1, 3, 3, 1, 4, 1, 3, 3, 1, 3, 2, 3, 4, 2, 3, 1, 3, 3, 4, 1, 1, 2, 4, 4, 3, 2, 3, 4, 3, 3, 3, 1, 3, 1, 1, 3, 1, 1, 4, 3, 4, 2, 2, 4, 4, 2, 3, 1, 2, 3, 2, 1, 1, 3, 1, 4, 4, 1, 2, 1, 2, 3, 2, 3, 3, 4, 2, 3, 1, 2, 2, 3, 3, 2, 3, 3, 3, 3, 1, 3, 4, 2, 1, 4, 3, 3, 3, 4, 3, 1, 1, 2, 2, 4, 1, 3, 2, 3, 1, 2, 3, 2, 4, 1, 1, 4, 2, 4, 3, 4, 4, 2, 4, 1, 4, 1, 2, 1, 2, 2, 1, 1, 2, 1, 3, 4, 1, 4, 1, 1, 2, 2, 1, 2, 3, 1, 2, 2, 2, 3, 3, 4, 1, 1, 3, 4, 1, 3, 4, 1, 3, 3, 1, 4, 1, 4, 1, 4, 2, 1, 4, 4, 3, 2, 2, 1, 4, 3, 3, 3, 1, 3, 3, 2, 2, 1, 4, 3, 4, 4, 2, 2, 2, 3, 3, 1, 4, 4, 4, 1, 2, 3, 2, 4, 2, 4, 1, 2, 1, 4, 2, 1, 4, 3, 4, 1, 4, 2, 1, 3, 2, 4, 2, 4, 1, 3, 1, 4, 1, 4, 2, 4, 3, 4, 3, 3, 3, 4, 1, 4, 2, 2, 1, 3, 4, 1, 2, 1, 4, 4, 2, 3, 4, 2, 2, 3, 2, 4, 1, 2, 4, 4, 4, 2, 2, 1, 4, 4, 1, 4, 3, 1, 3, 2, 2, 3, 2, 4, 2, 3, 4, 1, 2, 3, 1, 3, 2, 4, 2, 2, 1, 1, 3, 3, 3, 4, 2, 4, 3, 4, 3, 3, 4, 2, 4, 3, 3, 2, 4, 1, 1, 1, 1, 3, 4, 2, 2, 4, 3, 3, 3, 1, 3, 1, 3, 4, 1, 4, 1, 3, 2, 1, 3, 1, 3, 2, 4, 3, 4, 3, 4, 2, 4, 3, 3, 4, 2, 4, 2, 3, 3, 3, 3, 4, 4, 4, 2, 4, 1, 1, 1, 2, 2, 1, 3, 1, 2, 4, 2, 1, 1, 1, 4, 2, 1, 3, 4, 4, 3, 3, 4, 2, 1, 4, 2, 4, 3, 1, 3, 2, 2, 3, 1, 1, 4, 3, 4, 3, 3, 2, 4, 1, 4, 4, 1, 3, 3, 4, 2, 2, 4, 1, 3, 4, 4, 4, 4, 3, 3, 4, 3, 4, 4, 1, 1, 4, 3, 4, 2, 2, 1, 4, 3, 1, 4, 3, 3, 2, 1, 1, 3, 4, 2, 2, 4, 3, 2, 2, 4, 1, 1, 1, 2, 3, 3, 3, 4, 4, 2, 1, 2, 2, 1, 4, 2, 1, 4, 1, 3, 2, 2, 1, 3, 2, 4, 2, 4, 2, 3, 2, 3, 3, 2, 3, 4, 4, 4, 2, 2, 2, 3, 4, 1, 1, 4, 2, 2, 1, 2, 4, 1, 4, 3, 3, 1, 4, 1, 1, 3, 1, 4, 2, 3, 1, 2, 2, 3, 4, 2, 4, 3, 2, 3, 4, 3, 1, 2, 1, 4, 2, 3, 4, 3, 2, 3,],
    'total': [75, 1254, 377, 720, 770, 657, 1008, 397, 12, 1303, 128, 468, 1256, 423, 1118, 308, 442, 72, 435, 770, 875, 545, 1010, 828, 446, 1125, 830, 1140, 954, 245, 789, 71, 716, 15, 801, 515, 1176, 1132, 479, 779, 124, 198, 1008, 345, 1390, 1245, 577, 899, 144, 206, 1071, 1365, 542, 1298, 714, 462, 766, 947, 653, 420, 447, 182, 548, 513, 531, 797, 135, 374, 1358, 743, 92, 1276, 116, 156, 251, 745, 150, 31, 329, 326, 426, 1015, 348, 501, 1314, 1211, 172, 827, 1365, 894, 836, 467, 444, 389, 1344, 1394, 207, 844, 922, 590, 142, 88, 523, 53, 72, 1248, 885, 443, 27, 880, 251, 1021, 1080, 648, 1326, 316, 807, 648, 763, 240, 571, 766, 574, 214, 221, 226, 15, 1246, 1115, 744, 335, 189, 809, 150, 1089, 1028, 88, 236, 712, 1052, 599, 1387, 107, 650, 1134, 934, 1331, 937, 337, 956, 944, 100, 1077, 1151, 881, 621, 1354, 924, 1369, 468, 1120, 637, 110, 1019, 917, 913, 538, 1005, 543, 118, 220, 1305, 520, 1309, 873, 880, 140, 831, 1040, 806, 1214, 577, 740, 291, 483, 356, 800, 324, 1112, 378, 187, 1246, 1218, 923, 326, 1035, 561, 15, 887, 14, 616, 421, 57, 598, 887, 598, 598, 1268, 538, 316, 422, 267, 1117, 904, 1076, 887, 1329, 916, 454, 208, 88, 660, 1026, 1302, 361, 310, 534, 139, 508, 897, 414, 1149, 561, 319, 620, 338, 281, 547, 1158, 1348, 188, 233, 529, 932, 1088, 1153, 158, 349, 1031, 640, 687, 1260, 1306, 1044, 956, 181, 565, 1186, 1192, 1024, 1013, 912, 869, 1252, 224, 289, 842, 1345, 315, 937, 501, 651, 1335, 971, 1332, 421, 138, 354, 925, 314, 1157, 425, 481, 307, 1183, 1339, 148, 1038, 1046, 169, 909, 108, 1304, 1149, 428, 1043, 525, 480, 854, 1148, 113, 864, 1181, 111, 968, 718, 23, 361, 377, 208, 80, 503, 244, 222, 987, 1085, 436, 548, 823, 777, 690, 621, 700, 1060, 59, 536, 358, 953, 305, 404, 357, 1354, 61, 440, 242, 322, 1078, 131, 162, 598, 1127, 1290, 662, 82, 28, 207, 321, 867, 1092, 514, 584, 900, 271, 198, 430, 369, 1138, 1272, 254, 287, 138, 1204, 479, 1347, 981, 987, 310, 613, 357, 444, 1131, 432, 1086, 99, 801, 1012, 408, 416, 1168, 1289, 356, 912, 82, 831, 726, 792, 1395, 338, 1185, 336, 887, 207, 214, 363, 409, 374, 1107, 780, 1271, 471, 926, 872, 385, 856, 1370, 960, 812, 1018, 855, 140, 952, 123, 243, 667, 1064, 1138, 579, 295, 1320, 151, 49, 1040, 591, 662, 716, 1102, 1040, 46, 498, 1198, 649, 1392, 621, 976, 292, 242, 480, 792, 1393, 1207, 699, 761, 789, 632, 1397, 241, 1280, 246, 1019, 1254, 817, 1012, 706, 182, 759, 324, 80, 833, 979, 475, 1232, 127, 1219, 326, 1273, 424, 760, 1070, 488, 299, 102, 760, 751, 963, 1132, 103, 218, 1073, 203, 1206, 1237, 550, 476, 1242, 545, 1391, 160, 29, 1319, 881, 1266, 553, 858, 828, 496, 11, 1261, 792, 882, 300, 1276, 989, 1048, 344, 819, 752, 21, 212, 354, 872, 805, 1253, 570, 1230, 1156, 590, 1157, 1211, 1248, 161, 70, 1298, 551, 260, 783, 383, 274, 761, 782, 990, 730, 803, 1052, 1048, 839, 1221, 618, 910, 324, 1207, 167, 415, 37, 627, 812, 564, 700, 1073, 1255, 468, 1397, 1383, 968, 1333, 1272, 1071, 687, 1084, 853, 280, 175, 745, 1399, 760, 19, 879, 176, 329, 109, 111, 853, 880, 960, 1285, 1361, 1340, 886, 755, 524, 94, 1222, 393, 1088, 926, 196, 1329, 1050, 732, 64, 482, 483, 31, 1370, 647, 749, 1154, 838, 163, 989, 445]
}
df = pd.DataFrame(data_dict)
df.head()
customermonthtotal
0Jim275
1Mel21254
2Jim1377
3Mike3720
4Matthew2770

Using the .map() Method to Replicate VLOOKUP

Let’s say that we want to map in the gender of each customer. We happen to know our customers really well and can map in the genders. To do this, we develop a dictionary that contains all the values and their corresponding value.

We can pull each customers name by using the .unique() method on the customer column.

df.customer.unique()
array(['Jim', 'Mel', 'Mike', 'Matthew', 'Jen', 'Ari'], dtype=object)
genders_dict = {
    'Jim': 'Male',
    'Mel': 'Female',
    'Mike': 'Male',
    'Matthew': 'Male',
    'Jen': 'Female',
    'Ari': 'Male'
}

Now that we have a dictionary of all the assigned genders, we can generate a new column in the dataframe df that will map in the correct values.

df['gender'] = df.customer.map(genders_dict)
df.head()
customermonthtotalgender
0Jim275Male
1Mel21254Female
2Jim1377Male
3Mike3720Male
4Matthew2770Male

This method works extremely well and efficiently if the data isn’t stored in another dataframe. However, say you’re working with a relational database (like those covered in our SQL tutorials), and the data exists in another dataframe. Then, instead of generating a dictionary first, you can simply use the .join() method to join the dataframes together.

Using Pandas’ .join() Method to Replicate VLOOKUP

In the example below, we’re assuming you’re working with two dataframes. The same one we used above, df, and another one called genders. genders will contain the same data as the dictionary we created, but stored within a dataframe.

customer = ['Jim', 'Mel', 'Mike', 'Matthew', 'Jen', 'Ari']
genders = ['Male', 'Female', 'Male', 'Male' ,'Female', 'Male']
genders = pd.DataFrame({'customer': customer, 'genders': genders})
customergenders
0JimMale
1MelFemale
2MikeMale
3MatthewMale
4JenFemale
5AriMale

As we mentioned in our VLOOKUP tutorial, a VLOOKUP is essentially a LEFT JOIN in SQL. Using the .join() method, we’ll be emulating a LEFT JOIN.

df = pd.merge(df, genders, on='customer', how='left')
df.head()
customermonthtotalgendergenders
0Jim275MaleMale
1Mel21254FemaleFemale
2Jim1377MaleMale
3Mike3720MaleMale
4Matthew2770MaleMale

Conclusion: VLOOKUP in Python and Pandas using .map() or .merge()

Pandas makes it incredibly easy to replicate VLOOKUP style functions. In many ways, they remove a lot of the issues that VLOOKUP has, including not only merging on the left-most column.

For more Python coverage, check out our other tutorials. The official documentation can be found here for .map() and .merge().