#!/usr/bin/env python3 """ Family Tree Tool Reads family tree data from Excel, visualizes it, and allows adding new members. Usage: python family_tree_tool.py --input Kirk_Family_Tree.xlsx python family_tree_tool.py --input Kirk_Family_Tree.xlsx --add-person "John Doe" --birth-date "01/01/1990" python family_tree_tool.py --input Kirk_Family_Tree.xlsx --visualize """ import argparse import pandas as pd from pathlib import Path from datetime import datetime from openpyxl import load_workbook, Workbook from openpyxl.styles import Font, PatternFill, Alignment import sys try: import networkx as nx import matplotlib.pyplot as plt import matplotlib.patches as mpatches from matplotlib.patches import FancyBboxPatch, FancyArrowPatch HAS_VISUALIZATION = True except ImportError: HAS_VISUALIZATION = False print("Warning: matplotlib and networkx not installed. Visualization disabled.") print("Install with: pip install matplotlib networkx") try: import graphviz HAS_GRAPHVIZ = True except ImportError: HAS_GRAPHVIZ = False class FamilyTree: """Manages family tree data and relationships.""" def __init__(self, excel_file=None): """Initialize family tree from Excel file or create new.""" self.people = pd.DataFrame() self.parent_child = pd.DataFrame() self.marriages = pd.DataFrame() if excel_file and Path(excel_file).exists(): self.load_from_excel(excel_file) else: self._initialize_empty() def _initialize_empty(self): """Initialize empty dataframes with correct structure.""" self.people = pd.DataFrame(columns=[ 'Person ID', 'First Name', 'Middle Name', 'Last Name', 'Birth Date', 'Birth Place', 'Notes' ]) self.parent_child = pd.DataFrame(columns=[ 'Child ID', 'Parent ID', 'Relationship' ]) self.marriages = pd.DataFrame(columns=[ 'Partner 1 ID', 'Partner 2 ID', 'Marriage Date', 'Divorce Date' ]) def load_from_excel(self, excel_file): """Load family tree data from Excel file.""" try: self.people = pd.read_excel(excel_file, sheet_name='People') self.parent_child = pd.read_excel(excel_file, sheet_name='ParentChild') self.marriages = pd.read_excel(excel_file, sheet_name='Marriages') print(f"✓ Loaded {len(self.people)} people from {excel_file}") except Exception as e: print(f"Error loading Excel file: {e}") self._initialize_empty() def save_to_excel(self, excel_file): """Save family tree data to Excel file.""" try: with pd.ExcelWriter(excel_file, engine='openpyxl') as writer: self.people.to_excel(writer, sheet_name='People', index=False) self.parent_child.to_excel(writer, sheet_name='ParentChild', index=False) self.marriages.to_excel(writer, sheet_name='Marriages', index=False) # Format the Excel file wb = load_workbook(excel_file) # Format People sheet ws = wb['People'] for col in ws.columns: max_length = 0 col_letter = col[0].column_letter for cell in col: try: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 50) ws.column_dimensions[col_letter].width = adjusted_width # Header formatting header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid") header_font = Font(bold=True, color="FFFFFF") for sheet_name in ['People', 'ParentChild', 'Marriages']: ws = wb[sheet_name] for cell in ws[1]: cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal='center', vertical='center') wb.save(excel_file) print(f"✓ Saved family tree to {excel_file}") except Exception as e: print(f"Error saving Excel file: {e}") raise def get_next_person_id(self): """Generate next available Person ID.""" if self.people.empty or 'Person ID' not in self.people.columns: return 'P001' existing_ids = self.people['Person ID'].astype(str).tolist() max_num = 0 for pid in existing_ids: try: num = int(pid.replace('P', '')) if num > max_num: max_num = num except: pass return f"P{max_num + 1:03d}" def add_person(self, first_name, last_name="", middle_name="", birth_date="", birth_place="", notes=""): """Add a new person to the family tree.""" person_id = self.get_next_person_id() new_person = pd.DataFrame([{ 'Person ID': person_id, 'First Name': first_name, 'Middle Name': middle_name if middle_name else None, 'Last Name': last_name if last_name else None, 'Birth Date': birth_date if birth_date else None, 'Birth Place': birth_place if birth_place else None, 'Notes': notes if notes else None }]) self.people = pd.concat([self.people, new_person], ignore_index=True) print(f"✓ Added person: {person_id} - {first_name} {last_name}") return person_id def add_parent_child_relationship(self, child_id, parent_id, relationship="Father"): """Add a parent-child relationship.""" new_rel = pd.DataFrame([{ 'Child ID': child_id, 'Parent ID': parent_id, 'Relationship': relationship }]) self.parent_child = pd.concat([self.parent_child, new_rel], ignore_index=True) print(f"✓ Added relationship: {parent_id} is {relationship} of {child_id}") def add_marriage(self, partner1_id, partner2_id, marriage_date="", divorce_date=""): """Add a marriage relationship.""" new_marriage = pd.DataFrame([{ 'Partner 1 ID': partner1_id, 'Partner 2 ID': partner2_id, 'Marriage Date': marriage_date if marriage_date else None, 'Divorce Date': divorce_date if divorce_date else None }]) self.marriages = pd.concat([self.marriages, new_marriage], ignore_index=True) print(f"✓ Added marriage: {partner1_id} <-> {partner2_id}") def get_person_by_name(self, first_name, last_name=""): """Find person ID by name.""" mask = self.people['First Name'].str.contains(first_name, case=False, na=False) if last_name: mask = mask & self.people['Last Name'].str.contains(last_name, case=False, na=False) matches = self.people[mask] return matches def print_summary(self): """Print a summary of the family tree.""" print("\n" + "="*60) print("FAMILY TREE SUMMARY") print("="*60) print(f"Total People: {len(self.people)}") print(f"Parent-Child Relationships: {len(self.parent_child)}") print(f"Marriages: {len(self.marriages)}") print("\nPeople:") for _, person in self.people.iterrows(): name = f"{person['First Name']} {person.get('Last Name', '')}" print(f" {person['Person ID']}: {name.strip()}") print("="*60 + "\n") def _build_family_graph(self): """Build networkx graph from family data.""" G = nx.DiGraph() person_dict = {} # Add nodes (people) for _, person in self.people.iterrows(): pid = person['Person ID'] name = f"{person['First Name']}" if pd.notna(person.get('Last Name')): name += f" {person['Last Name']}" person_dict[pid] = { 'name': name, 'first_name': person['First Name'], 'last_name': person.get('Last Name', ''), 'birth_date': person.get('Birth Date', ''), 'birth_place': person.get('Birth Place', '') } G.add_node(pid, **person_dict[pid]) # Add parent-child edges (only Father/Mother relationships) for _, rel in self.parent_child.iterrows(): parent = rel['Parent ID'] child = rel['Child ID'] relationship = rel.get('Relationship', 'Parent') # Only add if it's a proper parent relationship if parent in person_dict and child in person_dict and relationship in ['Father', 'Mother', 'Parent']: G.add_edge(parent, child, relationship=relationship) return G, person_dict def visualize_hierarchical(self, output_file=None, root_person_id=None): """Create a proper hierarchical family tree visualization.""" if not HAS_VISUALIZATION: print("Visualization libraries not available. Install with: pip install matplotlib networkx") return try: G, person_dict = self._build_family_graph() if len(G.nodes()) == 0: print("No family data to visualize.") return # Find root (oldest generation) - person with no parents if root_person_id and root_person_id in G.nodes(): root = root_person_id else: # Find nodes with no incoming edges (no parents) roots = [n for n in G.nodes() if G.in_degree(n) == 0] if roots: root = roots[0] # Use first root found else: root = list(G.nodes())[0] # Fallback to first node # Calculate levels (generations) using BFS levels = {} queue = [(root, 0)] visited = set() while queue: node, level = queue.pop(0) if node in visited: continue visited.add(node) levels[node] = level # Add children to queue for child in G.successors(node): if child not in visited: queue.append((child, level + 1)) # Handle disconnected components for node in G.nodes(): if node not in levels: # Find shortest path to any root min_level = float('inf') for r in roots if roots else [list(G.nodes())[0]]: try: path = nx.shortest_path(G, r, node) min_level = min(min_level, len(path) - 1) except: pass levels[node] = min_level if min_level != float('inf') else 0 # Group nodes by level by_level = {} max_level = 0 for node, level in levels.items(): if level not in by_level: by_level[level] = [] by_level[level].append(node) max_level = max(max_level, level) # Calculate positions pos = {} level_height = 3.0 node_width = 2.0 for level in range(max_level + 1): if level not in by_level: continue nodes_at_level = by_level[level] num_nodes = len(nodes_at_level) total_width = num_nodes * node_width start_x = -total_width / 2 for i, node in enumerate(nodes_at_level): x = start_x + i * node_width + node_width / 2 y = -level * level_height pos[node] = (x, y) # Create figure fig, ax = plt.subplots(figsize=(max(16, max_level * 2), max(12, (max_level + 1) * 3))) # Draw edges (parent-child) for parent, child in G.edges(): if parent in pos and child in pos: ax.plot([pos[parent][0], pos[child][0]], [pos[parent][1], pos[child][1]], 'k-', linewidth=1.5, alpha=0.6, zorder=1) # Draw marriage connections for _, marriage in self.marriages.iterrows(): p1 = marriage['Partner 1 ID'] p2 = marriage['Partner 2 ID'] if p1 in pos and p2 in pos: # Draw horizontal line connecting spouses mid_y = (pos[p1][1] + pos[p2][1]) / 2 ax.plot([pos[p1][0], pos[p2][0]], [mid_y, mid_y], 'r--', linewidth=2, alpha=0.7, zorder=1) # Vertical lines to spouses ax.plot([pos[p1][0], pos[p1][0]], [pos[p1][1], mid_y], 'r--', linewidth=2, alpha=0.7, zorder=1) ax.plot([pos[p2][0], pos[p2][0]], [pos[p2][1], mid_y], 'r--', linewidth=2, alpha=0.7, zorder=1) # Draw nodes for node, (x, y) in pos.items(): person = person_dict[node] name = person['name'] # Create rounded rectangle for node bbox = FancyBboxPatch((x - 0.8, y - 0.3), 1.6, 0.6, boxstyle="round,pad=0.1", facecolor='lightblue', edgecolor='black', linewidth=1.5, zorder=2) ax.add_patch(bbox) # Add text ax.text(x, y, name, ha='center', va='center', fontsize=9, fontweight='bold', zorder=3, bbox=dict(boxstyle='round,pad=0.3', facecolor='white', alpha=0.7)) ax.set_xlim(-max_level * 2 - 2, max_level * 2 + 2) ax.set_ylim(-(max_level + 1) * level_height - 1, 1) ax.set_aspect('equal') ax.axis('off') ax.set_title("Kirk Family Tree - Hierarchical View", fontsize=16, fontweight='bold', pad=20) plt.tight_layout() if output_file: plt.savefig(output_file, dpi=300, bbox_inches='tight', facecolor='white') print(f"✓ Saved hierarchical tree to {output_file}") else: plt.savefig('family_tree_hierarchical.png', dpi=300, bbox_inches='tight', facecolor='white') print("✓ Saved hierarchical tree to family_tree_hierarchical.png") plt.close() except Exception as e: print(f"Error creating hierarchical visualization: {e}") import traceback traceback.print_exc() def visualize(self, output_file=None): """Create a visual representation of the family tree (network view).""" if not HAS_VISUALIZATION: print("Visualization libraries not available. Install with: pip install matplotlib networkx") return try: G, person_dict = self._build_family_graph() # Add marriage edges (undirected) for _, marriage in self.marriages.iterrows(): p1 = marriage['Partner 1 ID'] p2 = marriage['Partner 2 ID'] if p1 in G.nodes() and p2 in G.nodes(): G.add_edge(p1, p2, relationship='Marriage', style='dashed') # Create visualization plt.figure(figsize=(16, 12)) # Use hierarchical layout for family trees try: pos = nx.spring_layout(G, k=2, iterations=50) except: pos = nx.spring_layout(G) # Draw nodes nx.draw_networkx_nodes(G, pos, node_color='lightblue', node_size=2000, alpha=0.9) # Draw parent-child edges (solid) parent_edges = [(u, v) for u, v, d in G.edges(data=True) if d.get('relationship') != 'Marriage'] nx.draw_networkx_edges(G, pos, edgelist=parent_edges, edge_color='black', arrows=True, arrowsize=20, alpha=0.6) # Draw marriage edges (dashed, red) marriage_edges = [(u, v) for u, v, d in G.edges(data=True) if d.get('relationship') == 'Marriage'] nx.draw_networkx_edges(G, pos, edgelist=marriage_edges, edge_color='red', style='dashed', alpha=0.6, width=2) # Draw labels labels = {pid: person_dict[pid]['name'] for pid in G.nodes()} nx.draw_networkx_labels(G, pos, labels, font_size=8, font_weight='bold') plt.title("Kirk Family Tree - Network View", fontsize=16, fontweight='bold') plt.axis('off') plt.tight_layout() if output_file: plt.savefig(output_file, dpi=300, bbox_inches='tight') print(f"✓ Saved visualization to {output_file}") else: plt.savefig('family_tree_visualization.png', dpi=300, bbox_inches='tight') print("✓ Saved visualization to family_tree_visualization.png") plt.close() except Exception as e: print(f"Error creating visualization: {e}") import traceback traceback.print_exc() def export_gedcom(self, output_file=None): """Export family tree to GEDCOM format for genealogy software.""" if output_file is None: output_file = 'family_tree.ged' try: # Create mapping from Person ID to GEDCOM ID gedcom_ids = {} for idx, (_, person) in enumerate(self.people.iterrows(), 1): pid = person['Person ID'] gedcom_ids[pid] = f"@I{idx:04d}@" # Build family structure: map person -> families (as spouse and as child) person_families_spouse = {} # Person -> list of family IDs where they are spouse person_families_child = {} # Person -> family ID where they are child family_map = {} # Family ID -> (husband_id, wife_id, children_list, marriage_date) # Process marriages to create families family_num = 1 for _, marriage in self.marriages.iterrows(): p1 = marriage['Partner 1 ID'] p2 = marriage['Partner 2 ID'] if p1 in gedcom_ids and p2 in gedcom_ids: fam_id = f"@F{family_num:04d}@" # Determine husband and wife (assume first is husband, second is wife) # In GEDCOM, we need to identify gender, but for now use order husband_id = p1 wife_id = p2 # Find children of this couple children = [] for _, rel in self.parent_child.iterrows(): if rel['Parent ID'] in [p1, p2] and rel['Child ID'] in gedcom_ids: child_id = rel['Child ID'] if child_id not in children: children.append(child_id) marriage_date = marriage.get('Marriage Date') if pd.notna(marriage.get('Marriage Date')) else None family_map[fam_id] = (husband_id, wife_id, children, marriage_date) # Track families for each person if p1 not in person_families_spouse: person_families_spouse[p1] = [] person_families_spouse[p1].append(fam_id) if p2 not in person_families_spouse: person_families_spouse[p2] = [] person_families_spouse[p2].append(fam_id) # Track children's family for child_id in children: person_families_child[child_id] = fam_id family_num += 1 # Also handle parent-child relationships that aren't in marriages # (orphan children or children with only one parent known) for _, rel in self.parent_child.iterrows(): parent_id = rel['Parent ID'] child_id = rel['Child ID'] if child_id in gedcom_ids and child_id not in person_families_child: # Create a family for this parent-child relationship fam_id = f"@F{family_num:04d}@" # Determine if parent is father or mother if rel.get('Relationship') == 'Mother': family_map[fam_id] = (None, parent_id, [child_id], None) else: family_map[fam_id] = (parent_id, None, [child_id], None) person_families_child[child_id] = fam_id family_num += 1 # Write GEDCOM file with open(output_file, 'w', encoding='utf-8') as f: f.write("0 HEAD\n") f.write("1 SOUR Family Tree Tool\n") f.write("1 GEDC\n") f.write("2 VERS 5.5.1\n") f.write("2 FORM LINEAGE-LINKED\n") f.write("1 CHAR UTF-8\n") f.write("0 @SUBM@ SUBM\n") f.write("1 NAME Family Tree Tool\n") f.write("0 @SUBN@ SUBN\n") # Write individuals with bidirectional family references for idx, (_, person) in enumerate(self.people.iterrows(), 1): pid = person['Person ID'] gedcom_id = gedcom_ids[pid] f.write(f"0 {gedcom_id} INDI\n") f.write(f"1 NAME {person['First Name']}") if pd.notna(person.get('Last Name')): f.write(f" /{person['Last Name']}/") f.write("\n") if pd.notna(person.get('Birth Date')): f.write(f"1 BIRT\n") f.write(f"2 DATE {person['Birth Date']}\n") if pd.notna(person.get('Birth Place')): f.write(f"2 PLAC {person['Birth Place']}\n") # Add FAMC (family as child) - link to family where this person is a child if pid in person_families_child: f.write(f"1 FAMC {person_families_child[pid]}\n") # Add FAMS (family as spouse) - link to families where this person is a spouse if pid in person_families_spouse: for fam_id in person_families_spouse[pid]: f.write(f"1 FAMS {fam_id}\n") # Write families for fam_id, (husband_id, wife_id, children, marriage_date) in family_map.items(): f.write(f"0 {fam_id} FAM\n") if husband_id and husband_id in gedcom_ids: f.write(f"1 HUSB {gedcom_ids[husband_id]}\n") if wife_id and wife_id in gedcom_ids: f.write(f"1 WIFE {gedcom_ids[wife_id]}\n") if marriage_date: f.write(f"1 MARR\n") f.write(f"2 DATE {marriage_date}\n") # Add children for child_id in children: if child_id in gedcom_ids: f.write(f"1 CHIL {gedcom_ids[child_id]}\n") f.write("0 TRLR\n") print(f"✓ Exported GEDCOM file to {output_file}") print(f" GEDCOM file includes proper bidirectional references") print(f" You can now import this into genealogy software like:") print(f" - Gramps (free, open-source)") print(f" - Family Tree Maker") print(f" - Ancestry.com") print(f" - MyHeritage") print(f" - RootsMagic") except Exception as e: print(f"Error exporting GEDCOM: {e}") import traceback traceback.print_exc() def main(): """Main CLI interface.""" parser = argparse.ArgumentParser( description='Family Tree Tool - Manage and visualize family trees from Excel' ) parser.add_argument('--input', '-i', default='job_descriptions/Kirk_Family_Tree.xlsx', help='Input Excel file path') parser.add_argument('--output', '-o', help='Output Excel file path (default: overwrites input)') parser.add_argument('--visualize', '-v', action='store_true', help='Generate network visualization of family tree') parser.add_argument('--tree', '-t', action='store_true', help='Generate hierarchical family tree (proper tree layout)') parser.add_argument('--export-gedcom', action='store_true', help='Export family tree to GEDCOM format for genealogy software') parser.add_argument('--gedcom-output', help='Output file for GEDCOM export (default: family_tree.ged)') parser.add_argument('--summary', '-s', action='store_true', help='Print summary of family tree') # Add person arguments parser.add_argument('--add-person', nargs='+', help='Add a new person (provide: FirstName LastName)') parser.add_argument('--birth-date', help='Birth date for new person (format: DD/MM/YYYY)') parser.add_argument('--birth-place', help='Birth place for new person') parser.add_argument('--middle-name', help='Middle name for new person') parser.add_argument('--notes', help='Notes for new person') # Relationship arguments parser.add_argument('--add-child', nargs=2, metavar=('CHILD_ID', 'PARENT_ID'), help='Add parent-child relationship') parser.add_argument('--add-marriage', nargs=2, metavar=('PARTNER1_ID', 'PARTNER2_ID'), help='Add marriage relationship') parser.add_argument('--marriage-date', help='Marriage date (format: DD/MM/YYYY)') args = parser.parse_args() # Load family tree if Path(args.input).exists(): tree = FamilyTree(args.input) else: print(f"File {args.input} not found. Creating new family tree.") tree = FamilyTree() # Add person if requested if args.add_person: name_parts = args.add_person first_name = name_parts[0] last_name = " ".join(name_parts[1:]) if len(name_parts) > 1 else "" tree.add_person( first_name=first_name, last_name=last_name, middle_name=args.middle_name or "", birth_date=args.birth_date or "", birth_place=args.birth_place or "", notes=args.notes or "" ) # Add relationships if requested if args.add_child: tree.add_parent_child_relationship(args.add_child[0], args.add_child[1]) if args.add_marriage: tree.add_marriage( args.add_marriage[0], args.add_marriage[1], marriage_date=args.marriage_date or "" ) # Print summary if args.summary or not any([args.add_person, args.add_child, args.add_marriage, args.visualize, args.tree, args.export_gedcom]): tree.print_summary() # Visualize (network view) if args.visualize: tree.visualize() # Visualize (hierarchical tree) if args.tree: tree.visualize_hierarchical() # Export to GEDCOM if args.export_gedcom: gedcom_file = args.gedcom_output or 'family_tree.ged' tree.export_gedcom(gedcom_file) # Save if changes were made if args.add_person or args.add_child or args.add_marriage: output_file = args.output or args.input tree.save_to_excel(output_file) print(f"\n✓ Family tree updated and saved to {output_file}") if __name__ == '__main__': main()