如何使用Pandas实现透视效果：统计作者的Fiction与Non Fiction作品数量并分列为展示

Hey there! I see the issue with your current approach—using value_counts() gives you a hierarchical index where Genre is part of the rows, but you want those genres as distinct columns. Let's fix this with a couple straightforward methods.

Method 1: Use pivot_table (Clean & Intuitive) #

This is my go-to for this kind of reshaping, since it directly maps your desired rows/columns and aggregation:

import pandas as pd

# Assume your dataset is stored in a DataFrame called df
summary_table = df.pivot_table(
    index='Author ',  # Rows: Author names
    columns='Genre',  # Columns: Fiction/Non Fiction
    aggfunc='size',   # Count number of entries per group
    fill_value=0      # Fill 0 for authors missing one genre
)

# Clean up the output to make it a proper flat table
summary_table = summary_table.reset_index().rename_axis(None, axis=1)

print(summary_table)

Method 2: Modify Your Existing groupby Code #

If you prefer building on what you already tried, just add unstack() to pivot the Genre levels into columns:

# Start with your grouping, then unstack Genre into columns
summary_table = df.groupby(['Author ', 'Genre']).size().unstack(fill_value=0)

# Same cleanup as above
summary_table = summary_table.reset_index().rename_axis(None, axis=1)

Example Output #

Using your sample dataset, both methods will produce this clean table:

Both approaches ensure you get the exact structure you need: one row per author, with separate columns for each genre's count.

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