Tukukkk

Ok, so here is what I'm trying to do:

I have a DataFrame like this:

data = pd.DataFrame(

{'a' : [1,1,1,2,2,3,3,3],

 'b' : [23,45,62,24,45,34,25,62],

 })

I managed to calculate the mean of column 'a' grouped by the column 'b' by using the following code:

data.groupby('b', as_index=False)['a'].mean()

which resulted in a DataFrame like this:

However, I'd like to only calculate the mean for the values of 'b' that occur more than once in the DataFrame, resulting in a Dataframe like this:

I tried to do it by using the following line:

data.groupby('b', as_index=False).filter(lambda group: len(group)>1)['a'].mean()

but it results in the mean of the lines 1, 2, 4 and 7, which is obviously not what I want.
Can someone please help me to obtain the desired DataFrame and tell me what I'm getting wrong on the use of the filter function?

Thank you!

Grouping on Duplicates

You can do this with data['b'].duplicated(keep=False) to create a boolean mask first:

>>> data[data['b'].duplicated(keep=False)].groupby('b', as_index=False)['a'].mean()                                                                         

    b    a

0  45  1.5

1  62  2.0

data.b.duplicated(keep=False) marks all duplicated occurrences as True and lets you restrict output to those rows:

>>> data.b.duplicated(keep=False)                                                                                                                        

0    False

1     True

2     True

3    False

4     True

5    False

6    False

7     True

Name: b, dtype: bool



>>> data[data.b.duplicated(keep=False)]                                                                                                                  

   a   b

1  1  45

2  1  62

4  2  45

7  3  62

Update: Grouping by Arbitrary Number of Occurrences

Can this solution be generalized to look for an arbitrary number of occurrences? Let's say I wanted to calculate the mean only for values that occurred more than 5 times on the DataFrame.

In this scenario, you need to generate a boolean mask of the same shape as in the example above, but using a slightly different approach.

Here is one way:

>>> vc = data['b'].map(data['b'].value_counts(sort=False))

>>> vc                                                                                                                                                   



0    1

1    2

2    2

3    1

4    2

5    1

6    1

7    2

Name: b, dtype: int64

These are the element-wise counts for each element of b. To get this to a mask (say you want means for only count == 2, which is the same as the above in this example, but could be extended for any int):

mask = vc == 2  # or > 5, in your case

data[mask].groupby('b', as_index=False)['a'].mean()

You can filter before your dataframe via loc before groupby:

df = pd.DataFrame({'a' : [1,1,1,2,2,3,3,3],

                   'b' : [23,45,62,24,45,34,25,62]})



counts = df['b'].value_counts()



res = df.loc[df['b'].isin(counts[counts > 1].index)]

        .groupby('b', as_index=False)['a'].mean()



print(res)



    b    a

0  45  1.5

1  62  2.0

You were pretty close:

data.groupby('b').filter(lambda g:len(g)>1).groupby('b',as_index=False).mean()

results in the answer you were looking for:

    b    a

0  45  1.5

1  62  2.0