将值过多的行转换为列

Question

假设我有这样一个数据集。在

ID  Item
1   A
2   B
3   A
1   C
2   B
2   B
1   A
3   B

实际的数据集有50K行和8.5K个不同的项值。在

现在我想将每个Id的行转换为列，这样我们就可以计算每个用户的每个项的值。像下面这样

^{pr2}$

现在，如果我有like set no.of Item值，那么我可以在case aggregate语句中对它们进行硬编码。在

有点像

select ID, count(case when Item ='A' then 1 else Null end) A, 
count(case when Item='B' then 1 else Null end) B... and so on. 

但在这个例子中，我有8.5公里的独特项目。这将是一个太多的任务，无法像上面那样在sql中完成。在

如何实现这一点是Sql和ii）Python。请记住，我有50K行和8.5k个惟一项，所以python循环可能会变得有点慢。在

但我的第一个目标是用sql来实现，因为这是一个50K的样本数据，当数据变大时，sql仍然可以很好地保存它，但是python会变得很慢感觉。进去python我可以尝试一些代码也是。但是同样，当数据超过50K obs时，python的速度也会变慢

请告知

不使用Postgresql和HANA，因此特定于Postgres的函数可能无法工作。请建议通用sql方式。在

Answer 1

如果您确实想在python中执行此操作，那么应该查看pandas库

df = pd.DataFrame({
    'ID': [1, 2, 3, 1, 2, 2, 1, 3],
    'Item': ['A', 'B', 'A', 'C', 'B', 'B' , 'A', 'B']
})

pd.crosstab(df['ID'], df['Item'])

输出：

^{pr2}$

Answer 2

对于SQL解决方案，使用SQL技术但编写脚本如何？不知道它能撑多久，但如果你还没试过，你可以试试。 运行类似这样的命令来生成所需列的脚本：

select distinct 'sum(case when ITEM = ''' + ITEM + ''' then 1 else 0 end) ' + ITEM + ',' from YOUR_TABLE;

然后复制并粘贴结果，得到：

^{pr2}$

Answer 3

此查询可以生成所需的查询：

SELECT CONCAT("SELECT ID"
    , GROUP_CONCAT(DISTINCT 
        CONCAT(", COUNT(CASE Item WHEN '", Item, "' THEN 1 ELSE NULL END) AS `", Item, "`")
        ORDER BY Item
        )
    , "FROM `theTable` "
    , "GROUP BY ID"
  ) AS theQuery
FROM `theTable`
;

……但我只知道GROUP_CONCAT确实存在于MySQL中，而不存在于MSSQL中；我不知道其他数据库系统。另外，在MySQL中，您需要通过在这个查询之前使用一个类似于

^{pr2}$

…更改服务器的默认配置。 …其中“1000000”的长度足够大，无法截断结果字符串。在

如果查询太大，无论什么设置，group\u concat都无法容纳它：您可以运行多个这样的查询，使用where来减少处理的Item值，生成更小的“、COUNT（…）、COUNT（“列表”；然后手动合并这些结果。在

当然，即使这样生成查询想要的，我也不肯定MySQL会接受这么长时间的查询。在