1

I am trying to assign values to rows on a certain column in the DataFrame,

exchange_rates = {'TRY': 3.4155, 'NZD': 1.3683, 'HUF': 254.11, 'RUB': 57.185,
    'DKK': 6.1693, 'SEK': 7.9095, 'SGD': 1.337, 'CAD': 1.2087, 'JPY': 107.38,
    'AUD': 1.2339, 'HRK': 6.1621, 'CHF': 0.94569, 'ILS': 3.5163, 'THB': 33.1,
    'GBP': 0.75678, 'BGN': 1.6217, 'KRW': 1130.6, 'NOK': 7.7192, 'CZK': 21.641,
    'ZAR': 12.862, 'PHP': 50.809, 'MYR': 4.1945, 'EUR': 0.82919, 'MXN': 17.693,
    'CNY': 6.4615, 'HKD': 7.8076, 'RON': 3.8149, 'INR': 63.784, 'PLN': 3.5269,
    'IDR': 13163.0, 'BRL': 3.0892}

unique_currencies = ['CAD', 'CHF', 'CNY', 'EUR', 'JPY', 'GBP', 'SGD']

for cur in unique_currencies:
    cur_indices = df[df['currency'] == cur].index.tolist()
    for row_index in cur_indices:
        df.loc[row_index, 'common_amount'] = df.loc[row_index, 'amount'] / exchange_rates[cur]

So the code gets the indices of rows with a certain currency and then assigns value calculated based on its exchange rate to 'USD' to column common_amount.

I am wondering what is the best way to do this?

UPDATE

an example DataFrame,

   currency   amount
50      CAD   410.85
51      CAD   1441.68
17625   JPY   2797856.0
17663   JPY   1440.0
16734   CNY   27840.00
54546   CNY   273269.53
17654   GBP   384.0
17655   GBP   526.0
16732   CHF   474.7
16733   CHF   195173.3
Improve this question
3
  • Can you add some sample data? Commented Sep 11, 2017 at 11:35
  • 1
    @jezrael have added an example df Commented Sep 11, 2017 at 12:52
  • Thank you, I remove comment about data from my answer. Commented Sep 11, 2017 at 12:54

1 Answer 1

Reset to default
2

I think you need divide by div column created by map currency by dict:

print (df['currency'].map(exchange_rates))
50         1.20870
51         1.20870
17625    107.38000
17663    107.38000
16734      6.46150
54546      6.46150
17654      0.75678
17655      0.75678
16732      0.94569
16733      0.94569
Name: currency, dtype: float64

df['common_amount1'] = df['amount'].div(df['currency'].map(exchange_rates))
print (df)
      currency      amount  common_amount  common_amount1
50         CAD      410.85     339.910648      339.910648
51         CAD     1441.68    1192.752544     1192.752544
17625      JPY  2797856.00   26055.652822    26055.652822
17663      JPY     1440.00      13.410318       13.410318
16734      CNY    27840.00    4308.597075     4308.597075
54546      CNY   273269.53   42291.964714    42291.964714
17654      GBP      384.00     507.412987      507.412987
17655      GBP      526.00     695.050081      695.050081
16732      CHF      474.70     501.961531      501.961531
16733      CHF   195173.30  206381.901046   206381.901046
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

what if the df also contains rows with USD as their currency, what should I do with map; should I append dict exchange_rates with USD:1.0, so far I just df['common_amount'] = df[df['currency'] == 'USD']['amount'], before the loop begins

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.