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?
2 Answers
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 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' 
