HSql 行列转换(collect_list/set, lateral view + explode/posexplode)

-- 1、 简单创建样表
create table tmp.test_11
(name string,num int)
stored as parquet;

insert into tmp.test_11 values('张山',3),('李四',4);
insert into tmp.test_11 values('张山',1),('李四',2);

select * from tmp.test_11;
-+-------------+-------------+-
 |test_11.name | test_11.num |
-|-------------|-------------|-
 |张山	      |3            |
 |李四	      |4            |
 |张山          |1            |
 |李四          |2            |
-+-------------+-------------+-

-- 2、 多行转一行（行转列）collect_list
select name,collect_list(num) hangzhuanlie from tmp.test_11 group by name
-+-------------+-------------+-
 |test_11.name | hangzhuanlie|
-|-------------|-------------|-
 |张山	      |[3,1]        |
 |李四	      |[2,4]        |
-+-------------+-------------+-

-- 3、一行转多行（列转行）lateral view outer + explode
select 
   name  -- 不改变的列名
   ,num  -- 拆分后的列名
from 
(select name,collect_list(num) hangzhuanlie from tmp.test_11 group by name) t
lateral view outer explode(hangzhuanlie) tt as num  -- tt： 侧视表虚拟名（用于不变的行多重复制） ；num： 拆分后列名
-+-------------+-------------+-
 |test_11.name | test_11.num |
-|-------------|-------------|-
 |张山	      |1            |
 |张山	      |3            |
 |李四          |4            |
 |李四          |2            |
-+-------------+-------------+-

-- 5、备注
collect_set(col) ：相对于collect_list 返回去重后的array
posexplode(array): 类似于explode(array|map)，但它不只是返回数组的元素，而是返回元素及其在原始数组中的位置。

-- 6、posexplode 函数使用
-- 案例1
select name,pos,val from 
(select name,collect_set(num) hangzhuanlie from tmp.test_11 group by name) t
lateral view outer posexplode(hangzhuanlie) tt as pos,val

-+-------+-----+-------+-
 |name   | pos | val   |
-|-------|-------------|-
 |张山	|0    |   3   |
 |张山    |1    |   1   |
 |李四    |0    |   2   |
 |李四    |1    |   4   |
-+-------------+-------+-

-- 案列2 生成连续时间序列
select date_add('2023-12-01',t.pos) dd 
from 
(select posexplode(split(repeat('n',datediff('2023-12-31','2023-12-01')),'n'))) t


 	dd
1	2023-12-01
2	2023-12-02
3	2023-12-03
4	2023-12-04
5	2023-12-05
   ...
28	2023-12-28
29	2023-12-29
30	2023-12-30
31	2023-12-31

pandas 行列转换

1、一个array字段纵向扩展(多行) explode(col)

import pandas as pd

# 创建测试数据
test_data = pd.DataFrame({'name':['张三','李四','王五'],'love':['刘亦菲,陈奕迅','汪峰,周杰伦','迪迦,大谷,格尔赞']})
print(f'转换前：\n{test_data}',end='\n------------------------\n')

# 转换代码
test_data['love'] = test_data['love'].str.split(',')
test_data = test_data.explode('love')
print(f'转换后: \n{test_data}',end='\n------------------------\n')


转换前：
  name       love
0   张三    刘亦菲,陈奕迅
1   李四     汪峰,周杰伦
2   王五  迪迦,大谷,格尔赞
------------------------
转换后: 
  name love
0   张三  刘亦菲
0   张三  陈奕迅
1   李四   汪峰
1   李四  周杰伦
2   王五   迪迦
2   王五   大谷
2   王五  格尔赞
------------------------

2、一个array字段横向扩展(多列) .str.split(,expand=True)

# 创建测试数据
test_data = pd.DataFrame({'name':['张三','李四','王五'],'love':['刘亦菲,陈奕迅','汪峰,周杰伦','迪迦,格尔赞']})
print(f'转换前：\n{test_data}',end='\n------------------------\n')

# 转换代码
test_data[['love_1','love_2']] = test_data['love'].str.split(',',expand=True)
test_data.drop(columns='love',inplace=True)
print(f'转换后: \n{test_data}',end='\n------------------------\n')

转换前：
  name     love
0   张三  刘亦菲,陈奕迅
1   李四   汪峰,周杰伦
2   王五   迪迦,格尔赞
------------------------
转换后: 
  name love_1 love_2
0   张三    刘亦菲    陈奕迅
1   李四     汪峰    周杰伦
2   王五     迪迦    格尔赞
-------------------------

3、行转列(某些字段值转换为表头) pd.pivot

# 创建测试数据
test_data = pd.DataFrame({'name':['张三','张三','李四','李四','王五','王五']
                          ,'course':['数学','语文','数学','语文','数学','语文']
                          ,'score':[86,34,56,78,87,95]})
print(f'转换前：\n{test_data}',end='\n------------------------\n')

# 转换代码
test_data = pd.pivot(data=test_data # 待转换df
                     ,index='name'  # df交叉后行
                     ,columns='course' # df交叉后的列
                     ,values='score' # df交叉后数据（不发生聚合）
                    )
test_data.reset_index(inplace=True)

print(f'转换后: \n{test_data}',end='\n------------------------\n')


转换前：
  name course  score
0   张三     数学     86
1   张三     语文     34
2   李四     数学     56
3   李四     语文     78
4   王五     数学     87
5   王五     语文     95
------------------------
转换后: 
course name  数学  语文
0        张三  86  34
1        李四  56  78
2        王五  87  95
------------------------

4、列转行（部分列名转换位一列数据值）pd.melt

# 创建测试数据
test_data = pd.DataFrame({'name':['张三','李四','王五']
                          ,'数学':[86,56,87]
                          ,'语文':[34,78,95]
                         })
print(f'转换前：\n{test_data}',end='\n------------------------\n')


# 转换代码
test_data = pd.melt(frame=test_data # 待转换df
                     ,id_vars='name' # 固定的列
                     ,value_vars=['数学','语文'] # 待转换的列名
                     ,var_name='course' # 列转列转换后的列名称
                     ,value_name='score' # 最后数据列名称
                    )
# test_data.reset_index(inplace=True)

print(f'转换后: \n{test_data}',end='\n------------------------\n')


转换前：
  name  数学  语文
0   张三  86  34
1   李四  56  78
2   王五  87  95
------------------------
转换后: 
  name course  score
0   张三     数学     86
1   李四     数学     56
2   王五     数学     87
3   张三     语文     34
4   李四     语文     78
5   王五     语文     95
------------------------

5、多列合并两列（列合并）pd.lreshape

# 创建测试数据
test_data = pd.DataFrame({'name':['张三','李四','王五']
                          ,'科目1':['数学','数学','数学']
                          ,'科目1分数':[34,78,95]
                          ,'科目2':['语文','语文','语文']
                          ,'科目2分数':[86,56,87]
                         })
print(f'转换前：\n{test_data}',end='\n------------------------\n')


# 转换代码
test_data = pd.lreshape(data=test_data    # 待转换数据
                       ,groups={'科目':['科目1','科目2'],'分数':['科目1分数','科目2分数']} 
                       ,dropna=False # 是否删除空值
                       )


print(f'转换后: \n{test_data}',end='\n------------------------\n')


转换前：
  name 科目1  科目1分数 科目2  科目2分数
0   张三  数学     34  语文     86
1   李四  数学     78  语文     56
2   王五  数学     95  语文     87
------------------------
转换后: 
  name  科目  分数
0   张三  数学  34
1   李四  数学  78
2   王五  数学  95
3   张三  语文  86
4   李四  语文  56
5   王五  语文  87
------------------------