学習塾を運営するのに最適なのはどこ？ オープンデータを活用して実践的なスキルを身に付ける：ITエンジニアのためのデータサイエンティスト養成講座（特別編）（2/3 ページ）

[中林紀彦，日本アイ・ビー・エム]

（3）クロス集計されているデータを元に戻す

　図1のようにI列からDE列までの101列に0歳から100歳以上までのデータが入っていますので、列ごとに取り出してマージする作業が必要です。

# 必要なライブラリの読み込み
import pandas as pd
import datetime
# 各配列および定数の定義
wardlist = ['han', 'ina', 'mid', 'mih', 'tyu', 'wak']
yearlist = ['05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25']
month = '03'
# データフレームの定義
dfall = pd.DataFrame(columns=['year', 'month', 'ward', 'town', 'gender', 'age', 'num'])
# Excelファイルからデータを読み込みデータフレームに格納
for ward in wardlist: # 区毎のループ処理
    for year in yearlist: # 年毎のループ処理
        filename = ward + '_tyo' + year + month + '.xls'
        xls = pd.ExcelFile(filename)
        df = xls.parse(xls.sheet_names[0], skiprows=2, parse_cols="B:DE")
        df['year'] = 1988+int(year)
        df['month'] = int(month)
        # 文字列前後の空白の除去
        df['ward'] = df.iloc[:,0].fillna(method='ffill').apply(lambda x: x.strip())
        df['town'] = df.iloc[:,1].fillna(method='ffill').apply(lambda x: x.strip())
        df['gender'] = df.iloc[:,2].fillna(method='ffill').apply(lambda x: x.strip())
        #
        print datetime.datetime.now(), ward, year
        # 0歳から100歳以上までの各列を処理
        for i in range(0,101):
            df['age'] = i
            df['num'] = df.iloc[:,i+7].fillna(0).apply(lambda x: int(x))
            dfall = pd.concat([dfall, df.iloc[3:, 108:]])
            #
# 総数行の削除
dfall = dfall[dfall['gender'] != unicode('総数', 'utf-8')]
# CSVへの書き出し
dfall.to_csv('all_tyo.csv', index=False, encoding='UTF-8')
#

　ソースコードの25行目〜28行目で、0歳から100歳以上の101列を順に取り出して、データフレームのnumという列に入れて、pandasのconcat関数を使って順に統合していきます。

（4）平成を西暦に変換

　16行目で平成の年号に1988を加えて西暦に変換しています。

（5）空白となっているセルにデータを補完

　図1を見るとB列には本来全て美浜区が入っているべきなのですが、空白になっているセルがあります。また、C列も町丁名が最初の行にしかなく後の2行は空白です。ソースコードの19行目と20行目ではfillnaメソッドを使って本来はいっているべきデータを補完しています。今回のケースでは後続の空白は先に貼っていたデータを使って保管するという'ffill'というオプションでデータを補完しています。

　また、人数の部分で空欄になっているセルはfillnaメソッドで0に変換しておきます（27行目）。

（6）文字列の前後のスペースを除去

　Excelを見ただけでは分かりませんが、文字の前後にスペースが含まれているセルがありますので、前後のスペースを取り除いてきれいにしておきます。例えば、“美浜区”と“ 美浜区 ”（※前後にスペースが入っている）では見かけ上、ほとんど分かりませんが別データとして扱われますので、おかしなことになってしまいます。

（7）集計行の削除

　図1を見るとD列には“総数”という男性と女性を合計した集計行が入っていますので削除しておきます（31行目）。

データフレームの構造

　順序が逆になってしまいましたがデータフレームの構造は、9行目でデータフレームの定義をしています。今回は、

	年（year）
	月（month）
	区（ward）
	町丁（town）
	性別（gender）
	年齢（age）
	人口（num）

　というデータ構造を作っています。yearはExcelにはないデータなので、Excelのファイル名にある平成の年号に1988を加えて西暦のデータを生成しています。またmonthについても各都市の3月末時点のデータのみを使っていますので、こちらもExcelのファイル名にある03を整数に変換して生成しています（17行目）。

　ソースコードを実行すると、以下のようなデータが生成されて“all_tyo.csv”というファイル名のCSVデータが出来上がります。ここでようやくデータが分析できる状態になりました。

In [2]: dfall
Out[2]:
<class 'pandas.core.frame.DataFrame'>
Int64Index: 1954148 entries, 4 to 320
Columns: 7 entries, year to num
dtypes: int64(4), object(3)
In [3]: dfall.head(20)
Out[3]:
    year  month  ward     town gender  age  num
4   1993      3  花見川区    朝日ケ丘町      男    0   57
5   1993      3  花見川区    朝日ケ丘町      女    0   62
7   1993      3  花見川区      天戸町      男    0    8
8   1993      3  花見川区      天戸町      女    0    7
10  1993      3  花見川区      内山町      男    0    0
11  1993      3  花見川区      内山町      女    0    1
13  1993      3  花見川区     宇那谷町      男    0    2
14  1993      3  花見川区     宇那谷町      女    0    0
16  1993      3  花見川区      柏井町      男    0   27
17  1993      3  花見川区      柏井町      女    0   19
19  1993      3  花見川区  検見川町１丁目      男    0    8
20  1993      3  花見川区  検見川町１丁目      女    0    2
22  1993      3  花見川区  検見川町２丁目      男    0   11
23  1993      3  花見川区  検見川町２丁目      女    0   11
25  1993      3  花見川区  検見川町３丁目      男    0   28
26  1993      3  花見川区  検見川町３丁目      女    0   20
28  1993      3  花見川区  検見川町５丁目      男    0   13
29  1993      3  花見川区  検見川町５丁目      女    0    9
31  1993      3  花見川区      犢橋町      男    0    6
32  1993      3  花見川区      犢橋町      女    0    4