Pandas pd.melt() Function

Pandas 常用函数Pandas Common Functions

pd.melt()is a top-level function of the Pandas library, used toconvert wide table to long tableIt "melts" wide-format data containing multiple columns into long format, similar to the unpivot operation in Excel.

This is one of the most commonly used functions in data reshaping, especially useful when data needs to be converted to long format before data visualization, or when multiple columns need to be combined into one column.

Word Definition： meltIt means "to melt, to fuse"; here it refers to "melting" the wide table structure into a long table structure, similar to gathering multiple columns of data together.

Basic Syntax and Parameters

pd.melt()is a top-level function of the Pandas library, used to convert wide-format data to long format.

Syntax Format

pd.melt(frame, id_vars=None, value_vars=None, var_name=None, value_name='value', col_level=None)

Parameter Description

  • Parameter： frame
    • Type: DataFrame.
    • Description:Required parameter. The source data DataFrame, i.e., the data to be converted from wide to long.
  • Parameter： id_vars
    • Type: column name, tuple list, or None.
    • Description: Columns used as identifier (ID) variables, these columns remain unchanged after conversion. Can be a single column name or a list of column names. If not specified, all columns will be treated as value_vars.
  • Parameter： value_vars
    • Type: column name, tuple list, or None.
    • Description: The columns to be "melted", i.e., which columns are converted into rows. If not specified, all columns except id_vars will be converted.
  • Parameter： var_name
    • Type: string or None.
    • Description: The new column name generated to store the original column names. Default is 'variable'.
  • Parameter： value_name
    • Type: string.
    • Description: The generated value column name. Default is 'value'.
  • Parameter： col_level
    • Type: int, str, or None.
    • Description: If the columns are MultiIndex, specify the level to melt.

Function Description

  • Return value: Returns a DataFrame, with data converted from wide format to long format.
  • Effect: Melts the values of multiple columns into two columns: one column stores the original column names (variable), and one column stores the corresponding values (value).

Examples

Let us thoroughly master, through a series of examples from simple to complex,pd.melt()the usage of.

Example 1: Basic Usage - Convert a Simple Wide Table to a Long Table

Examples

import pandas as pd

# 1. Create wide-format data (one column for each quarter)
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'Q1_sales': [100, 150, 200],
    'Q2_sales': [120, 160, 210],
    'Q3_sales': [130, 170, 220]
})

print("=== Original Wide Format Data ===")
print(df)

# 2. Use pd.melt() to convert to long format
melted = pd.melt(df, id_vars=['name'], value_vars=['Q1_sales', 'Q2_sales', 'Q3_sales'])
print("\n=== pd.melt() converted to long format ===")
print(melted)

# 3. Custom column names
melted_custom = pd.melt(
    df,
    id_vars=['name'],
    value_vars=['Q1_sales', 'Q2_sales', 'Q3_sales'],
    var_name='quarter',
    value_name='sales'
)
print("\n=== Custom column names ===")
print(melted_custom)

Expected output:

=== 原始宽格式数据 ===
      name  Q1_sales  Q2_sales  Q3_sales
0    Alice      100      120      130
1     Bob      150      160      170
2  Charlie      200      210      220

=== pd.melt() 转换为长格式 ===
      name    variable  value
0    Alice   Q1_sales    100
1     Bob   Q1_sales    150
2  Charlie  Q1_sales    200
3    Alice   Q2_sales    120
4     Bob   Q2_sales    160
5  Charlie  Q2_sales    210
6    Alice   Q3_sales    130
7     Bob   Q3_sales    170
8  Charlie  Q3_sales    220

=== 自定义列名 ===
      name  quarter  sales
0    Alice  Q1_sales    100
1     Bob  Q1_sales    150
2  Charlie  Q1_sales    200
3    Alice  Q2_sales    200

Code analysis:

  1. The original data is in wide format, each row is a person, and Q1, Q2, Q3 are sales data columns for three quarters.
  2. id_vars=['name']Specify the name column to remain unchanged as the identifier.
  3. value_vars=['Q1_sales', 'Q2_sales', 'Q3_sales']Specify the columns to be melted.
  4. The result adds a variable column (storing original column names) and a value column (storing corresponding values).
  5. Usingvar_nameandvalue_namecan customize the names of the new columns.

Example 2: Without Specifying value_vars - Melt All Columns Except id_vars

When value_vars is not specified, all columns except id_vars are automatically melted.

Examples

import pandas as pd

# 1. Create data with more columns
df = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'math': [90, 85, 92],
    'english': [88, 90, 87],
    'science': [95, 88, 91]
})

print("=== Original Data ===")
print(df)

# 2. Only specify id_vars, automatically melt all other columns
melted = pd.melt(df, id_vars=['id', 'name'])
print("\n=== Melt all non-id columns ===")
print(melted)

# 3. Case with multiple id_vars
print("\n=== Multiple id_vars ===")
melted_multi = pd.melt(df, id_vars=['name'], value_vars=['math', 'english', 'science'])
print(melted_multi)

# 4. Use only one id_vars
print("\n=== One id_vars ===")
melted_one = pd.melt(df, id_vars=['id'])
print(melted_one)

Expected output:

=== 原始数据 ===
   id     name  math  english  science
0   1    Alice     90      88      95
1   2     Bob     85      90      88
2   3  Charlie    92      87      91

=== 融化所有非id列 ===
   id     name   variable  value
0   1    Alice      math     90
1   2       Bob     math     85
2   3  Charlie    math     92
3   1    Alice   english     88
4   2       融化为两列（variable 和 value）。

- 不指定 value_vars 时，系统会自动识别并熔化除 id_vars 外的所有列
- id_vars 可以是单个列或多个列的组合

Code analysis:

  • When not specified,value_varsit will automatically meltid_varsall columns except id_vars.
  • When only id_vars is specified and value_vars is not, each data row produces as many new rows as there are value_vars.
  • This method is very convenient when you need to process multi-column data uniformly.

Example 3: No id_vars - Melt All Columns

When id_vars is not specified, the original data's index serves as the identifier.

Examples

import pandas as pd

# 1. Create data (without an explicit id column)
df = pd.DataFrame({
    'Q1': [100, 150, 200],
    'Q2': [120, 160, 210],
    'Q3': [130, 170, 220]
})

print("=== Original Data ===")
print(df)

# 2. Do not specify id_vars, use the default index
melted = pd.melt(df)
print("\n=== Without specifying id_vars ===")
print(melted)

# 3. Restore the original index
print("\n=== Reset index ===")
melted_with_index = pd.melt(df, ignore_index=False)
print(melted_with_index)

# 4. Practical case: create an identifier
df_with_id = pd.DataFrame({
    'year': [2023, 2023, 2023],
    'Q1': [100, 150, 200],
    'Q2': [120, 160, 210]
})
print("\n=== Original data (with year) ===")
print(df_with_id)

# First add year to id_vars
melted_year = pd.melt(df_with_id, id_vars=['year'])
print("\n=== Melt result including year ===")
print(melted_year)

Expected output:

=== 原始数据 ===
     Q1   Q2   Q3
0  100  120  130
1  150  160  170
2  200  210  220

=== 不指定 id_vars ===
   variable  value
0        Q1    100
1        Q1    150
2        融化所有列，将原始列名转为 variable 列的值，原始值转为 value 列。

Code analysis:

  • When id_vars is not specified, only the variable and value columns remain.
  • Usingignore_index=Falsecan preserve the original index, making it easy to compare with the original data later.
  • In practice, one or more id_vars are usually specified to retain key information.

Example 4: Practical Case - Preparation for Data Visualization

melt() is a great helper for data visualization; many plotting libraries (such as Seaborn, Matplotlib) are better at handling long-format data.

Examples

import pandas as pd

# 1. Create sales data (one column per region)
sales = pd.DataFrame({
    'product': ['A', 'B', 'C', 'D'],
    'Jan': [1200, 1500, 900, 1100],
    'Feb': [1350, 1600, 950, 1200],
    'Mar': [1100, 1450, 880, 1050]
})

print("=== Wide-format sales data ===")
print(sales)

# 2. Melt into long format (for visualization)
sales_long = pd.melt(
    sales,
    id_vars=['product'],
    var_name='month',
    value_name='sales'
)

print("\n=== Long-format sales data ===")
print(sales_long)

# 3. Data processing example: calculate year-over-year growth
# Add month number
month_map = {'Jan': 1, 'Feb': 2, 'Mar': 3}
sales_long['month_num'] = sales_long['month'].map(month_map)

print("\n=== Add month number ===")
print(sales_long)

# 4. Filter data for a specific month
feb_sales = sales_long[sales_long['month'] == 'Feb']
print("\n=== Sales data for February ===")
print(feb_sales)

Expected output:

=== 宽格式销售数据 ===
  product   Jan   Feb   Mar
0       A  1200  1350  1100
product  Jan  180  170   6

=== 长格式销售数据（用于可视化） ===
   product month  sales
0       A   Jan  1200
1       B   Jan  1500
2       C   Jan   900
3       D   Jan  1100
4       A   Feb  1350

Code analysis:

  • Wide-format data has one column per region/time point, making it easy to view and enter data.
  • Long-format data has one row per observation, which is the standard format for statistical analysis.
  • Visualization libraries such as Seaborn accept long-format data, making it easy to create grouped bar charts, line charts, etc.
  • Usingid_varscan keep categorical variables such as product and region from being melted.

Example 5: Melting with Multi-level Column Index

When a DataFrame has a multi-level column index, you can usecol_levelthe parameter.

Examples

import pandas as pd

# 1. Create data with a multi-level column index
df = pd.DataFrame(
    [[100, 110, 200, 210], [150, 160, 180, 190]],
    index=['Store_A', 'Store_B'],
    columns=pd.MultiIndex.from_tuples([
        ('Electronics', 'Q1'), ('Electronics', 'Q2'),
        ('Furniture', 'Q1'), ('Furniture', 'Q2')
    ], names=['Category', 'Quarter'])
)

print("=== Original data (multi-level column index) ===")
print(df)

# 2. Melt all columns (without specifying a level)
print("\n=== Melt all columns ===")
melted_all = pd.melt(df, ignore_index=False)
print(melted_all)

# 3. Melt only the inner level (Quarter)
print("\n=== Melt only the Quarter level ===")
melted_quarter = pd.melt(df, col_level=1)
print(melted_quarter)

# 4. Melt the outer level (Category)
print(n=== Only melt the Category level ===)
melted_category = pd.melt(df, col_level=0)
print(melted_category)

Expected output:

=== 原始数据（多层列索引）===
Category      Electronics   Furniture
Quarter              Q1   Q2      Q1   融化不同层级会产生不同的结果：
- 融化内层（Quarter）保留 Category 作为 id
- 融化外层（Category）保留 Quarter 作为 id

Code explanation:

  • For a DataFrame with a multi-level column index, column names are in tuple form: (Category, Quarter).
  • col_level=0Melt the outer level (Category), keep Quarter as id.
  • col_level=1Melt the inner level (Quarter), keep Category as id.
  • This flexibility allows you to choose which level to melt based on your analysis needs.

Tip: pd.melt()It is a great helper for data visualization. Most plotting libraries (e.g., Seaborn, Plotnine) are better at handling long-format data; using melt() to transform data before plotting is a common practice.

Pandas 常用函数Common Pandas functions

Other extensions