import pandas as pd import geopandas as gpd from arcgis.features import FeatureLayer import os import sys import logging import json import argparse from datetime import datetime # Default output file name # Get project root (two levels up from this file: oat_nature_data_utils/explore_oat_arcgis_fields.py -> oat-nature-park/) PROJECT_ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) OAT_NATURE_DATA_DIR = os.path.join(PROJECT_ROOT, "oat_nature_data") OUTPUT_FILE = os.path.join(OAT_NATURE_DATA_DIR, "OAT_ArcGIS_OAT_Only.xlsx") COMBINED_JSON_FILE = os.path.join(OAT_NATURE_DATA_DIR, "OAT_ArcGIS_OAT_Only_data.json") # Configure logging log_filename = os.path.splitext(OUTPUT_FILE)[0] + '.log' logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s', handlers=[ logging.FileHandler(log_filename), logging.StreamHandler(sys.stdout) ] ) logger = logging.getLogger(__name__) # Immediate logging to verify script execution logger.info("="*50) logger.info("Script started") logger.info(f"Current working directory: {os.getcwd()}") logger.info(f"Log file location: {os.path.abspath(log_filename)}") logger.info("="*50) # List of ArcGIS feature layers to inspect layers = [ {"name": "Site_Boundaries", "url": "https://services-eu1.arcgis.com/N7YlixcRxR5tv3qH/arcgis/rest/services/Site_Boundary_Map_View/FeatureServer/0"}, {"name": "Mapper_Microhabitats", "url": "https://services-eu1.arcgis.com/N7YlixcRxR5tv3qH/arcgis/rest/services/Map_Viewer_View/FeatureServer/0"}, {"name": "Mapper_Lines", "url": "https://services-eu1.arcgis.com/N7YlixcRxR5tv3qH/arcgis/rest/services/Map_Viewer_View/FeatureServer/1"}, {"name": "Mapper_Areas", "url": "https://services-eu1.arcgis.com/N7YlixcRxR5tv3qH/arcgis/rest/services/Map_Viewer_View/FeatureServer/2"}, ] OAT_SCHOOLS_CSV = os.path.join(OAT_NATURE_DATA_DIR, "OAT_schools.csv") # Helper function to clean dataframe for JSON serialization def clean_dataframe_for_json(df): """Clean pandas DataFrame for JSON serialization by handling NaN and infinity values.""" if df.empty: return [] # Replace NaN, infinity values with None/null df_clean = df.replace([float('inf'), float('-inf')], None) # Convert to object type to allow None values, then replace NaN for col in df_clean.columns: if df_clean[col].isna().any(): df_clean[col] = df_clean[col].astype('object').where(df_clean[col].notna(), None) return df_clean.to_dict('records') def ensure_oat_csv(): logger.info("Checking for OAT schools CSV file...") if not os.path.exists(OAT_SCHOOLS_CSV): logger.info(f"{OAT_SCHOOLS_CSV} not found. Creating it with OAT school data...") # OAT school data from the notebook oat_data = { 'URN': [135234, 135769, 135960, 135979, 135980, 136145, 136185, 136186, 136187, 136680, 137109, 137152, 137196, 137673, 137674, 137838, 138506, 138846, 139403, 139509, 139535, 139918, 140016, 140032, 140199, 140364, 140374, 140806, 140807, 140845, 140864, 141169, 141269, 142186, 142643, 143824, 145008, 145134, 145501, 146063, 147683, 147793, 147796, 148562], 'Establishment name': [ 'George Salter Academy', 'Ormiston Shelfield Community Academy', 'Ormiston Park Academy', 'Ormiston Sandwell Community Academy', 'Ormiston Bushfield Academy', 'Ormiston Sir Stanley Matthews Academy', 'Ormiston Bolingbroke Academy', 'Ormiston Victory Academy', 'Ormiston Venture Academy', 'Ormiston Horizon Academy', 'Ormiston Ilkeston Enterprise Academy', 'Ormiston Rivers Academy', 'Ormiston Maritime Academy', 'Ormiston Forge Academy', 'Ormiston Endeavour Academy', 'Thomas Wolsey Ormiston Academy', 'Ormiston Sudbury Academy', 'Ormiston South Parade Academy', 'Ormiston Denes Academy', 'Ormiston Bridge Academy', 'Ormiston Cliff Park Primary Academy', 'Wodensborough Ormiston Academy', 'Ormiston Herman Academy', 'Stoke High School - Ormiston Academy', 'Ormiston Six Villages Academy', 'Cliff Park Ormiston Academy', 'Ormiston Meadows Academy', 'Ormiston Beachcroft Academy', 'Ormiston Latimer Academy', 'Cowes Enterprise College, An Ormiston Academy', 'Ormiston Chadwick Academy', 'Tenbury High Ormiston Academy', 'City of Norwich School, An Ormiston Academy', 'Ormiston Meridian Academy', 'Packmoor Ormiston Academy', 'Edward Worlledge Ormiston Academy', 'Ormiston SWB Academy', 'Ormiston NEW Academy', 'Flegg High Ormiston Academy', 'Broadland High Ormiston Academy', 'Sandymoor Ormiston Academy', 'Ormiston Queensmill Academy', 'Brownhills Ormiston Academy', 'Ormiston Kensington Queensmill Academy' ] } df = pd.DataFrame(oat_data) df.to_csv(OAT_SCHOOLS_CSV, index=False) logger.info(f"Created {OAT_SCHOOLS_CSV} with {len(oat_data['URN'])} OAT schools.") else: logger.info(f"Found existing {OAT_SCHOOLS_CSV}") # Load OAT URNs from CSV def load_oat_urns(): try: logger.info(f"Attempting to load OAT URNs from {OAT_SCHOOLS_CSV}") oat_df = pd.read_csv(OAT_SCHOOLS_CSV) oat_urns = set(oat_df["URN"].astype(str).str.strip()) logger.info(f"Successfully loaded {len(oat_urns)} OAT URNs from {OAT_SCHOOLS_CSV}") return oat_df # Return the full DataFrame instead of just URNs except Exception as e: logger.error(f"Error loading OAT URNs from {OAT_SCHOOLS_CSV}: {e}") raise # Re-raise the exception to be caught by the caller def get_layer_dataframe(layer_url, layer_name, oat_df): try: logger.info(f"Downloading data from {layer_name}...") feature_layer = FeatureLayer(layer_url) # First get the count of records try: count_result = feature_layer.query(where="1=1", return_count_only=True) total_records = count_result # count_result is already the integer count logger.info(f"Total records in {layer_name}: {total_records}") except Exception as e: logger.error(f"Error getting record count from {layer_name}: {e}") return pd.DataFrame() # Query with a limit to avoid timeout batch_size = 1000 all_records = [] for offset in range(0, total_records, batch_size): try: current_batch = min(batch_size, total_records - offset) logger.info(f"Fetching records {offset+1} to {offset+current_batch} of {total_records}...") query_result = feature_layer.query( where="1=1", result_record_count=batch_size, result_offset=offset ) if query_result.features: all_records.extend(query_result.features) logger.info(f"Successfully fetched batch {offset+1} to {offset+current_batch}") except Exception as e: logger.error(f"Error fetching batch {offset+1} to {offset+current_batch}: {e}") continue if not all_records: logger.warning(f"No records found in {layer_name}") return pd.DataFrame() # Convert to DataFrame, properly handling the attributes try: logger.info(f"Processing {len(all_records)} records from {layer_name}...") records = [] for i, feature in enumerate(all_records, 1): if i % 1000 == 0: logger.info(f"Processing record {i} of {len(all_records)}...") record = feature.as_dict # Extract attributes and geometry attrs = record.get('attributes', {}) geom = record.get('geometry', {}) # Combine attributes and geometry into a single record combined = {**attrs, 'geometry': geom} records.append(combined) df = pd.DataFrame(records) # Print column names for debugging logger.info(f"\nColumns in {layer_name}:") for col in df.columns: logger.info(f"- {col}") # Clean up column names for Excel (capitalize, replace underscores with spaces) df.columns = [col.replace('_', ' ').title() for col in df.columns] # More flexible URN column detection urn_cols = [col for col in df.columns if any(urn_term in col.upper() for urn_term in ['URN', 'SCHOOL', 'ID'])] if urn_cols: logger.info(f"\nPotential URN columns found: {urn_cols}") # Try each potential URN column for urn_col in urn_cols: try: logger.info(f"Trying to filter using column: {urn_col}") # Convert URNs to strings for comparison df[urn_col] = df[urn_col].astype(str).str.strip() oat_df['URN'] = oat_df['URN'].astype(str).str.strip() # Filter the data filtered_df = df[df[urn_col].isin(oat_df['URN'])] if not filtered_df.empty: logger.info(f"Successfully filtered using column: {urn_col}") # Merge with school names filtered_df = filtered_df.merge( oat_df[['URN', 'Establishment name']], left_on=urn_col, right_on='URN', how='left' ) # Log the schools found schools_found = filtered_df['Establishment name'].unique() logger.info(f"\nSchools with data in {layer_name}:") for school in sorted(schools_found): logger.info(f"- {school}") logger.info(f"{layer_name}: {len(filtered_df)} OAT records found.") return filtered_df except Exception as e: logger.error(f"Error filtering with column {urn_col}: {e}") continue logger.warning(f"None of the potential URN columns contained matching OAT URNs") else: logger.warning(f"No potential URN columns found in {layer_name}") return pd.DataFrame() # No matching URN data found except Exception as e: logger.error(f"Error processing data from {layer_name}: {e}") return pd.DataFrame() except Exception as e: logger.error(f"Error accessing {layer_name}: {e}") return pd.DataFrame() def export_all_layers_to_excel(layers, oat_df, output_file=OUTPUT_FILE): try: logger.info("Starting export process...") oat_data_found = False all_data = {} schools_with_data = set() # Collect all data first total_layers = len(layers) for i, layer in enumerate(layers, 1): try: logger.info(f"Processing layer {i} of {total_layers}: {layer['name']}") df = get_layer_dataframe(layer["url"], layer["name"], oat_df) if df is not None and not df.empty: all_data[layer["name"]] = df oat_data_found = True # Add schools from this layer to our set schools_with_data.update(df['Establishment name'].unique()) logger.info(f"Successfully processed layer {i} of {total_layers}") else: logger.warning(f"No OAT data for {layer['name']}, skipping.") except Exception as e: logger.error(f"Error processing layer {layer['name']}: {e}") continue if not oat_data_found: logger.warning("No OAT data found in any ArcGIS layer. No Excel file will be created.") return # Write to Excel try: logger.info(f"Writing data to {output_file}...") with pd.ExcelWriter(output_file, engine="openpyxl") as writer: total_sheets = len(all_data) for i, (layer_name, df) in enumerate(all_data.items(), 1): logger.info(f"Writing sheet {i} of {total_sheets}: {layer_name}") # Sheet name: max 31 chars, no special chars sheet_name = layer_name[:31].replace('/', '_').replace(' ', '_') df.to_excel(writer, sheet_name=sheet_name, index=False) logger.info(f"Exported {layer_name} (OAT only) to sheet '{sheet_name}' with {len(df)} rows.") except Exception as e: logger.error(f"Error writing to Excel file: {e}") return # Create summary of schools with and without data try: all_schools = set(oat_df['Establishment name']) schools_without_data = all_schools - schools_with_data logger.info("\n=== SUMMARY OF SCHOOLS ===") logger.info("\nSchools with data downloaded:") for school in sorted(schools_with_data): logger.info(f"- {school}") logger.info(f"\nTotal schools with data: {len(schools_with_data)}") logger.info("\nSchools without data:") for school in sorted(schools_without_data): logger.info(f"- {school}") logger.info(f"\nTotal schools without data: {len(schools_without_data)}") logger.info(f"\nAll OAT-related ArcGIS data exported to {output_file}") except Exception as e: logger.error(f"Error creating summary: {e}") return # Export to JSON after Excel export completes try: logger.info("\n" + "="*50) logger.info("Starting JSON export...") if export_all_layers_to_json(all_data, oat_df): logger.info("JSON export completed successfully") else: logger.warning("JSON export completed with errors, but Excel export was successful") except Exception as e: logger.error(f"Error during JSON export: {e}") logger.warning("Excel export was successful, but JSON export failed") except Exception as e: logger.error(f"Error in export_all_layers_to_excel: {e}") return def export_all_layers_to_json(all_data, oat_df): """Export all layer data to JSON files, including ALL schools from CSV.""" try: logger.info("Starting JSON export process...") # Map layer names to JSON keys layer_mapping = { 'Site_Boundaries': 'site_boundaries', 'Mapper_Microhabitats': 'microhabitats', 'Mapper_Lines': 'lines', 'Mapper_Areas': 'areas' } # Generate individual JSON files for each sheet json_data = {} for layer_name, df in all_data.items(): json_key = layer_mapping.get(layer_name, layer_name.lower().replace(' ', '_')) cleaned_data = clean_dataframe_for_json(df) json_data[json_key] = cleaned_data # Also save individual JSON file individual_json_file = os.path.join( OAT_NATURE_DATA_DIR, f"OAT_ArcGIS_OAT_Only_{json_key}.json" ) try: with open(individual_json_file, 'w', encoding='utf-8') as f: json.dump(cleaned_data, f, indent=2, ensure_ascii=False) logger.info(f"Exported {json_key} to {individual_json_file} with {len(cleaned_data)} records") except Exception as e: logger.error(f"Error writing individual JSON file for {json_key}: {e}") # Ensure all expected keys exist (even if empty) for json_key in ['site_boundaries', 'microhabitats', 'lines', 'areas']: if json_key not in json_data: json_data[json_key] = [] # Generate schools list with ALL schools from CSV (not just those with data) schools = [] try: for _, row in oat_df.iterrows(): schools.append({ "urn": str(row['URN']), "name": row['Establishment name'] }) logger.info(f"Included {len(schools)} schools in JSON output (all schools from CSV)") except Exception as e: logger.error(f"Error generating schools list: {e}") schools = [] # Create combined JSON structure combined_data = { "schools": schools, "site_boundaries": json_data.get('site_boundaries', []), "microhabitats": json_data.get('microhabitats', []), "lines": json_data.get('lines', []), "areas": json_data.get('areas', []) } # Write combined JSON file try: with open(COMBINED_JSON_FILE, 'w', encoding='utf-8') as f: json.dump(combined_data, f, indent=2, ensure_ascii=False) logger.info(f"Successfully exported combined JSON to {COMBINED_JSON_FILE}") logger.info(f" - Schools: {len(combined_data['schools'])}") logger.info(f" - Site Boundaries: {len(combined_data['site_boundaries'])}") logger.info(f" - Microhabitats: {len(combined_data['microhabitats'])}") logger.info(f" - Lines: {len(combined_data['lines'])}") logger.info(f" - Areas: {len(combined_data['areas'])}") except Exception as e: logger.error(f"Error writing combined JSON file: {e}") raise return True except Exception as e: logger.error(f"Error in export_all_layers_to_json: {e}") return False def generate_json_from_excel(excel_file, oat_df): """Generate JSON files from existing Excel file.""" try: logger.info(f"Reading Excel file: {excel_file}") excel_data = pd.read_excel(excel_file, sheet_name=None) # Map original layer names to their Excel sheet name patterns # Excel sheet names are truncated to 31 chars and have spaces/slashes replaced with underscores layer_to_sheet_patterns = { 'Site_Boundaries': ['Site_Boundaries', 'Site_Boundary'], 'Mapper_Microhabitats': ['Mapper_Microhabitats', 'Mapper_Microhabitat'], 'Mapper_Lines': ['Mapper_Lines', 'Mapper_Line'], 'Mapper_Areas': ['Mapper_Areas', 'Mapper_Area'] } all_data = {} for sheet_name, df in excel_data.items(): # Find matching layer name by checking if sheet name starts with or contains layer name patterns layer_name = None for orig_layer_name, patterns in layer_to_sheet_patterns.items(): for pattern in patterns: # Check if sheet name matches pattern (case-insensitive, handle truncation) if pattern.lower().replace('_', '').replace(' ', '') in sheet_name.lower().replace('_', '').replace(' ', ''): layer_name = orig_layer_name break if layer_name: break if layer_name: all_data[layer_name] = df logger.info(f"Loaded sheet '{sheet_name}' as {layer_name} with {len(df)} rows") else: logger.warning(f"Unknown sheet '{sheet_name}', skipping") # Export to JSON return export_all_layers_to_json(all_data, oat_df) except Exception as e: logger.error(f"Error generating JSON from Excel: {e}") return False if __name__ == "__main__": parser = argparse.ArgumentParser(description='Export OAT Nature data from ArcGIS to Excel and JSON') parser.add_argument('--json-only', action='store_true', help='Generate JSON files from existing Excel file (skip ArcGIS download)') args = parser.parse_args() ensure_oat_csv() oat_df = load_oat_urns() if args.json_only: # JSON-only mode: generate JSON from existing Excel logger.info("="*50) logger.info("JSON-ONLY MODE: Generating JSON from existing Excel file") logger.info("="*50) excel_file = OUTPUT_FILE if not os.path.exists(excel_file): logger.error(f"Excel file not found: {excel_file}") logger.error("Please run without --json-only flag to generate Excel file first") sys.exit(1) if generate_json_from_excel(excel_file, oat_df): logger.info("JSON generation completed successfully") else: logger.error("JSON generation failed") sys.exit(1) else: # Full export mode: download from ArcGIS and export to Excel + JSON logger.info("="*50) logger.info("FULL EXPORT MODE: Downloading from ArcGIS and exporting to Excel + JSON") logger.info("="*50) export_all_layers_to_excel(layers, oat_df) logger.info("ArcGIS data export process completed.")