Using Numpy arrays as lookup tables

Question

I have a 2D array of Numpy data read from a .csv file. Each row represents a data point with the final column containing a a 'key' which corresponds uniquely to 'key' in another Numpy array - the 'lookup table' as it were.

What is the best (most Numponic) way to match up the lines in the first table with the values in the second?

Answer 1

Some example data:

import numpy as np

lookup = np.array([[  1.     ,   3.14   ,   4.14   ],
                   [  2.     ,   2.71818,   3.7    ],
                   [  3.     ,  42.     ,  43.     ]])

a = np.array([[ 1, 11],
              [ 1, 12],
              [ 2, 21],
              [ 3, 31]])

Build a dictionary from key to row number in the lookup table:

mapping = dict(zip(lookup[:,0], range(len(lookup))))

Then you can use the dictionary to match up lines. For instance, if you just want to join the tables:

>>> np.hstack((a, np.array([lookup[mapping[key],1:] 
                            for key in a[:,0]])))
array([[  1.     ,  11.     ,   3.14   ,   4.14   ],
       [  1.     ,  12.     ,   3.14   ,   4.14   ],
       [  2.     ,  21.     ,   2.71818,   3.7    ],
       [  3.     ,  31.     ,  42.     ,  43.     ]])    

Answer 2

In the special case when the index can be calculated from the keys, the dictionary can be avoided. It's an advantage when the key of the lookup table can be chosen.

For Vebjorn Ljosa's example:

lookup:

>>> lookup[a[:,0]-1, :]
array([[  1.     ,   3.14   ,   4.14   ],
       [  1.     ,   3.14   ,   4.14   ],
       [  2.     ,   2.71818,   3.7    ],
       [  3.     ,  42.     ,  43.     ]])

merge:

>>> np.hstack([a, lookup[a[:,0]-1, :]])
array([[  1.     ,  11.     ,   1.     ,   3.14   ,   4.14   ],
       [  1.     ,  12.     ,   1.     ,   3.14   ,   4.14   ],
       [  2.     ,  21.     ,   2.     ,   2.71818,   3.7    ],
       [  3.     ,  31.     ,   3.     ,  42.     ,  43.     ]])