Reshaping Data with Pivot and Melt

The same data can be arranged in different shapes. Learning to reshape it makes analysis and charting much easier.

a. Creating Pivot Tables

A pivot table summarizes data by turning unique values from one column into new columns. It is the same idea as the Pivot Table feature in Excel.

pd.pivot_table(
df,
values="sales",
index="region",
columns="category",
aggfunc="sum"
)

In this example, each region is a row, each category is a column, and the cells show total sales. This gives a clear cross-view of the data.

You can use fill_value=0 to replace missing cells with zero. Add margins=True to get row and column totals. Pivot tables are great for reports and quick comparisons.

b. Using melt to Unpivot Data

The melt() function does the opposite of pivoting. It turns many columns into rows. This is useful when your data has one column per month or per year.

long_df = pd.melt(
df,
id_vars=["product"],
value_vars=["jan", "feb", "mar"],
var_name="month",
value_name="sales"
)

Here, id_vars are the columns you keep as they are. value_vars are the columns to turn into rows. var_name and value_name set the names of the new columns.

Many plotting and grouping tools work better with this long format. Melting is often the first step when preparing wide data for a chart.

c. Stacking and Unstacking

The stack() and unstack() methods move data between rows and columns using the index. stack() moves column labels into the row index. unstack() moves a row index level into columns.

grouped = df.groupby(["region", "category"])["sales"].sum()
table = grouped.unstack()

This is a quick way to turn a grouped result into a table that is easy to read. The result looks like a pivot table but is built from the index.

These methods work best with multi-level indexes. If they feel confusing at first, try them on a small table and print each step. Seeing the shape change makes it much clearer.

d. Wide Format vs Long Format

Wide format has one row per item and many columns for different values, like one column for each month. It is easy for humans to read and is common in reports and Excel files.

Long format has fewer columns and more rows. One column holds the category, such as month, and another holds the value. This format is better for grouping, filtering, and plotting with libraries like Seaborn and Plotly.

Choose the shape based on your goal. Use wide format for presenting tables and long format for analysis and charts. You can switch between them anytime using pivot() and melt().