import pandas as pd
import pymongo
import urllib
from bson.objectid import ObjectId
from bson.son import SON

password = "FILL_ME"

url = 'mongodb://taxi:' + urllib.quote_plus(password) + '@olive083.dakao.io:27017/driver_record'
client = pymongo.MongoClient(url)
db = client['driver_record']

cursor = db.hour_drive_info.aggregate([
  {'$match': {'day': "20160922"}},
  {'$group': {
    '_id': "$taxi_id",
    'raw_count': {'$sum' : "$raw_count"},
    'raw_length': {'$sum' : "$raw_length"},
    'time_drive': {'$sum' : "$time_drive"},
    'time_pickup': {'$sum' : "$time_pickup"},
    'time_break': {'$sum' : "$time_break"},
    'time_wait': {'$sum' : "$time_wait"},
  }}
])

driveInfoDF = pd.DataFrame(list(cursor))



import psycopg2
conn = psycopg2.connect("dbname='daum' user='taxi' host='taxi.gp.daumkakao.io' password='FILL_ME'")
cur = conn.cursor()
cur.execute("select driver_id, id from taxi.taxis")
res = cur.fetchall()
taxiDriverIdMapping = pd.DataFrame(res, columns = list(zip(*cur.description))[0])


df = pd.merge(driveInfoDF, taxiDriverIdMapping, left_on='_id', right_on='id')

total_driver = len(df['driver_id'].value_counts())

df['time_drive'].sum()/ total_driver
df['time_pickup'].sum()/ total_driver
df['time_break'].sum()/ total_driver
df['time_wait'].sum()/ total_driver

(df['time_drive'].sum() + df['time_pickup'].sum() + df['time_break'].sum() + df['time_wait'].sum()) / total_driver