パソコンスキルの教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

PythonでGoogleスプレッドシートのデータ集計・分析をする方法|プログラムソースを解説

python google spreadsheet data analysis

Pythonを使うとGoogleスプレッドシートのデータを読み込んだり、データを書き込んだりできます。

そこで、Pythonを使ってGoogleスプレッドシート上のデータを読み込み、データを表にして出力するプログラムを紹介します。

●この記事で分かること

・PythonでGoogleスプレッドシートのデータを読み込み、IF文で場合分けする

・Pythonで日付を比較する

・PythonでGoogleスプレッドシートの特定範囲にデータを出力する

それでは以下で詳しく紹介していきます。

プログラムの概要

この記事では、Googleスプレッドシート上の売上データから表に計算するプログラムを解説します。

実際に以下の作業をpythonで行うことを目指します。

f:id:gene320:20190421033409j:plain

プログラム実行前と実行後

プログラム実行前と実行後のGoogleスプレッドシートの状態は以下です。

f:id:gene320:20190421033845j:plain

PythonでGoogleスプレッドシートのデータを集計し書き出すコード

PythonでGoogleスプレッドシートのデータを集計し書き出すコードは以下です。

#ステップ1|ライブラリの設定
import gspread
from gspread_dataframe import get_as_dataframe, set_with_dataframe
from oauth2client.service_account import ServiceAccountCredentials
import datetime

#ステップ2|Google APIとの接続
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name("---あなたのGoogleアカウントで取得すること---.js", scope)

#OAuth2の資格情報を使用してGoogle APIにログインします。
gc = gspread.authorize(credentials)

#ステップ3|スプレッドシートからデータを取得
#共有設定したスプレッドシートキーを変数[SPREADSHEET_KEY]に格納する。
SPREADSHEET_KEY = '---GoogleスプレッドシートのURL---'

#各シートをws1, ws2とする
ws1= gc.open_by_key(SPREADSHEET_KEY).worksheet('データ') 
ws2= gc.open_by_key(SPREADSHEET_KEY).worksheet('集計') 

#全情報をvaluesに配列として入れ込む
values1=ws1.get_all_values()
values2=ws2.get_all_values()

#ws1の行数を取得
lastrow1 = len(values1)

#ws2の行数と列数を取得
lastrow2 = len(values2)
lastcol2 = len(values2[0])

#集計開始日と集計終了日を取得する
startdate=datetime.datetime(int(values2[1][1]) , int(values2[1][2]) , int(values2[1][3]))
enddate=datetime.datetime(int(values2[2][1]) , int(values2[2][2]) , int(values2[2][3]))

cell_list= ws2.range("B7:F" + str(lastrow2))

#ステップ4|取引先、担当者、日付が合致するときの取引金額を合計し、各値を配列に格納する
for i in range(6, lastrow2):
    for j in range(1, lastcol2):
        counter = 0
        for k in range(1, lastrow1):
            if  values1[k][1] == values2[i][0]:
                if values1[k][2] == values2[5][j]:
                    hiduke=values1[i][3].split("/")
                    torihikidate = datetime.datetime(int(hiduke[0]) , int(hiduke[1]) , int(hiduke[2]))
                    if startdate <= torihikidate <= enddate:
                        kingaku=values1[k][4].replace(',', '')
                        counter = counter + int(kingaku)
        for cell in cell_list:
            if cell.row==i+1 and cell.col == j+1:
                cell.value = counter

#ステップ5|Googleスプレッドシートに表データを出力する
ws2.update_cells(cell_list) 

以下でプログラムについて解説していきます。

Pythonプログラムの解説

この記事で紹介するプログラムは以下の5つのステップに分けて説明していきます。

ステップ1|ライブラリの設定

ステップ2|スプレッドシートからデータを取得

ステップ3|スプレッドシートからデータを取得

ステップ4|取引先、担当者、日付が合致するときの取引金額を合計し、各値を配列に格納する

ステップ5|Googleスプレッドシートに表データを出力する

以下で一つずつ紹介していきます。

ステップ1|ライブラリの設定

#ステップ1|ライブラリの設定
import gspread
from gspread_dataframe import get_as_dataframe, set_with_dataframe
from oauth2client.service_account import ServiceAccountCredentials
import datetime

このプログラムでは、Googleスプレッドシートの情報を読み込んだり書き込んだりするためのライブラリを使用します。

また日付比較をするために、datetimeのライブラリも使います。

ライブラリの読み込みはこちら(外部サイト)の記事を読むと分かりやすいです。

ステップ2|スプレッドシートからデータを取得

#ステップ2|Google APIとの接続
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name("---あなたのGoogleアカウントで取得すること---.js", scope)

#OAuth2の資格情報を使用してGoogle APIにログインします。
gc = gspread.authorize(credentials)

PythonでGoogleスプレッドシートを操作するためには、GoogleのAPIを利用できるように設定する必要があります。

とくに、このプログラムの4行目の「"---あなたのGoogleアカウントで取得すること---.js"」は各ユーザーが自分で設定しないといけない箇所です。

設定方法はこちら(外部サイト)の記事を読むと分かりやすいです。

ステップ3|スプレッドシートからデータを取得

#ステップ3|スプレッドシートからデータを取得
#共有設定したスプレッドシートキーを変数[SPREADSHEET_KEY]に格納する。
SPREADSHEET_KEY = '---GoogleスプレッドシートのURL---'

#各シートをws1, ws2とする
ws1= gc.open_by_key(SPREADSHEET_KEY).worksheet('データ') 
ws2= gc.open_by_key(SPREADSHEET_KEY).worksheet('集計') 

#全情報をvaluesに配列として入れ込む
values1=ws1.get_all_values()
values2=ws2.get_all_values()

#ws1の行数を取得
lastrow1 = len(values1)

#ws2の行数と列数を取得
lastrow2 = len(values2)
lastcol2 = len(values2[0])

#集計開始日と集計終了日を取得する
startdate=datetime.datetime(int(values2[1][1]) , int(values2[1][2]) , int(values2[1][3]))
enddate=datetime.datetime(int(values2[2][1]) , int(values2[2][2]) , int(values2[2][3]))

cell_list= ws2.range("B7:F" + str(lastrow2))

以下で解説を加えていきます。

SPREADSHEET_KEY = '---GoogleスプレッドシートのURL---'

1行目|「SPREADSHEET_KEY = '---GoogleスプレッドシートのURL---'」は以下のURLを記載します。

https://docs.google.com/spreadsheets/d/---GoogleスプレッドシートのURL---/edit#gid=~~~~~~~]

この「---GoogleスプレッドシートのURL---」の部分を使います。

#各シートをws1, ws2とする
ws1= gc.open_by_key(SPREADSHEET_KEY).worksheet('データ') 
ws2= gc.open_by_key(SPREADSHEET_KEY).worksheet('集計') 

2行目|シート名「データ」をws1と設定します。

3行目|シート名「集計」をws2と設定します。

#全情報をvaluesに配列として入れ込む
values1=ws1.get_all_values()
values2=ws2.get_all_values()

2行目|シート名「データ」の全ての値をvalues1として2次元配列として格納する

3行目|シート名「集計」の全ての値をvalues2として2次元配列として格納する

#ws1の行数を取得
lastrow1 = len(values1)

#ws2の行数と列数を取得
lastrow2 = len(values2)
lastcol2 = len(values2[0])

2行目|シート名「データ」の最終行をlastrow1として取得する

5行目|シート名「集計」の最終行をlastrow2として取得

6行目|シート名「集計」の最終列をlastcol2として取得

#集計開始日と集計終了日を取得する
startdate=datetime.datetime(int(values2[1][1]) , int(values2[1][2]) , int(values2[1][3]))
enddate=datetime.datetime(int(values2[2][1]) , int(values2[2][2]) , int(values2[2][3]))

2行目|集計開始日startdateとしてデータを取得する(values2の配列からシート「集計」のB2,C2,D2セルの値に相当する部分を取得)

3行目|集計終了日enddateとしてデータを取得する(values2の配列からシート「集計」のB3,C3,D3セルの値に相当する部分を取得)

cell_list= ws2.range("B7:F" + str(lastrow2))

1行目|データを入れ込む先であるシート「集計」のB7~F26を取得(ただし、26はlastrow2として取得し、str型を指定している)

ステップ4|取引先、担当者、日付が合致するときの取引金額を合計し、各値を配列に格納する

#ステップ4|取引先、担当者、日付が合致するときの取引金額を合計し、各値を配列に格納する
for i in range(6, lastrow2):
    for j in range(1, lastcol2):
        counter = 0
        for k in range(1, lastrow1):
            if  values1[k][1] == values2[i][0]:
                if values1[k][2] == values2[5][j]:
                    hiduke=values1[i][3].split("/")
                    torihikidate = datetime.datetime(int(hiduke[0]) , int(hiduke[1]) , int(hiduke[2]))
                    if startdate <= torihikidate <= enddate:
                        kingaku=values1[k][4].replace(',', '')
                        counter = counter + int(kingaku)
        for cell in cell_list:
            if cell.row==i+1 and cell.col == j+1:
                cell.value = counter

以下で解説を加えていきます。

for i in range(6, lastrow2):
    for j in range(1, lastcol2):
        counter = 0

range(開始, 終了)でfor文を行う範囲を指定できるので、以下のようにiとjを繰り返しで処理します。

1行目|i を 6からlastrow2の範囲でfor文の繰り返しを使います。

2行目|j を 1からlastcol2の範囲でfor文の繰り返しを使います。

3行目|counter=0として初期化します。

        for k in range(1, lastrow1):
            if  values1[k][1] == values2[i][0]:
                if values1[k][2] == values2[5][j]:
                    hiduke=values1[i][3].split("/")
                    torihikidate = datetime.datetime(int(hiduke[0]) , int(hiduke[1]) , int(hiduke[2]))
                    if startdate <= torihikidate <= enddate:
                        kingaku=values1[k][4].replace(',', '')
                        counter = counter + int(kingaku)

1行目|k を 1からlastrow1の範囲でfor文の繰り返しを使います。

2行目|「データ」シートのB列の値(取引先)と「集計」シートのA列の値(取引先001~取引先020)をif文で比較します。

3行目|「データ」シートのC列の値(担当者)と「集計」シートの6行目の値(担当者A~担当者E)をif文で比較します。

4行目|「データ」シートのD列の値(日付)を"/"で区切ります。

5行目|4行目で区切った値を日付に変換します。

6行目|5行目で変換した日付が集計期間に入っているかをif文で比較します。

7行目|「データ」シートのE列の値(取引金額)の「,」を除去します。

8行目|取引金額を累積して加算します

        for cell in cell_list:
            if cell.row==i+1 and cell.col == j+1:
                cell.value = counter

1行目|cell_listに格納されている要素をcellとして一つずつ処理します。(ここで使用するcell_listはシート「集計」のB7~F26です)

2行目|cellの行数がi+1と一致し、かつcellの列数がj+1と一致しているときをif文で探す。

3行目|2行目の条件に合う場合にcellの値をcounterにする。

ステップ5|Googleスプレッドシートに表データを出力する

#ステップ5|Googleスプレッドシートに表データを出力する
ws2.update_cells(cell_list) 

1行目|ステップ4で集計したデータが格納されたcell_listをシート「集計」のB7~F26に出力する

PythonでGoogleスプレッドシートのデータを処理してみよう

GoogleスプレッドシートのデータをPythonで集計や分析する方法を紹介しました。

ぜひチャレンジしてみてください。