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.
