0

I have a dataframe like this:

  currency  currency_value  amount curr_fin
0      EUR        0.839398    20.0      USD
1      GBP        0.751034    20.0      USD
2      AUD        1.361525     5.1      EUR
3      CAD        1.307768     3.5      USD
4      JPY      105.717997     8.0      GBP

whereas 'currency' and 'currency_value' are the forex references for the conversion against USD (USD is set to 1).

I want to convert the values in the 'amount' columns when the respective currency in 'curr_fin' is different from USD, because USD doesn't have to be converted. Please note that 'amount' and 'curr_fin' columns are much longer than 'currency' and 'currency_value' columns.

I transformed the forex reference into a dictionary for the iteration and then converted the values to list but probably it wasn't necessary:

    curr_dict = dict(zip(df['currency'], df['currency_value']))

    amounts = df['amount']
    lists = list(curr_dict.values())

but I got problems for the iterations, that it's not working. How is it possible to do such operation between columns?

    for forex in lists:
        if df['currency'] == df['curr_fin']:
            df['amount'] / df['currency']
            print(forex)
anky
  • 74,114
  • 11
  • 41
  • 70
Steven
  • 63
  • 2
  • 12
  • 2
    [Please don't post images of code/data (or links to them)](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question) – anky Sep 08 '20 at 17:12
  • @anky ok, what's the best way to show an excel table? – Steven Sep 08 '20 at 17:14
  • 1
    Post the output of something like `df.head()` instead – C.Nivs Sep 08 '20 at 17:16
  • 1
    thank you for the willingness to improve the question, you can check [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) it is really a good read , I usually copy the data , and do `d = pd.read_clipboard().to_dict()` and post `d` , of course you can try to keep the data as short as possible like @C.Nivs says – anky Sep 08 '20 at 17:16
  • @anky thanks for formatting. How can I format a table like that? I copied the output from spyder – Steven Sep 08 '20 at 17:24
  • 1
    @Steven copy the data and press `Ctrl+K` , [this](https://meta.stackoverflow.com/questions/251361/how-do-i-format-my-code-blocks) is a good read , your question will be complete if you can also post a expected output based on the input. – anky Sep 08 '20 at 17:26

1 Answers1

1

I think you are combining two DataFrames into one. currency and currency_value should be its own DataFrame. And the other two make up another.

Any how, using your original dataframe:

# Extract the exchange rate into its own series
fx = df[['currency', 'currency_value']].dropna().set_index('currency')['currency_value']
fx['USD'] = 1

# Perform the conversion
result = df['amount'] / df['curr_fin'].map(fx)
Code Different
  • 90,614
  • 16
  • 144
  • 163
  • 1
    pandas / numpy have lot of vectorized function that handles iterations extremely efficiently. You should prefer them over loop whenever possible – Code Different Sep 08 '20 at 17:45