用Pandas讀寫Excel檔案-輸出單sheet和多sheet

wait4friend發表於2018-05-15
Excel

背景

展示透過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
複製程式碼

相關文章