スプレッドシートをPandasのDataFrameとして扱いたいときありますよね？ありますね。
というわけで、Google Colaboratory環境でスプレッドシートをDataFrameに変換する方法をまとめました。
目次
ダミーデータ
gspread-dataframeライブラリを使う
辞書からDFに変換する
リストからDFに変換する
まとめ
ダミーデータ
ダミーデータを用意しました。１行目がヘッダー、２行目以降にレコードが続きます。
- 赤マーカー 空欄 (=欠損値)
- 列E:F データが入っていないが、スプレッドシートにカラムがある
- 行12:15 データが入っていないが、スプレッドシートに行がある
実際にスプレッドシートで扱われているデータはこんな感じじゃないでしょうか。DataFrameに変換するとして、方法ごとに差異を見ていきましょう。
gspread-dataframeライブラリを使う
from google.colab import auth
from google.auth import default
import gspread
from gspread_dataframe import get_as_dataframe
#Colab認証
auth.authenticate_user()
creds, _ = default()
gc = gspread.authorize(creds)
key = 'XXXXXXXXXXXXXXXXXXXXXXX';
workbook = gc.open_by_key(key)
worksheet = workbook.worksheet("DUMMY_personal_infomation")
df = get_as_dataframe(worksheet, usecols=[0,1,2,3], header=0, index_col=0).dropna(how='all')
df
- 空欄は
NaNとして扱われる -
usecolsで列指定をしないとF列まで全て読む - 行方向も全て読むので
dropna(how='all')で空行は削除する
辞書からDFに変換する
worksheet.get_all_records()で辞書型のデータを取得できるので、そこからDataframeに変換できます。
import gspread
import pandas as pd
key = 'XXXXXXXXXXXXXXXXXXXXXXX';
workbook = gc.open_by_key(key)
worksheet = workbook.worksheet("DUMMY_personal_infomation")
recordsDict = worksheet.get_all_records() #dictで取得する
df = pd.DataFrame(recordsDict).set_index('ID')
print(recordsDict)
#[{'ID': 1, 'name': '谷本志歩', 'sex': '女', 'date': '2000/12/13'}, {'ID': 2, 'name': '福地孝一', 'sex': '男', 'date': '1973/08/20'},
df
-
get_all_records()がデータのある行列しか取得しないため、データフレームへの変換時にテーブルのサイズを気にしなくても良い - 空欄のセルは、空として扱われる。
NaNにはならない
NaNとして扱いたい場合は、numpyをインポートしてnp.nanに置き換えましょう。
import numpy as np
df.replace('', np.nan)
リストからDFに変換する
get_all_values()は二次元配列でデータ取得します。
import gspread
import pandas as pd
key = 'XXXXXXXXXXXXXXXXXXXXXXX';
workbook = gc.open_by_key(key)
worksheet = workbook.worksheet("DUMMY_personal_infomation")
values = worksheet.get_all_values()
print(values[:1])
#[['ID', 'name', 'sex', 'date']]
print(values[1:])
#[['1', '谷本志歩', '女', '2000/12/13'], ['2', '福地孝一', '男', '1973/08/20'],
df = pd.DataFrame(values[1:], columns=values[:1]).set_index('ID')
df
- 二次元配列からそのままDataFrameを作成するとヘッダーが設定されない
- そのため、スライスでヘッダーとレコードに分割してからDataFrameに変換する
まとめ
- 基本的には、gspread-dataframeライブラリで、DFにするカラムを明示的に指定していった方が後々良さそうです。
- 一方で、辞書で取得すると表のサイズやヘッダーの扱いなど気にせず、パッっとDFに変換できるので、お手軽にデータをPandasに持ってきて、あれこれやりたい時には便利です。