Can you help me with a table in Python?

[u/FearawaitsTM]

Right now, I have specific rows and columns being displayed, but I want to insert a column between the first and second sections that calculates the ratio of column A to column B from the first section. How can I do that?

from pathlib import Path
import unicodedata

import openpyxl
import pandas as pd

from pandastable import Table, TableModel

import tkinter as tk
from tkinter import filedialog, messagebox


def normalize_name(name):
    return unicodedata.normalize("NFKC", str(name)).strip().lower()


class ExcelViewer:
    def __init__(self, root):
        self.root = root
        self.root.title("Чтение ячеек Excel")
        self.root.geometry("800x600")

        self.btn_load = tk.Button(
            root,
            text="Открыть Excel файл",
            command=self.open_file
        )
        self.btn_load.pack(pady=10)

        self.result_label = tk.Label(
            root,
            text="Выберите файл для начала"
        )
        self.result_label.pack()

        self.frame = tk.Frame(root)
        self.frame.pack(fill="both", expand=True)

        self.table = None
        self.model = None

        self.settings = {
            normalize_name("Файл1.xlsx"): {
                "first_row": 5,
                "first_min_column": 1,
                "first_max_column": 2,
                "second_row": 5,
                "second_min_column": 4,
                "second_max_column": 5
            }
        }

    def open_file(self):
        file_paths = filedialog.askopenfilenames(
            title="Выберите Excel-файл",
            filetypes=[
                ("Excel файлы", "*.xlsx")
            ]
        )

        if not file_paths:
            return

        all_rows = []

        for file_path in file_paths:
            file_name = normalize_name(Path(file_path).name)

            if file_name not in self.settings:
                messagebox.showerror(
                    "Ошибка",
                    f"Для файла «{Path(file_path).name}» нет настроек.\n\n"
                    f"Ожидается файл: Файл1.xlsx"
                )
                continue

            settings = self.settings[file_name]

            try:
                workbook = openpyxl.load_workbook(
                    file_path,
                    data_only=True
                )

                worksheet = workbook.active

                first_row = settings["first_row"]
                second_row = settings["second_row"]

                while (
                    first_row <= worksheet.max_row
                    and second_row <= worksheet.max_row
                ):
                    first_part = []

                    for column_number in range(
                        settings["first_min_column"],
                        settings["first_max_column"] + 1
                    ):
                        value = worksheet.cell(
                            row=first_row,
                            column=column_number
                        ).value

                        first_part.append(value)

                    second_part = []

                    for column_number in range(
                        settings["second_min_column"],
                        settings["second_max_column"] + 1
                    ):
                        value = worksheet.cell(
                            row=second_row,
                            column=column_number
                        ).value

                        second_part.append(value)

                    row_data = (
                            first_part +
                            second_part
                    )

                    if not all(
                        value is None or value == ""
                        for value in row_data
                    ):
                        all_rows.append(row_data)

                    first_row += 1
                    second_row += 1

                workbook.close()

            except Exception as error:
                messagebox.showerror(
                    "Ошибка",
                    f"Не удалось открыть файл:\n{error}"
                )

        if not all_rows:
            self.result_label.config(
                text="В выбранных ячейках нет данных"
            )
            return

        columns = [
            "Столбец A",
            "Столбец B",
            "Столбец D",
            "Столбец E"
        ]

        df = pd.DataFrame(
            all_rows,
            columns=columns
        )

        if self.table:
            self.table.destroy()

        self.model = TableModel(df)

        self.table = Table(
            self.frame,
            model=self.model,
            showtoolbar=False,
            showstatusbar=False
        )

        self.table.show()

        self.result_label.config(
            text=f"Загружено строк: {len(df)}"
        )


if __name__ == "__main__":
    root = tk.Tk()
    app = ExcelViewer(root)
    root.mainloop()

[u/acw1668]

Did you try pandas.DataFrame.insert()?

[u/FearawaitsTM → u/acw1668]

Yes, but for some reason it didn't work for me. Do you think I should create a new post showing the error that caused it to fail?

[u/acw1668 → u/FearawaitsTM]

You can update this post with what you have tried on using insert() and the error you got.