How to split a pandas dataframe on the frequency of values

user_6894782 0
Viewed 1332

I am interested in separating this data frame into 20 smaller dataframes based on the frequency of entries in column B. B has numerical entries, some of these are repeated several times, as can be seen below.

 A (index)              B (Column of interest) 
    0                              1
    1                              2
    2                              2
    3                              2  
    4                              3
   ...                            ... 
  25643                          5238
  25644                          5238 
  25645                          5238
  25646                          5238
  25647                          5238

I am looking to have one data frame for each frequency: 1-10, 11-20, 21-30,...., 191-200. Meaning, 1-10 dataframe contains all the entries from B that appear between 1 and 10 times throughout this dataframe. Similarly, the 11-20 dataframe contains all the entries that appear 11 and 20 times throughout the dataframe.

In the end, I should have 20 dataframes, all of which split this main dataframe.

All I've been able to do is find the distinct number of entries in my desired entries from Column B that correspond to these freeuqncies using the following code:

   df.loc[(df['B'] > 0) & (df['B'] < 11)]
   df.loc[(df['B'] > 10) & (df['B'] < 21)]
                    ...
   df.loc[df['B'] > 190) & (df['B'] < 201)

I have been thinking of using the groupby() function, however, I haven't found a way to group the column entries based on frequency.

Any help is appreciated!

pandas dataframe python
1 Answers

  • Count the occurrence of each value in the dataframe, bin the frequency ranges in groups of 10, and then create a dict of DataFrames for each range.

    1. Use pandas.Series.value_counts() and pandas.Series.map to create a count column in df, which will convey the frequency of the value in the B column.
      • df.B.map(df.groupby('B')['B'].count()) also works, but isn't necessary.
    2. Use pd.cut to bin the frequency ranges
      • labels are used, as they are easier to use as dict keys
        • Without using labels, the dict keys will be an Interval, like [Interval(10, 20, closed='right'), which is cumbersome.
    3. Use pandas.DataFrame.groupby with a dict-comprehension to create a dict of DataFrames based on the bin label.
      • The bin labels will be the dict keys
      • The bins column is categorical, so .groupby will create a group for each label, even if the group is empty, therefore use pandas.DataFrame.empty, so only non-empty groups will be added to the dict of DataFrames.
      • Replace g: dfg with g: pd.DataFrame(dfg.B) to have only column B in the dict.
        • Use dfg.reset_index(drop=True) or pd.DataFrame(dfg.B).reset_index(drop=True) to remove the original index.
import pandas as pd
import numpy as np

# setup test dataframe
np.random.seed(365)
df = pd.DataFrame({'B': np.random.randint(5238, size=(200000))})

# add a counts column to the dataframe
df['counts'] = df.B.map(df.B.value_counts())

# create a bins column for the frequency range
bins = range(0, 201, 10)
labels = range(10, 201, 10)
df['bins'] = pd.cut(df.counts, bins=bins, right=True, labels=labels)

# display(df.head())
      B  counts bins
0  2740      37   40
1  4897      41   50
2  4955      45   50
3   428      31   40
4   226      34   40

# create a dict of dataframes for the non-empty bins
dfd = {g: dfg for g, dfg in df.groupby('bins') if not dfg.empty}

# print dict keys
dfd.keys()
[out]:
dict_keys([20, 30, 40, 50, 60, 70])

# display(dfd[20].head())
          B  counts bins
5350   4986      19   20
5646   4952      20   20
11232  3728      19   20
11707  2819      20   20
13547  3728      19   20
user_77588040
Related