Parsing Excel with Panda - Issues with blank cells

Hello all,

An issue concerning the Panda Excel Reader / how tables communicate blank cells:
If you use the Panda Excel Reader and don’t fill every value of the parsed table, the equivalent for the cell is NaN (None Type of tables in that case). Issue: For the storage class you Need None to enable the correct working of constraints.
Example:

    for i, s in storages.iterrows():
        if s['Investment'] == 1 :
                
            noded[i] = solph.components.GenericStorage(
                    label=i,                    
                    inputs={noded[s['bus']]: solph.Flow(variable_costs=s['variable_costs'],
                            investment = solph.Investment(ep_costs=economics.annuity(capex=s['capex_pump'], n=s['calendaric_lifetime'],wacc=s['wacc'])), max=s['max_power'])},
                    outputs={noded[s['bus_to']]: solph.Flow(variable_costs=s['variable_costs'],
                             investment = solph.Investment(ep_costs=economics.annuity(capex=s['capex_turbine'], n=s['calendaric_lifetime'],wacc=s['wacc'])), max=s['max_power'])},
                    capacity_loss=s['capacity_loss'],
                    initial_capacity=s['initial_capacity'],
                    capacity_max=s['max_capacity'], 
                    capacity_min=s['min_capacity'],
                    inflow_conversion_factor=s['efficiency_pump'],
                    outflow_conversion_factor=s['efficiency_turbine'],
                    nominal_input_capacity_ratio = s['input_capacity_ratio'],
                    nominal_output_capacity_ratio = s['output_capacity_ratio'],
                    investment = solph.Investment(ep_costs=economics.annuity(
                            capex=s['capex_capacity'], n=s['calendaric_lifetime'],wacc=s['wacc'])),
                            )

In that case the Excel is blank and Returns the value NaN for the attribute nominal_input_capacity_ratio, which will then give NaN into the construcotr of the storageInvestmentclass. Since for oemof NaN is not None (even though it means the same) the constraint is not issued correctly.

@uwe.krien ; I would like to hear your advice / ideas.

My ideas are all a Little “clumsy”.

  1. Set the values to 0 in the Excel and tell oemof to behave the same as with None.
  2. Also pass a NaN value to the constructor which then has to be compared for every Attribute

All is kinda messy and requires many if’s. It would be easier if you could Change every NaN value to None.

I think there are three ways solving your issue.

  1. You can really replace nan with None using pandas (see stackoverflow).

  2. We could teach oemof to skip the attribute no matter the value is None or nan. We need to discuss this in the oemof community.

  3. You could make your excel reader less generic and design it in a way that nan is not passed to any object (this is what I usually do). You may have to check one or two values for nan. For me this is not “clumsy” as you do have some checks any way (e.g. if s['Investment'] == 1).

Great it seems to be working with the first Option - using panda. I actually couldn’t find anything in the Panda doc. Some People note, that the df.where returns an object instead of a DataFrame. Not sure about the difference, does it matter at any point?

To your second proposal, I think thats a worthy addition - if anybody has time to implement it…

I think an object needs more memory than a float but I do not know it for sure. I think it does not matter in your case but you can ask at stackoverflow to get a good answer.

Please mark it as solved if it works. For the second solution you can start an issue at github.