CODEWITHSUNDEEP
pythonpandasdataframe
William Wade
William Wade

Reputation: 405

How to perform varying operations on DataFrame rows?

I have a data set in which monetary figures are given in a number of different currencies, eg:

ID   X     Currency
1    10    AUD
2    20    NZD
3     5    GBP
4    15    USD

I am looking to create a new column which shows all of the values converted into GBP:

 ID   X     Currency Converted X
 1    10    AUD      5.5
 2    20    NZD      10.4
 3     5    GBP      5
 4    15    USD      12.3

I have a list of tuples containing the currency and exchange rate to GBP:

exchange=[(AUD,0.55),(NZD,0.52),(USD,0.82)]

Which I have then tried to apply via:

 for a,b in exchange:
        df.loc[df['Currency']==a,'Converted X'] = df.apply(lambda row: row['X']*b)

Producing the error:

KeyError: ('X', 'occurred at index Id')

or

for a,b in exchange:
        df.loc[df['Currency']==a,'X'] *= b)

Producing the error:

KeyError: 'Converted X'

I have looked at groupby() but I do not want to summarise the data at this point and would like the operation to be carried out on each row.

Upvotes: 0

Views: 35

Answers (2)

jezrael
jezrael

Reputation: 863166

You can use Series.map with multiple by column X by Series.mul - if need original value X if not exist value in dictionary add parameter fill_value=1:

df['Converted X'] = df['X'].mul(df['Currency'].map(dict(exchange)), fill_value=1)

print (df)
   ID   X Currency  Converted X
0   1  10      AUD          5.5
1   2  20      NZD         10.4
2   3   5      GBP          5.0
3   4  15      USD         12.3

Your solution should be changed:

df['Converted X'] = df['X']
for a,b in exchange:
        df.loc[df['Currency']==a,'Converted X'] = df['X']*b
print (df)
   ID   X Currency  Converted X
0   1  10      AUD          5.5
1   2  20      NZD         10.4
2   3   5      GBP          5.0
3   4  15      USD         12.3

Upvotes: 0

Mark Wang
Mark Wang

Reputation: 2757

To be safe, I would recommend you add GBP exchange rate into the exchange,

exchange.append(('GBP',1))

df['Converted X'] = df['Currency'].map(dict(exchange)).mul(df['X'])

Upvotes: 1

Related Questions