inFablic | Fablic, inc. Developer's Blog.

フリマアプリ ラクマを運営する Fablic の公式開発者ブログです。Fablic のデザイナー・エンジニア・ディレクターが情報発信していきます。

大量のデータが含まれるExcelファイルをコマンドラインで前処理する

こんにちは。shobyです。

皆さんは大量のデータが含まれるExcelファイルに対して、 分析用に前処理をする必要が生じたことはありませんか? 私はあります。

今回は、巨大なExcelファイルに対して前処理を行う方法についてご紹介します。

※今回の記事のインストール方法に関しては、macOS+Homebrew環境を想定しています。

概要

  • CSVに変換する
  • CSVに対して前処理を行う

CSVに変換する

Excelファイルのままでは処理がしづらいため、まずはCSVに変換します。 これには、LibreOfficeを使います。

ja.libreoffice.org

LibreOfficeはMicrosoft Officeの代替ツールとして有名ですが、 コマンドラインからheadlessで呼び出せるモードが付いており、 Officeファイルのconverterとして利用することができます。*1

LibreOffice ソフトウェアをパラメーターを指定して起動する - LibreOffice Help

LibreOfficeのインストール

MacではHomebrew-Caskで簡単にインストールすることができます。

brew cask install libreoffice

Excelファイルの変換コマンド

以下のようなコマンドでExcelファイルをCSVに変換することができます。

soffice --headless --convert-to csv --infilter=CSV:44,34,76,1 --outdir output_dir file_name.xlsx

infilterオプションに指定している数字は以下のような意味です。

  • 44:separatorにcommaを用いる
  • 34:fieldをdouble quoteで囲う
  • 76:UTF-8を用いる
  • 1:standard形式

それ以外のオプションの詳細は以下のリンクをご参照ください。

Filter Options - Apache OpenOffice Wiki

unoconv/unoconv.1.adoc at master · dagwieers/unoconv · GitHub

CSVに対して前処理を行う

CSVに変換することができれば、お好みの方法で前処理をすることができます。

今回は、一般的なawkやsed以外で、私が使っている方法をいくつかご紹介します。

xsv

xsvを用いることで、カラムの抽出や、シンプルな条件での検索、特定行数でのファイルの分割などを高速に行うことができます。

GitHub - BurntSushi/xsv: A fast CSV toolkit written in Rust.

インストール

brew install xsv

使用例

例として、ファイル分割をする方法をご紹介します。

CSVファイルの分割は、分割後の各ファイルにheaderを含める必要があったりと、なかなか面倒ですが、 xsvを使うことで簡単に行うことができます。

xsv split --size 10000 --filename file_name_split_{}.csv output_dir file_name.csv

このようなコマンドを打つと、以下のようにファイルを分割してくれます。

  • file_name_split_0.csv
  • file_name_split_10000.csv
  • file_name_split_20000.csv

q

qを用いることで、SQLライクな文法で、複雑な条件での検索を行うことができます。

Run SQL directly on CSV files | Text as Data | q

インストール

brew install q

なお、Python 3系では動かないため、実行時にはpyenvやvirtualenvを用いて2系を使用する必要があります。

使用例

例として、CSV中に含まれるflagが1のデータのみ抽出する方法をご紹介します。

q "SELECT * FROM file_name.csv WHERE flag = 1"

このように、複雑な条件での前処理が可能です。

まとめ

巨大なExcelファイルはLibreOfficeをconverterとして利用し、CSVに変換することで自由に前処理を行うことができます。 前処理にはxsvやqを用いると便利です。

*1:Office Converterとして有名なunoconvも内部ではLibreOfficeを利用しています