pandas dataframe pick all rows with row-count greater than > x

Question

How can I select all rows with count of rows >= 2?

I have the following pandas dataframe.

df = pd.DataFrame({"date": ["2000-01-03", "2000-01-04", "2000-01-04", "2000-01-04", "2000-01-04",
                             "2000-01-03", "2000-01-04", "2000-01-05", "2000-01-05", 
                             "2000-01-03", "2000-01-05", "2000-01-05", 
                             "2000-01-04", "2000-01-05"],
                   "sym": ["A", "A", "A", "A", "A" ,"B", "B","B", "B" ,"C", "C", "C", "D", "E"],
                   "val1": [1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 2, 2],
                   "val2": [2, 2, 2, 2, 2, 2, 3, 3, 3, 1, 1, 2, 2, 2]
                   
                  })

df

          date sym  val1  val2
0   2000-01-03   A     1     2
1   2000-01-04   A     1     2
2   2000-01-04   A     1     2
3   2000-01-04   A     1     2
4   2000-01-04   A     1     2
5   2000-01-03   B     2     2
6   2000-01-04   B     2     3
7   2000-01-05   B     2     3
8   2000-01-05   B     2     3
9   2000-01-03   C     3     1
10  2000-01-05   C     3     1
11  2000-01-05   C     3     2
12  2000-01-04   D     2     2
13  2000-01-05   E     2     2

I applied

df.groupby(['date', 'sym'], as_index=False).mean().sort_values(['sym','date'])

to average val1, val2 for a given date for each sym.

       date   sym  val1 val2
0   2000-01-03  A   1.0 2.0
3   2000-01-04  A   1.0 2.0
1   2000-01-03  B   2.0 2.0
4   2000-01-04  B   2.0 3.0
6   2000-01-05  B   2.0 3.0
2   2000-01-03  C   3.0 1.0
7   2000-01-05  C   3.0 1.5
5   2000-01-04  D   2.0 2.0
8   2000-01-05  E   2.0 2.0

Next, I need to select all rows for "sym" with row-count >= 2. in this example, the resulting df would be all rows from sym=A,B,C

Desired output:

       date    sym val1 val2
0   2000-01-03  A   1.0 2.0
3   2000-01-04  A   1.0 2.0
1   2000-01-03  B   2.0 2.0
4   2000-01-04  B   2.0 3.0
6   2000-01-05  B   2.0 3.0
2   2000-01-03  C   3.0 1.0
7   2000-01-05  C   3.0 1.5

I tried combinations of groupby, pivot, count with no luck.

First you say you need to select rows with count >= 50, and then with row-count >= 3 in the end. Ok lets assume you need rows with count >= 3 based on sym but how can A, C appear in your desired output? Can you please clarify? — SomeDude, Apr 28 '21 at 01:14
@SomeDude thanks for pointing it out. my bad on translating real problem to example code. A,B,C are selected b'cos row-count >= 2. — py_newbie, Apr 28 '21 at 01:18

score 2 · Accepted Answer · answered Apr 28 '21 at 01:19

See: How do I filter a pandas DataFrame based on value counts?

import pandas as pd

df = pd.DataFrame({"date": ["2000-01-03", "2000-01-04",
                            "2000-01-04", "2000-01-04",
                            "2000-01-04", "2000-01-03",
                            "2000-01-04", "2000-01-05",
                            "2000-01-05", "2000-01-03",
                            "2000-01-05", "2000-01-05",
                            "2000-01-04", "2000-01-05"],
                   "sym": ["A", "A", "A", "A", "A", "B",
                           "B", "B", "B", "C", "C", "C",
                           "D", "E"],
                   "val1": [1, 1, 1, 1, 1, 2, 2, 2, 2, 3,
                            3, 3, 2, 2],
                   "val2": [2, 2, 2, 2, 2, 2, 3, 3, 3, 1,
                            1, 2, 2, 2]
                   })

df = df \
    .groupby(['date', 'sym'], as_index=False) \
    .mean() \
    .sort_values(['sym', 'date'])

df = df[df['sym'].map(df['sym'].value_counts()) >= 2]
print(df)

Output:

         date sym  val1  val2
0  2000-01-03   A   1.0   2.0
3  2000-01-04   A   1.0   2.0
1  2000-01-03   B   2.0   2.0
4  2000-01-04   B   2.0   3.0
6  2000-01-05   B   2.0   3.0
2  2000-01-03   C   3.0   1.0
7  2000-01-05   C   3.0   1.5

pandas dataframe pick all rows with row-count greater than > x

1 Answers1