【Ansible×pandas】Excelからデータの入力/出力がしたい

はじめに

• バージョン
  • Python: 3.6.8
  • pandas: 1.1.5
  • openpyxl: 3.0.8
  • xlrd: 1.2.0
• 概要
  • Ansibleで自動化対応をしていると、Excelからデータを読み込めないか？と良く聞かれます。
    Ansibleの標準モジュールにはExcel用のが無いのでpythonで作ってみます。ただし、pythonの部分を
    作りこみすぎると、Ansibleのシンプルさが損なわれてしまうので、シンプルなコードを書きます。

今回はExcelファイル（sample.xlsx）の以下、2つのシートからデータを抽出します。
このデータから、ageが30以上のデータを新たなExcelに出力するコードを作成します。

• Sheet1
  

• Sheet2
  

1. サンプルコード

1-1. 実行用playbook

playbookと処理の流れは以下のようになっています。

• 1. Excelファイルの読み込み
  • 変数in_excelに記載されたExcelの内容を読み込む
• 1. 条件に合ったデータの抽出
  • ageが30以上のデータを抽出
• 1. Excelへの出力
  • 変数out_excelに記載されたExcelに結果を出力する

read_excel.yml

---
- hosts: localhost
  connection: local
  gather_facts: false
  tasks:
    - name: "input from excel"    # ポイント(1-1)
      script: >-
        ./script/input_excel.py {{ in_excel }}
      vars:
        in_excel: "/home/centos/ansible/data/sample.xlsx"
      register: input_result
      args:
        executable: python3

    - name: "fetch age >= 30"    # ポイント(1-2)
      set_fact:
        fetch_result: "{{ input_result_json | json_query(query_string) }}"
      vars:
        input_result_json: "{{ input_result.stdout  }}"
        query_string: "[?age >= `30`]"

    - name: "output to excel"    # ポイント(1-3)
      script: >-
        ./script/output_excel.py {{ out_excel }} "{{ fetch_result }}"
      vars:
        out_excel: "/home/centos/ansible/output/result.xlsx"
      args:
        executable: python3
      when:
        - fetch_result != []    # ポイント(1-4)

• ポイント

  • 1-1. Excelからデータを抽出する

    • scriptモジュールでは以下のように引数の指定が可能です

    • 引数はコード内でsysモジュールを使って取得します（詳細は後述）

      - name: "run script"
        script: sample.py 引数1 引数2
        args:
          executable: python3
      

  • 1-2. Excelからデータを抽出する

    • jmespathの使い方は過去の記事を参考にしてください
      • 【Ansible】json_queryでcontain/starts_with/ends_with/and/orを使ってみた

  • 1-3. Excelへの出力

    • scriptの記述をする部分で、**"{{ fetch_result }}"**のようにダブルクォーテーションで囲っています。
      このようにしないと、fetch_result内のスペースが引数と引数の境界だと認識されてしまうので注意してください。

  • 1-4. 出力結果が存在しない場合への対処

    • jmespathの結果が空であった場合は、処理をskipさせます

1-2. Excelデータ取得用script

以下が、Excelデータ取得用scriptです。処理の内容はコード内に記載してありますので、
そちらをご参照ください。また、記事の最後にdocumentのリンクを貼っておきます。

input_excel.py

import pandas as pd
import sys


# 引数を取得(引数の順番は1からスタート)
file_path = sys.argv[1]

# Excelからデータを取得する(sheet_name=Noneで全てのシート)
df = pd.read_excel(file_path, sheet_name=None)

for sh_name in df:

    # 新たな列(sheet)を作成し、シート名を記載
    df[sh_name]["sheet"] = sh_name

# シートごとのDataFrameを結合する(ignore_index=Trueで通し番号を削除)
df_all = pd.concat(df, ignore_index=True)

# DataFrameをdictに変換して出力する
print(df_all.to_dict(orient="records"))

1-3. Excelへの出力用script

以下が、Excelへの出力用scriptです。処理の内容はコード内に記載してありますので、
そちらをご参照ください。また、記事の最後にdocumentのリンクを貼っておきます。

outpu_excel.py

import pandas as pd
import sys
import ast

# 引数を取得(引数の順番は1からスタート)
file_path = sys.argv[1]
# ast.literal_eval(文字列)で文字列型 -> 辞書型へ変換  
name_list = ast.literal_eval(sys.argv[2])

# name_listをもとに、DataFrameを作成
df = pd.DataFrame(data=name_list)

# 列sheetから出力するシート名の一覧を取得(drop_duplicatesで列の項目の重複を排除)
sh_list = df["sheet"].drop_duplicates()

# 出力するExcelファイルのパスを指定
with pd.ExcelWriter(file_path) as writer:
    # シートごとにデータの出力を実施
    for sh_name in sh_list:
        # 出力するシート名に対応するデータを指定(dropで列sheetを削除)
        output_data = (
            df[df.sheet == sh_name]
            .drop("sheet", axis=1)
            .reindex(columns=["name", "age"])
        )
        # 出力するシート名に対応するデータを出力(index=Falseで行番号を削除)
        output_data.to_excel(writer, sheet_name=sh_name, index=False)

2. 実行結果

　想定通りの結果を得ることが出来ました。

• Sheet1
  

• Sheet2
  

まとめ

　pandasは内容が難しいですが、自動で表を作成してくれてデータ処理が楽になるので覚えておきましょう。

参考記事

script - Ansible Documentation
pandas.read_excel — pandas 1.3.2 documentation
pandas.concat — pandas 1.3.2 documentation
pandas.DataFrame.to_dict — pandas 1.3.2 documentation
pandas.DataFrame.drop_duplicates
pandas.DataFrame.drop — pandas 1.3.2 documentation
pandas.DataFrame.to_excel — pandas 1.3.2 documentation
JMESPATH - Home