Remove group of empty or nan in pandas groupby

user_6244094 0
Viewed 3566

In a dataframe, with some empty(NaN) values in some rows - Example below

s = pd.DataFrame([[39877380,158232151,20], [39877380,332086469,], [39877380,39877381,14], [39877380,39877383,8], [73516838,6439138,1], [73516838,6500551,], [735571896,203559638,], [735571896,282186552,], [736453090,6126187,], [673117474,12196071,], [673117474,12209800,], [673117474,618058747,6]], columns=['start','end','total'])

When I groupby start and end columns

s.groupby(['start', 'end']).total.sum()

the output I get is 

start      end
39877380   39877381    14.00
           39877383     8.00
           158232151   20.00
           332086469     nan
73516838   6439138      1.00
           6500551       nan
673117474  12196071      nan
           12209800      nan
           618058747    6.00
735571896  203559638     nan
           282186552     nan
736453090  6126187       nan

I want to exclude all the groups of start where all values with end is 'nan' - Expected output - 

start      end
39877380   39877381    14.00
           39877383     8.00
           158232151   20.00
           332086469     nan
73516838   6439138      1.00
           6500551       nan
673117474  12196071      nan
           12209800      nan
           618058747    6.00

I tried with dropna(), but it is removing all the nan values and not nan groups.

I am newbie in python and pandas. Can someone help me in this? thank you

pandas-groupby pandas python
1 Answers

In newer pandas versions is necessary use min_count=1 for missing values if use sum:

s1 = s.groupby(['start', 'end']).total.sum(min_count=1)
#oldier pandas version solution
#s1 = s.groupby(['start', 'end']).total.sum()

Then is possible filter if at least one non missing value per first level by Series.notna with GroupBy.transform and GroupBy.any, filtering is by boolean indexing:

s2 = s1[s1.notna().groupby(level=0).transform('any')]
#oldier pandas version solution
#s2 = s1[s1.notnull().groupby(level=0).transform('any')]
print (s2)
start      end      
39877380   39877381     14.0
           39877383      8.0
           158232151    20.0
           332086469     NaN
73516838   6439138       1.0
           6500551       NaN
673117474  12196071      NaN
           12209800      NaN
           618058747     6.0
Name: total, dtype: float64

Or is possible get unique values of first level index values by MultiIndex.get_level_values and filtering by DataFrame.loc:

idx = s1.index.get_level_values(0)
s2 = s1.loc[idx[s1.notna()].unique()]
#oldier pandas version solution
#s2 = s1.loc[idx[s1.notnull()].unique()]
print (s2)
start      end      
39877380   39877381     14.0
           39877383      8.0
           158232151    20.0
           332086469     NaN
73516838   6439138       1.0
           6500551       NaN
673117474  12196071      NaN
           12209800      NaN
           618058747     6.0
Name: total, dtype: float64
user_29010020
Related