How do I format certain columns one way and certain rows another way in python?-CodePudding

My question essentially is:

I have 3 major columns and 4 rows
The Delta column ALWAYS needs to be formatted as a whole number percent ex: 35%
Toyota & Honda Sales need to be formatted differently depending on the row
- Spend and Revenue need to be $XXX,XXX ex: $100,000
- Sale count needs to be a whole number XXX,XXX ex: 500
- Present-Value/Sale needs to always be percent ex: 35%

Put another way, I have one column that has a single formatting regimen, but two others that have variable formatting depending on row. Any idea for this?

#This is what I have to start

data = {'Toyota Sales Performance': [500000.0000, 150000.0000, 100.0000, .2500],
        'Honda Sales Performance': [750000.0000, 100000.0000, 200.0000, .3500], 
        'Delta': [.25, .35, .50, .75]}

df = pd.DataFrame(data, index=['Total Spend',
                               'Total Revenue',
                               'Total Sale Count',
                               'Present-Value/Sale'])
df

What I would like to see

data2 = {'Toyota Sales Performance': ['$500,000', '$150,000', 100, '25%'], 
        'Honda Sales Performance': ['$750,000', '$150,000', 200, '35%'], 
        'Delta': ['25%', '35%', '50%', '75%']}
df2 = pd.DataFrame(data2, index=['Total Spend',
                               'Total Revenue',
                               'Total Sale Count',
                               'Present-Value/Sale'])
df2

CodePudding user response：

You can use apply() to run own function on every column.

import pandas as pd

data = {
    'Toyota Sales Performance': [500000.0000, 150000.0000, 100.0000, .2500],
    'Honda Sales Performance': [750000.0000, 100000.0000, 200.0000, .3500], 
    'Delta': [.25, .35, .50, .75]
}

df = pd.DataFrame(data, index=['Total Spend',
                               'Total Revenue',
                               'Total Sale Count',
                               'Present-Value/Sale'])

#df['Delta'] = df['Delta'].apply(lambda val: f'{val:.0%}')
df['Delta'] = df['Delta'].apply('{:.0%}'.format)

def convert_column(col):
    return pd.Series({
        'Total Spend': "${:,}".format(int(col['Total Spend'])),
        'Total Revenue': "${:,}".format(int(col['Total Revenue'])),
        'Total Sale Count': int(col['Total Sale Count']),
        'Present-Value/Sale': "{:.0%}".format(col['Present-Value/Sale']),
    })

cols = ['Toyota Sales Performance', 'Honda Sales Performance']
df[cols] = df[cols].apply(convert_column, axis=0)

print(df)

Result:

                   Toyota Sales Performance Honda Sales Performance Delta
Total Spend                        $500,000                $750,000   25%
Total Revenue                      $150,000                $100,000   35%
Total Sale Count                        100                     200   50%
Present-Value/Sale                      25%                     35%   75%