根据位置和时间求和数据库

Question

我对Python中的for循环有问题。我想在没有熊猫的情况下，根据时间和地点对这些数据进行汇总。这些数据在MySQL数据库(MySQL工作台(中：

Time     No_of_people      Location
----------------------------------------
07:00         20           Liberty City
07:15         25           Liberty City
07:30         20           Liberty City
07:45         30           Liberty City
08:00         21           Liberty City
...
07:00         10           San Andreas
07:15         15           San Andreas
07:30         20           San Andreas
07:45         25           San Andreas
08:00         30           San Andreas

现在我希望它像：

Time     No_of_people      Location
----------------------------------------
07:00       116            Liberty City
08:00       120            Liberty City
...
07:00       100            San Andreas

这就是我目前所做的：

views.py:

def getData(request):
api = 'http://localhost:8000/api/myData/'
response = requests.get(api)
myData = response.json()
time = []
no_of_people = []
location = []    
for hourly in myData:
time.append(hourly['time'])
no_of_people.append(hourly['no_of_people'])
location.append(hourly['location'])
hour = []
for x in range(7,24):
hour.append(x)
uniqueLocation=[]
for x in location:
if x not in uniqueLocation:
uniqueLocation.append(x)
for uniqueIndex in uniqueLocation:
for x in hour:
sum =0
for index, t in enumerate(time):
x_time = t.split(":")[0]
if int(x_time) == x and uniqueIndex == location[index]:
sum += no_of_people[index]
print(str(sum))
json_obj = {
"time": time,
"no_of_people": no_of_people,
"location": location
}
return JsonResponse(data=json_obj)

Answer 1

您想按位置分组，因此我建议您以这种更容易可视化的格式为目标，然后尝试从那里构建表格输出(针对每个城市、每个时间、打印小时和人/小时(

[
{'location' : 'Liberty City', 'times': [{'hour' : '7:00', 'people' : 116}, ...]},
... 
] 

当使用几乎任何数据库时，尝试为每个对象(行、表、bucket、关系、(在此处插入数据库术语(等(创建一个类。然后，您可以在这里隔离逻辑，而不是混淆主要功能

class Location:
def __init__(self, name):
self.name = name 
self.times = list()
def __str__(self):
s = ['{}t{}t{}'.format(k, t[k], self.name) for t in self.times for k in t.keys()] 
return 'n'.join(s) 
def add_time(self, hour, people):
existing_people_for_hour = None
for t in self.times:  # loop existing times, looking for the hour 
existing_people_for_hour = t.get(hour) 
if existing_people_for_hour is not None:
t[hour] += people
break  # found the hour to update, so break the loop
if existing_people_for_hour is None:  # if the hour was never found, add to the times list 
self.times.append({hour : people}) 

有了这个，使用字典对位置值进行分组，您应该能够在最后的中打印它们

locations = dict()
for d in myData:
# parse each value out 
hour = d['time'][:2] + ':00'
p = int(d['no_of_people']) 
loc = d['location']
# get the location from the map, if exists, else create new one 
l = locations.get(loc, Location(loc))
l.add_time(hour, p)  # add the people for the time 
locations[loc] = l  # upsert the new location 
for l in locations.values():
print(l)

输出

07:00   95  Liberty City
08:00   21  Liberty City
07:00   70  San Andreas
08:00   30  San Andreas