上海市地铁刷卡数据到OD矩阵

上海市地铁刷卡数据到OD矩阵

前言

上海市地铁刷卡数据到OD矩阵
上期 ,定义出早高峰和晚高峰时段,接下来就是要分早高峰和晚高峰来做出上海市地铁刷卡人次OD矩阵(origin-destination matrix),因为上海轨道交通具有很好的连通性,所以我们可以利用抽象的矩阵理论来分析。这样整个上海市轨道交通的通勤OD情况就能够通过一个矩阵来表示和研究,矩阵中的元用来刻画各站点的客流来源和去向。

思路

首先要定义出地铁OD矩阵和矩阵中的元,定义
A = ( a i j ) A=(a_{ij}) A=(aij​)
为上海市地铁OD矩阵,矩阵中的元 a i j a_{ij} aij​定义为同一时段从第 j j j个站到第 i i i个站的刷卡人次,比如令 i i i为人民广场站, j j j为富锦路,此时 a i j a_{ij} aij​就表示相同时段内从富锦路进站,从人民广场出站的那波人,令 j j j取不同时值时候,同样也可以知道其他站点到人民广场站的刷卡人次,同理也可以令 i i i为其他站点,这样令 i i i和 j j j取不同的值时,就知道不同站点进出和特定流向情况,现在的问题是如何度量 a i j a_{ij} aij​大小。这里给出集合论方法,令
A = { 卡 号 : j 进 站 的 卡 号 } A=\{卡号: j进站的卡号\} A={卡号:j进站的卡号}
B = { 卡 号 : i 出 站 的 卡 号 } B=\{卡号:i出站的卡号\} B={卡号:i出站的卡号}
因为一个卡号对应一个人,集合 A A A表示从 j j j站进来的那波人次,集合 B B B表示从 i i i站出来的那波人次,如果两拨人次重复的,那么重复的那小波人次就是第 j j j个站到第 i i i个站的刷卡人次 则,此时集合 A A A和集合 B B B的交集 A ∩ B A\cap B A∩B 表示从 j j j 进站的且从 i i i 出站的卡号, 即交集 A ∩ B A\cap B A∩B的势便是 a i j a_{ij} aij​的取值。

结果预览

上海市地铁刷卡数据到OD矩阵

代码
# -*- coding: utf-8 -*-
"""
project_name:read_mysql
@author: 帅帅de三叔
Created on Thu Dec 12 15:10:32 2019
"""
import numpy as np #导入数值分析模块
import pandas as pd #导入数据分析模块
from sqlalchemy import create_engine #数据库引擎
connection=create_engine("mysql+pymysql://root:123456@localhost:3306/metro_sh?charset=utf8") #连接数据库
sql=pd.read_sql('zaogaofeng',connection) #读取sql数据库
df=pd.DataFrame(sql) #数据框化
#print(df.head()) #测试表头前5
stations=df['站点'].unique() #所有去重的站点
M=np.zeros(shape=(313,313)) #构造一个313*313零矩阵

for index1,i in enumerate(stations): #行
    for index2,j in enumerate(stations): #列
        card_out_i=df[(df['站点']==i)&(df['费用']!=0)]['卡号'] #第i站出站卡号序列
        card_in_j=df[(df['站点']==j)&(df['费用']==0)]['卡号'] #第j站进站卡号
        print(len(card_out_i),len(card_in_j)) #测试j进站,i出站的卡号
        ai=set(card_out_i) #出站列表集合化
        aj=set(card_in_j)  #进站列表集合化
        aij=ai.intersection(aj) #求交集,即从j进站,i出站的卡号
        print(len(aij)) #交集计数
        count=len(aij) 
        M[index1,index2]=count #赋值               
M=pd.DataFrame(M) #数据框化
M.columns=stations #构造表头
M.index=stations #构造索引
M.to_excel("早高峰OD矩阵.xlsx") #写入excel     
代码解读

整段代码大体过程是先用sqlalchemy模块的create_engine类连接到MySQL数据库,紧接着用 pd.read_sql() 读取库里面的数据表,比如这里的zaogaofeng,数据读出来了,接下来就完全是python操作了,如提取不重复的站点,用 card_out_i=df[(df[‘站点’]==i)&(df[‘费用’]!=0)][‘卡号’] 筛出第 i i i 站出站卡号序列,用 card_in_j=df[(df[‘站点’]==j)&(df[‘费用’]==0)][‘卡号’] 筛出第 j j j 站出站卡号序列,然后用set() 函数集合化,并求交集和交集的势,并把所求的结果赋值给先定义的零矩阵M的第 i i i行第 j j j列元 a i j a_{ij} aij​,最后把重新赋值后的矩阵写入到excel便得到想要得OD矩阵。

改进思路

首先,代码运行太慢了,不管是读取数据库MySQL还是构造OD矩阵中的多两层循环还是集合筛选运算,都很费时;其次是代码写的太散,通用性不强,想打包成函数或者类。下面以晚高峰为例,利用面向对象编程和模块化思想,看看代码是不是简洁些,运行时间多少?

改进代码
# -*- coding: utf-8 -*-
"""
project_name:read_mysql
@author: 帅帅de三叔
Created on Thu Dec 12 15:10:32 2019
"""
import time 
import numpy as np #导入数值分析模块
import pandas as pd #导入数据分析模块
from sqlalchemy import create_engine #数据库引擎
connection=create_engine("mysql+pymysql://root:123456@localhost:3306/metro_sh?charset=utf8") #连接数据库
sql=pd.read_sql('wangaofeng',connection) #读取sql数据库
data=pd.DataFrame(sql) #数据框化
print(data.head()) #测试表头前5

def generate_od_matrix(df): #自定义构造OD矩阵函数
    stations=list(df['站点'].unique()) #所有去重的站点,object类型转list
    OD_Matrix=np.zeros(shape=(len(stations),len(stations))) #构造一个313*313零矩阵
    for index1,i in enumerate(stations): #行
        for index2,j in enumerate(stations): #列
            print(i,j)
            card_out_i=df[(df['站点']==i)&(df['费用']!=0)]['卡号'] #第i站出站卡号序列
            card_in_j=df[(df['站点']==j)&(df['费用']==0)]['卡号'] #第j站进站卡号
            print(len(card_out_i),len(card_in_j)) #测试j进站,i出站的卡号
            ai=set(card_out_i) #出站列表集合化
            aj=set(card_in_j)  #进站列表集合化
            aij=ai.intersection(aj) #求交集,即从j进站,i出站的卡号
            print(len(aij)) #交集计数
            count=len(aij) #统计j进站,i出站的刷卡人次
            OD_Matrix[index1,index2]=count #赋值
    M=pd.DataFrame(OD_Matrix) #数据框化
    M.columns=stations #构造表头
    M.index=stations #构造索引
    M.to_excel("晚高峰OD矩阵.xlsx") #写入excel  
    
if __name__=="__main__": #起始主函数
    start_time=time.time() #开始时间
    generate_od_matrix(data)
    end_time=time.time() #结束时间
    print("the process lasts:",end_time-start_time) #程序运行总时间   
代码解读

整个程序跑了20989秒,近6个小时,要知道这才是晚高峰80分钟的时间跨度,150万条刷卡记录,如果换成一天的就是900万条,大约35小时,关键怎么在电脑上快速读取这么大数据和处理,这就是接下来要研究的问题了。为此,从新整理了代码,写了两个函数,第一个函数 read_mysql 用来读取MySQL得到一个数据框,第二个函数 generate_od_matrix调用第一个函数的结果来生成OD矩阵,最后主函数用来保存OD矩阵到excel中,本机配置如下
上海市地铁刷卡数据到OD矩阵
程序是从周一下午4点左右开跑的,周三早上来上班,发现程序跑完了,甚是欣慰,总共花了137036.9512345791秒,大约是38小时,比计划中的多3小时,这也是可以理解的,其实读数据大约只要30分钟,大部分时间是花在遍历数据并集合化处理上面。

完整代码
# -*- coding: utf-8 -*-
"""
project_name:read_ten_million_rows_data_from_mysql
@author: 帅帅de三叔
Created on Fri Dec 20 13:19:18 2019
"""
import time #导入时间模块
import numpy as np #导入数值分析模块
import pandas as pd #导入数据分析模块
import pymysql #导入数据库连接模块 

def read_mysql(): #定义读取MySQL函数
    rows=[] #用来存放行数据
    db=pymysql.connect(host='localhost',user="root",passwd="123456",database="metro_sh",port=3306,charset='utf8',cursorclass =pymysql.cursors.SSCursor) #连接到本地MySQL数据库
    cursor=db.cursor() #获取游标
    cursor.execute("SELECT * FROM metro20160901") #筛取数据 
    while True:
        row=cursor.fetchone() #一次只取一行
        rows.append(row)
        print("正在读取第%d行"%len(rows))
        print(row)
        if not row:
            break
    cursor.close() #关闭游标
    db.close() #关闭数据库连接
    df=pd.DataFrame(rows)
    return df

def generate_od_matrix(df): #定义生成od矩阵的函数
    df.columns=["卡号","日期","时间","站点","方式","费用","是否有优惠"] #重命名表头
    stations=df['站点'].unique() #所有去重的站点 
    od_matrix=np.zeros(shape=(len(stations),len(stations))) #构造一个313*313零矩阵
    for index1,i in enumerate(stations): #行
        for index2,j in enumerate(stations): #列
            print(index1,index2)
            card_out_i=df[(df['站点']==i)&(df['费用']!=0)]['卡号'] #第i站出站卡号序列
            card_in_j=df[(df['站点']==j)&(df['费用']==0)]['卡号'] #第j站进站卡号
            print(len(card_out_i),len(card_in_j)) #测试j进站,i出站的卡号
            ai=set(card_out_i) #出站列表集合化
            aj=set(card_in_j)  #进站列表集合化
            aij=ai.intersection(aj) #求交集,即从j进站,i出站的卡号
            print(len(aij)) #交集计数
            count=len(aij) 
            od_matrix[index1,index2]=count #赋值               
    od_matrix=pd.DataFrame(od_matrix) #数据框化   
    return od_matrix,stations

if __name__=="__main__":
    start_time=time.time() #开始时间
    M,stations=generate_od_matrix(read_mysql()) #函数嵌套调用读取数据库函数
    M.columns=stations #构造表头
    M.index=stations #构造索引
    M.to_excel("上海市OD矩阵.xlsx") #写入excel  
    end_time=time.time() #开始时间
    print('程序耗时:',end_time-start_time) #测试读取数据时间

如果你不会写代码或直接只想要数据的话可以关注“三行科创”公众号,在对话框留个邮箱和所要数据名称,我发给你。

参考文献

1,https://wenku.baidu.com/view/165abf1d336c1eb91a375d8d.html
2,https://wenku.baidu.com/view/fa71f2107375a417866f8f81.html?sxts=1575956307792
3,https://wenku.baidu.com/view/5710cba20d22590102020740be1e650e52eacf23.html?rec_flag=default&sxts=1575957393812

上海市地铁刷卡数据到OD矩阵

上一篇:C语言小游戏:和电脑玩21点游戏


下一篇:插着USB mic开机时,系统没有声音问题