ETLパターン：ローカルのよく知られたファイルを扱う

bharold

ETLパターンの連続体の中で最も単純なものは、最も一般的な形式であるCSV（カンマ区切り値）と、その近縁であるExcelスプレッドシートです。これらの形式は膨大なデータが移動し、簡単に共有でき、人間が読みやすいため、ArcGISで嫌う理由はほとんどありません。しかし、問題点もあります！

• フォーマットに適切なスキーマが組み込まれていない
• 列名がArcGISで有効でないことが多い
• 数値を含むテキストフィールドが数値として扱われる
• テキストフィールドの幅はCSVでは8000バイト、Excelでは255バイトと仮定される
• 整数フィールドが倍精度浮動小数点として扱われる場合がある
• Null値がゼロや他のありえない値でエンコードされている場合がある
• ジオメトリがフィーチャクラス作成に対応していない形式でエンコードされていることが多い

data.govからのこの人気データセットは、ワシントン州のオープンデータポータルからミラーされています：

まず、地図としてのデータ：

Washington EV Population

次に元のCSVファイル：

CSVまたはExcelのデータ問題

赤字で示したのは、これらのよくある違反例です。列名にスペースやメタ文字が含まれている、USPS郵便番号は先頭にゼロがある可能性があるためテキストであるべき、Electric RangeとBase MSRP列ではゼロがNull値として使われている、Vehicle LocationはWKT形式、2020 Census Tractには大きな整数と数字で始まる名前があります。

このデータセットは頻繁に更新されるため、もし使用に興味があれば、このような問題をジオプロセシングで自動化して処理したいところです。

まずは調査です！ブログのダウンロードにはCSVファイルの最大データ幅をスキャンするために使ったスクリプトツールがあります。以下がそのコードです：

 

tbl = arcpy.GetParameterAsText(0)
d = arcpy.da.Describe(tbl)
flds = [f.name for f in d['fields'] if f.type == 'String']
mDict = {f:0 for f in flds}
with arcpy.da.SearchCursor(tbl,flds) as cursor:
    for row in cursor:
        for f in flds:
            if row[flds.index(f)]:
                mDict[f] = max(mDict[f],len(row[flds.index(f)]))
for k in mDict.keys():
    arcpy.AddMessage(f"""Input table '{tbl}' field '{k}' has maximum data width '{mDict[k]}'""")

 

これにより以下の結果が得られます：

最大テキストフィールド幅

 郵便番号列（地元の知識によれば5文字幅）を除外すると、各テキストフィールドに必要な幅がわかります。

これで対象ファイルを正しく処理するスキーマ設計が可能になりました。私はModelBuilderを選びました。これはコアジオプロセシングやPython関数をラップできるためです。完成したモデルはこちらで、一緒に見ていきましょう。

EVPopulationモデル

最初の作業は設計したスキーマを適用することです。これはExport Table内のフィールドマップコントロールを使って行います。基本的には望むスキーマでインメモリテーブルにデータをコピーします。フィールドマップコントロールでは名前変更、型変換、さらにはフィールド構築も柔軟にできます。

今回は出力フィールドごとにソースフィールドが一つだけなので、Actionは常にFirstであり、フィールドプロパティのみ操作しています。

フィールドマップ

Export Tableの下流では、インメモリテーブルをテンプレートとして出力フィーチャクラスを作成し（まだジオメトリなし）、行を追加し、その後WKT列からジオメトリを作成し、ゼロエンコードされたNull値を持つ2つのフィールドはCalculate Fieldツール（条件付き処理あり）で修正します。最後にVehicle Locationフィールドは不要なので削除します。

これで終わりでしょうか？いいえ！ベストプラクティスは完全自動化であり、このデータはウェブから来ています。

ファイルダウンロードに使うURLはモデルが期待するジオプロセシング用Text File入力として有効ではありません。またユーザーがとは異なる名前のCSVファイルを入力すると、Export Table内のフィールドマップがデフォルト処理に戻り、設計したスキーマ設定が無効になるリスクもあります。

ウェブデータ入力と一貫したファイル名入力の両方を扱うため、このモデルEVPopulationは親モデルURL2EVPopulationにラップされています：

URL2EVPopulationWeb Download

まずCalculate Valueモデルツールが現在のデータを一貫した名前のText Fileパラメータータイプファイルへダウンロードし、その後EVPopulationモデルが処理を行います。非常にシンプルなPythonスニペットで自動化できる様子をご覧ください。😉

これで自動化されたCSVファイル処理が実現しました！「ローカルよく知られたファイル」の概念を少し拡張してウェブからデータ取得しましたが、今やインターネット上のファイルもローカルなのです。 いつでも好きな時にURL2EVPopulationをスケジュールまたは手動実行できます。

モデルとスクリプトツールはブログダウンロード内にあります。