CODEWITHSUNDEEP
pythonregexpandasstring-formatting
Martin Petrov
Martin Petrov

Reputation: 323

How to find number within a string in dataframe and re-format that number using thousands separator?

I have the below example

df = pd.DataFrame({'City': ['Houston', 'Austin', 'Hoover','NY','LA'],
                   'Rules': ['ACH_CM > 28419581.51 and AMT_PM > 30572998.00 and AMT_PPM > 30572998.00 and AMT_CM > 30572998.00'
                             , 'MAA_PM and _AMT_PPM > 30572998.00 and _AMT_PM > 16484703.01 and AMT_CM > 28419581.51'
                             , 'MAA_PM and AMT_CM > 284 and AMT_PM > 30572998.00 and AMT_PPM > 30572998.00 and AMT_PPPM > 30572998.00 and ACH_AMT_PPM > 16484703.01'
                            ,'MAA_CM'
                            ,'_AMT_PPM > 30572.00']},columns=['City', 'Rules'])

Desired output:

City    Rules
Houston ACH_CM > 28,419,581.51 and AMT_PM > 30,572,998.00 and AMT_PPM > 30,572,998.00 and AMT_CM > 30,572,998.00
Austin  MAA_PM and _AMT_PPM > 30,572,998.00 and _AMT_PM > 16,484,703.01 and AMT_CM > 28,419,581.51
Hoover  MAA_PM and AMT_CM > 284 and AMT_PM > 30,572,998.00 and AMT_PPM > 30,572,998.00 and AMT_PPPM > 30,572,998.00 and ACH_AMT_PPM > 16,484,703.01
NY      MAA_CM
LA      AMT_PPM > 30,572.00

I believe I should be using "{0:,.0f}".format but not sure how to apply it.

Upvotes: 2

Views: 104

Answers (3)

ipramusinto
ipramusinto

Reputation: 2648

Try this

df['Rules'] = df.Rules.apply(lambda x: re.sub("\d+\.\d+", my_func, x))

where my_func is defined below:

def my_func(matchobj):
    f = float(matchobj.group(0))
    return "{0:,.2f}".format(f)

Upvotes: 1

Niyas
Niyas

Reputation: 515

This should work.

def _format(x):
    unformatted = re.findall("\d+\.\d+", df['Rules'].iloc[0])
    formatted = ['{:,}'.format(float(x)) for x in unformatted]
    for i in range(len(unformatted)):
        x = x.replace(unformatted[i], formatted[i])
    return x

df['Rules'] = df['Rules'].map(_format)

Upvotes: 1

merik
merik

Reputation: 95

This might be useful:

if len("%0.f" % floating.number) >= 5:
    print ('do something')

Upvotes: 2

Related Questions