CODEWITHSUNDEEP
pythonpandasgroup-bypandas-groupby
Hekd
Hekd

Reputation: 309

Exclude zeros in a column when calculating mean using pandas

I have the following dataframe:

Trip_ID Trip_Trajectory Trip_Distance Trip_Speed
1001    1                15            2
1001    2                10            0
1001    3                20            6

I would like to aggregate the data using Groupby. I am getting this result:

Trip_ID Trip_Distance Trip_Speed
1001    45            2,67

This is the code that I am using now:

dataset_agg = dataset.groupby('Trip_ID').agg({'Trip_Distance': ['sum'], 'Trip_Speed': ['mean']})

But what I need is to exclude zeros in 'Trip_Speed' column to get the following result:

Trip_ID Trip_Distance Trip_Speed
1001    45            4

Thanks in advance

Upvotes: 1

Views: 3242

Answers (1)

cs95
cs95

Reputation: 402523

Replace zeros with NaN, NaNs are ignored during aggregation.

(df.replace(0, np.nan)
   .groupby('Trip_ID', as_index=False)
   .agg({'Trip_Distance': 'sum', 'Trip_Speed': 'mean'}))

   Trip_ID  Trip_Distance  Trip_Speed
0     1001             45         4.0

Upvotes: 2

Related Questions