Adding a new pandas column with mapped value from a dictionary
PythonPandasPython Problem Overview
I'm trying do something that should be really simple in pandas, but it seems anything but. I'm trying to add a column to an existing pandas dataframe that is a mapped value based on another (existing) column. Here is a small test case:
import pandas as pd
equiv = {7001:1, 8001:2, 9001:3}
df = pd.DataFrame( {"A": [7001, 8001, 9001]} )
df["B"] = equiv(df["A"])
print(df)
I was hoping the following would result:
A B
0 7001 1
1 8001 2
2 9001 3
Instead, I get an error telling me that equiv is not a callable function. Fair enough, it's a dictionary, but even if I wrap it in a function I still get frustration. So I tried to use a map function that seems to work with other operations, but it also is defeated by use of a dictionary:
df["B"] = df["A"].map(lambda x:equiv[x])
In this case I just get KeyError: 8001. I've read through documentation and previous posts, but have yet to come across anything that suggests how to mix dictionaries with pandas dataframes. Any suggestions would be greatly appreciated.
Python Solutions
Solution 1 - Python
The right way of doing it will be df["B"] = df["A"].map(equiv)
.
In [55]:
import pandas as pd
equiv = {7001:1, 8001:2, 9001:3}
df = pd.DataFrame( {"A": [7001, 8001, 9001]} )
df["B"] = df["A"].map(equiv)
print(df)
A B
0 7001 1
1 8001 2
2 9001 3
[3 rows x 2 columns]
And it will handle the situation when the key does not exist very nicely, considering the following example:
In [56]:
import pandas as pd
equiv = {7001:1, 8001:2, 9001:3}
df = pd.DataFrame( {"A": [7001, 8001, 9001, 10000]} )
df["B"] = df["A"].map(equiv)
print(df)
A B
0 7001 1
1 8001 2
2 9001 3
3 10000 NaN
[4 rows x 2 columns]