背景

展示通过Pandas读取Excel，输出Excel的基本用法。

指定sheet，指定需要的column，在读取的同时对column进行重新命名和指定类型
使用Spark对数据进行分析处理（如果是简单的处理，仅使用pandas也是可以的）
单个Sheet和多个Sheet Excel文件的输出

import pandas as pd
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('JupyterPySpark').enableHiveSupport().getOrCreate()

读取Excel

通过Pandas读取Excel的时候

可以只选择指定的部分col，但是要特别注意：usecols 在pandas 0.20.3 之后有bug，不能指定cols = ['户主名称','收款方户名','收款方账号']这种格式，只能用 col = [2,6,7]。据说在0.23.0修复
可以通过names参数给指定col重新命名，这是一个包含所有字段名称的列表
可以通过converters参数(或dtype)指定col类型，这是一个字典

# 全部字段
#cols = ['类型','户主编号','户主名称','收款方账户类型','收款方开户机构','开户机构支行全称','收款方户名','收款方账号']
#names = ['type','code','name','acct_type','acct_org','org_name','acct_name','acct_code']

# 选择部分字段
cols = ['户主名称','收款方户名','收款方账号'] # pandas <=0.20 版本可以这样使用
# cols=[2,6,7] # 0.21 及之后的版本这样使用
names = ['name','acct_name','acct_code']


pddf_meta = pd.read_excel('./sample.xlsx', sheet_name='银行账户关系', header=0, usecols = cols, names=names, converters={'收款方账号':str} ).dropna()

使用Spark处理数据

把Pands DF转换成Spark DF
构造Spark DF的过程中，显式指定一个schema，避免类型错误

Pandas DF 转 Spark DF

from pyspark.sql.types import *

# 显示指定DF的结构
schema = StructType().add('name', StringType()).add('acct_name', StringType()).add('acct_code', StringType())

df_meta = spark.createDataFrame(pddf_meta, schema)
df_meta.show(truncate=False)

+---------+---------+-------------------+
|name     |acct_name|acct_code          |
+---------+---------+-------------------+
|环球中心顺兴小吃城|小明       |6228450123084500000|
|成都丹露小角楼  |小红       |6222629123002790000|
|环球中心顺兴小吃城|小明       |6228450123084500000|
+---------+---------+-------------------+

df_meta.registerTempTable('df_meta')
# 使用Spark SQL进行数据统计
df_stats = spark.sql('select acct_code, acct_name, count(1) as cnt from df_meta group by acct_code, acct_name order by 1,2')
df_stats.show(5)

+-------------------+---------+---+
|          acct_code|acct_name|cnt|
+-------------------+---------+---+
|6222629123002790000|       小红|  1|
|6228450123084500000|       小明|  2|
+-------------------+---------+---+

Union All 操作

Spark中的unionAll() 和 union() 是相同的方法，并不会进行去重操作（注意这一点和SQL区别）。如下，我们有意构造了一条重复数据。

官方推荐使用union()。

df_tmp_1 = df_stats
df_tmp_2 = df_stats.filter("acct_name in ('小明')")
df_result = df_tmp_1.union(df_tmp_2)
df_result.show()

+-------------------+---------+---+
|          acct_code|acct_name|cnt|
+-------------------+---------+---+
|6222629123002790000|       小红|  1|
|6228450123084500000|       小明|  2|
|6228450123084500000|       小明|  2|
+-------------------+---------+---+

增加序号

根据业务需求，在输出结果中需要给每一行增加一个序列号。我们分别使用SparkSQL和原生Spark的 row_number() 函数来实现，两种方法没有本质的区别。

SparkSQL方式

df_result.registerTempTable('df_result')
df_result_1 = spark.sql("select row_number() over(order by r.cnt desc) as rn, r.* from df_result r")
df_result_1.show()

+---+-------------------+---------+---+
| rn|          acct_code|acct_name|cnt|
+---+-------------------+---------+---+
|  1|6228450123084500000|       小明|  2|
|  2|6228450123084500000|       小明|  2|
|  3|6222629123002790000|       小红|  1|
+---+-------------------+---------+---+

Spark 函数方式

import pyspark.sql.functions as F
from pyspark.sql.window import Window

df_result_2 = df_result.withColumn('rn', 		F.row_number().over(Window.orderBy( F.col('cnt').desc() ) ) )\
        .select(['rn','acct_code','acct_name','cnt'])
df_result_2.show()

+---+-------------------+---------+---+
| rn|          acct_code|acct_name|cnt|
+---+-------------------+---------+---+
|  1|6228450123084500000|       小明|  2|
|  2|6228450123084500000|       小明|  2|
|  3|6222629123002790000|       小红|  1|
+---+-------------------+---------+---+

通过Pandas导出Excel

转换为Pandas DF
导出Excel

# 转换为Pandas DF，并且重新命名字段
pddf_meta_out = df_result_2.toPandas()

pddf_meta_out.columns = ['序号', '收款方账号', '收款方户名', '数量']

Single Sheet

pddf_meta_out.to_excel('./sample_out_single.xlsx',sheet_name='输出stats', index=False)

Multi Sheets

ExcelWriter可以增加多个sheet
通过startrow指定起始Cell的位置

writer = pd.ExcelWriter('./sample_out_multi.xlsx')
pddf_meta_out.to_excel(writer,sheet_name='输出stats1', index=False)
pddf_meta_out.to_excel(writer,sheet_name='输出stats2', index=False, startrow=1)
writer.save()

查看一下是否生成了输出文件

! pwd && ls -lh

/home
total 60K
-rw-rw-r-- 1 etl etl  14K May  2 20:44 pandas_excel_in_out.ipynb
-rw-rw-r-- 1 etl etl 6.0K May  2 20:45 sample_out_multi.xlsx
-rw-rw-r-- 1 etl etl 5.5K May  2 20:45 sample_out_single.xlsx
-rw-rw-r-- 1 etl etl  12K Apr 27 15:24 sample.xlsx

用Pandas读写Excel文件-输出单sheet和多sheet

背景