そうだ、Pandas(python)にしよう

　社内のエクセルをどうにかするのを、2020年にもなったのでさすがにVBAは止めて、そろそろ、Pandas&pythonにしよう、という小話。
　以下、Python入門記事とかは読んだことある人向けなので、細かい解説は抜き。
Pandasやpythonの情報はwebにはいっぱい転がっているから、Pandas&pythonを使ってみようと思った良い子は、困った時は、いろいろググって見てね。

(学生さん向け？の付記)　コンプライアンス強化がうたわれる中で...

　テレビCMなんかで多くの人が名前はご存知だったりする、いわゆるユーザー企業というところでは、平成の古(いにしえ)のAcesssでのクエリがどこぞのデータベースにクエリを発行してから個人情報入りのファイルをエクセルに出力して...そのあと、ローカルでちまちまと切り貼りして何か別の用途に使ったりなんて事務作業があったりします。
　上場企業の場合、こうした作業がコンプラ的に微妙とか言われちゃうことがしばしばあるのです(誰が個人情報を目にしたのか、すべて明らかにする、といったところがコンプラ的な要請の例)。
　そんな時は、取り急ぎ、AWSなどのサーバ上のWindowsでAccessを動かすようにして、定時実行で出力されたCSVをサーバ側で処理を完結させるようにしちゃえば、ひとまずコンプライアンス的にオーケーなわけです。AWSならば、SystemsManagerでスクリプトを走らせてS3にファイル置いてしまう...といったあたり。ところが古いAccessがある企業では、それに付随したVBスクリプトだのVBAだのがあって、辛いので、もうそこは Pandas&pythonに置き換えちゃいなよという話。

[1]準備作業:エクセルの特定シート手作業支度

1-1.pandasでエクセルを読めるようにライブラリをインストール

$ pip install xlwt
$ pip install openpyxl

pnpってなんですか、という方はPandas&pythonの日本語解説で外さない鉄板、こちらのnkmk様あたりからいろいろググってください:

https://note.nkmk.me/python-pandas-to-excel/

1-2.pandasでエクセルのn番目のシートを読む。

Excelのシートのどれか一つをサクッと読めると便利ですよね。
Pandasでは、ExcelのシートをDataFrame(df)というものにすぐに変換できます。

.py

n=0 #pandasではエクセルのシートの右から順に0,1,2...と指定します。
# targetというファイル名のn番目のシートを読む例
df= pd.read_excel(target, sheet_name=n)

、ExcelのシートをDataFrame(df)として読み込むのは、その一部分だけを取り出したり、特定の列を消したり加えたりできるところ。

#[2] Dataframeの一部を切り出して、連番を1番から振り直す

2-1.連番付与関数の例

こんな感じ。連番について、エクセルは1始まり、pandasは0始まりの文化であることに注意。
連番振りたいカラム名は適宜指定してください。

.py

# new_index_col列(ディフォルト値は"連番")に1から連番を付与する関数　
def getPartDf(df,start, step=5, debug= True, new_index_col="連番"):
    dfx = df[start:start+step].reset_index()
    dfx[new_index_col] = dfx.index +1 #連番改めて付与。ここを1以外にすれば、
    dfy = dfx.set_index(new_index_col)
    return dfy if debug else dfy.drop("index", axis=1)

(私のように)馬鹿でかいエクセルファイルから読んだDataFrameを使う場合、ほんとに読めたか確かめたくなるだろうから、元のindexも残すようするdebugモードも用意(注、)

2-2.forループで連番付与関数を呼び出す例

range関数を用いて一定ステップ(step_num)毎にループを回します。

.py

core_name = """切り出し後ファイル"""
step_num= 10000
debug = False

# step_numずつExcelの行を別ファイルに切り出していく処理。
for i in range(0, len(df), step_num):
    print(f"{i}以上{i+step_num}未満")
    savedDf = getPartDf(df,i, step_num, debug)
    if (debug):
        print(savedDf.head())
    fn = f"{out}/{core_name}{i}_{i+step_num -1}.csv"
    savedDf.to_csv(fn, encoding="cp932")

端数はpandasがよしなに処理してくれます。

実行例:数十万行あるエクセルシートを処理した場合

debug =Falseの場合

画面出力:

0以上10000未満
10000以上20000未満
20000以上30000未満
30000以上40000未満
40000以上50000未満
50000以上60000未満
60000以上70000未満
(略)
len 836543

出力されたエクセルファイルの一部(拡張子表示しないと何が何やらですが...):

ちなみに、でかいExcel相手だと処理には結構時間がかかります。
この記事は、その待ち時間に書いたともいう。。