先頭行がそのままヘッダーにならない、若干エクセル風味の CSV ファイルを Python で前処理する

# 一行まとめ
先頭行をそのままヘッダー（列名）として扱えない CSV データの前処理をした。

# 背景
Python でデータ分析をする練習用のデータとして、[独立行政法人統計センター](https://www.nstac.go.jp/use/literacy/ssdse/)が提供している SSDSE（教育用標準データセット）を利用したい。

しかし、SSDSE-市区町村（SSDSE-A）をダウンロードしたところ、そのままでは扱いづらいデータであることがわかった。



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2602439/15071fec-ec69-1089-2ae4-64227ffa2723.png)

（CSV データなんだけど、若干のエクセル味を感じる…！）

解決したい問題
- 先頭行が正しい項目名になっていない
- 年度が一つの行になっており、そのままでは集計できない

# 対応（2023-12-23 更新）
いただいたコメントを参考にしてコードをコンパクトにできたので更新分を載せます。

```python
import pandas as pd

id_cols = ['地域コード', '都道府県', '市区町村']

df = pd.read_csv('SSDSE-A-2023.csv', encoding='cp932', header=[1,2])
df = pd.melt(
    df, id_vars=list(df.columns[:3]), 
    var_name=['年度', '項目'], value_name='値')
df.columns = [*id_cols, *df.columns[3:]]
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2602439/0a5edb44-26c2-0faa-c688-4adbe580a199.png)

header をリストで指定することで列を MultiIndex にしています。
MultiIndex はそのままだとちょっと扱いづらいので、`id_vars`などは少しまどろこっしい書き方になってます。

# 感想
だいぶスッキリしました。
マルチカラムの扱いは相変わらず自信ないですが。

---
以下、アーカイブとして.

# 対応（過去分）

解決したい問題
- 先頭行が正しい項目名になっていない
- 年度が一つの行になっており、そのままでは集計できない

戦略
- 列の階層構造を分解して縦持ちデータ（地域-項目-年度ごとに一行）にすることを目指す

- 必要な情報を一旦文字列で結合して、あとで分解する

# 対応（2023-12-23 更新）
いただいたコメントを参考にしてコードをコンパクトにできたので更新分を載せます。


単純にデータを読み込んだ場合の結果

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2602439/dd71463a-9b1e-eb7e-75dc-9cd5f9046d62.png)

データの前処理をした結果



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2602439/0d9e229f-f72f-e4ce-9541-398477fb0cfd.png)

実行した Python のコード

```python
import pandas as pd

FILE_PATH = 'あなたの/ファイルパス/を入れてください'

LOCAL_CODE = '地域コード'
PREFECTURE = '都道府県'
MUNICIPALITY = '市区町村'
ITEM_YEAR = '項目名（年度）'
ITEM_CODE = '項目コード'
ITEM_NAME = '項目名'
YEAR = '年度'
AMOUNT = '量'

df = pd.read_csv(FILE_PATH, encoding='shift-jis', header=None)
df.iloc[0:2,:3] = '' # 表の左上ゾーンの NaN が邪魔なので '' で埋めておく

# ヘッダーとしてまとめたい情報を抜き出す
item_code_row = df.iloc[0,:]
year_row = df.iloc[1,:]
item_name_row = df.iloc[2,:]

# 一旦文字列として結合してあとで分解する
_header = item_code_row + ',' + item_name_row + ',' + year_row
_header = _header.str.strip(',') # 地域コードなどは分割しないので , を削っておく
df.columns = _header

# ヘッダーの素材になった行を削除しておく
df = df.iloc[3:,]

# [地域-項目名-年度] が一行ずつになるようにデータを変換する
id_cols = [LOCAL_CODE, PREFECTURE, MUNICIPALITY]
_var_col = 'variable' # データ加工のための作業列

df = pd.melt(df, id_vars=id_cols, var_name=_var_col, value_name=AMOUNT)

df[ITEM_CODE] = df[_var_col].apply(lambda x: x.split(',')[0])
df[ITEM_NAME] = df[_var_col].apply(lambda x: x.split(',')[1])
df[YEAR] = df[_var_col].apply(lambda x: x.split(',')[2])
df = df[[*id_cols, ITEM_CODE, ITEM_NAME, YEAR, AMOUNT]] # 列の並び替え
df
```
# 感想
単純な問題のようで、実際には前処理にかなり手こずった。
とはいえ、利用可能なデータが公的に供給されていることはありがたい。
先頭行をそのままヘッダーに使えないデータは今後もしばしば出くわしそうなので、
今後こういうケースがあったら今回の知見を活かしたい。
（もうちょとスマートな解決方法もありそうだけど、まあ前処理は多少泥臭くてもやるものなので…）

# Appendix
## データの変形過程
実行環境は VSCode の Interactive Window.

```python
df = pd.read_csv(FILE_PATH, encoding='shift-jis', header=None)
df.iloc[0:2,:3] = '' # 表左上ゾーンの NaN が邪魔なので '' で埋めておく
df
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2602439/b6178455-7f87-a171-8f01-3b25d657978f.png)

```python
# ヘッダーとしてまとめたい情報を抜き出す
item_code_row = df.iloc[0,:]
year_row = df.iloc[1,:]
item_name_row = df.iloc[2,:]

# 一旦文字列として結合して、あとで分解する戦略を取る.
_header = item_code_row + ',' + item_name_row + ',' + year_row
_header = _header.str.strip(',') # 地域コードなどは分割しないので , を削っておく
df.columns = _header
df
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2602439/9a170b95-3a61-0a14-5256-4935b48eb898.png)


```python
# header の素材になった行を削除しておく
df = df.iloc[3:,]

# [地域 - 項目名 - 年度] が一行ずつになるようにデータを変換する
id_cols = [LOCAL_CODE, PREFECTURE, MUNICIPALITY]
_var_col = 'variable'

df = pd.melt(df, id_vars=id_cols, var_name=_var_col, value_name=AMOUNT)
df
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2602439/c77656d3-416c-849a-6529-803fb8d43784.png)


```python
# 文字列で結合された列を分解してごにょごにょする
df[ITEM_CODE] = df[_var_col].apply(lambda x: x.split(',')[0])
df[ITEM_NAME] = df[_var_col].apply(lambda x: x.split(',')[1])
df[YEAR] = df[_var_col].apply(lambda x: x.split(',')[2])
df = df[[*id_cols, ITEM_CODE, ITEM_NAME, YEAR, AMOUNT]] # 列の並び替え
df
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2602439/3f6eb9d2-f530-a97a-2246-3471a8e337cf.png)