常用python代码合集

1.数据处理

1.1连接mysql数据库并读取数据成dataframe格式

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import pymysql
import seaborn as sns; sns.set()
import copy
import math
import matplotlib.ticker as ticker
from matplotlib.pyplot import MultipleLocator
import warnings
from sqlalchemy import create_engine
warnings.filterwarnings('ignore')

# 连接mysql数据库
try:
    conn = pymysql.connect(host='localhost', user='your username', password='your password', db='your database', charset='utf8')
    print('数据库连接成功.')
except:
    print('数据库连接失败.')
conn.ping(reconnect=True)
cur = conn.cursor()
sql = '''
    select 收费站,桩号,车牌,车型,日期,时间 from `your table` limit 100
    '''
cur.execute(sql)
conn.commit()
df = cur.fetchall()
df = pd.DataFrame(df)
df.rename(columns = {0:'收费站',1:'桩号',2:'车牌',3:'车型',4:'日期',5:'时间'} , inplace = True)
cur.close()
conn.close()
df.head(2)

1.2基于轨迹数据得到相邻两次被检测时间间隔

JHexit这个dataframe如下所示，本代码得到同一辆车相邻两次被检测时间。

JHexit.sort_values(['车牌','时间'],ascending=True,inplace=True)
JHexit.reset_index(drop=True,inplace=True)
tmp = JHexit.shift(-1)
tmp.rename(columns={'收费站':'D收费站','桩号':'D桩号','车牌':'D车牌','时间':'D时间','tag':'Dtag'},inplace=True)
JHexit = JHexit.join(tmp[['D收费站','D桩号','D车牌','D时间','Dtag']])
JHexit.dropna(inplace=True)

2.画图

2.1plt绘制折线图

数据如下面这个dataframe所示，这个dataframe也就是下面代码中的JHexit。

tmp = JHexit[JHexit['收费站'] == '河北孟姜站'].groupby(['日期'])[['车牌']].count().reset_index()
tmp.rename(columns={'车牌':'num'},inplace=True)
# 设置 x 轴显示密度
fig, ax = plt.subplots(1, 1)
tick_spacing = 7
ax.xaxis.set_major_locator(ticker.MultipleLocator(tick_spacing))
plt.rc('font',family='AR PL UMing CN')
tmp.sort_values(['日期'],ascending=True,inplace=True)
plt.plot(tmp['日期'] , tmp['num'] ,  linewidth=1.0)
plt.xlabel("日期")
plt.ylabel('各类型车辆日交通总量')
plt.title('入口各车型日交通总量')
plt.legend(prop={'size':5})
plt.xticks(rotation=90) # 旋转90度
plt.show()

绘制结果如下：

2.2plt绘制分组柱状图

下面的代码绘制分组柱状图，JHexit这个dataframe和上面的JHexit是一样的，tmp这个dataframe如下图所示，这段代码是绘制出某些日期（2020.8.7-8.10）经过一些断面（‘河北北戴河站’,'河北秦皇岛东站’等）的出现数量前10的省份及其他省份的车辆数。

for date in ['2020-08-07','2020-08-08','2020-08-09','2020-08-10']:
    conn.ping(reconnect=True)
    cur = conn.cursor()
    sql = '''
        select 收费站,桩号,车牌,车型,日期,时间 from `your data` 
        where 日期 = \'{0}\'
        '''.format(date)
    cur.execute(sql)
    conn.commit()
    JHexit = cur.fetchall()
    JHexit = pd.DataFrame(JHexit)
    JHexit.rename(columns = {0:'收费站',1:'桩号',2:'车牌',3:'车型',4:'日期',5:'时间'} , inplace = True)
    JHexit['prov'] = JHexit[['车牌']].applymap(lambda x:x[0])
    JHexit['province'] = JHexit['prov'].apply(lambda x: 1 if '\u4e00' <= x <= '\u9fa5' else 0 ) # 只保留以中文开始的车牌
    JHexit = JHexit[JHexit['province'] == 1].reset_index()
    JHexit.drop(columns=['province'],inplace=True)
    cur.close()
    # 绘制指定截面和站点的车牌构成分组柱状图
    sites = ['河北北戴河站','河北秦皇岛东站','河北香河北站','河北香河东站','河北唐山北站']  # 需要分析截面或站点
    df = pd.DataFrame(columns=['prov'])
    for i in range(len(sites)):
        tmp = JHexit[JHexit['收费站'] == sites[i]].groupby(['prov'])[['车牌']].count().reset_index()
        tmp.rename(columns={'车牌':sites[i]},inplace=True)
        df = pd.merge(df , tmp,how='outer',on='prov')
    df.fillna(0,inplace=True)
    df.sort_values([sites[0]],ascending=False,inplace=True)
    df.reset_index(drop=True,inplace=True)
    tmp = df.iloc[0:10,:]
    tmp2 = ['其他']
    for i in range(len(sites)):
        tmp2.append(sum(df.iloc[10:,i+1]))
    tmp.loc[tmp.index.max() + 1] = tmp2

    #绘图  
    tmp.plot(kind='bar',width = 0.6)
    plt.xlabel("省份")
    plt.ylabel('交通总量')
    plt.title(date+"若干重要站点出口车牌分布")
    plt.legend(prop={'size':5})
    # plt.xticks(rotation=90) # 旋转90度
    x = range(0, len(tmp), 1)
    plt.xticks(x, tmp['prov'],rotation=0)
    plt.show()

结果如下图所示：

2.3 绘制累积曲线图

AllTab这个dataframe如下图所示，下面的代码块是绘制出ft1-4和lt1-4的累积曲线图。

plt.rcParams['figure.dpi'] = 120 #分辨率
font = {'family' : 'Times New Roman',
       'weight' : 'normal',
       'size'   : '8'}
plt.rc('font', **font)  # pass in the font dict as kwargs
plt.rc('axes',unicode_minus=False)
fig = plt.figure(figsize=(8,4))
ax1 = fig.add_subplot(1,1,1)
x_major_locator=MultipleLocator(2)
ax1.xaxis.set_major_locator(x_major_locator)
sns.kdeplot(AllTab['ft1'],cumulative=True , ax = ax1 , color='green')
sns.kdeplot(AllTab['ft2'],cumulative=True , ax = ax1 , color = 'red')
sns.kdeplot(AllTab['ft3'],cumulative=True , ax = ax1 , color = 'black')
sns.kdeplot(AllTab['ft4'],cumulative=True , ax = ax1 , color = 'orange')
sns.kdeplot(AllTab['lt1'],cumulative=True , ax = ax1, linestyle='dashed',color='green')
sns.kdeplot(AllTab['lt2'],cumulative=True , ax = ax1, linestyle='dashed' , color = 'red')
sns.kdeplot(AllTab['lt3'],cumulative=True , ax = ax1, linestyle='dashed', color = 'black')
sns.kdeplot(AllTab['lt4'],cumulative=True , ax = ax1, linestyle='dashed', color = 'orange')
ax1.set_xlabel("First and last detected time of each stage")
ax1.set_ylabel("Cumulative probability")
plt.xlim(0,24)
ax1.legend(['First detected time in the first stage','First detected time in the second stage',
            'First detected time in the third stage','First detected time in the fourth stage',
           'Last detected time in the first stage','Last detected time in the second stage',
            'last detected time in the third stage','Last detected time in the fourth stage'])
plt.show()

结果如下所示：