Mastering Pandas DataFrames: pivot() vs. melt() and Advanced Data Reshaping Techniques

Sample Data

import pandas as pd

data = {'Date': ['2022-01-01', '2022-01-01', '2022-01-02', '2022-01-02'],
        'City': ['A', 'B', 'A', 'B'],
        'Temperature': [25, 30, 22, 28],
        'Humidity': [50, 40, 60, 55]}

df = pd.DataFrame(data)

# Original DataFrame
df:
         Date City  Temperature  Humidity
0  2022-01-01    A           25        50
1  2022-01-01    B           30        40
2  2022-01-02    A           22        60
3  2022-01-02    B           28        55

1. pivot:

The pivot function in Pandas is used to reshape or transform data by pivoting the values of one column into multiple columns. It's particularly useful when you have long-form data (e.g., data in a tidy format with rows for each observation) and want to convert it into wide-form data with a column for each unique value in another column.

# Pivot the data
pivot_df = df.pivot(index='Date', columns='City', values='Temperature')

This will result in a DataFrame where the unique values in the ‘City’ column become separate columns, and the ‘Temperature’ values are placed accordingly. The resulting DataFrame pivot_df might look like:

# Pivot DataFrame
pivot_df:
City            A   B
Date                 
2022-01-01  25  30
2022-01-02  22  28

2. melt:

The melt function in Pandas is used to transform or reshape wide-form data into long-form data. It essentially "melts" the DataFrame, converting columns into rows. This is useful when you have data in a wide format, and you want to gather or unpivot specific columns.

# Melted the data
melted_df_single_var = pd.melt(df, id_vars=['Date', 'City'], var_name='Variable', value_name='Value')

This will result in a DataFrame where the ‘Temperature_A’ and ‘Temperature_B’ columns are melted into two columns, ‘City’ and ‘Temperature’. The resulting DataFrame melted_df might look like:

# Melted DataFrame
melted_df:
         Date City   Variable  Value
0  2022-01-01    A  Temperature     25
1  2022-01-01    B  Temperature     30
2  2022-01-02    A  Temperature     22
3  2022-01-02    B  Temperature…