上海市地铁刷卡数据到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的取值。
结果预览
代码
# -*- 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中，本机配置如下
程序是从周一下午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
