Pandas conditional mean of column where values greater than or less than zero

Question

I want to calculate the conditional mean of a column: If the values of the row elements are >0 then calculate mean of all such elements and if <0 then calculate the mean of these and store in avgGain and avgLoss.

Input:

Output:

avgGain     avgLoss
 8.275      -8.000

All these calculations should happen using either pandas apply or aggregate functions in a single statement.

Thanks

Did you even try anything before asking people to write code for you? — Marco Bonelli
– Marco Bonelli, Commented Sep 22, 2018 at 11:20
@MarcoBonelli I did try the approaches by more than 2 passes. one creating arrays of >0 vals and <0 and running mean on the same. I wanted pandas function to be able to do this in single pass. That is the reason it was specified that i want it using pandas apply agg. — Aditya Mertia
– Aditya Mertia, Commented Sep 22, 2018 at 11:32

Matt Messersmith · Accepted Answer · 2018-09-22 11:18:28Z

2

IIUC, could do:

# Setup (for reproducibility)
import pandas as pd

data = [-8.000,
    14.400,
    13.150,
     3.050,
    -8.000,
    -8.000,
     3.425,
     7.350,
    -8.000,
    -8.000,
     0.000]
df = pd.DataFrame(data, columns=["ProfitLoss"])

# Calculate the respective means (vectorized)
avgGain = df[df['ProfitLoss'] > 0].mean().values[0]
avgLoss = df[df['ProfitLoss'] < 0].mean().values[0]

# Print outputs to console
print("avgGain:", avgGain)
print("avgLoss:", avgLoss)

outputs:

Matthews-MacBook-Pro:stackoverflow matt$ python test.py
avgGain: 8.275
avgLoss: -8.0

as desired

answered Sep 22, 2018 at 11:18

Matt Messersmith

13.9k8 gold badges54 silver badges57 bronze badges

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

8 Comments

Marco Bonelli Over a year ago

I know reputation is cool but writing code for someone who doesn't even care to put any effort into asking for it is just bad.

Matt Messersmith Over a year ago

@MarcoBonelli There is clear effort here. OP has strung together input/output, and has asked a clear question. There is no code, but there is indeed significant effort.

Marco Bonelli Over a year ago

Please tell me you're joking for god's sake.

Aditya Mertia Over a year ago

@MarcoBonelli I thought of not adding my code coz it would work but won't be efficient and might distract. I wanted an efficient way of achieving this. Not sure why you got offended. There were existing questions of conditional mean but those were w.r.t to conditions in other columns.

Aditya Mertia Over a year ago

I agree with @MattMessersmith. And not everyone can be an expert in every language or library in the world. Programmers come here to learn. Every tool has a learning curve. A one liner code snippet written using a tool designed for it can immensely impact the time to market of the code. I wouldn't try and screw reputation scores of people just because i think a certain way. I'm sure adding code that uses numpy array or for loop wouldn't have helped calm you down :-)

|

Collectives™ on Stack Overflow

Pandas conditional mean of column where values greater than or less than zero

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related