csv_to_d3_hierarchy.py

import pandas as pd
import json
from statistics import mean

def create_hierarchy_from_csv(csv_file):
    # Read the CSV file
    df = pd.read_csv(csv_file)
    
    # Create the hierarchical structure
    hierarchy = {
        "name": "Company",
        "employees": [],
        "children": []
    }
    
    # Helper function to get or create a node at any level
    def get_or_create_node(parent, name):
        for child in parent["children"]:
            if child["name"] == name:
                return child
        new_node = {
            "name": name,
            "employees": [],
            "children": []
        }
        parent["children"].append(new_node)
        return new_node

    # Group employees by their organizational structure
    for _, row in df.iterrows():
        program_node = get_or_create_node(hierarchy, row["Program"])
        division_node = get_or_create_node(program_node, row["Division"])
        office_node = get_or_create_node(division_node, row["Office"])
        sub_office_node = get_or_create_node(office_node, row["Sub Office"])
        
        # Add employee data to the sub-office with all fields
        employee = {
            "name": row["Employee Name"],
            "id": row["Employee ID"],
            "type": row["Employment Type"],
            "position": {
                "number": row["Position Number"],
                "title": row["Position Title"]
            },
            "union_status": row["Union Status"],
            "competitive_level": row["Competitive Level"],
            "supervisor": {
                "name": row["Supervisor Name"],
                "id": row["Supervisor ID"]
            },
            "location": row["Location"],
            "satisfaction": row["Job Satisfaction"]
        }
        sub_office_node["employees"].append(employee)

    # Convert to JSON with proper formatting
    json_str = json.dumps(hierarchy, indent=2)
    
    # Create JavaScript code
    js_code = "const data = " + json_str + ";"
    
    # Save to a file
    with open('org_chart_data.js', 'w') as f:
        f.write(js_code)
    
    print("Data has been converted and saved to 'org_chart_data.js'")
    print("\nCopy the contents of org_chart_data.js and replace the existing data object in your HTML file.")

if __name__ == "__main__":
    create_hierarchy_from_csv('org_data.csv')