I want to create a matrix of plots fetching data from a database for a list of antennas and channels

Question

Plot switches horizontally when channel changes and vertically when antenna changes.

I am unable to switch the plots after plotting for a one plot e.g. 1,1 in matrix. The same data is fetched for the row and I end up getting undesired result.

    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)

Parfait · Answer 1 · 2019-07-18T18:37:33.030

Since there are quite a number of issues with current code, consider the following re-factored adjustment that imports all your data and then subsets the data to plot according to corresponding Antenna and Channel. Specifically, below uses:

Explicit joins (current ANSI-SQL standard) and not the obsolete, hard to read and maintain implicit joins with commas in FROM clause;
pandas.read_sql for one SQL query to avoid multiple nested loop cursor calls;
Parameterization to avoid string concatenation (with unofficially deprecated modulo % operator) and punctuation (enclosing strings with quotations).

NOTE: Do not confuse parameter placeholder %s with the modulo operator's placeholder which is used by most DB-APIs (Postgres, MySQL) whereas others use qmarks, ? (SQLite, PyODBC), and still others uses named/numbered (Oracle) placeholders each without quotes. Adjust per your actual database.
Splits data frame using DataFrame.groupby() by each antenna and further by each channel for plotting.

Adjusted code (untested and may need further adjustments)

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()

To demonstrate with random data:

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()

this gives me an error "'AxesSubplot' object is not subscriptable." — Rohan Ganu, Jul 17 '19 at 23:50
Check the `sql_df` data. Any missing values in *Antenna* and *Channel* columns? — Parfait, Jul 18 '19 at 00:32
I added a random data demo to illustrate code should work. Any issues must be data-specific. — Parfait, Jul 18 '19 at 18:38

I want to create a matrix of plots fetching data from a database for a list of antennas and channels

1 Answers1