How can i multiply values of a column without .apply in pandas?

Question

i am trying to convert each value (USD100-120 e.g.) in a column of my dataset to USD (there are many different currencys like EUR etc.) So based on their Currency i need to convert them with the respective conversion rate. My input file is something like this:

d = {'location': ['US', 'UK'], 'price': ['USD10-20', 'GBP10-20']}
df = pd.DataFrame(data=d)

location|price

US |USD10-20

UK |GBP10-20

etc.

I tried this :

def convertCurrency(price):
    c=CurrencyConverter()
    currency= price[0:3]
    numbers=re.findall(r'\d+',price)
    lowerbound= re.findall(r'\d+',price)[0]
    res=""
    upperbound='x'
    if currency=='USD':
        return price
    if len(numbers)>1:
        upperbound=numbers[1]
    first=int(c.convert(int(lowerbound),price,"USD"))
    if upperbound != 'x':
        second=int(c.convert(int(upperbound),price,"USD"))
        res=''+currency+str(first)+"-"+str(second)
    else:
        res = '' + currency + str(first)
    return res

and calling it with apply

df['price'] = df.apply(lambda row: convertCurrency(row.price), axis=1)

but this takes way too long. i also tried this:

df['price'] = convertCurrency(df['price'])

but this will throw an error because the function gets a series object and not a string. What do i have to change or Is there another way? My desired result will be

location|price

US |USD10-20

UK |USD14-28

Please include a small subset of your data as a __copyable__ piece of code that can be used for testing as well as your expected output. See [MRE - Minimal, Reproducible, Example](https://stackoverflow.com/help/minimal-reproducible-example), and [How to make good reproducible pandas examples](https://stackoverflow.com/q/20109391/15497888). — Henry Ecker, May 07 '21 at 11:14
can you try `print([x.upper() + ' | '+ y for x, y in zip(d['location'], d['price'])])` and confirm if this is what you want ? — sam, May 07 '21 at 11:34
no, i need to call the convert function for every value in a column... — realslimjp, May 07 '21 at 12:10

score 1 · Accepted Answer · answered May 07 '21 at 12:31

Let's try using extract to get usable values then apply on axis 1:

import pandas as pd
from currency_converter import CurrencyConverter

d = {'location': ['US', 'UK'], 'price': ['USD10-20', 'GBP10-20']}
df = pd.DataFrame(data=d)

c = CurrencyConverter()
# Extract Values
df[['currency', 'v1', 'v2']] = df['price'].str.extract(r'(\w{3})(\d+)-(\d+)',
                                                       expand=True)
# Mask For Non USD Currency
m = df['currency'].ne('USD')
# Replace price where not USD
df.loc[m, 'price'] = df[m].apply(
    lambda s: f'USD'
              f'{int(c.convert(int(s.v1), s.currency, "USD"))}'
              f'-'
              f'{int(c.convert(int(s.v2), s.currency, "USD"))}',
    axis=1
)
# Drop Added Columns
df = df.drop(columns=['currency', 'v1', 'v2'])
print(df)

Output:

  location     price
0       US  USD10-20
1       UK  USD13-27

How can i multiply values of a column without .apply in pandas?

1 Answers1