CODEWITHSUNDEEP
pythonpython-2.7pandas
vihaa_vrutti
vihaa_vrutti

Reputation: 291

separate number from string and add numbers using pandas

This is my code !!

import pandas as pd
df2 = pd.DataFrame({'A': ['1,008$','4,000$','6,000$','10,00$','8,00$','45 €','45 €']})

Data cleaning is also done. 

    result2 = df2['A'].str.replace(',','.')
    result2 = result2.str.replace('$','')
    result2 = result2.str.replace('€','')
    print (result2)

which gives me output like this

In [11]: result2
Out[11]: 
0    1.008
1    4.000
2    6.000
3    10.00
4     8.00
5       45
6      45 
Name: A, dtype: object

but my objective is to add amount of different currencies together , that is the output should be like this

Your amount in dollar is "29.008"
your amount in euros is "90"

i am not very good at python , please help me!

Upvotes: 1

Views: 109

Answers (1)

piRSquared
piRSquared

Reputation: 294258

Option 1 

df2.A.str.replace(',', '.').str.extract(
    '^(?P<Value>.*)(?P<Currency>\D)$', expand=True
).apply(pd.to_numeric, errors='ignore').groupby('Currency').Value.sum()

Currency
$    29.008
€    90.000
Name: Value, dtype: float64

Option 2 

v = pd.to_numeric(df2.A.str[:-1].str.replace(',', '.'))
c = df2.A.str[-1]

v.groupby(c).sum()

A
$    29.008
€    90.000
Name: A, dtype: float64

Option 3 

d = {'$': 'dollar', '€': 'euro'}

v = pd.to_numeric(df2.A.str[:-1].str.replace(',', '.'))
c = df2.A.str[-1].map(d)

v.groupby(c).sum()

A
dollar    29.008
euro      90.000
Name: A, dtype: float64

Upvotes: 2

Related Questions