In [1]:
import sys
sys.path.append('C:\Users\Chrisi\Desktop\Studium\iv\Project\DirtyDrive')
sys.path.append('C:\Users\Chrisi\Desktop\Studium\iv\Project\DirtyDrive\DirtyDrive')
sys.path
import pandas as pd
import numpy as np
import django, os
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'DirtyDrive.settings')
#settings.configure()
django.setup()
from django.contrib.gis import gdal
gdal.HAS_GDAL
from DirtyDrive import db_saver
import datetime
from DirtyDrive.models import CityDistrict, DriveNowCarType, DriveNowCar, DriveNowChargingStation, DriveNowPetrolStation
from django.db import connection
from django.db.models import Count, Avg
from django.contrib.gis.geos import Point, Polygon
import db_controller
c = db_controller.db_controller()
In [2]:
c.get_available_dates()[0]
Out[2]:
In [3]:
# prozentual Dirt Anteil in Zeit
clean_levels = ['VERY_CLEAN', 'CLEAN', 'REGULAR', 'POOR']
#todo nochmal drüber denken
for s in clean_levels:
print(s, DriveNowCar.objects.filter(innerCleanliness=s).count()*5 /60 /350)
In [49]:
# % poor
def dictfetchall(cursor):
"Return all rows from a cursor as a dict"
columns = [col[0] for col in cursor.description]
return [
dict(zip(columns, row))
for row in cursor.fetchall()
]
from django.db import connection
def getDayRangeOriginDF(fromDate,toDate):
#count cars by citydistrict
with connection.cursor() as cursor:
cursor.execute(
'SELECT dis.id, dis.name, count(car.id) As counter \
FROM public."DirtyDrive_citydistrict" as dis, public."DirtyDrive_drivenowcar" as car \
WHERE ST_Contains(dis.polygon, car.point) AND \
car.datetime BETWEEN to_date(\''+fromDate+'\',\'YYYY-MM-DD\') AND to_date(\''+toDate+'\',\'YYYY-MM-DD\') \
GROUP BY dis.id;'
)
#res = cursor.fetchall()
df_citydistrict = pd.DataFrame(dictfetchall(cursor))
with connection.cursor() as cursor:
cursor.execute(
'SELECT dis.id, dis.name, car."innerCleanliness" as clean, count(car.id) As count \
FROM public."DirtyDrive_citydistrict" as dis, "DirtyDrive_drivenowcar" as car \
WHERE ST_Contains(dis.polygon, car.point) AND \
car.datetime BETWEEN to_date(\''+fromDate+'\',\'YYYY-MM-DD\') AND to_date(\''+toDate+'\',\'YYYY-MM-DD\') \
GROUP BY dis.id , car."innerCleanliness" \
;'
)
#res = cursor.fetchall()
df_clean_group = pd.DataFrame(dictfetchall(cursor))
#df_clean_group
df = pd.merge(df_clean_group, df_citydistrict[['id', 'counter']], on='id')
df['relative'] = df['count']/df['counter']
#df[df['clean']=='POOR']
df_to_save = df.sort_values(['relative'], ascending=False)
return df_to_save
import json
def safeDateRangeOriginDF(df, filemane):
#filemane = "origin_day1-day7.json"
dic =[]
for i, values in enumerate(df[df['clean'] == 'POOR'].values):
#print values
dic.append ({'clean':values[0], 'count': int(values[1]), 'id': int(values[2]), \
'name': values[3], 'counter': int(values[4]), \
'relative': float("%.4f" % round(values[5],4)) })
dump = json.dumps(dic)
with open(filemane, "w") as text_file:
text_file.write(dump)
return dic
def safemuenchengeojson():
filemane = "gen/muenchen.geo.json"
features = []
for cd in CityDistrict.objects.order_by("id").raw('SELECT *, ST_AsGeoJSON(polygon) FROM public."DirtyDrive_citydistrict" ORDER BY id'):
p = geojson.loads(cd.st_asgeojson)
the_id = cd.id
df = df_to_save[df_to_save['id'] == the_id]
properties={"id": cd.id, "name": cd.name}
print(properties)
f = Feature(geometry=p, properties=properties)
features.append(f)
fc = FeatureCollection(features)
dump = geojson.dumps(fc, sort_keys=False)
print("falid: ", geojson.is_valid(geojson.loads(dump))['valid'])
with open(filemane, "w") as text_file:
text_file.write(dump)
In [51]:
fromDate = '2016-12-06'
toDate = '2016-12-08'
df_to_save = getDayRangeOriginDF('2016-12-06','2016-12-08')
bb = df_to_save.sort_values('id')
bb[bb['id'] == 23]
bb
Out[51]:
In [21]:
with connection.cursor() as cursor:
cursor.execute(
'SELECT dis.id, dis.name, count(car.id) As counter \
FROM public."DirtyDrive_citydistrict" as dis, public."DirtyDrive_drivenowcar" as car \
WHERE ST_Contains(dis.polygon, car.point) AND \
car.datetime BETWEEN to_date(\''+fromDate+'\',\'YYYY-MM-DD\') AND to_date(\''+toDate+'\',\'YYYY-MM-DD\') \
GROUP BY dis.id;'
)
aa =pd.DataFrame(dictfetchall(cursor))
aa
Out[21]:
In [11]:
pd.DataFrame(safeDateRangeOriginDF(df_to_save, 'filemane'))
Out[11]:
In [13]:
# clean 2 geojson
import geojson
from geojson import FeatureCollection, Feature
#safemuenchengeojson();
for a in range(1,9):
for b in range(a+1,9):
fromDate = '2016-12-0'+str(a)
toDate = '2016-12-0'+str(b)
filename = "gen/origin_"+str(a)+"-"+str(b-1)+".json"
print filename
df_to_save = getDayRangeOriginDF(fromDate,toDate)
safeDateRangeOriginDF(df_to_save, filename)