import os
import sys
import xml.etree.ElementTree as ET
import pandas as pd
from collections import OrderedDict

def extract_data(element):
    data = OrderedDict()
    for child in element:
        if len(child) > 0:
            data[child.tag] = extract_data(child)
        else:
            data[child.tag] = child.text
    return data

def flatten_ordered_dict(d, parent_key='', sep='.'):
    items = []
    for k, v in d.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, OrderedDict):
            items.extend(flatten_ordered_dict(v, new_key, sep=sep).items())
        else:
            items.append((new_key, v))
    return OrderedDict(items)

def process_xml_file(file_path):
    tree = ET.parse(file_path)
    root = tree.getroot()
    data = extract_data(root)
    flat_data = flatten_ordered_dict(data)

    df = pd.DataFrame([flat_data])

    # df['fio_cad_ingineer'] = df.apply(
    #     lambda row: f"{row.get('land_record.cad_works.cad_work.fio_cad_ingineer.surname', '')} {row.get('land_record.cad_works.cad_work.fio_cad_ingineer.name', '')} {row.get('land_record.cad_works.cad_work.fio_cad_ingineer.patronymic', '')}",
    #     axis=1
    # )

    df['fio_individual'] = df.apply(
        lambda row: f"{row.get('restrict_records.restrict_record.right_holders.right_holder.individual.surname', '-')} {row.get('restrict_records.restrict_record.right_holders.right_holder.individual.name', '')} {row.get('restrict_records.restrict_record.right_holders.right_holder.individual.patronymic', '')} {row.get('restrict_records.restrict_record.right_holders.right_holder.individual.birth_date', '')} {row.get('restrict_records.restrict_record.right_holders.right_holder.individual.birth_place', '')}",
        axis=1
    )

    df['identity_doc'] = df.apply(
        lambda row: f"{row.get('restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_name', '')} {row.get('restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_series', '-')} {row.get('restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_number', '')} {row.get('restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_date', '')} {row.get('restrict_records.restrict_record.right_holders.right_holder.individual.identity_doc.document_issuer', '')}",
        axis=1
    )

    columns_to_keep = [
        'land_record.object.common_data.cad_number',
        'land_record.object.common_data.quarter_cad_number',
        'fio_individual',
        'identity_doc',
        # 'details_statement.group_top_requisites.registration_number',
        # 'land_record.params.category.type.value',
        # 'land_record.params.permitted_use.permitted_use_established.by_document',
        'land_record.address_location.address.readable_address',
        # 'land_record.cad_works.cad_work.work_type',
        # 'fio_cad_ingineer',
        # 'land_record.cad_works.cad_work.cadastral_engineer_registry_number',
        'land_record.special_notes',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.cad_link.reg_number_border.number',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.cad_link.reg_number_border.name_by_doc',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.cad_link.reg_number_border.type_zone.code',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.cad_link.reg_number_border.type_zone.value',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.content_restrict_encumbrances',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_code.code',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_code.value',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_name',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_number',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_date',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.underlying_docs.underlying_document.document_issuer',
        # 'land_record.restrictions_encumbrances.restriction_encumbrance.starting_date',
        # 'restrict_records.restrict_record.record_info.registration_date',
        # 'restrict_records.restrict_record.restrictions_encumbrances_data.restriction_encumbrance_number',
        # 'restrict_records.restrict_record.restrictions_encumbrances_data.restriction_encumbrance_type.code',
        # 'restrict_records.restrict_record.restrictions_encumbrances_data.restriction_encumbrance_type.value',
        # 'restrict_records.restrict_record.restrictions_encumbrances_data.period.period_info.start_date',
        # 'fio_individual',
        # 'restrict_records.restrict_record.right_holders.right_holder.individual.citizenship.person_citizenship_country.citizenship_country.value',
        # 'restrict_records.restrict_record.right_holders.right_holder.individual.snils',
        # 'identity_doc',
        # 'restrict_records.restrict_record.underlying_documents.underlying_document.document_code.value',
        # 'restrict_records.restrict_record.underlying_documents.underlying_document.document_name',
        # 'deal_records.deal_record.deal_data.deal_type.value',
        # 'recipient_statement',
        # 'status'
    ]

    df = df.filter(items=columns_to_keep)
    return df

if __name__ == "__main__":
    # Determine the directory where the executable or script is located
    directory_path = os.path.dirname(sys.argv[0])
    all_files_data = []

    for filename in os.listdir(directory_path):
        if filename.endswith('.xml'):
            file_path = os.path.join(directory_path, filename)
            df = process_xml_file(file_path)
            all_files_data.append(df)

    if all_files_data:
        final_df = pd.concat(all_files_data, ignore_index=True)
        final_df.to_excel('output.xlsx', index=False)
    else:
        print("No XML files found in the directory.") 

у меня есть такая программа мне нужно переименовать колонки которые есть в columns_to_keep на русский язык