在Python中聚合JSON值

Question

我想知道是否可以在python中聚合新的值。在

例如，一个JSON值如下所示：

{"time": {"Friday": {"20:00": 2, "19:00": 1, "22:00": 10, "21:00": 5, 
          "23:00": 14, "0:00": 2, "18:00": 2}, "Thursday": {"23:00": 1, 
          "0:00": 1, "19:00": 1, "18:00": 1, "16:00": 2, "22:00": 2},
          "Wednesday": {"17:00": 2, "23:00": 3, "16:00": 1, "22:00": 1, 
          "19:00": 1, "21:00": 1}, "Sunday": {"16:00": 2, "17:00": 2, "19:00": 1, 
          "22:00": 4, "21:00": 4, "0:00": 3, "1:00": 2}, "Saturday": 
          {"21:00": 4, "20:00": 3, "23:00": 10, "22:00": 7, "18:00": 
          1, "15:00": 2, "16:00": 1, "17:00": 1, "0:00": 8, "1:00": 
          1}, "Tuesday": {"19:00": 1, "17:00": 1, "1:00": 2, "21:00": 
          1, "23:00": 3}, "Monday": {"18:00": 2, "23:00": 1, "22:00": 2}}

我想把它汇总起来，根据它开放的时间分为四类。在

这四类是：

早上6点到中午12点：早上

中午12点-下午5点：下午

下午5点至11点：晚上

晚上11点-早上6点：晚上

例如：

如果这是当前值：

^{pr2}$

则输出应为：

"Friday": {"morning": 0, "afternoon": 0, "evening": 15, "night": 0}

因此输出应该是

"Day": {"morning": count, "afternoon": count, "evening": count, "night":count}

对于数百个JSON值。在

我的想法是我可以创建4个代表每个时区的箱子。然后我将使用两个for循环来遍历每一天的值。如果值在bucket的范围内，我会把它加到计数中。然后我将把这一天存储在一个字典中，其值也是一个字典。内部字典由四个时区组成，以count为值。然后我会把这一天还给你，然后每天重新开始。在

到目前为止我还需要实现什么函数。在

import json
from datetime import datetime

def cleanStr4SQL(s):
    return s.replace("'","`").replace("\n"," ")

def parseCheckinData():
    #write code to parse yelp_checkin.JSON
    with open('yelp_checkin.JSON') as f:
        outfile = open('checkin.txt', 'w')
        line = f.readline()
        count_line = 0
        while line:
            data = json.loads(line)
            outfile.write(cleanStr4SQL(str(data['business_id'])) + '\t')
            outfile.write(aggregate(cleanStr4SQL(str(data['time']))))

            line = f.readline()
            count_line+=1
    print(count_line)
    outfile.close()
    f.close()

def aggregate(line):
    morning = []
    afternoon = []
    evening = []
    night = []
    for l in line:
        print(l)

我想知道用python解决这个问题的最佳方法是什么。在

如有任何建议，我们将不胜感激。我知道没有代码，但如果有人能给我指明方向那就太好了。在

谢谢你的阅读

Answer 1

这里有一种可能的方法。我只尝试了一个json字符串，因此您可能需要扩展它来处理多次出现的情况。在

import json
import pandas as pd

jsontxt = '{"time": {"Friday": {"20:00": 2, "19:00": 1, "22:00": 10, "21:00": 5, "23:00": 14, "0:00": 2, "18:00": 2}, "Thursday": {"23:00": 1, "0:00": 1, "19:00": 1, "18:00": 1, "16:00": 2, "22:00": 2}, "Wednesday": {"17:00": 2, "23:00": 3, "16:00": 1, "22:00": 1, "19:00": 1, "21:00": 1}, "Sunday": {"16:00": 2, "17:00": 2, "19:00": 1, "22:00": 4, "21:00": 4, "0:00": 3, "1:00": 2}, "Saturday": {"21:00": 4, "20:00": 3, "23:00": 10, "22:00": 7, "18:00": 1, "15:00": 2, "16:00": 1, "17:00": 1, "0:00": 8, "1:00": 1}, "Tuesday": {"19:00": 1, "17:00": 1, "1:00": 2, "21:00": 1, "23:00": 3}, "Monday": {"18:00": 2, "23:00": 1, "22:00": 2}}}'

# Parse the json and convert to a dictionary object
jsondict = json.loads(jsontxt)

# Convert the "time" element in the dictionary to a pandas DataFrame
df = pd.DataFrame(jsondict['time'])

# Define a function to convert the time slots to the categories
def cat(time_slot):
    if '06:00' <= time_slot < '12:00':
        return 'Morning'
    elif '12:00' <= time_slot < '17:00':
        return 'Afternoon'
    elif '17:00' <= time_slot < '23:00':
        return 'Evening'
    else:
        return 'Night'

# Add a new column "Time" to the DataFrame and set the values after left padding the values in the index
df['Time'] = df.index.str.rjust(5,'0')

# Add a new column "Category" and the set the values based on the time slot
df['Category'] = df['Time'].apply(cat)

# Create a pivot table based on the "Category" column
pt = df.pivot_table(index='Category', aggfunc=sum, fill_value=0)

# Convert the pivot table to a dictionary to get the json output you want
jsonoutput = pt.to_dict()
print(jsonoutput)

希望有帮助