In [ ]:
%%bq query -n requests
SELECT datetime, cpu_temperature, temperature
FROM `soracom_handson.raspi_env`
order by datetime asc
In [ ]:
import google.datalab.bigquery as bq
import pandas as pd
df_from_bq = requests.execute(output_options=bq.QueryOutput.dataframe()).result()
In [ ]:
# データの確認
df_from_bq
In [ ]:
# 文字列型でデータが取得されているので変換
df_from_bq['datetime'] = pd.to_datetime(df_from_bq['datetime'])
df_from_bq['cpu_temperature'] = df_from_bq['cpu_temperature'].astype('float')
df_from_bq['temperature'] = df_from_bq['temperature'].astype('float')
# 時系列データ化する
df = df_from_bq[['cpu_temperature', 'temperature']]
df.index = df_from_bq['datetime'].values
In [ ]:
# データの確認
df
In [ ]:
df.describe()
In [ ]:
df.plot(y=['cpu_temperature', 'temperature'], figsize=(16,4), alpha=0.5)
In [ ]:
df.plot(kind='scatter', x='cpu_temperature', y='temperature', c='cpu_temperature', cmap='winter')
In [ ]:
# 散布図から定性的に確認できた相関をさらに定量的に確認する
import numpy as np
np.corrcoef(df['cpu_temperature'], df['temperature'])
In [ ]:
df.tail(1)['cpu_temperature'] > df.tail(1)['temperature']
In [ ]:
N = 10
threashold = 8
df_N = df.tail(N)
sum(df_N['cpu_temperature'] > df_N['temperature']) > threashold
In [ ]:
# 10項移動平均
df.rolling(window=20, center=False).mean()
# 10項移動平均の最新のものを比較材料に使う
moving_average = df.rolling(window=10, center=False).mean().tail(1)
moving_average['cpu_temperature'] > moving_average['temperature']