如何用Pandas迭代处理列中95分位以上值并替换为前后均值？

Let's walk through the issues in your current code and fix them step by step:

Key Problems in Your Current Implementation #

1. Incorrect loop logic in removespikes:
   • When you loop for i in x, i is just the value from the Series, not its index. You can't modify the original Series this way, and checking x <= perc compares the entire Series to the percentile (not individual values).
   • mean(i-1, i+1) is invalid syntax—you need to reference the previous and next elements in the Series, not adjust the value itself. Also, you haven't imported or defined a mean function (use np.mean instead).
2. Not updating the DataFrame:
   • After processing col = removespikes(col), you don't assign this back to df[column], so none of your changes persist in the original dataset.
3. Plotting variable errors:
   • You reference x in plt.plot but haven't defined it (it should be your processed column col). The colline variable is also undefined.

Corrected Code #

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

def removespikes(series):
    # Calculate 95th percentile for the column
    perc = np.percentile(series, 95)
    # Convert to a mutable array for easier in-place modifications
    arr = series.to_numpy()
    
    for idx in range(len(arr)):
        # Skip 0 values and values below/equal to the 95th percentile
        if arr[idx] == 0 or arr[idx] <= perc:
            continue
        # Handle boundary case: first element has no previous value
        if idx == 0:
            arr[idx] = arr[idx+1]
        # Handle boundary case: last element has no next value
        elif idx == len(arr)-1:
            arr[idx] = arr[idx-1]
        # For middle elements, use average of previous and next values
        else:
            arr[idx] = np.mean([arr[idx-1], arr[idx+1]])
    
    # Convert back to Series to keep index alignment with the original DataFrame
    return pd.Series(arr, index=series.index)

# Load your dataset
df = pd.read_excel(r"Dati.xlsx")

# Optional: Cycle through colors for distinct plots
plot_colors = ['blue', 'green', 'red', 'orange', 'purple']

# Iterate over all columns except 'Depth'
for idx, column in enumerate(df.columns):
    if column == 'Depth':
        continue
    
    # Process the column to remove spikes
    processed_col = removespikes(df[column])
    # Update the original DataFrame with cleaned data
    df[column] = processed_col
    
    # Generate and save the plot
    color = plot_colors[idx % len(plot_colors)]  # Cycle through color list
    plt.plot(df['Depth'], processed_col, color=color, marker='o', ms=1.0, linestyle='-', lw=0.2)
    plt.xlabel('Depth')
    plt.ylabel(column)
    plt.title(f'{column} vs Depth (Spikes Removed)')
    plt.savefig(f'{column}.png', dpi=300)  # Higher DPI for clearer plots
    plt.clf()

What Changed? #

• Rewrote removespikes:
  • Converted the Series to a NumPy array for straightforward in-place edits.
  • Uses index positions (idx) to correctly access and modify elements, so we can reference previous/next values properly.
  • Added handling for boundary rows (first and last entries) since they don't have both a previous and next value to average.
• Updated the DataFrame: After processing each column, we assign the cleaned Series back to df[column] so changes are saved.
• Fixed plotting issues: Replaced undefined variables with valid references, added axis labels/titles for clarity, and added color cycling to make plots easier to distinguish.
• Improved robustness: The function now correctly skips 0 values and compares individual elements to the 95th percentile as intended.

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