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
,index='name'
,columns='course'
,values='score'
)
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
,id_vars='name'
,value_vars=['数学','语文']
,var_name='course'
,value_name='score'
)
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
------------------------