在Pandas中按指定列比较DataFrame行及多股票价格联动评分统计

Got it, let's break this down into actionable steps with code you can adapt to your data. First, let's confirm the core requirement: you have a list of pandas DataFrames (each for a single stock), with dates as rows and a price metric (I'm assuming daily returns/price changes, since we care about positive/negative values) as columns. For every pair of stocks, you want to:

• Add 1 to a cumulative score if both have positive values on the same date
• Subtract 1 if both have negative values
• Leave the score unchanged for mixed signs or zeros

Here's a practical implementation:

Step 1: Prepare Your Data #

First, we'll align all stock DataFrames to shared dates and combine them into a single table. This ensures we only compare dates where both stocks have data.

import pandas as pd
from itertools import combinations

# Assume your list of stock DataFrames is named `stock_dfs`
# Each DF should have a datetime index and one column (the price change metric)
# If your data is closing prices instead of returns, first calculate daily changes:
# for df in stock_dfs:
#     df['daily_return'] = df['close'].pct_change().dropna()

# Combine all DFs into one, align dates, and drop rows with missing data
combined_df = pd.concat([df for df in stock_dfs], axis=1).dropna()

# Optional: Rename columns to clear stock identifiers (e.g., tickers)
# combined_df.columns = ['AAPL', 'MSFT', 'GOOGL']

Step 2: Calculate Pairwise Cumulative Scores #

We'll generate all unique stock pairs, compute the daily contribution to the score for each pair, then sum those contributions to get the total cumulative score.

# Dictionary to store results: key = (stock1, stock2), value = cumulative score
pair_scores = {}

# Generate all unique unordered pairs (use permutations if you need ordered pairs)
for stock1, stock2 in combinations(combined_df.columns, 2):
    # Get daily values for both stocks
    s1 = combined_df[stock1]
    s2 = combined_df[stock2]
    
    # Calculate daily score contributions
    # +1 for both positive, -1 for both negative, 0 otherwise
    daily_updates = (
        ((s1 > 0) & (s2 > 0)).astype(int)  # Convert boolean to 1/0
        - ((s1 < 0) & (s2 < 0)).astype(int)  # Subtract 1 for both negative
    )
    
    # Sum all daily updates to get cumulative score
    total_score = daily_updates.sum()
    pair_scores[(stock1, stock2)] = total_score

# Convert results to a DataFrame for easier viewing
score_results = pd.DataFrame.from_dict(
    pair_scores,
    orient='index',
    columns=['Cumulative_Score']
)
score_results.index = pd.MultiIndex.from_tuples(
    score_results.index,
    names=['Stock 1', 'Stock 2']
)

print(score_results)

Key Notes & Customizations #

• Handling Zero Values: The current code ignores zeros (they don't affect the score). If you want to treat zeros as positive/negative or add a different rule, adjust the boolean conditions (e.g., s1 >= 0 instead of s1 > 0).
• Ordered vs Unordered Pairs: We use combinations for unordered pairs (e.g., (A,B) is the same as (B,A)). If you need directional scores (though they'll be identical here since the rule is symmetric), use itertools.permutations instead.
• Missing Data: The dropna() call removes dates where either stock has no data. If you need to keep those dates, you'll have to decide how to handle missing values (e.g., fill with 0, but this will alter your score calculations).

Example Output #

If you have two stocks with daily returns like:

The cumulative score for (Stock A, Stock B) would be 1 -1 +0 -1 +1 = 0.

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