Pandas：列标题转行标签及索引设置与可视化问题

Got it, let's walk through solving your three AFL data tasks step by step. I'll use pandas for data manipulation and seaborn/matplotlib for visualization since they're the go-to tools for this kind of work.

Step 1: Set the Player Column as the DataFrame Index #

First, we'll make the Player column our index so it's easier to reference players later. You can either modify the original DataFrame in-place or create a new one (I prefer the latter to avoid accidental data loss):

# Assuming your raw DataFrame is named `afl_df`
afl_df = afl_df.set_index('Player')

If you want to keep the original Player column in the DataFrame (not just as index), add drop=False to the arguments:

afl_df = afl_df.set_index('Player', drop=False)

Step 2: Transpose to Switch Rounds and Players #

Next, we need to flip the structure so rounds become rows and players become columns. Pandas has a simple .T method for transposing, but we'll clean up the result to make it more usable:

# Transpose the DataFrame
afl_transposed = afl_df.T

# Reset the index to turn round labels (R1-R8) into a proper column
afl_transposed = afl_transposed.reset_index().rename(columns={'index': 'Round'})

Now afl_transposed has Round as the first column, with each subsequent column representing a player's score in that round.

Step 3: Scatter Plot with Rounds on the X-axis #

For plotting, the most scalable and clean approach is to convert the transposed DataFrame into a long (tidy) format first, then use seaborn to plot all players at once. Here's how:

First, convert to long format:

import seaborn as sns
import matplotlib.pyplot as plt

# Melt the DataFrame to get a tidy structure: Round, Player, Score
afl_long = afl_transposed.melt(
    id_vars='Round',
    var_name='Player',
    value_name='Score'
)

Then plot the scatter graph:

plt.figure(figsize=(10, 6))
sns.scatterplot(
    data=afl_long,
    x='Round',
    y='Score',
    hue='Player',  # Different colors for each player
    s=100,  # Adjust point size for visibility
    alpha=0.8
)

plt.title('AFL Player Scores by Round')
plt.xlabel('Round')
plt.ylabel('Score')
plt.legend(bbox_to_anchor=(1.05, 1), loc='upper left')  # Move legend outside plot
plt.tight_layout()
plt.show()

If you prefer individual plots for each player (e.g., a grid of subplots), use matplotlib's subplots:

players = afl_transposed.columns[1:]  # Get list of player names
num_players = len(players)
rows = (num_players + 1) // 2  # Calculate rows for subplot grid

fig, axes = plt.subplots(rows, 2, figsize=(15, 5*rows))
axes = axes.flatten()  # Flatten axes array for easier looping

for idx, player in enumerate(players):
    ax = axes[idx]
    sns.scatterplot(
        data=afl_transposed,
        x='Round',
        y=player,
        ax=ax,
        color='blue',
        s=80
    )
    ax.set_title(f'{player} Scores by Round')
    ax.set_xlabel('Round')
    ax.set_ylabel('Score')

# Hide any empty subplots
for ax in axes[num_players:]:
    ax.set_visible(False)

plt.tight_layout()
plt.show()

The long-format seaborn approach is better for comparing all players at a glance, while the subplot method is great for deep-diving into individual player performance.

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