如何将同id_item的两行数据合并为一行，拆分IN/OUT相关列？

原始数据 #

期望结果 #

方法1：SQL实现 #

通过条件聚合直接在数据库端完成数据转换：

SELECT
    id_item,
    item_name,
    brand,
    COALESCE(MAX(CASE WHEN type = 'IN' THEN qty END), 0) AS IN,
    COALESCE(MAX(CASE WHEN type = 'IN' THEN date END), 'N/A') AS date_IN,
    COALESCE(MAX(CASE WHEN type = 'OUT' THEN qty END), 0) AS OUT,
    COALESCE(MAX(CASE WHEN type = 'OUT' THEN date END), 'N/A') AS date_OUT
FROM your_table
GROUP BY id_item, item_name, brand;

逻辑说明：用CASE语句筛选对应type的qty和date，MAX聚合确保每个id_item只保留一行有效数据，COALESCE处理缺失值，补0或N/A。

方法2：Python Pandas实现 #

适合Python环境下的批量数据处理：

import pandas as pd

# 加载原始数据（实际场景可替换为读取文件）
df = pd.DataFrame({
    'id_transaction': [4,4,5],
    'id_item': [83,84,83],
    'item_name': ['Glass Wine', 'Plastic Cup', 'Glass Wine'],
    'brand': ['XYZ', 'IKEA', 'XYZ'],
    'type': ['IN', 'IN', 'OUT'],
    'qty': [100,50,10],
    'date': ['2023-01-01', '2023-01-01', '2023-03-20']
})

# 透视表拆分数据
pivot_df = df.pivot(
    index=['id_item', 'item_name', 'brand'],
    columns='type',
    values=['qty', 'date']
).reset_index()

# 扁平化列名
pivot_df.columns = [
    col[0] if col[0] in ['id_item', 'item_name', 'brand'] 
    else f"{col[1]}_{col[0]}" 
    for col in pivot_df.columns
]

# 填充缺失值
pivot_df['IN_qty'] = pivot_df['IN_qty'].fillna(0)
pivot_df['OUT_qty'] = pivot_df['OUT_qty'].fillna(0)
pivot_df['IN_date'] = pivot_df['IN_date'].fillna('N/A')
pivot_df['OUT_date'] = pivot_df['OUT_date'].fillna('N/A')

# 调整列顺序匹配期望结果
result = pivot_df[['id_item', 'item_name', 'brand', 'IN_qty', 'IN_date', 'OUT_qty', 'OUT_date']]
result.columns = ['id_item', 'item_name', 'brand', 'IN', 'date', 'OUT', 'date']

print(result)

内容的提问来源于stack exchange，提问作者Binyamin W.