CODEWITHSUNDEEP
pythonpython-3.xpandas
Meisternoob
Meisternoob

Reputation: 171

Pandas, millions and billions

I have a dataframe with this kind of data

1   400.00M 
2   1.94B
3   2.72B
4   -400.00M
5   13.94B

I would like to convert the data to billions so that the output would be something like this

1   0.40 
2   1.94
3   2.72
4   -0.40
5   13.94

Note that dtype: object

Upvotes: 1

Views: 1841

Answers (3)

Richard Guy
Richard Guy

Reputation: 80

Or a list comprehension...

data = {'value': ['400.00M', '1.94B', '2.72B', '-400.00M', '13.94B']}
df = pd.DataFrame(data, index = [1, 2, 3, 4, 5])
df['value'] = [float(n[:-1])/1000 if n[-1:] == 'M' else float(n[:-1]) for n in df['value']]

...though @Andy's answer is more concise.

Upvotes: 0

malvoisen
malvoisen

Reputation: 317

You can use a lambda expression if you know for a fact that you either have only millions or billions:

amount=["400.00M","1.94B","2.72B","-400.00M","13.94B"]
df=pd.DataFrame(amount,columns=["amount"])
df.amount.apply(lambda x: float(x[:-1]) if x[-1]=="B" else float(x[:-1])/1000)

Upvotes: 0

Andy L.
Andy L.

Reputation: 25259

Use replace with dictionary and map pd.eval

Sample df:

Out[1629]:
        val
1   400.00M
2     1.94B
3     2.72B
4  -400.00M
5    13.94B

d = {'M': '*0.001', 'B': ''}

s_convert = df.val.replace(d, regex=True).map(pd.eval)

Out[1633]:
1     0.40
2     1.94
3     2.72
4    -0.40
5    13.94
Name: val, dtype: float64

Upvotes: 4

Related Questions