Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.soft-sys.math.mathematica > #9361

vlookup like function sugestion

From Rodrigo Murta <murta@rodrigomurta.com>
Newsgroups comp.soft-sys.math.mathematica
Subject vlookup like function sugestion
Date 2012-04-02 08:25 +0000
Organization Steven M. Christensen and Associates, Inc and MathTensor, Inc.
Message-ID <jlbnp4$44h$1@smc.vnet.net> (permalink)

Show all headers | View raw


When you are used to work with Excel, one big challenge in start
working with Mathematica is to find one way to merge table like
Vlookup does.
I know by my own experience that it's very difficult to handle this in
Mathematica, and I belive that a lot of Excel user get a little bit
frustrated in see that one simple operation like that is so difficult.
I would like to suggest to Mathematica team that the future version
could have some builting in function to handle this situation.
Meanwhile I use the below function that I would like to share with the
community:

vlookup[data1_, data2_, pk1_: 1, col1_: {2}, pk2_: 1] :=
Module[{look},
  look[val_, table_, pk2l_] := Select[table, #[[pk2l]] == val &];
  Join[#,
     Check[look[#[[pk1]], data2, pk2][[1, col1]],
       ConstantArray[Null, Length@col1]] // Quiet] & /@ data1]

Use example:

mydata={{"brazil",605,54161,a},{"india",550.92,49505,b},{"china",
550.92,49505,c},{"russia",752.61,54324,d}};
lookup={{54161,3.2,"rodrigo"},{5434,16.3,"murta"},{49505,12.3,
"daniel"}};

vlookup[mydata, lookup, 3, {2, 3}]

{{"brazil", 605, 54161, a, 3.2, "rodrigo"}, {"india", 550.92, 49505,
  b, 12.3, "daniel"}, {"china", 550.92, 49505, c, 12.3,
  "daniel"}, {"russia", 752.61, 54324, d, Null, Null}}

vlookup[mydata, lookup, 3]

{{"brazil", 605, 54161, a, 3.2}, {"india", 550.92, 49505, b,
  12.3}, {"china", 550.92, 49505, c, 12.3}, {"russia", 752.61, 54324,
  d, Null}}


Best Regards
Murta
PS: there is an topic about this in 2008
http://groups.google.com/group/comp.soft-sys.math.mathematica/browse_thread/thread/8116b7eb264bb20a/e3299e06272ab379?hl=en&lnk=gst&q=vlookup#e3299e06272ab379

Back to comp.soft-sys.math.mathematica | Previous | Next | Find similar | Unroll thread


Thread

vlookup like function sugestion Rodrigo Murta <murta@rodrigomurta.com> - 2012-04-02 08:25 +0000

csiph-web