CODEWITHSUNDEEP
pythonpandasdatetime
Mike
Mike

Reputation: 23

python pandas: accessing datetime series for currency conversion

I have a Panda dataframe as below:

>>> df.head()
            Date         From        To        Currency    Net
TransID
4569219     2015-02-21   Calzada     Michael   MXN         2396.0
5630763     2013-08-18   Juan        Edgar     USD         155.56
4698552     2013-08-17   David       Jessica   MXN         1965.23
5706840     2013-08-11   Edgar       Luis      MXN         923.22
7522850     2011-08-11   Jonathan    Juan      USD         58.23

The Date Series is dtype: datetime64[ns] and the Net one is dtype: float64.

And I want to convert any Mexican Pesos (MXN) Amount into USD, based on the exchange rate at the given date.

To do that I am using the forex_python library, for example:

>>> from forex_python.converter import CurrencyRates
... from datetime import datetime
...
... c = CurrencyRates()
... amount = 1000
... date = datetime(2013,02,21)
... print c.convert('MXN','USD', amount, date)
78.344

This converts 1000 MXN and returns the amount in USD. Now i want to apply this to my dataframe, but I get different types of errors. First, I have tried:

from forex_python.converter import CurrencyRates
from datetime import datetime

c = CurrencyRates()
df.loc[df['Currency'].str.contains('MXN'), 'Net'] = c.convert('MXN', 'USD', df['Net'], df['Dates'])

But I get:

AttributeError: 'Series' object has no attribute 'strftime'

After searching for the error here, I tried another way using the dt accessor:

from forex_python.converter import CurrencyRates
from datetime import datetime

c = CurrencyRates()
df.loc[df['Currency'].str.contains('MXN'), 'Net'] = c.convert('MXN', 'USD', df['Net'], datetime(df['Date'].dt.year, df['Date'].dt.month, df['Date'].dt.day))

This time I get another error:

TypeError: cannot convert the series to <type 'int'>

I should underline that it works fine if I'm not using the date argument (but I get today's exchange rate). I.e.:

df.loc[df['Currency'].str.contains('MXN'), 'Net'] = c.convert('MXN', 'USD', df['Net'])
df['Currency'] = 'USD'

I would love my final result to be:

>>> df.head()
            Date         From        To        Currency    Net
TransID
4569219     2015-02-21   Calzada     Michael   USD         158.68
5630763     2013-08-18   Juan        Edgar     USD         155.56
4698552     2013-08-17   David       Jessica   USD         154.40
5706840     2013-08-11   Edgar       Luis      USD         923.22
7522850     2011-08-11   Jonathan    Juan      USD         72.53

It seams like I can't extract the corresponding date from the Date Series to use in my c.convert() function. I have no idea how to solve this problem without extracting my information and working with lists, which I honestly don't really want to do.

Any ideas?

Thanks for your help.

Upvotes: 2

Views: 2383

Answers (1)

Ted Petrou
Ted Petrou

Reputation: 62017

This is a good use case for the apply which can loop through all your rows with axis=1.

df['Date'] = pd.to_datetime(df.Date)
df['Net'] = df.apply(lambda x: x.Net if x.Currency == 'USD' else 
                               c.convert('MXN', 'USD', x.Net, x.Date), axis=1)
df['Currency'] = 'USD'

              Date      From       To Currency         Net
TransID                                                   
4569219 2015-02-21   Calzada  Michael      USD  159.638292
5630763 2013-08-18      Juan    Edgar      USD  155.560000
4698552 2013-08-17     David  Jessica      USD  152.944025
5706840 2013-08-11     Edgar     Luis      USD   73.240889
7522850 2011-08-11  Jonathan     Juan      USD   58.230000

Upvotes: 2

Related Questions