Converting classes to nearest group with maximum vote

user_9939133 0
Viewed 27

I have a time series data with a column of a multiclass object, I would like to convert the object happening in less than two instances to the nearest bigger group. Here is an example of the data frame. I wish to convert 'No' appears at the 4th row to 'Yes' and similarly, 'Yes' in the 16th to 'No'.

df = pd.DataFrame(data = {'A': ['Yes', 'Yes', 'Yes', 'No', 'Yes', 'Yes', 'Probable',
                            'Probable', 'Probable', 'Yes', 'Yes', 'Yes',
                            'No', 'No','No', 'Yes', 'No','No'],
                          'Counter': [1, 2, 3, 1, 1, 2, 1, 2, 3, 1, 2, 3, 1, 2,
                                  3, 1, 1, 2]})

Does anyone could help me to define a loop or function? Thank you in advance.

pyspark pandas apache-spark-sql dataframe
1 Answers

The idea is to identify groups of repeated categories and calculate total count per group; then for groups <2 count change their categories to the nearest (which we take to be its preceding group with >=2 total count)

This identifies nearby groups and calculates the sum per group, putting the result in gc (for 'group counter'):

group_id = df['A'].ne(df['A'].shift()).cumsum()
df['gc'] = df.groupby(group_id, group_keys = False)['Counter'].transform(sum)
df

output:

    A           Counter    gc
--  --------  ---------  ----
 0  Yes               1     6
 1  Yes               2     6
 2  Yes               3     6
 3  No                1     1
 4  Yes               1     3
 5  Yes               2     3
 6  Probable          1     6
 7  Probable          2     6
 8  Probable          3     6
 9  Yes               1     6
10  Yes               2     6
11  Yes               3     6
12  No                1     6
13  No                2     6
14  No                3     6
15  Yes               1     1
16  No                1     3
17  No                2     3

Now we replace categories with NaNs for those where gc is < 2, and fill forward:

import numpy as np
df.loc[df['gc'] <2,'A'] = np.NaN
df.fillna(method = 'ffill').drop(columns = 'gc')

output:

    A           Counter
--  --------  ---------
 0  Yes               1
 1  Yes               2
 2  Yes               3
 3  Yes               1
 4  Yes               1
 5  Yes               2
 6  Probable          1
 7  Probable          2
 8  Probable          3
 9  Yes               1
10  Yes               2
11  Yes               3
12  No                1
13  No                2
14  No                3
15  No                1
16  No                1
17  No                2
user_145514260
Related