概要

仕事でデータ分析などを担当しているのですが、pythonで分析しているとpandasはRのDataFrameのように扱うことができて、本当に便利だなあと日々実感しています。
また、データ分析職であればGoogleスプレッドシートでの集計やレポーティングを行う機会も多いです。

このスプレッドシートの操作を簡単にしたい、あわよくば自動化して業務負担を軽くしたいと考え、pandas使いの自分にとって使いやすいpythonクラスを作成したので共有する趣旨の記事です。

pythonコード

今回作成したクラスとデモ用のサンプルコードを自分のGitHubリポジトリに上げておくので、ご覧ください。
https://github.com/kazuki-hayakawa/SpreadsheetPython

また、Google Spreadsheet をAPI経由で使えるようにする設定まわりは以下の記事が丁寧で参考になります。その点についてはそちらを参照ください。
Pythonを使ってGoogleスプレッドシートを自動で読み書きする - ＷＩＣの中から

クラスの内容

コードはこちら。必要なライブラリは事前にpip installするなりしておいてください。

gshandler.py

# -*- coding: utf-8 -*-
import json
import pandas as pd
import numpy as np
import gspread
from oauth2client.service_account import ServiceAccountCredentials

class GspreadHandler(object):
    """
        python から Google SpreadSheet を使うときの関数など集めたクラス
    """
    def __init__(self, json_file_path):
        self.json_file = json_file_path
        self.scope = ['https://spreadsheets.google.com/feeds']
        self.gsfile = None


    def get_gsfile(self, sheet_id):
        """ 読み書きするスプレッドシートのインスタンスを取得 """
        credentials = ServiceAccountCredentials.from_json_keyfile_name(self.json_file, self.scope)
        client = gspread.authorize(credentials)
        self.gsfile = client.open_by_key(sheet_id)


    def dataset_fromSheet(self, read_sheet_name):
        """
            スプレッドシートの該当シートを読み込んで前処理を行う。出力は DataFrame 型。

            read_sheet_name: 読み込み元シート名
        """

        # データ読み込み
        raw_data_sheet = self.gsfile.worksheet(read_sheet_name)
        dataset = pd.DataFrame(raw_data_sheet.get_all_values())

        # 0行目を列名にする
        dataset.columns =  list(dataset.iloc[0])
        dataset = dataset.drop(0, axis=0)
        dataset = dataset.reset_index(drop=True)

        return dataset


    def update_CellsOfColumn(self, dataset_series, write_sheet_name, column_alphabet):
        """
            スプレッドシートのある列を一気に更新する関数。
            ただし1行目には既にヘッダが入力されている想定で2行目以降に数値を入れる。

            dataset_series: 更新用dataframeの該当列。
            update_sheet: アップデート先スプレッドシートのインスタンス。
            column_alphabet: 更新する列名のアルファベット。
        """

        update_sheet = self.gsfile.worksheet(write_sheet_name)

        # 更新範囲指定。A2:A100 のような文字列をつくる
        data_range = column_alphabet + '2:' + column_alphabet + str(len(dataset_series)+1)
        cell_list = update_sheet.range(data_range)

        for (i, cell) in enumerate(cell_list):
            cell.value = str(dataset_series[i])

        update_sheet.update_cells(cell_list)

基本的な関数は２つです。

dataset_fromSheet … シート名を指定してそのシートの値を全て取得する
update_CellsOfColumn … DataFrameのSeriesを指定して、スプレッドシートの特定のカラムに書き込む

これらを作成した意図としては、『何かスプレッドシート上にあるデータを取得してきて、諸々の計算をして再度スプレッドシートに書き込む』という状況が頻繁に発生しており、それらを効率化、または自動化するためです。

実際に操作してみる（デモ）

デモのために 何かの生データをスプレッドシートから取得して、KPIの数値を計算し、再度スプレッドシートに書き込んで更新する という状況を想定します。

スプレッドシートにはいま以下のような数値が入力されているとします。

スプレッドシートを呼び出すためにシート名などの変数を定義します。json_fileはOAuth用クライアントIDの作成時に作成されたjsonファイルを指します。

# -*- coding: utf-8 -*-
import pandas as pd
from gshandler import GspreadHandler

json_file = 'YOUR_JSON_FILE_PATH'

# URL https://docs.google.com/spreadsheets/d/xxxxxxxxxxx の xxxxxxxxxxx 部分のこと
sheet_id = 'SPREADSHEET_ID'

read_sheet_name = 'dataset'
write_sheet_name = 'reporting'

次に、スプレッドシートのクラスのインスタンスを作成し、read_sheet_nameの名前のシートからデータを取得します。
注意点としては、スプレッドシートのセルに数値が入っていても取得した値は文字列型になっているので、適切にデータ型を直してあげる必要があります。

# スプレッドシートのクラスのインスタンスを作成、ファイル情報を読みとる
gh = GspreadHandler(json_file)
gh.get_gsfile(sheet_id)

# read_sheet_name のシートの値を取得。DataFrame型で取得。
dataset = gh.dataset_fromSheet(read_sheet_name)

# spreadsheet から読み取れるデータは文字列型なので、floatに直す。
dataset['hoge_data'] = dataset['hoge_data'].astype(float)
dataset['fuga_data'] = dataset['fuga_data'].astype(float)

ではKPIを計算しましょう。

# KPIを計算する。
dataset['hogehoge_KPI'] = dataset['hoge_data'] + dataset['fuga_data']

pandasであれば列ごとにまとめて処理するケースが多いと思います。今回もdataset['hogehoge_KPI']の計算値をまとめてスプレッドシートに書き込みたいところです。
書き込みたいシート名のシートを作成し、以下の画像のように事前にカラムを決めておくと便利です。

実際に列ごとにまるごと更新してみましょう。

# write_sheet_name のシートにデータを書き込む。
# 1列ごとにまとめて書き込む。書き込み先の列名はアルファベットで指定。
gh.update_CellsOfColumn(dataset['id'], write_sheet_name, "A")
gh.update_CellsOfColumn(dataset['hoge_data'], write_sheet_name, "B")
gh.update_CellsOfColumn(dataset['fuga_data'], write_sheet_name, "C")
gh.update_CellsOfColumn(dataset['hogehoge_KPI'], write_sheet_name, "D")

これでデータが以下の画像のように書き込まれます。

あとは、この数値をもとにグラフを描くもよし、数値をそのままレポーティングするもよしです。

いかがでしたでしょうか。作った本人が一番愛用しています笑
これのおかげでスプレッドシートの操作が大変便利になりました。
当記事をご覧頂いたpandas使いの方にもぜひ使っていただければと思います。

参考文献

Pythonを使ってGoogleスプレッドシートを自動で読み書きする - ＷＩＣの中から
本文中でも触れていますが、APIの認証設定まわりは大変参考になりました。

[python] pandas使いにとって使いやすいGoogleスプレッドシート操作クラスを作った

概要

pythonコード

クラスの内容

実際に操作してみる（デモ）

参考文献