import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline

"""
Explanation: EIS metadata validation script
Used to validate Planon output with spreadsheet input
1. Data import
End of explanation
"""


planon=pd.read_excel('EIS Assets v2.xlsx',index_col = 'Code')
master_loggerscontrollers_old = pd.read_csv('LoggersControllers.csv', index_col = 'Asset Code')
master_meterssensors_old = pd.read_csv('MetersSensors.csv', encoding = 'macroman', index_col = 'Asset Code')
master_loggerscontrollers=pd.read_excel(master,sheetname='Loggers Controllers', index_col = 'Asset Code')
master_meterssensors=pd.read_excel(master,sheetname='Meters Sensors', index_col = 'Asset Code')
planon['Code']=planon.index
master_loggerscontrollers['Code']=master_loggerscontrollers.index
master_meterssensors['Code']=master_meterssensors.index

"""
Explanation: 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.
End of explanation
"""


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]

"""
Explanation: Unify index, caps everything and strip of trailing spaces.
End of explanation
"""


planon.drop_duplicates(inplace=True)
master_loggerscontrollers.drop_duplicates(inplace=True)
master_meterssensors.drop_duplicates(inplace=True)

"""
Explanation: Drop duplicates (shouldn't be any)
End of explanation
"""


# 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)

"""
Explanation: Split Planon import into loggers and meters
Drop duplicates (shouldn't be any)
End of explanation
"""


len(planon_loggerscontrollers.index[planon_loggerscontrollers.index.duplicated()])

len(planon_meterssensors.index[planon_meterssensors.index.duplicated()])

"""
Explanation: Index unique? show number of duplicates in index
End of explanation
"""


planon_meterssensors.head(3)

"""
Explanation: 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.
End of explanation
"""


buildings=set(planon_meterssensors['BuildingNo.'])
buildings

len(buildings)

"""
Explanation: 2. Validation
Create list of all buildings present in Planon export. These are buildings to check the data against from Master.
End of explanation
"""


master_meterssensors_for_validation = \
    pd.concat([master_meterssensors.loc[master_meterssensors['Building Code'] == building] \
           for building in buildings])
master_meterssensors_for_validation.head(2)

#alternative method
master_meterssensors_for_validation2 = \
    master_meterssensors[master_meterssensors['Building Code'].isin(buildings)]
master_meterssensors_for_validation2.head(2)

"""
Explanation: 2.1. Meters
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.
End of explanation
"""


len(master_meterssensors_for_validation)

len(planon_meterssensors)-len(planon_meterssensors.index[planon_meterssensors.index.duplicated()])

"""
Explanation: 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.
End of explanation
"""


master_meterssensors_for_validation.sort_index(inplace=True)
planon_meterssensors.sort_index(inplace=True)

"""
Explanation: Sort datasets after index for easier comparison.
End of explanation
"""


planon_meterssensors.T

master_meterssensors_for_validation.T

"""
Explanation: 2.1.1 Slicing of meters to  only certain columns of comparison
End of explanation
"""


#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"
      }

"""
Explanation: Create dictionary that maps Planon column names onto Master.  
From Nicola:
- Code (Asset Code)
- Description
- EIS ID (Channel)
- Utility Type
- Fiscal Meter
- Tenant Meter
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.
End of explanation
"""


master_meterssensors_for_validation_filtered=master_meterssensors_for_validation[list(meters_match_dict.values())]
planon_meterssensors_filtered=planon_meterssensors[list(meters_match_dict.keys())]

master_meterssensors_for_validation_filtered.head(2)

planon_meterssensors_filtered.head(2)

"""
Explanation: 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.
End of explanation
"""


planon_meterssensors_filtered.columns=[meters_match_dict[i] for i in planon_meterssensors_filtered]

planon_meterssensors_filtered.drop_duplicates(inplace=True)
master_meterssensors_for_validation_filtered.drop_duplicates(inplace=True)

planon_meterssensors_filtered.head(2)

"""
Explanation: Unify headers, drop duplicates (bear the mind the spaces argument, this where it needs to be brought back in in the future!).
End of explanation
"""


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])

master_meterssensors_for_validation_filtered.head(2)

planon_meterssensors_filtered.head(2)

"""
Explanation: Fiscal/Tenant meter name needs fixing from Yes/No and 1/0.
End of explanation
"""


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),'%')

#without MC210
len(set([i for i in meterssensors_not_in_planon if i[:5]!='MC210']))

set([i for i in meterssensors_not_in_planon if i[:5]!='MC210'])

(set([i[:5] for i in meterssensors_not_in_planon]))

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),'%')

len(set([i for i in meterssensors_not_in_master]))

set([i[:9] for i in meterssensors_not_in_master])

set([i[:5] for i in meterssensors_not_in_master])

"""
Explanation: Cross-check missing meters
End of explanation
"""


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)))

master_meterssensors_for_validation_filtered[master_meterssensors_for_validation_filtered.index.duplicated()]

"""
Explanation: Check for duplicates in index, but not duplicates over the entire row
End of explanation
"""


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)

len(planon_meterssensors_filtered)

len(master_meterssensors_for_validation_filtered)

"""
Explanation: The duplicates are the nans. Remove these for now. Could revisit later to do an index-less comparison, only over row contents.
End of explanation
"""


comon_index=list(set(master_meterssensors_for_validation_filtered.index).intersection(set(planon_meterssensors_filtered.index)))

len(comon_index)

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()

len(master_meterssensors_for_validation_intersected)

len(planon_meterssensors_intersected)

"""
Explanation: 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).
End of explanation
"""


master_meterssensors_for_validation_intersected = master_meterssensors_for_validation_intersected[~master_meterssensors_for_validation_intersected.index.duplicated(keep='first')]

master_meterssensors_for_validation_intersected.head(2)

planon_meterssensors_intersected.head(2)

"""
Explanation: Still have duplicate indices. For now we just drop and keep the first.
End of explanation
"""


planon_meterssensors_intersected==master_meterssensors_for_validation_intersected

np.all(planon_meterssensors_intersected==master_meterssensors_for_validation_intersected)

"""
Explanation: 2.1.2. Primitive comparison
End of explanation
"""


(planon_meterssensors_intersected==master_meterssensors_for_validation_intersected).sum()

"""
Explanation: 2.1.3. Horizontal comparison
Number of cells matching
End of explanation
"""


(planon_meterssensors_intersected==master_meterssensors_for_validation_intersected).sum()/\
len(planon_meterssensors_intersected)*100

((planon_meterssensors_intersected==master_meterssensors_for_validation_intersected).sum()/\
len(planon_meterssensors_intersected)*100).plot(kind='bar')

"""
Explanation: Percentage matching
End of explanation
"""


df=pd.DataFrame((planon_meterssensors_intersected.T==master_meterssensors_for_validation_intersected.T).sum())
df

df=pd.DataFrame((planon_meterssensors_intersected.T==master_meterssensors_for_validation_intersected.T).sum()/\
len(planon_meterssensors_intersected.T)*100)
df[df[0]<100]

"""
Explanation: 2.1.4. Vertical comparison
End of explanation
"""


sum(planon_meterssensors_intersected['Description']!=master_meterssensors_for_validation_intersected['Description'])

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]

sum(planon_meterssensors_intersected['Description']!=master_meterssensors_for_validation_intersected['Description'])

"""
Explanation: 2.1.5. Smart(er) comparison
Not all of the dataframe matches. Let us do some basic string formatting, maybe that helps.
End of explanation
"""


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'])

"""
Explanation: Some errors fixed, some left. Let's see which ones. These are either:
- Wrong duplicate dropped
- Input human erros in the description.
- Actual erros somewhere in the indexing.
End of explanation
"""


sum(planon_meterssensors_intersected['Logger Channel']!=master_meterssensors_for_validation_intersected['Logger Channel'])

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]

sum(planon_meterssensors_intersected['Logger Channel']!=master_meterssensors_for_validation_intersected['Logger Channel'])

"""
Explanation: Let us repeat the exercise for Logger Channel. Cross-validate, flag as highly likely error where both mismatch.
End of explanation
"""


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'])

"""
Explanation: All errors fixed on logger channels.
End of explanation
"""


(planon_meterssensors_intersected!=master_meterssensors_for_validation_intersected).sum()/\
len(planon_meterssensors_intersected)*100

"""
Explanation: New error percentage:
End of explanation
"""


buildings=set(planon_loggerscontrollers['BuildingNo.'])
buildings

master_loggerscontrollers_for_validation = \
    pd.concat([master_loggerscontrollers.loc[master_loggerscontrollers['Building Code'] == building] \
           for building in buildings])
master_loggerscontrollers_for_validation.head(2)

master_loggerscontrollers[master_loggerscontrollers['Building Code']=='MC060']

planon_loggerscontrollers[planon_loggerscontrollers['BuildingNo.']=='MC060']

master_loggerscontrollers.loc['MC060-L01']

len(master_loggerscontrollers_for_validation)

len(planon_loggerscontrollers)-len(planon_loggerscontrollers.index[planon_loggerscontrollers.index.duplicated()])

master_loggerscontrollers_for_validation.sort_index(inplace=True)
planon_loggerscontrollers.sort_index(inplace=True)

master_loggerscontrollers_for_validation.loc['MC060-L01']

planon_loggerscontrollers.T

master_loggerscontrollers_for_validation.T

"""
Explanation: 2.2. Loggers
End of explanation
"""


#Planon:Master
loggers_match_dict={
      "BuildingNo.":"Building Code",
      "Building":"Building Name",
      "Description":"Description",
      "EIS ID":"Logger Serial Number",
      "Make":"Make",
      "Model":"Model",
      "Code":"Code"
      }

master_loggerscontrollers_for_validation_filtered=master_loggerscontrollers_for_validation[list(loggers_match_dict.values())]
planon_loggerscontrollers_filtered=planon_loggerscontrollers[list(loggers_match_dict.keys())]

master_loggerscontrollers_for_validation_filtered.head(2)

planon_loggerscontrollers_filtered.head(2)

planon_loggerscontrollers_filtered.columns=[loggers_match_dict[i] for i in planon_loggerscontrollers_filtered]

planon_loggerscontrollers_filtered.drop_duplicates(inplace=True)
master_loggerscontrollers_for_validation_filtered.drop_duplicates(inplace=True)

planon_loggerscontrollers_filtered.head(2)

master_loggerscontrollers_for_validation_filtered.head(2)

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),'%')

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),'%')

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)))

master_loggerscontrollers_for_validation_filtered[master_loggerscontrollers_for_validation_filtered.index.duplicated()]

comon_index=list(set(master_loggerscontrollers_for_validation_filtered.index).intersection(set(planon_loggerscontrollers_filtered.index)))

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()

master_loggerscontrollers_for_validation_intersected.head(2)

planon_loggerscontrollers_intersected.head(2)

planon_loggerscontrollers_intersected==master_loggerscontrollers_for_validation_intersected

"""
Explanation: Create dictionary that maps Planon column names onto Master.  
From Nicola:
- EIS ID (Serial Number)
- Make
- Model
- Description
- Code (Asset Code)
- Building Code
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.
End of explanation
"""


(planon_loggerscontrollers_intersected==master_loggerscontrollers_for_validation_intersected).sum()

"""
Explanation: Loggers matching
End of explanation
"""


(planon_loggerscontrollers_intersected==master_loggerscontrollers_for_validation_intersected).sum()/\
len(planon_loggerscontrollers_intersected)*100

((planon_loggerscontrollers_intersected==master_loggerscontrollers_for_validation_intersected).sum()/\
len(planon_loggerscontrollers_intersected)*100).plot(kind='bar')

"""
Explanation: Percentage matching
End of explanation
"""


sum(planon_loggerscontrollers_intersected['Building Name']!=master_loggerscontrollers_for_validation_intersected['Building Name'])

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]

sum(planon_loggerscontrollers_intersected['Building Name']!=master_loggerscontrollers_for_validation_intersected['Building Name'])

"""
Explanation: Loggers not matching on Building Name.
End of explanation
"""


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'])

"""
Explanation: That didnt help.
End of explanation
"""


sum(planon_loggerscontrollers_intersected['Logger Serial Number']!=master_loggerscontrollers_for_validation_intersected['Logger Serial Number'])

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]

sum(planon_loggerscontrollers_intersected['Logger Serial Number']!=master_loggerscontrollers_for_validation_intersected['Logger Serial Number'])

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'])

"""
Explanation: Follow up with lexical distance comparison. That would flag this as a match.
Loggers not matching on Serial Number.
End of explanation
"""


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

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'])

"""
Explanation: 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.
End of explanation
"""


(planon_loggerscontrollers_intersected!=master_loggerscontrollers_for_validation_intersected).sum()/\
len(planon_loggerscontrollers_intersected)*100

"""
Explanation: New error percentage:
End of explanation
"""


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),'%')

q1=pd.DataFrame(meterssensors_not_in_planon)

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),'%')

q2=pd.DataFrame(meterssensors_not_in_master)

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),'%')

q3=pd.DataFrame(loggerscontrollers_not_in_planon)

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),'%')

q4=pd.DataFrame(loggerscontrollers_not_in_master)

q5=pd.DataFrame((planon_meterssensors_intersected!=master_meterssensors_for_validation_intersected).sum()/\
len(planon_meterssensors_intersected)*100)

q6=pd.DataFrame((planon_loggerscontrollers_intersected!=master_loggerscontrollers_for_validation_intersected).sum()/\
len(planon_loggerscontrollers_intersected)*100)

w1=[]
for i in planon_meterssensors_intersected[planon_meterssensors_intersected['Description']!=master_meterssensors_for_validation_intersected['Description']].index:
    w1.append({"Meter":i,'Planon':planon_meterssensors_intersected.loc[i]['Description'],
               'Master':master_meterssensors_for_validation_intersected.loc[i]['Description']})

q7=pd.DataFrame(w1)

w2=[]
for i in planon_loggerscontrollers_intersected[planon_loggerscontrollers_intersected['Building Name']!=master_loggerscontrollers_for_validation_intersected['Building Name']].index:
    w2.append({"Logger":i,'Planon':planon_loggerscontrollers_intersected.loc[i]['Building Name'],
               'Master':master_loggerscontrollers_for_validation_intersected.loc[i]['Building Name']})

q8=pd.DataFrame(w2)

writer = pd.ExcelWriter('final5.xlsx')
q1.to_excel(writer,'Meters Master, not Planon')
q2.to_excel(writer,'Meters Planon, not Master')
q3.to_excel(writer,'Loggers Master, not Planon')
q4.to_excel(writer,'Loggers Planon, not Master')
q5.to_excel(writer,'Meters error perc')
q6.to_excel(writer,'Loggers error perc')
q7.to_excel(writer,'Meters naming conflcits')
q8.to_excel(writer,'Loggers naming conflicts')
writer.save()

"""
Explanation: (Bearing in my mind the above, this is technically 0)
End of explanation
"""


