#!/usr/bin/env python
# coding: utf-8

# # Google Cloud Datalabを使ってBigQueryのデータを分析する

# ## 1. 必要なパッケージのimport

# 今回必要なのはBigQueryを操作するためのgcp.bigqueryと、dataframeを使用するためのpandasなので、それらをimportします。

# In[1]:


import gcp.bigquery as bq
import pandas as pd


# ## 2. BigQueryにQueryを投げて結果を表示

# bq.Query('Query内容').results()で簡単に結果を表示することができます。
# 今回はBigQueryでサンプルとして提供されているgithub_timelineというテーブルを使用しています。

# In[2]:


bq.Query('SELECT * FROM [publicdata:samples.github_timeline] LIMIT 3').results()


# ## 3. BigQueryからデータをdataframeに出力する

# %%sql　で行頭を始めると、sqlのクエリを入力することができます。また、 --module モジュール名　と指定することで、クエリを保存することができます。

# 今回はgithub_timelineというテーブルから、レポジトリ作成日、閲覧数、言語を作成日が新しいものから上位百万件をとってきています。

# In[8]:


get_ipython().run_cell_magic('sql', '--module requests', 'SELECT repository_created_at, repository_watchers, repository_language \nFROM [publicdata:samples.github_timeline]\norder by repository_created_at desc\nlimit 1000000\n')


# In[6]:


get_ipython().run_cell_magic('bigquery', 'sample --count 5 --query requests', '')


# df = bq.Query('query内容').to_dataframe()とすることで、dataframeにqueryを投げた結果を出力することができます。

# In[9]:


df = bq.Query(requests).to_dataframe()
len(df)


# In[10]:


df.head(5)


# ## 4. 結果を描画する

# 例えば日ごとのレポジトリ閲覧数合計の推移を図示すると以下のようになります

# In[11]:


df['timestamp'] = pd.to_datetime(df['repository_created_at'])


# In[13]:


data = pd.Series(df['repository_watchers'].values, index=df['timestamp'])


# In[20]:


data.resample('d', how='sum').plot(figsize=(20,10))


# 他にも例えば言語ごとにレポジトリの投稿数を合計して図示することもできます（上位20言語）

# In[12]:


df['repository_language'].value_counts().head(20).plot(kind='bar', figsize=(20,10))


# 言語ごとに閲覧数を合計して図示することも簡単にできます。（上位20言語）

# In[21]:


groups = df.groupby('repository_language')


# In[25]:


groups['repository_watchers'].sum().order(ascending=False).head(20).plot(kind='bar', figsize=(20,10))