Google スプレッドシートの ImportHtml 関数で HTML の table 要素を取得。気象庁の気圧データを抽出してみる。

1. Google スプレッドシートでウェブサイトからデータを取得する

気象庁のウェブサイトには、過去の気象データが表形式で公開されている。

Google スプレッドシートの importhtml 関数 を利用すると、このデータを簡単に表計算に取り込むことができる。

例えば、「気象庁の日ごとのデータ」から、

• 「現地の気圧」
• 「平均気温」

の列を抽出したいとする。

 

2. IMPORTHTML 関数の使い方

a. 対象の table 要素が何番目にあるか調べる

importhtml 関数では、HTML の table 要素を抽出できる。その際、対象の table 要素が何番目にあるか指定する必要がある。そのためには、

1. データが表示されているページのソースを表示させる。（ブラウザとして Firefox を使っているなら、ページの適当なところで右クリック > 「ページのソースを表示」を選択。 ）
2. 抽出したデータを含む対象の table 要素が、ページの先頭から数えて何番目にあるか確認する。(`table’ で文字列の検索をするとよい。)
3. Google スプレッドシート において、A1 セルを選択しておき、数式のアイコンをクリック > 「その他の数式」を選択。

 

b. ImportHtml 関数の引数

数式を挿入するためのダイアログが表示されたら、

• Google > ImportHtml

を選択。その結果、A1 セルに次のように表示される。

=ImportHtml(URL, クエリ, 指数)

ここで指定する値は、

• URL : 対象のテーブルがある URL
• クエリ : “table”
• 指数 : そのページ内の何番目のテーブルを対象とするか？

(cf. Functions : Functions for external data - Google Docs Help Center)

変更（2014/03/02）： 例題のデータがあるページのソースを調べたら、table 要素がいくつかあった。目的の table は 4 番目にあることが分ったので、セル A1 に以下のように入力する。

=ImportHtml("http://www.data.jma.go.jp/obd/stats/etrn/view/daily_s1.php?prec_no=44&prec_ch=%93%8C%8B%9E%93s&block_no=47662&block_ch=%93%8C%8B%9E&year=2008&month=11&day=&elm=daily&view=", "table", 4)

少し待つと、以下のような表が Google スプレッドシートに作成される。

残念ながら、対象のページの文字エンコーディングが Shift_JIS だったので文字化けしてしまった。

 

3. 必要なデータの抽出

次に、シートを新しく追加し、上記の表のうち A 列と F 列のみを参照する。

上記の表があるシートの名前が `シート1’ であるとすると、新しく追加したシートの A1, B1 にはそれぞれ次のように入力。

• A1 : ='シート1'!B5
• B1 : ='シート1'!G5

必要なだけ連続データを作成して完了。

 

4. 柔軟にデータを扱いたいなら、プログラミング言語を利用したほうが良い

このようなデータは、プログラミング言語を利用して必要な情報を抽出する方が良い。

• cf. Python で簡単なテキスト処理 (2) - データの抽出

しかし、Google スプレードシートを利用するとお手軽に抽出処理を行える。

Python, Ruby で簡単なテキスト処理 (2) - 表からのデータの抽出

「Python で簡単なテキスト処理 (1) - 特定の単語が含む行を抽出する」の続き。

1. 表から、特定の列を抽出したい

今回は、実際に存在するデータから、特定の列を抽出をしてみる。

抽出対象のデータは、気象庁で公表されている気象データ。

気象庁 | 日ごとの値 via kwout

気象庁の過去のデータを見ると、日毎、時間毎に、いくつかの観測対象のデータが載せられている。

上記のデータは、

「 2008 年 1 月の東京の日ごと」

の気象データが掲載されているページ。この中から、特定の観測対象のデータのみを抽出したい。ただし、抽出するとき、抽出データを後で表計算に貼り付けるため、「タブ区切り」にすること。

対象の項目は、「現地の気圧」と「平均気温」のとする。

 

2. データをブラウザからエディタに貼りつける

例えば、東京の2008/1/1 ~ 1/5 までのデータを、ブラウザ上で選択し、エディタに貼り付けると、次のようになる。

1
 998.7 1003.1 -- -- -- 6.0 10.9 1.8 36 16 2.6 4.8 北西 9.4 北西 6.9 -- -- 晴後一時曇 快晴
2
 1007.2 1011.7 -- -- -- 6.2 11.2 1.1 35 19 3.1 6.6 北西 13.3 北西 9.1 -- -- 快晴 快晴
3
 1011.6 1016.1 -- -- -- 5.9 10.4 1.5 45 29 1.8 3.6 北北西 6.4 南南東 9.1 -- -- 晴 快晴
4
 1014.6 1019.1 -- -- -- 7.0 12.1 2.7 43 24 2.5 5.0 北西 9.4 北北西 9.0 -- -- 快晴 晴後曇
5
 1014.0 1018.6 0.0 0.0 0.0 6.0 8.7 4.0 53 39 2.0 3.8 北西 6.1 西北西 3.7 -- 

これを文字列として変数に代入し、処理をする。

 

3. Python の場合

data = ur"""ここに上記のデータを貼り付ける"""

import re

# 改行でデータを分割
for line in data.split('\n'):
    # 日付の行は飛ばす
    if re.search('^\d{1,2}$', line): continue
    # 空白でレコードを分割
    rec = line.split()
    # 必要な観測データのみ、タブ区切りでレコードを出力
    print rec[0], '\t', rec[5]

以下を参考。

• 2.3.6.1 文字列メソッド
• re -- 正規表現操作

 

リスト内包表記を使う場合

追記(2008.6.10): リスト内包表記を利用し、後述する Ruby のコードを真似てみる。

data = ur"""ここにデータを貼り付ける"""

for line in [line for line in
        data.split('\n') if not re.search('^\d{1,2}$', line)]:
    for i,v in enumerate(line.split()):
        if   i == 0: print v, "\t",
        elif i == 5: print v

うーん、読みにくい... ^^;

enumerate() をリスト内包表記の中に入れてやれば、

data = ur"""ここにデータを貼り付ける"""

for a,b in [filter(lambda (i,v): i in [0, 5] , enumerate(line.split()))
            for line in
                data.split('\n') if not re.search('^\d{1,2}$', line)]:
    print a[1], "\t", b[1]

filter() をリスト内包表記に変更すると、

data = ur"""ここにデータを貼り付ける"""

for rec in [[v for i,v in enumerate(line.split()) if i in [0,5]]
            for line in
                data.split('\n') if not re.search('^\d{1,2}$', line)]:
    print rec[0], "\t", rec[1]

enumerate() した後にフィルタしたものを [] で囲むのを忘れずに。

ついでに、reduce() を使うなら、(参考: jijixi's diary - リストの扱いあれこれ)

data = ur"""ここにデータを貼り付ける"""

for rec in [reduce(lambda r,(i,v): r + [v]  if i in [0,5] else r,
                    enumerate(line.split()),
                    [])
            for line in
                data.split('\n') if not re.search('^\d{1,2}$', line)]:
    print rec[0], "\t", rec[1]

reduce() 内の lambda における v を [] で囲むのを忘れずに。

しかし、慣れていないので読みにくい... ^^;

 

4. Ruby の場合

Ruby の場合、正規表現の書き方がシンプルでいい ^^

DATA.each do |line|
  next if line =~ /^\d{1,2}$/
  rec = line.split
  print rec[0], "\t", rec[5],"\n"
end

__END__
ここにデータを貼り付ける

追記(2008.6.10): 最初に不必要なデータを削除しておくやり方。

DATA.reject{|line|
  line =~ /^\d{1,2}$/
}.each{|line|
  line.split.each_with_index{|v,i|
    case i
    when 0
      print v, "\t"
    when 5
      puts  v
    end
  }
}

__END__
ここにデータを貼り付ける

6. 関連記事

• → Haskell で簡単なテキスト処理 - データの抽出
• Firefox でウェブサイトの表の内容をコピーして表計算ソフトに貼り付ける
• Google スプレッドシートで HTML の table 要素を抽出 – ImportHtml 関数
• 気象庁の過去のデータを開くためのブックマークレット