Check stitch

View or edit on GitHub

This page is synchronized from trase/models/indonesia/palm_oil/Check_stitch.ipynb. Last modified on 2026-08-05 15:56 CEST by Harry Biddle. Please view or edit the original file there; changes should be reflected here after a midnight build (CET time), or manually triggering it with a GitHub action (link).

import pandas as pd
from trase.tools.sps import SupplyChain
from trase.models.indonesia.palm_oil.demand import get_untraceable_demand
from trase.models.indonesia.palm_oil.process import process
from trase.tools.sps import stitch_dataframes
from trase.models.indonesia.palm_oil.flows import get_exports
from trase.tools.sps import (
    consolidate,
    print_message,
    stitch_dataframes,
    warn_if_supply_sheds_demand_exceeds_supply,
)

supplychain = SupplyChain("indonesia/palm_oil", year=2024)
supplychain.load()

# print(supplychain.get("concession").head())
# print(supplychain.get("exporter_mill").head())
print(supplychain.get("flows").head())

df_untraceable_demand = get_untraceable_demand(supplychain)
print("df_untraceable_demand")
print(df_untraceable_demand.head())
print(df_untraceable_demand.columns)

allocation = pd.read_csv("allocation_raw_lp_2.csv", index_col=[0, 1]).squeeze("columns")
df_lp_2 = allocation.reset_index()
df_lp_2["destination"] = "all"

df_lp_2 = pd.read_csv("df_lp_2_before_stitch.csv")
print(df_lp_2.head())

df_pre_lp_1 = pd.read_csv("df_pre_lp_1.csv")

df_lp_1 = pd.read_csv("df_lp_1.csv")
print(df_lp_1.head())

df_stitched = stitch_dataframes(
    df_lp_2, df_untraceable_demand, volume_column="vol"
).drop(columns="destination")

df_stitched.head()
df_stitched.columns.tolist()

df = pd.read_csv("df_before_process.csv", low_memory=False)
df.columns.tolist()
print(df.head())

# check why we have refinery id as NAN
df = pd.read_csv("df_before_process.csv", low_memory=False)

print(df["refinery_id"].dtype)
print(df["refinery_id"].isna().sum())
print(df["refinery_id"].value_counts(dropna=False).head(20))
# likely CPO

# HERE
import pandas as pd
from trase.tools.sps import SupplyChain, stitch_dataframes, consolidate
from trase.models.indonesia.palm_oil.flows import get_exports

year = 2024

supplychain = SupplyChain(
    "indonesia/palm_oil",
    year=year,
)
supplychain.preparation()
supplychain.load()

df = pd.read_csv("df_before_process.csv", low_memory=False)


print("start")
print(df["vol"].sum())
print(df.columns.tolist())

exporters = supplychain.get("exporter").rename(
    columns={
        "id": "exporter_id_raw",
        "name": "exporter",
        "group": "exporter_group",
    }
)
exporters["exporter_trase_id"] = exporters["exporter_id_raw"].str.replace(
    "E-", "ID-TRADER-", regex=False
)

mills = supplychain.get("mill", subset=["id", "company"]).rename(
    columns={
        "id": "mill_id_raw",
        "company": "mill",
    }
)

refinery_groups = supplychain.get(
    "refinery_group",
    subset=["refinery_trase_id", "refinery_name", "refinery_group"],
)

mill_groups = supplychain.get("mill_group")

concessions = supplychain.get(
    "concession",
    subset=["concession", "kabupaten"],
).drop_duplicates(subset=["concession"])

for col in [
    "concession",
    "refinery_id",
    "mill_id",
    "port_export_id",
    "exporter_id",
    "branch",
    "key",
    "commodity",
]:
    df[col] = df[col].fillna("NA").astype(str)

df["is_unknown"] = df["is_unknown"].replace("NA", False).fillna(False)
df["excess"] = df["excess"].replace("NA", False).fillna(False)
df["year"] = str(int(supplychain.context.year))

print(df["vol"].sum())
print(df.columns.tolist())

df_exports = supplychain.get(
    "flows",
    subset=[
        "commodity",
        "exporter.id",
        "port_of_export.id",
        "certification",
        "importer",
        "country",
        "hs",
        "vol",
        "fob",
    ],
).rename(
    columns={
        "exporter.id": "exporter_id",
        "port_of_export.id": "port_export_id",
    },
    errors="raise",
)

df_exports = df_exports.drop(columns=["_id"], errors="ignore")
df_exports["year"] = str(int(supplychain.context.year))

for col in ["commodity", "exporter_id", "port_export_id"]:
    df_exports[col] = df_exports[col].fillna("NA").astype(str)

for col in ["certification", "importer", "country", "hs"]:
    df_exports[col] = df_exports[col].fillna("").astype(str)

df_exports["importer"] = df_exports["importer"].replace(["", "NA"], "UNKNOWN")
df_exports["country"] = df_exports["country"].replace(["", "NA"], "UNKNOWN")
df_exports["certification"] = df_exports["certification"].replace(
    "", "UNKNOWN / NOT CERTIFIED"
)

df_exports["vol"] = pd.to_numeric(df_exports["vol"], errors="coerce").fillna(0)
df_exports["fob"] = pd.to_numeric(df_exports["fob"], errors="coerce").fillna(0)

print(df_exports.columns.tolist())
print(df_exports["vol"].sum())
print(df_exports.head())

stitch_keys = sorted((set(df.columns) & set(df_exports.columns)) - {"vol"})
print(stitch_keys)

df = stitch_dataframes(
    df,
    df_exports,
    volume_column="vol",
    values_right=["fob"],
    fillna="",
)

df["fob"] = pd.to_numeric(df["fob"], errors="coerce").fillna(0)

print(df["vol"].sum())
print(df["fob"].sum())
print(df.columns.tolist())
print(
    df[
        [
            "commodity",
            "exporter_id",
            "port_export_id",
            "importer",
            "country",
            "certification",
            "hs",
            "fob",
            "vol",
        ]
    ].head()
)

print(df["matched"].value_counts(dropna=False))
print(df.groupby("matched")["vol"].sum())

""" 
#stitch is ok considering big diff is only exporter_id NA and port_export_id NA and E-0395 and port NA


df_left = pd.read_csv("df_before_process.csv", low_memory=False)
df_left = df_left.drop(columns=["Unnamed: 0"], errors="ignore")

for col in [
    "concession",
    "refinery_id",
    "mill_id",
    "port_export_id",
    "exporter_id",
    "branch",
    "key",
    "commodity",
]:
    df_left[col] = df_left[col].fillna("NA").astype(str)

df_left["is_unknown"] = df_left["is_unknown"].replace("NA", False).fillna(False)
df_left["excess"] = df_left["excess"].replace("NA", False).fillna(False)
df_left["year"] = str(int(supplychain.context.year))

df_left_no_commodity = df_left.drop(columns=["commodity"], errors="ignore")
df_exports_no_commodity = df_exports.drop(columns=["commodity"], errors="ignore")

df_stitched = stitch_dataframes(
    df_left_no_commodity,
    df_exports_no_commodity,
    volume_column="vol",
    values_right=["fob"],
    fillna="",
)

print(df_stitched["matched"].value_counts(dropna=False))
print(df_stitched.groupby("matched")["vol"].sum())%%!
left_keys = (
    df_left.groupby(["exporter_id", "port_export_id"])["vol"]
    .sum()
    .reset_index(name="result_vol")
)

right_keys = (
    df_exports.groupby(["exporter_id", "port_export_id"])["vol"]
    .sum()
    .reset_index(name="export_vol")
)

key_compare = left_keys.merge(
    right_keys,
    on=["exporter_id", "port_export_id"],
    how="outer",
).fillna(0)

key_compare["diff"] = key_compare["result_vol"] - key_compare["export_vol"]

print(
    key_compare.sort_values("diff", key=lambda s: s.abs(), ascending=False)
    .head(50)
)
"""

df = df[df["concession"].notna()].copy()

df["concession"] = df["concession"].fillna("NA").astype(str)

df["concession_trase_id"] = df["concession"].apply(
    lambda x: x.replace("FFB-", "ID-PALM-CONCESSION-")
)
df["concession_trase_id"] = df["concession_trase_id"].mask(
    df["concession_trase_id"] == "UNKNOWN", "ID-PALM-CONCESSION-X"
)

# Add kabupaten/province from prepared concessions
df_concessions = supplychain.get("concession").copy()
print(df_concessions.columns.tolist())

df_concessions = df_concessions.rename(columns={"id": "concession"})
df_concessions = df_concessions[["concession", "kabupaten"]].drop_duplicates()

df_concessions["concession_trase_id"] = (
    df_concessions["concession"]
    .astype(str)
    .str.replace(
        "FFB-",
        "ID-PALM-CONCESSION-",
        regex=False,
    )
)

df = df.merge(
    df_concessions[["concession_trase_id", "kabupaten"]],
    on="concession_trase_id",
    how="left",
)

df["kabupaten"] = df["kabupaten"].fillna("").astype(str).str.strip()

df["kabupaten_trase_id"] = "ID-" + df["kabupaten"]
df["kabupaten_trase_id"] = df["kabupaten_trase_id"].replace("ID-", "ID-XXXX")

df["province_trase_id"] = "ID-" + df["kabupaten"].str[:2]
df["province_trase_id"] = df["province_trase_id"].replace("ID-", "ID-XX")

print("missing kabupaten:", (df["kabupaten_trase_id"] == "ID-XXXX").sum())
print("missing province:", (df["province_trase_id"] == "ID-XX").sum())

# refinery
df["refinery_trase_id"] = df["refinery_id"].apply(
    lambda x: (
        x.replace("R-", "ID-PALM-REFINERY-") if x != "NA" else "ID-PALM-REFINERY-X"
    )
)

not_refined_trase_id = "ID-PALM-REFINERY-0000"
df["refinery_trase_id"] = df["refinery_trase_id"].mask(
    df["commodity"] == "CPO", not_refined_trase_id
)

df["mill_trase_id"] = df["mill_id"].apply(
    lambda x: (x.replace("M-", "ID-PALM-MILL-") if x != "UNKNOWN" else "ID-PALM-MILL-X")
)

df["port_export_id"] = df["port_export_id"].fillna("NA").astype(str)

df["port_trase_id"] = df["port_export_id"].apply(
    lambda x: (
        x.replace("P-", "ID-PORT-")
        if x != "UNKNOWN"
        else ("ID-PORT-XXXX" if x != "NA" else "NA")
    )
)

df["exporter_id"] = df["exporter_id"].fillna("NA").astype(str)

df["exporter_trase_id"] = df["exporter_id"].apply(
    lambda x: x.replace("E-", "ID-TRADER-") if x != "NA" else ""
)

df["product_vol"] = df["vol"]
df["product_vol"] = df["product_vol"].mask(
    df["commodity"] == "RPO", df["product_vol"] * 0.95
)

df["exporter_trase_id"] = df["exporter_trase_id"].replace("NA", "")
df["port_trase_id"] = df["port_trase_id"].replace("NA", "")
df["refinery_id"] = df["refinery_id"].replace("NA", "")

df["certification"] = (
    df["certification"]
    .fillna("")
    .replace(
        "",
        "UNKNOWN / NOT CERTIFIED",
    )
)
df["country"] = df["country"].fillna("").replace("", "UNKNOWN")
df["importer"] = df["importer"].fillna("").replace("", "UNKNOWN")
df["hs"] = df["hs"].fillna("").replace("", "UNKNOWN")

df = df.sort_index(axis=1)
df = consolidate(df, ["vol", "product_vol", "fob"])

print(df["vol"].sum())
print(df["fob"].sum())
print(df.columns.tolist())

import importlib
import pandas as pd
from trase.tools.sps import SupplyChain
import trase.models.indonesia.palm_oil.process_nodb as process_nodb

importlib.reload(process_nodb)

year = 2024

supplychain = SupplyChain(
    "indonesia/palm_oil",
    year=year,
)
supplychain.preparation()
supplychain.load()

df = pd.read_csv("df_before_process.csv", low_memory=False)
df = df.drop(columns=["Unnamed: 0"], errors="ignore")

input_vol = df["vol"].sum()

df_processed = process_nodb.process(df.copy(), supplychain)

print("after/process output")
print("processed rows:", len(df_processed))
print("processed vol:", df_processed["vol"].sum())
print("processed fob:", df_processed["fob"].sum())
print("volume difference input - processed:")
print(input_vol - df_processed["vol"].sum())
print("done")

Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of Flows[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of MillOutput[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of ConcessionMillCosts[0m
    [97mLoading data from disk took 8.9 seconds[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of Exporters[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of Mills[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of ExporterMills[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of Ports[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of MillConcessionLinks[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of Concessions[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of MillGroups[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of RefineryGroups[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of MillRefineryCosts[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of MillPortCosts[0m
Extracting data from source ...[0m [97m took 0.1 seconds[0m
[97mSkipping re-process of RefineryPortCosts[0m
start
43533865.32444107


/home/sagemaker-user/repos/TRASE/trase/models/indonesia/palm_oil/process_nodb.py:80: FutureWarning: Downcasting object dtype arrays on .fillna, .ffill, .bfill is deprecated and will change in a future version. Call result.infer_objects(copy=False) instead. To opt-in to the future behavior, set `pd.set_option('future.no_silent_downcasting', True)`
  df["is_unknown"] = df["is_unknown"].replace("NA", False).fillna(False)


stitch export details
volume_column: vol
df_1 columns: ['branch', 'commodity', 'concession', 'excess', 'exporter_id', 'is_unknown', 'key', 'mill_id', 'port_export_id', 'refinery_id', 'year', 'vol']
df_2 columns: ['commodity', 'exporter_id', 'port_export_id', 'certification', 'importer', 'importer_group', 'country', 'hs', 'year', 'fob', 'vol']
df_1 dtypes: {'branch': dtype('O'), 'commodity': dtype('O'), 'concession': dtype('O'), 'excess': dtype('bool'), 'exporter_id': dtype('O'), 'is_unknown': dtype('bool'), 'key': dtype('O'), 'mill_id': dtype('O'), 'port_export_id': dtype('O'), 'refinery_id': dtype('O'), 'year': dtype('O'), 'vol': dtype('float64')}
df_2 dtypes: {'commodity': dtype('O'), 'exporter_id': dtype('O'), 'port_export_id': dtype('O'), 'certification': dtype('O'), 'importer': dtype('O'), 'importer_group': dtype('O'), 'country': dtype('O'), 'hs': dtype('O'), 'year': dtype('O'), 'fob': dtype('float64'), 'vol': dtype('float64')}
export stitch summary
source fob: 20114421140.794052
kept fob: 16872494421.086754
dropped export-only fob: 3241926719.585739
commodity  exporter_id  port_export_id
RPO        E-0395       UNKNOWN           1.938891e+09
CPO        E-0395       UNKNOWN           1.303021e+09
RPO        E-0489       P-0010            4.724611e+03
Name: fob, dtype: float64
after consolidate
43533865.324441135
after consolidate again
43533865.32444107
after merge mill group
43533865.32444107
end
43533865.32444107
after/process output
processed rows: 9695818
processed vol: 43533865.32444107
processed fob: 16872494421.086744
volume difference input - processed:
0.0
done