How to convert numeric strings such as 200.13K and 1.2M to integer using pandas?

Question

There is a column "Vol" in my df which has values ending with K and M for thousands and millions respectively, these values are "object" in dtype and I need them to be converted in to "double".

Example for the column       what I need it to look like

Vol                          Result
920.81K                      920810
1.28M                        1200000
2.19M                        2190000
443.66K                      443660
682.81K                      682810

Answer 1

There are a few ways to do this. My favourite is using replace and pd.eval. Assuming "Vol" is a string column, you can do:

df['Vol'].replace({'K': '*1e3', 'M': '*1e6'}, regex=True).map(pd.eval)

0     920810.0
1    1280000.0
2    2190000.0
3     443660.0
4     682810.0
Name: Vol, dtype: float64

Depending on the orders of magnitude you need to support, you can modify the replacement dict as needed.