Problem

Table: Products

1
2
3
4
5
6
7
8

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+

product_id is the primary key (column with unique values) for this table. Each row in this table indicates the product’s price in 3 different stores: store1, store2, and store3. If the product is not available in a store, the price will be null in that store’s column.

Write a solution to rearrange the Products table so that each row has (product_id, store, price). If a product is not available in a store, do not include a row with that product_id and store combination in the result table.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1:

Input: Products table:

1
2
3
4
5
6

+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+

Output:

1
2
3
4
5
6
7
8
9

+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+

Explanation: Product 0 is available in all three stores with prices 95, 100, and 105 respectively. Product 1 is available in store1 with price 70 and store3 with price 80. The product is not available in store2.

Solution

Method 1 - Union

Code

1
2
3
4
5
6
7

SELECT product_id, 'store1' AS store, store1 AS price FROM Products WHERE store1 IS NOT NULL
UNION 
SELECT product_id, 'store2' AS store, store2 AS price FROM Products WHERE store2 IS NOT NULL
UNION 
SELECT product_id, 'store3' AS store, store3 AS price FROM Products WHERE store3 IS NOT NULL

ORDER BY product_id, stor
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

import pandas as pd

def rearrange_products_table(products: pd.DataFrame) -> pd.DataFrame:
    rearranged_rows = []

    # Iterate over each row in the original table
    for _, row in products.iterrows():
        product_id = row['product_id']

        # Check each store for price availability
        for store_col in ['store1', 'store2', 'store3']:
            price = row[store_col]
            if pd.notna(price):
                # If the price is not null, add the (product_id, store, price) tuple to the list
                rearranged_rows.append((product_id, store_col, price))

    # Create a new DataFrame with the rearranged rows
    result_df = pd.DataFrame(rearranged_rows, columns=['product_id', 'store', 'price'])

    return result_df

Method 2 - Reverse Pivot

Code

1
2
3
4
5
6
7

SELECT product_id,store,price
FROM Products
UNPIVOT
(
	price
	FOR store in (store1,store2,store3)
) AS T
1
2
3
4

def rearrange_products_table(products: pd.DataFrame) -> pd.DataFrame:
    return pd.melt(
        products, id_vars='product_id', var_name='store', value_name='price'
    ).dropna()