BigQueryのオープンデータを用いたデータ分析の学習記録です。書き進めるうちに内容が膨らみ、Qiita初投稿にして全4本になりました。
0. 学習の前に
簡単に私のスペックを紹介しておきます。
- アラサー
- SE歴合計4年弱（複数社でSEとして勤務歴あり、直近の仕事は非IT）
- SE歴＝バックエンドエンジニア（主な開発言語：Java、VBA、C#）
- BigQuery：実務経験なし
- SQL：実務経験あり（MySQL、SQLServer、PostgreSQL）
- Python：実務経験なし（独学中）
0-1. 全体のゴール
- BigQueryのオープンデータから、自分が欲しいデータを抽出する
- 抽出したデータを分析する
0-2. 技術
- BigQuery
- Colaboratory
- Google Cloud Platform
- Google Gemini
- Python3
- SQL
0-3. この記事で扱う範囲
● 1. データセットの選択
● 2. 集計データの内容
● 3. 結果の事前予想
● 4. データ抽出
＊ 4-1. アメリカで人気の名前年間トップ10（表）
＊ 4-2. アメリカで人気の名前年間トップ5の推移（折れ線グラフ）
・4-2-1. Googleアカウント認証
・4-2-2. SQLクエリ定義
・4-2-3. BigQueryからPandasへ読み込み
・4-2-4. データの集計と折れ線グラフ化
＊ 4-3. アメリカ年間出生者数に占めるトップ10の割合の推移（帯グラフ）
・4-3-1. データセットからデータ抽出
・4-3-2. SQLクエリ定義
・4-3-3. BigQueryからPandasへ読み込み
・4-3-4. データの集計と帯グラフ化
● 5. データ分析
＊ 5-1. 登録者数に占めるトップ10の割合の減少
・5-1-1. 女の子
・5-1-2. 男の子
＊ 5-2. ボーダーレスな名前の増加
＊ 5-3. ユニセックス・男女ペアの名前の増加
・5-3-1. ユニセックスな名前
・5-3-2. 男女ペアの名前
＊ 5-4. 1910年と2021年のトップ10比較
・5-4-1. 女の子
・5-4-2. 男の子
＊ 5-5. トップ5入りする名前の変遷
・5-5-1. 女の子
・5-5-2. 男の子
＊ 5-6. その他
・5-6-1. 2022年以降のデータ
1. データセットの選択
何でもよかったのですが、今回はUSA Name Dataを選択。ざっくりデータの中身を説明すると、以下の通りです。
- アメリカ社会保障局が作成
- 以下をすべて満たす人の名前を記載（2015年2月末時点）
- 1879年以降にアメリカで生まれた人
- 社会保障カードを申請した人
- 記録に出生地の記載がある人
- データベース
- state（String）：出生州
- gender（String）：性別（男：M、女：F）
- year（Integer）：出生年（1910～2021）
- name（String）：名前（given name）
- number（Integer）：人数
ちなみにプライバシー保護の観点から、number＜5の名前は登録されていません。
| state | gender | year | name | number |
|---|---|---|---|---|
| AK | F | 1910 | Lucy | 6 |
| WY | M | 2021 | Sebastian | 7 |
たとえば上記データの場合、以下のように読み取れます。
- 1910年、アラスカ州（AK）でLucyという女の子が6人生まれた
- 2021年、ワイオミング州（WY）でSebastianという男の子が7人生まれた
2. 集計データの内容
USA Name Dataから、以下のデータを抽出します。すべて男女別です。
- アメリカで人気の名前年間トップ10（表）
- アメリカで人気の名前年間トップ5の推移（折れ線グラフ）
- アメリカ年間出生者数に占めるトップ10の割合の推移（帯グラフ）
3. 結果の事前予想
集計したデータから見える傾向の分析だけでなく、事前予想との比較もします。
- 移民の増加によって名前が多様化し、各年・性別ごとの登録者数に占めるトップ10の割合は、現代に近づくほど低下する
- 現代に近づくにつれ、国籍に限定しない名前が上位に入りやすくなる（例：ハナコは日本的、アンはボーダーレス）
- 現代に近づくにつれ、ユニセックスな名前（例：Alex、Taylor）や、男性名と女性名で対になる名前（例：Gabriel/Gabrielle、Jonathan/Johanna）が増える
- 1910年のトップ10と2021年のトップ10は、ほぼ完全に入れ替わる（古風な名前が減り、現代的な名前が増える）
- 移民の増加によって名前が多様化し、現代に近づくほどトップ5の入れ替わりが激しくなる
思うがまま書き出しました。2.と3.は「全体として予想通りの傾向があるものの、トップ10圏外のデータからはその傾向を確認できない」が起こり得ます。個人的には追及したいですが、トップ10圏外のデータは深追いしません。また、4.の「ほぼ」は、1、2個程度は残っていると予想しました。
4. データ抽出
今回はGCPのクエリとGoogle Colabを使いました。本来はVertex AI Workbenchも利用予定でしたが、インスタンスを起動すると料金が発生するので取りやめました。
4-1. アメリカで人気の名前年間トップ10（表）
早速データを抽出していきます。まずはGCPのクエリで次のSQLを叩きました。
SELECT
name,
year,
gender,
sumNumber,
rank
FROM (
SELECT
name,
year,
gender,
SUM(number) AS sumNumber, -- 全米の登録者数
ROW_NUMBER() OVER(
PARTITION BY year, gender -- 各「年」と「性別」で順位をリセット
ORDER BY SUM(number) DESC
) AS rank
FROM
`bigquery-public-data.usa_names.usa_1910_current`
GROUP BY
name, year, gender
) AS RankedData
WHERE
rank <= 10
ORDER BY
year DESC,
gender ASC,
rank ASC;
抽出したデータを CSV として出力したところ、以下の内容が得られました。内訳は「トップ10×男女×1910～2021年（112年分）＋ヘッダー」で、合計2,241行です。
name,year,gender,sumNumber,rank
Olivia,2021,F,17728,1
Emma,2021,F,15433,2
Charlotte,2021,F,13285,3
--- 中略 ---
Frank,1910,M,3761,8
Edward,1910,M,3402,9
Henry,1910,M,2884,10
ベースとなるデータを抽出したばかりですが
このまま続けると長編になりすぎるため、当初の予定だった「1記事で全部まとめる」を断念し、区切って進めることにします。次回は折れ線グラフを作ります！