Drop groups whose variance is zero

user_10292638 0
Viewed 44

Suppose the next df:

d={'month': ['01/01/2020', '01/02/2020', '01/03/2020', '01/01/2020', '01/02/2020', '01/03/2020'], 
   'country': ['Japan', 'Japan', 'Japan', 'Poland', 'Poland', 'Poland'], 
   'level':['A01', 'A01', 'A01', 'A00','A00', 'A00'],
   'job title':['Insights Manager', 'Insights Manager', 'Insights Manager', 'Sales Director', 'Sales Director', 'Sales Director'],
   'number':[0, 0.001, 0, 0, 0, np.nan],
   'age':[24, 22, 45, np.nan, 60, 32]}


df=pd.DataFrame(d)

The idea is to get the variance for an specific column by group (in this case by: country, level and job title), then select the segments whose variance is below certain threshold and drop them from the original df.

However when applied:

# define variance threshold   
threshold = 0.0000000001 

# get the variance by group for specific column 
group_vars=df.groupby(['country', 'level', 'job title']).var()['number']

# select the rows to drop 
rows_to_drop = df[group_vars<threshold].index

# drop the rows in place
#df.drop(rows_to_drop, axis=0, inplace=True)

The next error arises:

ValueError: Buffer dtype mismatch, expected 'Python object' but got 'long long'

Expected dataframe would drop: Poland A00 Sales Director 0.000000e+00 for all months , as it is a segment with zero-variance.

Is it possible to reindex group_vars in order to drop it from original df?

What am I missing?

pandas dataframe statistics python
1 Answers

You can achieve this with transform

# define variance threshold   
threshold = 0.0000000001 

# get the variance by group for specific column 
group_vars=df.groupby(['country', 'level', 'job title'])['number'].transform('var')

# select the rows to drop 
rows_to_drop = df[group_vars<threshold].index

# drop the rows in place
df.drop(rows_to_drop, axis=0, inplace=True)

Which gives:

        month country level         job title  number   age
0  01/01/2020   Japan   A01  Insights Manager   0.000  24.0
1  01/02/2020   Japan   A01  Insights Manager   0.001  22.0
2  01/03/2020   Japan   A01  Insights Manager   0.000  45.0
user_47003270
Related