本文Pandas版本必须为0.25以上才能使用explode，可以通过以下命令看Pandas版本：
pip show pandas
可以通过以下命令升级Pandas到最新版：
pip install pandas --upgrade
也可以通过以下命令安装指定版本的Pandas：
pip install pandas==1.0.3
目录
效果展示
在hive中：
hive> select s from wc_t;
s
apple apple cdh
dest cdh firend dest
english firend apple dest
girl gift hit dest girl
Time taken: 0.191 seconds, Fetched 4 row(s)
select wc.word, count(1) count
from
(select explode(split(s, ' ')) as word from wc_t) wc
group by wc.word
order by count desc;
word count
--------------
dest 4
apple 3
girl 2
cdh 2
firend 2
hit 1
english 1
gift 1
Time taken: 2.119 seconds, Fetched 8 row(s)
在python中：
import pandas as pd
In[1]:
df = pd.read_csv("word.txt", header=None)
df
out[1]:
0
0 apple apple cdh
1 dest cdh firend dest
2 english firend apple dest
3 girl gift hit dest girl
In[2]:
df = pd.read_csv("word.txt", header=None, names=['s'])
df["s"] = df["s"].str.split(" ")
se = df.explode("s").rename(columns={"s": "word"}).groupby("word").apply(len)
se.sort_values(ascending=False, inplace=True)
se.reset_index(name="count")
out[2]:
word count
0 dest 4
1 apple 3
2 girl 2
3 firend 2
4 cdh 2
5 hit 1
6 gift 1
7 english 1
hive实现的详解
word.txt文件的内容：
apple apple cdh
dest cdh firend dest
english firend apple dest
girl gift hit dest girl
hive表数据准备：
create table wc_t(s string);
load data local inpath 'word.txt' into table wc_t;
首先，使用 split 函数将数据切割成一个一个的单词：
hive> select split(s, ' ') from wc_t;
["apple","apple","cdh"]
["dest","cdh","firend","dest"]
["english","firend","apple","dest"]
["girl","gift","hit","dest","girl"]
Time taken: 0.36 seconds, Fetched 4 row(s)
然后，使用 explode 函数将集合中的元素拆分成多行元素：
hvie> select explode(split(s, ' ')) word from wc_t;
word
apple
apple
cdh
dest
cdh
firend
dest
english
firend
apple
dest
girl
gift
hit
dest
girl
Time taken: 0.207 seconds, Fetched 16 row(s)
最后，使用聚合函数统计多行数据：
select wc.word, count(1) count
from
(select explode(split(s, ' ')) word from wc_t) wc
group by wc.word
order by count desc;
word count
--------------
dest 4
apple 3
girl 2
cdh 2
firend 2
hit 1
english 1
gift 1
Time taken: 2.119 seconds, Fetched 8 row(s)
python实现的详解
读取数据：
import pandas as pd
df = pd.read_csv("word.txt", header=None, names=['s'])
df
s
0 apple apple cdh
1 dest cdh firend dest
2 english firend apple dest
3 girl gift hit dest girl
将单词切割成数组：
df["s"] = df["s"].str.split(" ")
df
s
0 [apple, apple, cdh]
1 [dest, cdh, firend, dest]
2 [english, firend, apple, dest]
3 [girl, gift, hit, dest, girl]
将数组中的元素拆分成多行元素：
df = df.explode("s")
df
s
0 apple
0 apple
0 cdh
1 dest
1 cdh
1 firend
1 dest
2 english
2 firend
2 apple
2 dest
3 girl
3 gift
3 hit
3 dest
3 girl
修改列名：
df = df.rename(columns={"s": "word"})
df.head()
word
0 apple
0 apple
0 cdh
1 dest
1 cdh
分组聚合，计算每个单词出现的次数（返回一个Series）：
se = df.groupby("word").apply(len)
se
word
apple 3
cdh 2
dest 4
english 1
firend 2
gift 1
girl 2
hit 1
dtype: int64
由于无多余的数值列进行数值统计，故只能通过apply传递函数进行计算。
对次数进行排序：
se.sort_values(ascending=False, inplace=True)
se
word
dest 4
apple 3
girl 2
firend 2
cdh 2
hit 1
gift 1
english 1
dtype: int64
最后将结果还原为DataFrame：
se.reset_index(name="count")
word count
0 dest 4
1 apple 3
2 girl 2
3 firend 2
4 cdh 2
5 hit 1
6 gift 1
7 english 1
一步到位：
df = pd.read_csv("word.txt", header=None, names=['s'])
df["s"] = df["s"].str.split(" ")
se = df.explode("s").rename(columns={"s": "word"}).groupby("word").apply(len)
se.sort_values(ascending=False, inplace=True)
se.reset_index(name="count")
word count
0 dest 4
1 apple 3
2 girl 2
3 firend 2
4 cdh 2
5 hit 1
6 gift 1
7 english 1
小例子
有一个gross.csv文件，内容如下：
Action|Adventure|Fantasy|Sci-Fi,760505847.0
Action|Adventure|Fantasy,309404152.0
Action|Adventure|Thriller,200074175.0
Action|Thriller,448130642.0
Documentary,
Action|Adventure|Sci-Fi,73058679.0
Action|Adventure|Romance,336530303.0
Adventure|Animation|Comedy|Family|Fantasy|Musical|Romance,200807262.0
Action|Adventure|Sci-Fi,458991599.0
Adventure|Family|Fantasy|Mystery,301956980.0
Action|Adventure|Sci-Fi,330249062.0
Action|Adventure|Sci-Fi,200069408.0
Action|Adventure,168368427.0
Action|Adventure|Fantasy,423032628.0
Action|Adventure|Western,89289910.0
Action|Adventure|Fantasy|Sci-Fi,291021565.0
Action|Adventure|Family|Fantasy,141614023.0
每行数据表示某部电影所属的电影类型，和该部电影的票房总数。现在要求用python或hive 按照电影类型分类，统计出不同类型的票房总数。
hive实现
加载数据：
CREATE TABLE movie_gross (
genres string,
gross bigint
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
load data local inpath 'gross.csv' into table movie_gross;
sql查询语句：
select a.genre,sum(a.gross) gross
from
(select genre,gross from movie_gross
lateral view explode(split(genres,'\\|')) tmp as genre) a
group by a.genre
order by gross desc;
结果：
genre gross
-------------------------
Adventure 4284974020
Action 4230340420
Fantasy 2428342457
Sci-Fi 2113896160
Thriller 648204817
Family 644378265
Romance 537337565
Mystery 301956980
Musical 200807262
Animation 200807262
Comedy 200807262
Western 89289910
Documentary NULL
Time taken: 2.592 seconds, Fetched 13 row(s)
python实现
import pandas as pd
df = pd.read_csv("gross.csv", header=None, names=["genres", "gross"])
df["genres"] = df["genres"].str.split("|")
df.explode("genres").groupby('genres').sum().sort_values("gross", ascending=False)
结果：
gross
genres
Adventure 4.284974e+09
Action 4.230340e+09
Fantasy 2.428342e+09
Sci-Fi 2.113896e+09
Thriller 6.482048e+08
Family 6.443783e+08
Romance 5.373376e+08
Mystery 3.019570e+08
Animation 2.008073e+08
Comedy 2.008073e+08
Musical 2.008073e+08
Western 8.928991e+07
Documentary 0.000000e+00