CODEWITHSUNDEEP
pythonpandasmultiplication
hoa tran
hoa tran

Reputation: 1679

Multiplying columns in separate pandas dataframes

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

Upvotes: 2

Views: 233

Answers (2)

Cameron Riddell
Cameron Riddell

Reputation: 13407

You can avoid the drop by not overwriting df1 on the merge operation:

df1["VND Amount"] = df1.merge(df2, on="Currency").eval("Amount * Rate")

Alternatively you can use .reindex to align df2 to df1 based on the currency column:

df1["VND Amount"] = (
    df1["Amount"] * 
    (df2.set_index("Currency")["Rate"]  # set the index and return Rate column
        .reindex(df1["Currency"])       # align "Rate" values to df1 "Currency"
        .to_numpy()                     # get numpy array to avoid pandas 
                                        #   auto alignment on math ops
    )
)

Upvotes: 1

sammywemmy
sammywemmy

Reputation: 28644

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

Upvotes: 2

Related Questions