CODEWITHSUNDEEP
pythonpandasdataframe
jrjames83
jrjames83

Reputation: 901

Pandas - Apply Style/Formatting Based on Column Name and Value of a Row

import pandas as pd

df2 = pd.DataFrame.from_records(
{'Compared': {0: -0.090909090999999997,
  1: -0.130434783,
  2: -0.10714285699999999,
  3: -0.15966386599999999},
 'LastYR': {0: 5500.0, 1: 115.0, 2: 84.0, 3: 40520.523399999998},
 'METRIC': {0: 'Visits', 1: 'Units', 2: 'Orders', 3: 'Sales'},
 'Today': {0: 5000.0, 1: 100.0, 2: 75.0, 3: 34050.860000000001},
 'region_rollup': {0: 'America', 1: 'America', 2: 'America', 3: 'America'}}

)
df2.head()

How can I apply:

a) a % format to any entire column I would like

b) A currency $ format to any row where the METRIC value is 'Sales'

enter image description here

I have tried the df.style.format() function, which will let me subset columns and apply the % formatting, but I cannot determine what kind of function to write which would allow me to format a column, based on the value of another column (again, "if metric = sales then format today as currency", more or less).

Thanks!

Upvotes: 3

Views: 3263

Answers (1)

niraj
niraj

Reputation: 18218

For first condition, if percentage of only numbers may be following would work too:

df2['%'] = (df2['LastYR']/ df2['LastYR'].sum()) * 100

For second condition, may be you can use, for e.g. if METRIC is Salesthen, divide some other column value, in below if Sales then dividing Today column by 100.0, else keeping same as Today column value:

df2['Currency'] = df2.apply(lambda row: (row['Today'] / 100.0 if row['METRIC'] == 'Sales' else row['Today']), axis=1)

Result:

   Compared      LastYR  METRIC     Today region_rollup          %   Currency
0 -0.090909   5500.0000  Visits   5000.00       America  11.899733  5000.0000
1 -0.130435    115.0000   Units    100.00       America   0.248813   100.0000
2 -0.107143     84.0000  Orders     75.00       America   0.181741    75.0000
3 -0.159664  40520.5234   Sales  34050.86       America  87.669713   340.5086

Update:

Using function and using apply:

def test_function(row):
    if row['METRIC'] == 'Sales':
        return row['Today'] / 100.0 
    else:
        return row['Today']

And using lambda and apply for each row:

df2['Currency'] = df2.apply(lambda row: test_function(row), axis=1)

Update 2:

def test_function(row, column):
    if row['METRIC'] == 'Sales':
        return row[column] / 100.0 
    else:
        return row[column]

df2['%'] = (df2['LastYR']/ df2['LastYR'].sum()) * 100

column_list = ['Today', 'LastYR']
for column in column_list:
    df2[column] = df2.apply(lambda row: test_function(row, column), axis=1)

Upvotes: 2

Related Questions