<jupyter_start><jupyter_text>Función _getKeyVariable_ para identificar si existe un nombre clave de la variable, si no existe retorna ""<jupyter_code>def getKeyVariable(var_value):
    try:
        var_value.astype(float)
        return var_value + ":"
    except:
        return ""
def getTimestamp(tmYear, tmMonth, tmDay, tmHour, tmMin):
    tsDate = str(tmYear) + "/" + str(tmMonth) + "/" + str(tmDay)
    tsDate = pd.to_datetime(tsDate, format="%Y/%m/%d")
    if (tmHour > 23):
        tmHour = 0
        tsDate += timedelta(days=1)
    tsDate = tsDate.replace(hour=tmHour, minute=tmMin)
    return tsDate
myconn = sql.connect("eplusout.sql")
mycursor = myconn.cursor()
comando = """SELECT ReportData.TimeIndex, ReportData.ReportDataDictionaryIndex, Time.DayType, ReportData.Value
  FROM (ReportData INNER JOIN ReportDataDictionary ON ReportData.ReportDataDictionaryIndex = ReportDataDictionary.ReportDataDictionaryIndex) 
  INNER JOIN Time ON ReportData.TimeIndex = Time.TimeIndex"""

nombres = ["Time Index", "Variable Index", "Type of Day", "Variable Value"]
data =pd.DataFrame( mycursor.execute(comando).fetchall(),columns=nombres)
data.dropna(inplace=True)
data  = data.pivot(index="Time Index", columns="Variable Index", values=["Variable Value"])
data<jupyter_output><empty_output><jupyter_text>3. Executar comando para extraer nombre de variables<jupyter_code>comando_variables = "SELECT ReportDataDictionaryIndex, KeyValue, Name, Units FROM ReportDataDictionary"
nombres = ['Index_variable','Key_variable','Name_variable','Unit_variable']
name_variables =pd.DataFrame( mycursor.execute(comando_variables).fetchall(),columns=nombres)
name_variables.set_index('Index_variable',inplace=True)
name_variables<jupyter_output><empty_output><jupyter_text>Crear nombre compuesto para el reporte de variables que contenga clave, nombre y unidad de medida<jupyter_code>name_variables["Variable Name"] = getKeyVariable(name_variables['Key_variable']) +  \
                                  + name_variables['Name_variable'] \
                                  + " (" \
                                  + name_variables['Unit_variable'] + ")"
name_variables
data
data.columns = [name_variables.loc[columna[1]]["Key_variable"] + ':' + name_variables.loc[columna[1]]["Variable Name"] for columna in data.columns]

data<jupyter_output><empty_output><jupyter_text>4. Executar comando para encontrar datos de tiempo<jupyter_code>comando_tiempo = "SELECT tm.TimeIndex, tm.Year, tm.Month, tm.Day, tm.Hour, tm.Minute FROM Time AS tm"
nombres = ['timeindex','year','month','day','hour','minute']
data_timestamp = pd.DataFrame(mycursor.execute(comando_tiempo).fetchall(),columns=nombres)
data_timestamp = data_timestamp[data_timestamp.year != 0]
# data_timestamp.set_index('timeindex',inplace=True)
data_timestamp['tiempo'] = str(data_timestamp.year)+'-'+str(data_timestamp.month)+'-'+str(data_timestamp.day)
data_timestamp
tmp = list(mycursor.execute(comando_tiempo).fetchall())
tmp = list(tmp)
# tmp = tmp
tmp
str(data_timestamp.year).split('\n')
data_timestamp<jupyter_output><empty_output><jupyter_text>Crear una columna con los timestamp consolidados de los datos del dataframe del tiempo.<jupyter_code>df_yeartm["Timestamp"] = df_yeartm.apply(lambda x : getTimestamp(x[1], x[2], x[3], x[4], x[5]), axis = 1)
time_cols = []
for i in df3.index:
    time_cols.append(df_yeartm["Timestamp"][i])
df3.index = time_cols<jupyter_output><empty_output><jupyter_text>5. Dataframe con la información extraída del archivo SQL<jupyter_code>df3.plot(subplots=True, figsize=(14,120));
len(df3.columns)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1298449612403101, 'fraction_numerical': 0.0069213732004429675, 'mean_word_length': 5.829867674858223, 'pattern_counts': {'":': 1, '<': 25, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '219610', 'license': 'MIT', 'n_tokens_mistral': 1206, 'n_tokens_neox': 1200, 'n_words': 224, 'path': 'notebooks/tmp/efrain_sql2csv-Copy1.ipynb', 'repo_name': 'AltamarMx/read_epsql', 'size': '3612'}