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
.dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; }
ga:dimension8 ga:pagePath ga:date ga:pageviews
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-sk... 2017-10-01 2
1727 1633723053 /blog/google-search-console-の-キーワードを-python-sk... 2017-10-01 1
2066 175791501.2 /blog/pep8wraning-do-not-assign-a-lambda-expre... 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
1234 1455379.151 /blog/java-url文字列からクエリストリングを取得/ 2017-10-01 3
3594 365205259.2 /blog/Verifying-the-vulnerability-of-blogs-bui... 2017-10-01 1
3336 266615173.2 /blog/django-18からsettingspyのtemplatesが非推奨になって警... 2017-10-01 1
2768 199775680.2 /blog/pip-install-mock-on-el-capitan/ 2017-10-01 2
... ... ... ... ...
1198 1439517877 /blog/google-spread-sheet-の-複数のシートのデータをスクリプトで統... 2017-10-31 1

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 で可視化できたらいいかもと思います。
以上です。

コメント

カテゴリー

最近の投稿