"""Board vs journalist schema discrepancies — interactive Marimo notebook.""" import marimo __generated_with = "0.23.1" app = marimo.App() @app.cell def _(): import marimo as mo return (mo,) @app.cell def _(mo): from collections import Counter import html as _html import json import pandas as pd BOARD_DIR = "data/Collected_by_the_Government/" JOURNAL_DIR = "data/Collected_by_the_Journalist/" TABLES = ["people", "plans", "places"] GROUP_COLS = {"people": "role", "plans": "plan_type", "places": "zone"} def read_csv(path: str) -> pd.DataFrame: return pd.read_csv(path, dtype=str, keep_default_na=False) def group_value(table: str, row: pd.Series) -> str: raw = str(row[GROUP_COLS[table]]).strip() if not raw: return "(unspecified)" if table == "plans": return raw.title() return raw # Build plan_id -> topic short name (using the journalist superset). _plan_topic_df = read_csv(JOURNAL_DIR + "plan_topics.csv").merge( read_csv(JOURNAL_DIR + "topics.csv"), on="topic_id", how="left" ) PLAN_TOPIC = { str(pid).strip(): str(lt).strip() for pid, lt in zip(_plan_topic_df["plan_id"], _plan_topic_df["long_topic"]) } # Build plan_id -> earliest meeting number so plans sort by when they appeared _mp_df = read_csv(JOURNAL_DIR + "meeting_plans.csv") _mp_df["_num"] = pd.to_numeric( _mp_df["meeting_id"].str.extract(r"(\d+)", expand=False), errors="coerce" ).fillna(999) PLAN_MEETING_NUM: dict[str, int] = ( _mp_df.groupby(_mp_df["plan_id"].str.strip())["_num"].min().astype(int).to_dict() ) def group_subgroup(table: str, row: pd.Series) -> tuple[str, str | None]: if table == "plans": plan_id = str(row["plan_id"]).strip() topic = PLAN_TOPIC.get(plan_id, "").strip() or "(no topic)" ptype = str(row["plan_type"]).strip().title() or "(unspecified)" return topic, ptype return group_value(table, row), None def classify_by_pk( pk: str, bdf: pd.DataFrame, jdf: pd.DataFrame ) -> tuple[set[str], set[str], set[str]]: b = set(bdf[pk].astype(str).str.strip()) j = set(jdf[pk].astype(str).str.strip()) return b & j, b - j, j - b def membership_of(k: str, both: set, bonly: set, jonly: set) -> str: if k in both: return "both" if k in bonly: return "board_only" if k in jonly: return "journal_only" return "both" PLAN_TYPE_EMOJI = { "Discussion": "\U0001f5e3\ufe0f", "Feedback": "\U0001f4ac", "Report": "\U0001f4c4", "Take Action": "\u270a", "Travel": "\U0001f697", "Presentation": "\U0001f4ca", "Proposal": "\U0001f4dd", } def display_name(table: str, row: pd.Series) -> str: if table in ("people", "places"): return str(row["name"]).strip() if table == "plans": ptype = str(row["plan_type"]).strip().title() emoji = PLAN_TYPE_EMOJI.get(ptype, "\u25cf") return f"{emoji} {str(row['long_title']).strip()}" return "?" people_b = read_csv(BOARD_DIR + "people.csv") people_j = read_csv(JOURNAL_DIR + "people.csv") plans_b = read_csv(BOARD_DIR + "plans.csv") plans_j = read_csv(JOURNAL_DIR + "plans.csv") places_b = read_csv(BOARD_DIR + "places.csv") places_j = read_csv(JOURNAL_DIR + "places.csv") pb, p_only_b, p_only_j = classify_by_pk("people_id", people_b, people_j) plb, pl_only_b, pl_only_j = classify_by_pk("plan_id", plans_b, plans_j) plab, pla_only_b, pla_only_j = classify_by_pk("place_id", places_b, places_j) ROW_MEMBERSHIP_ORDER = {"both": 0, "board_only": 1, "journal_only": 2} EDGE_MEMBERSHIP_ORDER = {"both": 0, "partial": 1, "journal_only": 2} def build_table_entries( table: str, pk: str, both: set, bonly: set, jonly: set, dfb: pd.DataFrame, dfj: pd.DataFrame, ) -> list[dict]: universe = both | bonly | jonly entries: list[dict] = [] for k in sorted(universe): sk = str(k).strip() rb = dfb[dfb[pk].astype(str).str.strip() == sk] rj = dfj[dfj[pk].astype(str).str.strip() == sk] row = rb.iloc[0] if len(rb) else rj.iloc[0] g, sg = group_subgroup(table, row) entries.append( { "id": sk, "label": display_name(table, row), "membership": membership_of(sk, both, bonly, jonly), "group": g, "subgroup": sg, } ) entries.sort( key=lambda e: ( e["group"].lower(), PLAN_MEETING_NUM.get(e["id"], 999), ROW_MEMBERSHIP_ORDER[e["membership"]], e["label"].lower(), ) ) return entries tables_data = { "people": build_table_entries( "people", "people_id", pb, p_only_b, p_only_j, people_b, people_j ), "plans": build_table_entries( "plans", "plan_id", plb, pl_only_b, pl_only_j, plans_b, plans_j ), "places": build_table_entries( "places", "place_id", plab, pla_only_b, pla_only_j, places_b, places_j ), } valid_ids = {t: {e["id"] for e in entries} for t, entries in tables_data.items()} # --- per-junction edge extraction -------------------------------------- def plan_people_edges(folder: str) -> set[tuple[str, str]]: df = read_csv(folder + "plan_people_participations.csv") out: set[tuple[str, str]] = set() for _, r in df.iterrows(): plan_id = str(r["plan_id"]).strip() people_id = str(r["people_id"]).strip() if plan_id in valid_ids["plans"] and people_id in valid_ids["people"]: out.add((plan_id, people_id)) return out def travel_link_edges(folder: str) -> set[tuple[str, str]]: df = read_csv(folder + "travel_links.csv") out: set[tuple[str, str]] = set() for _, r in df.iterrows(): plan_id = str(r["plan_id"]).strip() place_id = str(r["place_id"]).strip() if plan_id in valid_ids["plans"] and place_id in valid_ids["places"]: out.add((plan_id, place_id)) return out def people_place_trips(folder: str) -> dict[tuple[str, str], set[str]]: """(people_id, place_id) -> set of trip_ids that witness the visit.""" tp = read_csv(folder + "trip_people.csv") tpl = read_csv(folder + "trip_places.csv") merged = tp.merge(tpl, on="trip_id", how="inner", suffixes=("_p", "_q")) out: dict[tuple[str, str], set[str]] = {} for _, r in merged.iterrows(): people_id = str(r["people_id"]).strip() place_id = str(r["place_id"]).strip() trip_id = str(r["trip_id"]).strip() if people_id in valid_ids["people"] and place_id in valid_ids["places"]: out.setdefault((people_id, place_id), set()).add(trip_id) return out def plan_attributable_set(folder: str) -> set[tuple[str, str]]: """(people_id, place_id) pairs reachable via some plan P that rosters the person AND whose travel_links include the place.""" ppp = read_csv(folder + "plan_people_participations.csv") tl = read_csv(folder + "travel_links.csv") joined = ppp[["plan_id", "people_id"]].merge( tl[["plan_id", "place_id"]], on="plan_id", how="inner" ) out: set[tuple[str, str]] = set() for _, r in joined.iterrows(): people_id = str(r["people_id"]).strip() place_id = str(r["place_id"]).strip() if people_id in valid_ids["people"] and place_id in valid_ids["places"]: out.add((people_id, place_id)) return out pp_board = plan_people_edges(BOARD_DIR) pp_journ = plan_people_edges(JOURNAL_DIR) tl_board = travel_link_edges(BOARD_DIR) tl_journ = travel_link_edges(JOURNAL_DIR) trips_board = people_place_trips(BOARD_DIR) trips_journ = people_place_trips(JOURNAL_DIR) # Journalist is the superset, so use its plan-attribution set as the canonical reference. plan_attr = plan_attributable_set(JOURNAL_DIR) # plan_id -> Title-Cased plan_type (used to decide dotted vs solid on plans<->people edges) PLAN_TYPE_MAP = { str(r["plan_id"]).strip(): str(r["plan_type"]).strip().title() for _, r in plans_j.iterrows() } edges_data: list[dict] = [] # plans <-> people: dotted only when the plan is a Travel plan for plan_id, people_id in sorted(pp_board | pp_journ): in_board = (plan_id, people_id) in pp_board in_journ = (plan_id, people_id) in pp_journ if in_board and in_journ: membership = "both" elif in_journ: membership = "journal_only" else: membership = "both" # board-only is empty; defensive edges_data.append( { "a_table": "plans", "a_id": plan_id, "b_table": "people", "b_id": people_id, "membership": membership, "plan_attributable": PLAN_TYPE_MAP.get(plan_id, "") == "Travel", "n_trips_both": None, "n_trips_journ_only": None, } ) # plans <-> places: binary, always plan-attributable for plan_id, place_id in sorted(tl_board | tl_journ): in_board = (plan_id, place_id) in tl_board in_journ = (plan_id, place_id) in tl_journ if in_board and in_journ: membership = "both" elif in_journ: membership = "journal_only" else: membership = "both" edges_data.append( { "a_table": "plans", "a_id": plan_id, "b_table": "places", "b_id": place_id, "membership": membership, "plan_attributable": True, "n_trips_both": None, "n_trips_journ_only": None, } ) # people <-> places: three-state membership based on supporting trip evidence all_pairs = set(trips_board) | set(trips_journ) for pair in sorted(all_pairs): b_trips = trips_board.get(pair, set()) j_trips = trips_journ.get(pair, set()) if not b_trips and not j_trips: continue if not b_trips: membership = "journal_only" elif b_trips == j_trips: membership = "both" else: membership = "partial" edges_data.append( { "a_table": "people", "a_id": pair[0], "b_table": "places", "b_id": pair[1], "membership": membership, "plan_attributable": pair in plan_attr, "n_trips_both": len(b_trips & j_trips), "n_trips_journ_only": len(j_trips - b_trips), } ) entities_view = ( pd.DataFrame( [ { "table": t, "group": e["group"], "subgroup": e.get("subgroup"), "id": e["id"], "label": e["label"], "membership": e["membership"], } for t, entries in tables_data.items() for e in entries ] ) .assign( _ord=lambda d: d["membership"].map(ROW_MEMBERSHIP_ORDER), _sub=lambda d: d["subgroup"].fillna(""), ) .sort_values(["table", "group", "_sub", "_ord", "label"], kind="mergesort") .drop(columns=["_ord", "_sub"]) .reset_index(drop=True) ) pair_counts: dict[tuple[str, str], Counter] = {} for e in edges_data: pk_pair = tuple(sorted([e["a_table"], e["b_table"]])) pair_counts.setdefault(pk_pair, Counter())[e["membership"]] += 1 edge_summary_df = pd.DataFrame( [ { "table_a": a, "table_b": b, "both": c.get("both", 0), "partial": c.get("partial", 0), "journal_only": c.get("journal_only", 0), "total": c.get("both", 0) + c.get("partial", 0) + c.get("journal_only", 0), } for (a, b), c in sorted(pair_counts.items()) ] ) payload = json.dumps( {"tables": tables_data, "edges": edges_data}, ensure_ascii=False ) ROW_H, HDR_H, COL_HDR_H, COL_PAD, GROUP_GAP = 14, 18, 32, 24, 30 column_heights = [] for t, entries in tables_data.items(): n_groups = len({e["group"] for e in entries}) row_h = ROW_H * 2 if t == "plans" else ROW_H # plans rows may wrap to ~2 lines column_heights.append( len(entries) * row_h + n_groups * HDR_H + max(0, n_groups - 1) * GROUP_GAP ) iframe_height = max(column_heights) + COL_HDR_H + COL_PAD + 160 HTML_TEMPLATE = r"""

1. How to read this chart

The board reports are missing a lot of data compared to yours — especially plans that people participated in, and places that they travelled to.

This chart helps you spot missing links between plans ↔ people ↔ places in board data, highlighting potential biases that the board might try to hide from their reporting.

2. Show related tables for each person

Below are the list of people in the board.

Click on Plans to display the plans associated to each person, and Places to display the places that they travelled to:

3. Filter by dataset and read colors

The color of each plan or place reflects whether they appear in:

Common data: in both board data and your collected data

Partially missing trips: board data has fewer trips than your data

Journalist data only: only in your collected data

The same color logic also applies to links between plans ↔ people ↔ places.

Click on a dataset to filter data in that dataset:

4. Explore links to a given plan/person/place

Click on a person, plan, or place to highlight linked entities.

Tip: Click on a plan topic, person role, or place zone to see the corresponding links across plans, people, and places under it.

""" widget_html = HTML_TEMPLATE.replace("__PAYLOAD__", payload) iframe_html = ( '' '

' '' '

' ) widget = mo.Html(iframe_html) return edge_summary_df, entities_view, widget @app.cell def _(widget): widget return if __name__ == "__main__": app.run()