Pandas DataFrame: Converting between currencies

Question

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.

Answer 1

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.

Answer 2

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).

Answer 3

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

Answer 4

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