In [1]:

    

import pandas as pd, json


    

In [2]:

    

df=pd.read_excel('MASTER PlanonLoggersAndMeters 17 10 16.xlsx',sheetname='Meters Sensors')


    

In [3]:

    

df.columns


    

    
Out[3]:





Index(['Logger Asset Code', 'Asset Code', 'Description', 'Make', 'Model',
       'Meter Units', 'Meter Pulse Value', 'Classification Group',
       'Logger Channel', 'Utility Type', '??', 'Meter Type', 'Building Code',
       'Building Name', 'Space', 'Additional Location Info', 'Tenant meter',
       'Fiscal meter', 'Parent meter', 'Child meters', 'Communications type',
       'Electrical panel ID'],
      dtype='object')

In [4]:

    

len(df['Utility Type'].dropna())


    

    
Out[4]:





1189

In [5]:

    

len(df['Meter Units'].dropna())


    

    
Out[5]:





4712

In [6]:

    

len(df['Make'].dropna())


    

    
Out[6]:





926

In [7]:

    

df=df[['Asset Code','Description','Classification Group','Utility Type','Meter Units','Make','Model']]


    

In [11]:

    

A=[]
B=[]
C=[]
D=[]
for i in df['Asset Code']:
    if '/' in i:
        a=i[:i.find('-')]
        b=i[i.find('-')+1:i.find('/')]
        c=i[i.find('/')+1:]
    else: 
        d=i[i.find('-')+1:]
        b=d[:d.find('-')]
        c=d[d.find('-')+1:]
    A.append(a)
    B.append(b)
    C.append(c)
    D.append(a+'-'+b+'/'+c)


    

In [12]:

    

df['s']=1
df['Asset Code']=D


    

In [13]:

    

df['A']=A
df['B']=B
df['C']=C


    

In [20]:

    

U=[]
for i in df['Utility Type']:
    if type(i)==str: U.append(i.strip())
    else: U.append(i)
df['Utility Type']=U


    

In [21]:

    

df.columns


    

    
Out[21]:





Index(['Asset Code', 'Description', 'Classification Group', 'Utility Type',
       'Meter Units', 'Make', 'Model', 's', 'A', 'B', 'C'],
      dtype='object')

In [22]:

    

df.fillna('NoData').to_csv('meters.csv')


    

In [25]:

    

df['Make'].unique()


    

    
Out[25]:





array([nan, 'Micronics', 'Sensus', 'Crompton', 'Schneider', ' ', 'HB',
       'Selec', 'Ri Meter', 'Kamstrup', 'B Meters', 'Merlin Gerin',
       'Danfoss', 'Inepro', 'Jet', 'IME', 'Entity', 'Delta',
       'Carlo Gavazzi', 'ACTARIS', 'Schlumberger', 'Autometers', 'Elster',
       'Actaris', 'Delta ', 'Landis', 'B Metersss', 'GWF', 'Nuovo Pignone',
       'Socomec', 'Siemens', 'Northern Design', 'Common', 'Micronic',
       'Meters UK', 'Integra', 'Schneider Electric', 'Micronics (Metrima)',
       'JSM ', 'Micronics ', 'Kent', 'ND Metering Solutions', 'DMS',
       'IMAC Systems Ltd', 'Contoil', 'SVM', 'GT4', 'GMT', 'Metrix',
       'Instromet', 'B  Meters', 'Merlin', 'PoWoGaz', 'PR', 'Zenner',
       'Itron', 'AquaMotion', 'Ri', 'Boss', 'GasTube', 'Sontex', 'Powagaz',
       'BU.G.I (meters) Ltd.', 'WRAS', 'Scheider Electric', 'NWM', 'ABB',
       'Socam', 'Nothern Design', 'IME ', 'Square D', 'AHS', 'PowoGaz',
       'B-H', 'Socomec ', 'EATON', 'DeltaFlow', 'Autometer', 'EMDI',
       'Landis Gyr+'], dtype=object)