Pandas Reshaping Long to Wide by Two Variables

Manipulating data between long and wide formats is a common task in data analysis. In Python's Pandas library, melt and stack/unstack operations are commonly used for this purpose. However, certain scenarios may arise where a more straightforward approach is desired.

One such scenario is when reshaping data that includes two variables (e.g., a numeric variable like sales and a categorical variable like product) into a wide format. Using melt/stack/unstack methods alone may not provide the desired output.

In this example, we have "long" data with the following columns: Salesman, Height, product, and price. Our goal is to reshape this data into a "wide" format with columns for each unique product, including its corresponding price.

Salesman  Height   product      price
  Knut      6        bat          5
  Knut      6        ball         1
  Knut      6        wand         3
  Steve     5        pen          2

To accomplish this, we can leverage Pandas' pivot function, which provides a convenient way to create pivot tables. We specify the index column (Salesman), pivot columns (obs), and values column (price).

Here's the Python code to reshape the data:

wide_df = df.pivot(index='Salesman', columns='product', values='price')

This will produce the desired "wide" format:

Salesman  Height    product_1  price_1  product_2 price_2 product_3 price_3  
  Knut      6        bat          5       ball      1        wand      3
  Steve     5        pen          2        NA       NA        NA       NA