如何基于账户拆分数据构建与原表一致的Tableau频率直方图

Got it, let's break down how to build that frequency histogram step by step—first we sum PnL per account, then bin those total values and count how many accounts fall into each bin. Below are practical implementations using two common tools: Python (Pandas) and SQL.

Python (Pandas) Implementation #

Step 1: Load Data and Calculate Total PnL per Account #

First, we'll group the raw data by account and compute the sum of PnL for each account.

import pandas as pd

# Example raw data (adjust to match your actual data format)
raw_data = pd.DataFrame({
    'pnl': [1, 3, 1, 2, 2, 0, 3, 8, 3, 3, 1, 4, 0, 4, 0, 5, 0, 6, 14],
    'acc': ['a', 'a', 'b', 'b', 'c', 'c', 'd', 'd', 'd', 'd', 'd', 'e', 'e', 'e', 'e', 'f', 'f', 'g', 'g']
})

# Group by account and sum PnL
account_total_pnl = raw_data.groupby('acc')['pnl'].sum().reset_index()
print("Total PnL per Account:")
print(account_total_pnl)

Step 2: Bin the Total PnL Values #

Next, we define the same bin ranges you used before (0-4, 5-9, 10-14) and assign each account's total PnL to the correct bin. We'll also add a catch-all bin for values over 14.

# Define bin ranges and labels
bins = [0, 4, 9, 14, float('inf')]
bin_labels = ['0-4', '5-9', '10-14', '15+']

# Assign bins to each account's total PnL
account_total_pnl['pnl_bin'] = pd.cut(
    account_total_pnl['pnl'],
    bins=bins,
    labels=bin_labels,
    include_lowest=True  # Ensures 0 is included in the first bin
)

Step 3: Count Accounts per Bin and Visualize #

Finally, we count the number of accounts in each bin, and optionally plot the histogram.

# Calculate frequency per bin
histogram_counts = account_total_pnl.groupby('pnl_bin').size().reset_index(name='account_count')
print("\nFrequency Histogram Results:")
print(histogram_counts)

# Optional: Plot the histogram
import matplotlib.pyplot as plt

plt.bar(histogram_counts['pnl_bin'], histogram_counts['account_count'])
plt.xlabel('Total PnL Bins')
plt.ylabel('Number of Accounts')
plt.title('Frequency of Total PnL per Account')
plt.show()

SQL Implementation #

If you're working with a database, you can achieve the same result with nested queries: first compute total PnL per account, then bin and count.

-- Get the final histogram counts
SELECT
    CASE
        WHEN total_pnl BETWEEN 0 AND 4 THEN '0-4'
        WHEN total_pnl BETWEEN 5 AND 9 THEN '5-9'
        WHEN total_pnl BETWEEN 10 AND 14 THEN '10-14'
        ELSE '15+'
    END AS bin,
    COUNT(*) AS account_count
FROM (
    -- Subquery to calculate total PnL per account
    SELECT acc, SUM(pnl) AS total_pnl
    FROM pnl_data
    GROUP BY acc
) AS account_totals
GROUP BY bin
ORDER BY bin;

This query will return the same bin-count structure as the Python approach, ready for you to use in building a histogram.

内容的提问来源于stack exchange，提问作者asb