def paris_df(res, units, date, distribution='Worldwide'): engine = create_engine('sqlite:///../PI.sqlite') plants = ('SP5 ', 'ISA1 ', 'ISA2 ', 'CISA1', 'CISA2', 'TISA2') df = pd.DataFrame() for indicator in ('CISA2', 'ISA2 ', 'CRE ', 'CY ', 'FLR ', 'GRLF ', 'SP1 ', 'SP2 ', 'SP5 ', 'UA7 ', 'UCF ', 'UCLF ', 'US7 '): print('.',end='') for u in units: active = x.active_unit(date).LocId.tolist() # all active units and plants centre = f.centre(x.cnt(u), date, False) # list of units in the same centre reactor = pd.read_sql('select D.LocId from PI_NsssTypeLookup T inner join PI_UnitData D \ on T.NsssTypeId=D.NsssTypeId where T.NsssType="{}"'.format(x.reactor(u, False)), engine).LocId ar = set(reactor) & set(active) r = set([f.station(u, date) for u in ar]) r = set(reactor) | set(r) # TODO: to be checked RandC = set(centre) & set(r) distr = active if distribution=='Worldwide' else r if distribution=='Same reactor type' \ else RandC if distribution=='Same reactor type and RC' else None Id = f.station(u,date) if indicator in plants else u if distr is not None: df = df.append(quartile(res, Id, indicator, distr), ignore_index=False) else: sys.exit(3) if Id != u: break return df
def analyseTime(qtr, centreCode): """ :param qtr: YYYYMM :param centreCode: a letter of centre :return: median and mean time were spending for analysis """ req = 'select julianday(ProductionDate)-julianday(SubmittalDate) from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\ .format(f.centre(centreCode, qtr), qtr) return pd.read_sql(req, engine).median(), pd.read_sql(req, engine).mean()
def DES(date, uList=None): units = f.centre(uList, date) if uList is not None else 0 req = 'select SourceId, ElementCode, ElementValue from PI_IndValues where SourceId in ({}) and SourceCode = " " ' \ 'and YrMn = {}'.format(units,date) res = pd.read_sql(req, engine) res.SourceId, res.ElementCode = res.SourceId.apply( name), res.ElementCode.apply(eName) res['Date'] = date return res
def pi2(qtr, centreCode, plt=False): req = 'select max(julianday(ProductionDate)-julianday("{}")) from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\ .format(endOfQtr(qtr), f.centre(centreCode, qtr), qtr) maxUnit = pd.read_sql(req, engine).values[0][0] req = 'select avg(julianday(ProductionDate)-julianday("{}")) from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\ .format(endOfQtr(qtr), f.centre(centreCode, qtr), qtr) avg = pd.read_sql(req, engine).values[0][0] req = 'select julianday(ProductionDate)-julianday("{}") from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\ .format(endOfQtr(qtr), f.centre(centreCode, qtr), qtr) d = pd.read_sql(req, engine) med = d.median()[0] req = 'select sum((julianday(ProductionDate)-julianday("{}"))>60) from PI_DataSubmittal where LocId in ({}) and YrMn = {}'\ .format(endOfQtr(qtr), f.centre(centreCode, qtr, True, True), qtr) numOfOut = pd.read_sql(req, engine).values[0][0] if plt: return d.hist(label='PI-2 for ' + centreCode) return maxUnit, avg, med, numOfOut
def SOER(date, uList='T'): tqdm.pandas() units = f.centre(uList, date) if uList is not None else 0 uid = pd.read_sql( 'select UnitID ,UnitName from CORE_Unit where INPORef in ({})'.format( units), engine) visit = pd.read_sql( 'select UnitID, VisitID, VisitUnitID from CORE_VisitUnit', engine) visit = visit[visit.UnitID.isin(uid.UnitID)] v = pd.read_sql( 'select VisitID, VisitTypeID, StartDate, EndDate, YearFilter from CORE_Visit', engine) visit = visit.merge(v, on='VisitID') visit = visit[visit.YearFilter == int(str(date)[:4])].drop_duplicates( subset='VisitID') soer = pd.read_sql('select VisitSOERID, VisitID from SITS_VisitSOER', engine) visit = visit.merge(soer, on='VisitID') visit['VisitSOERRecID'] = visit.VisitSOERID.progress_apply( lambda recID: pd.read_sql( 'select VisitSOERRecID from SITS_VisitSOERRec where VisitSOERID={}' .format(recID), engine).VisitSOERRecID.values[0]) #visit['SOERSubRecID'] = visit.VisitSOERID.progress_apply(lambda recID: pd.read_sql( # 'select SOERSubRecID from SITS_VisitSOERSubRec where VisitSOERID={}'.format(recID), # engine).SOERSubRecID.values[0]) #visit['SOERSubRecCode'] = visit.SOERSubRecID.progress_apply(lambda recID: pd.read_sql( # 'select SOERSubRecCode from CORE_SOERSubRec where SOERRecID={}'.format(recID), # engine).SOERSubRecCode.values[0]) #visit['HasClassification'] = visit.SOERSubRecID.progress_apply(lambda recID: pd.read_sql( # 'select HasClassification from CORE_SOERSubRec where SOERRecID={}'.format(recID), # engine).HasClassification.values[0]) #visit['SOERID'] = visit.SOERID.progress_apply(lambda recID: pd.read_sql( # 'select SOERCode from CORE_SOER where SOERID={}'.format(recID), # engine).SOERCode.values[0]) #visit['SOERCode'] = visit.SOERID.progress_apply(lambda recID: pd.read_sql( # 'select SOERCode from CORE_SOER where SOERID={}'.format(recID), # engine).SOERCode.values[0]) visit['Notes'] = visit.VisitSOERRecID.progress_apply( lambda visitID: pd.read_sql( 'select Notes from SITS_VisitSOERSubRec where VisitSOERRecID={}'. format(visitID), engine).Notes.values[0]) visit['OECTNotes'] = visit.VisitSOERRecID.progress_apply( lambda visitID: pd.read_sql( 'select OECTNotes from SITS_VisitSOERSubRec where VisitSOERRecID={}' .format(visitID), engine).OECTNotes.values[0]) # replace UID with name visit.UnitID = visit.UnitID.progress_apply( lambda id: uid[uid.UnitID == id].UnitName.values[0]) visit.VisitTypeID = visit.VisitTypeID.progress_apply( lambda id: pd.read_sql( 'select VisitTypeCode from CORE_VisitType where VisitTypeID={}'. format(id), engine).VisitTypeCode.values[0]) visit = visit.drop(columns=[ 'VisitID', 'VisitUnitID', 'YearFilter', 'VisitSOERID', 'VisitSOERRecID' ]) print(list(visit)) ''' print(visit.tail(), '\nlen=', len(visit)) soer = pd.read_sql('select VisitSOERRecID, SOERRecID, CreateDate from SITS_VisitSOERRec', engine).drop_duplicates() soer = soer[soer.SOERRecID.isin(visit.SOERRecID)].drop_duplicates() visit = visit.merge(soer, on='SOERRecID').drop_duplicates() print(visit.tail(), '\nlen=', len(visit)) soer = pd.read_sql('select VisitSOERRecID, Notes, OECTNotes from SITS_VisitSOERSubRec', engine).drop_duplicates() visit = visit.merge(soer, on='VisitSOERRecID').drop_duplicates() visit.UnitID = visit.UnitID.progress_apply(lambda id: uid[uid.UnitID == id].UnitName.values[0]) visit['VisitSOERID'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql( 'select VisitSOERID from SITS_VisitSOER where VisitID={} and VisitSOERID is not null'.format(visitID), engine).VisitSOERID.values) visit['VisitSOERID'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql( 'select VisitSOERID from SITS_VisitSOER where VisitID={} and VisitSOERID is not null'.format(visitID), engine).VisitSOERID.values) visit['Notes'] = visit.VisitSOERRecID.progress_apply(lambda visitID: pd.read_sql( 'select Notes from SITS_VisitSOERSubRec where VisitSOERRecID={}'.format(visitID), engine).Notes.values[0]) visit['OECTNotes'] = visit.VisitSOERRecID.progress_apply(lambda visitID: pd.read_sql( 'select OECTNotes from SITS_VisitSOERSubRec where VisitSOERRecID={}'.format(visitID), engine).OECTNotes.values[0]) visit['VisitType'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql( 'select VisitTypeID from CORE_Visit where VisitID={}'.format(visitID), engine).VisitTypeID.values[0]) visit.VisitType = visit.VisitType.progress_apply(lambda visitTypeID: pd.read_sql( 'select VisitType from CORE_VisitType where VisitTypeID={}'.format(visitTypeID), engine).VisitType.values[0]) visit['YearFilter'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql( 'select YearFilter from CORE_Visit where VisitID={}'.format(visitID), engine).YearFilter.values[0]) year = int(str(date)[:4]) visit = visit[visit.YearFilter==year] visit['StartDate'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql( 'select StartDate from CORE_Visit where VisitID={}'.format(visitID), engine).StartDate.values[0]) visit['EndDate'] = visit.VisitID.progress_apply(lambda visitID: pd.read_sql( 'select EndDate from CORE_Visit where VisitID={}'.format(visitID), engine).EndDate.values[0]) print(visit.tail()) ''' print(visit.head()) return visit