0
\$\begingroup\$

I'm trying to bin multicolumn Pandas Dataframe, and use the upper limit of the interval for further analysis.

This is my approach:

# Creating a dummy dataframe:

df=pd.DataFrame({'A':np.random.randint(10, size=5),'B':np.random.randint(20, size=5)}).

# Binning dataframe for all columns, and use the upper interval value as a separate column:

df2 = df[['A','B']].apply(lambda x: x.value_counts(bins=np.arange(0, max(df.B)+2, 2), sort=False)).reset_index().rename({'index':'binName'}, axis = 'columns')
df2['binName'] = df2['binName'].map(attrgetter('right')).astype(int)

df2

Result:

binName A   B
0   2   3   0
1   4   1   0
2   6   1   0
3   8   0   3
4   10  0   0
5   12  0   2 

Is there a better solution, I think I'm overdoing it.

I'm aware of pandas cut but it does not work for multiple column (I could be wrong).
Thanks!

\$\endgroup\$

1 Answer 1

1
\$\begingroup\$

Instead of applying value_counts to each column individually, the more common approach in pandas would be to reshape to long format (a single column), perform the binning operations on the Series, then return to wide format.

Reproducible setup:

import numpy as np
import pandas as pd
from numpy.random import Generator, MT19937

rng = Generator(MT19937(10))
size = 5
df = pd.DataFrame({'A': rng.integers(10, size=size),
                   'B': rng.integers(20, size=size)})

df:

   A   B
0  7   9
1  9   3
2  8   8
3  8  10
4  3  17

We can first stack to go to long format, then use groupby apply to use the Series.value_counts function on each group ('A' and 'B' here). unstack allows us to go back from a single column Series to a multiple column DataFrame. Lastly some cleanup, rename_axis to handle the axis names created when unstacking and reset_index to restore the range index.

Since the upperbound for each bin is an already calculated value, we can simply use the bins variable to insert a new column 'binName' to the front of the DataFrame.

bins_size = 2
# Get Maximum value from entire DataFrame
df_max_value = df.max().max()
# Build Bins
bins = np.arange(0, df_max_value + bins_size, bins_size)
df2 = (
    df.stack().droplevel(0)  # Convert to long format
        .groupby(level=0)  # Group by "columns" now in index
        .apply(pd.Series.value_counts, bins=bins, sort=False)
        .unstack(level=0)  # Convert back to wide
        .rename_axis(columns=None)
        .reset_index(drop=True)
)
df2.insert(0, 'binName', bins[1:])

df2:

   binName  A  B
0        2  0  0
1        4  1  1
2        6  0  0
3        8  3  1
4       10  1  2
5       12  0  0
6       14  0  0
7       16  0  0
8       18  0  1

Ideally we should be able to use groupby value_counts with bins:

bins_size = 2
# Get Maximum value from entire DataFrame
df_max_value = df.max().max()
# Build Bins
bins = np.arange(0, df_max_value + bins_size, bins_size)
df2 = (
    df.stack()  # Convert to long format
        .groupby(level=1)
        .value_counts(bins=bins, sort=False)
        .unstack(level=0)  # Convert back to wide
        .rename_axis(columns=None) # cleanup columns
        .reset_index(drop=True) # Restore range index
)
# Add binName column to beginning
df2.insert(0, 'binName', bins[1:])

Unfortunately, there is an issue which causes categorical values to function incorrectly (though this may be resolved in future). Currently, this results in incorrect values being produced:

   binName  A  B
0        2  1  1  # Incorrect
1        4  3  1  # Incorrect
2        6  1  2  # Incorrect
3        8  0  1  # Incorrect
4       10  0  0  # Incorrect
5       12  0  0
6       14  0  0
7       16  0  0
8       18  0  0  # Incorrect

For a less idiomatic, but much faster approach, we can use NumPy instead.

Use np.searchsorted to determine where which bin the value should fall in. Then use the results of binning to calculate the total for each column. Create an empty array of the counts with np.zeros then np.add with ufunc.at on each column to add 1 for each index value stored in a.

Then convert this 2D array into the DataFrame constructor with the came columns as df had. Lastly, insert the bins into the front of the DataFrame.

bins_size = 2
# Get Maximum value from entire DataFrame
df_max_value = df.max().max()
# Build Bins
bins = np.arange(0, df_max_value + bins_size, bins_size, dtype=np.float32)
# Reduce initial lowerbound below 0
# (this is done automatically by Series.value_counts)
bins[0] -= 0.001
# Use searchsorted to determine which bin the value belongs in
a = np.searchsorted(bins, df, side='left')
# Create Empty Count Array of Zeros
c = np.zeros((len(bins), df.columns.size), dtype=np.int32)
# Add bin counts from each column
for i in range(df.columns.size):
    np.add.at(c[:, i], a[:, i], 1)

# Build New DataFrame (excluding unused lower bound bin)
df2 = pd.DataFrame(c[1:], columns=df.columns)
# Add binName to beginning of DataFrame
df2.insert(0, 'binName', bins[1:].astype(int))

df2:

   binName  A  B
0        2  0  0
1        4  1  1
2        6  0  0
3        8  3  1
4       10  1  2
5       12  0  0
6       14  0  0
7       16  0  0
8       18  0  1

For testing, I increased the length of the DataFrame to 5000, with maximum of 100 bins:

from operator import attrgetter

import numpy as np
import pandas as pd
from numpy.random import Generator, MT19937

rng = Generator(MT19937(10))
size = 5000
df = pd.DataFrame({'A': rng.integers(100, size=size),
                   'B': rng.integers(200, size=size)})


def pandas_way(df_):
    bins_size = 2
    df_max_value = df_.max().max()
    bins = np.arange(0, df_max_value + bins_size, bins_size)
    df2 = (
        df_.stack().droplevel(0)
            .groupby(level=0)
            .apply(pd.Series.value_counts, bins=bins, sort=False)
            .unstack(level=0)
            .rename_axis(columns=None)
            .reset_index(drop=True)
    )
    df2.insert(0, 'binName', bins[1:])
    return df2


def numpy_way(df_):
    bins_size = 2
    df_max_value = df_.max().max()
    bins = np.arange(0, df_max_value + bins_size, bins_size, dtype=np.float32)
    bins[0] -= 0.001
    a = np.searchsorted(bins, df_, side='left')
    c = np.zeros((len(bins), len(df_.columns)), dtype=np.int32)
    for i in range(df_.columns.size):
        np.add.at(c[:, i], a[:, i], 1)

    df2 = pd.DataFrame(c[1:], columns=df_.columns)
    df2.insert(0, 'binName', bins[1:].astype(int))
    return df2


def original_way(df_):
    df2 = df_[['A', 'B']].apply(
        lambda x: x.value_counts(bins=np.arange(0, max(df_.B) + 2, 2),
                                 sort=False)).reset_index().rename(
        {'index': 'binName'}, axis='columns')
    df2['binName'] = df2['binName'].map(attrgetter('right')).astype(int)
    return df2


# Sanity Checks that all ways produce the same results
print(original_way(df).eq(numpy_way(df)).all(axis=None))
print(original_way(df).eq(pandas_way(df)).all(axis=None))
print(numpy_way(df).eq(pandas_way(df)).all(axis=None))

Some timings via %timeit:

%timeit numpy_way(df)
1.55 ms ± 16.5 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
%timeit pandas_way(df)
11.1 ms ± 173 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
%timeit original_way(df)
11.4 ms ± 315 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Note: while timings may vary, NumPy is significantly faster than pandas.

\$\endgroup\$
2
  • \$\begingroup\$ Thank you, I learned from you that it is better to start with a long dataframe, So I did so. But, I can not make it work. So, If I started with this pd.DataFrame({'Value':np.random.randint(10, size=10), 'Sample':pd.DataFrame(np.arange(10).reshape(10,1)).applymap(lambda x: np.random.choice(['A', 'B']))[0]}) How Can I reach the same result? \$\endgroup\$
    – Medhat
    Jan 3, 2022 at 21:50
  • 1
    \$\begingroup\$ Almost the same way just specify the group column and value column without stacking (just modifying the first code block): df.groupby('Sample')['Value'].apply(pd.Series.value_counts, bins=bins, sort=False).unstack(level=0).reset_index(drop=True). The max value is just in the Value column now so also need to change df_max_value = df['Value'].max() \$\endgroup\$ Jan 3, 2022 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.