def test_date_cols(self): global server, pwd, user print("==> test_date_cols") try: host_params1 = {'host': server, 'port': 9200, 'use_ssl': True} # RPINUM es = ES([host_params1], connection_class=RC, http_auth=( user, pwd), use_ssl=True, verify_certs=False) print(es.info()) try: es.indices.delete('test_date_cols') except elasticsearch.NotFoundError: pass doc = { 'attr1': 'test' } es.index(index="test_date_cols", id="t1", doc_type='_doc', body=doc) time.sleep(1) res = es_helper.elastic_to_dataframe(es, index="test_date_cols", datecolumns=["date1"]) print(len(res)) print(res.columns) self.assertTrue("date1" in res.columns) doc = { 'attr1': 'test', 'date1': datetime.now() } es.index(index="test_date_cols", id="t2", doc_type='_doc', body=doc) time.sleep(1) res = es_helper.elastic_to_dataframe(es, index="test_date_cols", datecolumns=["date1"]) self.assertTrue("date1" in res.columns) doc = { 'attr1': 'test', 'date2': datetime.now() } es.index(index="test_date_cols", id="t3", doc_type='_doc', body=doc) time.sleep(1) res = es_helper.elastic_to_dataframe(es, index="test_date_cols", datecolumns=["date1", "date2"]) self.assertTrue("date1" in res.columns) self.assertTrue("date2" in res.columns) es.indices.delete('test_date_cols') finally: pass
def getTagWeather(es, city,table,tag,time): query='cpost: '+city value = 0 if table == 'pred': start = datetime.now() end = start+timedelta(hours=time) df = es_helper.elastic_to_dataframe(es, 'weather_prediction', start=start, end=end, query=query) df = df.sort_values(by="@timestamp", ascending=False).reset_index(drop=True) logger.info(df) value = df.at[0, tag] logger.info(value) else: end = datetime.now() start = end-timedelta(hours = 3) df = pd.DataFrame() if time == 0: df = es_helper.elastic_to_dataframe(es, 'weather_real', start=start, end=end, query=query) df = df.sort_values('@timestamp', ascending=False).reset_index(drop=True) logger.info(df) value = df.at[0, tag] else: start = end-timedelta(hours=time) df = es_helper.elastic_to_dataframe(es, 'weather_real', start=start, end=end, query=query) df = df.sort_values('@timestamp').reset_index(drop=True) value = df.at[0, tag] return str(value)
def test_size(self): global server, pwd print("==> test_size") try: host_params1 = { 'host': server, 'port': 9200, 'use_ssl': True } # RPINUM es = ES([host_params1], connection_class=RC, http_auth=("user", pwd), use_ssl=True, verify_certs=False) print(es.info()) res2 = es_helper.elastic_to_dataframe( es, index="docker_stats*", size=10, timestampfield="read", start=datetime.datetime.now() - datetime.timedelta(hours=1), end=datetime.datetime.now()) print(len(res2)) self.assertTrue(res2 is not None) self.assertTrue((len(res2) >= 0) and (len(res2) <= 10)) finally: pass
def get(self, user=None): logger.info("schamps - get products tree") df = es_helper.elastic_to_dataframe(es, index="products_parameters_new", query="display: true") objClass = {} lvl1 = df .sortLvl1.unique() for i in lvl1: objClass[i] = {} subDF = df.loc[df['sortLvl1'] == i] lvlObjects = subDF.sortLvl2.unique() for j in lvlObjects: objClass[i][j] = {} subSubDF = subDF.loc[subDF['sortLvl2'] == j] subLvlObjectsLvl3 = subSubDF.sortLvl3.unique() objClass[i][j]['sortLvl3'] = subLvlObjectsLvl3.tolist() subLvlObjectsLvl4 = subSubDF.sortLvl4.unique() objClass[i][j]['sortLvl4'] = subLvlObjectsLvl4.tolist() subLvlObjectsLvl5 = subSubDF.sortLvl5.unique() objClass[i][j]['sortLvl5'] = subLvlObjectsLvl5.tolist() subLvlObjectsLvl6 = subSubDF.sortLvl6.unique() objClass[i][j]['sortLvl6'] = subLvlObjectsLvl6.tolist() subLvlObjectsLvl7 = subSubDF.sortLvl7.unique() objClass[i][j]['sortLvl7'] = subLvlObjectsLvl7.tolist() subLvlObjectsLvl8 = subSubDF.sortLvl8.unique() objClass[i][j]['sortLvl8'] = subLvlObjectsLvl8.tolist() subLvlObjectsLvl9 = subSubDF.sortLvl9.unique() objClass[i][j]['sortLvl9'] = subLvlObjectsLvl9.tolist() subLvlObjectsLvl10 = subSubDF.sortLvl10.unique() objClass[i][j]['sortLvl10'] = subLvlObjectsLvl10.tolist() #for k in subLvlObjects: # objClass[i][j][k] = {} req = {'results': False, 'reccords': objClass} logger.info(objClass) return {'error':"",'status':'ok', 'data': json.dumps(req)}
def test_elastic_to_panda(self): """ Send Receive """ global server, pwd, user print("==> test_elastic_to_panda") try: host_params1 = {'host': server, 'port': 9200, 'use_ssl': True} # RPINUM es = ES([host_params1], connection_class=RC, http_auth=( user, pwd), use_ssl=True, verify_certs=False) print(es.info()) res2 = es_helper.elastic_to_dataframe(es, index="docker_stats*", scrollsize=1000, datecolumns=[ "read"], timestampfield="read", start=datetime.now()-timedelta(hours=1), end=datetime.now()) res2["read"].describe() print(len(res2)) self.assertTrue(res2 is not None) self.assertTrue(len(res2) > 0) finally: pass
def getLastUpdate(es): end = datetime.now() start = end-timedelta(days=3) df = es_helper.elastic_to_dataframe(es, 'weather_real', start=start, end=end) df = df.sort_values(by="@timestamp", ascending=False).reset_index(drop=True) value = df.at[0, "@timestamp"] return int(value/60000)
def getECPowerCogens(): global es cogens = es_helper.elastic_to_dataframe(es, 'cogen_parameters', query="source: ecpower") cogens = cogens [['ecid', 'name']] cogensTab = [] for key, row in cogens.iterrows(): rec = {"id": row['ecid'], "name": row['name']} cogensTab.append(rec) return cogensTab
def getLogoWeather(es, city,table,time): query='cpost: '+city value = 1 if table == 'pred': start = datetime.now() end = start+timedelta(hours=time) df = es_helper.elastic_to_dataframe(es, 'weather_prediction', start=start, end=end, query=query) df = df.sort_values(by="@timestamp", ascending=False).reset_index(drop=True) logger.info(df) row = df.iloc[0] logger.info(row) radiance = int(row['radiance']) symbol_cover = int(row['symbol_cover']) wind = int(row['wind_speed_to']) rain = float(row['qpcp']) temp = float(row['temp']) if radiance <= 750 and radiance > 350 and symbol_cover in [6,7]: value = 2 if wind >= 50: value = 4 elif radiance > 20 and symbol_cover in [4,5]: value = 3 elif symbol_cover in [6,7] and rain >=4 and rain < 7: value = 5 if temp < 2: value = 7 elif rain > 7: value = 6 if temp < 2: value = 8 elif radiance == 0 and symbol_cover in [6,7]: value = 9 elif radiance == 0 and symbol_cover in [4,5,6,7]: value = 10 if rain > 4: value = 12 if wind >= 50: value = 11 elif radiance == 0 and symbol_cover in [6,7] and temp < 2 and rain > 4: value = 13 if rain > 7: value = 14 return value
def calcCogen(): global es logger.info("Calc Cogen ...") end = datetime.now() start = end - timedelta(days=30) cogens = es_helper.elastic_to_dataframe(es, 'cogen_computed', query='*', start=start, end=end) cogengrouped = cogens.groupby(['Contract', 'client', 'name']).agg({ '@timestamp': 'max', 'Hours_Index': 'max', 'Starts_Index': 'max' }).reset_index() cogengrouped = cogengrouped.set_index('name') params = es.search("cogen_parameters", size='10000') for cog in params['hits']['hits']: name = cog['_source']['name'] if name in cogengrouped.index: cogstats = cogengrouped.loc[name, :] cog['_source']['Hours'] = cogstats['Hours_Index'] cog['_source']['Starts'] = cogstats['Starts_Index'] cog['_source']['LastUpdate'] = int(time.time() * 1000) cog['_source']['modifyBy'] = 'GTC' bulkbody = '' action = {} for cog in params['hits']['hits']: action["index"] = { "_index": cog['_index'], "_type": "doc", "_id": cog['_id'] } # print(action) newrec = cog['_source'] # print(newrec) bulkbody += json.dumps(action) + "\r\n" bulkbody += json.dumps(newrec) + "\r\n" res = es.bulk(body=bulkbody)
def retrieve_raw_data(day): start_dt = datetime(day.year, day.month, day.day) end_dt = datetime(start_dt.year, start_dt.month, start_dt.day, 23, 59, 59) df_raw = es_helper.elastic_to_dataframe(es, index='opt_cleaned_data*', query='*', start=start_dt, end=end_dt, scrollsize=10000, size=1000000) containertimezone = pytz.timezone(get_localzone().zone) df_raw['@timestamp'] = pd.to_datetime(df_raw['@timestamp'], \ unit='ms', utc=True).dt.tz_convert(containertimezone) df_raw = df_raw.sort_values('@timestamp') return df_raw
def retrieve_dict_old(es): today_time = datetime.min.time() today_datetime = datetime.combine(datetime.now().date(), today_time) dataframe = es_helper.elastic_to_dataframe( es, index="parl_kizeo*", scrollsize=1000, timestampfield="update_time_dt", query='wo_state:Underway', end=today_datetime, _source=['update_time_dt', 'wo_state', 'user_name']) #return dataframe dict_old = {} for key, value in dataframe.groupby( 'user_name').count()['_id'].iteritems(): try: dict_old[key.split(' ')[0]] = value except: print('unable to split this key: ' + str(key)) return dict_old
def retrieve_raw_data(day): start_dt = datetime(day.year, day.month, day.day, 14, 30, 0) end_dt = datetime(start_dt.year, start_dt.month, start_dt.day, 20, 59, 59) df_raw = es_helper.elastic_to_dataframe( es, index='opt_sites_data*', query='client: COGLTS AND area_name: *CH4*', start=start_dt, end=end_dt, size=1000000) containertimezone = pytz.timezone(get_localzone().zone) try: df_raw['@timestamp'] = pd.to_datetime(df_raw['@timestamp'], \ unit='ms', utc=True).dt.tz_convert(containertimezone) except: pass df_raw = df_raw.sort_values('@timestamp') logger.info(df_raw) return df_raw
def compute_kpi105_monthly(start, end): """ Compute KPI 105 monthy records (Records used for the dashboard) Parameters ---------- start Date of the first imported record end Date of the last imported record """ logger.info("====> compute_kpi105_monthly") starttime = time.time() logger.info(start) logger.info(end) start = mkFirstOfMonth(start) end = end.replace(day=calendar.monthrange(end.year, end.month)[1]) logger.info(start) logger.info(end) # df_kpi105 = etp.genericIntervalSearch(es, 'biac_kpi105', query='*', start=start, end=end) df_kpi105 = es_helper.elastic_to_dataframe(es, 'biac_kpi105', query='*', start=start, end=end) df_kpi105['month'] = pd.to_datetime(df_kpi105['@timestamp'], unit='ms').dt.strftime('%Y-%m') df_kpi105 = df_kpi105.groupby(['lot', 'month', 'ronde_number' ]).count()[['@timestamp' ]].rename(columns={ '@timestamp': 'count' }).reset_index() records_number = 0 for lot in df_kpi105['lot'].unique(): logger.info(">>>>> Working for lot:" + str(lot)) df_lot = df_kpi105[df_kpi105['lot'] == lot] min_date = datetime.strptime(min(df_lot['month']), '%Y-%m') max_date = datetime.strptime(max(df_lot['month']), '%Y-%m') months = [] current = min_date while current <= max_date: months.append(current.strftime('%Y-%m')) current += relativedelta(months=1) df_months = pd.DataFrame(months) df_months['join'] = 1 df_months.columns = ['month', 'join'] rondes = [1] if int(lot) == 2: rondes = [1, 2, 3] df_rondes = pd.DataFrame(rondes) df_rondes['join'] = 1 df_rondes.columns = ['ronde_number', 'join'] df_default = df_months.merge(df_rondes, left_on='join', right_on='join') del df_default['join'] df_default['number_of_days'] = df_default['month'].apply( lambda x: get_days_already_passed(x)) df_default['_id'] = df_default['month'] + '_' + df_default[ 'ronde_number'].astype(str) df_lot['_id'] = df_lot['month'] + '_' + df_lot['ronde_number'].astype( str) del df_lot['month'] del df_lot['ronde_number'] df_merged = df_default.merge(df_lot, left_on='_id', right_on='_id', how="outer") df_merged['lot'] = df_merged['lot'].fillna(lot) df_merged['count'] = df_merged['count'].fillna(0) df_merged['percent'] = round( 100 * df_merged['count'] / df_merged['number_of_days'], 2) df_merged['_index'] = 'biac_month_kpi105' df_merged['_timestamp'] = pd.to_datetime(df_merged['month'], format='%Y-%m') df_merged.columns = [ 'month', 'ronde_number', 'number_of_days', '_id', 'lot', 'ronde_done', 'percent', '_index', '_timestamp' ] df_merged['ronde_done'] = df_merged['ronde_done'].astype(int) df_merged['_id'] = df_merged['_id'].apply( lambda x: 'lot' + str(int(lot)) + '_' + str(x)) logger.info("Storing " * 20) # pte.pandas_to_elastic(es, df_merged) es_helper.dataframe_to_elastic(es, df_merged) records_number += len(df_merged) endtime = time.time() log_message( "Compute monthly KPI105 (process biac_import_kpi105.py) finished. Duration: %d Records: %d." % (endtime - starttime, records_number))
def compute_kpi103_monthly(start, end): """ Called three seconds after an import with the first day and last day of the previous import. Parameters ---------- start Date of the first imported record end Date of the last imported record """ starttime = time.time() logger.info(start) logger.info(end) start = mkFirstOfMonth(start) end = end.replace(day=calendar.monthrange(end.year, end.month)[1]) logger.info(start) logger.info(end) # df_kpi103 = etp.genericIntervalSearch(es, 'biac_kpi103', query='*', start=start, end=end,timestampfield="date") df_kpi103 = es_helper.elastic_to_dataframe(es, 'biac_kpi103', query='*', start=start, end=end,timestampfield="date") #logger.info(df_kpi103['date'].dt) df_kpi103['month'] = pd.to_datetime(df_kpi103['date2']).dt.strftime('%Y-%m') df_kpi103=df_kpi103.groupby(['month', 'ronde_number']).count()[['date']].rename(columns={'date': 'count'}).reset_index() min_date = datetime.strptime(min(df_kpi103['month']), '%Y-%m') max_date = datetime.strptime(max(df_kpi103['month']), '%Y-%m') months = [] current = min_date while current <= max_date: months.append(current.strftime('%Y-%m')) current += relativedelta(months=1) df_months = pd.DataFrame(months) df_months['join']=1 df_months.columns=['month', 'join'] rondes = [1, 2] df_rondes = pd.DataFrame(rondes) df_rondes['join']=1 df_rondes.columns=['ronde_number', 'join'] df_default=df_months.merge(df_rondes, left_on='join', right_on='join') del df_default['join'] df_default['number_of_days'] = df_default['month'].apply(lambda x: get_days_already_passed(x)) df_default['_id'] = df_default['month'] + '_' + df_default['ronde_number'].astype(str) df_kpi103['_id'] = df_kpi103['month'] + '_' + df_kpi103['ronde_number'].astype(str) del df_kpi103['month'] del df_kpi103['ronde_number'] df_merged = df_default.merge(df_kpi103, left_on='_id', right_on='_id', how="outer") df_merged = df_merged.fillna(0) df_merged['percent'] = round(100*df_merged['count'] / df_merged['number_of_days'], 2) df_merged['_index'] = 'biac_month_kpi103' df_merged['_timestamp'] = pd.to_datetime(df_merged['month'], format='%Y-%m') df_merged.columns=['month', 'ronde_number', 'number_of_days', '_id', 'ronde_done', 'percent', '_index', '_timestamp'] df_merged['ronde_done'] = df_merged['ronde_done'].astype(int) es_helper.dataframe_to_elastic(es, df_merged) endtime = time.time() log_message("Compute monthly KPI103 (process biac_import_kpi103.py) finished. Duration: %d Records: %d." % (endtime-starttime, df_merged.shape[0]))
def messageReceived(destination, message, headers): global es, basefile records = 0 starttime = time.time() logger.info("==> " * 10) logger.info("Message Received %s" % destination) logger.info(headers) now = datetime.now() local_timezone = tzlocal.get_localzone() if "CamelSplitAttachmentId" in headers: headers["file"] = headers["CamelSplitAttachmentId"] if "file" in headers: logger.info("File:%s" % headers["file"]) log_message("Import of file [%s] started." % headers["file"]) else: headers["file"] = "From_Rest_API" dfconfig = es_helper.elastic_to_dataframe(es, index="nyx_config_telephony") dfconfig = dfconfig.set_index(["DnisNr"]) dfconfight = dfconfig.to_dict('index') mess = base64.b64decode(message) df = None mesin = mess.decode("utf-8", "ignore") # te=pd.read_fwf(StringIO(full) ## , names=["Date","Hour","Duration","A4","Code","A6","A7","Called","Caller","A10","Desk","A12","A13","A14","A15","A16","A17"] # ,delim_whitespace=True, header=None,converters={"Date":str,"Hour":str,"Called":str,"Caller":str,"Desk":str}) # colspecs=[[0, 6], # [6, 13], # [13, 19], # [20, 24],#A4 # [25, 27],#COde # [27, 30],#A6 # [30, 37],#A7 # [37, 57],#CALLED # [58, 88],#CALLER # [89, 96],#Rings # [97, 107],#DESK # [108, 123],#A12 # [124, 133],#A13 # [134, 138],#A14 # [137, 143],#A15 # [144, 155], # [156, 159]] colspecs = [ [0, 6], [6, 13], [13, 19], [20, 24], #A4 [25, 27], #COde [27, 30], #A6 [30, 37], #A7 [37, 60], #CALLED [61, 88], #CALLER [89, 96], #Rings [97, 107], #DESK [108, 123], #A12 [124, 135], #A13 [137, 140], #A14 [139, 145], #A15 [146, 156], [157, 161] ] logger.info("Remove LIFE SIGNS") mesin = mesin.split("\n") mesin = [_.strip() for _ in mesin if "Cofely" not in _] mesin = "\n".join(mesin) logger.info("Panda read...") te = pd.read_fwf(StringIO(mesin), header=None, colspecs=colspecs, converters={ "A13": str, "A4": str, "A15": str, "A16": str, "Date": str, "Hour": str, "Called": str, "Caller": str, "Desk": str }, names=[ "Date", "Hour", "Duration", "A4", "Code", "A6", "A7", "Called", "Caller", "Rings", "Desk", "A12", "A13", "A14", "A15", "A16", "A17" ]) logger.info("Done") #te=te[89:] # IMPORTANT get rid of the base template file te["Caller"] = te["Caller"].fillna("") te["Code"] = te["Code"].fillna("") te["Desk"] = te["Desk"].fillna(0) te['InternalCalled1'] = te['Called'].str.replace(' ', '') te['InternalCalled'] = (te['InternalCalled1'].str.len() < 6) te['InternalCaller1'] = te['Caller'].str.replace(' ', '') te['InternalCaller'] = (te['InternalCaller1'].str.len() < 6) te['SolidusCalled'] = (te['InternalCalled1'].str.match("93901")) te['Desk2'] = pd.to_numeric(te['Desk'], errors='coerce', downcast='integer') te['DeskCaller'] = pd.to_numeric(te['Caller'], errors='coerce', downcast='integer') del te['InternalCalled1'] del te['InternalCaller1'] calltype = [] for index, row in te.iterrows(): if (row["Caller"].find("91931") >= 0): calltype.append("Test") elif (row["Desk2"] > 76800) and (row["Desk2"] < 76810) and ( row["DeskCaller"] > 76900) and (row["DeskCaller"] < 76910): calltype.append("Transfer") elif (row["DeskCaller"] > 76800) and (row["DeskCaller"] < 76810) and ( row["Desk2"] > 76900) and (row["Desk2"] < 76910): calltype.append("Transfer") elif (row["Desk2"] > 76900) and (row["Desk2"] < 76910): calltype.append("InDispa") elif (row["Desk2"] > 76800) and (row["Desk2"] < 76810): calltype.append("InDesk") elif (row["SolidusCalled"]): if (not (row["InternalCaller"])): calltype.append("In") else: calltype.append("InOther") else: if (row["InternalCaller"]): if (row["DeskCaller"] > 76900) and (row["DeskCaller"] < 76910): calltype.append("OutDispa") elif (row["DeskCaller"] > 76800) and (row["DeskCaller"] < 76810): calltype.append("OutDesk") else: calltype.append("Out") else: calltype.append("Other") te['CallType'] = calltype te['DurationSecond'] = te['Duration'] % 100 te['DurationMinute'] = te['Duration'] / 100 te['DurationMinute2'] = te['DurationMinute'].astype(int) te['Duration'] = te['DurationMinute2'] * 60 + te['DurationSecond'] #logger.info(te) messagebody = "" action = {} te2 = te del te2["A4"] del te2["A6"] #del te2["A10"] #del te2["A11"] del te2["A12"] del te2["A13"] del te2["A14"] #del te2["A15"] #del te2["A16"] #del te2["A17"] del te2["A7"] te2["A15"].fillna(0, inplace=True) te2["A16"].fillna(0, inplace=True) te2["A17"].fillna(0, inplace=True) te2["timestamp"] = te2["Date"] + te2["Hour"] te2["Date2"] = pd.to_datetime(te2["timestamp"], format="%d%m%y%H%M%S") te2["Date"] = pd.to_datetime(te2["timestamp"], format="%d%m%y%H%M%S") te2["Date"] = te2['Date'].dt.tz_localize(tz='Europe/Paris', ambiguous=True) del te2["timestamp"] del te2["Hour"] te2 for index, row in te2.iterrows(): obj = {} #obj["@timestamp"]=int(row["Date"].timestamp())*1000 obj["@timestamp"] = row['Date'].isoformat() obj["Duration"] = row["Duration"] obj["Code"] = row["Code"].replace(' ', '') obj["Called"] = str(row["Called"]).replace(' ', '') try: if int(obj["Called"]) in dfconfight: obj["Client"] = dfconfight[int(obj["Called"])]["Name"] except: pass obj["Caller"] = row["Caller"].replace(' ', '') try: obj["Desk"] = int(row["Desk"]) except: obj["Desk"] = row["Desk"] try: obj["DeskCaller"] = int(row["DeskCaller"]) except: obj["DeskCaller"] = row["DeskCaller"] if str(obj["DeskCaller"]) == 'nan': obj["DeskCaller"] = "" obj["InternalCaller"] = row["InternalCaller"] obj["InternalCalled"] = row["InternalCalled"] obj["SolidusCalled"] = row["SolidusCalled"] obj["CallType"] = row["CallType"] obj["Rings"] = row["Rings"] obj["A15"] = row["A15"] obj["A16"] = row["A16"] obj["A17"] = row["A17"] if "nan" not in obj["Called"] + '_' + obj["Caller"]: action["index"] = { "_index": "telephony", "_type": "doc", "_id": str(int(row["Date2"].timestamp()) * 1000) + '_' + obj["Called"] + '_' + obj["Caller"] } messagebody += json.dumps(action) + "\r\n" messagebody += json.dumps(obj) + "\r\n" # if "NaN" in messagebody: # print("BAD") # pass if (len(messagebody) > 50000): logger.info("BULK") try: resbulk = es.bulk(messagebody) #print(resbulk["errors"]) if resbulk["errors"]: logger.error("BULK ERROR") for item in resbulk["items"]: for key in item: if "error" in item[key]: logger.error(item) logger.info(messagebody) except: logger.error("Unable to bulk", exc_info=True) logger.info(resbulk) messagebody = "" try: if len(messagebody) > 0: resbulk = es.bulk(messagebody) except: logger.error("Unable to bulk", exc_info=True) logger.error(resbulk) #print (messagebody) logger.info("FINISHED") endtime = time.time() try: log_message("Import of file [%s] finished. Duration: %d Records: %d." % (headers["file"], (endtime - starttime), df.shape[0])) except: log_message("Import of file [%s] finished. Duration: %d." % (headers["file"], (endtime - starttime)))
def messageReceived(destination, message, headers): global es starttime = time.time() logger.info("==> " * 10) logger.info("Message Received %s" % destination) logger.info(headers) local_timezone = tzlocal.get_localzone() try: logger.info( 'waiting 5 sec before doing the request to be sure date are correctly inserted by biac_import_kizeo.py' ) time.sleep(5) logger.info('message: ' + str(message)) obj = json.loads(message) last_update = datetime.fromtimestamp(obj['end_ts']) interval = define_interval(last_update) start_dt = interval['dt_start'] end_dt = interval['dt_end'] logger.info(start_dt) logger.info(end_dt) df = es_helper.elastic_to_dataframe(es, index="biac_kizeo", scrollsize=1000, start=start_dt, end=end_dt, datecolumns=["@timestamp"]) df['month'] = df['@timestamp'].dt.strftime('%Y-%m') df_grouped = df.groupby(['lot', 'kpi', 'contract', 'screen_name', 'month']) \ .agg({'check_conform':'sum', 'check_no_conform':'sum', 'check_number':'sum', '@timestamp':'max'}) \ .reset_index() df_grouped2 = df[df['contract']=='BACFIR'].groupby(['lot', 'kpi', 'contract', 'month']) \ .agg({'check_conform':'sum', 'check_no_conform':'sum', 'check_number':'sum', '@timestamp':'max'}) \ .reset_index() df_grouped2['screen_name'] = 'BACFIR' df_grouped = df_grouped.append(df_grouped2) df_lot4 = pd.DataFrame() try: #handling lot4 df_lot4 = es_helper.elastic_to_dataframe( es, index="biac_spot_lot4", query="kpi:302", scrollsize=1000, start=start_dt, end=end_dt, datecolumns=["@timestamp"]) except elasticsearch.NotFoundError: logger.warn('Index biac_spot_lot4 does not exist') if len(df_lot4) == 0: obj = { 'lot': '4', 'kpi': 302, 'contract': 'DNBBA', 'screen_name': 'DNBBA', 'month': '2019-01', 'conform': 0, 'not_conform': 0, 'check': 0, '@timestamp': datetime(2019, 1, 1, tzinfo=local_timezone), } df_lot4 = pd.DataFrame.from_dict({0: obj.values()}, orient='index', columns=obj.keys()) else: df_lot4['screen_name'] = 'BACDNB' df_lot4['month'] = df_lot4['@timestamp'].dt.strftime('%Y-%m') df_lot4['lot'] = df_lot4['lot'].astype(str) df_grouped_lot4 = df_lot4.groupby(['lot', 'kpi', 'contract', 'screen_name', 'month']) \ .agg({'conform':'sum', 'not_conform':'sum', 'check':'sum', '@timestamp':'max'}) \ .reset_index() df_grouped_lot4 = df_grouped_lot4.rename( columns={ "conform": "check_conform", "not_conform": "check_no_conform", "check": "check_number" }) df_grouped = df_grouped.append(df_grouped_lot4) df_grouped['_index'] = 'biac_month_2_kizeo' df_grouped['_id'] = df_grouped.apply(lambda row: row['lot'] + '_' + str(row['kpi']) + '_' + \ row['contract'].lower() + '_' + row['screen_name'].lower() + '_' + \ str(int(row['@timestamp'].timestamp())), axis=1) df_grouped = df_grouped.rename(columns={"@timestamp": "last_update"}) df_grouped['_id'] = df_grouped['_id'].str.replace(' ', '_') df_grouped['_id'] = df_grouped['_id'].str.replace('/', '_') df_grouped['percentage_conform'] = round( 100 * (df_grouped['check_conform'] / df_grouped['check_number']), 2).fillna(100) df_grouped['percentage_conform'] = df_grouped[ 'percentage_conform'].apply(lambda x: ('%f' % x).rstrip('0').rstrip('.')) df_grouped['percentage_no_conform'] = round( 100 * (df_grouped['check_no_conform'] / df_grouped['check_number']), 2).fillna(0) df_grouped['percentage_no_conform'] = df_grouped[ 'percentage_no_conform'].apply(lambda x: ('%f' % x).rstrip('0').rstrip('.')) df_grouped['percentage_conform'] = df_grouped[ 'percentage_conform'].fillna(100) df_grouped['percentage_no_conform'] = df_grouped[ 'percentage_no_conform'].fillna(0) logger.info(df_grouped) es_helper.dataframe_to_elastic(es, df_grouped) except Exception as e: endtime = time.time() logger.error(e, exc_info=True) log_message("Process month Kizeo failed. Duration: %d Exception: %s." % ((endtime - starttime), str(e))) endtime = time.time() try: log_message("Process month Kizeo finished. Duration: %d Records: %d." % ((endtime - starttime), df_grouped.shape[0])) except: log_message("Process month Kizeo finished. Duration: %d." % ((endtime - starttime))) logger.info("<== " * 10)
def compute502barchart_v3(reporttype): print('==============> KPI502 bar graph') cur_active = es_helper.elastic_to_dataframe( es, "biac_kpi502", "active:1 AND key: \"" + reporttype + "\"") startdate = datetime.strptime(cur_active["filedate"].iloc[0], "%Y-%m") start = startdate #-timedelta(days=2) start_overdue_4 = start + relativedelta(months=-13) start_overdue_4 = str(start_overdue_4.year) + "-" + (str( start_overdue_4.month)).zfill(2) start_overdue_5 = start + relativedelta(months=-7) start_overdue_5 = str(start_overdue_5.year) + "-" + (str( start_overdue_5.month)).zfill(2) print(start_overdue_5) # startminusonemonth=start-timedelta(days=32) startminusonemonth = start - relativedelta(months=1) filedate = str(start.year) + "-" + (str(start.month)).zfill(2) filedateminusonemonth = str(startminusonemonth.year) + "-" + (str( startminusonemonth.month)).zfill(2) print("FileDate: %s" % (filedate)) queryadd = " AND key: \"" + reporttype.replace("Lot4 (DNB)", "Lot4 (BACDNB)") + "\"" if "All" in reporttype: queryadd = " AND Lot: \"Lot 2\"" print(queryadd) query = '(ShortStatusFU: Actievereist OR ValueCount:0) AND filedate:' + filedate + queryadd queryminusonemonth = '(ShortStatusFU: Actievereist OR ValueCount:0) AND filedate:' + filedateminusonemonth + queryadd print(query) print(queryminusonemonth) cur_df = es_helper.elastic_to_dataframe(es, "biac_kpi502", query) prev_df = es_helper.elastic_to_dataframe(es, "biac_kpi502", queryminusonemonth) cur_df_4 = cur_df[cur_df["ShortStatus"] == 4].copy() cur_df_4_gr = cur_df_4.groupby("MonthFU").agg({"ValueCount": ["sum"]}) cur_df_4_gr.columns = ["value"] prev_df_4 = prev_df[prev_df["ShortStatus"] == 4].copy() prev_df_4_gr = prev_df_4.groupby("MonthFU").agg({"ValueCount": ["sum"]}) prev_df_4_gr.columns = ["value"] merge_4_gr = cur_df_4_gr.merge(prev_df_4_gr, how="left", left_index=True, right_index=True) merge_4_gr.columns = ["Cur", "Prev"] merge_4_gr.fillna(0, inplace=True) merge_4_gr["Good"] = merge_4_gr["Prev"] - merge_4_gr["Cur"] merge_4_gr["Good"] = merge_4_gr["Good"].apply(lambda x: 0 if x < 0 else x) merge_4_gr["Month"] = merge_4_gr.index merge_4_gr["Month"] = merge_4_gr["Month"].apply(lambda x: " " + str(x) if x == "OVERDUE" else x) merge_4_gr = merge_4_gr.sort_values("Month", ascending=False).reset_index(drop=True) merge_4_gr.loc[merge_4_gr["Month"].str.contains("OVERDUE") > 0, "Good"] = 0 merge_4_gr["Total"] = merge_4_gr["Cur"] + merge_4_gr["Good"] cur_df_4[(cur_df_4["MonthFU"] == "OVERDUE") & (cur_df_4["Month"] == start_overdue_4)] newoverdue_4 = cur_df_4[(cur_df_4["MonthFU"] == "OVERDUE") & (cur_df_4["Month_"] == start_overdue_4)].shape[0] merge_4_gr["NewOverdue"] = 0 merge_4_gr.loc[merge_4_gr["Month"].str.contains("OVERDUE"), "NewOverdue"] = newoverdue_4 merge_4_gr.loc[ merge_4_gr["Month"].str.contains("OVERDUE"), "Cur"] = merge_4_gr.loc[merge_4_gr["Month"].str.contains("OVERDUE"), "Cur"] - newoverdue_4 cur_df_5 = cur_df[cur_df["ShortStatus"] == 5].copy() cur_df_5_gr = cur_df_5.groupby("MonthFU").agg({"ValueCount": ["sum"]}) cur_df_5_gr.columns = ["value"] prev_df_5 = prev_df[prev_df["ShortStatus"] == 5].copy() prev_df_5_gr = prev_df_5.groupby("MonthFU").agg({"ValueCount": ["sum"]}) prev_df_5_gr.columns = ["value"] merge_5_gr = cur_df_5_gr.merge(prev_df_5_gr, how="left", left_index=True, right_index=True) merge_5_gr.columns = ["Cur", "Prev"] merge_5_gr.fillna(0, inplace=True) merge_5_gr["Good"] = merge_5_gr["Prev"] - merge_5_gr["Cur"] merge_5_gr["Good"] = merge_5_gr["Good"].apply(lambda x: 0 if x < 0 else x) merge_5_gr["Month"] = merge_5_gr.index merge_5_gr["Month"] = merge_5_gr["Month"].apply(lambda x: " " + str(x) if x == "OVERDUE" else x) merge_5_gr = merge_5_gr.sort_values("Month", ascending=False).reset_index(drop=True) merge_5_gr.loc[merge_5_gr["Month"].str.contains("OVERDUE") > 0, "Good"] = 0 merge_5_gr["Total"] = merge_5_gr["Cur"] + merge_5_gr["Good"] newoverdue_5 = cur_df_5[(cur_df_5["MonthFU"] == "OVERDUE") & (cur_df_5["Month_"] == start_overdue_5)].shape[0] merge_5_gr["NewOverdue"] = 0 merge_5_gr.loc[merge_5_gr["Month"].str.contains("OVERDUE"), "NewOverdue"] = newoverdue_5 merge_5_gr.loc[ merge_5_gr["Month"].str.contains("OVERDUE"), "Cur"] = merge_5_gr.loc[merge_5_gr["Month"].str.contains("OVERDUE"), "Cur"] - newoverdue_5 generate_kibana_dash(merge_4_gr, 4, reporttype) generate_kibana_dash(merge_5_gr, 5, reporttype)
def messageReceived(destination, message, headers): global es, goodmonth records = 0 starttime = time.time() logger.info("==> " * 10) logger.info("Message Received %s" % destination) if destination == "/topic/RECOMPUTE_502": recomputeKPI502() return logger.info(headers) filepath = "" file = "" if "CamelSplitAttachmentId" in headers: headers["file"] = headers["CamelSplitAttachmentId"] if "file" in headers: logger.info("File:%s" % headers["file"]) log_message("Import of file [%s] started." % headers["file"]) file = headers["file"] #file= './tmp/Safety Register - KPI502 - Lots_1_2_3 - 2019-04.xlsx' #file="Safety Register - KPI502 - Lots_1_2_3 - 2019-0.xlsx" logger.info("FILE :" + file) filename = file logger.info("FILE NAME :" + filename) filedate = file[1:].split(".")[0][-7:] goodmonth = filedate.split("-")[1] + "-" + filedate.split("-")[0] logger.info("MONTH(BEF) :" + goodmonth) gm = datetime.strptime(goodmonth, "%m-%Y") gm = gm - relativedelta(months=1) goodmonth = datetime.strftime(gm, "%m-%Y") xlsbytes = base64.b64decode(message) f = open('./tmp/excel.xlsx', 'wb') f.write(xlsbytes) f.close() filename = './tmp/excel.xlsx' # exit() #filename = './tmp/KPI502 - Lot2- 2019-01.xlsx' #file="KPI502 - Lot2- 2019-01.xlsx" #filename = './tmp/KPI502 - Lot2 - 2019-03.xlsx' # logger.info("MONTH(AFT) :"+goodmonth) try: logger.info("Opening XLS...") dfdata = pd.DataFrame() for sheet in ["Lot 1", "Lot 2", "Lot 3", "Lot 4"]: logger.info(">>>>>>>>>> LOADING:" + sheet) try: dfd = pd.read_excel(filename, sheet_name=sheet, skiprows=2, index_col=0) except: logger.error("Unable to read sheet.") continue dfd["Sheet"] = sheet #logger.info(dfd.describe()) dfdata = dfdata.append(dfd) #,ignore_index = True) #break #dfdata=dfdata.reset_index() logger.info("Done.") #dfdata = dfdata.drop('Unnamed: 0', axis=1) dfdata = dfdata.reset_index() del dfdata["index"] #print(dfdata.columns) logger.info(dfdata.columns) #newcols=['Month','SRid','Status','Opm. number','Definition','Building','Floor','Place','Technic','Sub-technic','materials','AssetCode','Device','BACid','Frequency','Control','Report','Report Date','Reference Date','Reference Year','Reference source date','Last shipment','Repeat','Point of interest','KPI timing','GroupNum','Type','FB Name','FB date','FB','Orig label','Orig Definition','Control organism','To','Cc 1','Cc 2','Cc 3','Cc 4','Cc 5','Cc 6','Cc 7','Cc 8','Cc 9','BAC Dept','BAC Sub Dept','BAC Service','Group','Contractor','Lot','KPI Type','ShortStatus','ShortStatusFU','ConcatShortStatus&OrigStatus','LongStatus','Classification nr (O/I)','Classification (O/I)','Show graph','Report link','Status (M-1)','Report Date (M-1)','FB Date (M-1)','Deleted vs M-1','MonthFU','Sheet'] newcols = [ 'Month', 'SRid', 'Status', 'Opm. number', 'Definition', 'Building', 'Floor', 'Place', 'Technic', 'Sub-technic', 'materials', 'AssetCode', 'Device', 'BACid', 'Frequency', 'Control', 'Report', 'Report Date', 'Reference Date', 'Reference Year', 'Reference source date', 'Last shipment', 'Repeat', 'Point of interest', 'KPI timing', 'GroupNum', 'Type', 'FB Name', 'FB date', 'FB', 'Orig label', 'Orig Definition', 'Control organism', 'To', 'Cc 1', 'Cc 2', 'Cc 3', 'Cc 4', 'Cc 5', 'Cc 6', 'Cc 7', 'Cc 8', 'Cc 9', 'BAC Dept', 'BAC Sub Dept', 'BAC Service', 'Group', 'Contractor', 'Lot', 'KPI Type', 'ShortStatus', 'ShortStatusFU', 'ConcatShortStatus&OrigStatus', 'LongStatus', 'Classification nr (O/I)', 'Classification (O/I)', 'Show graph', 'Report link', 'Status (M-1)', 'Report Date (M-1)', 'FB Date (M-1)', 'Deleted vs M-1', "KPI Type Nr", "CheckArchived", 'Sheet' ] logger.info(newcols) dfdata.columns = newcols dfdata["KPI Type Nr"] = 502 dfdata["MonthFU"] = dfdata.apply(computeOverdue, axis=1) dfdata2 = dfdata.reset_index(drop=True) dfdata2.fillna('', inplace=True) dfdata2 = dfdata2.drop(dfdata2[dfdata2["Month"] == ''].index) dfdata2['_index'] = "biac_kpi502" #dfdata2['_timestamp'] = dfdata2['Reference Date'].apply(lambda x: getTimestamp(x)*1000) #dfdata2['_timestamp'] = dfdata2['Reference Date'].apply(lambda x: int(x.timestamp()*1000)) dfdata2['_timestamp'] = dfdata2.apply(lambda row: fixDate(row), axis=1) dfdata2['_id'] = dfdata2.apply( lambda row: get_id(row['Month'], row['SRid']), axis=1) dfdata2['_id'] = dfdata2['_id'].apply(lambda x: goodmonth + "-" + x) logger.info(dfdata2) filedate = file[1:].split(".")[0][-7:] dfdata2["filedate"] = filedate dfdata2['key'] = dfdata2.apply(computeReport, axis=1) dfdata2["ShortStatus"] = dfdata2["LongStatus"].apply( computeShortStatus) logger.info(len(dfdata2)) dfdata2 = dfdata2[(dfdata2["ShortStatus"] == 4) | (dfdata2["ShortStatus"] == 5)] logger.info(len(dfdata2)) dfdata2.drop_duplicates('_id', inplace=True) dfdata2["Month_"] = dfdata2["Month"] logger.info("BEFORE CLEANING") logger.info(dfdata2.shape) for x in [str(x) for x in range(2010, 2018)]: dfdata2 = dfdata2[~dfdata2["Month_"].str.contains(x)] dfdata2 = dfdata2[~dfdata2["Month_"]. isin(["2018-01", "2018-02", "2018-03", "2018-04"])] logger.info("AFTER CLEANING") logger.info(dfdata2.shape) dfdata2["Month"] = goodmonth dfdata2["ValueCount"] = 1 logger.info(filedate) #res4=compute_previous_months(int(filedate.split("-")[1]),int(filedate.split("-")[0]),12,skip=0) res4 = compute_previous_months(int(goodmonth.split("-")[0]), int(goodmonth.split("-")[1]), 12, skip=0) res4lot4 = compute_previous_months(int(goodmonth.split("-")[0]), int(goodmonth.split("-")[1]), 6, skip=0) res4table = [] for key in dfdata2['key'].unique(): print("===>" * 30) print(key) if key.startswith("Lot4"): res4touse = res4lot4 print("LOT4" * 100) else: res4touse = res4 for rec in res4touse: res4table.append({ "Month": goodmonth, "MonthFU": rec, "ShortStatus": 4, "Status": 4, "ValueCount": 0, "_id": goodmonth + rec + "-S4-" + str(hash(key)), "_index": "biac_kpi502", "key": key, "filedate": filedate }) res4tabledf = pd.DataFrame(res4table) #print(res4tabledf) es_helper.dataframe_to_elastic(es, res4tabledf) #A/0 #res5=compute_previous_months(int(filedate.split("-")[1]),int(filedate.split("-")[0]),6,skip=0) res5 = compute_previous_months(int(goodmonth.split("-")[0]), int(goodmonth.split("-")[1]), 6, skip=0) res5lot4 = compute_previous_months(int(goodmonth.split("-")[0]), int(goodmonth.split("-")[1]), 3, skip=0) res5table = [] for key in dfdata2['key'].unique(): if key.startswith("Lot4"): res5touse = res5lot4 print("LOT4" * 100) else: res5touse = res5 for rec in res5touse: res5table.append({ "Month": goodmonth, "MonthFU": rec, "ShortStatus": 5, "Status": 5, "ValueCount": 0, "_id": goodmonth + rec + "-S5-" + str(hash(key)), "_index": "biac_kpi502", "key": key, "filedate": filedate }) # DELETE OLD DATA deletequery = { "query": { "bool": { "must": [{ "query_string": { "query": "Month: \"" + goodmonth + "\"" } }] } } } logger.info( ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Deleting ORIGINALS records") logger.info(deletequery) try: resdelete = es.delete_by_query(body=deletequery, index="biac_kpi502") logger.info(resdelete) except Exception as e3: logger.info(e3) logger.info("Unable to delete records.") logger.info("Waiting for deletion to finish") time.sleep(3) # WRITE DATA res5tabledf = pd.DataFrame(res5table) es_helper.dataframe_to_elastic(es, res5tabledf) es_helper.dataframe_to_elastic(es, dfdata2) ## NOW COMPUTE MONTH logger.info("Waiting for records to be written...") time.sleep(3) deletequery = { "query": { "bool": { "must": [{ "query_string": { "query": "Month: \"" + goodmonth + "\"" } }] } } } logger.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Deleting records") logger.info(deletequery) try: resdelete = es.delete_by_query(body=deletequery, index="biac_month_kpi502") logger.info(resdelete) except Exception as e3: logger.info(e3) logger.info("Unable to delete records.") logger.info("Waiting for deletion to finish") time.sleep(3) goodmonth_dt = datetime.strptime(goodmonth, '%m-%Y') df_kpi502 = es_helper.elastic_to_dataframe( es, "biac_kpi502", query='ShortStatusFU: Actievereist AND Month: ' + goodmonth) df_kpi502_4 = df_kpi502[df_kpi502["ShortStatus"] == 4] df_kpi502_4_overdue = df_kpi502_4[df_kpi502_4["MonthFU"].str.contains( "OVERDUE")].shape[0] logger.info("4 overdue:%d" % (df_kpi502_4_overdue)) kpi502_4 = df_kpi502_4_overdue / df_kpi502_4.shape[0] kpi502_4 df_kpi502_5 = df_kpi502[df_kpi502["ShortStatus"] == 5] df_kpi502_5_overdue = df_kpi502_5[df_kpi502_5["MonthFU"].str.contains( "OVERDUE")].shape[0] kpi502_5 = df_kpi502_5_overdue / df_kpi502_5.shape[0] logger.info("5 overdue:%d" % (df_kpi502_5_overdue)) #df_kpi502_4_overdue df_kpi502.shape[0] df_kpi502_4_obj = { "total": df_kpi502_4.shape[0], "overdue": df_kpi502_4_overdue } df_kpi502_5_obj = { "total": df_kpi502_5.shape[0], "overdue": df_kpi502_5_overdue } df_kpi502 = dfdata2.copy() recs = [] recsfire = [] recsokko = [] for key in df_kpi502.key.unique(): #logger.info(key) #goodmonth=filedate.split('-')[1]+"-"+filedate.split('-')[0] newrec = { "key": key, "type": "summary", "_id": goodmonth + "_" + key, "_index": "biac_month_kpi502", "filedate": filedate, "Month": goodmonth } if key.startswith("Lot4"): limitmonth4 = (goodmonth_dt - relativedelta(months=6)).strftime('%Y-%m') limitmonth5 = (goodmonth_dt - relativedelta(months=3)).strftime('%Y-%m') else: limitmonth4 = (goodmonth_dt - relativedelta(months=12)).strftime('%Y-%m') limitmonth5 = (goodmonth_dt - relativedelta(months=6)).strftime('%Y-%m') logger.info(">>>>>KEY:" + key) logger.info(">>>>>LIMIT4:" + limitmonth4) logger.info(">>>>>LIMIT5:" + limitmonth5) df_kpi502_4 = df_kpi502[(df_kpi502["ShortStatus"] == 4) & (df_kpi502["key"] == key)] newoverdues_4 = 0 oldoverdues_4 = 0 for index, row in df_kpi502_4.iterrows(): newrec_okko = { "key": key, "type": "stat", "_index": "biac_month_kpi502", "filedate": filedate, "Month": goodmonth } if "OVERDUE" in row["MonthFU"]: if row["Month_"] == limitmonth4: newrec_okko["sub_type"] = "new_overdue_remarks" newoverdues_4 += 1 else: newrec_okko["sub_type"] = "overdue_remarks" oldoverdues_4 += 1 else: newrec_okko["sub_type"] = "ok_remarks" newrec_okko["@timestamp"] = datetime.utcnow().isoformat() recsokko.append(newrec_okko) logger.info(">>>>>" + str(df_kpi502_4[df_kpi502_4["Month_"] == limitmonth4].shape[0])) #df_kpi502_4_overdue=df_kpi502_4[df_kpi502_4["MonthFU"].str.contains("OVERDUE")].shape[0] df_kpi502_4_overdue = newoverdues_4 kpi502_4_percentage = 1 if df_kpi502_4.shape[0] - oldoverdues_4 > 0: kpi502_4_percentage = (df_kpi502_4.shape[0] - oldoverdues_4 - newoverdues_4) / (df_kpi502_4.shape[0] - oldoverdues_4) else: kpi502_4_percentage = 1 df_kpi502_4_obj = { "total": df_kpi502_4.shape[0], "overdue": df_kpi502_4_overdue, "percentage": round(kpi502_4_percentage * 100, 2) } newrec["Total_Remarks"] = df_kpi502_4.shape[0] newrec["Overdues_Remarks"] = df_kpi502_4_overdue newrec["Old_Overdues_Remarks"] = oldoverdues_4 newrec["New_Overdues_Remarks"] = newoverdues_4 newrec["KPI_Remarks"] = round(kpi502_4_percentage * 100, 2) df_kpi502_5 = df_kpi502[(df_kpi502["ShortStatus"] == 5) & (df_kpi502["key"] == key)] newoverdues_5 = 0 oldoverdues_5 = 0 for index, row in df_kpi502_5.iterrows(): newrec_okko = { "key": key, "type": "stat", "_index": "biac_month_kpi502", "filedate": filedate, "Month": goodmonth } if "OVERDUE" in row["MonthFU"]: if row["Month_"] == limitmonth5: newrec_okko["sub_type"] = "new_overdue_breaches" newoverdues_5 += 1 else: newrec_okko["sub_type"] = "overdue_breaches" oldoverdues_5 += 1 else: newrec_okko["sub_type"] = "ok_breaches" newrec_okko["@timestamp"] = datetime.utcnow().isoformat() recsokko.append(newrec_okko) #df_kpi502_5_overdue=df_kpi502_5[df_kpi502_5["MonthFU"].str.contains("OVERDUE")].shape[0] df_kpi502_5_overdue = newoverdues_5 kpi502_5_percentage = 1 if df_kpi502_5.shape[0] - oldoverdues_5 > 0: kpi502_5_percentage = (df_kpi502_5.shape[0] - oldoverdues_5 - newoverdues_5) / (df_kpi502_5.shape[0] - oldoverdues_5) else: kpi502_5_percentage = 1 # logger.info(df_kpi502_5) newrec["Total_Breaches"] = df_kpi502_5.shape[0] newrec["Overdues_Breaches"] = df_kpi502_5_overdue newrec["KPI_Breaches"] = round(kpi502_5_percentage * 100, 2) newrec["Old_Overdues_Breaches"] = oldoverdues_5 newrec["New_Overdues_Breaches"] = newoverdues_5 newrec["Total"] = df_kpi502_4.shape[0] + df_kpi502_5.shape[0] newrec["Overdues"] = df_kpi502_4_overdue + df_kpi502_5_overdue percentage = 0 if df_kpi502_4.shape[0] + df_kpi502_5.shape[0] > 0: percentage = (df_kpi502_4_overdue + df_kpi502_5_overdue) / ( df_kpi502_4.shape[0] + df_kpi502_5.shape[0]) newrec["KPI"] = round(percentage * 100, 2) if "BACFIR" not in key: recs.append(newrec) else: recsfire.append(newrec) pdfire = pd.DataFrame(recsfire) firerec = json.loads(pdfire.sum().to_json()) firerec["filedate"] = filedate firerec["Month"] = goodmonth firerec["type"] = "summary" firerec["_index"] = "biac_month_kpi502" firerec["key"] = "ALL (BACFIR)" if firerec["Total_Breaches"] - firerec["Old_Overdues_Breaches"] > 0: firerec["KPI_Breaches"] = round( 100 * (firerec["Total_Breaches"] - firerec["Old_Overdues_Breaches"] - firerec["New_Overdues_Breaches"]) / (firerec["Total_Breaches"] - firerec["Old_Overdues_Breaches"]), 2) else: firerec["KPI_Breaches"] = 100 if firerec["Total_Remarks"] > 0: firerec["KPI_Remarks"] = round( 100 * (firerec["Total_Remarks"] - firerec["Old_Overdues_Remarks"] - firerec["New_Overdues_Remarks"]) / (firerec["Total_Remarks"] - firerec["Old_Overdues_Remarks"]), 2) else: firerec["KPI_Remarks"] = 100 df_month_kpi502 = pd.DataFrame(recs + [firerec]) es_helper.dataframe_to_elastic(es, df_month_kpi502) df_month_kpi502_stats = pd.DataFrame(recsokko) es_helper.dataframe_to_elastic(es, df_month_kpi502_stats) except Exception as e: endtime = time.time() logger.error(e, exc_info=True) log_message("Import of file [%s] failed. Duration: %d Exception: %s." % (file, (endtime - starttime), str(e))) logger.info(">>>>Set Active Fields") logger.info("Reset active records ") logger.info("=====================") time.sleep(3) updatequery = { "script": { "inline": "ctx._source.active=0", "lang": "painless" }, 'query': { 'bool': { 'must': [{ 'query_string': { 'query': '*' } }] } } } logger.info("*=" * 30) logger.info(json.dumps(updatequery)) try: resupdate = es.update_by_query(body=updatequery, index="biac_kpi502") logger.info(resupdate) except Exception as e3: logger.error(e3) logger.error("Unable to update records biac_month_kpi502.") try: resupdate = es.update_by_query(body=updatequery, index="biac_month_kpi502") logger.info(resupdate) except Exception as e3: logger.error(e3) logger.error("Unable to update records biac_month_kpi502.") # Activating current month logger.info("Waiting for update...") time.sleep(3) logger.info("Done") updatequery = { "script": { "inline": "ctx._source.active=1", "lang": "painless" }, 'query': { 'bool': { 'must': [{ 'query_string': { 'query': 'Month: "' + goodmonth + '"' } }] } } } logger.info("Update active records") logger.info(updatequery) try: resupdate = es.update_by_query(body=updatequery, index="biac_kpi502") logger.info(resupdate) except Exception as e3: logger.error(e3) logger.error("Unable to update records biac_month_kpi502.") try: resupdate = es.update_by_query(body=updatequery, index="biac_month_kpi502") logger.info(resupdate) except Exception as e3: logger.error(e3) logger.error("Unable to update records biac_month_kpi502.") recomputeKPI502() endtime = time.time() try: log_message("Import of file [%s] finished. Duration: %d Records: %d." % (file, (endtime - starttime), df.shape[0])) except: log_message("Import of file [%s] finished. Duration: %d." % (file, (endtime - starttime))) logger.info("<== " * 10)
def update_kpi101_monthly(es, month, number_of_call_1=-1, number_of_call_2=-1, number_of_call_3=-1): #logger.info('update_kpi101_monthly date: '+str(month)) start_dt = mkFirstOfMonth(month) end_dt = mkLastOfMonth(month) logger.info('**' * 100) logger.info(start_dt) logger.info(end_dt) logger.info(number_of_call_1) logger.info(number_of_call_2) logger.info(number_of_call_3) df = es_helper.elastic_to_dataframe(es, index="biac_kpi101_call*", query='*', start=start_dt, end=end_dt, timestampfield='datetime') df_group = None if len(df) > 0: df_group = df.groupby('lot').agg({'_id': 'count'}) obj = { 'not_timely_answer': 0, 'not_timely_answer_1': 0, 'not_timely_answer_2': 0, 'not_timely_answer_3': 0, 'number_of_call_1': 0, 'number_of_call_2': 0, 'number_of_call_3': 0, 'percentage': 0, 'percentage_1': 0, 'percentage_2': 0, 'percentage_3': 0, } try: obj['not_timely_answer_1'] = df_group.loc[1, '_id'] except: logger.info('no value for 1') try: obj['not_timely_answer_2'] = df_group.loc[2, '_id'] except: logger.info('no value for 2') try: obj['not_timely_answer_3'] = df_group.loc[3, '_id'] except: logger.info('no value for 3') df_month = None if number_of_call_1 == -1 or number_of_call_2 == -1 or number_of_call_3 == -1: df_month = es_helper.elastic_to_dataframe(es, index="biac_kpi101_monthly*", query='*', start=start_dt, end=end_dt, timestampfield='datetime') if number_of_call_1 == -1: try: obj['number_of_call_1'] = df_month.iloc[0]['number_of_call_1'] except: obj['number_of_call_1'] = 0 else: obj['number_of_call_1'] = number_of_call_1 if number_of_call_2 == -1: try: obj['number_of_call_2'] = df_month.iloc[0]['number_of_call_2'] except: obj['number_of_call_2'] = 0 else: obj['number_of_call_2'] = number_of_call_2 if number_of_call_3 == -1: try: obj['number_of_call_3'] = df_month.iloc[0]['number_of_call_3'] except: obj['number_of_call_3'] = 0 else: obj['number_of_call_3'] = number_of_call_3 obj['not_timely_answer'] = obj['not_timely_answer_1'] + obj[ 'not_timely_answer_2'] + obj['not_timely_answer_3'] obj['number_of_call'] = obj['number_of_call_1'] + obj[ 'number_of_call_2'] + obj['number_of_call_3'] if obj['number_of_call'] != 0: obj['percentage'] = round( ((obj['number_of_call'] - obj['not_timely_answer']) / obj['number_of_call']) * 100, 2) if obj['number_of_call_1'] != 0: obj['percentage_1'] = round( ((obj['number_of_call_1'] - obj['not_timely_answer_1']) / obj['number_of_call_1']) * 100, 2) if obj['number_of_call_2'] != 0: obj['percentage_2'] = round( ((obj['number_of_call_2'] - obj['not_timely_answer_2']) / obj['number_of_call_2']) * 100, 2) if obj['number_of_call_3'] != 0: obj['percentage_3'] = round( ((obj['number_of_call_3'] - obj['not_timely_answer_3']) / obj['number_of_call_3']) * 100, 2) local_timezone = tzlocal.get_localzone() obj['datetime'] = local_timezone.localize(start_dt) _id = int(obj['datetime'].timestamp()) * 1000 res = es.index(index="biac_kpi101_monthly", doc_type='doc', id=_id, body=json.dumps(obj, cls=DateTimeEncoder)) logger.info(res) return obj
def compute305(): logger.info(">>> COMPUTE 305 STARTED") global goodmonth time.sleep(3) orgdf = es_helper.elastic_to_dataframe( es, "biac_kpi305", query="*", start=datetime.now() - timedelta(days=365), end=datetime.now() + timedelta(days=365), sort=None, timestampfield="CheckMonth") # orgdf=etp.genericIntervalSearch(es,"biac_kpi305",query="*",doctype="doc",start=datetime.now()-timedelta(days=365),end=datetime.now()+timedelta(days=365),sort=None,timestampfield="CheckMonth") months = orgdf["Month"].unique() keys = orgdf["key"].unique() logger.info(keys) alllot = {} for month in months: for key in keys: regex = r"\(([A-Z]*)\)" result = re.search(regex, key, re.MULTILINE) if result: lot = result.group(1) else: lot = "NA" if not lot + month in alllot: alllot[lot + month] = { "Positive": 0, "Negative": 0, "Month": month, "key": lot } logger.info("Loading " + month + " Key " + key) onekeydf = orgdf[(orgdf["key"] == key) & (orgdf["Month"] == month)] good = onekeydf["CountPositives"].sum() bad = onekeydf["CountNC"].sum() alllot[lot + month]["Positive"] += int(good) alllot[lot + month]["Negative"] += int(bad) logger.info("==> %d %d" % (bad, good)) # alllot[key+month]={"Positive":int(good),"Negative":int(bad),"Month":month,"key":key} bulkbody = "" es_index = "biac_month_kpi305" for key in alllot: action = {} action["index"] = { "_index": es_index, "_type": "doc", "_id": (alllot[key]["key"] + alllot[key]["Month"]).replace( "(", "").replace(")", "").replace(" ", "").lower() } alllot[key][ "Total"] = alllot[key]["Positive"] + alllot[key]["Negative"] if alllot[key]["Total"] > 0: alllot[key]["KPI"] = round( alllot[key]["Positive"] * 100 / alllot[key]["Total"], 2) else: alllot[key]["KPI"] = 100 newrec = json.dumps(alllot[key]) bulkbody += json.dumps(action) + "\r\n" bulkbody += newrec + "\r\n" res = es.bulk(bulkbody) if res["errors"]: logger.error("Error in bulk") logger.info(res) logger.info(">>>>Set Active Fields") logger.info("Reset active records ") logger.info("=====================") time.sleep(3) updatequery = { "script": { "inline": "ctx._source.active=0", "lang": "painless" }, 'query': { 'bool': { 'must': [{ 'query_string': { 'query': '*' } }] } } } logger.info("*=" * 30) logger.info(json.dumps(updatequery)) try: resupdate = es.update_by_query(body=updatequery, index="biac_kpi305") logger.info(resupdate) except Exception as e3: logger.error(e3) logger.error("Unable to update records biac_month_kpi305.") try: resupdate = es.update_by_query(body=updatequery, index="biac_month_kpi305") logger.info(resupdate) except Exception as e3: logger.error(e3) logger.error("Unable to update records biac_month_kpi305.") # Activating current month logger.info("Waiting for update...") time.sleep(3) logger.info("Done") updatequery = { "script": { "inline": "ctx._source.active=1", "lang": "painless" }, 'query': { 'bool': { 'must': [{ 'query_string': { 'query': 'Month: "' + goodmonth + '"' } }] } } } logger.info("Update active records") logger.info(updatequery) try: resupdate = es.update_by_query(body=updatequery, index="biac_kpi305") logger.info(resupdate) except Exception as e3: logger.error(e3) logger.error("Unable to update records biac_month_kpi305.") try: resupdate = es.update_by_query(body=updatequery, index="biac_month_kpi305") logger.info(resupdate) except Exception as e3: logger.error(e3) logger.error("Unable to update records biac_month_kpi305.") logger.info(">>> COMPUTE 305 FINISHED")
def checkCommentsStatus(): logger.info(">>>>>>>> CHECKING FEEDBACK STATUS") start_dt, end_dt = get_month_day_range(datetime.now() - timedelta(days=60)) end_dt = datetime.now() df = es_helper.elastic_to_dataframe( es, query="xlsx: true AND docx: true AND ((-_exists_:sent) OR sent:false)", index="biac_feedback_status", start=start_dt, end=end_dt, timestampfield="reportdate", datecolumns=['xlsx_date', 'docx_date', 'reportdate']) if not df.empty: now = datetime.now(pytz.timezone('Europe/Paris')) date_window = (now - timedelta(hours=3)) if 'xlsx_date' not in df.columns: df['xlsx_date'] = None df['xlsx_date'] = df['xlsx_date'].fillna(date_window) if 'docx_date' not in df.columns: df['docx_date'] = None df['docx_date'] = df['docx_date'].fillna(date_window) df['last_update'] = df.apply( lambda row: max(row['docx_date'], row['xlsx_date']), axis=1) df = df[df['last_update'] <= date_window] if not df.empty: for index, row in df.iterrows(): id = row["_id"] logger.info("Executing record:" + id) rec = es.get(index="biac_feedback_status", doc_type="doc", id=id)["_source"] logger.info(rec) rec["sent"] = True creds = { "token": "feedbackdispatcher", "user": { "firstname": "Feedback", "lastname": "Dispatcher", "id": "FeedbackDispatcher", "login": "******", "user": "******", "language": "en", "privileges": ["admin"] } } report = { "description": "Generates the report of the", "title": "Feedback KPI report ", "exec": "./reports/pythondef/Lot2KPI.py", "icon": "plane-departure", "output": ["docx"], "parameters": [{ "name": "param1", "title": "Date", "type": "date", "value": row['reportdate'].isoformat() }, { "name": "param2", "title": "Contract / Technic", "type": "combo", "value": row["reporttype"] }, { "name": "param3", "title": "Type", "type": "text", "value": "Final" }] } parameters_4567 = [{ "name": "param1", "title": "Date", "type": "date", "value": row['reportdate'].isoformat() }, { "name": "param2", "title": "Type", "type": "text", "value": "Final" }] report['title'] += row['reporttype'] report['description'] += row['reporttype'] if row['reporttype'] == 'Lot4 (BACDNB)': report['exec'] = './reports/pythondef/Lot4KPI.py' report['parameters'] = parameters_4567 elif row['reporttype'] == 'Lot5': report['exec'] = './reports/pythondef/Lot5KPI.py' report['parameters'] = parameters_4567 elif row['reporttype'] == 'Lot6': report['exec'] = './reports/pythondef/Lot6KPI.py' report['parameters'] = parameters_4567 elif row['reporttype'] == 'Lot7': report['exec'] = './reports/pythondef/Lot7KPI.py' report['parameters'] = parameters_4567 maanden = [ 'Januari', 'Februari', 'Maart', 'April', 'Mei', 'Juni', 'Juli', 'Augustus', 'September', 'Oktober', 'November', 'December' ] task = { "mailAttachmentName": "KPI rapport ${KPI} ${DATE}-val", "attachmentName": "KPI rapport ${KPI} ${DATE}-val", "icon": "file-excel", "mailSubject": "SLA-KPI gevalideerde rapport ${DATE} ${YEAR} ${KPI}", "mailTemplate": """ <body> <h2>KPI gevalideerde rapport ${KPI} </h2> <br/> Goedemorgen,<br/> <br/> Hierbij het gevalideerde rapport van de KPI voor ${KPI}, met inbegrip van de commentaren en de nieuwe berekende scores.<br/> Percentagewijzigingen, zoals deze tijdens de maandelijkse vergadering door BAC geaccepteerd werden, zijn in de overzichtstabel aangepast. De titel werd door 'Gevalideerd' vervangen en de naam van het rapport werd in "val" veranderd. Het gevalideerde rapport moet behouden worden. <br/> <br/> Mvg,<br/> <br/> <br/> <img border="0" width="81" height="42" style="width:.8437in;height:.4375in" id="CofelyLogo" src="cid:cofely_logo.png" alt="cofely_logo.png"> <br/> </body> """, "mailingList": [], } entity = getEntityObj(es, row["reporttype"]) for dest in entity["header"]["list"]: if "mail" in dest: task["mailingList"].append(dest["mail"]) #task=json.loads(json.dumps(task).replace("${KPI}",entity["title"]).replace("${DATE}",start_dt.strftime("%B"))) task = json.loads( json.dumps(task).replace( "${KPI}", entity["title"]).replace( "${DATE}", maanden[row['reportdate'].month - 1]).replace( "${YEAR}", str(row['reportdate'].year))) message = { "id": "id_" + str(uuid.uuid4()), "creds": creds, "report": report, "privileges": ["admin"], "task": task, "entity": entity, "mailAttachmentName": task["mailAttachmentName"], "mailSubject": task["mailSubject"] } logger.info(message) conn.send_message("/queue/NYX_REPORT_STEP1", json.dumps(message)) es.index(index="biac_feedback_status", doc_type="doc", id=id, body=rec) logger.info("<<<<<<<< CHECKING FEEDBACK STATUS")
def proceed_computation(start): logger.info('**********************************************************') logger.info('PROCEED COMPUTATION: ' + str(start)) bulkbody = '' flag = True end = start + timedelta(hours=2) while flag: if end > datetime.now(containertimezone): flag = False #bulkbody = '' df_alarms = es_helper.elastic_to_dataframe(es, index='opt_sites_data*', query=QUERY, start=start, end=end) if len(df_alarms) == 0: logger.info('no alarms on this period -> add 2 hours to end') end = end + timedelta(hours=2) else: logger.info('-' + str(start)) logger.info('-' + str(end)) logger.info('-------') df_alarms['datetime'] = pd.to_datetime(df_alarms['@timestamp'], unit="ms", utc=True) \ .dt.tz_convert('Europe/Paris') if len(df_alarms) == 0: logger.info('NO DATA') df_alarms = df_alarms.sort_values('@timestamp') df = df_alarms[[ '@timestamp', 'datetime', 'client', 'name', 'value' ]] for alarm in df['name'].unique(): #logger.info(alarm) df_filtered = df[df['name'] == alarm] if len(df_filtered) > 0: bulkbody += compute_alarms(alarm, df_filtered) if start == max(df['datetime']).to_pydatetime(): logger.info('big hole') obj = { 'start': str(start), 'end': str(end), } conn.send_message('/topic/ALARMS_MISSING_DATA', json.dumps(obj)) end = end + timedelta(hours=2) else: start = max(df['datetime']).to_pydatetime() end = start + timedelta(hours=2) if bulkbody != '': bulkres = es.bulk(bulkbody) if (not (bulkres["errors"])): logger.info("BULK done without errors.") else: for item in bulkres["items"]: if "error" in item["index"]: logger.info(item["index"]["error"]) return start
def update_month_kpi104(es, month): logger.info(month) local_timezone = tzlocal.get_localzone() start_dt = month end_dt = datetime(month.year, month.month, calendar.monthrange(month.year, month.month)[1]) logger.info('-------------') logger.info(start_dt) logger.info(end_dt) df = es_helper.elastic_to_dataframe(es, index="biac_kpi104_check*", query='*', start=start_dt, end=end_dt) logger.info('res len %d' % len(df)) max_dt = start_dt.astimezone(local_timezone) obj_past = None try: obj_past = es.get(index="biac_kpi104_monthly", doc_type='doc', id=start_dt.astimezone(local_timezone))['_source'] except elasticsearch.NotFoundError: logger.error("Unable to retrive past data") logger.error(error) obj_past = { '@timestamp': start_dt.astimezone(local_timezone), 'last_update': start_dt.astimezone(local_timezone), 'shift_number': 0, 'shift_presence': 0, 'percentage': 100 } es.index(index="biac_kpi104_monthly", doc_type='doc', id=obj_past['@timestamp'], body=json.dumps(obj_past, cls=DateTimeEncoder)) logger.info(obj_past) shift_presence = 0 if len(df) == 0: logger.info('empty data frame') else: df['dt'] = pd.to_datetime(df['@timestamp'], unit='ms', utc=True) shift_number = 0 shift_presence = 0 percentage = 100 try: shift_presence = df[df['value']]['value'].count() max_dt = max(df[df['value']]['dt']).to_pydatetime().astimezone( local_timezone) shift_number = max_dt.day * 6 percentage = 0 except: logger.info('shift_presence to 0') if obj_past['shift_number'] != 0: shift_number = 6 shift_presence = 0 percentage = 0 logger.info(max_dt) logger.info('shift_number %d ' % shift_number) logger.info('shift_presence %d ' % shift_presence) obj = { '@timestamp': start_dt.astimezone(local_timezone), 'last_update': max_dt, 'shift_number': shift_number, 'shift_presence': shift_presence, 'percentage': percentage } if shift_number != 0: obj['percentage'] = round((shift_presence * 100) / shift_number, 1) logger.info(json.dumps(obj, cls=DateTimeEncoder)) res = es.index(index="biac_kpi104_monthly", doc_type='doc', id=obj['@timestamp'], body=json.dumps(obj, cls=DateTimeEncoder)) logger.info(res)
def create_obj(df_raw, start): #DISPO DEBIT ENTREE THIOPAQ + ZOOM obj = compute_avail_debit_entry_thiopac(df_raw) obj_report_cogen = {} obj_report_cogen['entry_biogas_thiopaq_Nm3'] = obj['value'] obj_report_cogen['entry_biogas_thiopaq_kWh'] = round( obj['value'] * 6.1656, 2) obj_report_cogen['entry_biogas_thiopaq_MWh'] = round( obj['value'] * 6.1656 / 1000, 2) obj_report_cogen['entry_biogas_thiopaq_minus_120_Nm3'] = obj[ 'value_minus_120'] obj_report_cogen['entry_biogas_thiopaq_minus_120_kWh'] = round( obj['value_minus_120'] * 6.1656, 2) obj_report_cogen['entry_biogas_thiopaq_minus_120_MWh'] = round( obj['value_minus_120'] * 6.1656 / 1000, 2) obj_report_cogen['entry_biogas_thiopaq_120_220_Nm3'] = obj['value_120_220'] obj_report_cogen['entry_biogas_thiopaq_120_220_kWh'] = round( obj['value_120_220'] * 6.1656, 2) obj_report_cogen['entry_biogas_thiopaq_120_220_MWh'] = round( obj['value_120_220'] * 6.1656 / 1000, 2) obj_report_cogen['entry_biogas_thiopaq_220_330_Nm3'] = obj['value_220_330'] obj_report_cogen['entry_biogas_thiopaq_220_330_kWh'] = round( obj['value_220_330'] * 6.1656, 2) obj_report_cogen['entry_biogas_thiopaq_220_330_MWh'] = round( obj['value_220_330'] * 6.1656 / 1000, 2) obj_report_cogen['entry_biogas_thiopaq_330_600_Nm3'] = obj['value_330_600'] obj_report_cogen['entry_biogas_thiopaq_330_600_kWh'] = round( obj['value_330_600'] * 6.1656, 2) obj_report_cogen['entry_biogas_thiopaq_330_600_MWh'] = round( obj['value_330_600'] * 6.1656 / 1000, 2) #GASNAT ENRTREE COGEN entry_gasnat = compute_gasnat_entry(df_raw) obj_report_cogen['entry_gasnat_cogen_Nm3'] = entry_gasnat obj_report_cogen['entry_gasnat_cogen_kWh'] = round(entry_gasnat * 10.42, 2) obj_report_cogen['entry_gasnat_cogen_MWh'] = round( entry_gasnat * 10.42 / 1000, 2) #ENTREE BIOGASCOGEN ET CHAUDIERE entry_biogas_cogen, df_entree_biogas = compute_entry_biogas_cogen(df_raw) obj_report_cogen['entry_biogas_cogen_Nm3'] = entry_biogas_cogen obj_report_cogen['entry_biogas_cogen_kWh'] = round( entry_biogas_cogen * 6.1656, 2) obj_report_cogen['entry_biogas_cogen_MWh'] = round( entry_biogas_cogen * 6.1656 / 1000, 2) obj_report_cogen['entry_biogas_boiler_Nm3'] = round( obj_report_cogen['entry_biogas_thiopaq_Nm3'] - entry_biogas_cogen, 2) obj_report_cogen['entry_biogas_boiler_kWh'] = round( obj_report_cogen['entry_biogas_thiopaq_kWh'] - obj_report_cogen['entry_biogas_cogen_kWh'], 2) obj_report_cogen['entry_biogas_boiler_MWh'] = round( obj_report_cogen['entry_biogas_thiopaq_MWh'] - obj_report_cogen['entry_biogas_cogen_MWh'], 2) obj_report_cogen['entry_total_cogen_kWh'] = round( obj_report_cogen['entry_biogas_cogen_kWh'] + obj_report_cogen['entry_gasnat_cogen_kWh'], 2) obj_report_cogen['entry_total_cogen_MWh'] = round( obj_report_cogen['entry_biogas_cogen_MWh'] + obj_report_cogen['entry_gasnat_cogen_MWh'], 2) #CALENDAR OPEN DAYS FOR RATIOS start = containertimezone.localize(start) start_year, end_year = datetime(start.year, 1, 1), datetime(start.year, 12, 31, 23, 59, 59) df_calendar = es_helper.elastic_to_dataframe(es, start=start_year, end=end_year, index='nyx_calendar', query='type:LUTOSA', datecolumns=['date'], timestampfield='date') open_days = (end_year - start_year).days + 1 logger.info('size df_calendar: ' + str(len(df_calendar))) if len(df_calendar) > 0: open_days = df_calendar.loc[df_calendar['on'], 'on'].count() logger.info('opening days: ' + str(open_days)) target_prod_biogaz = ch.get_targets(es)['biogas'] target_prod_elec = ch.get_targets(es)['elec'] target_prod_heat = ch.get_targets(es)['heat'] target_runtime_cogen = ch.get_targets(es)['runtime'] daily_target_prod_biogaz = target_prod_biogaz / open_days daily_target_prod_elec = target_prod_elec / open_days daily_target_prod_heat = target_prod_heat / open_days daily_target_runtime_cogen = target_runtime_cogen / open_days day_on = True try: day_on = bool(df_calendar[df_calendar['date'] == start]['on'].iloc[0]) except: pass obj_report_cogen['on'] = day_on #HEURES DE FCT (DE ROTATION) COGEN percent_value = compute_avail_moteur(df_raw) obj_report_cogen['daily_avail_motor'] = round(percent_value, 2) obj_report_cogen['daily_avail_motor_hour'] = round(percent_value * 24, 2) if day_on: obj_report_cogen[ 'daily_avail_motor_target_hour'] = daily_target_runtime_cogen obj_report_cogen['daily_avail_motor_ratio_target'] = round( (obj_report_cogen['daily_avail_motor_hour'] / daily_target_runtime_cogen), 2) else: obj_report_cogen['daily_avail_motor_target_hour'] = 0 #PROD ELEC COGEN prod_elec_cogen = compute_prod_elec_cogen(df_raw) obj_report_cogen['out_elec_cogen_kWh'] = round(prod_elec_cogen, 2) obj_report_cogen['out_elec_cogen_MWh'] = round(prod_elec_cogen / 1000, 2) if day_on: obj_report_cogen['out_elec_cogen_target_MWh'] = daily_target_prod_elec obj_report_cogen['out_elec_cogen_ratio_target'] = round( obj_report_cogen['out_elec_cogen_MWh'] / obj_report_cogen['out_elec_cogen_target_MWh'], 2) else: obj_report_cogen['out_elec_cogen_target_MWh'] = 0 #PROD THERM COGEN prod_therm_cogen_ht, prod_therm_cogen_drycooler = compute_prod_therm_cogen( df_raw) prod_therm_cogen = prod_therm_cogen_ht + prod_therm_cogen_drycooler obj_report_cogen['out_therm_cogen_kWh'] = round(prod_therm_cogen, 2) obj_report_cogen['out_therm_cogen_MWh'] = round(prod_therm_cogen / 1000, 2) obj_report_cogen['out_therm_cogen_ht_kWh'] = round(prod_therm_cogen_ht, 2) obj_report_cogen['out_therm_cogen_ht_MWh'] = round( prod_therm_cogen_ht / 1000, 2) obj_report_cogen['out_therm_cogen_drycooler_kWh'] = round( prod_therm_cogen_drycooler, 2) obj_report_cogen['out_therm_cogen_drycooler_MWh'] = round( prod_therm_cogen_drycooler / 1000, 2) if day_on: obj_report_cogen['out_therm_cogen_target_MWh'] = daily_target_prod_heat obj_report_cogen['out_therm_cogen_ratio_target'] = round( obj_report_cogen['out_therm_cogen_MWh'] / obj_report_cogen['out_therm_cogen_target_MWh'], 2) else: obj_report_cogen['out_therm_cogen_target_MWh'] = 0 obj_report_cogen['out_total_cogen_kWh'] = round( obj_report_cogen['out_therm_cogen_kWh'] + obj_report_cogen['out_elec_cogen_kWh'], 2) obj_report_cogen['out_total_cogen_MWh'] = round( obj_report_cogen['out_therm_cogen_MWh'] + obj_report_cogen['out_elec_cogen_MWh'], 2) if day_on: obj_report_cogen[ 'entry_biogas_thiopaq_target_MWh'] = daily_target_prod_biogaz obj_report_cogen['entry_biogas_thiopaq_ratio_target'] = round( (obj_report_cogen['entry_biogas_thiopaq_MWh'] / obj_report_cogen['entry_biogas_thiopaq_target_MWh']), 2) else: obj_report_cogen['entry_biogas_thiopaq_target_MWh'] = 0 #RENDEMENTS if obj_report_cogen['entry_total_cogen_kWh'] == 0: obj_report_cogen['total_efficiency'] = 0 obj_report_cogen['elec_efficiency'] = 0 obj_report_cogen['therm_efficiency'] = 0 obj_report_cogen['gasnat_ratio'] = 0 obj_report_cogen['biogas_ratio'] = 0 else: obj_report_cogen['total_efficiency'] = round( obj_report_cogen['out_total_cogen_kWh'] / obj_report_cogen['entry_total_cogen_kWh'], 2) obj_report_cogen['elec_efficiency'] = round( obj_report_cogen['out_elec_cogen_kWh'] / obj_report_cogen['entry_total_cogen_kWh'], 2) obj_report_cogen['therm_efficiency'] = round( obj_report_cogen['out_therm_cogen_kWh'] / obj_report_cogen['entry_total_cogen_kWh'], 2) obj_report_cogen['gasnat_ratio'] = round( obj_report_cogen['entry_gasnat_cogen_kWh'] / obj_report_cogen['entry_total_cogen_kWh'], 2) obj_report_cogen['biogas_ratio'] = round( obj_report_cogen['entry_biogas_cogen_kWh'] / obj_report_cogen['entry_total_cogen_kWh'], 2) obj_report_cogen['total_efficiency_wo_zero'] = obj_report_cogen[ 'total_efficiency'] obj_report_cogen['therm_efficiency_wo_zero'] = obj_report_cogen[ 'therm_efficiency'] obj_report_cogen['elec_efficiency_wo_zero'] = obj_report_cogen[ 'elec_efficiency'] obj_report_cogen['gasnat_ratio_wo_zero'] = obj_report_cogen[ 'gasnat_ratio'] obj_report_cogen['biogas_ratio_wo_zero'] = obj_report_cogen[ 'biogas_ratio'] #DISPO THIOPAQ obj = compute_dispo_thiopaq(df_raw) obj_report_cogen['max_theorical_avail_thiopaq_hour'] = obj[ 'max_theorical_avail_thiopaq_hour'] obj_report_cogen['avail_thiopaq_hour'] = obj['avail_thiopaq_hour'] if day_on: obj_report_cogen['avail_thiopaq_ratio'] = obj['avail_thiopaq_ratio'] #DISPO COGEN obj = compute_dispo_cogen(df_raw) obj_report_cogen['max_theorical_avail_cogen_hour'] = obj[ 'max_theorical_avail_cogen_hour'] obj_report_cogen['avail_cogen_hour'] = obj['avail_cogen_hour'] if day_on: obj_report_cogen['avail_cogen_ratio'] = obj['avail_cogen_ratio'] #STARTS AND STOPS starts, stops = compute_starts_and_stops(df_raw) obj_report_cogen['starts'] = int(starts) obj_report_cogen['stops'] = int(stops) return obj_report_cogen
def get_kpi304_values(es, lot, tec, date): query = 'lot:' + lot if lot == '2': query += ' AND tec:' + tec print(query) containertimezone = pytz.timezone(tzlocal.get_localzone().zone) start_dt = containertimezone.localize(datetime(date.year, date.month, 1)) end_dt = containertimezone.localize( datetime(date.year, date.month, calendar.monthrange(date.year, date.month)[1], 23, 59, 59)) print(start_dt) print(end_dt) dataframe = es_helper.elastic_to_dataframe(es, index="biac_kpi304", datecolumns=["@timestamp"], query=query, start=start_dt, end=end_dt) if len(dataframe) == 0: print('dataframe empty we create in DB') default_df = pd.DataFrame(pd.date_range(start=start_dt, end=end_dt), columns=['_timestamp']) if lot == '1' or lot == '2': default_df['tech'] = 2 default_df['tech1'] = 1 default_df['tech2'] = 1 else: default_df['tech'] = 1 default_df['hoofd'] = 1 default_df['dayofweek'] = default_df['_timestamp'].dt.dayofweek default_df.loc[default_df['dayofweek'] >= 5, 'tech'] = 0 default_df.loc[default_df['dayofweek'] >= 5, 'hoofd'] = 0 default_df['total'] = default_df['tech'] + default_df['hoofd'] default_df['tec'] = tec default_df['lot'] = lot default_df['_index'] = 'biac_kpi304' default_df['_id'] = default_df['lot'] + '_' + default_df[ 'tec'] + '_' + default_df['_timestamp'].astype(str) del default_df['dayofweek'] es_helper.dataframe_to_elastic(es, default_df) default_df['_timestamp'] = default_df['_timestamp'].dt.date.astype(str) logger.info('query' * 100) thr = Timer(5, update_kib_kpi304, (es, lot, tec, date)) thr.start() return default_df.rename({ '_timestamp': '@timestamp' }, axis='columns').to_json(orient='records') else: dataframe.sort_values('@timestamp', inplace=True) dataframe['@timestamp'] = dataframe['@timestamp'].dt.date.astype(str) return dataframe.to_json(orient='records')
def messageReceived(destination,message,headers): """ Main function that reads the Excel file. """ global es starttime = time.time() logger.info("==> "*10) logger.info("Message Received %s" % destination) logger.info(headers) file_name = 'default' if "CamelSplitAttachmentId" in headers: headers["file"] = headers["CamelSplitAttachmentId"] if "file" in headers: file_name = headers["file"] logger.info("File:%s" %file_name) log_message("Import of file [%s] started." %file_name) histo_date = datetime.now().date() flag_histo = False regex = r'_([0-9]{8})\.' z = re.findall(regex, file_name) if z: flag_histo = True histo_date = datetime.strptime(z[0], "%Y%m%d").date() flag_already_histo_for_today = False try: dataframe=es_helper.elastic_to_dataframe(es,index="biac_histo_maximo" ,size=1 ,query='histo_date:'+str(histo_date)) if len(dataframe) > 0: flag_already_histo_for_today=True except: pass logger.info('Histo flag: '+str(flag_histo)) logger.info('Histo date : '+str(histo_date)) logger.info('Already histo for date: '+str(histo_date)+' -> '+str(flag_already_histo_for_today)) xlsbytes = base64.b64decode(message) f = open('./tmp/excel.xlsx', 'wb') f.write(xlsbytes) f.close() df = None try: sheet = 'WorkOrdersListBAC.rdl' df = pd.read_excel('./tmp/excel.xlsx', sheetname=sheet) newheaders = df.iloc[0] df = df[1:] df.columns = newheaders bulkbody = '' bulkres = '' reserrors= [] df.fillna(0,inplace=True) if not flag_histo: es.indices.delete(index="biac_maximo", ignore=[400, 404]) es.indices.delete(index="biac_503maximo", ignore=[400, 404]) time.sleep(3) df = df[df['Contract'] !=0] df['Contract'] = df['Contract'].apply(lambda x: x.upper()) df['lot'] = df['Contract'].apply(lambda x: set_lot(x)) df['technic'] = df.apply(lambda row: set_technic(row['Contract'], row['Pm execution team']), axis=1) df['screen_name'] = df.apply(extract_screen_name, axis=1) df[['lot', 'technic', 'Contract', 'Pm execution team']] now = datetime.now() displayStart = getDisplayStart(now) displayStop = getDisplayStop(now) start_dt_503, stop_dt_503 = getDisplayDate503() for index, row in df.iterrows(): woid = row[0] contract= row[1] worktype = row[2] wosummary = row[3] status = row[4] getTsDate = lambda cpltd: getTimestamp(cpltd) if cpltd != None and cpltd != np.nan else 0 completedDate = getTsDate(row[5]) pmExecutionTeam = row['Pm execution team'] woExecutionTeam = row['Wo execution team'] displaykpi302Start = getDisplayKPI302(now) nowts = int(now.timestamp()) display = 0 displaykpi302 = 0 row['ScheduledStart'] = row['ScheduledStart'].replace(hour=12, minute=0) if woExecutionTeam == np.nan: woExecutionTeam = 0 targetStart = getTsDate(row['TargetStart']) scheduledStart = getTsDate(row['ScheduledStart']) actualStartMax = getTsDate(row['ActualStart max']) actualStart = getTsDate(row['ActualStart']) KPI301 = row['KPI301'] assetDescription = row['Asset description'] routeDescription = row['Route description'] actualFinishMax = getTsDate(row['ActualFinish max']) actualFinish = getTsDate(row['ActualFinish']) KPI302 = row['KPI302'] KPI503 = "" KPI503computed =" " if "KPI503" in row: KPI503 = row['KPI503'] KPI503computed = row['KPI503'] overdue = 0 ispm=(worktype=="PM") issafe=(worktype=="SAFE" or worktype=="GRIS") try: if ispm or issafe: if displayStart <= scheduledStart < displayStop: display = 1 elif scheduledStart < displayStart: overdue = 1 else: if start_dt_503.timestamp() <= scheduledStart < stop_dt_503.timestamp(): display = 1 KPI503computed = 'New overdue' elif scheduledStart < start_dt_503.timestamp(): overdue = 1 KPI503computed = 'Overdue' except Exception as e: logger.warning('Scheduled start not int : ' + str(scheduledStart) + '-' + str(row['ScheduledStart'])) logger.info(e) try: if displaykpi302Start <= actualStart < nowts: displaykpi302 = 1 except Exception as e: logger.info(e) difference = now - row['ScheduledStart'] days = int(difference /timedelta(days=1)) strDays = '+'+str(days)+'j' sevendays = getTimestamp(now - timedelta(days=7)) fourthteendays = getTimestamp(now - timedelta(days=14)) if displayStart <= scheduledStart < fourthteendays: strDays = '+14j' if fourthteendays <= scheduledStart < sevendays: if KPI301 == 'Not Started NOK' or KPI301 == 'Started NOK': strDays ='+14j' else: strDays = '+7j' if strDays == '+14j' and KPI301 == 'Started OK': strDays = '+15j' future = 1 if nowts < scheduledStart: future = 0 newrec = { "woid": woid, "contract": contract, "worktype": worktype, "wosummary": wosummary, "status": status, "completedDate": completedDate*1000, "pmExecutionTeam": pmExecutionTeam, "woExecutionTeam": woExecutionTeam, "targetStart": targetStart*1000, "scheduledStart": scheduledStart*1000, "Scheduled": scheduledStart*1000, "actualStartMax": actualStartMax*1000, "Started": actualStart*1000, "future": future, "actualStart": actualStart*1000, "displayKPI301": display, "displayKPI302": displaykpi302, "technic": row.technic, "lot": row.lot, "screen_name": row.screen_name, "strDays": strDays, "KPI301": KPI301, "assetDescription": assetDescription, "routeDescription": routeDescription, "overdue": overdue, "actualFinishMax": actualFinishMax*1000, "actualFinish": actualFinish*1000, "KPI302": KPI302, "KPI503": KPI503, "KPI503computed": KPI503computed, } if not ispm or not issafe: dt = datetime.fromtimestamp(scheduledStart).date() newrec['strMonths'] = compute_str_months(dt) if not flag_histo: es_id = woid+'_'+str(scheduledStart) action = {} if ispm: action["index"] = {"_index": 'biac_maximo', "_type": "doc", "_id": es_id} elif issafe: action["index"] = {"_index": 'biac_safemaximo', "_type": "doc", "_id": es_id} else: action["index"] = {"_index": 'biac_503maximo', "_type": "doc", "_id": es_id} bulkbody += json.dumps(action)+"\r\n" bulkbody += json.dumps(newrec) + "\r\n" if not flag_already_histo_for_today: es_id = woid+'_'+str(scheduledStart)+'_'+(histo_date.strftime('%Y-%m-%d')) action = {} if ispm: action["index"] = {"_index": 'biac_histo_maximo', "_type": "doc", "_id": es_id} elif issafe: action["index"] = {"_index": 'biac_safehisto_maximo', "_type": "doc", "_id": es_id} else: action["index"] = {"_index": 'biac_503histo_maximo', "_type": "doc", "_id": es_id} newrec['histo_date_dt'] = str(histo_date) newrec['histo_date'] = histo_date.strftime('%Y-%m-%d') bulkbody += json.dumps(action)+"\r\n" bulkbody += json.dumps(newrec) + "\r\n" if len(bulkbody) > 512000: logger.info("BULK READY:" + str(len(bulkbody))) bulkres = es.bulk(bulkbody, request_timeout=30) logger.info("BULK DONE") bulkbody = '' if(not(bulkres["errors"])): logger.info("BULK done without errors.") else: for item in bulkres["items"]: if "error" in item["index"]: logger.info(item["index"]["error"]) reserrors.append( {"error": item["index"]["error"], "id": item["index"]["_id"]}) if len(bulkbody) > 0: logger.info("BULK READY FINAL:" + str(len(bulkbody))) bulkres = es.bulk(bulkbody) logger.info("BULK DONE FINAL") if(not(bulkres["errors"])): logger.info("BULK done without errors.") else: for item in bulkres["items"]: if "error" in item["index"]: logger.info(item["index"]["error"]) reserrors.append( {"error": item["index"]["error"], "id": item["index"]["_id"]}) time.sleep(3) first_alarm_ts = getTimestamp(df['ScheduledStart'].min()) last_alarm_ts = getTimestamp(df['ScheduledStart'].max()) obj = { 'start_ts': int(first_alarm_ts), 'end_ts': int(last_alarm_ts) } logger.info(obj) if not flag_histo: conn.send_message('/topic/BIAC_MAXIMO_IMPORTED', json.dumps(obj)) conn.send_message('/topic/BIAC_MAXIMOBIS_IMPORTED', json.dumps(obj)) except Exception as e: endtime = time.time() logger.error(e) log_message("Import of file [%s] failed. Duration: %d Exception: %s." % (headers["file"],(endtime-starttime),str(e))) endtime = time.time() try: log_message("Import of file [%s] finished. Duration: %d Records: %d." % (headers["file"],(endtime-starttime),df.shape[0])) except: log_message("Import of file [%s] finished. Duration: %d." % (headers["file"],(endtime-starttime))) logger.info("<== "*10)
def update_kib_kpi304(es, lot, tec, date): containertimezone = pytz.timezone(tzlocal.get_localzone().zone) start_dt = containertimezone.localize(datetime(date.year, date.month, 1)) end_dt = containertimezone.localize( datetime(date.year, date.month, calendar.monthrange(date.year, date.month)[1], 23, 59, 59)) query = 'lot:' + str(lot) + ' AND tec:' + tec logger.info('query' * 100) logger.info(query) logger.info(start_dt) logger.info(end_dt) logger.info(query) df = es_helper.elastic_to_dataframe(es, index='biac_kpi304',datecolumns=["@timestamp"]\ , query=query, start=start_dt, end=end_dt) if 'off' not in df: df['off'] = 0 df['off'] = df['off'].fillna(0) df['week_day'] = df['@timestamp'].dt.weekday logger.info(df.shape) df.head() new_arr = [] for index, row in df.iterrows(): flag_off = False if row['week_day'] == 5 or row['week_day'] == 6 or int( row['off']) == 1: flag_off = True type_list = ['hoofd', 'tech1', 'tech2'] if 'tech1' not in row or row['tech1'] != row['tech1']: type_list = ['hoofd', 'tech'] for i in type_list: obj = { 'type': i, 'lot': row['lot'], 'kpi304_technic': row['tec'], '@timestamp': row['@timestamp'], } if flag_off: obj['value'] = -1 else: obj['value'] = row[i] obj['_id'] = 'lot' + str( row['lot']) + '_' + row['tec'] + '_' + i + '_' + str( int(obj['@timestamp'].timestamp() * 1000)) if obj['type'] == 'hoofd': obj['type_nl'] = 'Verantwoordelijke' elif obj['type'] == 'tech': obj['type_nl'] = 'Technieker' elif obj['type'] == 'tech1': obj['type_nl'] = 'Technieker 1' elif obj['type'] == 'tech2': obj['type_nl'] = 'Technieker 2' new_arr.append(obj) df_to_push = pd.DataFrame(new_arr) df_to_push['_index'] = 'biac_kib_kpi304' logger.info(df_to_push.shape) es_helper.dataframe_to_elastic(es, df_to_push)
def compute_targets(year): global es target_prod_biogaz = ch.get_targets(es)['biogas'] target_prod_elec = ch.get_targets(es)['elec'] target_prod_heat = ch.get_targets(es)['heat'] target_runtime_cogen = ch.get_targets(es)['runtime'] start, end = datetime(year, 1, 1), datetime(year, 12, 31, 23, 59, 59) logger.info(start) logger.info(end) df_calendar = es_helper.elastic_to_dataframe(es, start=start, end=end, index='nyx_calendar', query='type:LUTOSA', datecolumns=['date'], timestampfield='date') open_days = (datetime(year, 12, 31) - datetime(year, 1, 1)).days + 1 logger.info('size df_calendar: ' + str(len(df_calendar))) if len(df_calendar) > 0: open_days = df_calendar.loc[df_calendar['on'], 'on'].count() # df_calendar['date']=pd.to_datetime(df_calendar['date'], utc=True).dt.tz_convert('Europe/Paris').dt.date df_calendar['date'] = pd.to_datetime(df_calendar['date']).dt.date del df_calendar['_id'] del df_calendar['_index'] logger.info('opening days: ' + str(open_days)) daily_target_prod_biogaz = target_prod_biogaz / open_days daily_target_prod_elec = target_prod_elec / open_days daily_target_prod_heat = target_prod_heat / open_days daily_target_runtime_cogen = target_runtime_cogen / open_days df_daily_lutosa = es_helper.elastic_to_dataframe( es, start=start, end=end, index='daily_cogen_lutosa', datecolumns=['@timestamp'], query='*') if len(df_daily_lutosa) == 0: return None if 'on' in df_daily_lutosa.columns: del df_daily_lutosa['on'] df_daily_lutosa['date'] = pd.to_datetime( df_daily_lutosa['@timestamp']).dt.date if len(df_calendar) > 0: logger.info('merge calendar') df_daily_lutosa = df_daily_lutosa.merge(df_calendar[['date', 'on']], left_on='date', right_on='date', how='left') df_daily_lutosa['entry_biogas_thiopaq_target_MWh'] = 0 df_daily_lutosa.loc[ df_daily_lutosa['on'], 'entry_biogas_thiopaq_target_MWh'] = daily_target_prod_biogaz df_daily_lutosa.loc[ df_daily_lutosa['on'], 'entry_biogas_thiopaq_ratio_target'] = round( (df_daily_lutosa.loc[df_daily_lutosa['on'], 'entry_biogas_thiopaq_MWh'] / df_daily_lutosa.loc[df_daily_lutosa['on'], 'entry_biogas_thiopaq_target_MWh']), 2) df_daily_lutosa['daily_avail_motor_target_hour'] = 0 df_daily_lutosa.loc[ df_daily_lutosa['on'], 'daily_avail_motor_target_hour'] = daily_target_runtime_cogen df_daily_lutosa.loc[ df_daily_lutosa['on'], 'daily_avail_motor_ratio_target'] = round( (df_daily_lutosa.loc[df_daily_lutosa['on'], 'daily_avail_motor_hour'] / df_daily_lutosa.loc[df_daily_lutosa['on'], 'daily_avail_motor_target_hour']), 2) df_daily_lutosa['out_elec_cogen_target_MWh'] = 0 df_daily_lutosa.loc[df_daily_lutosa['on'], 'out_elec_cogen_target_MWh'] = daily_target_prod_elec df_daily_lutosa.loc[df_daily_lutosa['on'], 'out_elec_cogen_ratio_target'] = round( (df_daily_lutosa.loc[df_daily_lutosa['on'], 'out_elec_cogen_MWh'] / df_daily_lutosa.loc[df_daily_lutosa['on'], 'out_elec_cogen_target_MWh']), 2) df_daily_lutosa['out_therm_cogen_target_MWh'] = 0 df_daily_lutosa.loc[df_daily_lutosa['on'], 'out_therm_cogen_target_MWh'] = daily_target_prod_heat df_daily_lutosa.loc[df_daily_lutosa['on'], 'out_therm_cogen_ratio_target'] = round(( df_daily_lutosa.loc[df_daily_lutosa['on'], 'out_therm_cogen_MWh'] / df_daily_lutosa.loc[df_daily_lutosa['on'], 'out_therm_cogen_target_MWh']), 2) if 'on_x' in df_daily_lutosa.columns: del df_daily_lutosa['on_x'] if 'on_y' in df_daily_lutosa.columns: del df_daily_lutosa['on_y'] if 'weekday' in df_daily_lutosa.columns: del df_daily_lutosa['weekday'] if 'date' in df_daily_lutosa.columns: del df_daily_lutosa['date'] if 'type' in df_daily_lutosa.columns: del df_daily_lutosa['type'] df_daily_lutosa.loc[df_daily_lutosa['on'] == False, 'out_therm_cogen_ratio_target'] = np.nan df_daily_lutosa.loc[df_daily_lutosa['on'] == False, 'out_elec_cogen_ratio_target'] = np.nan df_daily_lutosa.loc[df_daily_lutosa['on'] == False, 'daily_avail_motor_ratio_target'] = np.nan df_daily_lutosa.loc[df_daily_lutosa['on'] == False, 'entry_biogas_thiopaq_ratio_target'] = np.nan df_daily_lutosa.loc[df_daily_lutosa['on'] == False, 'avail_cogen_ratio'] = np.nan df_daily_lutosa.loc[df_daily_lutosa['on'] == False, 'avail_thiopaq_ratio'] = np.nan es_helper.dataframe_to_elastic(es, df_daily_lutosa)