How would you add new column to a dataframe based on conditional math from another dataframe?-CodePudding

Let me simplify this.

I have currency conversation key DataFrame, it has 2 columns. Currency and Spot FX Rate in USD.

currency    fx_rate
INR         0.013223
JPY         0.008653
MYR         0.239000
CNY         0.157300
HKD         0.128160

My 2nd DataFrame is

currency ID   amount outstanding
    INR 78waf 1000000000
    JPY 48waf 100000000000
    MYR 38waf 10000000
    CNY 28waf 1000000000
    HKD 18waf 10000000

How would I create a Fourth Column in my 2nd DF ("CONVERTED AMOUNT") based on Currency Key DataFrame? You would need to Multiple for the conversion (for INR: 0.013223 * 1000000000 = 13223000)

end goal:

currency ID   amount outstanding CONVERTED AMOUNT
    INR 78waf 1000000000         13223000
    JPY 48waf 100000000000       etc..
    MYR 38waf 10000000           ...
    CNY 28waf 1000000000         ...
    HKD 18waf 10000000           ...

CodePudding user response：

Your two last dataframes seem to have more columns than they should.

Anyway, if I understand your problem correctly, I will probably just go like this:

df["CONVERTED AMOUNT"] = df["amount"] * 0.013223

CodePudding user response：

You could map "fx_rate" to "currency" column in df2 and multiply with "amount_outstanding":

df2['converted_amount'] = df2['currency'].map(df1.set_index('currency')['fx_rate']) * df2['amount_outstanding']

You could also set_index with "currency" and multiply the amounts on matching currencies and reset_index:

df2 = df2.set_index('currency')
df2['converted_amount'] = df2['amount_outstanding'].mul(df1.set_index('currency')['fx_rate'])
df2 = df2.reset_index()

Another options is to merge the DataFrames on "currency" and multiply the relevant columns:

merged = df2.merge(df1, on='currency')
merged['converted_amount'] = merged['amount_outstanding'] * merged['fx_rate']
out = merged.drop(columns=['fx_rate'])

Output:

  currency     ID  amount_outstanding  converted_amount
0      INR  78waf          1000000000        13223000.0
1      JPY  48waf        100000000000       865300000.0
2      MYR  38waf            10000000         2390000.0
3      CNY  28waf          1000000000       157300000.0
4      HKD  18waf            10000000         1281600.0