29

Let's say I have the Pandas dataframe with columns of different measurement attributes and corresponding measurement values.

ID     Parameter     Value
0      'A'           4.3
1      'B'           3.1
2      'C'           8.9
3      'A'           2.1
4      'A'           3.9
.      .             .
.      .             .
.      .             .
100    'B'           3.8

How can I filter this dataframe to only have measurements that appear more than X number of times? For example, for this dataframe I want to get all rows with more than 5 measurements (lets say only parameters 'A' and 'B' appear more than 5 times) to get a dataframe like below. 

ID     Parameter     Value
0      'A'           4.3
1      'B'           3.1
3      'A'           2.1
.      .             .
.      .             .
.      .             .
100    'B'           3.8
Char
  • 1,635
  • 7
  • 27
  • 38
  • Possible duplicate of [Pandas: Selecting rows based on value counts of a particular column](https://stackoverflow.com/questions/36166090/pandas-selecting-rows-based-on-value-counts-of-a-particular-column) – m0nhawk Feb 05 '18 at 17:47

5 Answers5

40

You can use value_counts + isin -

v = df.Parameter.value_counts()
df[df.Parameter.isin(v.index[v.gt(5)])]

For example, where K = 2 (get all items which have more than 2 readings) -

df

   ID Parameter  Value
0   0         A    4.3
1   1         B    3.1
2   2         C    8.9
3   3         A    2.1
4   4         A    3.9
5   5         B    4.5

v = df.Parameter.value_counts()
v

A    3
B    2
C    1
Name: Parameter, dtype: int64

df[df.Parameter.isin(v.index[v.gt(2)])]

   ID Parameter  Value
0   0         A    4.3
3   3         A    2.1
4   4         A    3.9
cs95
  • 379,657
  • 97
  • 704
  • 746
16

Use transform + size with boolean indexing:

df[df.groupby('Parameter')['Parameter'].transform('size') > 5]
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
6

By using filter 

df.groupby('Parameter').filter(lambda x : x['Parameter'].shape[0]>=5)
BENY
  • 317,841
  • 20
  • 164
  • 234
3

You can use value_counts() to get the rows in a DataFrame with their original indexes where the values in for a particular column appear more than once with Series manipulation

freq = DF['attribute'].value_counts()
# index of items that appear more than once
items = freq[freq>1].index 
more_than_1_df = DF[DF['attribute'].isin(items)]
more_than_1_df
Jerrold110
  • 191
  • 1
  • 4
2

Loc with count could also work

df.loc[df.Parameter.isin((df.groupby('Parameter').size().Value >= 5).index)]
Espoir Murhabazi
  • 5,973
  • 5
  • 42
  • 73