data.datasets.pypsaeur.neighbor_reduction()

Complexity

Conditions

47

Size

Total Lines	1067
Code Lines	722

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

"""The central module containing all code dealing with importing data from
def neighbor_reduction():
    network_solved = read_network()
    network_prepared = prepared_network(planning_horizon="2045")

    # network.links.drop("pipe_retrofit", axis="columns", inplace=True)

    wanted_countries = countries_list()

    foreign_buses = network_solved.buses[
        (~network_solved.buses.index.str.contains("|".join(wanted_countries)))
        | (network_solved.buses.index.str.contains("FR6"))
    ]
    network_solved.buses = network_solved.buses.drop(
        network_solved.buses.loc[foreign_buses.index].index
    )

    # Add H2 demand of Fischer-Tropsch process and methanolisation
    # to industrial H2 demands
    industrial_hydrogen = network_prepared.loads.loc[
        network_prepared.loads.carrier == "H2 for industry"
    ]
    fischer_tropsch = (
        network_solved.links_t.p0[
            network_solved.links.loc[
                network_solved.links.carrier == "Fischer-Tropsch"
            ].index
        ]
        .mul(network_solved.snapshot_weightings.generators, axis=0)
        .sum()
    )
    methanolisation = (
        network_solved.links_t.p0[
            network_solved.links.loc[
                network_solved.links.carrier == "methanolisation"
            ].index
        ]
        .mul(network_solved.snapshot_weightings.generators, axis=0)
        .sum()
    )
    for i, row in industrial_hydrogen.iterrows():
        network_prepared.loads.loc[i, "p_set"] += (
            fischer_tropsch[
                fischer_tropsch.index.str.startswith(row.bus[:5])
            ].sum()
            / 8760
        )
        network_prepared.loads.loc[i, "p_set"] += (
            methanolisation[
                methanolisation.index.str.startswith(row.bus[:5])
            ].sum()
            / 8760
        )
    # drop foreign lines and links from the 2nd row

    network_solved.lines = network_solved.lines.drop(
        network_solved.lines[
            (
                network_solved.lines["bus0"].isin(network_solved.buses.index)
                == False
            )
            & (
                network_solved.lines["bus1"].isin(network_solved.buses.index)
                == False
            )
        ].index
    )

    # select all lines which have at bus1 the bus which is kept
    lines_cb_1 = network_solved.lines[
        (
            network_solved.lines["bus0"].isin(network_solved.buses.index)
            == False
        )
    ]

    # create a load at bus1 with the line's hourly loading
    for i, k in zip(lines_cb_1.bus1.values, lines_cb_1.index):

        # Copy loading of lines into hourly resolution
        pset = pd.Series(
            index=network_prepared.snapshots,
            data=network_solved.lines_t.p1[k].resample("H").ffill(),
        )
        pset["2011-12-31 22:00:00"] = pset["2011-12-31 21:00:00"]
        pset["2011-12-31 23:00:00"] = pset["2011-12-31 21:00:00"]

        # Loads are all imported from the prepared network in the end
        network_prepared.add(
            "Load",
            "slack_fix " + i + " " + k,
            bus=i,
            p_set=pset,
            carrier=lines_cb_1.loc[k, "carrier"],
        )

    # select all lines which have at bus0 the bus which is kept
    lines_cb_0 = network_solved.lines[
        (
            network_solved.lines["bus1"].isin(network_solved.buses.index)
            == False
        )
    ]

    # create a load at bus0 with the line's hourly loading
    for i, k in zip(lines_cb_0.bus0.values, lines_cb_0.index):
        # Copy loading of lines into hourly resolution
        pset = pd.Series(
            index=network_prepared.snapshots,
            data=network_solved.lines_t.p0[k].resample("H").ffill(),
        )
        pset["2011-12-31 22:00:00"] = pset["2011-12-31 21:00:00"]
        pset["2011-12-31 23:00:00"] = pset["2011-12-31 21:00:00"]

        network_prepared.add(
            "Load",
            "slack_fix " + i + " " + k,
            bus=i,
            p_set=pset,
            carrier=lines_cb_0.loc[k, "carrier"],
        )

    # do the same for links
    network_solved.mremove(
        "Link",
        network_solved.links[
            (~network_solved.links.bus0.isin(network_solved.buses.index))
            | (~network_solved.links.bus1.isin(network_solved.buses.index))
        ].index,
    )

    # select all links which have at bus1 the bus which is kept
    links_cb_1 = network_solved.links[
        (
            network_solved.links["bus0"].isin(network_solved.buses.index)
            == False
        )
    ]

    # create a load at bus1 with the link's hourly loading
    for i, k in zip(links_cb_1.bus1.values, links_cb_1.index):
        pset = pd.Series(
            index=network_prepared.snapshots,
            data=network_solved.links_t.p1[k].resample("H").ffill(),
        )
        pset["2011-12-31 22:00:00"] = pset["2011-12-31 21:00:00"]
        pset["2011-12-31 23:00:00"] = pset["2011-12-31 21:00:00"]

        network_prepared.add(
            "Load",
            "slack_fix_links " + i + " " + k,
            bus=i,
            p_set=pset,
            carrier=links_cb_1.loc[k, "carrier"],
        )

    # select all links which have at bus0 the bus which is kept
    links_cb_0 = network_solved.links[
        (
            network_solved.links["bus1"].isin(network_solved.buses.index)
            == False
        )
    ]

    # create a load at bus0 with the link's hourly loading
    for i, k in zip(links_cb_0.bus0.values, links_cb_0.index):
        pset = pd.Series(
            index=network_prepared.snapshots,
            data=network_solved.links_t.p0[k].resample("H").ffill(),
        )
        pset["2011-12-31 22:00:00"] = pset["2011-12-31 21:00:00"]
        pset["2011-12-31 23:00:00"] = pset["2011-12-31 21:00:00"]

        network_prepared.add(
            "Load",
            "slack_fix_links " + i + " " + k,
            bus=i,
            p_set=pset,
            carrier=links_cb_0.carrier[k],
        )

    # drop remaining foreign components
    for comp in network_solved.iterate_components():
        if "bus0" in comp.df.columns:
            network_solved.mremove(
                comp.name,
                comp.df[~comp.df.bus0.isin(network_solved.buses.index)].index,
            )
            network_solved.mremove(
                comp.name,
                comp.df[~comp.df.bus1.isin(network_solved.buses.index)].index,
            )
        elif "bus" in comp.df.columns:
            network_solved.mremove(
                comp.name,
                comp.df[~comp.df.bus.isin(network_solved.buses.index)].index,
            )

    # Combine urban decentral and rural heat
    network_prepared, network_solved = combine_decentral_and_rural_heat(
        network_solved, network_prepared
    )

    # writing components of neighboring countries to etrago tables

    # Set country tag for all buses
    network_solved.buses.country = network_solved.buses.index.str[:2]
    neighbors = network_solved.buses[network_solved.buses.country != "DE"]

    neighbors["new_index"] = (
        db.next_etrago_id("bus") + neighbors.reset_index().index
    )

    # Use index of AC buses created by electrical_neigbors
    foreign_ac_buses = db.select_dataframe(
        """
        SELECT * FROM grid.egon_etrago_bus
        WHERE carrier = 'AC' AND v_nom = 380
        AND country!= 'DE' AND scn_name ='eGon100RE'
        AND bus_id NOT IN (SELECT bus_i FROM osmtgmod_results.bus_data)
        """
    )
    buses_with_defined_id = neighbors[
        (neighbors.carrier == "AC")
        & (neighbors.country.isin(foreign_ac_buses.country.values))
    ].index
    neighbors.loc[buses_with_defined_id, "new_index"] = (
        foreign_ac_buses.set_index("x")
        .loc[neighbors.loc[buses_with_defined_id, "x"]]
        .bus_id.values
    )

    # lines, the foreign crossborder lines
    # (without crossborder lines to Germany!)

    neighbor_lines = network_solved.lines[
        network_solved.lines.bus0.isin(neighbors.index)
        & network_solved.lines.bus1.isin(neighbors.index)
    ]
    if not network_solved.lines_t["s_max_pu"].empty:
        neighbor_lines_t = network_prepared.lines_t["s_max_pu"][
            neighbor_lines.index
        ]

    neighbor_lines.reset_index(inplace=True)
    neighbor_lines.bus0 = (
        neighbors.loc[neighbor_lines.bus0, "new_index"].reset_index().new_index
    )
    neighbor_lines.bus1 = (
        neighbors.loc[neighbor_lines.bus1, "new_index"].reset_index().new_index
    )
    neighbor_lines.index += db.next_etrago_id("line")

    if not network_solved.lines_t["s_max_pu"].empty:
        for i in neighbor_lines_t.columns:

The variable neighbor_lines_t does not seem to be defined in case BooleanNotNode on line 915 is False. Are you sure this can never be the case?

            new_index = neighbor_lines[neighbor_lines["name"] == i].index
            neighbor_lines_t.rename(columns={i: new_index[0]}, inplace=True)

    # links
    neighbor_links = network_solved.links[
        network_solved.links.bus0.isin(neighbors.index)
        & network_solved.links.bus1.isin(neighbors.index)
    ]

    neighbor_links.reset_index(inplace=True)
    neighbor_links.bus0 = (
        neighbors.loc[neighbor_links.bus0, "new_index"].reset_index().new_index
    )
    neighbor_links.bus1 = (
        neighbors.loc[neighbor_links.bus1, "new_index"].reset_index().new_index
    )
    neighbor_links.index += db.next_etrago_id("link")

    # generators
    neighbor_gens = network_solved.generators[
        network_solved.generators.bus.isin(neighbors.index)
    ]
    neighbor_gens_t = network_prepared.generators_t["p_max_pu"][
        neighbor_gens[
            neighbor_gens.index.isin(
                network_prepared.generators_t["p_max_pu"].columns
            )
        ].index
    ]

    gen_time = [
        "solar",
        "onwind",
        "solar rooftop",
        "offwind-ac",
        "offwind-dc",
        "solar-hsat",
        "urban central solar thermal",
        "rural solar thermal",
        "offwind-float",
    ]

    missing_gent = neighbor_gens[
        neighbor_gens["carrier"].isin(gen_time)
        & ~neighbor_gens.index.isin(neighbor_gens_t.columns)
    ].index

    gen_timeseries = network_prepared.generators_t["p_max_pu"].copy()
    for mgt in missing_gent:  # mgt: missing generator timeseries
        try:
            neighbor_gens_t[mgt] = gen_timeseries.loc[:, mgt[0:-5]]
        except:
            print(f"There are not timeseries for {mgt}")

    neighbor_gens.reset_index(inplace=True)
    neighbor_gens.bus = (
        neighbors.loc[neighbor_gens.bus, "new_index"].reset_index().new_index
    )
    neighbor_gens.index += db.next_etrago_id("generator")

    for i in neighbor_gens_t.columns:
        new_index = neighbor_gens[neighbor_gens["Generator"] == i].index
        neighbor_gens_t.rename(columns={i: new_index[0]}, inplace=True)

    # loads
    # imported from prenetwork in 1h-resolution
    neighbor_loads = network_prepared.loads[
        network_prepared.loads.bus.isin(neighbors.index)
    ]
    neighbor_loads_t_index = neighbor_loads.index[
        neighbor_loads.index.isin(network_prepared.loads_t.p_set.columns)
    ]
    neighbor_loads_t = network_prepared.loads_t["p_set"][
        neighbor_loads_t_index
    ]

    neighbor_loads.reset_index(inplace=True)
    neighbor_loads.bus = (
        neighbors.loc[neighbor_loads.bus, "new_index"].reset_index().new_index
    )
    neighbor_loads.index += db.next_etrago_id("load")

    for i in neighbor_loads_t.columns:
        new_index = neighbor_loads[neighbor_loads["Load"] == i].index
        neighbor_loads_t.rename(columns={i: new_index[0]}, inplace=True)

    # stores
    neighbor_stores = network_solved.stores[
        network_solved.stores.bus.isin(neighbors.index)
    ]
    neighbor_stores_t_index = neighbor_stores.index[
        neighbor_stores.index.isin(network_solved.stores_t.e_min_pu.columns)
    ]
    neighbor_stores_t = network_prepared.stores_t["e_min_pu"][
        neighbor_stores_t_index
    ]

    neighbor_stores.reset_index(inplace=True)
    neighbor_stores.bus = (
        neighbors.loc[neighbor_stores.bus, "new_index"].reset_index().new_index
    )
    neighbor_stores.index += db.next_etrago_id("store")

    for i in neighbor_stores_t.columns:
        new_index = neighbor_stores[neighbor_stores["Store"] == i].index
        neighbor_stores_t.rename(columns={i: new_index[0]}, inplace=True)

    # storage_units
    neighbor_storage = network_solved.storage_units[
        network_solved.storage_units.bus.isin(neighbors.index)
    ]
    neighbor_storage_t_index = neighbor_storage.index[
        neighbor_storage.index.isin(
            network_solved.storage_units_t.inflow.columns
        )
    ]
    neighbor_storage_t = network_prepared.storage_units_t["inflow"][
        neighbor_storage_t_index
    ]

    neighbor_storage.reset_index(inplace=True)
    neighbor_storage.bus = (
        neighbors.loc[neighbor_storage.bus, "new_index"]
        .reset_index()
        .new_index
    )
    neighbor_storage.index += db.next_etrago_id("storage")

    for i in neighbor_storage_t.columns:
        new_index = neighbor_storage[
            neighbor_storage["StorageUnit"] == i
        ].index
        neighbor_storage_t.rename(columns={i: new_index[0]}, inplace=True)

    # Connect to local database
    engine = db.engine()

    neighbors["scn_name"] = "eGon100RE"
    neighbors.index = neighbors["new_index"]

    # Correct geometry for non AC buses
    carriers = set(neighbors.carrier.to_list())
    carriers = [e for e in carriers if e not in ("AC")]
    non_AC_neighbors = pd.DataFrame()
    for c in carriers:
        c_neighbors = neighbors[neighbors.carrier == c].set_index(
            "location", drop=False
        )
        for i in ["x", "y"]:
            c_neighbors = c_neighbors.drop(i, axis=1)
        coordinates = neighbors[neighbors.carrier == "AC"][
            ["location", "x", "y"]
        ].set_index("location")
        c_neighbors = pd.concat([coordinates, c_neighbors], axis=1).set_index(
            "new_index", drop=False
        )
        non_AC_neighbors = pd.concat([non_AC_neighbors, c_neighbors])

    neighbors = pd.concat(
        [neighbors[neighbors.carrier == "AC"], non_AC_neighbors]
    )

    for i in [
        "new_index",
        "control",
        "generator",
        "location",
        "sub_network",
        "unit",
        "substation_lv",
        "substation_off",
    ]:
        neighbors = neighbors.drop(i, axis=1)

    # Add geometry column
    neighbors = (
        gpd.GeoDataFrame(
            neighbors, geometry=gpd.points_from_xy(neighbors.x, neighbors.y)
        )
        .rename_geometry("geom")
        .set_crs(4326)
    )

    # Unify carrier names
    neighbors.carrier = neighbors.carrier.str.replace(" ", "_")
    neighbors.carrier.replace(
        {
            "gas": "CH4",
            "gas_for_industry": "CH4_for_industry",
            "urban_central_heat": "central_heat",
            "EV_battery": "Li_ion",
            "urban_central_water_tanks": "central_heat_store",
            "rural_water_tanks": "rural_heat_store",
        },
        inplace=True,
    )

    neighbors[~neighbors.carrier.isin(["AC"])].to_postgis(
        "egon_etrago_bus",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="bus_id",
    )

    # prepare and write neighboring crossborder lines to etrago tables
    def lines_to_etrago(neighbor_lines=neighbor_lines, scn="eGon100RE"):
        neighbor_lines["scn_name"] = scn
        neighbor_lines["cables"] = 3 * neighbor_lines["num_parallel"].astype(
            int
        )
        neighbor_lines["s_nom"] = neighbor_lines["s_nom_min"]

        for i in [
            "Line",
            "x_pu_eff",
            "r_pu_eff",
            "sub_network",
            "x_pu",
            "r_pu",
            "g_pu",
            "b_pu",
            "s_nom_opt",
            "i_nom",
            "dc",
        ]:
            neighbor_lines = neighbor_lines.drop(i, axis=1)

        # Define geometry and add to lines dataframe as 'topo'
        gdf = gpd.GeoDataFrame(index=neighbor_lines.index)
        gdf["geom_bus0"] = neighbors.geom[neighbor_lines.bus0].values
        gdf["geom_bus1"] = neighbors.geom[neighbor_lines.bus1].values
        gdf["geometry"] = gdf.apply(
            lambda x: LineString([x["geom_bus0"], x["geom_bus1"]]), axis=1
        )

        neighbor_lines = (
            gpd.GeoDataFrame(neighbor_lines, geometry=gdf["geometry"])
            .rename_geometry("topo")
            .set_crs(4326)
        )

        neighbor_lines["lifetime"] = get_sector_parameters("electricity", scn)[
            "lifetime"
        ]["ac_ehv_overhead_line"]

        neighbor_lines.to_postgis(
            "egon_etrago_line",
            engine,
            schema="grid",
            if_exists="append",
            index=True,
            index_label="line_id",
        )

    lines_to_etrago(neighbor_lines=neighbor_lines, scn="eGon100RE")

    def links_to_etrago(neighbor_links, scn="eGon100RE", extendable=True):
        """Prepare and write neighboring crossborder links to eTraGo table

        This function prepare the neighboring crossborder links
        generated the PyPSA-eur-sec (p-e-s) run by:
          * Delete the useless columns
          * If extendable is false only (non default case):
              * Replace p_nom = 0 with the p_nom_op values (arrising
                from the p-e-s optimisation)
              * Setting p_nom_extendable to false
          * Add geomtry to the links: 'geom' and 'topo' columns
          * Change the name of the carriers to have the consistent in
            eGon-data

        The function insert then the link to the eTraGo table and has
        no return.

        Parameters
        ----------
        neighbor_links : pandas.DataFrame
            Dataframe containing the neighboring crossborder links
        scn_name : str
            Name of the scenario
        extendable : bool
            Boolean expressing if the links should be extendable or not

        Returns
        -------
        None

        """
        neighbor_links["scn_name"] = scn

        dropped_carriers = [
            "Link",
            "geometry",
            "tags",
            "under_construction",
            "underground",
            "underwater_fraction",
            "bus2",
            "bus3",
            "bus4",
            "efficiency2",
            "efficiency3",
            "efficiency4",
            "lifetime",
            "pipe_retrofit",
            "committable",
            "start_up_cost",
            "shut_down_cost",
            "min_up_time",
            "min_down_time",
            "up_time_before",
            "down_time_before",
            "ramp_limit_up",
            "ramp_limit_down",
            "ramp_limit_start_up",
            "ramp_limit_shut_down",
            "length_original",
            "reversed",
            "location",
            "project_status",
            "dc",
            "voltage",
        ]

        if extendable:
            dropped_carriers.append("p_nom_opt")
            neighbor_links = neighbor_links.drop(
                columns=dropped_carriers,
                errors="ignore",
            )

        else:
            dropped_carriers.append("p_nom")
            dropped_carriers.append("p_nom_extendable")
            neighbor_links = neighbor_links.drop(
                columns=dropped_carriers,
                errors="ignore",
            )
            neighbor_links = neighbor_links.rename(
                columns={"p_nom_opt": "p_nom"}
            )
            neighbor_links["p_nom_extendable"] = False

        if neighbor_links.empty:
            print("No links selected")
            return

        # Define geometry and add to lines dataframe as 'topo'
        gdf = gpd.GeoDataFrame(
            index=neighbor_links.index,
            data={
                "geom_bus0": neighbors.loc[neighbor_links.bus0, "geom"].values,
                "geom_bus1": neighbors.loc[neighbor_links.bus1, "geom"].values,
            },
        )

        gdf["geometry"] = gdf.apply(
            lambda x: LineString([x["geom_bus0"], x["geom_bus1"]]), axis=1
        )

        neighbor_links = (
            gpd.GeoDataFrame(neighbor_links, geometry=gdf["geometry"])
            .rename_geometry("topo")
            .set_crs(4326)
        )

        # Unify carrier names
        neighbor_links.carrier = neighbor_links.carrier.str.replace(" ", "_")

        neighbor_links.carrier.replace(
            {
                "H2_Electrolysis": "power_to_H2",
                "H2_Fuel_Cell": "H2_to_power",
                "H2_pipeline_retrofitted": "H2_retrofit",
                "SMR": "CH4_to_H2",
                "Sabatier": "H2_to_CH4",
                "gas_for_industry": "CH4_for_industry",
                "gas_pipeline": "CH4",
                "urban_central_gas_boiler": "central_gas_boiler",
                "urban_central_resistive_heater": "central_resistive_heater",
                "urban_central_water_tanks_charger": "central_heat_store_charger",
                "urban_central_water_tanks_discharger": "central_heat_store_discharger",
                "rural_water_tanks_charger": "rural_heat_store_charger",
                "rural_water_tanks_discharger": "rural_heat_store_discharger",
                "urban_central_gas_CHP": "central_gas_CHP",
                "urban_central_air_heat_pump": "central_heat_pump",
                "rural_ground_heat_pump": "rural_heat_pump",
            },
            inplace=True,
        )

        H2_links = {
            "H2_to_CH4": "H2_to_CH4",
            "H2_to_power": "H2_to_power",
            "power_to_H2": "power_to_H2_system",
            "CH4_to_H2": "CH4_to_H2",
        }

        for c in H2_links.keys():

            neighbor_links.loc[
                (neighbor_links.carrier == c),
                "lifetime",
            ] = get_sector_parameters("gas", "eGon100RE")["lifetime"][
                H2_links[c]
            ]

        neighbor_links.to_postgis(
            "egon_etrago_link",
            engine,
            schema="grid",
            if_exists="append",
            index=True,
            index_label="link_id",
        )

    extendable_links_carriers = [
        "battery charger",
        "battery discharger",
        "home battery charger",
        "home battery discharger",
        "rural water tanks charger",
        "rural water tanks discharger",
        "urban central water tanks charger",
        "urban central water tanks discharger",
        "urban decentral water tanks charger",
        "urban decentral water tanks discharger",
        "H2 Electrolysis",
        "H2 Fuel Cell",
        "SMR",
        "Sabatier",
    ]

    # delete unwanted carriers for eTraGo
    excluded_carriers = [
        "gas for industry CC",
        "SMR CC",
        "DAC",
    ]
    neighbor_links = neighbor_links[
        ~neighbor_links.carrier.isin(excluded_carriers)
    ]

    # Combine CHP_CC and CHP
    chp_cc = neighbor_links[
        neighbor_links.carrier == "urban central gas CHP CC"
    ]
    for index, row in chp_cc.iterrows():
        neighbor_links.loc[
            neighbor_links.Link == row.Link.replace("CHP CC", "CHP"),
            "p_nom_opt",
        ] += row.p_nom_opt
        neighbor_links.loc[
            neighbor_links.Link == row.Link.replace("CHP CC", "CHP"), "p_nom"
        ] += row.p_nom
        neighbor_links.drop(index, inplace=True)

    # Combine heat pumps
    # Like in Germany, there are air heat pumps in central heat grids
    # and ground heat pumps in rural areas
    rural_air = neighbor_links[neighbor_links.carrier == "rural air heat pump"]
    for index, row in rural_air.iterrows():
        neighbor_links.loc[
            neighbor_links.Link == row.Link.replace("air", "ground"),
            "p_nom_opt",
        ] += row.p_nom_opt
        neighbor_links.loc[
            neighbor_links.Link == row.Link.replace("air", "ground"), "p_nom"
        ] += row.p_nom
        neighbor_links.drop(index, inplace=True)
    links_to_etrago(
        neighbor_links[neighbor_links.carrier.isin(extendable_links_carriers)],
        "eGon100RE",
    )
    links_to_etrago(
        neighbor_links[
            ~neighbor_links.carrier.isin(extendable_links_carriers)
        ],
        "eGon100RE",
        extendable=False,
    )
    # Include links time-series
    # For heat_pumps
    hp = neighbor_links[neighbor_links["carrier"].str.contains("heat pump")]

    neighbor_eff_t = network_prepared.links_t["efficiency"][
        hp[hp.Link.isin(network_prepared.links_t["efficiency"].columns)].index
    ]

    missing_hp = hp[~hp["Link"].isin(neighbor_eff_t.columns)].Link

    eff_timeseries = network_prepared.links_t["efficiency"].copy()
    for met in missing_hp:  # met: missing efficiency timeseries
        try:
            neighbor_eff_t[met] = eff_timeseries.loc[:, met[0:-5]]
        except:
            print(f"There are not timeseries for heat_pump {met}")

    for i in neighbor_eff_t.columns:
        new_index = neighbor_links[neighbor_links["Link"] == i].index
        neighbor_eff_t.rename(columns={i: new_index[0]}, inplace=True)

    # Include links time-series
    # For ev_chargers
    ev = neighbor_links[neighbor_links["carrier"].str.contains("BEV charger")]

    ev_p_max_pu = network_prepared.links_t["p_max_pu"][
        ev[ev.Link.isin(network_prepared.links_t["p_max_pu"].columns)].index
    ]

    missing_ev = ev[~ev["Link"].isin(ev_p_max_pu.columns)].Link

    ev_p_max_pu_timeseries = network_prepared.links_t["p_max_pu"].copy()
    for mct in missing_ev:  # evt: missing charger timeseries
        try:
            ev_p_max_pu[mct] = ev_p_max_pu_timeseries.loc[:, mct[0:-5]]
        except:
            print(f"There are not timeseries for EV charger {mct}")

    for i in ev_p_max_pu.columns:
        new_index = neighbor_links[neighbor_links["Link"] == i].index
        ev_p_max_pu.rename(columns={i: new_index[0]}, inplace=True)

    # prepare neighboring generators for etrago tables
    neighbor_gens["scn_name"] = "eGon100RE"
    neighbor_gens["p_nom"] = neighbor_gens["p_nom_opt"]
    neighbor_gens["p_nom_extendable"] = False

    # Unify carrier names
    neighbor_gens.carrier = neighbor_gens.carrier.str.replace(" ", "_")

    neighbor_gens.carrier.replace(
        {
            "onwind": "wind_onshore",
            "ror": "run_of_river",
            "offwind-ac": "wind_offshore",
            "offwind-dc": "wind_offshore",
            "offwind-float": "wind_offshore",
            "urban_central_solar_thermal": "urban_central_solar_thermal_collector",
            "residential_rural_solar_thermal": "residential_rural_solar_thermal_collector",
            "services_rural_solar_thermal": "services_rural_solar_thermal_collector",
            "solar-hsat": "solar",
        },
        inplace=True,
    )

    for i in [
        "Generator",
        "weight",
        "lifetime",
        "p_set",
        "q_set",
        "p_nom_opt",
        "e_sum_min",
        "e_sum_max",
    ]:
        neighbor_gens = neighbor_gens.drop(i, axis=1)

    neighbor_gens.to_sql(
        "egon_etrago_generator",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="generator_id",
    )

    # prepare neighboring loads for etrago tables
    neighbor_loads["scn_name"] = "eGon100RE"

    # Unify carrier names
    neighbor_loads.carrier = neighbor_loads.carrier.str.replace(" ", "_")

    neighbor_loads.carrier.replace(
        {
            "electricity": "AC",
            "DC": "AC",
            "industry_electricity": "AC",
            "H2_pipeline_retrofitted": "H2_system_boundary",
            "gas_pipeline": "CH4_system_boundary",
            "gas_for_industry": "CH4_for_industry",
            "urban_central_heat": "central_heat",
        },
        inplace=True,
    )

    neighbor_loads = neighbor_loads.drop(
        columns=["Load"],
        errors="ignore",
    )

    neighbor_loads.to_sql(
        "egon_etrago_load",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="load_id",
    )

    # prepare neighboring stores for etrago tables
    neighbor_stores["scn_name"] = "eGon100RE"

    # Unify carrier names
    neighbor_stores.carrier = neighbor_stores.carrier.str.replace(" ", "_")

    neighbor_stores.carrier.replace(
        {
            "Li_ion": "battery",
            "gas": "CH4",
            "urban_central_water_tanks": "central_heat_store",
            "rural_water_tanks": "rural_heat_store",
            "EV_battery": "battery_storage",
        },
        inplace=True,
    )
    neighbor_stores.loc[
        (
            (neighbor_stores.e_nom_max <= 1e9)
            & (neighbor_stores.carrier == "H2_Store")
        ),
        "carrier",
    ] = "H2_underground"
    neighbor_stores.loc[
        (
            (neighbor_stores.e_nom_max > 1e9)
            & (neighbor_stores.carrier == "H2_Store")
        ),
        "carrier",
    ] = "H2_overground"

    for i in [
        "Store",
        "p_set",
        "q_set",
        "e_nom_opt",
        "lifetime",
        "e_initial_per_period",
        "e_cyclic_per_period",
        "location",
    ]:
        neighbor_stores = neighbor_stores.drop(i, axis=1, errors="ignore")

    for c in ["H2_underground", "H2_overground"]:
        neighbor_stores.loc[
            (neighbor_stores.carrier == c),
            "lifetime",
        ] = get_sector_parameters("gas", "eGon100RE")["lifetime"][c]

    neighbor_stores.to_sql(
        "egon_etrago_store",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="store_id",
    )

    # prepare neighboring storage_units for etrago tables
    neighbor_storage["scn_name"] = "eGon100RE"

    # Unify carrier names
    neighbor_storage.carrier = neighbor_storage.carrier.str.replace(" ", "_")

    neighbor_storage.carrier.replace(
        {"PHS": "pumped_hydro", "hydro": "reservoir"}, inplace=True
    )

    for i in [
        "StorageUnit",
        "p_nom_opt",
        "state_of_charge_initial_per_period",
        "cyclic_state_of_charge_per_period",
    ]:
        neighbor_storage = neighbor_storage.drop(i, axis=1, errors="ignore")

    neighbor_storage.to_sql(
        "egon_etrago_storage",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="storage_id",
    )

    # writing neighboring loads_t p_sets to etrago tables

    neighbor_loads_t_etrago = pd.DataFrame(
        columns=["scn_name", "temp_id", "p_set"],
        index=neighbor_loads_t.columns,
    )
    neighbor_loads_t_etrago["scn_name"] = "eGon100RE"
    neighbor_loads_t_etrago["temp_id"] = 1
    for i in neighbor_loads_t.columns:
        neighbor_loads_t_etrago["p_set"][i] = neighbor_loads_t[
            i
        ].values.tolist()

    neighbor_loads_t_etrago.to_sql(
        "egon_etrago_load_timeseries",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="load_id",
    )

    # writing neighboring link_t efficiency and p_max_pu to etrago tables
    neighbor_link_t_etrago = pd.DataFrame(
        columns=["scn_name", "temp_id", "p_max_pu", "efficiency"],
        index=neighbor_eff_t.columns.to_list() + ev_p_max_pu.columns.to_list(),
    )
    neighbor_link_t_etrago["scn_name"] = "eGon100RE"
    neighbor_link_t_etrago["temp_id"] = 1
    for i in neighbor_eff_t.columns:
        neighbor_link_t_etrago["efficiency"][i] = neighbor_eff_t[
            i
        ].values.tolist()
    for i in ev_p_max_pu.columns:
        neighbor_link_t_etrago["p_max_pu"][i] = ev_p_max_pu[i].values.tolist()

    neighbor_link_t_etrago.to_sql(
        "egon_etrago_link_timeseries",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="link_id",
    )

    # writing neighboring generator_t p_max_pu to etrago tables
    neighbor_gens_t_etrago = pd.DataFrame(
        columns=["scn_name", "temp_id", "p_max_pu"],
        index=neighbor_gens_t.columns,
    )
    neighbor_gens_t_etrago["scn_name"] = "eGon100RE"
    neighbor_gens_t_etrago["temp_id"] = 1
    for i in neighbor_gens_t.columns:
        neighbor_gens_t_etrago["p_max_pu"][i] = neighbor_gens_t[
            i
        ].values.tolist()

    neighbor_gens_t_etrago.to_sql(
        "egon_etrago_generator_timeseries",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="generator_id",
    )

    # writing neighboring stores_t e_min_pu to etrago tables
    neighbor_stores_t_etrago = pd.DataFrame(
        columns=["scn_name", "temp_id", "e_min_pu"],
        index=neighbor_stores_t.columns,
    )
    neighbor_stores_t_etrago["scn_name"] = "eGon100RE"
    neighbor_stores_t_etrago["temp_id"] = 1
    for i in neighbor_stores_t.columns:
        neighbor_stores_t_etrago["e_min_pu"][i] = neighbor_stores_t[
            i
        ].values.tolist()

    neighbor_stores_t_etrago.to_sql(
        "egon_etrago_store_timeseries",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="store_id",
    )

    # writing neighboring storage_units inflow to etrago tables
    neighbor_storage_t_etrago = pd.DataFrame(
        columns=["scn_name", "temp_id", "inflow"],
        index=neighbor_storage_t.columns,
    )
    neighbor_storage_t_etrago["scn_name"] = "eGon100RE"
    neighbor_storage_t_etrago["temp_id"] = 1
    for i in neighbor_storage_t.columns:
        neighbor_storage_t_etrago["inflow"][i] = neighbor_storage_t[
            i
        ].values.tolist()

    neighbor_storage_t_etrago.to_sql(
        "egon_etrago_storage_timeseries",
        engine,
        schema="grid",
        if_exists="append",
        index=True,
        index_label="storage_id",
    )

    # writing neighboring lines_t s_max_pu to etrago tables
    if not network_solved.lines_t["s_max_pu"].empty:
        neighbor_lines_t_etrago = pd.DataFrame(
            columns=["scn_name", "s_max_pu"], index=neighbor_lines_t.columns
        )
        neighbor_lines_t_etrago["scn_name"] = "eGon100RE"

        for i in neighbor_lines_t.columns:
            neighbor_lines_t_etrago["s_max_pu"][i] = neighbor_lines_t[
                i
            ].values.tolist()

        neighbor_lines_t_etrago.to_sql(
            "egon_etrago_line_timeseries",
            engine,
            schema="grid",
            if_exists="append",
            index=True,
            index_label="line_id",
        )