UWaterlooRFRChatBot / case_study_pipeline /sync_spreadsheet_cases.py
Anna Zhou
deploy with CS27-CS35 summaries
51b3964
Raw History Blame Contribute Delete
12.8 kB
"""
sync_spreadsheet_cases.py β€” Create Drive folders and local case study dirs for
entries in the Ajibade et al. 2022 spreadsheet not already covered by CS1-CS20.
Reads the "Full data_Clean" sheet from:
https://docs.google.com/spreadsheets/d/1PGvxXlBUP-DFTuaYKhO5xSesHnPGhMlM
Compares Village/City + Country against existing RFR - CS* folders in Drive.
Creates new CS## folders (Drive + local data/raw/ + case_meta.json) for
entries not yet covered.
Usage:
python3 -m case_study_pipeline.sync_spreadsheet_cases # dry-run
python3 -m case_study_pipeline.sync_spreadsheet_cases --execute # create folders
Flags:
--execute Actually create folders (default is dry-run only)
--start-cs NUM Override starting CS number (default: next after highest existing)
--skip-drive Skip Drive folder creation, only create local dirs
"""
from __future__ import annotations
import argparse
import csv
import io
import json
import re
import sys
from pathlib import Path
REPO_ROOT = Path(__file__).resolve().parent.parent
PIPELINE_DIR = Path(__file__).resolve().parent
DRIVE_FOLDERS_FILE = PIPELINE_DIR / "drive_folders.json"
DATA_RAW = REPO_ROOT / "data" / "raw"
SPREADSHEET_ID = "1PGvxXlBUP-DFTuaYKhO5xSesHnPGhMlM"
SHEET_GID = "1698438069" # Full data_Clean tab
PARENT_FOLDER_ID = "1SEYkC05ICecWn6EleREn6GZaw2CKEWZZ"
# Rows where the city cell contains citation text rather than a place name
_JUNK_PATTERNS = [
re.compile(r"^\s*pp\.\s*\d+"),
re.compile(r"Environmental resettlement", re.I),
re.compile(r"Village\s*/\s*City", re.I), # header repeat
]
# ---------------------------------------------------------------------------
# Drive helpers (reuse existing auth)
# ---------------------------------------------------------------------------
def _get_service():
from .onboard_case_study import get_drive_service
matches = list(REPO_ROOT.glob("client_secret*.json"))
if not matches:
print(" [Drive] No client_secret*.json found β€” use --skip-drive to skip Drive creation.")
return None
token_file = Path.home() / ".rfr-drive-token.json"
return get_drive_service(matches[0], token_file)
def _fetch_sheet_csv(service) -> str:
"""Download the Full data_Clean sheet as CSV using the Drive-authorized HTTP session."""
url = (
f"https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}"
f"/export?format=csv&gid={SHEET_GID}"
)
http = service._http
response, content = http.request(url)
if response.status != 200:
raise RuntimeError(f"Failed to fetch spreadsheet CSV: HTTP {response.status}")
return content.decode("utf-8-sig") # strip BOM if present
def _list_existing_cs_folders(service) -> dict[str, dict]:
"""Return {folder_title: {id, title}} for all RFR - CS* folders in the parent."""
result = {}
page_token = None
while True:
params = dict(
q=f"'{PARENT_FOLDER_ID}' in parents and mimeType = 'application/vnd.google-apps.folder'",
fields="nextPageToken, files(id, name)",
pageSize=100,
)
if page_token:
params["pageToken"] = page_token
resp = service.files().list(**params).execute()
for f in resp.get("files", []):
if re.match(r"RFR\s*-\s*CS\d+", f["name"], re.I):
result[f["name"]] = {"id": f["id"], "title": f["name"]}
page_token = resp.get("nextPageToken")
if not page_token:
break
return result
def _create_drive_folder(service, folder_name: str) -> str:
meta = {
"name": folder_name,
"mimeType": "application/vnd.google-apps.folder",
"parents": [PARENT_FOLDER_ID],
}
f = service.files().create(body=meta, fields="id,name").execute()
return f["id"]
# ---------------------------------------------------------------------------
# Spreadsheet parsing
# ---------------------------------------------------------------------------
def _is_junk(city: str) -> bool:
if not city or city.strip() in ("", "N/A", "Village/ City"):
return True
return any(p.search(city) for p in _JUNK_PATTERNS)
def _parse_sheet(csv_text: str) -> list[dict]:
"""Return list of {city, province, country} dicts from the sheet."""
reader = csv.reader(io.StringIO(csv_text))
headers = None
rows = []
for raw in reader:
if headers is None:
headers = [h.strip().lower() for h in raw]
continue
if len(raw) < 3:
continue
city_idx = next((i for i, h in enumerate(headers) if "village" in h or "city" in h), None)
province_idx = next((i for i, h in enumerate(headers) if "province" in h or "state" in h), None)
country_idx = next((i for i, h in enumerate(headers) if "country" in h), None)
if city_idx is None or country_idx is None:
continue
city = re.sub(r'\s+', ' ', raw[city_idx]).strip() if city_idx < len(raw) else ""
province = re.sub(r'\s+', ' ', raw[province_idx]).strip() if province_idx is not None and province_idx < len(raw) else ""
country = re.sub(r'\s+', ' ', raw[country_idx]).strip() if country_idx < len(raw) else ""
if not country:
continue
rows.append({"city": city, "province": province, "country": country})
return rows
def _deduplicate(rows: list[dict]) -> list[dict]:
seen = set()
out = []
for r in rows:
key = (r["city"].lower(), r["country"].lower())
if key in seen:
continue
seen.add(key)
out.append(r)
return out
# ---------------------------------------------------------------------------
# Matching against existing CS folders
# ---------------------------------------------------------------------------
def _normalise(s: str) -> str:
s = s.lower()
s = re.sub(r"[^a-z0-9 ]", " ", s)
s = re.sub(r"\s+", " ", s).strip()
# Normalise country name variants
s = s.replace("united states", "us").replace("united kingdom", "uk").replace("england", "uk")
return s
def _already_covered(row: dict, existing_titles: list[str]) -> bool:
"""Return True if this city+country is already represented in a CS folder title."""
city = _normalise(row["city"])
country = _normalise(row["country"])
for title in existing_titles:
t = _normalise(title)
city_words = [w for w in city.split() if len(w) > 2]
if city_words and country and all(w in t for w in city_words) and country[:4] in t:
return True
return False
# ---------------------------------------------------------------------------
# Folder/dir creation helpers
# ---------------------------------------------------------------------------
def _next_cs_number(existing_titles: list[str], override: int | None) -> int:
if override:
return override
nums = []
for t in existing_titles:
m = re.search(r"CS(\d+)", t, re.I)
if m:
nums.append(int(m.group(1)))
return max(nums, default=20) + 1
def _make_folder_name(cs_num: int, city: str, province: str, country: str) -> str:
"""RFR - CS## City, Province, Country"""
parts = [p for p in [city if city != "N/A" else "", province, country] if p]
location = ", ".join(parts)
return f"RFR - CS{cs_num} {location}"
def _load_drive_folders() -> dict:
if DRIVE_FOLDERS_FILE.exists():
return json.loads(DRIVE_FOLDERS_FILE.read_text())
return {"_comment": "Maps case study IDs to Google Drive folder IDs.",
"_parent_folder_id": PARENT_FOLDER_ID}
def _save_drive_folders(data: dict) -> None:
DRIVE_FOLDERS_FILE.write_text(json.dumps(data, indent=2) + "\n")
def _create_local_dirs(cs_id: str, city: str, province: str, country: str) -> None:
case_dir = DATA_RAW / cs_id
case_dir.mkdir(parents=True, exist_ok=True)
(case_dir / "pipeline_output").mkdir(exist_ok=True)
meta_path = case_dir / "case_meta.json"
if not meta_path.exists():
location = f"{city}, {province}" if province and province != "N/A" else city
meta_path.write_text(json.dumps({
"case_id": cs_id,
"name": city if city != "N/A" else location,
"location": location,
"country": country,
}, indent=2))
# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------
def main(argv: list[str] | None = None) -> None:
parser = argparse.ArgumentParser(
description="Create Drive + local dirs for spreadsheet case studies not yet in CS1-CS20."
)
parser.add_argument("--execute", action="store_true",
help="Actually create folders (default: dry-run only)")
parser.add_argument("--start-cs", type=int, default=None, metavar="NUM",
help="Override starting CS number")
parser.add_argument("--skip-drive", action="store_true",
help="Skip Drive folder creation, local dirs only")
parser.add_argument("--csv", metavar="PATH",
help="Path to a locally-downloaded CSV of the Full data_Clean sheet "
"(fallback if Drive auth is broken). "
"In Google Sheets: File β†’ Download β†’ CSV.")
args = parser.parse_args(argv)
dry_run = not args.execute
if dry_run:
print("DRY RUN β€” pass --execute to actually create folders.\n")
# --- Auth & data --------------------------------------------------------
service = None
if not args.skip_drive:
service = _get_service()
if service is None:
print("Could not get Drive service. Use --skip-drive to create local dirs only.")
sys.exit(1)
print("Fetching spreadsheet data...")
if args.csv:
csv_text = Path(args.csv).read_text(encoding="utf-8-sig")
print(f" Using local CSV: {args.csv}")
elif service:
csv_text = _fetch_sheet_csv(service)
else:
print(" No CSV source available. Pass --csv <path> or fix Drive auth.")
sys.exit(1)
rows = _parse_sheet(csv_text)
rows = [r for r in rows if not _is_junk(r["city"])]
rows = _deduplicate(rows)
print(f" {len(rows)} unique location rows after filtering.\n")
print("Fetching existing CS folders from Drive...")
existing = _list_existing_cs_folders(service) if service else {}
existing_titles = list(existing.keys())
print(f" {len(existing_titles)} existing CS folders found.\n")
# --- Compare & plan -----------------------------------------------------
to_create = []
covered = []
for row in rows:
if _already_covered(row, existing_titles):
covered.append(row)
else:
to_create.append(row)
print(f"Already covered by existing folders: {len(covered)}")
print(f"New folders to create: {len(to_create)}\n")
if not to_create:
print("Nothing to do β€” all spreadsheet entries are already covered.")
return
next_cs = _next_cs_number(existing_titles, args.start_cs)
drive_data = _load_drive_folders()
print(f"{'CS ID':<8} {'Drive folder name':<65} {'Status'}")
print("-" * 90)
for i, row in enumerate(to_create):
cs_num = next_cs + i
cs_id = f"CS{cs_num}"
folder_name = _make_folder_name(cs_num, row["city"], row["province"], row["country"])
if dry_run:
print(f"{cs_id:<8} {folder_name:<65} [dry-run]")
continue
# Create Drive folder
folder_id = ""
if service and not args.skip_drive:
try:
folder_id = _create_drive_folder(service, folder_name)
drive_data[cs_id] = folder_id
status = f"Drive βœ“ ({folder_id[:20]}...)"
except Exception as e:
status = f"Drive FAILED: {e}"
else:
status = "Drive skipped"
# Create local dirs
_create_local_dirs(cs_id, row["city"], row["province"], row["country"])
status += " | Local βœ“"
print(f"{cs_id:<8} {folder_name:<65} {status}")
if not dry_run:
_save_drive_folders(drive_data)
print(f"\ndrive_folders.json updated ({len(drive_data) - 2} entries).")
print(f"Local dirs created under data/raw/.")
print(f"\nNext steps:")
print(f" Download papers: bash download_all_papers.sh")
print(f" Run pipeline: python3 -m case_study_pipeline.run_case_study --case-folder data/raw/CS<NN>")
else:
print(f"\nRun with --execute to create {len(to_create)} folders.")
if __name__ == "__main__":
main()