【SQL】BigQueryでモンテカルロ法を用いて円周率の近似計算をする
BigQueryで2つの乱数を生成して円周率の近似値を求める方法です。 実用性は無いと思いますが、練習問題にはなるかもしれないですね。
WITH RandomValue AS ( SELECT num, RAND() AS rand1, RAND() AS rand2, FROM UNNEST(GENERATE_ARRAY(1, 1000000)) AS num) SELECT (pi / num) * 4 AS pi FROM( SELECT SUM(IF(POW(POW(rand1, 2) + POW(rand2, 2), 0.5) < 1, 1, 0))AS pi, COUNT(1) AS num FROM RandomValue)
【SQL】BigQueryで素数の列を生成してみる
暇だったのでBigQueryで1000以下の素数の列を作りました。
素数の列がほしい時に使ってください。
NOT EXISTを使うのがポイントです！
WITH Numbers AS ( SELECT num FROM UNNEST(GENERATE_ARRAY(1, 1000)) AS num ORDER BY num) SELECT N1.num AS primeNumber FROM Numbers AS N1 WHERE N1.num > 1 AND NOT EXISTS( SELECT * FROM Numbers AS N2 WHERE N1.num >= N2.num * N2.num AND N2.num > 1 AND MOD(N1.num, N2.num) = 0);
【R言語】dplyrを使えばExcelの集計作業を代替できてしかも速い！
今回はR言語のdplyrの紹介です。
表計算をするなら使いたいパッケージ、それがdplyrです。
などなど・・・dplyrを身につければExcel要らず！になれるはずです。
では早速やってみましょう。
サンプルデータに台湾行政区人口順位表を使います。
ダウンロード
（クリックするとCSVファイルがダウンロードされます。）
CSVファイルの読み込み
まずはダウンロードしたファイルを読み込みます。
Rstudioで以下を実行してください。
taiwan_cities <- read.csv("taiwan_cities.csv", header=TRUE, fileEncoding="UTF-8-BOM")
このファイル、 fileEncoding="UTF-8 とすると読み込めなかったのですが、 fileEncoding="UTF-8-BOM こうすると読み込めました。なんでかはよくわからないです・・・
▼Rstudioでの読み込み結果がこちら
次に、面積、人口、人口密度のクラスを変更します。試しに以下のように入力すると、
class(taiwan_cities$人口) # [1] "factor"
と factor が返ってきます。数値に変換しないといけませんね。
CSV取り込みでよくやる前処理なのですが、ここではカンマを取ってから数値に直す、という処理をしないといけません。面倒ですがやってみましょう。
taiwan_cities$人口 <- gsub(",", "", taiwan_cities$人口)
こうすると、カンマ（","）をスペース（""）で置換できます。面積と、人口密度にも同じ処理をしましょう。
さらに、as.numeric()でクラスを数値に変換します。小数点がある場合は、round()で整数にしてしまいましょう。
taiwan_cities$面積 <- round(as.numeric(gsub(",", "", taiwan_cities$面積)), digits = 0)
dplyrで集計をする
分類の列を見ると、直轄市、県、市という値が入っていることがわかります。
この分類ごとに、面積と人口を合計し、さらに人口密度を算出してみましょう。
library(dplyr) taiwan_cities_bunrui <- taiwan_cities %>% dplyr::group_by(分類) %>% summarise(各分類の数 = n(), 面積の合計 = sum(面積), 人口の合計 = sum(人口), 人口密度 = round(人口の合計 / 面積の合計, digits = 0))
結果、こうなります。
ここで、直轄市、県、市のそれぞれの数を知りたかったので、各分類の数 = n()として、個数を数えています。
さらにこれを人口で降順に並べ替えたいなら、arrange()とdesc()を用いて、
taiwan_cities_bunrui <- taiwan_cities_bunrui %>% dplyr::arrange(desc(人口の合計))
とすれば、降順で表示できます。
LEFT JOINで2つのテーブルをくっつける（VLOOKUP的な処理）
次に、各行政区に自分が行ったことがある／ないのテーブルを読み込みましょう。
まずは、CSVをダウンロード。
ダウンロード
（クリックするとCSVファイルがダウンロードされます。）
read.csv()で読み込みましょう。
taiwan_exp <- read.csv("taiwan_exp.csv", header=TRUE, fileEncoding="UTF-8-BOM")
こんなファイルです。
ここには行ったことがある行政区しか載せていないため、最初のファイル（taiwan_cities）とは行の数が合いません。
さて、最初のテーブル（taiwan_cities）と今読み込んだテーブル（taiwan_exp）をLEFT JOINでくっつけてみましょう！
ExcelのVLOOKUPと同じようなことができます。
taiwan_cities_exp <- dplyr::left_join(taiwan_cities, taiwan_exp, by = "行政区")
こんなふうになります！元のテーブルの右側に、列が加わりました。
left_joinの説明をすると、
dplyr::left_join(左側に置きたいテーブル, 右側に置きたいテーブル, by = "キー")
となります。
左側のテーブルを基準としているため、左側のテーブルはすべての行が残りますが、右側のテーブルは必ずしもすべてが残るわけではありません。（今回はたまたま残っていますが。）
また、左側にあって右側にない場合は、NAとなります。（NULLとなります。）
以上の要領でdplyrを駆使していけばマジでExcelいらずになります！
処理も速いし最高ですね。もうExcelには戻れませんよ・・・
【Python】フォルダ内のCSVファイルを一括で読み込み縦につなげる方法
Pythonでディレクトリ内のCSVファイルを一気に読み込んで、かつそれらを連結させる方法です。
CSVでログが吐かれている場合など、この方法を使えば一発ですね。
Excelで消耗している方は是非トライしてみてください。流れはこんな感じです。
# import module import pandas as pd import glob import os # ディレクトリ変更 os.chdir('/Users/username/directory') # 空のDataFrameを定義 df = pd.DataFrame() # .csvを含むファイルをpd.read_csv()で読み込む for i in glob.glob("*.csv*"): tmp_df = pd.read_csv(i) # DataFrameを連結する df = pd.concat([df, tmp_df])
繰り返し文（for文）を使えば指定した文字列を含むファイルをすべて読み込めます。
ただし、列名が揃っていないとうまく結合できないです。
その場合は、以下のようにして列名を変更してください。
# 列名の変更 df = df.rename(columns = {'col1': 'renamed_col1', 'col2': 'renamed_col2'})
【SQL】BigQueryで日付の列を生成する方法
指定した日付から本日までの日付の列を生成する方法です。
日付が歯抜けになっているテーブルとJOINしたいときや、日付のマスタを作る際に使えそうです。
▼Query
-- 指定した日付から本日までの日付の列を生成する WITH DateRange AS( SELECT jpDate FROM UNNEST(GENERATE_DATE_ARRAY(DATE('2015-04-01'), CURRENT_DATE('Asia/Tokyo'))) AS jpDate ORDER BY jpDate) SELECT * FROM DateRange
▼このようなアウトプットが得られます。
【Python】kaggleで使えるDataFrameのメモリを減らす関数
pandasで重たいデータを扱う際にマストな関数です！
初参加したkaggleのコンペ「ASHRAE - Great Energy Predictor III」の公開カーネルで使用されていたので、自分もkaggleや仕事で積極的に使っています。
特に、ASHRAEは扱うデータが大きかったので、重宝しましたね～
ガンガン使ってメモリ削減していきましょう！
# Function to reduce the DF size import pandas as pd import numpy as np def reduce_mem_usage(df, verbose=True): numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64'] start_mem = df.memory_usage().sum() / 1024**2 for col in df.columns: col_type = df[col].dtypes if col_type in numerics: c_min = df[col].min() c_max = df[col].max() if str(col_type)[:3] == 'int': if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max: df[col] = df[col].astype(np.int8) elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max: df[col] = df[col].astype(np.int16) elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max: df[col] = df[col].astype(np.int32) elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max: df[col] = df[col].astype(np.int64) else: if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max: df[col] = df[col].astype(np.float16) elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max: df[col] = df[col].astype(np.float32) else: df[col] = df[col].astype(np.float64) end_mem = df.memory_usage().sum() / 1024**2 if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem)) return df
CSVを読み込む際に使うのが良いと思います。
# CSVを読み込む際に使うと良い df = reduce_mem_usage(pd.read_csv('train.csv'))
出典：
https://www.kaggle.com/gemartin/load-data-reduce-memory-usage
【Python】SpotifyのAPIを使ってaikoの人気曲とアルバムを取得する
SpotifyのAPIを使うと、色々と楽しそうなことができるみたいなので試してみます。
この記事では、以下の2つを解説します。
- アーティストの人気曲の取得
- アーティストのアルバムの取得
とりあえず、自分の好きなaikoとPUFFYとMayday（台湾のアーティスト）の楽曲をSpotify APIで探してみたいと思います。
Spotify APIを使うための準備
まず、Spotifyのアカウントを持っていない場合は以下のリンクからアカウントを作りましょう。
https://www.spotify.com/jp/
次に、client_idとclient_secretが必要になるので、以下のリンクから取得してください。
https://developer.spotify.com/dashboard/
Pythonの開発環境がある方は、以下のコマンドでSpotifyのAPIを使えるようにします。
pip install spotipy
spotifyではなくspotipyなんですね〜
簡単ですが、準備はこれでOKです。
リファレンスは次のリンクから。わからないことがあれば適宜参照してください。
https://spotipy.readthedocs.io/en/2.9.0/
Spotify APIでアーティストのTop Tracksを取得する
ここからが本題！好きなアーティストのTop Tracks（人気の曲）を取得してみます。
まず、先程取得したclient_idとclient_secretを入力して、認証をします。
# import modules import pandas as pd import spotipy from spotipy.oauth2 import SpotifyClientCredentials client_id = 'XXXXXXXXXXXXXXXX' client_secret = 'XXXXXXXXXXXXXXXX' client_credentials_manager = spotipy.oauth2.SpotifyClientCredentials(client_id, client_secret) spotify = spotipy.Spotify(client_credentials_manager=client_credentials_manager)
次にアーティストのIDを取得します。
APIを使ってアーティストを検索することもできるのですが、ここではSpotifyのWebページから取得します。
Spotifyのページからアーティストを検索し、アーティストページのURLの末尾の文字列をコピーします。
このページのURLですね。
https://open.spotify.com/artist/6TDMbiQCWeMClsMr9ORLRK
# アーティストのID artist = 'spotify:artist:6TDMbiQCWeMClsMr9ORLRK' # aiko
このIDを使って、色々やってみます。
まずはTop Tracksを取得してみましょう。
spotify.artist_top_tracks(artist)
とすると、指定したアーティストの人気の10曲を取得できるようです。
曲の情報はかなり詳細に記されているのですが、そのうちの一部を取り出しましょう。
これらを取得してDataFrameにします。
# Top Tracksを取得して、曲名、リリース日、人気度（popularity）などをDataFrameとして表示する results = spotify.artist_top_tracks(artist) name = [] release_date = [] duration_ms = [] popularity = [] preview_url = [] images_url = [] for track in results['tracks']: name.append(track['name']) release_date.append(track['album']['release_date']) duration_ms.append(track['duration_ms']) popularity.append(track['popularity']) preview_url.append(track['preview_url']) images_url.append(track['album']['images'][0]['url']) df_tracks = pd.DataFrame() df_tracks['name'] = name df_tracks['release_date'] = release_date df_tracks['duration_ms'] = duration_ms df_tracks['popularity'] = popularity df_tracks['preview_url'] = preview_url df_tracks['images_url'] = images_url
すると、以下のようにDataFrameを作ることができます。
カブトムシ、キラキラ、花火などaikoの代表的な曲が並んでいるのがわかります。
popularityが一番大きいのは青空ですね。
リリース日が年だったり年月日だったりすのが気になりますが・・・まあいいでしょう。
アーティストのアルバム情報を取得する
次に、アルバム情報を取得しましょう！
アルバムに関しても、かなり詳細な情報が取得できるようですが、ここでは以下を取りだしてみます。
ここで、アルバムを利用可能な地域"available_markets"を['JP']に指定しておきます。
こうしておくことで、日本語名のアルバムのみを取り出せます。
※外国のアーティストなら指定しなくてもいいかも。詳しくは実際に触ってみてください。
# アルバム情報を取得して、アルバム名、リリース日、URLなどをDataFrameとして表示する results = spotify.artist_albums(artist) name = [] release_date = [] total_tracks = [] external_urls = [] for item in results['items']: # 海外の楽曲なら不要 if item['available_markets'] == ['JP']: name.append(item['name']) release_date.append(item['release_date']) total_tracks.append(item['total_tracks']) external_urls.append(item['external_urls']['spotify']) df_aibums = pd.DataFrame() df_aibums['name'] = name df_aibums['release_date'] = release_date df_aibums['total_tracks'] = total_tracks df_aibums['external_urls'] = external_urls
こちらを実行すると、画像のようなDataFrameになると思います。
全部で10曲のアルバムがありますね〜
【Python】pandas.date_rangeで連続した日付の列を取得する
任意の期間の日付の列を生成したいときに使います。 1月1日から丸一年分の日付が欲しいときや、ある1週間の日付が欲しいときに重宝します。
# 連続した日付の列を取得 pd.date_range('2020-01-01', periods=7, freq='D')
periodsで期間の長さを指定、freqでどの単位（頻度）で取得するのかを指定します。 "D"なら日毎、"H"なら1時間ごと、"Q"なら四半期ごと、というように指定できます。
【Python】リスト内包表記でDataFrameの列名を一括変更する
DataFrameの列名を一括で変更する方法です。
df.rename(columns = {'col_name1': 'col_name2'})
とするよりも簡単で便利かもしれません。
例えば、以下の画像のような、dayと数字の間にあるアンダースコア"_"を取り除きたい場合など。
リスト内包表記を使えば一括で変更できます。
# 列名を変更 df.columns = [col.replace('_', '') for col in df.columns]
【R言語】decomposeで時系列データの要素を分解してみる
Rで時系列データを手軽に分析したいなってときにdecomposeをよく使うのですが、 その方法を簡単に説明します。
decompose関数は、時系列データを
・tread ・seasonal ・random
の3つに分解してくれます。 トレンドと季節性を分解してくれるので、例えば季節の影響なく売上が伸びているのか（減っているのか）を調べる時に使えそうだなと。
ただ、このdecomposeが中でどういう処理をしているのかはよく知らないので、 ちゃんと勉強しないとですね・・・
データはAirPassengersを用いました。
ダウンロードはこちら： https://www.kaggle.com/rakannimer/air-passengers/data
data <- read.csv('airpassengers.csv') ts.d <- ts(data$X.Passengers, frequency = 12) decomposed.ts.d <- decompose(ts.d) plot(decomposed.ts.d)
1年単位で周期しているだろうと思いfrequency = 12と指定しました（月単位でデータが格納されているので）。
たった4行で書けるので、手軽！
▼plotした結果
けれど、Random項がランダムじゃないっぽい波形をしているので、このモデルでは不十分そうなことがわかります。
もっと詳しくやるなら、やはりこの本からですかね。 ARモデルが詳しく解説されています。
