2

As per my knowledge Python loops are slow, hence it is preferred to use pandas inbuilt functions.

In my problem, one column will have different currencies, I need to convert them to dollar. How can I detect and convert them to dollar using pandas inbuilt functions ?

My column as following:

100Dollar
200Dollar
100Euro
300Euro
184pounds
150pounds
10rupee
30rupee

Note: amount and currency name is in same column.

Note: conversion exchange rate w.r.t dollar {Euro: 1.2, pounds: 1.3, rupee: 0.05}

Note: currency enum is ['Euro', 'Dollar', 'Pounds', 'Rupee']

Kiran
  • 2,147
  • 6
  • 18
  • 35
  • 1
    We would need a conversion / exchange rate to be able to do this. – Erfan Dec 21 '19 at 16:06
  • either you need to have standard currency terminologies: `EUR`, `INR`,`USD` etc, else you have to give us some sort of mapping to do this – anky Dec 21 '19 at 16:17

2 Answers2

3

I would suggest something similar to the below using the CurrencyConverter package (tested with google for accuracy):

from currency_converter import CurrencyConverter
c = CurrencyConverter()
d={'Dollar':'USD','Euro':'EUR','pounds':'GBP','rupee':'INR'} #mapping dict

m=pd.DataFrame(df['column'].replace(d,regex=True).str.findall(r'(\d+|\D+)').tolist())
new_df=df.assign(USD_VALUE=[c.convert(a,b,'USD') for a,b in zip(m[0],m[1])])

      column   USD_VALUE
0  100Dollar  100.000000
1  200Dollar  200.000000
2    100Euro  110.770000
3    300Euro  332.310000
4  184pounds  242.428366
5  150pounds  197.631820
6    10rupee    0.140999
7    30rupee    0.422996
anky
  • 74,114
  • 11
  • 41
  • 70
2

Use Series.str.extract with regular expressions to extra the correct values into a new column. Then map the exchange_rate to the Currency column to calculate the Amount dollars:

df[['Amount', 'Currency']] = df['column'].str.extract(r'(\d+)(\D+)')

exchange_rate = {'Euro': 1.2, 'pounds': 1.3, 'rupee': 0.05}
df['Amount_dollar'] = pd.to_numeric(df['Amount']) * df['Currency'].map(exchange_rate).fillna(1) 

      column  Amount Currency  Amount_dollar
0  100Dollar     100   Dollar         100.00
1  200Dollar     200   Dollar         200.00
2    100Euro     100     Euro         120.00
3    300Euro     300     Euro         360.00
4  184pounds     184   pounds         239.20
5  150pounds     150   pounds         195.00
6    10rupee      10    rupee           0.50
7    30rupee      30    rupee           1.50
Erfan
  • 40,971
  • 8
  • 66
  • 78
  • 1
    I see your point, but I think OP just gave an example of exchange rates. Normally when you have financial data, somewhere in the database there is an "exchange rate table" stored with daily exchange rates for all the currency's. At least that's my experience. I saw the package you provided, which is nice, but when working with financial data, it's really hard to convince regulators that you've used an open source package for currency conversion. Although these packages are most of the time more accurate. – Erfan Dec 21 '19 at 17:08