Gas_Cons_Loc_Tozzi.py

# -*- coding: utf-8 -*-
"""
Created on Tue Oct  3 11:20:13 2017

@author: d_floriello

Local Gas Consumption
"""

###### da qui 1 #########

from __future__ import division
import pandas as pd
import numpy as np
from collections import OrderedDict
from os import listdir
from os.path import isfile, join
import unidecode
import datetime
#import time

####################################################################################################
####################################################################################################
def UpdateZona(vec, j, val):
    for k in range(j, 12, 1):
        vec[k] += val
    return vec
####################################################################################################
def cleanDF(df):
    lr = list(set(df['REMI'].values.tolist()))
    surv = []
    for l in lr:
        print(l)
        ldf = df.loc[df['REMI'] == l]
        if ldf.shape[0] == 1:
            surv.append(l)
        else:
            ld = []
            #ldf = ldf.reset_index(drop = True)
            for i in range(ldf.shape[0]):
                ii = ldf.index.tolist()[i]
                ld.append(np.sum(np.abs(np.diff(ldf[ldf.columns[2:]].iloc[ii].values.tolist()))))
            surv.append(ldf.index.tolist()[ld.index(np.max(ld))])
    return df.iloc[surv]
####################################################################################################
def RCleaner(df):
    df1 = df.loc[df['SHIPPER'] == '0001808491-AXOPOWER SRL']
    df2 = df1.loc[df['DESCRIZIONE_PRODOTTO'] != 'Solo Superi e Quota Fissa']
    df3 = df2.loc[df2['D_VALIDO_AL'] >= datetime.datetime(2017, 10, 1)]
    return df3.reset_index(drop = True)
####################################################################################################
def ActiveAtMonth(start, end):
    active = np.repeat(0,12)
    if start <= datetime.datetime(2016,10,1) and end >= datetime.datetime(2017, 9, 30):
        active = np.repeat(1, 12)
        return active
    elif start <= datetime.datetime(2016,10,1) and end <= datetime.datetime(2017, 9, 30):
        fm = end.month
        if fm >= 10:
            active[:fm-9] = 1
        else:
            active[:fm+3] = 1
        return active
    elif start >= datetime.datetime(2016,10,1) and end >= datetime.datetime(2017, 9, 30):
        im = start.month   
        if im >= 10:
            active[im-10:] = 1
        else:
            active[im+2:] = 1
        return active
    elif start >= datetime.datetime(2016,10,1) and end <= datetime.datetime(2017, 9, 30):
        im = start.month
        fm = end.month
        if im == fm:
            if im >= 10:
                active[im-10] = 1
            else:
                active[im+2] = 1
        else:
            if im >= 10 and fm >= 10:
                active[im-10:fm-9] = 1
            elif im >= 10 and fm <= 10:
                active[im-10:fm+3] = 1
            elif im <= 10 and fm <= 10:
                active[im+2:fm+3] = 1
            else:
                print('impossible dates:')
                print(start) 
                print(end)
        return active
####################################################################################################
def Regulator(vec, k):
    res = np.repeat(0,12)
    if k >= 10:
        res[:k-9] = vec[:k-9]/100
    else:
        res[:k+3] = vec[:k+3]/100
    return res
####################################################################################################
def GenerateCapacity(C, m):
    cvec = np.repeat(0, 12)
    if m >= 10:
        cvec[m-10:] = C
    else:
        cvec[m+2:] = C
    return cvec
####################################################################################################
def GetStartDate(dt):
    if dt >= datetime.datetime(2016,10,1):
        return dt
    else:
        return datetime.datetime(2016,10,1)
####################################################################################################
def GetEndDate(dt):
    if dt <= datetime.datetime(2017,9,30):
        return dt
    else:
        return datetime.datetime(2017,9,30)
####################################################################################################
def WYEstimation(cons, prof, setmonth, pp):
    used_perc = 0
    for m in setmonth:
        used_perc += prof[pp].loc[prof.index.month == m].sum()
    if used_perc > 1e-3:
        return cons/(used_perc/100)
    else:
        return 0
####################################################################################################
def DaConferire(l, prof, setmonth, pp):
    ### l = [cons_contr, cons_distr, sii, VAF, vaf]
    if l[3] > 0:
        return l[3]
    elif l[3] == 0 and l[2] > 0 and l[4] >= 0:
        if l[2] >= l[4] or l[4] == 0: 
            return l[2]
        elif l[4] > l[2]:
            y = WYEstimation(l[4], prof, setmonth, pp)
            if y > 0:
                return y
            else:
                if l[2] > 0:
                    return l[2]
    elif l[3] == 0 and l[2] == 0 and l[1]> 0:
        return l[1]
    else:
        return l[0]
####################################################################################################
def GetYears():
    A = datetime.datetime.now().year
    current_month = datetime.datetime.now().month
    if 2 > current_month:
        return list(range(A-2, A))
    elif current_month == 2:
        return [A-1]
    else:
        return list(range(A-1, A+1))
####################################################################################################
def GetMonths():
    current_month = datetime.datetime.now().month - 2
    if current_month == -1: ### Gennaio
        return [12], list(range(1,12))
    elif current_month == 0: ### Febbraio
        return [],list(range(1,13))
    elif current_month == 1: ### Marzo
        return list(range(2,13)),[1]
    elif current_month == 2: ### Aprile
        return list(range(3,13)),[1,2]
    elif current_month == 3: ### Maggio
        return list(range(4,13)),list(range(1, 4))
    elif current_month == 4: ### Giugno
        return list(range(5,13)),list(range(1, 5))
    elif current_month == 5: ### Luglio
        return list(range(6,13)),list(range(1, 6))
    elif current_month == 6: ### Agosto
        return list(range(7,13)),list(range(1, 7))
    elif current_month == 7: ### Settembre
        return list(range(8,13)),list(range(1, 8))
    elif current_month == 8: ### Ottobre
        return list(range(9,13)),list(range(1, 9))
    elif current_month == 9: ### Novembre
        return list(range(10,13)),list(range(1, 10))
    else: ### Dicembre
        return list(range(11,13)),list(range(1, 11))
####################################################################################################        

###### a qui 1 #########

###### da qui 2 #########
doc1 = "Z:/AREA ENERGY MANAGEMENT GAS/Transizione shipper/AT 2017-2018/20171201 Report Fatturato Gas_Ottobre.xlsx"
#doc2 = 'C:/Users/d_floriello/Downloads/170206-101449-218.xls'
#doc2 = "Z:/AREA ENERGY MANAGEMENT GAS/ESITI TRASPORTATORI/17-18 Anagrafica Clienti.xlsx"
doc2 = "Z:/AREA ENERGY MANAGEMENT GAS/Transizione shipper/AT 2017-2018/20171201 Trasferimenti Gennaio 2018.xlsx"
doc3 = "Z:/AREA ENERGY MANAGEMENT GAS/Aggiornamento Anagrafico Gas/1712/Anagrafica TIS EVOLUTION.xlsm"


df181 = pd.read_excel(doc1, sheetname = 'Report fatturato GAS', skiprows = [0,1], converters={'PDR': str,'REMI': str,
                      'COD_CLIENTE': str})
#df218 = pd.read_excel(doc2, sheetname = 'Anagrafica EE+GAS_Globale', skiprows = [0,1], converters={'FORNITURA_POD': str, 
#                      'CLIENTE_CODICE': str, 'COD_REMI': str})
df218 = pd.read_excel(doc2, sheetname = 'Anagrafica', skiprows = [0], converters={'PDR': str, 'REMi': str})

dfA = pd.read_excel(doc3, sheetname = 'Importazione', converters={'COD_PDR': str, 'COD_REMI': str})


prof = pd.read_excel('C:/Users/c_tozzi/Documents/Profili standard di prelievo 2017-18.xls.xlsx', sheetname = '% prof', 
                     skiprows = [0,2])    

prof = prof.set_index(pd.date_range(start = '2017-10-01', end = '2018-09-30', freq = 'D'))

###### a qui 2 #########

###### da qui 3 #########

df218 = df218[df218.columns[:13]]
df218 = df218.loc[df218['FINE FORNITURA'] > datetime.datetime.now()]

years = GetYears()
months = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]

#current_month = datetime.datetime.now().month - 2
#tym = list(set(months).intersection(set(np.arange(1,current_month + 1,1).tolist())))
#lym = list(set(months).difference(set(np.arange(1,current_month + 1,1).tolist())))    

lym, tym = GetMonths()

#df218 = RCleaner(df218)

cod = list(set(df218['RAGIONE SOCIALE']))

res = OrderedDict()
for c in cod:
    cdf218 = df218.loc[df218['RAGIONE SOCIALE'] == c]
    pdrs = list(set(cdf218['PDR'].values.tolist()))
    remi = cdf218['REMi'].values.tolist()[-1]
    if len(pdrs) > 0 and str(pdrs[0]) != 'nan':
        for p in pdrs:
            setm = []
            mcount = 0
            vaf = 0
            cons_contr = cdf218['CONSUMO CONTRATTUALE'].loc[cdf218['PDR'] == p].values.tolist()[-1]
            cons_distr = cdf218['CONSUMO DISTRIBUTORE'].loc[cdf218['PDR'] == p].values.tolist()[-1]            
            pdf181 = df181.loc[df181['PDR'] == p]
            PDR = cdf218['PROFILO PRELIEVO'].loc[cdf218['PDR'] == p].values.tolist()
            if not isinstance(PDR, list):
                PDR = [PDR]
            if len(PDR) == 0:
                pp = 'T2E1'
            elif (len(PDR) == 1) and (not str(PDR[0]) == 'nan'):
                pp = PDR[0]
            else:
                if not str(PDR[-1]) == 'nan':
                    pp = PDR[-1]
                else:
                    pp = 'T2E1'
            for y in years:
                ydf = pdf181.loc[pdf181['ANNO_COMPETENZA'] == y]
                if y == years[0]:
                    MM = lym
                else:
                    MM = tym
                if ydf.shape[0] > 0:
                    for m in MM:
                        mydf = ydf.loc[ydf['MESE_COMP'] == m]
                        if mydf.shape[0] > 0:
                            setm.append(m)                            
                            mcount += 1
                            m_cons = mydf['CONSUMO_SMC'].sum() 
                            if mydf['CONSUMO_SMC'].sum() < 0:                        
                                vaf += 0
                            else:
                                vaf += m_cons
            fm12 = vaf
            sii = 0
            if len(dfA['PRELIEVO_ANNUO_PREV'].loc[dfA['COD_PDR'] == p].values.tolist()) > 0:
                sii = float(dfA['PRELIEVO_ANNUO_PREV'].loc[dfA['COD_PDR'] == p].values.tolist()[0])
                if str(dfA['COD_PROF_PREL_STD'].loc[dfA['COD_PDR'] == p].values.tolist()[0]) != 'nan':
                    pp = dfA['COD_PROF_PREL_STD'].loc[dfA['COD_PDR'] == p].values.tolist()[0]
            VAF = 0
            if mcount == 12:
                VAF = vaf
            res[str(p)] = [str(p), remi, pp, cons_contr, cons_distr, sii, VAF, vaf, max([DaConferire([cons_contr, cons_distr, sii, VAF, vaf], prof, setm, pp),5])]
    else:
        setm = []
        mcount = 0
        vaf = 0
        cons_contr = cdf218['CONSUMO CONTRATTUALE'].loc[cdf218['REMi'] == remi].values.tolist()[-1]
        cons_distr = cdf218['CONSUMO DISTRIBUTORE'].loc[cdf218['REMi'] == remi].values.tolist()[-1]            
        PDR = cdf218['PROFILO PRELIEVO'].loc[cdf218['REMi'] == remi].values.tolist()[-1]
        if not isinstance(PDR, list):
            PDR = [PDR]
        if len(PDR) == 0:
            pp = 'T2E1'
        elif (len(PDR) == 1) and (not str(PDR[0]) == 'nan'):
            pp = PDR[0]
        else:
            if not str(PDR[-1]) == 'nan':
                pp = PDR[-1]
            else:
                pp = 'T2E1'
        pdf181 = df181.loc[df181['REMI'] == remi]
        for y in years:
            ydf = pdf181.loc[pdf181['ANNO_COMPETENZA'] == y]
            if y == years[0]:
                MM = lym
            else:
                MM = tym
            if ydf.shape[0] > 0:
                for m in MM:
                    mydf = ydf.loc[ydf['MESE_COMP'] == m]
                    if mydf.shape[0] > 0:
                        setm.append(m)
                        mcount += 1
                        m_cons = mydf['CONSUMO_SMC'].sum() 
                        if mydf['CONSUMO_SMC'].sum() < 0:                        
                            vaf += 0
                        else:
                            vaf += m_cons
        fm12 = vaf
        sii = 0
        if len(dfA['PRELIEVO_ANNUO_PREV'].loc[dfA['COD_REMI'] == remi].values.tolist()) > 0:
            sii = float(dfA['PRELIEVO_ANNUO_PREV'].loc[dfA['COD_REMI'] == remi].values.tolist()[0])
            pp = dfA['COD_PROF_PREL_STD'].loc[dfA['COD_PDR'] == p].values.tolist()[0]
        VAF = 0
        if mcount == 12:
            VAF = vaf
        res[remi] = [remi, remi, pp, cons_contr, cons_distr, sii, VAF, vaf, max([DaConferire([cons_contr, cons_distr, sii, VAF, vaf], prof, setm, pp),5])]


resdf = pd.DataFrame.from_dict(res, orient = 'index')
resdf.columns = [['PDR','REMI', 'PROFILO_PRELIEVO', 'CONSUMO_CONTRATTUALE', 'CONSUMO_DISTRIBUTORE', 'SII', 
                  'VOLUME ANNUO FATTURATO', 'FATTURATO MIN 12', 'DA CONFERIRE']]

#### check that every pdr has a value in "da conferire": ###########################################
if resdf['DA CONFERIRE'].loc[resdf['DA CONFERIRE'] == 0].values.size > 0:
    print('ATTENZIONE: alcuni PDR non hanno valore da conferire!!')
    print(resdf['PDR'].loc[resdf['DA CONFERIRE'] == 0].values)
else:
    print('tutti i PDR hanno valori da conferire')
####################################################################################################
### capacity constraint (=> 5) on REMI, not PDR
####################################################################################################
resdf.to_excel('C:/Users/c_tozzi/CapGas/ConsumiStimati.xlsx')

###### a qui 3 #########

###### da qui 4 #########
                  
####### aggregazione capacità per trasportatore
trasp = pd.read_excel('Z:\AREA ENERGY MANAGEMENT GAS\ESITI TRASPORTATORI\DB Trasportatori.xlsx', skiprows = [0,1,2,3,4,5], converters={0: str, 6: str})
trasp = trasp[trasp.columns[[0,6]]]
trasp = trasp.dropna()
trasp.columns = [['REMI', 'AREA']]
trasp = trasp.loc[trasp['AREA'] != '0']

### SNAM
directory = 'Z:\AREA ENERGY MANAGEMENT GAS\ESITI TRASPORTATORI\SNAM'
listfiles = [f for f in listdir(directory) if isfile(join(directory, f))]                 
base = [lf for lf in listfiles if 'CONF' in lf]
others = list(set(listfiles).difference(set(base)))
    
snamb = pd.read_excel(directory + '/' + base[0], sheetname = 'Punti di Riconsegna', converters = {'Codice Punto': str}) 
snama = pd.read_excel(directory + '/' + base[0], sheetname = 'Punti di uscita')   
snamb.columns = [unidecode.unidecode(x) for x in snamb.columns.tolist()]
snama.columns = [unidecode.unidecode(x) for x in snama.columns.tolist()]
remi_snam = list(set(snamb['Codice Punto'].values.tolist()))


cen = np.repeat(snama['Capacita Richiesta [Sm3/g]'].iloc[snama['Codice Punto'].tolist().index('M_RN_CEN')],12)
mer = np.repeat(snama['Capacita Richiesta [Sm3/g]'].iloc[snama['Codice Punto'].tolist().index('M_RN_MER')],12)
noc = np.repeat(snama['Capacita Richiesta [Sm3/g]'].iloc[snama['Codice Punto'].tolist().index('M_RN_NOC')],12)
nor = np.repeat(snama['Capacita Richiesta [Sm3/g]'].iloc[snama['Codice Punto'].tolist().index('M_RN_NOR')],12)
soc = np.repeat(snama['Capacita Richiesta [Sm3/g]'].iloc[snama['Codice Punto'].tolist().index('M_RN_SOC')],12)
sor = np.repeat(snama['Capacita Richiesta [Sm3/g]'].iloc[snama['Codice Punto'].tolist().index('M_RN_SOR')],12)

CGsnam = OrderedDict()
for rs in remi_snam:
    index_rs = remi_snam.index(rs)
    atrs = snamb.loc[snamb['Codice Punto'] == rs]
    atr = []
    atr.append(rs)
    atr.append('M_RN_' + trasp['AREA'].loc[trasp['REMI'] == rs].values.tolist()[0])
    mcg = np.repeat(atrs['Capacita Sottoscritta [Sm3/g]'].values.tolist()[0], 12)
    atr.extend(mcg)
    CGsnam[index_rs] = atr

cgsnam = pd.DataFrame.from_dict(CGsnam, orient = 'index')
cgsnam.columns = [['REMI', 'AREA', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]

###### ALTERNATIVE BUILDING ######
g_i = 0
newremi = OrderedDict()
for of in others:
    if 'TRAS' in of:
        df = pd.read_excel(directory + '/' + of, sheetname = 'Esito', converters = {'PdR Aggregato': str})
        df.columns = [unidecode.unidecode(x) for x in df.columns.tolist()]
        for i in range(df.shape[0]):
            g_i += 1
            rr = df['PdR Aggregato'].iloc[i]
            za = df['Codice Area di prelievo'].iloc[i]
            m = str(int(df['Data Inizio'].iloc[i][3:5]))
            if za == 'M_RN_CEN':
                cen = UpdateZona(cen, int(m), df['Cap Addiz RN Conf/Cap Rila RN'].iloc[i])
            elif za == 'M_RN_MER':
                mer = UpdateZona(mer, int(m), df['Cap Addiz RN Conf/Cap Rila RN'].iloc[i])
            elif za == 'M_RN_NOC':
                noc = UpdateZona(noc, int(m), df['Cap Addiz RN Conf/Cap Rila RN'].iloc[i])
            elif za == 'M_RN_NOR':
                nor = UpdateZona(nor, int(m), df['Cap Addiz RN Conf/Cap Rila RN'].iloc[i])
            elif za == 'M_RN_SOC':
                soc = UpdateZona(soc, int(m), df['Cap Addiz RN Conf/Cap Rila RN'].iloc[i])
            elif za == 'M_RN_SOR':
                sor = UpdateZona(sor, int(m), df['Cap Addiz RN Conf/Cap Rila RN'].iloc[i])
            else:
                print('NO ZONE FOUND!!!')
            if str(rr) != 'nan' and rr != '':    
                C = df['Capacita Trasferita'].iloc[i] + df['Cap Addiz RR Conf/Rein RR Conf'].iloc[i]
                atr = [rr, za]
                atr.extend(GenerateCapacity(C, int(m)).tolist())                   
                newremi[g_i] = atr
        
    elif 'INCR' in of:
        dfr = pd.read_excel(directory + '/' + of, sheetname = 'Punti di Riconsegna', converters = {'Codice Punto': str})
        dfr.columns = [unidecode.unidecode(x) for x in dfr.columns.tolist()]
        dfa = pd.read_excel(directory + '/' + of, sheetname = 'Punti di uscita', converters = {'Codice Punto': str})
        dfa.columns = [unidecode.unidecode(x) for x in dfa.columns.tolist()]
        for i in range(dfa.shape[0]):
            za = dfa['Codice Punto'].iloc[i]
            m = str(int(dfa['Termini Temporali Da'].iloc[i][3:5]))
            if za == 'M_RN_CEN':
                cen = UpdateZona(cen, int(m), dfa['Capacita Sottoscritta [Sm3/g]'].iloc[i])
            elif za == 'M_RN_MER':
                mer = UpdateZona(mer, int(m), dfa['Capacita Sottoscritta [Sm3/g]'].iloc[i])
            elif za == 'M_RN_NOC':
                noc = UpdateZona(noc, int(m), dfa['Capacita Sottoscritta [Sm3/g]'].iloc[i])
            elif za == 'M_RN_NOR':
                nor = UpdateZona(nor, int(m), dfa['Capacita Sottoscritta [Sm3/g]'].iloc[i])
            elif za == 'M_RN_SOC':
                soc = UpdateZona(soc, int(m), dfa['Capacita Sottoscritta [Sm3/g]'].iloc[i])
            elif za == 'M_RN_SOR':
                sor = UpdateZona(sor, int(m), dfa['Capacita Sottoscritta [Sm3/g]'].iloc[i])
            else:
                print('NO ZONE FOUND!!!')
        for i in range(dfr.shape[0]):
            g_i += 1
            rr = dfr['Codice Punto'].iloc[i]
            print(rr)
            m = str(int(dfr['Termini Temporali Da'].iloc[i][3:5]))
            if rr != '' and str(rr) != 'nan':      
                C = dfr['Capacita Sottoscritta [Sm3/g]'].iloc[i]
                atr = [rr, za]
                atr.extend(GenerateCapacity(C, int(m)).tolist())                   
                newremi[g_i] = atr
                
        
newremi = pd.DataFrame.from_dict(newremi, orient = 'index')
newremi.reset_index(drop = True)
newremi.columns = [['REMI', 'AREA', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]

cg2 = cgsnam.append(newremi, ignore_index = True)
cg2 = cg2.groupby('REMI')
cg2 = cg2.agg(sum)

###### a qui 4 #########


#num_nuovi_remi_snam = len(list(set(cgsnam['REMI'].values.tolist()).difference(set(newremi['REMI'].values.tolist())))) 
#if len(newremi.keys()) > 0:
#    newremi = pd.DataFrame.from_dict(newremi, orient = 'index')
#    newremi = newremi.reset_index(drop = True)
#    newremi.columns = [['REMI', 'AREA', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]
#time.sleep(2)
#print('#############################################################################################')
#print('ci sono {} nuovi REMI da SNAM!'.format(num_nuovi_remi_snam))
#print('#############################################################################################')


#cgsnam = cgsnam.append(cleanDF(NRemi).dropna(), ignore_index = True)    

#### another check:
#cgsnam.sum(axis = 1)
#cgsnam2.sum(axis = 1)
#cgsnam.to_excel('cgsnam.xlsx')
#### check on newremi:
#NRemi2 = NRemi
#nr = cleanDF(NRemi).dropna()
#nr.to_excel('nr.xlsx')

###### da qui 5 #########


### RETRAGAS
directory = 'Z:\AREA ENERGY MANAGEMENT GAS\ESITI TRASPORTATORI\RETRAGAS'
listfiles = [f for f in listdir(directory) if isfile(join(directory, f))]                 
base = [lf for lf in listfiles if 'CONF' in lf]
others = list(set(listfiles).difference(set(base)))
variazioni = [f for f in others if 'Variazioni' in f][0]
    
rgb = pd.read_excel(directory + '/' + base[0], converters = {'PdrLogico': str}, skiprows = [0,1,2,3,4,5,6,8]) 
remi_rg = list(set(rgb['PdrLogico'].values.tolist()))
    
Rg = OrderedDict()
for rg in remi_rg:
    index_rs = remi_rg.index(rg)
    atrs = rgb.loc[rgb['PdrLogico'] == rg]
    atr = []
    atr.append(rg)
    atr.append('M_RN_' + trasp['AREA'].loc[trasp['REMI'] == rg].values.tolist()[0])
    cap = 0
    if atrs['Esito'].values.tolist()[0] > 0:
        cap = int(atrs['capacita'].values.tolist()[0])
    mcg = np.repeat(cap, 12)
    atr.extend(mcg)
    Rg[index_rs] = atr

Rg = pd.DataFrame.from_dict(Rg, orient = 'index')
Rg.columns = [['REMI', 'AREA', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]

###### check retragas:
# Rg2 = Rg

g_i = 0
newremi = OrderedDict()
for of in others:
    print(of)    
    if 'TRAS' in of or 'INCR' in of or 'RCT' in of or 'REIN' in of:
        m = str(int(of[2:4]))
        df = pd.read_excel(directory + '/' + of, converters = {'PdrLogico': str}, skiprows = [0,1,2,3,4,5,6, 8])
        df.columns = [unidecode.unidecode(x) for x in df.columns.tolist()]
        for i in range(df.shape[0]):
            atr = []
            g_i += 1
            rr = df['PdrLogico'].iloc[i]
            print(rr)
            if rr != '' and str(rr) != 'nan':
                cap = 0
#                if 'INCR' in of:
#                    if df['Esito'].iloc[i] > 0:
#                        cap = df['CapacitaRichiesta'].iloc[i] 
#                else:
#                    cap = df['CapacitaRichiesta'].iloc[i]
                cap = df['CapacitaRichiesta'].iloc[i]
                atr = [rr, 'M_RN_' + trasp['AREA'].loc[trasp['REMI'] == rr].values.tolist()[0]]
                atr.extend(GenerateCapacity(cap, int(m)).tolist())
                newremi[g_i] = atr
    elif 'Variazioni' in of:
        df = pd.read_excel(directory + '/' + variazioni, converters = {'Codice logico di riconsegna': str, 'Data Inizio': str})
        df.columns = [unidecode.unidecode(x) for x in df.columns.tolist()]
        for i in range(df.shape[0]):
            atr = []
            print(g_i)
            g_i += 1
            rr = df['Codice logico di riconsegna'].iloc[i]
            #if df['Capacita di trasporto conferita - Sm3 g'].iloc[i] < 0:
            cap = df['Capacita di trasporto conferita - Sm3 g'].iloc[i]
            atr = [rr, 'M_RN_' + trasp['AREA'].loc[trasp['REMI'] == rr].values.tolist()[0]]
            atr.extend(GenerateCapacity(cap, int(m)).tolist())
            newremi[g_i] = atr
                
num_nuovi_remi_retragas = 0                
newremi = pd.DataFrame.from_dict(newremi, orient = 'index')
if newremi.shape[0] > 0:
    newremi.reset_index(drop = True)
    newremi.columns = [['REMI', 'AREA', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]
    num_nuovi_remi_retragas = len(list(set(Rg['REMI'].values.tolist()).difference(set(newremi['REMI'].values.tolist()))))     
    Rg = Rg.append(newremi, ignore_index = True)
    
Rg2 = Rg.groupby('REMI')
Rg2 = Rg2.agg(sum)

#Rg = Rg.append(NRemi, ignore_index = True)

###### a qui 5 #########

###### da qui 6 #########


### SGI
directory = 'Z:\AREA ENERGY MANAGEMENT GAS\ESITI TRASPORTATORI\S.G.I.'
listfiles = [f for f in listdir(directory) if isfile(join(directory, f))]                 
base = [lf for lf in listfiles if 'CONF' in lf]
others = list(set(listfiles).difference(set(base)))

    
sgi = pd.read_excel(directory + '/' + base[0], converters = {'Punto di Riconsegna': str}) 
sgi.columns = [unidecode.unidecode(x) for x in sgi.columns.tolist()]

remi_sgi = list(set(sgi['Punto di Riconsegna'].values.tolist()))
    
    
sg = OrderedDict()
for rg in remi_sgi:
    index_rs = remi_sgi.index(rg)
    atrs = sgi.loc[sgi['Punto di Riconsegna'] == rg]
    atr = []
    atr.append(rg)
    atr.append('M_RN_' + trasp['AREA'].loc[trasp['REMI'] == rg].values.tolist()[0])
    mcg = np.repeat(sgi['Capacita impegnata (Sm3/g)'].loc[sgi['Punto di Riconsegna'] == rg], 12)
    atr.extend(mcg)
    sg[index_rs] = atr
    
sg = pd.DataFrame.from_dict(sg, orient = 'index')
sg.columns = [['REMI', 'AREA', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]

### check SGI:
# sg2 = sg

g_i = 0
newremi = OrderedDict()
for of in others:
    print(of)    
    if 'TRASPASS' not in of:
        df = pd.read_excel(directory + '/' + of, converters = {'PUNTO DI RICONSEGNA': str})
        df.columns = [unidecode.unidecode(x) for x in df.columns.tolist()]
        for i in range(df.shape[0]):
            print(i)
            g_i += 1
            rr = df['PUNTO DI RICONSEGNA'].iloc[i]
            m = str(df["DATA DI INIZIO VALIDITA' DELLA CAPACITA' RICHIESTA"].iloc[i].month)
            if rr != '' and str(rr) != 'nan':
                    atr = []
                    atr.append(rr)
                    atr.append('M_RN_' + trasp['AREA'].loc[trasp['REMI'] == rr].values.tolist()[0])
                    up = df["CAPACITA'\nOTTENUTA\n(Sm3/g)"].iloc[i]
                    atr.extend(GenerateCapacity(up, int(m)))
                    newremi[g_i] = atr
    else:
        df = pd.read_excel(directory + '/' + of, converters = {'PUNTO DI RICONSEGNA': str})
        df.columns = [unidecode.unidecode(x) for x in df.columns.tolist()]
        for i in range(df.shape[0]):
            print(i)
            g_i += 1
            rr = df['PUNTO DI RICONSEGNA'].iloc[i]
            m = str(df["DATA DI INIZIO VALIDITA' DELLA CAPACITA' RICHIESTA"].iloc[i].month)
            if rr != '' and str(rr) != 'nan':
                    atr = []
                    atr.append(rr)
                    atr.append('M_RN_' + trasp['AREA'].loc[trasp['REMI'] == rr].values.tolist()[0])
                    up = -df["CAPACITA'\nTRASFERITA\n(Sm3/g)"].iloc[i]
                    atr.extend(GenerateCapacity(up, int(m)))
                    newremi[g_i] = atr

num_nuovi_remi_sgi = 0
newremi = pd.DataFrame.from_dict(newremi, orient = 'index')
if newremi.shape[0] > 0:
    newremi.columns = [['REMI', 'AREA', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]
    num_nuovi_remi_sgi = len(list(set(sg['REMI'].values.tolist()).difference(set(newremi['REMI'].values.tolist())))) 

#sg = sg[sg.columns[:14]]

sg = sg.append(newremi, ignore_index = True)

sg2 = sg.groupby('REMI')
sg2 = sg2.agg(sum)

###### a qui 6 #########

###### da qui 7 #########


##### CMVGT
cvmgt = ['14061','NOR',5,5,5,5,5,5,5,5,5,5,5,5]
cvmgt = pd.DataFrame(cvmgt)
cvmgt = cvmgt.T
cvmgt.columns = [['REMI', 'AREA', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]

cvmgt = cvmgt.groupby('REMI')
cvmgt = cvmgt.agg(sum)
########  AGGREGATION OF THE TRANSPORTERS  
cg1 = cg2.append(Rg2)
CGM = cg1.append(sg2)
CGM = CGM.append(cvmgt)
CGM = CGM.drop('AREA', axis = 1)
cols = ['10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']
CGM = CGM[cols]
CGM['REMI'] = CGM.index
#grouped = CGM.groupby('REMI', 'AREA'])
###################### Aggiunta remi entranti #################################
entranti = list(set(resdf.REMI.values.tolist()).difference(set(CGM.index.values.tolist())))
DFE = OrderedDict()

for ent in entranti:
    res = []
    dfe = resdf.loc[resdf.REMI == ent]
    captot = dfe['DA CONFERIRE'].sum()
    res.extend(np.repeat(captot, 12).tolist())
    res.append(ent)
    DFE[ent] = res
    
DFE = pd.DataFrame.from_dict(DFE, orient = 'index')
DFE.columns = CGM.columns
CGM = CGM.append(DFE)

CGM = CGM.groupby('REMI')
CGM = CGM.agg(sum)

###### a qui 7 #########

###### da qui fino alla fine #########



###############################################################################
#cgm2 = grouped.agg(sum)
writer = pd.ExcelWriter('C:/Users/c_tozzi/CapGas/Conferimenti.xlsx')
CGM.to_excel(writer, sheet_name = 'Conferimenti REMI')
cgm2 = CGM

zone = OrderedDict()
zone['CEN'] = cen
zone['MER'] = mer
zone['NOC'] = noc
zone['NOR'] = nor
zone['SOC'] = soc
zone['SOR'] = sor

Z = pd.DataFrame.from_dict(zone, orient = 'index')
Z.columns = ['10','11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']

Z.to_excel(writer, sheet_name = 'Conferimenti ZONE')
#writer.save()
################################################ 
### Estimated requested capacity    

pdrprofile = OrderedDict()
remis = list(set(resdf['REMI'].values.tolist()))

for re in remis:
    re2 = ''
    if re == '34366101':
        re2 = '34366100'
    else:
        re2 = re
    atremi = resdf.loc[resdf['REMI'] == re]
    pdrl = list(set(atremi['PDR'].tolist()))
    for p in pdrl:
        atpdr = df218.loc[df218['PDR'] == p].reset_index(drop = True)
        if atpdr.shape[0] > 0:
            for i in range(atpdr.shape[0]):
                di = GetStartDate(atpdr['AXOPOWER SHIPPER'].iloc[i])
                df = GetEndDate(atpdr['FINE FORNITURA'].iloc[i])
                tot_cap = (atremi['DA CONFERIRE'].loc[atremi['PDR'] == p].tolist()[0]) * (ActiveAtMonth(di, df)) * (prof[atpdr['PROFILO PRELIEVO'].iloc[i]].resample('M').max()/100)
                pre = [re, trasp['AREA'].loc[trasp['REMI'] == re2].values.tolist()[0]]
                pre.extend(tot_cap.tolist())
                pdrprofile[str(p) + '_' + str(i)] = pre
    
    
pdrprofile = pd.DataFrame.from_dict(pdrprofile, orient = 'index')
#pdrprofile = pdrprofile.reset_index(drop = True)
pdrprofile.columns = [['REMI', 'AREA', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]
 
gbr = pdrprofile.groupby('REMI')
GBR = gbr.agg(sum)
GBR.to_excel(writer, sheet_name = 'Stima capacita richiesta')                 
#writer.save()
### Estimation of residual capacity

mtoday = datetime.datetime.now().month
diff = OrderedDict()
cap_remi = cgm2.index.tolist()
missing = []
for i in GBR.index.tolist():
    remi = i
    try:
        cap_remi.index(remi)
        dv = cgm2.loc[remi].values.ravel() - 1.2 * Regulator(GBR.loc[i].values.ravel(), mtoday)
        ld = [remi]
        ld.extend(dv.tolist())
        diff[i] = ld
    except:
        missing.append(remi)
print('mancano {} REMI'.format(len(missing)))
        
Diff = pd.DataFrame.from_dict(diff, orient = 'index')
Diff.columns = [['REMI', '10', '11', '12', '1', '2', '3', '4', '5', '6', '7', '8', '9']]

Diff.to_excel(writer,'Capacita residue')
writer.save()