#!/usr/bin/env python3 """Reproduce the provincial indices reported in the PLOS ONE submission. Usage: python S1_Code_Reproduce_Analysis.py S1_Data_Heritage_Tourism_China.xlsx The script reads the ``Raw_Provincial_Counts`` sheet and writes a CSV with the normalized indicators, entropy weights, composite indices, and coupling coordination results. Only Python's standard library is required. """ from __future__ import annotations import csv import math import sys import zipfile import xml.etree.ElementTree as ET from pathlib import Path NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main", "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships"} REL_NS = {"p": "http://schemas.openxmlformats.org/package/2006/relationships"} def _column_index(cell_ref: str) -> int: letters = "".join(ch for ch in cell_ref if ch.isalpha()) value = 0 for ch in letters: value = value * 26 + ord(ch.upper()) - 64 return value - 1 def read_xlsx_sheet(path: Path, sheet_name: str) -> list[list[object]]: """Read a simple XLSX worksheet without third-party dependencies.""" with zipfile.ZipFile(path) as archive: shared: list[str] = [] if "xl/sharedStrings.xml" in archive.namelist(): root = ET.fromstring(archive.read("xl/sharedStrings.xml")) for item in root.findall("m:si", NS): shared.append("".join(node.text or "" for node in item.iterfind(".//m:t", NS))) workbook = ET.fromstring(archive.read("xl/workbook.xml")) rels = ET.fromstring(archive.read("xl/_rels/workbook.xml.rels")) rel_map = {rel.attrib["Id"]: rel.attrib["Target"] for rel in rels.findall("p:Relationship", REL_NS)} target = None for sheet in workbook.findall("m:sheets/m:sheet", NS): if sheet.attrib.get("name") == sheet_name: target = rel_map[sheet.attrib[f"{{{NS['r']}}}id"]] break if target is None: raise KeyError(f"Worksheet not found: {sheet_name}") target = target.lstrip("/") if not target.startswith("xl/"): target = "xl/" + target root = ET.fromstring(archive.read(target)) rows: list[list[object]] = [] for row in root.findall("m:sheetData/m:row", NS): values: list[object] = [] for cell in row.findall("m:c", NS): idx = _column_index(cell.attrib["r"]) while len(values) <= idx: values.append(None) cell_type = cell.attrib.get("t") value_node = cell.find("m:v", NS) inline_node = cell.find("m:is/m:t", NS) if inline_node is not None: value: object = inline_node.text or "" elif value_node is None: value = None elif cell_type == "s": value = shared[int(value_node.text)] else: raw = value_node.text or "" try: number = float(raw) value = int(number) if number.is_integer() else number except ValueError: value = raw values[idx] = value rows.append(values) return rows def minmax(columns: list[list[float]]) -> list[list[float]]: normalized: list[list[float]] = [[0.0] * len(columns) for _ in range(len(columns[0]))] for j, column in enumerate(columns): lo, hi = min(column), max(column) span = hi - lo for i, value in enumerate(column): normalized[i][j] = 0.0 if span == 0 else (value - lo) / span return normalized def entropy_weights(normalized: list[list[float]]) -> list[float]: n = len(normalized) k = 1.0 / math.log(n) divergences: list[float] = [] for j in range(len(normalized[0])): total = sum(row[j] for row in normalized) entropy = 0.0 if total > 0: for row in normalized: p = row[j] / total if p > 0: entropy -= k * p * math.log(p) divergences.append(1.0 - entropy) divisor = sum(divergences) return [value / divisor for value in divergences] def composite(normalized: list[list[float]], weights: list[float]) -> list[float]: return [sum(value * weight for value, weight in zip(row, weights)) for row in normalized] def calculate(records: list[dict[str, object]]) -> tuple[list[dict[str, object]], list[float], list[float]]: cultural_names = ["Intangible_Heritage", "Protected_Sites", "Historic_Cities", "Traditional_Villages"] tourism_names = ["A_Level_Attractions", "National_Scenic_Areas", "Scenic_Points"] cultural_columns = [[float(row[name]) for row in records] for name in cultural_names] tourism_columns = [[float(row[name]) for row in records] for name in tourism_names] cultural_norm = minmax(cultural_columns) tourism_norm = minmax(tourism_columns) cultural_weights = entropy_weights(cultural_norm) tourism_weights = entropy_weights(tourism_norm) uc = composite(cultural_norm, cultural_weights) ut = composite(tourism_norm, tourism_weights) results: list[dict[str, object]] = [] for i, record in enumerate(records): denom = uc[i] + ut[i] coupling = 0.0 if denom == 0 else 2.0 * math.sqrt(uc[i] * ut[i]) / denom development = 0.5 * (uc[i] + ut[i]) coordination = math.sqrt(coupling * development) output = dict(record) for j, name in enumerate(cultural_names): output[f"Norm_{name}"] = cultural_norm[i][j] for j, name in enumerate(tourism_names): output[f"Norm_{name}"] = tourism_norm[i][j] output.update({ "Cultural_Index_Uc": uc[i], "Tourism_Index_Ut": ut[i], "Coupling_C": coupling, "Development_T": development, "Coordination_D": coordination, "Structural_Difference_Uc_minus_Ut": uc[i] - ut[i], }) results.append(output) ranked = sorted(results, key=lambda row: float(row["Coordination_D"]), reverse=True) for rank, row in enumerate(ranked, 1): row["Rank"] = rank return ranked, cultural_weights, tourism_weights def main() -> None: workbook = Path(sys.argv[1]) if len(sys.argv) > 1 else Path("S1_Data_Heritage_Tourism_China.xlsx") rows = read_xlsx_sheet(workbook, "Raw_Provincial_Counts") headers = [str(value) for value in rows[0]] records = [dict(zip(headers, row)) for row in rows[1:] if row and row[0] is not None] results, cultural_weights, tourism_weights = calculate(records) output = workbook.with_name("Reproduced_Provincial_Results.csv") with output.open("w", newline="", encoding="utf-8-sig") as handle: writer = csv.DictWriter(handle, fieldnames=list(results[0].keys())) writer.writeheader() writer.writerows(results) print("Cultural weights:", ", ".join(f"{value:.10f}" for value in cultural_weights)) print("Tourism weights:", ", ".join(f"{value:.10f}" for value in tourism_weights)) print(f"Wrote {output}") if __name__ == "__main__": main()