Groupby based on condition of pevious value (Pandas Python)

nrasif18 0
Viewed 23

for instance, I have this sample dataframe

    Depth  Fluid

0   235.5  nan
1   236    water
2   236.5  water
3   237    nan
4   237.5  water
5   238    water

Now i want to get the sample data just to be like this

    Min_Depth  Max_Depth  Fluid

0   236        236.5      water
1   237.5      238        water

I have read about pandas groupby and shift method, the logic is if previous value is still the same (water) then group it, and use min and max to get the Depth, but I'm not sure if I can build the code something like what I want. I'm new in Python, I appreciate very much if someone can help me to write the code. Thanks!

dataframe pandas python
2 Answers

You can check with cumsum

x = df.Fluid.ne('water')
out = df[~x].groupby([x.cumsum(),df.Fluid]).agg(max_dp= ('Depth','max'),
                                                min_dp= ('Depth','min')).reset_index(level=1)
out
Out[202]: 
       Fluid  max_dp  min_dp
Fluid                       
1      water   236.5   236.0
2      water   238.0   237.5
BENY0

Another possible solution, based on pandas.unstack:

(df.dropna()
 .assign(Fluid = lambda x: sorted(list(range(len(x)//2)) * 2))
 .set_index(['Fluid', ['minD', 'maxD'] * 2])
 .unstack()
 .droplevel(0, axis=1)
 .reset_index()
 .assign(Fluid = 'water'))

Output:

   Fluid   maxD   minD
0  water  236.5  236.0
1  water  238.0  237.5
userBtwM0
Related