Mapping values from one DataFrame to another

Question

I am trying to figure out some fast and clean way to map values from one DataFrame A to another. Let say I have DataFrame like this one:

    C1  C2  C3  C4  C5
1   a   b   c   a
2   d   a   e   b   a
3   a   c
4   b   e   e

And now I want to change those letter codes to actual values. My DataFrame Bwith explanations looks like that:

    Code    Value
1   a       'House'
2   b       'Bike'
3   c       'Lamp'
4   d       'Window'
5   e       'Car'

So far my brute-force approach was to just go through every element in A and check with isin() the value in B. I know that I can also use Series (or simple dictionary) as an B instead of DataFrame and use for example Code column as a index. But still I would need to use multiple loops to map everything.

Is there any other nice way to achieve my goal?

Answer 1

Another alternative is map. Although it requires looping over columns, if I didn't mess up the tests, it is still faster than replace:

A = pd.DataFrame(np.random.choice(list("abcdef"), (1000, 1000)))
B = pd.DataFrame({'Code': ['a', 'b', 'c', 'd', 'e'],
                  'Value': ["'House'", "'Bike'", "'Lamp'", "'Window'", "'Car'"]})
B = B.set_index("Code")["Value"]

---

%timeit A.replace(B)
1 loop, best of 3: 970 ms per loop

---

C = pd.DataFrame()

%%timeit
for col in A:
    C[col] = A[col].map(B).fillna(A[col])
1 loop, best of 3: 586 ms per loop

Answer 2

You could use replace:

A.replace(B.set_index('Code')['Value'])

---

import pandas as pd
A = pd.DataFrame(
    {'C1': ['a', 'd', 'a', 'b'],
     'C2': ['b', 'a', 'c', 'e'],
     'C3': ['c', 'e', '', 'e'],
     'C4': ['a', 'b', '', ''],
     'C5': ['', 'a', '', '']})
B = pd.DataFrame({'Code': ['a', 'b', 'c', 'd', 'e'],
                  'Value': ["'House'", "'Bike'", "'Lamp'", "'Window'", "'Car'"]})
print(A.replace(B.set_index('Code')['Value']))

yields

         C1       C2      C3       C4       C5
0   'House'   'Bike'  'Lamp'  'House'         
1  'Window'  'House'   'Car'   'Bike'  'House'
2   'House'   'Lamp'                          
3    'Bike'    'Car'   'Car'