def regen_time():
try:
view_conn = None
view_conn = config.get_connection(config.db_config)
date_1, date_2 = config.get_last_month_dates()
# pd.set_option('display.max_columns', None)
df = get_regen(view_conn, date_1, date_2)
df = df.drop([
'DEPT_ID', 'STATION', 'SPECODE', 'MEDIA_SIZE', 'THICKNESS_TYPE',
'ID', 'OPR_TIME', 'CODE', 'ERROR_DESC', 'PROD_COMMENT',
'ERR_TYPE_ID', 'ERR_TYPE_DESC', 'ERR_ID', 'ERR_DESC', 'ISSUE',
'CHAMBER_NAME', 'UPDATED_DATE', 'UPDATED_BY', 'YEAR', 'MONTH',
'MONTHLY_OPR_TIME', 'MONTHLY_DOWNTIME', 'MONTHLY_DOWNTIME_PCT',
'MONTHLY_DOWNTIME_MU_PCT'
],
axis=1)
# header = df.columns.values.tolist()
df['PROD_DATE'] = pd.to_datetime(df['PROD_DATE'])
df['STOP_TIME'] = pd.to_datetime(
df.groupby(['PROD_DATE', 'LINE_ID'])['STOP_TIME'].transform(min))
df['START_TIME'] = pd.to_datetime(
df.groupby(['PROD_DATE', 'LINE_ID'])['START_TIME'].transform(max))
df1 = df.groupby([
'PROD_DATE', 'LINE_ID', 'PROGRAM', 'DISK_TYPE', 'STOP_TIME',
'START_TIME'
],
as_index=False).agg({'DURATION': 'sum'})
df2 = df1[df1['STOP_TIME'].dt.time < pd.to_datetime('08:30:00').time()]
df2['CHECK'] = pd.to_timedelta('08:30:00') - pd.to_timedelta(
df2['STOP_TIME'].dt.time.astype(str))
df2['CHECK'] = df2['CHECK'] / pd.to_timedelta(1, unit='m')
df2['DURATION'] = df2['DURATION'] - df2['CHECK']
df2 = df2.drop(['CHECK'], axis=1)
df1.update(df2)
df1.DURATION = df1.DURATION.round(decimals=2)
df1 = df1[(df1['DURATION'] > 0)]
df1['HOURS'] = df1.DURATION.apply(lambda x: (x / 60)).round(decimals=2)
df1.rename(columns={
'PROD_DATE': 'Date',
'LINE_ID': 'Line',
'PROGRAM': 'Program',
'DISK_TYPE': 'Disk Type'
},
inplace=True)
df1["Line"] = pd.to_numeric(df1["Line"], downcast='integer')
df1['Date'] = df1['Date'].dt.strftime('%d-%b')
# raw = df1
# df1 = df1.drop(['START_TIME', 'STOP_TIME'], axis=1)
df1["Target"] = 9
# clean = df1[['Date', 'Line', 'Hours', 'Target', 'Program', 'Disk Type',
# 'DURATION']]
# df1.to_csv('MUS.csv', index=False)
# print(df1)
# df1.to_excel("clean1.xlsx", index=False, sheet_name='Regen Report')
except Exception as e:
raise e
finally:
if view_conn is not None:
view_conn.close()
return df1
def get_regen_dates():
view_conn = None
view_conn = config.get_connection(config.db_config)
date_1, date_2 = config.get_last_month_dates()
# pd.set_option('display.max_columns', None)
df = get_regen(view_conn, date_1, date_2)
listy = df.PROD_DATE.unique()
return listy
def get_avg():
# Calculate avg and upload to DB and retrieve 12 month data
# return df
listo = config.get_last_month_dates()
avg = round(clean["Hours"].mean(), 2)
count = clean["Hours"].count()
listy = [listo[0], avg, count]
myPg = None
try:
myPg = Postgres("128.53.1.198/5432", "spt_db", "spt_admin", "sptadmin")
df1 = myPg.query(f"SELECT * FROM regen.timing where date ="
f" '{listo[0].replace('/', '-')}' ")[0]
if df1.empty:
# print(df1)
myPg.connect()
print("Connection succeeded..")
# ['2016-08-01,16.5,6', '2016-09-01,15.71,7']
vals = [",".join(str(bit) for bit in listy)]
for i in vals:
y = i.split(",")
x = tuple(y)
strSQL = f"INSERT INTO regen.timing values {x}"
myPg.execute(strSQL)
myPg.commit()
else:
print('exist!')
except Exception as err:
print(f"Upload Error Occured: {err}")
finally:
df = myPg.query(
"SELECT * FROM regen.timing "
"WHERE date >= date_trunc('month', now()) - interval '13 month' and"
" date < date_trunc('month', now())")[0]
# df.to_csv('pastdatatab.csv')
if myPg is not None:
myPg.close()
return df
import pandas as pd
import numpy as np
import re
from modules import config, mus_regen
# import mus_regen
# import config
view_conn = config.get_connection(config.db_config)
date_1, date_2 = config.get_last_month_dates()
def extract_leak(words):
"""Remove punctuation from list of tokenized words"""
new_words = []
number_list = []
stringy = words.splitlines()
list_ = ['leak']
list1_ = ['ok', 'no', 'any']
stringx = []
for i, word in enumerate(stringy):
# remove puntuation and
new_word = re.sub(r'[^\w\s\'\"]', ' ', word).strip().lower()
# change
new_word = re.sub(r'^chg', 'Activities', new_word)
# remove double space
new_word = re.sub(re.compile(r' +'), ' ', new_word)
stringx.append(new_word)
if any(word in new_word for word in list_) and all(word not in new_word
for word in list1_):
new_words.append(new_word)
number_list.append(i)