[Python] How can I create a column that is the result of replacing values in two or more other...

Consider the dataframe df:

import pandas as pd

df = pd.DataFrame({'Event1': ['Music', 'Something else 1', 'Theatre', 'Comedy'],
'Event2': ['Something else 2', 'Ballet', 'Something else 3', 'Something else 4'],
'Cost': [10000, 5000, 15000, 2000]})
print(df)

Event1 Event2 Cost
0 Music Something else 2 10000
1 Something else 1 Ballet 5000
2 Theatre Something else 3 15000
3 Comedy 4 2000


I would like to map the values of the Event1 and Event2 to the values in the respective dictionaries:

# Mapping tables
dict1 = {'Music': 'M', 'Cooking': 'C', 'Theatre': 'T', 'Comedy': 'C'}
dict2 = {'Ballet': 'B', 'Swimming': 'S'}


And store the mappings in a common column because I know that per row, only the value of one column will be mapped. The end result would be:

# desired outcome
result = pd.DataFrame({'Event1': ['Music', 'Something else 1', 'Theatre', 'Comedy'],
'Event2': ['Something else 2', 'Ballet', 'Something else 3', '4'],
'Event': ['M', 'B', 'T', 'C'],
'Cost': [10000, 5000, 15000, 2000]})

print(result)

Event1 Event2 Event Cost
0 Music Something else 2 M 10000
1 Something else 1 Ballet B 5000
2 Theatre Something else 3 T 15000
3 Comedy 4 C 2000


I can only do this in a messy and lengthy way and was hoping there is clean maybe idiomatic way of doing this.

How would you advise doing it?

Continue reading...