Python Pandas df, best way to replace $, M and K in currency amount to change to int

user_14316994 0
Viewed 593

I'm doing a personal project to practice pandas, and Beautiful soup, I scraped this info and have it in a pandas df like this:

0        €8.5M
1           €0
2        €9.5M
3          €2M
4         €21M
         ...  
16534    €1.8M
16535    €1.1M
16536    €550K
16537    €650K
16538    €1.1M
Name: Value, Length: 16539, dtype: object
0        €67K
1          €0
2        €15K
3        €11K
4        €13K
         ... 
16534     €3K
16535     €2K
16536     €2K
16537     €7K
16538     €3K
Name: Wage, Length: 16539, dtype: object

So in order to analyse this info I want to clean and transform this data into integers, what I could come up with was this:

df['Wage'] = df['Wage'].apply(lambda x: re.sub('€','',x))
df['Wage'] = df['Wage'].apply(lambda x: re.sub('K','000',x))

df['Value'] = df['Value'].apply(lambda x: re.sub('€','',x))
df['Value'] = df['Value'].apply(lambda x : re.sub('M','00000',x) if (('M' in x) and ('.' in x))else x)
df['Value'] = df['Value'].apply(lambda x : re.sub('[.]','',x))
df['Value'] = df['Value'].apply(lambda x : re.sub('M','000000',x))
df['Value'] = df['Value'].apply(lambda x : re.sub('K','000',x))

df['Wage'] = df['Wage'].astype(int)
df['Value'] = df['Value'].astype(int)

I replaced first the money sign, then check for dots so I can replace the M for 5 zeros, then the remaining M's for 6 zeros, then the K's for 3 zeros and then I do the type change into int. But I feel like this is not a good way of doing this, What do you think? What would be a better way of doing this? I tried creating a function but couldn't do it.

pandas dataframe python regex
2 Answers

Updated Solution:

New solution: Using .replace() and astype() only.
Without relying on pd.eval for formula evaluation:

You can translate M, K to the corresponding magnitudes in exponential format:

K converted to e+03 in scientific notation

M converted to e+06 in scientific notation

(supports integer as well as float numbers in any number of decimal places)

Then, convert the text in scientific notation to float type, followed by casting to integer for final required format, as follows:

df['Value'] = df['Value'].replace({'€': '', ' ': '', 'M': 'e+06', 'K': 'e+03'}, regex=True).astype(float).astype(int)

Input data:

         Value
0        €8.5M
1           €0
2        €9.5M
3          €2M
4         €21M
16534    €1.8M
16535    €1.1M
16536    €550K
16537    €650K
16538    €1.1M

Output:

print(df)

          Value
0       8500000
1             0
2       9500000
3       2000000
4      21000000
16534   1800000
16535   1100000
16536    550000
16537    650000
16538   1100000

Old Solution:

You can convert M, K to formula and then use pd.eval to evaluate the numeric values.

K converted to formula * 1000

M converted to formula * 1000000

In this way, we can support the base values with any number of decimal points (with or without decimal point and how long the fractional part could be). We can just get the correct results from the formulas for all lengths of fractional parts after decimal points.

df['Value'] = df['Value'].str.replace('€', '')
df['Value'] = df['Value'].str.replace('M', ' * 1000000')
df['Value'] = df['Value'].str.replace('K', ' * 1000')
df['Value'] = df['Value'].map(pd.eval).astype(int)

Or simplified code in one line, thanks to @MustafaAydın's suggestion:

df['Value'] = df['Value'].replace({"€": "", "M": "*1E6", "K": "*1E3"}, regex=True).map(pd.eval).astype(int)

Result:

print(df)


          Value
0       8500000
1             0
2       9500000
3       2000000
4      21000000
16534   1800000
16535   1100000
16536    550000
16537    650000
16538   1100000

With the input sample data as follows:

         Value
0        €8.5M
1           €0
2        €9.5M
3          €2M
4         €21M
16534    €1.8M
16535    €1.1M
16536    €550K
16537    €650K
16538    €1.1M

Before the last step, we got:

               Value
0      8.5 * 1000000
1                  0
2      9.5 * 1000000
3        2 * 1000000
4       21 * 1000000
16534  1.8 * 1000000
16535  1.1 * 1000000
16536     550 * 1000
16537     650 * 1000
16538  1.1 * 1000000

Then we feed it to pd.eval for it to evaluate and convert to numeric value (in float) where we can further cast it to integer.

user_150706970

You can replace the values in one regex Series.str.replace() pass using

import pandas as pd
df = pd.DataFrame({'Value':['€8.5M', '€0','€9.5M','€2M','€21M','€1.8M','€1.1M','€550K','€650K','€1.1M']})

def repl(x):
    if not x.group(2):
        return x.group(1)
    if x.group(2) == 'K':
        return str(int(float(x.group(1)) * 1000))
    else:
        return str(int(float(x.group(1)) * 1000000))

>>> df['Value'].str.replace(r'^€?(\d+(?:\.\d+)?)([KM])?$', lambda x: repl(x), regex=True).astype(int)
0     8500000
1           0
2     9500000
3     2000000
4    21000000
5     1800000
6     1100000
7      550000
8      650000
9     1100000
Name: Value, dtype: int32

The ^€?(\d+(?:\.\d+)?)([KM])?$ regex matches

  • ^ - start of string
  • €? - an optional €
  • (\d+(?:\.\d+)?) - Group 1: one or more digits and then an optional sequence of a . and one or more digits
  • ([KM])? - an optional Group 2: K or M
  • $ - end of string.

If there is no K or M, the Group 1 value is returned. If there is K, the number in Group 1 is multiplied by 1000, else, by 1000000.

user_38329700
Related