Suppose I have a data like this:
and I want to change the value of name and gender into an integer, and I have a dictionary like this:
I understand that I can use replace function in pandas, but I don't know how to use an excel dictionary to replace the value
thank you
Assuming you have a pair of csv files: "replace.csv" representing the first table and "table.csv" representing the second table i.e. "dict". Use the following code:
import pandas as pd
frame = pd.read_csv('replace.csv')
table = pd.read_csv('table.csv', header=None)
mapping = dict([(k, v) for k, v in table.values])
frame.replace(to_replace={"name": mapping, "gender": mapping}, inplace=True)
print frame
The line mapping = dict([(k, v) for k, v in table.values]) is the one that transforms your dataframe into a dict. For more on transforming a dataframe into a dictionary see the documentation, also this question provides different ways of transforming a dataframe into a dictionary. For reading excel files, instead of csv files, see this.
