Pandas: Formatting mixed object column from Excel into currency and percentages

EA Bubnoff 0
Viewed 20

I need to format a column of pandas object into percentages, currency or strings based on whether it matches a decimal number, is numeric, or neither.

df = pd.DataFrame([[1000, 'Jerry', 'BR1','BR1','Between 0.23% - 0.32%',''], 
                    [1001, 'N/A', 'N/A', 'BR1','0.025',''], 
                    ['N/A', 'N/A', 'BR3', 'BR2','0.25',''],
                    [1003, 'Perry','BR4','BR1','20000','']],
                   columns=['ID', 'Name', 'Branch', 'Member of','Percent, Comment, or Value','Formatting'],
                   dtype='object')

Solution so far:

def format_col(df):
    if str(df['Percent, Comment, or Value']).replace(".", "", 1).isnumeric():
        formatted_val = "{:.0%}". format(float(df['Percent, Comment, or Value']))
    if df['Percent, Comment, or Value'].isnumeric():
        formatted_val = "${:,.2f}".format(float(df['Percent, Comment, or Value']))
    else:
        formatted_val = df['Percent, Comment, or Value']

    return formatted_val

df['Formatting'] = df.apply(format_col, axis=1)

The function correctly identifies the currency value but not the decimal values:

    ID  Name    Branch  Member of   Percent, Comment, or Value  Formatting
    0   1000    Jerry   BR1     BR1     Between 0.23% - 0.32%   Between 0.23% - 0.32%
    1   1001    N/A     N/A     BR1     0.025   0.025
    2   N/A     N/A     BR3     BR2     0.25    0.25
    3   1003    Perry   BR4     BR1     20000   $20,000.00

How do I match the decimal numbers?

pandas python
1 Answers

This is what I ended up with:

def format_col(df, col):
    val = df[col]

    val = re.sub('\.0$', '', str(val))

    if ('.' in str(val)) & str(val).replace(".", "", 1).isdigit():
        formatted_val = "{:.2%}".format(float(val))
    elif str(val) == '0':
        formatted_val = val
    elif str(val).isnumeric():
        formatted_val = "${:,.2f}".format(float(val))
    else:
        formatted_val = val

    return formatted_val

Then apply:

df['Formatting'] = df.apply(format_col, axis=1)

Results:

ID  Name    Branch  Member of   Percent, Comment, or Value  Formatting
1000    Jerry   BR1     BR1     Between 0.23% - 0.32%       Between 0.23% - 0.32%
1001    N/A     N/A     BR1     0.025                       2.50%
N/A     N/A     BR3     BR2     0.25                        25.00%
1003    Perry   BR4     BR1     20000                       $20,000.00

Is there a simpler way?

EA Bubnoff0
Related