数据治理--《道路运输行政处罚立案信息》基于时间维度切割及类别维度的数据治理和分析
《道路运输行政处罚立案信息》在http://opendata.chinadep.com上有两组数据：文件分别是‘2017年案件信息新’和‘2018年案件信息’，共21871条记录。
以下本人展示如何对这部分数据进行1.质量把控。2.维度加工。3.频次分析。
第一个环节：数据入库。
将两组数据进行数据编码调整之后导入数据库。（工具使用，见图1）
入库之后进入第二个环节：数据规范和数据清洗。
第一步：将2017年和2018年的数据合并成一张表。（代码行数：5）
第二步：优化数据质量。
（1）在将表放入数据仓库进行数据拆装之前，先探查一下这张表的数据质量情况，使用数据探查工具查看其列模式，出现四种模式的数据，其中一种模式中，出现‘上海市交通委员会执法总队,2018/9/10 14:28’格式的时间数据，其时间字段的数据中就出现了不符合规范格式的时间字段，这样的脏数据有181条，在这样的话 [处罚日期] 就无法对时间日期进行计算和切割了，而以时间为数据为粒子的分析需要。（工具使用，见图2）
（2）准备一张新表，对数据质量进行加工。（代码行数：1）
（3）将处罚日期字段类型改为nvarchar(50) not null。（代码行数：1）
(4) 将‘上海市交通委员会执法总队，’替换成空字符。（代码行数：1）
第三步：转换数据格式。
接下去将[处罚日期]从字符串类型转换成日期类型再将数据放入一张新表中。（代码行数：1）
第四步，配置数据唯一性。
（1）将[案件案号]字段类型改为nvarchar(50) not null，为配置成主键做准备。（代码行数：1）
（2）去除主键字段中重复数据。（代码行数：3）（案件案号字段中有66条重复记录）
（3）配置[案件案号]为主键。（代码行数：1）
样我们就经历了四道数据初步加工 。第一道工序将两年的数据合并起来（确保数据完整）；
第二道工序修正有问题的数据，同时保证关键字段的数据质量（确保数据质量）；第三道工序，将时间字段转换成某种时间格式（确保数据可以计算性）；第四道工序，将[案件案号]字段设置为主键（确保主键数据的唯一性）；注：这里选择案件案号作为主键，案件案号具有唯一性，决定书号在不同类型的案件中可能会存在重复，因此今后可配置为外键。通过四道工序的加工，我们可以将数据放入仓库，它目前是一组数据拆装的'可加工数据',具备放入数据仓库的前提条件。
至此，我已经对原始数据进行了一次清洗，去除重复记录66条，修正不符合时间规范的数据181条。在21805条记录中一共清洗有质量问题的数据247条。这些数据给“规范数据唯一性”，“分析数据时间维度”带来了障碍，目前这些脏数据已经清洗了。
将21805条记录作为进入数据加工仓库的可加工数据，准备放入数据加工仓库。（数据规范和清洗过程见图3）
第三个环节：数据加工。
使用工具将规范后的数据放入数据加工仓库，数据加工仓库就是一个介于数据分析层和基础数据层之间的中间库，用于加工数据，加工后的数据是可以有分析价值的。
将数据导入数据加工仓库（见图4）。
基于时间维度的分析：切割时间维度。
第一步：添加派生列。
(1)添加年月日字串。（代码行数：1）
(2)添加年份维度。（代码行数：1）
(3)添加季度维度。 （代码行数：1）
(4)添加月份维度。（代码行数：1）
(5)添加日期维度。（代码行数：1）
第二步：添加数据提取列（每周日期和时分数据不具有持久性，无法用派生列计算）（代码行数：26）
经过时间维度分割后的数据（见图5）。经过时间维度切割的已加工数据，是符合进入数据分析层条件的数据，可以进入数据分析环节。
第四个环节：数据分析。
将加工后的数据放入数据分析工具后算出分析结果。
第一：从[被处罚主体]角度分析排名前两位的公司在季度、每日整点数、每周日期、日期、月份5个时间维度上的特征。（见图6）
违法行为排名前12的公司，违法行为数量占比。
/*
大众交通（集团）股份有限公司 728 0.0333868378812199
上海华晨汽车租赁有限公司 657 0.0301307039669801
上海巴士第三公共交通有限公司 373 0.0171061683100206
上海巴士第五公共交通有限公司 315 0.014446227929374
上海巴士第一公共交通有限公司 313 0.0143545058472827
上海神州华东汽车租赁有限公司 239 0.010960788809906
上海白玉兰高速客运有限公司 236 0.0108232056867691
上海海博出租汽车有限公司 229 0.0105021783994497
上海巴士第四公共交通有限公司 222 0.0101811511121302
赫兹汽车租赁（上海）有限公司 198 0.00908048612703508
上海强生出租汽车有限公司 159 0.00729190552625545
上海巴士第二公共交通有限公司 158 0.00724604448520981
*/
1.大众交通（集团）股份有限公司：
（1）每日9:00至11:00是违法行为高发时段，上午10:00是违法行为峰值点，下午是13:00至16:00,14:00至15:00是违法行为峰值点。
（2）每周六、周日是违法行为相对极少数的日期。
2.上海华晨汽车租赁有限公司
（1）每日下午13:00至16:00是违法高发时段。
（2）每周四是违法行为高发时段。
（3）每年第二季度及5月6月是违法行为高发期。
对以上两家违法大户公司的管理策略可以参考以上时间段进行重点管理和违法行为管理漏洞的研究。
第二：从[违章事实]角度分析排名第一位的违法行为在季度、每日整点数、每周日期、日期、月份5个时间维度上的特征。
排名前6位的违法行为，违法数据量占比。
/*
从事非法客运活动第一次被查获 4580 0.210043567988993
不按批准的客运站点停靠 1566 0.0718183902774593
未持有效的包车客运标志牌进行经营 1548 0.0709928915386379
驾驶员无从业资格证件驾驶出租汽车 1334 0.0611786287548727
将客运服务车辆交于无准营证的人员驾驶 1242 0.0569594129786746
使用无道路运输证的车辆参加货物运输 1096 0.0502637009860124
*/
1.从事非法客运活动第一次被查获
（1）周二、四是'从事非法客运活动'的高发日期
（2）每日14:00至16:00是高发时段
（3）12月份此类违法行为极少
2.不按批准的客运站点停靠
(1)每周六、周日此违法行为极少
(2)每日9:00至11:00是违法行为高发时段，上午10:00是违法行为峰值点，下午是13:00至16:00,14:00至15:00是违法行为峰值点。
（3）12月份此类违法行为极少。
3．未持有效的包车客运标志牌进行经营
（1）每日9:00至12:00是违法行为高发时段，上午10:00是违法行为峰值点，下午是13:00至16:00,14:00至15:00是违法行为峰值点。
（2）每周六、周日此类违法行为极少。
（3）12月份此类违法行为极少。
4．驾驶员无从业资格证件驾驶出租汽车
(1)每日9:00至11:00是违法行为高发时段，上午10:00是违法行为峰值点，下午是13:00至16:00,14:00至15:00是违法行为峰值点。
(2)每周六、周日此类违法行为极少。
(3)12月份此类违法行为极少。
5.将客运服务车辆交于无准营证的人员驾驶
(1)每日9:00至11:00是违法行为高发时段，上午10:00是违法行为峰值点，下午是13:00至15:00,14:00至15:00是违法行为峰值点。
(2)每周六、周日此类违法行为极少。
(3)12月份此类违法行为极少。
6．使用无道路运输证的车辆参加货物运输
（1）每日9:00至12:00是违法行为高发时段，上午10:00是违法行为峰值点，下午是13:00至16:00,14:00至15:00是违法行为峰值点。
（2）每周六、周日无违法，每周五较少。
（3）12月份此类违法行为较少。
建议对以上6类违法行为按照时间段进行管理，并且对管理漏洞进行排查。
第三：不同违法行为对应不同违法主体所占比例
1．从事非法客运活动第一次被查获
/*
上海华晨汽车租赁有限公司 588 0.128384279475983
上海神州华东汽车租赁有限公司 177 0.038646288209607
赫兹汽车租赁（上海）有限公司 164 0.0358078602620087
上海庞博汽车租赁有限公司 92 0.0200873362445415
北京神州汽车租赁有限公司上海虹口分公司 84 0.0183406113537118
北京神州汽车租赁有限公司上海虹桥机场分公司 75 0.0163755458515284
上海一嗨汽车租赁有限公司 69 0.0150655021834061
北京神州汽车租赁有限公司上海分公司 68 0.0148471615720524
上海首汽汽车租赁有限责任公司 43 0.00938864628820961
首汽租赁有限责任公司上海分公司 34 0.0074235807860262
上海骁泓汽车租赁有限公司 18 0.00393013100436681
上海妙玛汽车租赁有限公司 13 0.00283842794759825
上海晋腾汽车租赁服务有限公司 12 0.00262008733624454
上海伯乐汽车租赁有限公司 6 0.00131004366812227
上海易檀汽车租赁有限公司 5 0.00109170305676856
王伟 5 0.00109170305676856
*/
上海华晨汽车租赁有限公司从事非法客运活动违法行为占10650家违法主体21805起违法行为的12%。
2．不按批准的客运站点停靠
/*
上海白玉兰高速客运有限公司 172 0.109833971902937
上海新世纪运输有限公司 107 0.0683269476372925
上海新大都客运有限公司 60 0.0383141762452107
上海沪铁富康长途客运有限公司 59 0.0376756066411239
上海芷新客运有限公司 54 0.0344827586206897
上海中山交通客运有限公司 49 0.0312899106002554
上海芷新太平洋客运服务有限公司 46 0.0293742017879949
*/
上海白玉兰高速客运有限公司’和‘上海新世纪运输有限公司’不按批准的客运站点停靠所占比例较高。
3．未持有效的包车客运标志牌进行经营
/*
上海顺风旅行社有限公司 64 0.041343669250646
上海龙泽汽车服务有限公司 55 0.0355297157622739
上海白玉兰国际旅行社有限公司 48 0.0310077519379845
上海友恒客运有限公司 44 0.0284237726098191
上海蓝鲸客运有限公司 42 0.0271317829457364
*/
以上5家公司‘未持有效的包车客运标志牌进行经营’违法行为管理漏洞比较严重。
4.驾驶员无从业资格证件驾驶出租汽车
/*
刘杰 5 0.00374812593703148
*/
刘杰被处罚5次，被处罚次数最多.
5.将客运服务车辆交于无准营证的人员驾驶
/*
大众交通（集团）股份有限公司 682 0.549114331723027
上海海博出租汽车有限公司 211 0.169887278582931
上海强生出租汽车有限公司 126 0.101449275362319
*/
--大众交通（集团）股份有限公司'将客运服务车辆交于无准营证的人员驾驶'的违法存在管理漏洞。
6．使用无道路运输证的车辆参加货物运输
/*
上海钧剑汽车销售有限公司 9 0.00821167883211679
上海更茂机电设备经营部 8 0.0072992700729927
*/
--以上两家公司” 使用无道路运输证的车辆参加货物运输
“的违法行为发生频率较高。
对以上一些公司及个人存在违法管理漏洞进新排查，及时弥补管理漏洞。
分析报告总结：根据2017年和2018年违法管理的数据在时间维度和类别频次上的分析，通过数据‘上海市交通委员会执法总队’可以于2019年可以参考以上数据分析结果，对部分企业及其违法时间段着重管理并寻找漏洞；对部分违法行为及其时间段着重管理并寻找漏洞；对部分企业及个人在部分违法行为上的管理漏洞寻找原因。比如说：上海华晨汽车租赁有限公司为什么选择每周四违法行为特别多。又比如说：从事非法客运活动，这类违法行为为什么周二和周四特别多。这样就是让数据分析结论来指引工作，可以将每两年的数据分析结论作为后一年工作的指引方案。从时间、违法分类、企业、个人等多个维度制定有针对性的新管理措施，并且可以在面上可提前预防大量的违法行为和排查出部分管理漏洞。
| 
发表于 2019-9-20 14:04:07
