Master Pandas From Scratch

Learn Pandas fundamentals, Series, DataFrames, data selection, filtering, cleaning, sorting, grouping, merging, reshaping, string operations, time series, data analysis, performance optimization, visualization, and practical projects through structured lessons.

COURSE SYLLABUS
1. Pandas Introduction and Environment Setup
Introduction to Pandas Installation and Environment Setup
2. Pandas Core Data Structures
Data Structures Overview Pandas Series DataFrames Indexes, Rows and Columns Shape, Size and Dimensions
3. Pandas Data Input and Output
Data Input and Output Overview Reading CSV, Excel and JSON Files Writing and Exporting Files
4. Initial Data Inspection
Data Inspection Overview Head and Tail Data Info and Types Descriptive Statistics
5. Selection and Indexing
Selection and Indexing Overview Column Selection loc and iloc Index Management
6. Filtering and Conditional Selection
Filtering Overview Boolean Filtering Multiple Conditions isin, between and str.contains
7. Basic Data Cleaning
Data Cleaning Overview Missing Values Duplicate Data Rename and Replace Data Type Conversion
8. Vectorized Operations and Sorting
Operations and Sorting Overview Column Operations Sorting Values and Indexes Sum, Mean, Max and Min
9. GroupBy and Aggregation
GroupBy Overview Grouping Data Aggregation Functions Group Filtering Group Transform
10. Merging, Joining and Concatenating
Merge, Join and Concat Overview Concatenation Merging DataFrames Joining by Index
11. Reshaping and Pivoting
Reshaping Overview Pivot Tables Melting Data Stack and Unstack Cross Tabulation
12. String and Categorical Data
String and Categorical Overview String Operations Categorical Data One-Hot Encoding
13. Time Series Analysis
Time Series Overview DateTime Operations Time-Based Indexes Resampling Data Shift and Rolling Windows
14. Custom Functions and Mapping
Apply and Mapping Overview Apply and Lambda Functions Map Operations Element-Wise Operations
15. Performance and Large Datasets
Performance Overview Memory Optimization Processing Files in Chunks PyArrow, Dask and Polars
16. Pandas Data Visualization
Visualization Overview Basic Plotting Histograms, Lines, Scatter and Box Plots Matplotlib and Seaborn

Melting DataFrames in Pandas

Learn how to transform wide-format data into long-format data using the Pandas melt() function. Understand identifier columns, variable columns, value columns, and practical data transformation techniques.

1. Introduction to Melting

Melting is a data reshaping operation that converts a DataFrame from a wide format into a long format. It takes multiple columns and combines their column names into one column and their corresponding values into another.

In a wide-format DataFrame, related measurements often appear in separate columns. In a long-format DataFrame, those measurements are represented as rows, making the data easier to analyze, group, visualize, and combine.

Key Concepts

  • Wide format: Different measurements are stored in separate columns.
  • Long format: Measurement names and values are stored in separate columns.
  • Identifier variables: Columns that remain unchanged during melting.
  • Variable column: A column containing the names of the melted columns.
  • Value column: A column containing the values from the melted columns.

2. Understanding Wide and Long Formats

Consider a DataFrame containing the marks of students in three subjects. Each subject has its own column, so the DataFrame is in wide format.

Wide Format Student Math Science English Asha 85 90 88 Ravi 78 82 80 melt() Long Format Student Subject Marks AshaMath85 AshaScience90 AshaEnglish88 RaviMath78 RaviScience82 RaviEnglish80

Melting converts subject columns into subject rows while retaining the student identifier.

3. The melt() Function

Pandas provides the melt() function to reshape a DataFrame from wide format to long format.

pandas.melt(
    frame,
    id_vars=None,
    value_vars=None,
    var_name="variable",
    value_name="value",
    ignore_index=True
)

Important Parameters

Parameter Description
frame The DataFrame to reshape.
id_vars Columns to retain as identifier columns.
value_vars Columns to unpivot. If omitted, all columns not specified in id_vars are used.
var_name Name of the output column that stores the original column names.
value_name Name of the output column that stores the corresponding values.
ignore_index If True, creates a new sequential index in the result. The default is True.

4. Creating a Sample DataFrame

First, import Pandas and create a DataFrame with student names and subject marks.

import pandas as pd

df = pd.DataFrame({
    "Student": ["Asha", "Ravi", "Neha"],
    "Math": [85, 78, 92],
    "Science": [90, 82, 88],
    "English": [88, 80, 95]
})

print(df)

Output

  Student  Math  Science  English
0    Asha    85       90       88
1    Ravi    78       82       80
2    Neha    92       88       95

The Student column identifies each student, while the subject columns contain the values that can be melted into rows.

5. Basic Melting with id_vars

Use id_vars to preserve the student names and value_vars to select the subject columns to melt.

long_df = pd.melt(
    df,
    id_vars=["Student"],
    value_vars=["Math", "Science", "English"]
)

print(long_df)

Output

  Student variable  value
0    Asha     Math     85
1    Ravi     Math     78
2    Neha     Math     92
3    Asha  Science     90
4    Ravi  Science     82
5    Neha  Science     88
6    Asha  English     88
7    Ravi  English     80
8    Neha  English     95
Remember: Each student appears once for every selected subject. With three students and three subject columns, the melted DataFrame contains nine rows.

6. Renaming the Output Columns

The default output column names are variable and value. Use var_name and value_name to give them more meaningful names.

long_df = pd.melt(
    df,
    id_vars=["Student"],
    value_vars=["Math", "Science", "English"],
    var_name="Subject",
    value_name="Marks"
)

print(long_df.head())

Output

  Student  Subject  Marks
0    Asha     Math     85
1    Ravi     Math     78
2    Neha     Math     92
3    Asha  Science     90
4    Ravi  Science     82

The resulting columns describe the data clearly: the student, the subject, and the marks obtained.

7. Melting All Non-Identifier Columns

You do not need to specify value_vars when you want to melt every column except the identifier columns.

long_df = df.melt(
    id_vars="Student",
    var_name="Subject",
    value_name="Marks"
)

print(long_df.head())

In this example, Pandas automatically selects Math, Science, and English for melting because Student is the identifier column.

Important: If you add another column to the original DataFrame and omit value_vars, that column may also be melted. Specify value_vars when you need precise control over which columns are transformed.

8. Melting Multiple Identifier Columns

A DataFrame can contain more than one identifier column. For example, a sales DataFrame may have both a product name and a region.

sales = pd.DataFrame({
    "Product": ["Laptop", "Phone"],
    "Region": ["North", "South"],
    "Q1": [100, 150],
    "Q2": [120, 170]
})

long_sales = pd.melt(
    sales,
    id_vars=["Product", "Region"],
    value_vars=["Q1", "Q2"],
    var_name="Quarter",
    value_name="Sales"
)

print(long_sales)

Output

  Product Region Quarter  Sales
0  Laptop  North      Q1    100
1   Phone  South      Q1    150
2  Laptop  North      Q2    120
3   Phone  South      Q2    170

Both Product and Region remain unchanged as identifier columns. The quarter names and sales values are transformed into the Quarter and Sales columns.

9. Melting a DataFrame with a Custom Index

By default, melt() uses ignore_index=True, which creates a new sequential index. Set it to False when you want to retain the original index labels.

df_indexed = df.set_index("Student")

result = df_indexed.reset_index().melt(
    id_vars="Student",
    value_vars=["Math", "Science"],
    var_name="Subject",
    value_name="Marks",
    ignore_index=False
)

print(result)

Here, the original student index is first restored as a regular Student column using reset_index(). The ignore_index=False option preserves the index labels of the DataFrame being melted, which in this example are the default row labels from the original DataFrame.

Note: If preserving a meaningful original index is important, inspect the index before and after melting. Resetting the index changes it into a column and affects which index labels are available for preservation.

10. Understanding the Number of Rows

Melting generally creates one output row for each combination of original rows and selected value columns.

Original Rows 3 × Melted Columns 3 = Output Rows 9 3 students × 3 subject columns = 9 rows General rule: output rows = input rows × selected value columns

The row-count rule applies when all selected columns are melted and no rows are otherwise removed.

For example, if a DataFrame has 100 rows and you melt four value columns, the result normally contains 400 rows.

11. Melting and Missing Values

Melting reshapes values; it does not automatically remove missing values. If the original DataFrame contains missing values in selected columns, those values can appear in the melted result.

df_missing = pd.DataFrame({
    "Student": ["Asha", "Ravi"],
    "Math": [85, None],
    "Science": [90, 82]
})

long_df = df_missing.melt(
    id_vars="Student",
    var_name="Subject",
    value_name="Marks"
)

print(long_df)

To remove rows with missing marks after melting, use dropna() on the relevant column.

clean_df = long_df.dropna(subset=["Marks"])

print(clean_df)
Best practice: Decide whether missing values should be retained, removed, or handled using another strategy based on the requirements of your analysis.

12. Common Mistakes and Best Practices

Common Mistakes

  • Forgetting to specify identifier columns in id_vars.
  • Melting columns that should have remained unchanged.
  • Using unclear output names such as variable and value in a complex analysis.
  • Assuming that melting automatically removes missing values.
  • Expecting the melted DataFrame to retain the same number of rows as the original.

Best Practices

  • Identify the columns that uniquely describe each observation.
  • Use value_vars when only specific columns should be melted.
  • Choose meaningful names with var_name and value_name.
  • Inspect the output using head(), shape, and info().
  • Check missing values and data types before further analysis.

13. Revision Questions

  1. What is melting in Pandas?
  2. How does wide-format data differ from long-format data?
  3. What is the purpose of id_vars?
  4. What happens when value_vars is omitted?
  5. How can you customize the names of the output columns?
  6. How many rows are produced when 5 rows and 4 value columns are melted?
  7. Does the melt() function automatically remove missing values?

14. Practical Exercises

Exercise 1: Student Marks

Create a DataFrame containing three students and their marks in Mathematics, Science, and English. Convert the subject columns into rows using melt().

Goal: Produce columns named Student, Subject, and Marks.

Exercise 2: Quarterly Sales

Create a DataFrame containing product names, regions, and sales for Q1, Q2, Q3, and Q4. Melt the quarterly columns while preserving product and region identifiers.

Goal: Produce columns named Product, Region, Quarter, and Sales.

Exercise 3: Clean the Melted Data

Add missing values to some of the measurements in your DataFrame. Melt the data and then remove rows where the measurement value is missing.

Goal: Practice combining melt() and dropna().

Summary

Melting is a fundamental Pandas reshaping technique that converts wide-format data into long-format data. The melt() function combines selected column names into a variable column and their corresponding values into a value column. Use id_vars to preserve identifier columns, value_vars to select columns for transformation, and var_name and value_name to make the output easier to understand. After melting, inspect the resulting shape, index, data types, and missing values before continuing with analysis.