how to convert a dataframe of counts to a probability density function

user_3198568 0
Viewed 4095

Suppose that I have the following observations of integers:

df = pd.DataFrame({'observed_scores': [100, 100, 90, 85, 100, ...]})

I know that this can be used as an input to make a density plot:

df['observed_scores'].plot.density()

but suppose that what I have is a counts table:

df = pd.DataFrame({'observed_scores': [100, 95, 90, 85, ...], 'counts': [1534, 1399, 3421, 8764, ...})

which is cheaper to store than the full observed_scores Series (I have LOTS of observations).

I know it's possible to plot the histogram using the counts, but how do I plot the density plot? If possible, can it be done without having to unstack/unravel the counts table into thousands of rows?

scikit-learn pandas python
2 Answers

IIUC, statsmodels lets you fit a weighted KDE:

from statsmodels.nonparametric.kde import KDEUnivariate

df = pd.DataFrame({'observed_scores': [100, 95, 90, 85],
                   'counts': [1534, 1399, 3421, 8764]})

kde1= KDEUnivariate(df.observed_scores)
kde_noweight = KDEUnivariate(df.observed_scores)
kde1.fit(weights=df.counts, fft=False)
kde_noweight.fit()
plt.plot(kde1.support, kde1.density)
plt.plot(kde_noweight.support, kde_noweight.density)
plt.legend(['weighted', 'unweighted'])

Output:

enter image description here

user_94628290

You can construct the weighted kde manually using scipy. This will match perfectly so long as you specify a bw_method as a scalar. When allowing the default, the fitting becomes inconsitent

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from scipy import stats

np.random.seed(410112)
# Real counts data to check with
df = pd.DataFrame({'observed_scores': np.random.randint(1, 100, 40000)})

# Aggregated to value_counts, which is what you have access to
df1 = df.groupby('observed_scores').size().to_frame('counts').reset_index()

Code

def weighted_kde(y, weights, bw_method=None):
    sample_range = np.nanmax(y) - np.nanmin(y)
    ind = np.linspace(
        np.nanmin(y) - 0.5 * sample_range,
        np.nanmax(y) + 0.5 * sample_range,
        1000)

    gkde = stats.gaussian_kde(y, bw_method=bw_method, weights=weights)

    y = gkde.evaluate(ind)
    
    return ind, y

Check output

bw_method=0.5

fig, ax = plt.subplots()
# Underlying data, with pandas kde
df['observed_scores'].plot.density(ax=ax, bw_method=bw_method, label='pandas density', lw=2)

# From aggregated counts data
ind, y = weighted_kde(df1['observed_scores'], df1['counts'], bw_method=bw_method)
ax.plot(ind, y, label='Manual Weighted KDE', lw=2, linestyle='--')

ax.legend()
plt.show()

enter image description here

user_43333590
Related