ExcelやCSVの表形式から、入れ子の形式のJsonに変換したい

Question

やりたいこと

ExcelやCSVの表形式のデータをJsonファイルに出力するにはどのようにしたらいいでしょうか？
Pandasを使ってやってみたりしましたが、そもそもどこからやっていいかわからず質問させていただきました。

追記

階層1の中に階層2、階層3を入れていきたいのと、
階層の深さによらず、最下層と所有者を辞書形式にしたいです。

操作前データ

階層1	階層2	階層3	所有者
果物	赤	りんご	A
果物	赤	いちご	A
果物	黄色	ばなな	B
果物	黄色	れもん	C
野菜	赤	にんじん	C
野菜	赤	とまと	A
野菜	緑	きゅうり	B
野菜	緑	せろり	A
その他	赤		B
その他	黄色		A

期待するoutput

{
	"果物": {
		"赤": [
			{
				"りんご": "Ａ",
				"いちご": "Ａ"
			}
		],
		"黄色": [
			{
				"ばなな": "Ｂ",
				"れもん": "Ｃ"
			}
		]
	},
	"野菜": {
		"赤": [
			{
				"にんじん": "Ｃ",
				"とまと": "Ａ"
			}
		],
		"緑": [
			{
				"きゅうり": "Ｂ",
				"せろり": "Ａ"
			}
		]
	},
	"その他": {
		"赤": "Ｂ.",
		"黄色": "Ａ"
	}
}

やってみたこと

以下のコードを実行してみましたが、その他の最終階層に所有者の値が入りません。

import pandas as pd
import json

# Read the Excel file
df = pd.read_excel('sample.xlsx')

# Create a dictionary to hold the data
data = {}

# Iterate over the rows of the DataFrame
for index, row in df.iterrows():
    # Create a reference to the dictionary for the current row
    current_dict = data
    
    # Iterate over the values in the row
    for i, value in enumerate(row[:-1]):
        # If the value is not empty, use it as a key in the dictionary
        if not pd.isna(value):
            # If the key does not exist in the dictionary, create it
            if value not in current_dict:
                # If this is the last column, the value is the owner
                if i == len(row)-2:
                    current_dict[value] = row[-1]
                # Otherwise, create a new dictionary
                else:
                    current_dict[value] = {}
            # Update the reference to the dictionary for the current row
            current_dict = current_dict[value]
    
# Convert the dictionary to JSON and write it to a file
with open('example.json', 'w') as f:
    json.dump(data, f, ensure_ascii=False, indent=4)

出力結果(example.json)

{
    "果物": {
        "赤": {
            "りんご": "A",
            "いちご": "A"
        },
        "黄色": {
            "ばなな": "B",
            "れもん": "C"
        }
    },
    "野菜": {
        "赤": {
            "にんじん": "C",
            "とまと": "A"
        },
        "緑": {
            "きゅうり": "B",
            "せろり": "A"
        }
    },
    "その他": {
        "赤": {},
        "黄色": {}
    }
}

Comments

[can110]

操作前データからどのようなルール（仕様）によって期待するoutputのような構造になるのかを記載ください。

[syayo]

コメントありがとうございます。
階層1の中に階層2、階層3を入れていきたいのと、
階層の深さによらず、最下層と所有者を辞書形式にしたいです。
質問にも追記いたしました。

[melian]

"階層1": { "赤": [ ... の "階層1" は "果物" ではないでしょうか？ また同様に、"階層2": { "赤": [ ... の "階層2" は "野菜" かと思いますが。

[syayo]

melian さんのおっしゃる通りです。
質問のJSONを修正いたしました。

[TakaiY]

階層3に同じキーが現われた場合、どのように対応すべきですか？
"赤": [
{
"りんご": "Ａ",
"いちご": "Ａ"
"いちご": "B"
}
というデータは作れないという意味です。

[syayo]

階層3に同じキーが現れた場合は考慮していませんでした。
errorになってしまうかと思いますので、そのような表データは使用しないというルールで運用したいです。

Answer 1

今のコードに近い形でやるなら、次に入れるキーという形で、ひとつ前のループの変数を持っておくようにすればできそうです。

data = {}

for index, *row in df.itertuples():
    current_dict = data
    key = row[0]  # 次に入れるキー
    for value in row[1:-1]:
        if not pd.isna(value):
            if key not in current_dict:
                current_dict[key] = {}
            current_dict = current_dict[key]
            key = value
    current_dict[key] = row[-1]

Comments

[syayo]

ありがとうございます！
教えていただいたコードで実行したところ、うまくいきました。
先に回答して頂いた方にベストアンサーは上げてしまいましたが、本当にありがとうございます。

Answer 2

import io
import pandas as pd
import numpy as np
import json

csv_data = '''
階層1,階層2,階層3,階層4,所有者
果物,赤,りんご,,A
果物,赤,いちご,甘い,A
果物,黄色,ばなな,,B
果物,黄色,れもん,酸っぱい,C
野菜,赤,にんじん,,C
野菜,赤,とまと,甘い,A
野菜,緑,きゅうり,,B
野菜,緑,せろり,,A
その他,赤,,,B
その他,黄色,,,A
'''

#
def nested_dict(df):
    l = len(df.columns)
    if l == 1:
        return df.values[0][0] #if df.values.size == 1 else df.values.squeeze()
    if df.values[0][0] is np.nan:
        idx = df.iloc[0].first_valid_index()
        if idx == df.columns[-1]:
            return df.values[0][-1]
        df = df.loc[:,idx:]
    grp = df.groupby(df.columns[0], sort=False, dropna=False)
    return {k: nested_dict(g.iloc[:,1:]) for k, g in grp}

dic = nested_dict(df)
js = json.dumps(dic, ensure_ascii=False, indent=2)
print(js)

# {
#   "果物": {
#     "赤": {
#       "りんご": "A",
#       "いちご": {
#         "甘い": "A"
#       }
#     },
#     "黄色": {
#       "ばなな": "B",
#       "れもん": {
#         "酸っぱい": "C"
#       }
#     }
#   },
#   "野菜": {
#     "赤": {
#       "にんじん": "C",
#       "とまと": {
#         "甘い": "A"
#       }
#     },
#     "緑": {
#       "きゅうり": "B",
#       "せろり": "A"
#     }
#   },
#   "その他": {
#     "赤": "B",
#     "黄色": "A"
#   }
# }

Comments

[syayo]

回答頂きありがとうございます。
回答内容で狙いのデータは変換できましたが、階層4、5と階層が増えた場合も対応したく、実際に試したところ、どうしてもNaNがJsonに入ってしまいます。
例えば以下のような階層が深くなった場合のコードはどこを修正すればよろしいでしょうか？
csv_data = '''
階層1,階層2,階層3,階層4,所有者
果物,赤,りんご,,A
果物,赤,いちご,甘い,A
果物,黄色,ばなな,,B
果物,黄色,れもん,酸っぱい,C
野菜,赤,にんじん,,C
野菜,赤,とまと,甘い,A
野菜,緑,きゅうり,,B
野菜,緑,せろり,,A
その他,赤,,,B
その他,黄色,,,A
'''

[melian]

修正しましたが、入力データによっては NaN が残ってしまうかもしれません。

[syayo]

ありがとうございます。目的とした出力を確認できました。
早急にご回答いただき、ありがとうございました。

[syayo]

すみません。
期待する結果だと思っていましたが、少し違いました。
細かいですが、再度質問させていただきます。

最終階層まで｛｝でくくられていますが、期待する出力は、最終階層の辞書が［］の中に｛｝が入る形式になっております。
今形式にアウトプットできますでしょうか？

期待する結果
{
"果物": {
"赤": [
{
"りんご": "Ａ",
"いちご": "Ａ"
}
],
"黄色": [
{
"ばなな": "Ｂ",
"れもん": "Ｃ"
}
]
},
"野菜": {
"赤": [
{
"にんじん": "Ｃ",
"とまと": "Ａ"
}
],
"緑": [
{
"きゅうり": "Ｂ",
"せろり": "Ａ"
}
]
},
"その他": {
"赤": "Ｂ.",
"黄色": "Ａ"
}
}

[melian]

はい、可能です。ですが、時間が取れないので他の方の回答をベストアンサーにしていただいて構いません。よろしくお願いします。

Answer 3

DataFrameでこういうことをするのは苦手なので、ベタなやりかたです。 質問のコードもDFっぽくはない作りのようですが。
また、3階層限定です。「階層の深さによらず、最下層と所有者を辞書形式にしたい」というのはちょっと難しいような。

import csv

out_data = {}

with open('test.csv', mode='r') as inf:
    reader = csv.DictReader(inf)
    for row in reader:
        cat1 = out_data.get(row['階層1'], {})
        cat2 = cat1.get(row['階層2'], {})
        cat2[row['階層3']] = row['所有者']
        cat1[row['階層2']] = cat2
        out_data[row['階層1']] = cat1

Comments

[syayo]

回答して頂きありがとうございます。
そうですよね、私の方でも階層を３に固定であればできそうだと思っていましたが、階層の変化に対応するのに苦戦しております。。。