Multiplying columns in separate pandas dataframes

Question

I'm trying to multiply data from 2 different dataframes and my code as below:

import pandas as pd
import numpy as np

df1 = pd.DataFrame({'v_contract_number': ['VN120001438','VN120001439',
                                          'VN120001440','VN120001438',
                                          'VN120001439','VN120001440'],
                                            'Currency': ['VND','USD','KRW','USD','KRW','USD'],
                                        'Amount': [10000,5000,6000,200,150,175]})
df2 = pd.DataFrame({'Currency': ['VND','USD','KRW'],'Rate': [1,23000,1200]})
print(df1)

# df1
  v_contract_number Currency  Amount
0       VN120001438      VND   10000
1       VN120001439      USD    5000
2       VN120001440      KRW    6000
3       VN120001438      USD     200
4       VN120001439      KRW     150
5       VN120001440      USD     175

print(df2)
  Currency   Rate
0      VND      1
1      USD  23000
2      KRW   1200

df1 = df1.merge(df2)
df1['VND AMount'] = df1['Amount'].mul(df1['Rate'])
df1.drop('Rate', axis=1, inplace=True)
print(df1)

# result
  v_contract_number Currency  Amount  VND AMount
0       VN120001438      VND   10000       10000
1       VN120001439      USD    5000   115000000
2       VN120001438      USD     200     4600000
3       VN120001440      USD     175     4025000
4       VN120001440      KRW    6000     7200000
5       VN120001439      KRW     150      180000

This is exactly what I want but I would like to know that have another way to not merge and drop as I did? The reason that I drop ‘Rate’ because I dont want it appears in my report.

Thanks and best regards

Answer 1

You can use pandas' map for this:

df2 = df2.set_index('Currency').squeeze() # squeeze converts to a Series

df1.assign(VND_Amount = df1.Amount.mul(df1.Currency.map(df2)))

  v_contract_number Currency  Amount  VND_Amount
0       VN120001438      VND   10000       10000
1       VN120001439      USD    5000   115000000
2       VN120001440      KRW    6000     7200000
3       VN120001438      USD     200     4600000
4       VN120001439      KRW     150      180000
5       VN120001440      USD     175     4025000