Calculate Avg based on the specific conditions in another column pandas

user_8901845 0
Viewed 32

I have a dataframe as shown below df:

ID     Age_days    N_30     N_More_365  Group
1      565         60       1000        Good
2      385         2        180         Normal
3      10          4        0           Normal
4      100         0        100         Normal
5      965         0        1200        Good
6      1165        0        3200        Good
7      865         10       4000        Normal

Where I would like to calculate a column called Avg_N_More_365

Explanation:

if df['Age_days'] > 365, df['Avg_N_More_365'] = df['N_More_365']/(df['Age_days']-365)
else df['Avg_N_More_365'] = 0

Expected Output:

 ID     Age_days    N_30     N_More_365  Group      Avg_N_More_365
    1      565         60       1000        Good       5
    2      385         2        180         Normal     9
    3      10          4        0           Normal     0
    4      100         0        100         Normal     0
    5      965         0        1200        Good       2
    6      1165        0        3200        Good       4
    7      865         10       4000        Normal     8
pandas dataframe python-3.x
3 Answers

Firstly create a function that do this for you:-

def func(val):
    if val['Age_days']>365:
        return val['N_More_365']/(val['Age_days']-365)
    else:
        return 0

Now finally use apply() method and chain astype() method to it:-

df['Avg_N_More_365']=df.apply(func,axis=1).astype(int)

Now if you print df you will get your expected output:-

    ID  Age_days    N_30    N_More_365  Group   Avg_N_More_365
0   1   565           60    1000        Good         5
1   2   385           2     180         Normal       9
2   3   10            4     0           Normal       0
3   4   100           0     100         Normal       0
4   5   965           0     1200        Good         2
5   6   1165          0     3200        Good         4
6   7   865           10    4000        Normal       8
user_142898920

Use numpy.where for improve performance:

mask = df['Age_days'] > 365
df['Avg_N_More_365'] = np.where(mask, df['N_More_365']/(df['Age_days']-365), 0)
print (df)
   ID  Age_days  N_30  N_More_365   Group  Avg_N_More_365
0   1       565    60        1000    Good             5.0
1   2       385     2         180  Normal             9.0
2   3        10     4           0  Normal             0.0
3   4       100     0         100  Normal             0.0
4   5       965     0        1200    Good             2.0
5   6      1165     0        3200    Good             4.0
6   7       865    10        4000  Normal             8.0

df = pd.concat([df] * 10000, ignore_index=True)


#Anurag Dabas solution
In [224]: %timeit df['Avg_N_More_365_1']=df.apply(func,axis=1)
959 ms ± 57.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [225]: %timeit df['Avg_N_More_365'] = np.where(df['Age_days'] > 365, df['N_More_365']/(df['Age_days']-365), 0)
1.77 ms ± 8.81 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

#CFreitas answer is wrong, so not tested
user_29010020

This will work:

df.loc[df['Age_days'] > 365, ['Avg_N_More_365']] = df['N_More_365'] / df['Age_days']

The new column will be added to existing dataframe.

user_11389460
Related