Apply function rowwise to pandas dataframe while referencing a column

Question

I have a pandas dataframe like this:

df = pd.DataFrame({'A': [2, 3], 'B': [1, 2], 'C': [0, 1], 'D': [1, 0], 'total': [4, 6]})

   A  B  C  D  total
0  2  1  0  1      4
1  3  2  1  0      6

I'm trying to perform a rowwise calculation and create a new column with the result. The calculation is to divide each column ABCD by the total, square it, and sum it up rowwise. This should be the result (0 if total is 0):

   A  B  C  D  total  result
0  2  1  0  1      4   0.375
1  3  2  1  0      6   0.389

This is what I've tried so far, but it always returns 0:

df['result'] = df[['A', 'B', 'C', 'D']].apply(lambda x: ((x/df['total'])**2).sum(), axis=1)

I guess the problem is df['total'] in the lambda function, because if I replace this by a number it works fine. I don't know how to work around this though. Appreciate any suggestions.

sammywemmy · Accepted Answer · 2020-08-12 12:22:15Z

4

A combination of div, pow and sum can solve this :

df["result"] = df.filter(regex="[^total]").div(df.total, axis=0).pow(2).sum(1)
df

A   B   C   D   total   result
0   2   1   0   1   4   0.375000
1   3   2   1   0   6   0.388889

answered Aug 12, 2020 at 12:22

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ayoub ZAROU · Accepted Answer · 2020-08-12 13:02:08Z

4

you could do

df['result'] = (df.loc[:, "A": 'D'].divide(df.total, axis=0) ** 2).sum(axis=1)

edited Aug 12, 2020 at 13:02

answered Aug 12, 2020 at 12:25

Ayoub ZAROU

2,4178 silver badges20 bronze badges

2 Comments

Ruthger Righart Over a year ago

I am running your code with Pandas 1.0.0. and I get NAN. Could you check?

Ayoub ZAROU Over a year ago

my bad, I didn't specify the axis used to compute the sum, thanks for pointing that out

Collectives™ on Stack Overflow

Apply function rowwise to pandas dataframe while referencing a column

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related