Pandas DataFrame: Converting between currencies

user_5645165 0
Viewed 479

I have a column that has entries that look like this

df
      price
0  42000SEK
1   1200EUR
2   2200EUR

I want to convert the entires that have Euros to SEK(swedish krona) and remove both SEK and EUR so i end up with this. (lets for simplicity say 1 EUR = 10 SEK)

['42000']
['12000']
['22000']

With this line of code i can find the rows that has Euros.

df[df.price.str.contains('EUR')]

but i dont know how to continue from here.

I could use this answer

Pandas DataFrame currency conversion

if i could split on first letter in the cell but i cant figure out how to do that.

pandas python
4 Answers

Let's try replace and pd.eval:

df["price"].replace({"SEK": "*1", "EUR": "*10"}, regex=True).map(pd.eval)

Output:

0    42000
1    12000
2    22000
Name: price, dtype: int64

This works nicely assuming you have no NaNs, and that there are only two currencies with one of them needing conversion. If you do have NaNs, fill them first. Finally, assign this back to the column to update the DataFrame.

user_49090870

You could do:

import pandas as pd

# setup
df = pd.DataFrame(data=['42000SEK', '1200EUR', '2200EUR'], columns=['price'])

# create multiplier
mult = df['price'].str.contains('EUR$').map({ True : 10, False : 1})

# extract values
values = df['price'].str.replace(r'\D+', '').astype(int)

df['price'] = values * mult
print(df)

Output

   price
0  42000
1  12000
2  22000

For creating the multiplier column, you could use np.where:

import numpy as np

# create multiplier
mult = np.where(df['price'].str.contains('EUR$'), 10, 1)

If you want to keep all the info I suggest you use str.extract with a regex for extracting the value and the currency, for example:

# extract multiplier and value, concat with existing df
res = pd.concat((df, df['price'].str.extract(r'(?P<value>\d+)(?P<currency>\D+)')), 1)

# create result and put it a column named total
res['total'] = res['value'].astype(int) * np.where(res['currency'].eq('EUR'), 10, 1)
print(res)

Output

      price  value currency  total
0  42000SEK  42000      SEK  42000
1   1200EUR   1200      EUR  12000
2   2200EUR   2200      EUR  22000
user_40015920

Original dataframe

df = pd.DataFrame(data=['42000SEK','1200EUR','2200EUR'],columns=['price'])
price
42000SEK
1200EUR
2200EUR

Currency conversion dataframe

cc = pd.DataFrame({'from':['EUR','SEK'],'to':['SEK','EUR'],'rate':[10,.1]})
from to rate
EUR SEK 10
SEK EUR .1

(may have dozens of currency pairs)

Input fields separation

df['value'] = df['price'].str.slice(stop=-3).astype(int)

df['currency'] = df['price'].str.slice(start=-3)
price value currency
42000SEK 42000 SEK
1200EUR 1200 EUR
2200EUR 2200 EUR

Applying conversion to EUR:

df['price in EUR'] = df['value']*df.merge(cc[cc.to=='EUR'].append({'from':'EUR','to':'EUR','rate':1},ignore_index=True),left_on='currency',right_on='from',how='left')['rate']
price value currency price in EUR
42000SEK 42000 SEK 4200
1200EUR 1200 EUR 1200
2200EUR 2200 EUR 2200

Explanation:

  • take only the conversion pairs to EUR: cc[cc.to=='EUR']

  • ensure that there will be self-conversion, otherwise we won't have the lines that were EUR already: .append({'from':'EUR','to':'EUR','rate':1},ignore_index=True)

  • join prices dataframe with conversion rates in a temporary column, note that the column currency is the same as the temporary column "from": df.merge(...,left_on='currency',right_on='from',how='left')['rate']

  • multiply this temporary rate column with the prices column and append as a new column named 'price in EUR': df['price in EUR'] = df['value']*...

Now it is possible to convert many currencies to EUR, all you need is to feed the currency conversion dataframe (cc).

user_79926000

If you want to retain the amount and currency, you can use regular expressions to extract the number part and non-number part separately:

df = pd.DataFrame({'price': ['42000SEK', '1200EUR', '2200EUR']})
df['amount'] = df.price.str.extract(r'(\d+)')
df['currency'] = df.price.str.extract(r'([^\d]+)')
print(df)

Output

      price amount currency
0  42000SEK  42000      SEK
1   1200EUR   1200      EUR
2   2200EUR   2200      EUR
user_45848090
Related