CODEWITHSUNDEEP
python-3.xpandasdataframe
ShugoChara12
ShugoChara12

Reputation: 1

Converting exchange rates in pandas dataframe to GBP

I have a dataframe that looks like this. I'm trying to convert these stock prices to the corresponding exchange rates and output them into GBP.

df_test = pd.DataFrame({'Stock': ['AAPL', 'AAL', 'BBW', 'BBY'],
                        'Price': [123, 21, 15, 311],
                        'Currency': ['USD', 'CAD', 'EUR', 'GBP'],
                        'Date': ['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-04'],
                        'USD': [1.6, 1.5, 1.55, 1.57],
                        'CAD': [1.7, 1.79, 1.75, 1.74],
                        'EUR': [1.17, 1.21, 1.18, 1.19],
                        'GBP': [1, 1, 1, 1]})

I want to multiply the stock price for each row so for example since AAPL is in USD I want it to be multiplied by 1.6 so it is converted into GBP, and the end output I am trying to get is something that would look like this.

df_end = pd.DataFrame({'Stock': ['AAPL', 'AAL', 'BBW', 'BBY'],
                       'Price': [123, 21, 15, 311],
                       'Currency': ['USD', 'CAD', 'EUR', 'GBP'],
                       'Date': ['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-04'],
                       'USD': [1.6, 1.5, 1.55, 1.57],
                       'CAD': [1.7, 1.79, 1.75, 1.74],
                       'EUR': [1.17, 1.21, 1.18, 1.19],
                       'GBP': [1, 1, 1, 1],
                       'GBP value': [196.8, 37.59, 17.7, 311]})

Any help at all on this would be very much appreciated, thank you.

Upvotes: 0

Views: 491

Answers (2)

Quang Hoang
Quang Hoang

Reputation: 150785

This used to be .lookup which is deprecated in Pandas 1.2.0. A way around this is get_indexer:

idx = df_test.columns.get_indexer(df_test['Currency'])
df_test['GBP val'] = df_test['Price'] * df_test.values[df.index, idx]

Output:

  Stock  Price Currency        Date   USD   CAD   EUR  GBP GBP val
0  AAPL    123      USD  2020-01-01  1.60  1.70  1.17    1   196.8
1   AAL     21      CAD  2020-01-02  1.50  1.79  1.21    1   37.59
2   BBW     15      EUR  2020-01-03  1.55  1.75  1.18    1    17.7
3   BBY    311      GBP  2020-01-04  1.57  1.74  1.19    1     311

Upvotes: 1

Andrej Kesely
Andrej Kesely

Reputation: 195553

df_test["GBP value"] = df_test.apply(
    lambda x: x["Price"] * x[x["Currency"]], axis=1
)
print(df_test)

Prints:

  Stock  Price Currency        Date   USD   CAD   EUR  GBP  GBP value
0  AAPL    123      USD  2020-01-01  1.60  1.70  1.17    1     196.80
1   AAL     21      CAD  2020-01-02  1.50  1.79  1.21    1      37.59
2   BBW     15      EUR  2020-01-03  1.55  1.75  1.18    1      17.70
3   BBY    311      GBP  2020-01-04  1.57  1.74  1.19    1     311.00

Upvotes: 0

Related Questions