# -*- coding: utf-8 -*- """ General description ------------------- As the csv-reader was removed with version 0.2 this example shows how to create an excel-reader. The example is equivalent to the old csv-reader example. Following the example one can customise the excel reader to ones own needs. The pandas package supports the '.xls' and the '.xlsx' format but one can create read and adept the files with open source software such as libreoffice, openoffice, gnumeric,... Data ---- scenario.xlsx Installation requirements ------------------------- This example requires the latest version of oemof (v0.3.x) and others. Install by: pip3 install 'oemof<0.4,>=0.3' pip3 install xlrd pip3 install matplotlib pip3 install networkx If you want to plot the energy system's graph, you have to install pygraphviz using: pip3 install pygraphviz For pygraphviz under Windows, some hints are available in the oemof Wiki: https://github.com/oemof/oemof/wiki/Windows---general 5.1.2018 - uwe.krien@rl-institut.de 7.5.2018 - jonathan.amme@rl-institut.de """ __copyright__ = "oemof developer group" __license__ = "GPLv3" import os import logging import pandas as pd from oemof.tools import logger from oemof import solph from oemof import outputlib def nodes_from_excel(filename): """Read node data from Excel sheet Parameters ---------- filename : :obj:`str` Path to excel file Returns ------- :obj:`dict` Imported nodes data """ # does Excel file exist? if not filename or not os.path.isfile(filename): raise FileNotFoundError('Excel data file {} not found.' .format(filename)) xls = pd.ExcelFile(filename) nodes_data = {'buses': xls.parse('buses'), 'renewables': xls.parse('renewables'), 'demand': xls.parse('demand'), 'powerlines': xls.parse('powerlines'), 'timeseries': xls.parse('time_series') } # set datetime index nodes_data['timeseries'].set_index('timestamp', inplace=True) nodes_data['timeseries'].index = pd.to_datetime( nodes_data['timeseries'].index) print('Data from Excel file {} imported.' .format(filename)) return nodes_data def create_nodes(nd=None): """Create nodes (oemof objects) from node dict Parameters ---------- nd : :obj:`dict` Nodes data Returns ------- nodes : `obj`:dict of :class:`nodes ` """ if not nd: raise ValueError('No nodes data provided.') nodes = [] # Create Bus objects from buses table busd = {} for i, b in nd['buses'].iterrows(): if b['active']: bus = solph.Bus(label=b['label']) nodes.append(bus) busd[b['label']] = bus if b['excess']: nodes.append( solph.Sink(label=b['label'] + '_excess', inputs={busd[b['label']]: solph.Flow( variable_costs=b['excess costs'])}) ) if b['shortage']: nodes.append( solph.Source(label=b['label'] + '_shortage', outputs={busd[b['label']]: solph.Flow( variable_costs=b['shortage costs'])}) ) # Create Source objects with fixed time series from 'renewables' table for i, re in nd['renewables'].iterrows(): if re['active']: # set static outflow values outflow_args = {'nominal_value': re['capacity'], 'fixed': True} # get time series for node and parameter for col in nd['timeseries'].columns.values: if col.split('.')[0] == re['label']: outflow_args[col.split('.')[1]] = nd['timeseries'][col] # create nodes.append( solph.Source(label=re['label'], outputs={ busd[re['to']]: solph.Flow(**outflow_args)}) ) # Create Sink objects with fixed time series from 'demand' table for i, de in nd['demand'].iterrows(): if de['active']: # set static inflow values inflow_args = {'nominal_value': de['nominal value'], 'fixed': de['fixed']} # get time series for node and parameter for col in nd['timeseries'].columns.values: if col.split('.')[0] == de['label']: inflow_args[col.split('.')[1]] = nd['timeseries'][col] # create nodes.append( solph.Sink(label=de['label'], inputs={ busd[de['from']]: solph.Flow(**inflow_args)}) ) for i, p in nd['powerlines'].iterrows(): if p['active']: bus1 = busd[p['bus_1']] bus2 = busd[p['bus_2']] nodes.append( solph.custom.Link( label='powerline' + '_' + p['bus_1'] + '_' + p['bus_2'], inputs={bus1: solph.Flow(), bus2: solph.Flow()}, outputs={bus1: solph.Flow(nominal_value=p['capacity'], variable_costs=p['variable costs']), bus2: solph.Flow(nominal_value=p['capacity'], variable_costs=p['variable costs']) }, conversion_factors={(bus1, bus2): p['efficiency'], (bus2, bus1): p['efficiency']}) ) return nodes logger.define_logging() datetime_index = pd.date_range('2016-01-01 00:00:00', '2016-01-01 23:00:00', freq='60min') # model creation and solving logging.info('Starting optimization') # initialisation of the energy system esys = solph.EnergySystem(timeindex=datetime_index) # read node data from Excel sheet excel_nodes = nodes_from_excel( os.path.join(os.path.dirname(__file__), 'scenario.xlsx',)) # create nodes from Excel sheet data my_nodes = create_nodes(nd=excel_nodes) # add nodes and flows to energy system esys.add(*my_nodes) print("*********************************************************") print("The following objects have been created from excel sheet:") for n in esys.nodes: oobj = str(type(n)).replace("", "") print(oobj + ':', n.label) print("*********************************************************") # creation of a least cost model from the energy system om = solph.Model(esys) om.receive_duals() # solving the linear problem using the given solver om.solve(solver='cbc') # print and plot some results results = outputlib.processing.results(om) outputlib.processing.parameter_as_dict(esys, exclude_none=True) logging.info("Done!") writer = pd.ExcelWriter('results.xlsx') block = outputlib.views.node(results, 'R2_bus_el') (block['sequences']).to_excel(writer, 'R2_bus_el') block = outputlib.views.node(results, 'R1_bus_el') (block['sequences']).to_excel(writer, 'R1_bus_el') writer.save()