리트코드 : 1795. Rearrange Products Table

리트코드 : 1795. Rearrange Products Table

• [link]
  

---

문제

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.



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.

• PIVOT UNPIVOT
• UNION으로 합쳐주기

문제 풀이

MySQL

WITH TEMP AS (
  SELECT product_id, 'store1' AS store, store1 AS price
  FROM products
  UNION ALL
  SELECT product_id, 'store2' AS store, store2 AS price
  FROM products
  UNION ALL
  SELECT product_id, 'store3' AS store, store3 AS price
  FROM products
)
SELECT *
FROM TEMP
WHERE price IS NOT NULL

• 대소문자를 구분하는 문제인듯? 대문자로 쓰니까 통과를 못함...
• 각 컬럼별로 접근해서 UNION 3번 해주기

Pandas

import pandas as pd

def rearrange_products_table(products: pd.DataFrame) -> pd.DataFrame:
    melted = pd.melt(products, id_vars='product_id', var_name='store', value_name='price')
    return melted[~melted['price'].isnull()]

• 파이썬에는 무조건 이런 피벗관련 메서드가 있을 것 같아서 찾아보니까, melt 메서드를 사용한다.
• id_vars에는 기준 컬럼을, var_name에는 피벗 해제할 컬럼을, value_name에는 값을 적는다.

코멘트

• .