gistfile1.py

import MySQLdb
import MySQLdb.cursors
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import datetime
import time
import sys
import logging

sql = {'database': 'XXXXX', 'host': 'XXXXXX', 'username': 'user', 'password': 'XXXXX'}

scanner1 = {'name': 'XXXXXX', 'query_limit': 4018, 'dist': 6160, 'elev': 8, 'query_start': 0, 'scn_id': 177,
            'sector_size': 45, 'az_low': 2, 'az_high': 3}
scanner2 = {'name': 'YYYYYY', 'query_limit': 4018, 'dist': 2480, 'elev': 19, 'query_start': 0, 'scn_id': 252,
            'sector_size': 45,  'az_low': 252, 'az_high': 253}

cnr_ll = -25
cnr_ul = -5
rs_ll = -30
rs_ul = 30
time_offset = '50min'

# create logging object and write to file
logging.basicConfig(filename=scanner1['name'] + scanner2['name'] + "/" + scanner1['name'] + scanner2['name'] + "output_"
                             + str(scanner1['scn_id']) + str(scanner2['scn_id']) + ".log", filemode='w',
                    level=logging.INFO)

# add stream handler to write logging entries to stdout
root = logging.getLogger()
root.setLevel(logging.DEBUG)
ch = logging.StreamHandler(sys.stdout)
ch.setLevel(logging.DEBUG)
formatter = logging.Formatter('%(levelname)s - %(message)s')
ch.setFormatter(formatter)
root.addHandler(ch)

df_temp = pd.DataFrame()
df_result = pd.DataFrame(columns=['dt_mid', 'v', 'u', 'hspeed', 'dir'])
df_result_10minavg = pd.DataFrame(columns=['dt_mid', 'v', 'u', 'hspeed', 'dir', 'avail'])


def dbconnect():
    # connect to db
    cnx = MySQLdb.connect(user=sql['username'], passwd=sql['password'], host=sql['host'],
                          db=sql['database'], cursorclass=MySQLdb.cursors.SSCursor)
    cursor = cnx.cursor()
    return cnx, cursor


def get_midlos(cursor, scanner):
    # retrieve the middle LOS azimuth value and elevation angle
    print("{0} Sending LOS/elev query to SQL db.. Please stand by:".format(scanner['name']))
    query_scenario = "SELECT azim, elev FROM wind_def_{0} WHERE scn_id = {1} LIMIT 450,{2}".format(scanner['name'], scanner['scn_id'], scanner['sector_size'])
    logging.info(query_scenario)
    cursor.execute(query_scenario)
    fetched = cursor.fetchmany(scanner['sector_size'])

    if len(fetched) == 0:
        logging.error("Error! Please check that scn_id is valid")
    else:
        logging.info("LOS/elev data fetched!")

    middle_position = np.round(len(fetched) / 2)

    for i, (azim, elev) in enumerate(fetched):
        if i == middle_position:
            middle_azim = azim
            scanner['elev'] = elev

    az_low = middle_azim - 0.5
    az_high = middle_azim + 0.5
    logging.info("Selecting range of azimuth values between: {0} and {1}".format(az_low, az_high))

    return az_low, az_high


def get_data(cursor, scanner):

    # send query for data
    print("{0} Sending data query to SQL db.. Please stand by:".format(scanner['name']))

    query_data = ("SELECT gw_id, scn_id, dt_start, dt_stop, azim, radSpeed, cnr, disp "
    "FROM wisscas.wind_def_{0} def "
    "INNER JOIN wind_data_{0} dat using (gw_id) "
    "WHERE def.scn_id = {1} AND def.azim BETWEEN {2} and {3} "
    "AND dat.dist = {4} LIMIT {5},{6};".format(scanner['name'], scanner['scn_id'], scanner['az_low'], scanner['az_high']
                                               , scanner['dist'], scanner['query_start'], scanner['query_limit']))
    logging.info(query_data)

    start = time.time()
    cursor.execute(query_data)
    df_data = pd.DataFrame(columns=['gw_id', 'scn_id', 'dt_start', 'dt_stop', 'azim', 'radSpeed', 'cnr', 'disp'], index=np.arange(scanner['query_limit']))

    count = 0
    increment = 500

    while True:
        rows_data = cursor.fetchmany(size=increment)
        fetched = len(rows_data)
        if fetched == 0:
            break
        df_data.iloc[count:(count + fetched)] = rows_data
        count += fetched
        sys.stdout.write('{0} rows fetched : {1} % imported\r'.format(count, (np.round((count / scanner['query_limit']) * 100)), 2))
        sys.stdout.flush()

    logging.info("data fetched!")
    logging.info("Total time = " + str(round((time.time() - start), 4)) + " seconds")
    print("First 5 rows: ")
    print(df_data.head(5))
    print("Last 5 rows: ")
    print(df_data.tail(5))

    return df_data


def plotcnrspd(df_data, is_filtered):
    # plot cnr & radial speed for defined distance
    # is_filtered arg used to re-plot
    if is_filtered == 0:
        print("Plotting (unfiltered) CNR and radial speeds:")
        fig1 = plt.figure()
        plt.subplot(211)
        plt.plot(list(df_data.cnr.values))
        plt.axhline(y=-25, color='r')
        plt.axhline(y=-5, color='r')
        plt.ylim(-30, 20)
        plt.ylabel('(unfiltered) cnr')

        plt.subplot(212)
        plt.plot(list(df_data.radSpeed.values))
        plt.ylim(-25, 25)
        plt.ylabel('(unfiltered) radSpeed')
        # plt.savefig(scanner_name + "/" + scanner_name + "_fig_cnrspd_" + str(scn_id) + ".png", bbox_inches='tight')
        plt.show()
        # logging.info("Plot saved to /" + scanner_name + "/" + scanner_name + "_fig_cnrspd_" + str(scn_id) + ".png")

    elif is_filtered == 1:
        print("Plotting filtered CNR and radial speeds:")
        fig2 = plt.figure()
        plt.subplot(211)
        df_wind_filtered = df_data.query('filtered == 0')
        plt.plot(list(df_wind_filtered.cnr.values))
        plt.ylim(-30, 20)
        plt.ylabel('(filtered) cnr')

        plt.subplot(212)
        plt.plot(list(df_wind_filtered.radSpeed.values))
        plt.ylim(-25, 25)
        plt.ylabel('(filtered) radSpeed')
        # plt.savefig(scanner_name + "/" + scanner_name + "_fig_cnrspd_filtered_" + str(scn_id) + ".png", bbox_inches='tight')
        plt.show()
        # logging.info("Plot saved to /" + scanner_name + "/" + scanner_name + "_fig_cnrspd_filtered_" + str(scn_id) + ".png")


def fixazim(df_data, scanner):
    # applies azimuth correction factor (0.5 deg in this case)
    azim_correction = 0.5
    logging.info("{0} Correcting azimuth values by -{1} deg".format(scanner['name'], azim_correction))
    df_data['azim'] = df_data['azim'].apply(lambda x: x - azim_correction)
    return df_data


def convtime(labviewtime):
    # convert time from labview epoch (Jan 1, 1904) to UNIX epoch (Jan 1,1970) and then to common timestamp
    unixtime = labviewtime - 2082844800
    timestamp = datetime.datetime.utcfromtimestamp(int(unixtime)).strftime('%Y-%m-%d %H:%M:%S')
    return timestamp


def main():
    cnx, cursor = dbconnect()
    scanner1['az_low'], scanner1['az_high'] = get_midlos(cursor, scanner1)
    scanner2['az_low'], scanner2['az_high'] = get_midlos(cursor, scanner2)
    df_data1 = get_data(cursor, scanner1)
    df_data2 = get_data(cursor, scanner2)
    df_data1 = fixazim(df_data1, scanner1)
    df_data2 = fixazim(df_data2, scanner2)

    plotcnrspd(df_data1, 0)
    plotcnrspd(df_data2, 0)

    cursor.close()
    cnx.close()
    logging.shutdown()

if __name__ == '__main__':
    main()