How to convert currency in a database using pandas

Question

In my problem one column have type of currency and another have the value, I need to convert them to USD. How can I do that using pandas ?

My Columns as following:-

I want like this:

from currency_converter import SINGLE_DAY_ECB_URL
last_date = con.bounds['USD']
con = CurrencyConverter(SINGLE_DAY_ECB_URL)


def currency_convertor(row,new_currency='USD'):
    amount = row['Amount']
    curr = row['Currency']
    new_curr = con.convert(amount,curr,new_currency)
    return new_curr

df5 = df5.apply(lambda x: currency_convertor(x,new_currency="USD"), axis=1)

BUT after conversion I'm getting only amount column like this

I want it as second table

Answer 1

For the answer below I'll assume that you have a dataframe, df, with columns named currency and amount.

I have cobbled together a demo jupyter notebook to illustrate the method.

1. Work out what currencies you have in your dataframe

   You'll need an exchange rate for every currency you have in your dataframe, so you need to know what currencies you have.

   currencies = df.currency.unique().tolist()
   currencies = dict.fromkeys(currencies, pd.NA)
   

2. Define an exchange rate for every currency

   Exchange rates vary over time, and can vary depending on who you ask, so you'll need to define a set exchange rate to use. You can define these yourself manually:

   currencies['CAD'] = 1.23
   currencies['GBP'] = 0.72
   

   Alternatively, as It_is_Chris pointed out, you could use the CurrencyConverter library to source these automatedly in real time:

   from currency_converter import CurrencyConverter
   c = CurrencyConverter()
   for key in currencies:
      try:
         currencies[key] = c.convert(1, 'USD', key)
      except:
         pass
   

3. Convert the currencies in your dataframe

   Try and avoid looping through pandas dataframes; the built in methods are much faster. In this case you can use apply():

   df['amount_conv'] = df.apply(lambda x: x.amount / currencies[x.currency], axis=1 )