基于Pandas从交易活动生成月末持仓：统计正余额标的数量

Got it, let's walk through how to calculate the number of issues with a positive balance at each month end using your transaction data df_td. I'll use pandas since you're working with a DataFrame—this approach is efficient and easy to adapt to your actual data.

Step 1: Prep Your Data & Calculate Net Share Changes #

First, make sure your date column is properly formatted as datetime, then convert each transaction's action into a numerical value to compute net share movements:

import pandas as pd

# Ensure date is datetime type
df_td['date'] = pd.to_datetime(df_td['date'])

# Convert action to +/-1 and calculate net shares per transaction
df_td['net_shares'] = df_td['action'].map({'+' : 1, '-' : -1}) * df_td['shares']

Step 2: Compute Cumulative Balances Per Issue #

Sort the data by issue and date to ensure transactions are processed in order, then calculate the running total of shares for each issue:

# Sort to maintain transaction order
df_sorted = df_td.sort_values(['issue', 'date']).reset_index(drop=True)

# Calculate cumulative balance for each issue over time
df_sorted['cumulative_balance'] = df_sorted.groupby('issue')['net_shares'].cumsum()

Step 3: Generate Month End Dates #

Extract all unique months from your transaction data and convert them to the last day of each month (our target dates for balance checks):

# Get all unique months in the data, convert to month-end timestamps
all_month_periods = df_sorted['date'].dt.to_period('M').unique()
month_end_dates = all_month_periods.to_timestamp('M')

Step 4: Map Each Issue to Month End Balances #

We need to get the balance of each issue as of every month end—even if there were no transactions that month (those will default to 0, since no activity means no change from initial 0 balance):

# Create a grid of all issues + all month ends
issues = df_sorted['issue'].unique()
month_issue_grid = pd.MultiIndex.from_product(
    [issues, month_end_dates], 
    names=['issue', 'month_end']
).to_frame(index=False)

# Merge to get the latest balance before/on each month end
df_merged = pd.merge_asof(
    month_issue_grid.sort_values('month_end'),
    df_sorted[['issue', 'date', 'cumulative_balance']].sort_values('date'),
    left_on='month_end',
    right_on='date',
    by='issue',
    direction='backward'  # Grab the most recent transaction before the month end
)

# Fill missing balances (no transactions that month) with 0
df_merged['cumulative_balance'] = df_merged['cumulative_balance'].fillna(0)

Step 5: Count Positive Balance Issues Per Month End #

Finally, group by month end and count how many issues have a balance greater than 0:

# Calculate the count of issues with positive balance each month
monthly_positive_counts = df_merged.groupby('month_end').apply(
    lambda x: (x['cumulative_balance'] > 0).sum()
).reset_index(name='positive_issue_count')

# Optional: Format month end dates for readability
monthly_positive_counts['month_end'] = monthly_positive_counts['month_end'].dt.strftime('%Y-%m-%d')

# View the result
print(monthly_positive_counts)

Key Notes to Adapt This to Your Data #

• Initial Balances: If you have starting balances for some issues (not 0), add a row for each issue with a date before your first month end, action='+', and shares equal to the initial balance.
• Duplicate Transactions: If multiple transactions exist for the same issue on the same date, consider aggregating net_shares by issue and date first to avoid double-counting.
• Edge Cases: This handles months where an issue has no transactions (balance stays at 0, so it won't be counted as positive) and months where the last transaction is exactly on the month end.

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