Equivalent of Series.map for DataFrame

[BrenBarn]

I think there should be a way to take a DataFrame and use the values in its columns as indices into a MultiIndexed Series. Here is an example:

>>> d = pandas.DataFrame({"Let": ["A", "B", "C"], "Num": [1, 2, 3]})
>>> d
  Let  Num
0   A    1
1   B    2
2   C    3
>>> ser = pandas.Series(
...     ['a', 'b', 'c', 'd', 'e', 'f'],
...     index=pandas.MultiIndex.from_arrays([["A", "B", "C"]*2, [1, 2, 3, 4, 5, 6]])
... )
>>> ser
A  1    a
B  2    b
C  3    c
A  4    d
B  5    e
C  6    f
dtype: object

With this data, you should be able to do d.map(ser) (or whatever method name instead of map) and get the same result as this:

>>> ser.ix[d.apply(tuple, axis=1)]
A  1    a
B  2    b
C  3    c
dtype: object

You currently cannot do this without converting the rows to tuples (ser[d] gives ValueError: Cannot index with multidimensional key). Converting to tuple is an awkward way to do a very natural task, which is using a tabular array of data to look up values in a tabular index (i.e., a MultiIndex).

I'm creating an issue to resurrect this request from much earlier discussion here and here.

[jreback]

map is just a fancy way of doing a merge

In [24]: d
Out[24]: 
  Let  Num
0   A    1
1   B    2
2   C    3

In [25]: ser
Out[25]: 
A  1    a
B  2    b
C  3    c
A  4    d
B  5    e
C  6    f
dtype: object

In [26]: ser.index.names=['Let','Num']

In [27]: pd.merge(d, ser.reset_index(), on=['Let','Num'])
Out[27]: 
  Let  Num  0
0   A    1  a
1   B    2  b
2   C    3  c

[jreback]

I suppose this could be shown in a doc example and the merge docs (and maybe cross-ref .map with .merge.

[jreback]

@BrenBarn do you want to do a doc-example for this?

[JBuitelaar]

I find myself doing something like this all the time, so I wrote my own function for it.

In terms of functionality, it's like a (vectorized and more generic) VLOOKUP in Excel. The implementation is just a wrapper around pd.DataFrame.merge (or join for some cases)

df.vlookup(other, drop_missing_rows=False, drop_lookup_cols=False ).

Similar to pd.DataFrame.merge and pd.Series.map, but:

• You may (or may not) want to keep the columns you're joining on (Series.map drops them, df.merge keeps them)
• always join on other.index.names, but you may want to use the index of df (like join) or the columns (like merge) or a combination.
• just like map, it raises when other.index rows are not unique
• you may want to drop the rows that don't have an entry in other (inner join) or insert NAs (left join).

Happy to submit if it helps

[pdpark]

I'll write up an example of this in the merging docs.