python - 我想创建一个图表矩阵，从数据库中获取天线和通道列表的数据

Question

当频道改变时水平切换，天线改变时垂直切换。

在绘制一个图后，我无法切换图，例如矩阵中的 1,1。为该行获取相同的数据，我最终得到了不想要的结果。

    i = 0
    for antenna in antennas:
        ant = antenna[0]
          for channel in channels:
            channela = channel[0]
            query_test_run = "SELECT TestRunID FROM TestRuns WHERE DutID = '%s' and Rate = '%s' and Mode = '%s' and Band = '%s' and BandWidth = '%s' and Channel = '%s' and Antenna = '%s' " %(select_dut,select_rate,select_mode,select_band,select_bw,channela,ant)
    cursor.execute(query_test_run)     
    test_run = cursor.fetchall()
    print(test_run)
    for run in test_run:
        tstrun = run[0]
        final_query = "SELECT tx.Set_Power,tx.EVM,tx.TX_Power from TXEVM as tx, TestRuns as t, DUTs as d, APModels as a WHERE d.ModelID = a.ModelID and tx.TestRunID = t.TestRunID and t.DUTID = d.DUTID and tx.TestRunID = '%s' ORDER BY d.Serial_Number, tx.Set_Power ASC" %(tstrun)
        cursor.execute(final_query)
        data = cursor.fetchall() 
         if data != []:

            print(data,antenna,channel,run)
            fig, ax = plt.subplots(nrows=1, ncols=len(channels),figsize=(30,5.5))
            df = pd.DataFrame( [[ij for ij in i] for i in data] )
            df.rename(columns={0: 'Set_Power', 1: 'EVM', 2: 'TX_Power'}, inplace=True);
            for i in range(len(channels)):

                for row in data:
                    row = ax[i]
                    channel = channels[i]
                    x = df['TX_Power']
                    y = df['EVM']
                    row.plot(x,y)

标签： pythonfor-loopmatplotlib

Answer 1

由于当前代码存在很多问题，请考虑以下重构调整，该调整会导入所有数据，然后根据相应的Antenna和Channel对数据进行子集化。具体来说，以下用途：

• 显式连接（当前的 ANSI-SQL 标准）而不是过时的、难以阅读和维护的隐式连接，其中包含逗号 inFROM子句；
• pandas.read_sql为一个SQL 查询避免多次嵌套循环游标调用；

• 参数化以避免字符串连接（使用非官方弃用的模%运算符）和标点符号（用引号括起来的字符串）。

  注意：不要将参数占位符%s与大多数 DB-API（Postgres、MySQL）使用的模运算符占位符混淆，而其他占位符使用 qmarks ?（SQLite、PyODBC），还有一些使用命名/编号（Oracle）占位符，每个都没有引号. 根据您的实际数据库进行调整。

• 分割数据帧，DataFrame.groupby()由每个天线使用，并进一步由每个通道进行绘图。

调整后的代码（未经测试，可能需要进一步调整）

import pandas as pd
from matplotlib.pyplot as plt

# ONE QUERY OF ALL NEEDED INFO (NO SPLIT OF ANTENNA AND CHANNEL)
sql = """SELECT t.Antenna, t.Channel, t.Bandwidth, t.Band,
                t.Mode, t.Rate, t.DutID,
                tx.Set_Power, tx.EVM, tx.TX_Power 
         FROM TXEVM AS tx
         INNER JOIN TestRuns AS t ON tx.TestRunID = t.TestRunID
         INNER JOIN DUTs AS d ON t.DUTID = d.DUTID
         INNER JOIN APModels AS a ON d.ModelID = a.ModelID  
         WHERE tx.TestRunID = %s
           AND t.BandWidth = %s 
           AND t.Band = %s
           AND t.Mode = %s
           AND t.Rate = %s
           AND t.DutID = %s 
         ORDER BY d.Serial_Number, tx.Set_Power
      """     
prms = [tstrun, select_bw, select_band, select_mode, select_rate, select_dut]

# BUILD DATA FRAME OUT OF SQL RESULTSET
sql_df = pd.read_sql(sql, conn, params = prms)

# SPLIT MAIN DATA FRAME BY ANTENNA
for a, ant_grp in sql_df.groupby(['Antenna']):       
   fig, ax = plt.subplots(nrows=1, ncols=len(ant_grp['Channel'].unique()), figsize=(30,5.5))

   # SPLIT ANTENNA SUBSET BY CHANNEL
   for i, (ch, chnl_grp) in enumerate(ant_grp.groupby(['Channel'])):
      ax[i].plot(chnl_grp['TX_Power'], chnl_grp['EVM'])
      ax[i].set_title('{}\n{}'.format(a, ch))             # PREFERRED STRING CONCAT METHOD

   plt.tight_layout()
   plt.show()
   plt.clf()
   plt.close()

---

用随机数据进行演示：

os_tools = ['windows', 'mac', 'linux']
data_tools = ['sas', 'stata', 'spss', 'python', 'r', 'julia']

### DATA BUILD
np.random.seed(11212018)
random_df = pd.DataFrame({'Antenna': np.random.choice(os_tools, 500),
                          'Channel': np.random.choice(data_tools, 500),                          
                          'TX_Power': np.arange(0, 500, 1),
                          'EVM': np.random.uniform(1, 100, 500)
                         })

# SPLIT MAIN DATA FRAME BY ANTENNA
for a, ant_grp in random_df.groupby(['Antenna']):             
   fig, ax = plt.subplots(nrows=1, ncols=len(ant_grp['Channel'].unique()), figsize=(12,3))

   # SPLIT ANTENNA SUBSET BY CHANNEL
   for i, (ch, chnl_grp) in enumerate(ant_grp.groupby(['Channel'])):
      ax[i].plot(chnl_grp['TX_Power'], chnl_grp['EVM'])
      ax[i].set_title('{}\n{}'.format(a, ch))             # PREFERRED STRING CONCAT METHOD

   plt.tight_layout()
   plt.show()
   plt.clf()
   plt.close()