【Python】PandasでExcelファイルを読み込んでtsvファイルにおこす

今回はタイトル通り、 Excelファイルから新たにtsvファイルを生成させるコマンドラインツールなるものを作成しました。

よく会社でG suiteでデータをスプレッドシートなんか管理されているかと思います。 だんだんデータが溜まっていくと、開くのに時間がかかったり、大事な情報だときちんとヴァージョン管理したいと思ったりしたことがあるかと思います。

また、スプレッドシートだと誰かが変更したりすると履歴が追いにくい... (※ 最近？また履歴確認機能がアップデートされましたが、「なんかな〜　ん〜　見にくいというかそもそも履歴とか見ない」とかとか..) ミスってコピペする場所間違えたのに気づかない... とかあるかと思います。

そこで、Githubで管理してみるか！となったのはいいものの、 GithubだとExcelファイルそのまま管理させても表示されないですよね。

なので、Githubでは、csv/tsvファイルに対してRenderingされる機能があるのです。 まあ既に知ってる方もいるかと思います。

さて、Excelファイルからcsv化しよう！となるわけです。

保存するときに拡張子を変更すれば問題ないです。 ただ、「どうせ管理するなら、このファイル○○毎に分けて管理しない？」ってなります笑

ならないならいいんです笑 ただ、スプレッドシート→Excelファイルを読み込んで、新たにある規則に基いてcsv化しようってなって、 さらにデータ(セル)が多いと中々手作業だと時間が取られる作業になってしまいます。

なので、Pythonを使ってあるワードに対して、ワードにマッチした行をヘッダー付で抽出し、新たに(私の場合は)tsvファイルを生成しようというわけです。

Pythonのコマンドラインツールみたくなるようにしました。

イメージとしては以下。

$ python3 create_tsv.py --file target.xlsx --word target_word

こんなかんじでローカルで実行すると、同階層にtsvファイルを生成され、 そのままGithubにあげるとこんなかんじで参照できるようにします。

ちなみにこのRendering機能では、フィルタできる機能もデフォルトであるのでシンプルに使えて便利です。

さて、実行環境を確認していきます。

開発環境

言語: Python (version 3.5.0) Pythonのversionを簡単に切替できるツールは、過去記事にあるので、よかったら観覧してみて下さい。 OS: macOS 主なlibrary: pandas (version .22.0), xlrd

準備

Excelファイル

今回は元となるExcelファイルが必要です。

想定するExcelファイルはこんなかんじとします。

			xx		xx
No	列一	列二	列三	列四	列五	列六
1	aaaa	bbbb	cccc	dddd	eeee	ffff
2	aaaa	bbbb	cccc	dddd	eeee	ffff
3	zzzz	bbbb	cccc	dddd	eeee	ffff
4	aaaa	bbbb	cccc	dddd	eeee	ffff
5	zzzz	bbbb	cccc	dddd	eeee	ffff
6	aaaa	bbbb	cccc	dddd	eeee	ffff
7	aaaa	bbbb	cccc	dddd	eeee	ffff

ちょっと分かりづらいですが、上2段がheader(ヘッダー)です。 xxも何かしらのheaderだと思って下さい。

このheaderが2つある(しかもheaderの上段と下段でheader数が相違している)ことで私はハマりました... それは後ほど説明します。

こうしたExcelファイルを置いておきます。

Pythonファイル

とりあえずsample.pyとしてPythonファイルを作成します。

ライブラリインストール

$ pip3 install pandas
$ pip3 install xlrd

pandas

今回pandasという、主にデータ解析を得意とするライブラリを使っていきたいと思います。 知ってたのですが、ちゃんと触る機会がなかったので、面白かったです！

xlrd

xlrdは、Excel(xls)のデータをPythonで読むためのライブラリです。 xlrd自体のExcelデータの抽出順序は、book → sheet → cell です。

準備はこれくらい。

本来、requirements.txtを作成しておけばいいですが、今回はそんなボリューム感あるコードでないので割愛します。

コード書いていく

さきほど作成したsample.pyを開いて、さっそくコード書いていきます。

1. ライブラリの定義とコマンドラインオプション

まずは、ライブラリのimportから。

# coding:utf-8

import pandas as pd
import sys
import argparse

pipでインストールしたライブラリを定義しておきます。

続いて、イメージ通り作成するとなると、コマンドラインからオプションを指定することで 後々使い勝手がよくなりますので、オプションを受け付けるようにします。

ここではargparseを使います。 コードがどんな引数を期待するのか定義するためです。 もし、想定外の引数がわたってきたらエラーを発生させます。

def get_args():
    parser = argparse.ArgumentParser()
    parser.add_argument("--file", type=str)
    parser.add_argument("--word", type=str)
    arg = parser.parse_args()
    return(arg)

--fileと--wordをオプションで受け付けるようにしました。

argをreturnしているので、

option = get_args()

として、option.fileみたいにコード内で使えます。

2. Excelファイルを読み込んでシートを確認する

まずは、Excelファイルを読み込みます。

# get Excel file from Command line
excel_file = './' + option.file

# Open excel_file (xls,xlsx)
input_file = pd.ExcelFile(excel_file)

ここでpandas登場です。 pd.ExcelFile()で、ExcelfileオブジェクトとしてPythonから参照できます。

そして、シートを取得します。 もちろんExcelファイルによっては、シートが沢山あるものも存在しますよね。

# get Sheets
sheets_name = input_file.sheet_names

これでsheets_name[0]とかやってシート名を参照します。

3. DataFrameとして対象のシートの読込

ここでparseという関数を使用します。 こうすることで、対象のシートをDataFrameとして取得します。 パラメータはこれ以外にもあるので、公式を参照しましょう。

df_sample = input_file.parse(sheets_name[0],
                              skiprows=1,
                              header=[0,1],
                              index_col=0,
                              names=headers,
                              usecols="B:E,G:H" 
                              )

第一引数には、対象シートを指定します。

• skiprows 対象シートの一番上の行から、いくつ読み飛ばすか指定します。 今回は1行飛ばすかんじです。

• header skiprowsでスキップしたところから、headerが何行あるか(もしくはheaderの有無)を指定します。 今回は、headerが2行ある想定なので[0,1]を指定。

• index_col indexとするcolumn名を指定します。 今回は、No.の列をindex扱いにしようかと思います。

• names カラムの名前を指定します。 今回だとheadersという変数を指定しています。後ほど詳細を説明します。

• usecols 特定の列を読み込む場合に指定します。 今回だとB:E,G:Hなので、ExcelファイルのB〜E列とG〜H列を抽出させます。

これでDataFrameとして色々なメソッドを使用してアクセスできるようになりました。

4. 指定したwordにあったデータを抽出する

コマンドラインからオプションで指定するword(例: --word zzzz)のみを抽出する必要があるので、 DataFrameにアクセスして対象を検索します。

今回は、列1のzzzzという文字列を検索すると想定します。

df_ordered = df_sample[df_sample.ix[:, 0] == option.word]

データにアクセスする方法として、今回はixを使用します。 開発中、locやilocも使用したのですが、headerが2行あることでなのか？うまくいかずでした...

こちらが参考になりました。

行ラベルを指定せず、列ラベルを指定します。(intで)

5. 不要な列を除外

列4を除外する想定です。 dropメソッドを使用します。 columnsを指定することで簡単に除外できます。

df_dropped = df_ordered.drop(columns=[('', '列四')])

いざtsvファイルとして吐き出す

to_csvメソッドですが、tsvはタブ区切りなので問題ないです。 パラメータsepで指定します。

output_file = 'output_' + option.table + '.tsv'
df_dropped.to_csv(output_file,
                sep="\t",
                header=True,
                encoding="utf-8"
                )

ここまでできればもう出力されます。

が、1点記事上部で述べていたheaderが2行あること(header数がheader同士で相違)でおこる問題がありました。 私がまだpandas初心者なのでうまい方法があったかもしれませんが、詰まったのでメモです。

3. DataFrameとして対象のシートの読でnamesの指定を変数にしました。 結果から言うと、これが解決策でした。

どういうことかと言うと、 基のExcelファイルでheaderが2行あって、尚且つheader数が一致しないと headerがMultiIndexとして扱われていました。

そのせいでコマンドラインから指定するwordとの判定にも苦労しましたし、 不要な列の削除するdropメソッドのところも苦労しました。

drop(columns=[('', '列四')])となっていますが、この正体は以下になります。 (これがheaders変数)

headers = pd.MultiIndex.from_tuples([('', '列一'), ('', '列二), ('xx',列三'), ('xx',列四'), ('',列五')])

pandasのfrom_tuplesを使いました。 これで明示的にMultiIndexの階層化するわけです。 今回はparseメソッドのパラメータに直接ぶち込みました笑 本来であれば、DataFrameにする際のcolumnsパラメータでうまいこと指定するようです。

というわけで、headers変数は、ファイルの上部に定義しておきましょう！

さて、これで以上です。 あとは、コマンドラインからpythonコマンドを実行して、 生成されたtsvファイルをそのままgithubにあげましょう！

pandasを初めてちゃんと触りましたが、非常に面白かったです。 これからも少しずつ触ってもう少し慣れたらと思います。