Pandas を使って、Google スプレッドシートのデータをsqlite3 にimportする


Google Sheet > pandas > sqlite3 の流れでテーブルを作成、データをインポートできるか試してみました。
結果を以下に記載します。


参考


前提

OS 、Python の version は以下の通りです。

  • OS

    % sw_vers
    ProductName:    Mac OS X
    ProductVersion: 10.13.2
    BuildVersion:   17C88
    

  • Python

    % python3 -V
    Python 3.6.2
    


必要なライブラリのインストール

df2gspread をインストールします。

python3 -m pip install df2gspread --user

手順

以下手順を踏んで、データをsqliteにインポートします。

  • df2gspread で Google スプレッドシート からデータをエクスポート。データを DataFrame として取り込む。

  • pandas で sqlite の テーブルを作成する。

df2gspread で Google スプレッドシート からデータをエクスポート。データを DataFrame として取り込む。

df2gspread で スプレッドシートのデータを DataFrame として取り込みます。
スプレッドシートのデータは予め、Google Analytics - Google スプレッドシート アドオン でデータを取得しています。
start_cell の値は、A15 は、Google Analytics - Google スプレッドシート アドオン を使用したデータの取り込みであれば、基本的にA15 になると思います。
スプレッドシートには、キーのアカウントのアクセス権限が必要になりますので、403エラーになるようであれば、権限を付与してください。

from oauth2client.service_account import ServiceAccountCredentials
def download_as_df(sheet_id, sheet_name):
    from df2gspread import gspread2df as g2d
    # key_file 以下の指定方法だと、notebook と同じディレクトリにあるキーファイルを取得しています。
    key_file = "spreadsheet_api_key.json"
    scope = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(key_file, scope)    
    # 1brCpWvk2uofc3MEt-ASb2cuZ-u8Zmx-ICxSTltlbVBQ はスプレッドシートのID なのでそれぞれ取得対象のスプレッドシートで変わります。
    df = g2d.download(sheet_id, wks_name=sheet_name, col_names=True, row_names=False, credentials=credentials, start_cell = 'A15')
    df = df.sort_values(by='ga:date') 
    return df
import pandas as pd
df = download_as_df("1brCpWvk2uofc3MEt-ASb2cuZ-u8Zmx-ICxSTltlbVBQ", "ユーザーの行動レポート 201710")
# データフレームの内容を確認する
df
ga:dimension8ga:pagePathga:datega:pageviews
154106339101.2/blog/cent-os-69-に-memcached-をインストールログの設定まで実施する/2017-10-011
3316257535979.2/blog/python-requests-post-リクエスト送信時にheader-を設定する/2017-10-011
875132184887.2/blog/google-search-console-の-キーワードを-python-sk...2017-10-012
17271633723053/blog/google-search-console-の-キーワードを-python-sk...2017-10-011
2066175791501.2/blog/pep8wraning-do-not-assign-a-lambda-expre...2017-10-011
22331818150150/blog/python-requests-post-リクエスト送信時にheader-を設定する/2017-10-012
4348639013335.2/blog/youtube-data-api-v3-java-paging-search/2017-10-011
4767785076192.2/blog/dropwizard-adminとapplicationのcontextroot...2017-10-011
1078139048351.2/blog/google-apps-script-でスプレッドシートの列の値を取得する/2017-10-011
5266993520188.2/blog/migrations-to-mezzanine-4.2.3-from-4.2.2/2017-10-011
4550703959728.2/2017-10-011
12341455379.151/blog/java-url文字列からクエリストリングを取得/2017-10-013
3594365205259.2/blog/Verifying-the-vulnerability-of-blogs-bui...2017-10-011
3336266615173.2/blog/django-18からsettingspyのtemplatesが非推奨になって警...2017-10-011
2768199775680.2/blog/pip-install-mock-on-el-capitan/2017-10-012
...............
11981439517877/blog/google-spread-sheet-の-複数のシートのデータをスクリプトで統...2017-10-311

5305 rows × 4 columns

pandas で sqlite の テーブルを作成する。

export した データを元に、sqlite のテーブルを作成します。

import sqlite3
#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
# tableを作成する 
df.to_sql("userreports_201710", conn, if_exists="replace")
# コネクションを閉じる
conn.close()

登録されたデータを確認

登録されたデータを確認します。

#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
c = conn.cursor()
sql = 'select * from userreports_201710'
for row in c.execute(sql):
    print(row)
conn.close()
(154, '106339101.2', '/blog/cent-os-69-に-memcached-をインストールログの設定まで実施する/', '2017-10-01', '1')
(3316, '257535979.2', '/blog/python-requests-post-リクエスト送信時にheader-を設定する/', '2017-10-01', '1')
(875, '132184887.2', '/blog/google-search-console-の-キーワードを-python-sklearn-randomforestclassifier-でクラス分類してカテゴリ分けする/', '2017-10-01', '2')
(1727, '1633723053', '/blog/google-search-console-の-キーワードを-python-sklearn-の-ナイーブベイズ分類-で-カテゴリ分けする/', '2017-10-01', '1')
(2066, '175791501.2', '/blog/pep8wraning-do-not-assign-a-lambda-expression-use-a-def/', '2017-10-01', '1')
(2233, '1818150150', '/blog/python-requests-post-リクエスト送信時にheader-を設定する/', '2017-10-01', '2')
(4348, '639013335.2', '/blog/youtube-data-api-v3-java-paging-search/', '2017-10-01', '1')
(4767, '785076192.2', '/blog/dropwizard-adminとapplicationのcontextrootを切り替える/', '2017-10-01', '1')
(1078, '139048351.2', '/blog/google-apps-script-でスプレッドシートの列の値を取得する/', '2017-10-01', '1')
(5266, '993520188.2', '/blog/migrations-to-mezzanine-4.2.3-from-4.2.2/', '2017-10-01', '1')
(4550, '703959728.2', '/', '2017-10-01', '1')

index を テーブルに登録しないようにする

index を sqlite に登録しないようにします。

import sqlite3
#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
# tableを作成する
df.to_sql("userreports_201710",  conn, index=False, if_exists="replace")
# コネクションを閉じる
conn.close()
#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
c = conn.cursor()
sql = 'PRAGMA TABLE_INFO(userreports_201710);'
for row in conn.execute(sql).fetchall():
    print(row)
conn.close()
(0, 'ga:dimension8', 'TEXT', 0, None, 0)
(1, 'ga:pagePath', 'TEXT', 0, None, 0)
(2, 'ga:date', 'TEXT', 0, None, 0)
(3, 'ga:pageviews', 'TEXT', 0, None, 0)

column 名を変更して登録する

: がSQL の予約語の扱いになり、" でカラム名をエスケープする必要があります。
SQL を書く際に、面倒なので、カラム名の:を置換します。

# column に含まれる: を _ に置換する
df.columns = [ column.replace(":","_") for column in df.columns]
import sqlite3
#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
# tableを作成時に index=False をパラメータで渡す
df.to_sql("userreports_201710",  conn, index=False, if_exists="replace")
# コネクションを閉じる
conn.close()
#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
c = conn.cursor()
sql = 'PRAGMA TABLE_INFO(userreports_201710);'
for row in conn.execute(sql).fetchall():
    print(row)
conn.close()
(0, 'ga_dimension8', 'TEXT', 0, None, 0)
(1, 'ga_pagePath', 'TEXT', 0, None, 0)
(2, 'ga_date', 'TEXT', 0, None, 0)
(3, 'ga_pageviews', 'TEXT', 0, None, 0)

どのような用途で使うか

superset は 現状、Google スプレッドシートのコネクタ、Google Aanalytics のコネクタがありません。
個人的に、Google Analytics のデータを Goolge スプレッドシート経由で取得し、sqlite に インポート後に、superset で可視化できたらいいかもと思います。
以上です。

コメント