将数据框转换为具有嵌套在groupby上的2级数据的JSON [英] Convert dataframe to JSON with 2 level nested with groupby
问题描述
我有一个小要求,我需要以JSON格式列出给定两周内的所有客户及其数量.目前,我有这种方式的数据框-
I have a small requirement where in I need to list down all customers and their amounts for a given fortnight in a JSON format.Currently, I have a dataframe this way -
FortNight Amount Customer Parameter
Apr-2FN-2018 339632.00 10992 CustomerSales
Apr-2FN-2018 27282.00 10994 CustomerSales
Apr-2FN-2018 26353.00 10995 CustomerSales
May-1FN-2018 24797.00 11000 CustomerSales
May-1FN-2018 21093.00 10990 CustomerSales
May-2FN-2018 45679.0 10992 CustomerSales
May-2FN-2018 45459.0 10995 CustomerSales
May-2FN-2018 35885.0 10994 CustomerSales
期望的JSON-
"CustomerSales" : [
{"FortNight" : "Apr-2FN-2018",
"Details" :[
{"Customer": "10992","Amount" : 339632.00},
{"Customer": "10994","Amount" : 27282.00},
{"Customer": "10995","Amount" : 26353.00}
]
},
{"FortNight" : "May-1FN-2018",
"Details" :[
{"Customer": "11000","Amount" : 24797.00},
{"Customer": "10990","Amount" : 21093.00}
]
},
{"FortNight" : "May-2FN-2018",
"Details" :[
{"Customer": "10992","Amount" : 45679.00},
{"Customer": "10995","Amount" : 45459.00},
{"Customer": "10994","Amount" : 35885.00}
]
}
]
我尝试了- 将数据帧转换为具有2级嵌套数组的JSON
但是我仍然停留在另一个层次上.
But I am still stuck at another level more.
我也尝试了其他方法,但徒劳无功.欢迎任何帮助.在此先感谢!! ...
I tried other ways too, but in vain. Any help is welcome. Thanks in advance!!...
推荐答案
您可以使用CustomerSales
字典(键等于FortNight
值)来简化此操作,而不用使用"FortNight"字符串作为每个词典中的键.给定指定的要求,以下应该起作用.您可以遍历DataFrame并构建字典.然后,第二组代码将该字典转换为您指定的格式.
You could make this easier by using a dictionary of CustomerSales
where the key is equal to the FortNight
value instead of having a list of dictionaries using a "FortNight" string as a key in each dictionary. Given the requirements specified the following should work. You can loop through the DataFrame and build out a dictionary. Then the second set of code converts that dictionary into the format you specified.
sales = {}
for idx, row in df.iterrows():
fn = row["FortNight"]
fn_sales = sales.get(fn)
if fn_sales is None:
sales[fn] = {
"Details": []
}
fn_sales = sales.get(fn)
fn_sales["Details"].append({
"Customer": row["Customer"],
"Amount": row["Amount"]
}
)
sales_obj = {"CustomerSales": []}
for fn, details in sales.items():
sales_obj["CustomerSales"].append({
"FortNite": fn,
"Details": details["Details"]
})
这篇关于将数据框转换为具有嵌套在groupby上的2级数据的JSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!