Change data type from object to string in pandas-CodePudding

This seems very simple but I just can't figure it out.

I have a dataframe with an amount column in GBpence, Euro and USD. I need a new column with the amount in GBP, the only way I can think of doing this is to first convert the amount field to string, separate the amount from the currency sign and then use numpy to perform a conditional calculation.

df = pd.DataFrame({'date': ['2018-11-22','2018-11-23','2018-11-24'],
               'amount': ['3.80p','$4.50','\N{euro sign}3.40'], 
               'usd-gbp':['0.82','0.83','0.84'], 
               'eur-gbp':['0.91','0.92','0.93']})

I am trying to convert the amount column to string so I can extract the currency and float amount but it just converts all the rows into the same string.

df['new'] = str(df['amount'])

Expected output would just be the amount values in string format so I can perform slicing on them.

Any help would be appreciated. Thanks.

CodePudding user response：

You can use replace to replace currency symbol by '0.82*' and '0.91*' then evaluate the operation with pd.eval:

to_gbp = {'p': '*1', '\$': '0.82*', '€': '0.91*'}
df['gbp'] = pd.eval(df['amount'].replace(to_gbp, regex=True))
print(df)

# Output:
  amount usd-gbp eur-gbp    gbp
0  3.80p    0.82    0.91  3.800
1  $4.50    0.82    0.91  3.690
2  €3.40    0.82    0.91  3.094

Detail about replace:

>>> df['amount'].replace(to_gbp, regex=True)
0       3.80*1
1    0.82*4.50
2    0.91*3.40
Name: amount, dtype: object

Update

I didnt mention that the exchange rates will differ based on the date. I have updated the question so show this. Is this still possible with 'replace'?

Create a custom function and apply it to each row:

def convert(row):
    d = {'$': 'usd-gbp', '€': 'eur-gbp'}
    c = row['amount'][0]
    return float(row['amount'][1:]) * float(row[d[c]]) \
               if c in d else float(row['amount'][:-1])

df['gbp'] = df.apply(convert, axis=1)
print(df)

# Output:
         date amount usd-gbp eur-gbp    gbp
0  2018-11-22  3.80p    0.82    0.91  3.800
1  2018-11-23  $4.50    0.83    0.92  3.735
2  2018-11-24  €3.40    0.84    0.93  3.162