司机出车分层

最新推荐文章于 2024-09-11 11:42:24 发布

qq_43078871

最新推荐文章于 2024-09-11 11:42:24 发布

阅读量50

点赞数

文章标签： python 算法机器学习

本文链接：https://blog.csdn.net/qq_43078871/article/details/132624229

版权

司机时间切片（144*10分钟 = 24小时）

select
city_id,driver_id
,sum(case when gap= 0   then rate_online_time else 0    end) as m1
,sum(case when gap= 1   then rate_online_time else 0    end) as m2
,sum(case when gap= 2   then rate_online_time else 0    end) as m3
,sum(case when gap= 3   then rate_online_time else 0    end) as m4
,sum(case when gap= 4   then rate_online_time else 0    end) as m5
,sum(case when gap= 5   then rate_online_time else 0    end) as m6
,sum(case when gap= 6   then rate_online_time else 0    end) as m7
,sum(case when gap= 7   then rate_online_time else 0    end) as m8
,sum(case when gap= 8   then rate_online_time else 0    end) as m9
,sum(case when gap= 9   then rate_online_time else 0    end) as m10
,sum(case when gap= 10  then rate_online_time else 0    end) as m11
,sum(case when gap= 11  then rate_online_time else 0    end) as m12
,sum(case when gap= 12  then rate_online_time else 0    end) as m13
,sum(case when gap= 13  then rate_online_time else 0    end) as m14
,sum(case when gap= 14  then rate_online_time else 0    end) as m15
,sum(case when gap= 15  then rate_online_time else 0    end) as m16
,sum(case when gap= 16  then rate_online_time else 0    end) as m17
,sum(case when gap= 17  then rate_online_time else 0    end) as m18
,sum(case when gap= 18  then rate_online_time else 0    end) as m19
,sum(case when gap= 19  then rate_online_time else 0    end) as m20
,sum(case when gap= 20  then rate_online_time else 0    end) as m21
,sum(case when gap= 21  then rate_online_time else 0    end) as m22
,sum(case when gap= 22  then rate_online_time else 0    end) as m23
,sum(case when gap= 23  then rate_online_time else 0    end) as m24
,sum(case when gap= 24  then rate_online_time else 0    end) as m25
,sum(case when gap= 25  then rate_online_time else 0    end) as m26
,sum(case when gap= 26  then rate_online_time else 0    end) as m27
,sum(case when gap= 27  then rate_online_time else 0    end) as m28
,sum(case when gap= 28  then rate_online_time else 0    end) as m29
,sum(case when gap= 29  then rate_online_time else 0    end) as m30
,sum(case when gap= 30  then rate_online_time else 0    end) as m31
,sum(case when gap= 31  then rate_online_time else 0    end) as m32
,sum(case when gap= 32  then rate_online_time else 0    end) as m33
,sum(case when gap= 33  then rate_online_time else 0    end) as m34
,sum(case when gap= 34  then rate_online_time else 0    end) as m35
,sum(case when gap= 35  then rate_online_time else 0    end) as m36
,sum(case when gap= 36  then rate_online_time else 0    end) as m37
,sum(case when gap= 37  then rate_online_time else 0    end) as m38
,sum(case when gap= 38  then rate_online_time else 0    end) as m39
,sum(case when gap= 39  then rate_online_time else 0    end) as m40
,sum(case when gap= 40  then rate_online_time else 0    end) as m41
,sum(case when gap= 41  then rate_online_time else 0    end) as m42
,sum(case when gap= 42  then rate_online_time else 0    end) as m43
,sum(case when gap= 43  then rate_online_time else 0    end) as m44
,sum(case when gap= 44  then rate_online_time else 0    end) as m45
,sum(case when gap= 45  then rate_online_time else 0    end) as m46
,sum(case when gap= 46  then rate_online_time else 0    end) as m47
,sum(case when gap= 47  then rate_online_time else 0    end) as m48
,sum(case when gap= 48  then rate_online_time else 0    end) as m49
,sum(case when gap= 49  then rate_online_time else 0    end) as m50
,sum(case when gap= 50  then rate_online_time else 0    end) as m51
,sum(case when gap= 51  then rate_online_time else 0    end) as m52
,sum(case when gap= 52  then rate_online_time else 0    end) as m53
,sum(case when gap= 53  then rate_online_time else 0    end) as m54
,sum(case when gap= 54  then rate_online_time else 0    end) as m55
,sum(case when gap= 55  then rate_online_time else 0    end) as m56
,sum(case when gap= 56  then rate_online_time else 0    end) as m57
,sum(case when gap= 57  then rate_online_time else 0    end) as m58
,sum(case when gap= 58  then rate_online_time else 0    end) as m59
,sum(case when gap= 59  then rate_online_time else 0    end) as m60
,sum(case when gap= 60  then rate_online_time else 0    end) as m61
,sum(case when gap= 61  then rate_online_time else 0    end) as m62
,sum(case when gap= 62  then rate_online_time else 0    end) as m63
,sum(case when gap= 63  then rate_online_time else 0    end) as m64
,sum(case when gap= 64  then rate_online_time else 0    end) as m65
,sum(case when gap= 65  then rate_online_time else 0    end) as m66
,sum(case when gap= 66  then rate_online_time else 0    end) as m67
,sum(case when gap= 67  then rate_online_time else 0    end) as m68
,sum(case when gap= 68  then rate_online_time else 0    end) as m69
,sum(case when gap= 69  then rate_online_time else 0    end) as m70
,sum(case when gap= 70  then rate_online_time else 0    end) as m71
,sum(case when gap= 71  then rate_online_time else 0    end) as m72
,sum(case when gap= 72  then rate_online_time else 0    end) as m73
,sum(case when gap= 73  then rate_online_time else 0    end) as m74
,sum(case when gap= 74  then rate_online_time else 0    end) as m75
,sum(case when gap= 75  then rate_online_time else 0    end) as m76
,sum(case when gap= 76  then rate_online_time else 0    end) as m77
,sum(case when gap= 77  then rate_online_time else 0    end) as m78
,sum(case when gap= 78  then rate_online_time else 0    end) as m79
,sum(case when gap= 79  then rate_online_time else 0    end) as m80
,sum(case when gap= 80  then rate_online_time else 0    end) as m81
,sum(case when gap= 81  then rate_online_time else 0    end) as m82
,sum(case when gap= 82  then rate_online_time else 0    end) as m83
,sum(case when gap= 83  then rate_online_time else 0    end) as m84
,sum(case when gap= 84  then rate_online_time else 0    end) as m85
,sum(case when gap= 85  then rate_online_time else 0    end) as m86
,sum(case when gap= 86  then rate_online_time else 0    end) as m87
,sum(case when gap= 87  then rate_online_time else 0    end) as m88
,sum(case when gap= 88  then rate_online_time else 0    end) as m89
,sum(case when gap= 89  then rate_online_time else 0    end) as m90
,sum(case when gap= 90  then rate_online_time else 0    end) as m91
,sum(case when gap= 91  then rate_online_time else 0    end) as m92
,sum(case when gap= 92  then rate_online_time else 0    end) as m93
,sum(case when gap= 93  then rate_online_time else 0    end) as m94
,sum(case when gap= 94  then rate_online_time else 0    end) as m95
,sum(case when gap= 95  then rate_online_time else 0    end) as m96
,sum(case when gap= 96  then rate_online_time else 0    end) as m97
,sum(case when gap= 97  then rate_online_time else 0    end) as m98
,sum(case when gap= 98  then rate_online_time else 0    end) as m99
,sum(case when gap= 99  then rate_online_time else 0    end) as m100
,sum(case when gap= 100 then rate_online_time else 0    end) as m101
,sum(case when gap= 101 then rate_online_time else 0    end) as m102
,sum(case when gap= 102 then rate_online_time else 0    end) as m103
,sum(case when gap= 103 then rate_online_time else 0    end) as m104
,sum(case when gap= 104 then rate_online_time else 0    end) as m105
,sum(case when gap= 105 then rate_online_time else 0    end) as m106
,sum(case when gap= 106 then rate_online_time else 0    end) as m107
,sum(case when gap= 107 then rate_online_time else 0    end) as m108
,sum(case when gap= 108 then rate_online_time else 0    end) as m109
,sum(case when gap= 109 then rate_online_time else 0    end) as m110
,sum(case when gap= 110 then rate_online_time else 0    end) as m111
,sum(case when gap= 111 then rate_online_time else 0    end) as m112
,sum(case when gap= 112 then rate_online_time else 0    end) as m113
,sum(case when gap= 113 then rate_online_time else 0    end) as m114
,sum(case when gap= 114 then rate_online_time else 0    end) as m115
,sum(case when gap= 115 then rate_online_time else 0    end) as m116
,sum(case when gap= 116 then rate_online_time else 0    end) as m117
,sum(case when gap= 117 then rate_online_time else 0    end) as m118
,sum(case when gap= 118 then rate_online_time else 0    end) as m119
,sum(case when gap= 119 then rate_online_time else 0    end) as m120
,sum(case when gap= 120 then rate_online_time else 0    end) as m121
,sum(case when gap= 121 then rate_online_time else 0    end) as m122
,sum(case when gap= 122 then rate_online_time else 0    end) as m123
,sum(case when gap= 123 then rate_online_time else 0    end) as m124
,sum(case when gap= 124 then rate_online_time else 0    end) as m125
,sum(case when gap= 125 then rate_online_time else 0    end) as m126
,sum(case when gap= 126 then rate_online_time else 0    end) as m127
,sum(case when gap= 127 then rate_online_time else 0    end) as m128
,sum(case when gap= 128 then rate_online_time else 0    end) as m129
,sum(case when gap= 129 then rate_online_time else 0    end) as m130
,sum(case when gap= 130 then rate_online_time else 0    end) as m131
,sum(case when gap= 131 then rate_online_time else 0    end) as m132
,sum(case when gap= 132 then rate_online_time else 0    end) as m133
,sum(case when gap= 133 then rate_online_time else 0    end) as m134
,sum(case when gap= 134 then rate_online_time else 0    end) as m135
,sum(case when gap= 135 then rate_online_time else 0    end) as m136
,sum(case when gap= 136 then rate_online_time else 0    end) as m137
,sum(case when gap= 137 then rate_online_time else 0    end) as m138
,sum(case when gap= 138 then rate_online_time else 0    end) as m139
,sum(case when gap= 139 then rate_online_time else 0    end) as m140
,sum(case when gap= 140 then rate_online_time else 0    end) as m141
,sum(case when gap= 141 then rate_online_time else 0    end) as m142
,sum(case when gap= 142 then rate_online_time else 0    end) as m143
,sum(case when gap= 143 then rate_online_time else 0    end) as m144
from
(select  city_id
        ,driver_id
        ,hour
        ,gap
        ,round(avg(online_min),4) as rate_online_time
from 
     (
     select   city_id
              ,current_stat_date
              ,driver_id
              ,hour
              ,floor((hour(minute)*60+minute(minute))/10) as gap
              ,count(distinct time)
              ,round(count(distinct time)/10,4) as online_min
     from 
     (
     select  
        country_code
        ,city_id
        ,weekofyear(concat_ws('-', substr(pt, 1, 4), substr(pt, 5, 2), substr(pt, 7, 2))) as week_num
        ,date_format(to_date(stat_start_hour), 'u') week_day
        ,pt
        ,case   when car_level in (1300, 20001, 2400) then 10001
                    when car_level in (1800, 21018) then 10002
                    when car_level in (21096, 21114) then 10003
             end as product_ds_id
        ,driver_id
        ,to_date(from_unixtime(unix_timestamp(time) + (2 * 3600))) as current_stat_date
        ,hour(from_unixtime(unix_timestamp(time) + (2 * 3600)))    as hour
        ,from_unixtime(unix_timestamp(time) + (2 * 3600))  as minute
        ,from_unixtime(unix_timestamp(time) + (2 * 3600))  as time
        from    drv_table
    where   pt between regexp_replace('${start_date}', '-', '') and regexp_replace('${end_date}', '-', '')
      and   country_code in ('BR', 'MX')
     )a group by city_id,driver_id,current_stat_date,hour,floor((hour(minute)*60+minute(minute))/10)
)b group by city_id ,driver_id ,hour ,gap
) c
group by city_id,driver_id;

import numpy as np
import pandas as pd
import time
from sklearn.cluster import KMeans
from scipy.spatial.distance import cdist
import matplotlib.cm as cm
import matplotlib.pyplot as plt

n_clu=4

data_raw_1 = pd.read_csv("/Users/didi/Desktop/澳洲挑单与忠诚司机收入指标分析/driver_data.csv", sep=',')
data_raw_1 = data_raw_1.fillna(0)

data_raw = data_raw_1[data_raw_1.city_id==61020600]
data_raw.tail()

city_id_list = data_raw['city_id'].unique()
for city_id in city_id_list:
    data_all = data_raw[data_raw.city_id==city_id]
    #data_all = data_raw
    x = data_all.ix[:, 2:].values
    u,s,v = np.linalg.svd(x, full_matrices=False)
    v0 = v[0, :]
    v1 = v[1, :]
    v2 = v[2, :]
    v3 = v[3, :]

    corr0 = []
    corr1 = []
    corr2 = []
    corr3 = []

    for i in range(len(x)):
        corr0.append((np.corrcoef(x[i],v0))[0][1])
        corr1.append((np.corrcoef(x[i],v1))[0][1])
        corr2.append((np.corrcoef(x[i],v2))[0][1])
        corr3.append((np.corrcoef(x[i],v3))[0][1])

    t = pd.concat([data_all['driver_id'],pd.Series(corr0,index=data_all.index),pd.Series(corr1,index=data_all.index),pd.Series(corr2,index=data_all.index),pd.Series(corr3,index=data_all.index)],axis=1)
    t.columns=['driver_id','v0','v1','v2','v3']

    x_train = t.ix[:, 1:].values
    where_are_inf = np.isinf(x_train)
    x_train[where_are_inf] = 0
    kmeans = KMeans(n_clusters=n_clu,random_state=100).fit(x_train)
    kmeans_group_out =kmeans.labels_

    data_out = pd.concat([data_all[['city_id', 'driver_id']], pd.Series(kmeans_group_out,index=data_all.index)], axis=1)
    data_out.columns = ['city_id', 'driver_id','group_id']
    data_out = data_out.sort_values(by='group_id', ascending=True)
    data_out.to_csv("/Users/didi/Desktop/澳洲挑单与忠诚司机收入指标分析/{0}_result.csv".format(str(city_id)), sep='\t', header=False, index=0)

    print(len(data_all))
    print(len(data_out))
    data_all.drop(['city_id'], axis=1, inplace=True)
    data_out.drop(['city_id'], axis=1, inplace=True)
    merge_df = pd.merge(data_out, data_all, on=['driver_id'], how='inner')
    merge_df.to_csv("/Users/didi/Desktop/澳洲挑单与忠诚司机收入指标分析/{0}_merge.csv".format(str(city_id)), index=0)

    pic_data = merge_df.ix[1:, 2:].values
    plt.figure(figsize=(12, 10))
    plt.imshow(pic_data, aspect='auto', cmap=cm.viridis)
    plt.xlim(0,143)
    plt.colorbar()
    plt.title('{0}_{1}'.format(str(city_id), n_clu))
    plt.savefig('/Users/didi/Desktop/澳洲挑单与忠诚司机收入指标分析/{0}_{1}_pic.png'.format(str(city_id), n_clu))
    
    print("{0} complete".format(str(city_id)))

data = pd.read_csv("/Users/didi/Desktop/澳洲挑单与忠诚司机收入指标分析/data.csv", sep=',')
data = data.fillna(0)

data

driver_1_high=data.loc[(data.lable==0)&(data.grab_rate>0.7)]
driver_1_low=data.loc[(data.lable==0)&(data.grab_rate<=0.7)]

from scipy import stats

data.tail()

stats.levene(driver_1_high.gmv,driver_1_low.gmv)

import matplotlib.pyplot as plt
plt.hist(driver_1_low.tph)

stats.normaltest(driver_1_high.gmv)

stats.mannwhitneyu(driver_1_high.rides_rate,driver_1_low.rides_rate)

driver_0=data.loc[(data.lable==0)]

stats.normaltest(driver_0)

stats.normaltest(data)

stats.mannwhitneyu(driver_1_high.iph,driver_1_low.iph)

stats.levene(driver_1_high.iph,driver_1_low.iph)

stats.ttest_ind(driver_1_high.gmv,driver_1_low.gmv,equal_var=False)

stats.ttest_ind(driver_1_high.iph,driver_1_low.iph)

stats.levene(driver_2_high.iph,driver_2_low.iph)

stats.ttest_ind(driver_2_high.iph,driver_2_low.iph)

stats.levene(driver_1_high.gmv,driver_1_low.gmv)

stats.ttest_ind(driver_1_high.gmv,driver_1_low.gmv,equal_var=False)

stats.ttest_ind(driver_1_high.iph,driver_1_low.iph,equal_var=False)

import numpy as np
import pandas as pd
import time
from sklearn.cluster import KMeans
from scipy.spatial.distance import cdist
import matplotlib.cm as cm
import matplotlib.pyplot as plt
data = pd.read_csv("/Users/didi/Desktop/data.csv", sep=',')

data

plt.boxplot(x=data.values,labels=data.columns,whis=1.5)
plt.show()

data.boxplot()
plt.show()