Python Pandas return DataFrame where value count is above a set number

Question

I have a Pandas DataFrame, and I want to return the DataFrame only if that Customer Number occurs more than a set number of times.

Here is a sample of the DataFrame:

114  2017-04-26      1       7507       34      13
115  2017-04-26      3      77314       41      14
116  2017-04-27      7       4525      190     315
117  2017-04-27      7       5525       67      94
118  2017-04-27      1       6525       43     378
119  2017-04-27      3       7415       38      27
120  2017-04-27      2       7613       47      10
121  2017-04-27      2      77314        9       3
122  2017-04-28      1        227       17       4
123  2017-04-28      8       4525      205     341
124  2017-04-28      1       7415       31      20
125  2017-04-28      2      77314        8       2

And here is if that customer occurs more than 5 times, using this code:

print(zip_data_df['Customers'].value_counts()>5)

7415      True
4525      True
5525      True
77314     True
6525      True
4111      True
227       True
206      False
7507     False
7613     False
4108     False
3046     False
2605     False
4139     False
4119     False

Now I expected if I did this:

print(zip_data_df[zip_data_df['Customers'].value_counts()>5])

It would show me the whole DataFrame for customers that occur more than 5 times, but I got a Boolean error. I realize why it gives me an error now: one DataFrame is just telling me if that customer number occurs more than 5 times or not, and the other is showing me every time that customer number occurs. They don't match in length. But how do I get it so the dataframe will only return records where that customer occurs more than 5 times?

I'm sure there is some simple answer I'm overlooking, but I appreciate any help you can get me.

Answer 1

So the issue here is indexing: value_counts() returns a Series indexed on 'Customers,' while zip_data_df seems to be indexed on something else. You can do something like:

cust_counts = zip_data_df['Customers'].value_counts().rename('cust_counts')

zip_data_df = zip_data_df.merge(cust_counts.to_frame(),
                                left_on='Customers',
                                right_index=True)

From there, you can select conditionally from zip_data_df like so:

zip_data_df[zip_data_df.cust_counts > 5]