Pandas percent difference from mean

user_4367371 0
Viewed 127

I have a pandas data frame with many columns and for each column I would like to generate a new columns where the result is the percent difference of the value in relation to the mean of that column, as seen in the example below:

d = {'var1': [1, 2], 'var2': [3,4]}
df = pd.DataFrame(data=d)
df
    var1   var2  
0     1     3     
1     2     4

result:

    var1   var2  var1_avg  var2_avg
0     1     3     -0.33     -0.14
1     2     4      0.33      0.14

I am aware of how to find the mean of the column and then compute the percent difference, but only for a singe column, as seen below:

df['var1_avg'] = (df.var1 - df.var1.mean()) / df.var1.mean()

However, I have 100's of columns and would like a way where I can apply this to each column and append the "_avg" to each new column name.

pandas dataframe python
5 Answers

You can use pandas.concat and pandas.DataFrame.add_suffix:

>>> pd.concat([df, ((df - df.mean())/df.mean()).add_suffix("_avg")], axis = 1)
   var1  var2  var1_avg  var2_avg
0     1     3 -0.333333 -0.142857
1     2     4  0.333333  0.142857
user_137432330

Given that you have hundreds of columns, I opted to avoid using a for loop when declaring the new columns (that is, I did not use for loops in the calculation process itself). This is the most efficient solution I could think of.

new_cols = [col + '_avg' for col in df.columns]
df[new_cols] = df.apply(lambda x: (x - x.mean()) / x.mean())
user_97958170

Out of curiosity I timed all the solutions given so far (other than @moys, which got edited to be essentially the same as mine [now deleted as it is a poor result and the code is duplicated in this answer]). The results were quite consistent for 10 iterations over a 100x100 dataframe, with @PabloC solution far and away the best; about 35x faster than mine, 120x faster than @Arturo and 2000x faster than @unltd_J:

nick     : 3.6475648000000005
pablo    : 0.09790619999999972
unltd_J  : 189.9798728
arturo   : 11.067582600000009

Testing code:

import timeit
import pandas as pd
import numpy as np
from pandas.testing import assert_frame_equal

def nick(df):
    for col in df.columns:
        df[f'{col}_avg'] = (df[col] - df[col].mean()) / df[col].mean()
    return df
    
def pablo(df):
    pd.concat([df, ((df - df.mean())/df.mean()).add_suffix("_avg")], axis = 1)
    return df

def unltd_J(df):
    for col in df.columns:
        df[f'{col}_avg'] = [(x - np.mean(df[col])) / np.mean(df[col]) for x in df[col]]
    return df
    
def arturo(df):
    new_cols = [col + '_avg' for col in df.columns]
    df[new_cols] = df.apply(lambda x: (x - x.mean()) / x.mean())
    return df
    
size = 100
df = pd.DataFrame(np.random.randint(0,100,size=(size, size)), columns=['var' + str(i) for i in range(size)])
print(df)

assert_frame_equal(nick(df), pablo(df))
assert_frame_equal(nick(df), unltd_J(df))
assert_frame_equal(nick(df), arturo(df))

for fn in [nick, pablo, unltd_J, arturo]:
    name = fn.__name__
    t = timeit.timeit(f'{name}(df)', setup='''
import pandas as pd
import numpy as np
''', number=10, globals=globals())
    print(f'{name:8} : {t}')
user_94737640

You can do something like this. Here i am just multiplying all the "Var" column values by 3, you can use your logic of calculating the value you want. If you can provide the logic that you are using, I can update the code appropriately.

for col in df.columns:
        if 'var'in col:
            df[col+'_avg'] = (df[col] - df[col].mean()) / df[col].mean()

Output

var1    var2    var1_avg    var2_avg
1         3     -0.333333   -0.142857
2         4     0.333333    0.142857
user_112320910

You are basically just making a new list of values for each column you have in the df and adding it to the columns.

for col in df.columns:

    df[f'{col}_avg'] = [(x - np.mean(df[col])) / np.mean(df[col]) for x in df[col]]

output

    var1    var2    var1_avg    var2_avg
   0   1       3     -0.3333     -0.1428
   1   2       4      0.3333      0.1428
user_130637550
Related