JSONファイルからDBのテーブル形式のデータを抽出する

階層構造になっているJSONファイルから、DBに登録するためのテーブル形式のデータを抽出する方法を考えます。例として、伝票データをJSONファイルで受け取って、DBの伝票テーブルと伝票明細テーブルに登録するためのデータを作成してみます。

## TL;DR

- JSONデータのフラット化には``pandas.json_normalize()``を使います。
- 階層構造の親子データを紐づけるために、``pandas.json_normalize(record_path="children", meta=["parent_id"])``を使います。
- 入力データに存在しない列を追加するときには`pandas.DataFrame.reindex()`が便利です。

## 入出力データ

入力となるJSONデータのイメージがこちら。

```json:slip.json
[
    {
        "slip_no": 1234,
        "amount": 2400,
        "destination": {
            "cd": "A0001",
            "name": "ほげほげマート"
        },
        "details": [
            {
                "item_cd": "item0001",
                "item_name": "商品A",
                "price":800,
                "count": 3
            }
        ]
    },
    {
        "slip_no": 5678,
        "amount": 1300,
        "destination": {
            "cd": "A1002",
            "name": "ふがふがストア"
        },
        "details": [
            {
                "item_cd": "item0001",
                "item_name": "商品A",
                "price":800,
                "count": 2
            },
            {
                "item_cd": "item0002",
                "item_name": "商品B",
                "price":100,
                "count": 5
            }
        ]
    },
    {
        "slip_no": 1122,
        "amount": 3100,
        "destination": {
            "cd": "A3001",
            "name": "ピヨピヨ商店"
        },
        "details": [
            {
                "item_cd": "item0001",
                "item_name": "商品A",
                "price":800,
                "count": 2
            },
            {
                "item_cd": "item0002",
                "item_name": "商品B",
                "price":100,
                "count": 5
            },
            {
                "item_cd": "item0003",
                "item_name": "商品C",
                "price":50,
                "count": 10
            },
            {
                "item_cd": "item0004",
                "item_name": "商品D",
                "price":500,
                "count": 1
            }
        ]
    }
]
```

出力となるテーブル形式のイメージがこちらです。

slip_no | amount | details | destination.cd | destination.name
---- | ---- | ---- | ---- | ----
1234 | 2400 | "[{'item_cd': 'item0001', 'item_name': '商品A', 'price': 800, 'count': 3}]" | A0001 | ほげほげマート
5678 | 1300 | "[{'item_cd': 'item0001', 'item_name': '商品A', 'price': 800, 'count': 2}, {'item_cd': 'item0002', 'item_name': '商品B', 'price': 100, 'count': 5}]" | A1002 | ふがふがストア
1122 | 3100 | "[{'item_cd': 'item0001', 'item_name': '商品A', 'price': 800, 'count': 2}, {'item_cd': 'item0002', 'item_name': '商品B', 'price': 100, 'count': 5}, {'item_cd': 'item0003', 'item_name': '商品C', 'price': 50, 'count': 10}, {'item_cd': 'item0004', 'item_name': '商品D', 'price': 500, 'count': 1}]" | A3001 | ピヨピヨ商店

slip_no | item_cd | item_name | price | count | updated_by | updated_date
---- | ---- | ---- | ---- | ---- | ---- | ----
1234 | item0001 | 商品A | 800 | 3 | patch_230225 | 2023-02-25 18:00:00
5678 | item0001 | 商品A | 800 | 2 | patch_230225 | 2023-02-25 18:00:00
5678 | item0002 | 商品B | 100 | 5 | patch_230225 | 2023-02-25 18:00:00
1122 | item0001 | 商品A | 800 | 2 | patch_230225 | 2023-02-25 18:00:00
1122 | item0002 | 商品B | 100 | 5 | patch_230225 | 2023-02-25 18:00:00
1122 | item0003 | 商品C | 50 | 10 | patch_230225 | 2023-02-25 18:00:00
1122 | item0004 | 商品D | 500 | 1 | patch_230225 | 2023-02-25 18:00:00

データ抽出のポイントとしては、下記の3点です。

- オブジェクトとして定義されている項目のフラット化
- 親子データを対応付けるためのキー項目付与
- 入力データにない列の設定

## オブジェクトとして定義されている項目のフラット化

オブジェクトとして定義されている項目（例では``destination``）をフラット化したいです。こんな時には``pandas.json_normalize()``を使います。

```python:normalize.py
import pands as pa
import json
import sys

file_name = sys.argv[1]
df = pa.read_json(file_name)

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(df)

print(slip_head)
```

実行結果は以下のようになります。``destination.cd``のように、オブジェクト名.メンバー名の形式でフラット化されているのがわかります。

```
$ python3 normalize.py ./slip.json
   slip_no  amount                                            details destination.cd destination.name
0     1234    2400  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A0001          ほげほげマート
1     5678    1300  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A1002          ふがふがストア
2     1122    3100  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A3001           ピヨピヨ商店
```

ただし、これだけでは配列型がそのまま出力されていますので、次は配列型のデータを抽出してみます。

## 親子データを対応付けるためのキー項目付与

配列型のデータを抽出するためには、``pandas.json_normalize()``の``record_path``という引数に項目名を指定します。

```python:normalize.py
import pands as pa
import json
import sys

file_name = sys.argv[1]
json_data = json.load(open(file_name))

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(json_data)
print(slip_head)

print('----------------------------------------')

# 配列型のデータを抽出する。
slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
print(slip_detail )
```

```
$ python3 normalize.py ./slip.json 
   slip_no  amount                                            details destination.cd destination.name
0     1234    2400  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A0001          ほげほげマート
1     5678    1300  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A1002          ふがふがストア
2     1122    3100  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A3001           ピヨピヨ商店
----------------------------------------
    item_cd item_name  price  count slip_no
0  item0001       商品A    800      3    1234
1  item0001       商品A    800      2    5678
2  item0002       商品B    100      5    5678
3  item0001       商品A    800      2    1122
4  item0002       商品B    100      5    1122
5  item0003       商品C     50     10    1122
6  item0004       商品D    500      1    1122
```

これだけでは親子関係を紐づけるための項目（伝票番号）が足りません。``pandas.json_normalize()``では、``meta``という引数を使って親の項目を付与して抽出することができます。

伝票番号（slip_no）を親のキーとして付与する例を示します。

```python:normalize.py
import pands as pa
import json
import sys

file_name = sys.argv[1]
json_data = json.load(open(file_name))

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(json_data)
print(slip_head)

print('----------------------------------------')

# 配列型のデータを抽出する。
slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
print(slip_detail )
```

最後の列に``slip_no``という列が追加されて抽出されます。

```
$ python3 normalize.py ./slip.json
   slip_no  amount                                            details destination.cd destination.name
0     1234    2400  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A0001          ほげほげマート
1     5678    1300  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A1002          ふがふがストア
2     1122    3100  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A3001           ピヨピヨ商店
----------------------------------------
    item_cd item_name  price  count slip_no
0  item0001       商品A    800      3    1234
1  item0001       商品A    800      2    5678
2  item0002       商品B    100      5    5678
3  item0001       商品A    800      2    1122
4  item0002       商品B    100      5    1122
5  item0003       商品C     50     10    1122
6  item0004       商品D    500      1    1122
```

また、データの構造によっては親と子に同じ項目名が含まれていることもあるでしょう。その場合には``pandas.json_normalize()``の``meta_prefix``という引数を使って``meta``で指定した項目にプレフィックスを付与することもできます。

## 入力データにない列の設定

DBのテーブルに追加したいとき、元のデータに存在しない列を追加したくなる場合があります。例えば、``updated_by``や``updated_date``などのシステム管理の列です。

この場合には``pandas.DataFrame.reindex()``が使えます。この関数の主な役割は、index（DataFrameを2次元配列ととらえた場合の行）を編集することですが、同時に列も編集することができます。``columns``という引数に、新しく生成されるDataFrameで利用したい列を指定することで任意の列を追加することができます。

また、``columns``に指定する順番で、列の順番を入れ替えることもできます。

```python:normalize.py
import pands as pa
import json
import sys

file_name = sys.argv[1]
json_data = json.load(open(file_name))

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(json_data)
print(slip_head)

print('----------------------------------------')

# 配列型のデータを抽出する。
slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
print(slip_detail)

print('----------------------------------------')

# 列を追加する。
slip_detail = slip_detail.reindex(columns=['slip_no', 'item_cd', 'item_name', 'price', 'count', 'updated_by', 'updated_date'])
print(slip_detail)
```

```
$ python3 normalize.py ./slip.json
   slip_no  amount                                            details destination.cd destination.name
0     1234    2400  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A0001          ほげほげマート
1     5678    1300  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A1002          ふがふがストア
2     1122    3100  [{'item_cd': 'item0001', 'item_name': '商品A', '...          A3001           ピヨピヨ商店
----------------------------------------
    item_cd item_name  price  count slip_no
0  item0001       商品A    800      3    1234
1  item0001       商品A    800      2    5678
2  item0002       商品B    100      5    5678
3  item0001       商品A    800      2    1122
4  item0002       商品B    100      5    1122
5  item0003       商品C     50     10    1122
6  item0004       商品D    500      1    1122
----------------------------------------
  slip_no   item_cd item_name  price  count  updated_by  updated_date
0    1234  item0001       商品A    800      3         NaN           NaN
1    5678  item0001       商品A    800      2         NaN           NaN
2    5678  item0002       商品B    100      5         NaN           NaN
3    1122  item0001       商品A    800      2         NaN           NaN
4    1122  item0002       商品B    100      5         NaN           NaN
5    1122  item0003       商品C     50     10         NaN           NaN
6    1122  item0004       商品D    500      1         NaN           NaN
```

## CSVファイルに出力

最後に、伝票ヘッダと伝票明細をCSVファイルに出力します。``index=False``をつけないと、DataFrameのindexが出力されてしまってテーブルの形になりませんので注意してください。

```python:normalize.py
import pands as pa
import json
import sys

file_name = sys.argv[1]
json_data = json.load(open(file_name))

# オブジェクトのメンバーをフラット化する。
slip_head = pa.json_normalize(json_data)
print(slip_head)

print('----------------------------------------')

# 配列型のデータを抽出する。
slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
print(slip_detail)

print('----------------------------------------')

# 列を追加する。
slip_detail = slip_detail.reindex(columns=['slip_no', 'item_cd', 'item_name', 'price', 'count', 'updated_by', 'updated_date'])
print(slip_detail)

# CSVファイルに出力する。
slip_head.to_csv('slip_head', index=False)
slip_detail.to_csv('slip_detail', index=False)
```


## まとめ

階層構造を持つJSONファイルから、``pandas.json_normalize()``と``pandas.DataFrame.reindex()``を使ってDBのテーブル形式でデータを取り出す方法を見てきました。最終的には、ここで抽出したデータをExcelか何かに貼り付けて、追加した列に固定値を埋めるなりしてDBに投入すれば良いと思います。ここまでやったのだからDBへの投入もPythonでやればいいと思うかもしれませんが、本番環境に投入するデータは投入する前にレビューしたいじゃないですか（SIer脳）。

また、記事を書きながら「どうせなら明細番号とかも付与したい」と思いましたが、多分``pandas.DataFrame.groupby.cumcount()``でできます。

```
slip_detail = pa.json_normalize(json_data, record_path='details', meta='slip_no')
slip_detail['detail_no'] = slip_detail.groupby('slip_no').cumcount()
```

## 参考資料

- [pandas.json_normalize](https://pandas.pydata.org/docs/reference/api/pandas.json_normalize.html)
- [pandas.DataFrame.reindex](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.reindex.html)
- [pandas.DataFrame.cumcount](https://pandas.pydata.org/docs/reference/api/pandas.core.groupby.GroupBy.cumcount.html)
- [pandasのjson_normalizeで辞書のリストをDataFrameに変換](https://note.nkmk.me/python-pandas-json-normalize/)
- [Pandasのjson_normalizeを詳しく調べてみる](https://qiita.com/simonritchie/items/7e50b47ccadbbb0fba57)
- [Pandasで同一のIDだった時に連番を追加する](https://nyagolante.net/2021/02/16/50/)