In [16]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
Read data. There are two datasets: Planon and Master. The latter is the EIS data nomencalture that was created. Master is made up of two subsets: loggers and meters. Loggers are sometimes called controllers and meters are sometimes called sensors. In rare cases meters or sensors are also called channels.
In [17]:
planon=pd.read_excel('EIS Assets.xlsx',index_col = 'Code')
master_loggerscontrollers = pd.read_csv('LoggersControllers.csv', index_col = 'Asset Code')
master_meterssensors = pd.read_csv('MetersSensors.csv', encoding = 'macroman', index_col = 'Asset Code')
planon['Code']=planon.index
master_loggerscontrollers['Code']=master_loggerscontrollers.index
master_meterssensors['Code']=master_meterssensors.index
Unify index, caps everything and strip of trailing spaces.
In [18]:
planon.index=[str(i).upper().strip() for i in planon.index]
master_loggerscontrollers.index=[str(i).upper().strip() for i in master_loggerscontrollers.index]
master_meterssensors.index=[str(i).upper().strip() for i in master_meterssensors.index]
Drop duplicates (shouldn't be any)
In [19]:
planon.drop_duplicates(inplace=True)
master_loggerscontrollers.drop_duplicates(inplace=True)
master_meterssensors.drop_duplicates(inplace=True)
Split Planon import into loggers and meters
Drop duplicates (shouldn't be any)
In [20]:
# Split the Planon file into 2, one for loggers & controllers, and one for meters & sensors.
planon_loggerscontrollers = planon.loc[(planon['Classification Group'] == 'EN.EN4 BMS Controller') | (planon['Classification Group'] == 'EN.EN1 Data Logger')]
planon_meterssensors = planon.loc[(planon['Classification Group'] == 'EN.EN2 Energy Meter') | (planon['Classification Group'] == 'EN.EN3 Energy Sensor')]
planon_loggerscontrollers.drop_duplicates(inplace=True)
planon_meterssensors.drop_duplicates(inplace=True)
Index unique? show number of duplicates in index
In [22]:
len(planon_loggerscontrollers.index[planon_loggerscontrollers.index.duplicated()])
Out[22]:
In [23]:
len(planon_meterssensors.index[planon_meterssensors.index.duplicated()])
Out[23]:
Meters are not unique. This is becasue of the spaces served. This is ok for now, we will deal with duplicates at the comparison stage. Same is true for loggers - in the unlikely event that there are duplicates in the future.
In [24]:
planon_meterssensors.head(3)
Out[24]:
Create list of all buildings present in Planon export. These are buildings to check the data against from Master.
In [25]:
buildings=set(planon_meterssensors['BuildingNo.'])
buildings
Out[25]:
In [26]:
len(buildings)
Out[26]:
Create dataframe slice for validation from master_meterssensors where the only the buildings located in buildings are contained. Save this new slice into master_meterssensors_for_validation. This is done by creating sub-slices of the dataframe for each building, then concatenating them all together.
In [27]:
master_meterssensors_for_validation = \
pd.concat([master_meterssensors.loc[master_meterssensors['Building Code'] == building] \
for building in buildings])
master_meterssensors_for_validation.head(2)
Out[27]:
In [28]:
master_meterssensors_for_validation.loc['MC202-B15/F15']
Out[28]:
In [29]:
#alternative method
master_meterssensors_for_validation2 = \
master_meterssensors[master_meterssensors['Building Code'].isin(buildings)]
master_meterssensors_for_validation2.head(2)
Out[29]:
Planon sensors are not unique because of the spaces served convention in the two data architectures. The Planon architecture devotes a new line for each space served - hence the not unique index. The Master architecture lists all the spaces only once, as a list, therefore it has a unique index. We will need to take this into account and create matching dataframe out of planon for comparison, with a unique index.
In [31]:
len(master_meterssensors_for_validation)
Out[31]:
In [32]:
len(planon_meterssensors)-len(planon_meterssensors.index[planon_meterssensors.index.duplicated()])
Out[32]:
Sort datasets after index for easier comparison.
In [33]:
master_meterssensors_for_validation.sort_index(inplace=True)
planon_meterssensors.sort_index(inplace=True)
In [35]:
planon_meterssensors.T
Out[35]:
In [36]:
master_meterssensors_for_validation.T
Out[36]:
Create dictionary that maps Planon column names onto Master.
From Nicola:
Building code and Building name are implicitly included. Logger Serial Number, IP or MAC would be essential to include, as well as Make and Model. Additional Location Info is not essnetial but would be useful to have. Locations (Locations.Space.Space number and Space Name) are included in the Planon export - but this is their only viable data source, therefore are not validated against.
In [37]:
#Planon:Master
meters_match_dict={
"BuildingNo.":"Building Code",
"Building":"Building Name",
"Description":"Description",
"EIS ID":"Logger Channel",
"Tenant Meter.Name":"Tenant meter",
"Fiscal Meter.Name":"Fiscal meter",
"Code":"Code"
}
Filter both dataframes based on these new columns. Then remove duplicates. Currently, this leads to loss of information of spaces served, but also a unique index for the Planon dataframe, therefore bringing the dataframes closer to each other. When including spaces explicitly in the comparison (if we want to - or just trust the Planon space mapping), this needs to be modified.
In [38]:
master_meterssensors_for_validation_filtered=master_meterssensors_for_validation[list(meters_match_dict.values())]
planon_meterssensors_filtered=planon_meterssensors[list(meters_match_dict.keys())]
In [39]:
pd.DataFrame(master_meterssensors_for_validation_filtered.loc['MC202-B15/F15'])
Out[39]:
In [40]:
master_meterssensors_for_validation_filtered.head(2)
Out[40]:
In [41]:
planon_meterssensors_filtered.head(2)
Out[41]:
Unify headers, drop duplicates (bear the mind the spaces argument, this where it needs to be brought back in in the future!).
In [42]:
planon_meterssensors_filtered.columns=[meters_match_dict[i] for i in planon_meterssensors_filtered]
In [43]:
planon_meterssensors_filtered.drop_duplicates(inplace=True)
master_meterssensors_for_validation_filtered.drop_duplicates(inplace=True)
In [44]:
planon_meterssensors_filtered.head(2)
Out[44]:
Fiscal/Tenant meter name needs fixing from Yes/No and 1/0.
In [45]:
planon_meterssensors_filtered['Fiscal meter']=planon_meterssensors_filtered['Fiscal meter'].isin(['Yes'])
planon_meterssensors_filtered['Tenant meter']=planon_meterssensors_filtered['Tenant meter'].isin(['Yes'])
master_meterssensors_for_validation_filtered['Fiscal meter']=master_meterssensors_for_validation_filtered['Fiscal meter'].isin([1])
master_meterssensors_for_validation_filtered['Tenant meter']=master_meterssensors_for_validation_filtered['Tenant meter'].isin([1])
In [46]:
master_meterssensors_for_validation_filtered.head(2)
Out[46]:
In [47]:
planon_meterssensors_filtered.head(2)
Out[47]:
Cross-check missing meters
In [48]:
a=np.sort(list(set(planon_meterssensors_filtered.index)))
b=np.sort(list(set(master_meterssensors_for_validation_filtered.index)))
meterssensors_not_in_planon=[]
for i in b:
if i not in a:
print(i+',',end=" "),
meterssensors_not_in_planon.append(i)
print('\n\nMeters in Master, but not in Planon:',
len(meterssensors_not_in_planon),'/',len(b),':',
round(len(meterssensors_not_in_planon)/len(b)*100,3),'%')
In [52]:
#without MC210
len(set([i for i in meterssensors_not_in_planon if i[:5]!='MC210']))
Out[52]:
In [49]:
set([i for i in meterssensors_not_in_planon if i[:5]!='MC210'])
Out[49]:
In [51]:
(set([i[:5] for i in meterssensors_not_in_planon]))
Out[51]:
In [53]:
a=np.sort(list(set(planon_meterssensors_filtered.index)))
b=np.sort(list(set(master_meterssensors_for_validation_filtered.index)))
meterssensors_not_in_master=[]
for i in a:
if i not in b:
print(i+',',end=" "),
meterssensors_not_in_master.append(i)
print('\n\nMeters in Planon, not in Master:',
len(meterssensors_not_in_master),'/',len(a),':',
round(len(meterssensors_not_in_master)/len(a)*100,3),'%')
In [54]:
len(set([i for i in meterssensors_not_in_master]))
Out[54]:
In [55]:
set([i[:9] for i in meterssensors_not_in_master])
Out[55]:
In [56]:
set([i[:5] for i in meterssensors_not_in_master])
Out[56]:
Check for duplicates in index, but not duplicates over the entire row
In [57]:
print(len(planon_meterssensors_filtered.index))
print(len(set(planon_meterssensors_filtered.index)))
print(len(master_meterssensors_for_validation_filtered.index))
print(len(set(master_meterssensors_for_validation_filtered.index)))
In [58]:
master_meterssensors_for_validation_filtered[master_meterssensors_for_validation_filtered.index.duplicated()]
Out[58]:
The duplicates are the nans. Remove these for now. Could revisit later to do an index-less comparison, only over row contents.
In [59]:
good_index=[i for i in master_meterssensors_for_validation_filtered.index if str(i).lower().strip()!='nan']
master_meterssensors_for_validation_filtered=master_meterssensors_for_validation_filtered.loc[good_index]
master_meterssensors_for_validation_filtered.drop_duplicates(inplace=True)
In [60]:
len(planon_meterssensors_filtered)
Out[60]:
In [61]:
len(master_meterssensors_for_validation_filtered)
Out[61]:
Do comparison only on common indices. Need to revisit and identify the cause missing meters, both ways (5 Planon->Meters and 30 Meters->Planon in this example).
In [62]:
comon_index=list(set(master_meterssensors_for_validation_filtered.index).intersection(set(planon_meterssensors_filtered.index)))
In [63]:
len(comon_index)
Out[63]:
In [64]:
master_meterssensors_for_validation_intersected=master_meterssensors_for_validation_filtered.loc[comon_index].sort_index()
planon_meterssensors_intersected=planon_meterssensors_filtered.loc[comon_index].sort_index()
In [65]:
len(master_meterssensors_for_validation_intersected)
Out[65]:
In [66]:
len(planon_meterssensors_intersected)
Out[66]:
Still have duplicate indices. For now we just drop and keep the first.
In [67]:
master_meterssensors_for_validation_intersected = master_meterssensors_for_validation_intersected[~master_meterssensors_for_validation_intersected.index.duplicated(keep='first')]
In [68]:
master_meterssensors_for_validation_intersected.head(2)
Out[68]:
In [69]:
planon_meterssensors_intersected.head(2)
Out[69]:
In [70]:
planon_meterssensors_intersected==master_meterssensors_for_validation_intersected
Out[70]:
In [71]:
np.all(planon_meterssensors_intersected==master_meterssensors_for_validation_intersected)
Out[71]:
Number of cells matching
In [72]:
(planon_meterssensors_intersected==master_meterssensors_for_validation_intersected).sum()
Out[72]:
Percentage matching
In [73]:
(planon_meterssensors_intersected==master_meterssensors_for_validation_intersected).sum()/\
len(planon_meterssensors_intersected)*100
Out[73]:
In [74]:
((planon_meterssensors_intersected==master_meterssensors_for_validation_intersected).sum()/\
len(planon_meterssensors_intersected)*100).plot(kind='bar')
Out[74]:
In [75]:
df=pd.DataFrame((planon_meterssensors_intersected.T==master_meterssensors_for_validation_intersected.T).sum())
df
Out[75]:
In [76]:
df=pd.DataFrame((planon_meterssensors_intersected.T==master_meterssensors_for_validation_intersected.T).sum()/\
len(planon_meterssensors_intersected.T)*100)
df[df[0]<100]
Out[76]:
Not all of the dataframe matches. Let us do some basic string formatting, maybe that helps.
In [77]:
sum(planon_meterssensors_intersected['Description']!=master_meterssensors_for_validation_intersected['Description'])
Out[77]:
In [78]:
planon_meterssensors_intersected['Description']=[str(s).lower().strip().replace(' ',' ').replace(' ',' ') for s in planon_meterssensors_intersected['Description'].values]
master_meterssensors_for_validation_intersected['Description']=[str(s).lower().strip().replace(' ',' ').replace(' ',' ') for s in master_meterssensors_for_validation_intersected['Description'].values]
In [79]:
sum(planon_meterssensors_intersected['Description']!=master_meterssensors_for_validation_intersected['Description'])
Out[79]:
Some errors fixed, some left. Let's see which ones. These are either:
In [80]:
for i in planon_meterssensors_intersected[planon_meterssensors_intersected['Description']!=master_meterssensors_for_validation_intersected['Description']].index:
print(i,'\t\tPlanon:',planon_meterssensors_intersected.loc[i]['Description'],'\t\tMaster:',master_meterssensors_for_validation_intersected.loc[i]['Description'])
Let us repeat the exercise for Logger Channel. Cross-validate, flag as highly likely error where both mismatch.
In [81]:
sum(planon_meterssensors_intersected['Logger Channel']!=master_meterssensors_for_validation_intersected['Logger Channel'])
Out[81]:
In [82]:
planon_meterssensors_intersected['Logger Channel']=[str(s).lower().strip().replace(' ',' ').replace(' ',' ') for s in planon_meterssensors_intersected['Logger Channel'].values]
master_meterssensors_for_validation_intersected['Logger Channel']=[str(s).lower().strip().replace(' ',' ').replace(' ',' ') for s in master_meterssensors_for_validation_intersected['Logger Channel'].values]
In [83]:
sum(planon_meterssensors_intersected['Logger Channel']!=master_meterssensors_for_validation_intersected['Logger Channel'])
Out[83]:
All errors fixed on logger channels.
In [84]:
for i in planon_meterssensors_intersected[planon_meterssensors_intersected['Logger Channel']!=master_meterssensors_for_validation_intersected['Logger Channel']].index:
print(i,'\t\tPlanon:',planon_meterssensors_intersected.loc[i]['Logger Channel'],'\t\tMaster:',master_meterssensors_for_validation_intersected.loc[i]['Logger Channel'])
New error percentage:
In [85]:
(planon_meterssensors_intersected!=master_meterssensors_for_validation_intersected).sum()/\
len(planon_meterssensors_intersected)*100
Out[85]:
In [143]:
buildings=set(planon_loggerscontrollers['BuildingNo.'])
buildings
Out[143]:
In [145]:
master_loggerscontrollers_for_validation = \
pd.concat([master_loggerscontrollers.loc[master_loggerscontrollers['Building Code'] == building] \
for building in buildings])
master_loggerscontrollers_for_validation.head(2)
Out[145]:
In [146]:
master_loggerscontrollers[master_loggerscontrollers['Building Code']=='MC060']
Out[146]:
In [147]:
planon_loggerscontrollers[planon_loggerscontrollers['BuildingNo.']=='MC060']
Out[147]:
In [148]:
master_loggerscontrollers.loc['MC060-L01']
Out[148]:
In [149]:
len(master_loggerscontrollers_for_validation)
Out[149]:
In [150]:
len(planon_loggerscontrollers)-len(planon_loggerscontrollers.index[planon_loggerscontrollers.index.duplicated()])
Out[150]:
In [151]:
master_loggerscontrollers_for_validation.sort_index(inplace=True)
planon_loggerscontrollers.sort_index(inplace=True)
In [152]:
master_loggerscontrollers_for_validation.loc['MC060-L01']
Out[152]:
In [153]:
planon_loggerscontrollers.T
Out[153]:
In [154]:
master_loggerscontrollers_for_validation.T
Out[154]:
Create dictionary that maps Planon column names onto Master.
From Nicola:
Building code and Building name are implicitly included. Logger IP or MAC would be essential to include, as well as Make and Model. Additional Location Info is not essnetial but would be useful to have. Locations (Locations.Space.Space number and Space Name) are included in the Planon export - but this is their only viable data source, therefore are not validated against.
In [155]:
#Planon:Master
loggers_match_dict={
"BuildingNo.":"Building Code",
"Building":"Building Name",
"Description":"Description",
"EIS ID":"Logger Serial Number",
"Make":"Make",
"Model":"Model"
}
In [156]:
master_loggerscontrollers_for_validation_filtered=master_loggerscontrollers_for_validation[list(loggers_match_dict.values())]
planon_loggerscontrollers_filtered=planon_loggerscontrollers[list(loggers_match_dict.keys())]
In [157]:
master_loggerscontrollers_for_validation_filtered.head(2)
Out[157]:
In [158]:
planon_loggerscontrollers_filtered.head(2)
Out[158]:
In [159]:
planon_loggerscontrollers_filtered.columns=[loggers_match_dict[i] for i in planon_loggerscontrollers_filtered]
In [160]:
planon_loggerscontrollers_filtered.drop_duplicates(inplace=True)
master_loggerscontrollers_for_validation_filtered.drop_duplicates(inplace=True)
In [161]:
planon_loggerscontrollers_filtered.head(2)
Out[161]:
In [162]:
master_loggerscontrollers_for_validation_filtered.head(2)
Out[162]:
In [163]:
a=np.sort(list(set(planon_loggerscontrollers_filtered.index)))
b=np.sort(list(set(master_loggerscontrollers_for_validation_filtered.index)))
loggerscontrollers_not_in_planon=[]
for i in b:
if i not in a:
print(i+',',end=" "),
loggerscontrollers_not_in_planon.append(i)
print('\n\nLoggers in Master, but not in Planon:',
len(loggerscontrollers_not_in_planon),'/',len(b),':',
round(len(loggerscontrollers_not_in_planon)/len(b)*100,3),'%')
In [164]:
a=np.sort(list(set(planon_loggerscontrollers_filtered.index)))
b=np.sort(list(set(master_loggerscontrollers_for_validation_filtered.index)))
loggerscontrollers_not_in_master=[]
for i in a:
if i not in b:
print(i+',',end=" "),
loggerscontrollers_not_in_master.append(i)
print('\n\nLoggers in Planon, not in Master:',
len(loggerscontrollers_not_in_master),'/',len(a),':',
round(len(loggerscontrollers_not_in_master)/len(a)*100,3),'%')
In [88]:
print(len(planon_loggerscontrollers_filtered.index))
print(len(set(planon_loggerscontrollers_filtered.index)))
print(len(master_loggerscontrollers_for_validation_filtered.index))
print(len(set(master_loggerscontrollers_for_validation_filtered.index)))
In [89]:
master_loggerscontrollers_for_validation_filtered[master_loggerscontrollers_for_validation_filtered.index.duplicated()]
Out[89]:
In [90]:
comon_index=list(set(master_loggerscontrollers_for_validation_filtered.index).intersection(set(planon_loggerscontrollers_filtered.index)))
In [91]:
master_loggerscontrollers_for_validation_intersected=master_loggerscontrollers_for_validation_filtered.loc[comon_index].sort_index()
planon_loggerscontrollers_intersected=planon_loggerscontrollers_filtered.loc[comon_index].sort_index()
In [92]:
master_loggerscontrollers_for_validation_intersected.head(2)
Out[92]:
In [93]:
planon_loggerscontrollers_intersected.head(2)
Out[93]:
In [94]:
planon_loggerscontrollers_intersected==master_loggerscontrollers_for_validation_intersected
Out[94]:
Loggers matching
In [95]:
(planon_loggerscontrollers_intersected==master_loggerscontrollers_for_validation_intersected).sum()
Out[95]:
Percentage matching
In [96]:
(planon_loggerscontrollers_intersected==master_loggerscontrollers_for_validation_intersected).sum()/\
len(planon_loggerscontrollers_intersected)*100
Out[96]:
In [97]:
((planon_loggerscontrollers_intersected==master_loggerscontrollers_for_validation_intersected).sum()/\
len(planon_loggerscontrollers_intersected)*100).plot(kind='bar')
Out[97]:
Loggers not matching on Building Name.
In [98]:
sum(planon_loggerscontrollers_intersected['Building Name']!=master_loggerscontrollers_for_validation_intersected['Building Name'])
Out[98]:
In [99]:
planon_loggerscontrollers_intersected['Building Name']=[str(s).lower().strip().replace(' ',' ').replace(' ',' ') for s in planon_loggerscontrollers_intersected['Building Name'].values]
master_loggerscontrollers_for_validation_intersected['Building Name']=[str(s).lower().strip().replace(' ',' ').replace(' ',' ') for s in master_loggerscontrollers_for_validation_intersected['Building Name'].values]
In [100]:
sum(planon_loggerscontrollers_intersected['Building Name']!=master_loggerscontrollers_for_validation_intersected['Building Name'])
Out[100]:
That didnt help.
In [101]:
for i in planon_loggerscontrollers_intersected[planon_loggerscontrollers_intersected['Building Name']!=master_loggerscontrollers_for_validation_intersected['Building Name']].index:
print(i,'\t\tPlanon:',planon_loggerscontrollers_intersected.loc[i]['Building Name'],'\t\tMaster:',master_loggerscontrollers_for_validation_intersected.loc[i]['Building Name'])
Follow up with lexical distance comparison. That would flag this as a match.
Loggers not matching on Serial Number.
In [102]:
sum(planon_loggerscontrollers_intersected['Logger Serial Number']!=master_loggerscontrollers_for_validation_intersected['Logger Serial Number'])
Out[102]:
In [103]:
planon_loggerscontrollers_intersected['Logger Serial Number']=[str(s).lower().strip().replace(' ',' ').replace(' ',' ').replace('{','').replace('}','') for s in planon_loggerscontrollers_intersected['Logger Serial Number'].values]
master_loggerscontrollers_for_validation_intersected['Logger Serial Number']=[str(s).lower().strip().replace(' ',' ').replace(' ',' ').replace('{','').replace('}','') for s in master_loggerscontrollers_for_validation_intersected['Logger Serial Number'].values]
In [104]:
sum(planon_loggerscontrollers_intersected['Logger Serial Number']!=master_loggerscontrollers_for_validation_intersected['Logger Serial Number'])
Out[104]:
In [105]:
for i in planon_loggerscontrollers_intersected[planon_loggerscontrollers_intersected['Logger Serial Number']!=master_loggerscontrollers_for_validation_intersected['Logger Serial Number']].index:
print(i,'\t\tPlanon:',planon_loggerscontrollers_intersected.loc[i]['Logger Serial Number'],'\t\tMaster:',master_loggerscontrollers_for_validation_intersected.loc[i]['Logger Serial Number'])
Technically the same, but there is a number format error. Compare based on float value, if they match, replace one of them. This needs to be amended, as it will throw cannot onvert to float exception if strings are left in from the previous step.
In [106]:
z1=[]
z2=[]
for i in planon_loggerscontrollers_intersected.index:
if planon_loggerscontrollers_intersected.loc[i]['Logger Serial Number']!=master_loggerscontrollers_for_validation_intersected.loc[i]['Logger Serial Number']:
if float(planon_loggerscontrollers_intersected.loc[i]['Logger Serial Number'])==\
float(master_loggerscontrollers_for_validation_intersected.loc[i]['Logger Serial Number']):
z1.append(str(int(float(planon_loggerscontrollers_intersected.loc[i]['Logger Serial Number']))))
z2.append(str(int(float(planon_loggerscontrollers_intersected.loc[i]['Logger Serial Number']))))
else:
z1.append(planon_loggerscontrollers_intersected.loc[i]['Logger Serial Number'])
z2.append(master_loggerscontrollers_for_validation_intersected.loc[i]['Logger Serial Number'])
else:
z1.append(planon_loggerscontrollers_intersected.loc[i]['Logger Serial Number'])
z2.append(planon_loggerscontrollers_intersected.loc[i]['Logger Serial Number'])
planon_loggerscontrollers_intersected['Logger Serial Number']=z1
master_loggerscontrollers_for_validation_intersected['Logger Serial Number']=z2
In [107]:
for i in planon_loggerscontrollers_intersected[planon_loggerscontrollers_intersected['Logger Serial Number']!=master_loggerscontrollers_for_validation_intersected['Logger Serial Number']].index:
print(i,'\t\tPlanon:',planon_loggerscontrollers_intersected.loc[i]['Logger Serial Number'],'\t\tMaster:',master_loggerscontrollers_for_validation_intersected.loc[i]['Logger Serial Number'])
New error percentage:
In [108]:
(planon_loggerscontrollers_intersected!=master_loggerscontrollers_for_validation_intersected).sum()/\
len(planon_loggerscontrollers_intersected)*100
Out[108]:
(Bearing in my mind the above, this is technically 0)