Pandas计算滞后N日股价收益率均值及DataFrame文本粘贴方法

Hey there! Let's break down your three technical needs and the formatting issue step by step:

First, we'll compute the daily return as (current price / lagged price) - 1, then find the average of these returns. Here's a concrete example:

import pandas as pd

# Sample data (replace with your actual DataFrame)
df = pd.DataFrame({
    'date': pd.date_range(start='2024-01-01', periods=30),
    'price': [50, 52, 51, 55, 54, 56, 58, 60, 59, 62, 65, 63, 66, 68, 70, 72, 71, 73, 75, 74, 76, 78, 77, 79, 80, 82, 81, 83, 85, 84]
})

# Ensure date column is datetime type (critical for proper ordering)
df['date'] = pd.to_datetime(df['date'])

# Create a column for 6-day lagged price
df['price_lag6'] = df['price'].shift(6)

# Calculate daily returns for the 6-day lag
df['return_lag6'] = (df['price'] / df['price_lag6']) - 1

# Compute the average return (drop NaNs since first 6 rows have no lagged data)
avg_lag6_return = df['return_lag6'].dropna().mean()
print(f"Average 6-day lag return: {avg_lag6_return:.4f}")

The shift(6) method moves the price column down by 6 rows, aligning each price with its value from 6 days prior. We drop NaNs because the first 6 entries can't have a 6-day lag.

For this, we'll group the DataFrame by your identifier column, sort each group by date (critical for large datasets where order might be inconsistent), compute the 2-day lag returns, and then take the average per group. Here's how:

# Sample large DataFrame with multiple identifiers (replace with your data)
large_df = pd.DataFrame({
    'date': pd.date_range(start='2024-01-01', periods=20).repeat(3),
    'id': ['A', 'B', 'C'] * 20,
    'price': [55.1, 96.1, 17.3, 67.4, 78, 12.1, 57.2, 98.5, 18.2, 69.3, 80.2, 13.5] * 5
})

# Convert date to datetime
large_df['date'] = pd.to_datetime(large_df['date'])

# Define a function to calculate average 2-day lag return for a single group
def get_avg_lag_return(group):
    # Sort group by date to ensure correct lag calculation
    group_sorted = group.sort_values('date')
    # Add 2-day lagged price
    group_sorted['price_lag2'] = group_sorted['price'].shift(2)
    # Calculate returns
    group_sorted['return_lag2'] = (group_sorted['price'] / group_sorted['price_lag2']) - 1
    # Return average of non-null returns
    return group_sorted['return_lag2'].dropna().mean()

# Apply the function to each identifier group and create a new DataFrame
avg_returns_df = large_df.groupby('id').apply(get_avg_lag_return).reset_index(name='avg_2day_lag_return')

print(avg_returns_df)

This will give you a new DataFrame with each identifier and its corresponding average 2-day lag return.

Having trouble copying Excel tables directly? Try these easy methods:

• Method 1: Export from Pandas to Markdown
  If your data is already in a Pandas DataFrame, install the tabulate library (pip install tabulate) then run:

print(your_dataframe.to_markdown(index=False))

Copy the output and paste it directly into Stack Exchange—it'll render as a clean table.

• Method 2: Manually Format Excel Data

1. Select your Excel data (including headers) and copy it.
2. Paste into a plain text editor (like Notepad or VS Code).
3. Replace all tab characters with |, then wrap each line with | at the start and end.
4. Add a separator line right below the headers: |---|---|...| (one --- per column).

Example before formatting:

date    id  price
2024-01-01  A   55.1
2024-01-01  B   96.1

After formatting:

| date       | id  | price |
|------------|-----|-------|
| 2024-01-01 | A   | 55.1  |
| 2024-01-01 | B   | 96.1  |

Paste this markdown into Stack Exchange, and it'll display as a proper table.

• Method 3: Use CSV to Markdown
  Save your Excel sheet as a CSV file, open it, copy the content, and use a simple markdown table converter (but the first two methods are faster and don't require external tools).

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