Life is Like a Boat

忘備録や投資日記、プログラミングに関するメモやtipsなど

景気ウォッチャー調査のxlsファイルをpandasで加工してみる

官公庁が出している統計データの大半はpdf, csv, xlsです。pdfはさておき、pandasはcsvやxlsをわりかし楽にdataframeに取り込めるのでありがたいです。

元データ

統計表(Excel表形式):景気ウォッチャー調査 - 内閣府 季節調整値の全国の分野・業種別、地域別DIの推移(Excel形式)を使います。

PandasでImport

df = pd.read_excel(XLS_FILE_PATH, sheet_name=sheet_type, skiprows=5)

excelファイルを取り込むメソッドはread_excelです。sheet_nameを指定してあげるとそのシートをdataframeにしてくれます。

skiprowsで不要な行をスキップします。

官公庁のデータ見てると、例えば年月のカラムがこの様に表現されているケースをよく見かけます。   f:id:nerimplo:20180702155531p:plain 2002年1月、2002年2月とするはずが、年を省略してあります。
dataframeとして取り込むと、この年の部分が欠損値になってしまい、時系列で分析できなくなります。

欠損値を埋める

Pandasの素晴らしい点は、欠損値を他の値で埋めてくれるメソッドを用意してくれているところです。

df['ABC']=df['ABC'].fillna(method='ffill')

fillnaでmethod='ffill'とすると前の値で置き換えられます。この場合は矢印の部分が2002年になりますね。
下記参考にしたブログ記事です。 pandasで欠損値NaNを除外(削除)・置換(穴埋め)・抽出 | note.nkmk.me

下記はシート名、分野別(現状)、分野別(先行き)をdataframe化する際のサンプルです。

import pandas as pd
from datetime import datetime
import numpy as np

XLS_FILE_PATH = 'EXCEL_FILE_PATH'

def combine_year_and_month(year,month):
    y = year.replace('年', '').replace(' ','')
    m = month
    return datetime(year=int(y), month=int(m), day=1)

def load_sheets_by_category():
    dfs = []
    for sheet_type in ['分野別(現状)', '分野別(先行き)']:
        df = pd.read_excel(XLS_FILE_PATH, sheet_name=sheet_type, skiprows=5)
        df = df.drop('Unnamed: 0',axis=1)
        df['Unnamed: 1']=list(map(lambda x : x.replace('\u3000',' ') if type(x) == str else x, df['Unnamed: 1']))
        df = df.replace(r'\s+', np.nan, regex=True)

        df['Unnamed: 1']=df['Unnamed: 1'].fillna(method='ffill')

        df['YearMonth']=list(map(combine_year_and_month, df['Unnamed: 1'], df['Unnamed: 2']))

        #YearMonth欄を作ったので1,2は不要
        df = df.drop('Unnamed: 1',axis=1)
        df = df.drop('Unnamed: 2',axis=1)

        NAMING_RULE = {
                        'Unnamed: 3':'合計',
            'Unnamed: 5':'小売関連',
            'Unnamed: 6':'飲食関連',
            'Unnamed: 7':'サービス関連',
            'Unnamed: 8':'住宅関連',
            'Unnamed: 4':'家計動向',
            'Unnamed: 9':'企業動向',
            'Unnamed: 10':'製造業',
            'Unnamed: 11':'非製造業',
            'Unnamed: 12':'雇用関連',
                      }

        df.rename(columns=NAMING_RULE, inplace=True)
        dfs.append(df)
    return dfs

df_current, df_future = load_sheets_by_category()