python - Sum 数据库的位置和时间
问题描述
我对 Python 中的 for 循环有疑问。我想根据时间和位置对这些数据求和,而不需要 pandas。此数据在 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
这是我目前所做的:
视图.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)
解决方案
您想按位置分组,因此我建议您瞄准这种更易于可视化的格式,然后尝试从那里构建表格输出(对于每个城市,每次,打印小时和人/小时)
[
{'location' : 'Liberty City', 'times': [{'hour' : '7:00', 'people' : 116}, ...]},
...
]
在使用几乎任何数据库时,请尝试为每个对象(行、表、存储桶、关系、(在此处插入数据库术语)等)创建一个类。然后,您可以在此处隔离逻辑,而不是使 main 函数混乱
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
推荐阅读
- sql-server - 如何将特定列与月份和年份与财政年度相加
- c# - 如何包含两个相同类型的关系属性?
- powershell - 为什么当我将我的 PS 脚本存储在变量中时会得到不同的结果?
- laravel - Laravel Catch 所有路线
- javascript - 无法使用 Javascript(VueJS)读取 Firestore 文档
- php - 为什么在laravel中无法识别路线?
- python - 即使元素存在,Selenium find_element 也会抛出异常
- f# - 为什么绑定到同一个变量的函数的引用相等返回false?
- ios - iOS:Mac Catalyst,在其他 Mac 上测试 iOS 应用
- swift - 接受数值类型并将它们转换为双精度的 Swift 函数