交通数据处理-计算途径某些路段的车辆数

根据车辆的运行轨迹，计算先经过某些路段，再经过某些路段的车辆数。

欢迎关注本人公众号--交通数据探索师

如下表，

其中：vehicle: 车辆编号；route: 车辆轨迹。

以第一行为例，车辆car1按顺序经过了路段123、路段456、路段789。

现统计先通过路段123再通过路段789的车辆数。

大家可以可以先思考一下怎么使用Python和SQL进行实现。

 

Python

实现思路：以car1的轨迹为例，现将route拆分成列表['123', '456', '789']，判断该列表中是否包含路段123和路段789，如果包含则再进一步判断路段123在列表中的位置是不是在路段789的前面，如果也是，则该车辆满足要求。​

import pandas as pd
import numpy as np 

data = pd.DataFrame(
    {
        'vehicle': ['car1', 'car2', 'car3', 'car4', 'car5'],
        'route': ['123+456+789', '1123+789+357', '123+456+7899', '123+456+789+548', '789+123']
    }
)

# 统计途径路段:123和789的车辆数
data = (
    data.
    eval("route=route.str.split('+')").  # 将route拆分成列表
    # 如果车辆的route包含123和789则flag列赋值为1 否则为0
    assign(flag=lambda df: df['route'].apply(lambda x: 1 if set(['123', '789']).issubset(set(x)) else 0)).
    query("flag==1").  # 将flag列为1的数据筛选出来
    # 比较123和456在列表中的位置
    assign(order_check=lambda df: df['route'].apply(lambda x: x.index('123') < x.index('789'))).
    query("order_check==True")
)
data

至此筛选出来了满足条件的车辆，可进一步处理出车辆数。​

clickhouse

select vehicle, 
            splitByChar('+', route) as route_list,  # 将route拆分成数组
            hasAll(route_list, ['123', '789']) as flag  # 判断['123', '789']是否都包含在route_list中
            indexOf(route_list, '123') as one,  # 计算123在route_list中的位置
            indexOf(route_list, '789') as two   # 计算789在route_list中的位置
from table_name
where flag = 1
and one < two