Problem
Table: Products
+-------------+---------+
| 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:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
+------------+--------+--------+--------+
Output:
+------------+--------+-------+
| 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
SQL
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
Python
- Create an empty list to store the rearranged rows
- Iterate over each row in the original products DataFrame.
- For each row, extract the product_id value.
- Check each store column (store1, store2, and store3) for price availability.
- Get the price value from the current store column.
- Check if the price is not null (i.e., the product is available in that store).
- If the price is not null, add a new row to the list with the values (product_id, store, price).
- After processing all rows, create a new DataFrame using the list of rearranged rows.
- The new DataFrame will have three columns: product_id, store, and price.
- Return the newly created DataFrame containing the rearranged table structure.
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
SQL
SELECT product_id,store,price
FROM Products
UNPIVOT
(
price
FOR store in (store1,store2,store3)
) AS T
Pandas
def rearrange_products_table(products: pd.DataFrame) -> pd.DataFrame:
return pd.melt(
products, id_vars='product_id', var_name='store', value_name='price'
).dropna()