Re: Mathematica Equivalent of Excel VLOOKUP function
- To: mathgroup at smc.vnet.net
- Subject: [mg93107] Re: Mathematica Equivalent of Excel VLOOKUP function
- From: Januk <ggroup at sarj.ca>
- Date: Sun, 26 Oct 2008 01:30:01 -0500 (EST)
- References: <gdrq74$mlf$1@smc.vnet.net>
You could try something along the lines of:
mydata={{"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}};
mylookup={{54161,3.2},{54324,16.3},{49505,12.3}};
vlookup[record_, lookupTable_, recordIndex_, matchColumn_: 1,
resultColumn_: - 1] :=
Module[{nTab, key, i, tabResult},
key = record[[recordIndex]];
nTab = Length[lookupTable];
i = 1;
While[
i <= nTab && lookupTable[[i, matchColumn]] != key,
i++];
If[i <= nTab,
tabResult = lookupTable[[i, resultColumn]],
tabResult = "No Match"
];
If[MatchQ[ tabResult, _List],
Join[record, tabResult],
Append[record, tabResult]
]
]
mydata2=vlookup[#, mylookup, 4, 1, 2]&/@mydata;
mydata2//TableForm
Note that this is written so you can grab more than just a single
column from your lookup table. Also, you can compare more than just a
single column value. You can use any expression that Part will accept
for the recordIndex, matchColumn and resultColumn. Also note that you
need to be careful if your lookup table does not contain a matching
entry or if it contains more than one matching entry.
I hope that helps.
Januk
On Oct 24, 2:33 am, "Charles L. Snyder" <clsny... at gmail.com> wrote:
> 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