公共用水域水質測定データはwebで公開されていますが,都道府県によって公開方法がまちまちです.pdfのみだったり,CSVだったり,Excelだったりしますし,形式もばらばらです.それぞれに応じてpandasでデータを読み込み処理する際のTipsをまとめます.なお,基本的にファイルのエンコーディングはUTF-8とし,pandasのDataFrameは一般にdfとします.

dfのカラム名とindex名のリストを抽出する

以下のように.valuesでリスト化できます.

col_names=df.columns.values
index_names=df.index.values

dfの列データの全角文字を半角文字に変換

Pythonで全角文字を半角文字に変換する方法です.いろいろな方法があるようですが,中でも高速と評判のmojimojiを使ってみました.まずはインストールです.が,インストール時にエラーが出たので,その前に必要なパッケージとして,gcc-c++をインストールする必要がありました.

# yum install gcc-c++
$ pip install mojimoji

対象とするdfの列をcolとし,colの各データtxtに含まれる全角を半角に,リスト内包を用いて変換します.

dfc = [mojimoji.zen_to_han(txt) for txt in df[col].values]
df[col]=dfc

複数のシートを持つエクセルファイルからデータを読み込む

東京都のエクセルファイルを例に,ブック全体をbookに収め,全シート名をsheetnamesリストに収めます.
各シート内のエクセル表の1行目,3行目および4行目は飛ばし,2行目をカラム名に,シート名を辞書のkeyにして,全シートを順次読み込みます.dfstnは観測地点情報を含むdfで,dfstn.stn_nameがエクセルファイルのシート名と同じ観測地点名を持つカラムです.このカラムに含まれるsheetnameがエクセルのシート名に一致している場合のみ読み込みます.dfstnには観測地点名に加え,緯度経度情報も含めてあり,後ほど,緯度経度情報を加えます.

file="15_toukyowan13.xls"
book=xlrd.open_workbook(file)
sheetnames=[name for name in book.sheet_names()]
dfxls={sheetname: pd.read_excel(file, sheetname=sheetname, skiprows=[0,2,3], \
       skipinitialspace=True, na_values=(' ')) \
       for sheetname in dfstn.stn_name.values \
       if sheetname in sheetnames}
stn_name=list(dfxls.keys()) # シート名のリスト
dfi=[] # シート毎のdfを格納するリスト
for i in range(len(stn_name)): # シート毎のループ
  dfi.append(dfxls[stn_name[i]])
  # 以下はdfstnから測点番号,測点名,緯度,経度情報を抽出する例
  stn_lon_lat=dfstn[dfstn["stn_name"]==stn_name[i]].values
  col_stn=[stn_lon_lat[0][0]] * len(dfi[i])
  col_stn_name=[stn_lon_lat[0][1]] * len(dfi[i])
  col_lon=[stn_lon_lat[0][3]] * len(dfi[i])
  col_lat=[stn_lon_lat[0][2]] * len(dfi[i])
  dfi[i]["stn"]=col_stn  # 測点番号のカラムを追加
  dfi[i]["stn_name"]=col_stn_name  # 測点名のカラムを追加
  dfi[i]["lon"]=col_lon  # 経度のカラムを追加
  dfi[i]["lat"]=col_lat  # 緯度のカラムを追加
  df=pd.concat(dfi, ignore_index=True) # シート毎のdfを縦に繋げ1つのdfに

dfのデータをきれいにする

東京都のエクセルデータには奇妙なデータ,例えば,数値の後に空白文字がたくさん付いたセルやデータなしの部分に空白文字が複数入ったセル,が存在しています.これをきれいに掃除し,適切に処理する必要があります.

# カラム名のリストdf.columns.valuesの要素に空白文字があれば削除します
columns=[s.strip() for s in df.columns.values]
df.columns=columns
# セルの空白文字を削除し,データの存在しないセルをNaNに置換します
df.replace(r'\s+', "", regex=True, inplace=True)
df.replace("",np.nan, inplace=True) # データなしをNaNに置換
# 年月日カラム"date"では最初のセルにしか年月日情報が入っていないので,
# この欠損値を前方穴埋めします
df.date.fillna(method='ffill', inplace=True)
# 全角文字のカラム名を半角英数文字等で置換します
df.rename(columns={"採取水深": "Sampling_depth"}, inplace=True)
df.rename(columns={"DO": "DO"}, inplace=True)

年月日カラムと時刻カラムを結合してdatetimeのindexを作る

それぞれのカラム”date”と”time”のデータはそれぞれ”2013/4/10″および”8:35:00″のようになっています.これらを結合してindexにします.

date=df.date # "date"カラム
time=df.time # "time"カラム
datetime=[d.strftime("%Y/%m/%d") + " " + s.strftime("%H:%M:%S") \
       for d,s in zip(date,time)]
datetime=pd.to_datetime(datetime)
df.index=datetime # indexにする

DOのように”<0.1"といった文字列を適当な数値に変換する

例えばDOは0.1未満という表現を”<0.1"と表しています.これでは数値として扱われないので,例えばこれを仮に0.05のように0.5倍して実数化することを考えます. valを倍率(今の例では0.5),対象とするカラムをcolとします.文字列sに含まれる"<"を取り除くには,"<"が先頭文字であることから,s[1:]としています.if elseを含むリスト内包を使います.

dfc=[val * float(s[1:]) if str(s)[0] == "<" else s for s in df[col].values]
df[col]=np.array(dfc, dtype=np.float64)