Mathematica Equivalent of Excel VLOOKUP function
- To: mathgroup at smc.vnet.net
- Subject: [mg93020] Mathematica Equivalent of Excel VLOOKUP function
- From: "Charles L. Snyder" <clsnyder at gmail.com>
- Date: Fri, 24 Oct 2008 02:28:21 -0400 (EDT)
Hi -
I have two large csv files.The first is the data:
mydata=Table[{{"jones", 34124324, 605, 54161, C33}, {"smith",
343214234, 550.92, 49505, V55}, {"andrews", 54554543, 550.92, 49505,
G55}, {"robinson", 565654565, 550.90, 49505, K77}, {"burt", 9898966,
752.61, 54324, U88}}]
the second file is the 'lookup table':
mylookup=Table[{{54161, 3.2}, {54324, 16.3}, {49505, 12.3}}]
MS Excel has a function called VLOOKUP - it finds a column of your
data (eg, the 4th column in my example (54161, 49505, 49505,
49505,54324)), and sequentially looks up each value in the lookup
table (which has unique values only), and then "grabs' the adjacent
column ([key]value pairs), and adds the looked up value as a final
column in the original data:
result = Table[{{"jones", 34124324, 605, 54161, C33, 3.2}, {"smith",
343214234, 550.92, 49505, V55, 12.3}, {"andrews", 54554543, 550.92,
49505, G55,12.3}, {"robinson", 565654565, 550.90, 49505, K77,12.3},
{"burt", 9898966, 752.61, 54324, U88,16.3}}]
I have found some old posts that come close to a solution, but aren't
quite right:
makeLookupTable[symbol_, list_,
column_] := (symbol[___] = Table[0., {Length[list[[1]] - 1]}];
Scan[With[{key = #[[column]]}, symbol[key] = Delete[#, column]] &,
list]);
m1 = Table[{i, Random[], Random[]}, {i, 10}];
m2 = Table[{Random[], Random[], Random[], i}, {i, 2, 12}];
allkeys = Union[m1[[All, 1]], m2[[All, 4]]]
Join[{#}, lt1[#], lt2[#]] & /@ allkeys
Thanks in advance
clsnyder