将数据框转换为特定的JSON

Question

我想将我的DataFrame转换为特定的JSON。我尝试使用to_dict()，但目前我没有找到复制输出的正确参数。

你知道吗?

我的代码:

import pandas as pd
data = {
'alt' : ["BeattheBeachmark NEW", "BeattheBeachmark NEW"],
'Mod' : ["GA", "GA"],
'Pers' : ["Movment", "Movment"],
'Vie' : ["Inprogress", "Inprogress"],
'Actions' : ["Clear", "Add"]
}
df = pd.DataFrame(data)

My output:

result = {
"alt" : {
"BeattheBeachmark NEW" : {
"Mod" : {
"GA" :  {
"Pers" : {
"Movment" : {
"Vie" : {
"Inprogress" : {
'Actions' : ["Clear", "Add"]
}
}
}
}
}
}
}
}
}

Answer 1

您可以通过"alt"， "Mod"…一路上等等,创造你的字典:

import pandas as pd
import json
data = {
'alt' : ["BeattheBeachmark NEW", "BeattheBeachmark NEW"],
'Mod' : ["GA", "GA"],
'Pers' : ["Movment", "Movment"],
'Vie' : ["Inprogress", "Inprogress"],
'Actions' : ["Clear", "Add"]
}
df = pd.DataFrame(data)
output_dict = dict()
output_dict['alt'] = dict()
for alt in df.groupby("alt"):
output_dict['alt'][alt[0]] = dict()
output_dict['alt'][alt[0]]["Mod"] = dict()
for mod in alt[1].groupby("Mod"):
output_dict['alt'][alt[0]]["Mod"][mod[0]] = dict()
output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"] = dict()
for pers in mod[1].groupby("Pers"):
output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"][pers[0]] = dict()
output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"][pers[0]]["Vie"] = dict()
for vie in pers[1].groupby("Vie"):
output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"][pers[0]]["Vie"][vie[0]] = dict()
output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"][pers[0]]["Vie"][vie[0]]["Actions"] = list(vie[1].Actions)
print(json.dumps(output_dict, indent=4))

输出:

{
"alt": {
"BeattheBeachmark NEW": {
"Mod": {
"GA": {
"Pers": {
"Movment": {
"Vie": {
"Inprogress": {
"Actions": [
"Clear",
"Add"
]
}
}
}
}
}
}
}
}
}

编辑:为了存档的目的，我为这类问题添加了一个递归解决方案，使其更通用:

import pandas as pd
import json
data = {
'alt' : ["BeattheBeachmark NEW", "BeattheBeachmark NEW"],
'Mod' : ["GA", "GA"],
'Pers' : ["Movment", "Movment"],
'Vie' : ["Inprogress", "Inprogress"],
'Actions' : ["Clear", "Add"]
}
df_in = pd.DataFrame(data)
output_dict = dict()
def extract_columns(df, col, output_dict):
if col == len(df.columns)-1:
output_dict[df.columns[col]] = list(df[df.columns[col]])
else:
output_dict[df.columns[col]] = dict()
for first_col_grp in df.groupby(df.columns[col]):
output_dict[df.columns[col]][first_col_grp[0]] = dict()
extract_columns(first_col_grp[1], col+1, output_dict[df.columns[col]][first_col_grp[0]])

extract_columns(df_in, 0, output_dict)
print(json.dumps(output_dict, indent=4))

Answer 2

要获得与示例中相同的字典，可以遍历数据框架的列并创建字典(使用文字求值来帮助)。To_json返回一个字符串，你想要一个列表):

import ast
your_dict = {}
for col in df.columns:
your_dict[col] = df[col].to_json(orient='records')
your_dict[col] = ast.literal_eval(your_dict[col])
print(your_dict)

给你:

{'alt': ['BeattheBeachmark NEW', 'BeattheBeachmark NEW'],
'Mod': ['GA', 'GA'],
'Pers': ['Movment', 'Movment'],
'Vie': ['Inprogress', 'Inprogress'],
'Actions': ['Clear', 'Add']}