https://pastein.ru/t/G2f
Загрузка данных
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Скрипт для автоматической сборки «Формы сбора данных для анкетирования»
из выгрузки расписания по кафедрам (43 файла Excel).
Формирует итоговый файл Excel с 3 листами (аналогично прошлогодней форме):
1. «Общая (кроме ФВ, ЦПД)» — все дисциплины по группам и преподавателям
2. «Список групп и ППС ФВ» — две параллельные таблицы: группы с физкультурой и список ППС каф. ФВ
3. «ЦПД » — таблица учебных групп и руководителей проектов ЦПД
Запуск:
py main.py --input-dir . --output "итог\z.xlsx"
"""
from __future__ import annotations
from collections import Counter
import argparse
import logging
import os
import re
import sys
from pathlib import Path
from typing import Dict, List, Optional, Set, Tuple
import openpyxl
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
import pandas as pd
# ==============================================================================
# 1. НАСТРОЙКИ ПО УМОЛЧАНИЮ
# ==============================================================================
CURRENT_ACADEMIC_YEAR_SHORT = 26
ONLY_OCHNAYA = True
EXCLUDE_GRADUATING_BACHELORS_AND_MASTERS = False
DEDUPLICATE_MULTI_DEPT_TEACHERS = True
FV_DISCIPLINE_KEYWORDS = (
"физической культуре",
"физическая культура",
"физкультура",
"прикладная физическая культура",
)
EXCLUDED_DISCIPLINE_EXACT = {
"проектная деятельность",
}
EXCLUDED_DISCIPLINE_KEYWORDS = (
"учебная практика",
"производственная практика",
"преддипломная практика",
"научно-исследовательская работа",
"государственная итоговая аттестация",
"подготовка к процедуре защиты",
)
# ==============================================================================
# 2. СПРАВОЧНИКИ ФАКУЛЬТЕТОВ, КАФЕДР И НАПРАВЛЕНИЙ МОСКОВСКОГО ПОЛИТЕХА
# ==============================================================================
# Определение факультета/института группы по цифрам после дефиса (например, 261-321 -> '3' -> 'ФИТ')
GROUP_SUFFIX_TO_FACULTY: Dict[str, str] = {
"10": "ТФ",
"0": "ФИДИЖ",
"1": "ТФ",
"2": "ФМ",
"3": "ФИТ",
"4": "ФУиГХ",
"5": "ФХТиБ",
"6": "ФЭиУ",
"7": "ПФ",
"8": "ИГРИК",
"9": "ПИШ ТЛ",
}
# Привязка первых 2 цифр после дефиса (например, 231-631 -> '63' -> 'РиС') к профильной кафедре группы
# Позволяет безошибочно выбирать кафедру, если преподаватель работает сразу на 2 кафедрах одного факультета
GROUP_PREFIX2_TO_KAFEDRA: Dict[str, str] = {
"01": "ИДиК",
"02": "ЖиМК",
"11": "НТС",
"12": "НТС",
"13": "ДПМиСМ",
"14": "Дизайн",
"15": "ЭдТиМЭ",
"16": "ДПМиСМ",
"17": "ДПМиСМ",
"18": "НТС",
"20": "АиУ",
"21": "СМиС",
"22": "ОМДиАТ",
"23": "СМиС",
"24": "МиТЛП",
"25": "АиУ",
"26": "АиУ",
"27": "ОМДиАТ",
"28": "Материаловедение",
"29": "АиУ",
"32": "ИКТ",
"33": "ИиИТ",
"35": "ИБ",
"36": "ПИ",
"37": "ИБ",
"41": "ПГПС",
"42": "ЭиПЭ",
"43": "ПТ",
"44": "ПГПС",
"45": "ПГПС",
"51": "ЭБТС",
"52": "АОиАТП",
"53": "ХБТ",
"54": "ТНТ",
"55": "АОиАТП",
"56": "ПАХТ",
"57": "АОиАТП",
"61": "Менеджмент",
"62": "ЭиО",
"63": "РиС",
"64": "УП",
"65": "УП",
"66": "УП",
"67": "Менеджмент",
"71": "ИМП",
"72": "ТиУКвПиУП",
"73": "ПС",
"74": "ТиУКвПиУП",
"75": "ТиУКвПиУП",
"81": "ХТОПП",
"82": "ХТОПП",
"91": "ПИШ ТЛ",
"92": "ПИШ ТЛ",
"93": "ПИШ ТЛ",
"94": "ПИШ ТЛ",
}
SPECIALIST_SUFFIX_PREFIXES: Tuple[str, ...] = (
"11", "37", "41", "45", "55", "81",
)
SPECIALIST_EXACT_GROUPS: Set[str] = {
"211-231", "211-331", "211-451", "211-521",
"211-811", "211-812", "221-331", "221-451",
}
# Правила распознавания кафедр по КОРНЯМ слов (работают для любого падежа: Именительного и Родительного)
DEPARTMENT_RULES: List[Tuple[Tuple[str, ...], str, str]] = [
# ПИШ ТЛ
(("передов", "пиш", "fdr", "инженерная школа", "инженерной школы"), "ПИШ ТЛ", "ПИШ ТЛ"),
# ФВ (Физическое воспитание)
(("физкульт", "физическ", "фв"), "ФВ", "ФБК"),
# ЦПД
(("проектн", "цпд"), "ЦПД", "ЦПД"),
# ИГРИК
(("иллюстрац", "эстамп", "ииэ"), "ИиЭ", "ИГРИК"),
(("рисун", "живопис", "риж"), "РиЖ", "ИГРИК"),
(("художествен", "печатн", "хтопп"), "ХТОПП", "ИГРИК"),
# ПФ
(("принтмедиа", "инновацион", "имп"), "ИМП", "ПФ"),
(("полиграфических систем", "полиграфические системы", "полиграф систем", "кафедра пс"), "ПС", "ПФ"),
(("качеств", "упаковоч", "тиуквпиуп", "тиук"), "ТиУКвПиУП", "ПФ"),
# ТФ
(("динамик", "прочност", "сопротивлен", "дпмисм"), "ДПМиСМ", "ТФ"),
(("наземн", "транспортных средств", "транспортные средства", "нтс"), "НТС", "ТФ"),
(("энергоустанов", "малой энергет", "эдтимэ"), "ЭдТиМЭ", "ТФ"),
(("дизайн",), "Дизайн", "ТФ"),
# ФБК
(("гуманитарн", "кафедра гд"), "ГД", "ФБК"),
(("иностран", "кафедра ия"), "ИЯ", "ФБК"),
(("математ",), "Математика", "ФБК"),
(("механизм", "технической механик", "техническая механик", "компьютерного моделир", "тмикм"), "ТМиКМ", "ФБК"),
(("физик",), "Физика", "ФБК"),
# ФИДИЖ
(("журналист", "массовых коммуникац", "массовые коммуникац", "жимк"), "ЖиМК", "ФИДИЖ"),
(("издательск", "книговед", "идик"), "ИДиК", "ФИДИЖ"),
(("русск", "литератур", "ряил", "ряиил"), "РЯиЛ", "ФИДИЖ"),
# ФИТ
(("информационной безопасн", "информационная безопасн", "кафедра иб"), "ИБ", "ФИТ"),
(("инфокогнитив", "икт"), "ИКТ", "ФИТ"),
(("информатики и", "информатика и", "ииит"), "ИиИТ", "ФИТ"),
(("прикладной информат", "прикладная информат", "кафедра пи"), "ПИ", "ФИТ"),
(("смарт", "smart"), "СМАРТ", "ФИТ"),
# ФХТиБ (АОиАТП идет перед АиУ, чтобы слово 'автоматизации' не перехватывалось правилом 'автоматик')
(("аппаратурн", "автоматизации технологическ", "автоматизация технологическ", "аоиатп"), "АОиАТП", "ФХТиБ"),
# ФМ
(("автоматик", "аиу"), "АиУ", "ФМ"),
(("материаловед",), "Материаловедение", "ФМ"),
(("литей", "митлп"), "МиТЛП", "ФМ"),
(("давлен", "аддитивн", "омдиат"), "ОМДиАТ", "ФМ"),
(("сварочн", "сварк", "отисп"), "ОТиСП", "ФМ"),
(("стандартизац", "метролог", "сертификац", "смис"), "СМиС", "ФМ"),
(("оборудование машиностроен", "оборудования машиностроен", "машиностроен", "тиом"), "ТиОМ", "ФМ"),
# ФУиГХ
(("гражданск", "подземн", "строительств", "пгпс"), "ПГПС", "ФУиГХ"),
(("теплоэнергет", "кафедра пт"), "ПТ", "ФУиГХ"),
(("электрооборуд", "промышленной электроник", "промышленная электроник", "эипэ"), "ЭиПЭ", "ФУиГХ"),
# ФХТиБ (остальные)
(("процессов и аппарат", "процессы и аппарат", "пахт"), "ПАХТ", "ФХТиБ"),
(("низких температур", "низкие температур", "капиц", "тнт"), "ТНТ", "ФХТиБ"),
(("биотехнолог", "биологическ", "химической и", "химическая и", "химии и", "химия и", "хбт"), "ХБТ", "ФХТиБ"),
(("экологическ", "техносферн", "эбтс"), "ЭБТС", "ФХТиБ"),
# ФЭиУ («Реклама и связи с общественностью...» сокращается как «РиС»)
(("менеджмент",), "Менеджмент", "ФЭиУ"),
(("реклам", "связей с обществен", "связи с обществен", "рисом", "рис"), "РиС", "ФЭиУ"),
(("персонал", "кафедра уп"), "УП", "ФЭиУ"),
(("экономик", "эио"), "ЭиО", "ФЭиУ"),
]
# Точное соответствие краткого названия кафедры -> Факультет/институт ППС
KAFEDRA_TO_FACULTY: Dict[str, str] = {
"ИиЭ": "ИГРИК", "РиЖ": "ИГРИК", "ХТОПП": "ИГРИК",
"ПИШ ТЛ": "ПИШ ТЛ",
"ИМП": "ПФ", "ПС": "ПФ", "ТиУКвПиУП": "ПФ",
"ДПМиСМ": "ТФ", "Дизайн": "ТФ", "НТС": "ТФ", "ЭдТиМЭ": "ТФ",
"ГД": "ФБК", "ИЯ": "ФБК", "Математика": "ФБК", "ТМиКМ": "ФБК", "Физика": "ФБК", "ФВ": "ФБК",
"ЖиМК": "ФИДИЖ", "ИДиК": "ФИДИЖ", "РЯиЛ": "ФИДИЖ",
"ИБ": "ФИТ", "ИКТ": "ФИТ", "ИиИТ": "ФИТ", "ПИ": "ФИТ", "СМАРТ": "ФИТ",
"АиУ": "ФМ", "Материаловедение": "ФМ", "МиТЛП": "ФМ", "ОМДиАТ": "ФМ",
"ОТиСП": "ФМ", "СМиС": "ФМ", "ТиОМ": "ФМ",
"ПГПС": "ФУиГХ", "ПТ": "ФУиГХ", "ЭиПЭ": "ФУиГХ",
"АОиАТП": "ФХТиБ", "ПАХТ": "ФХТиБ", "ТНТ": "ФХТиБ", "ХБТ": "ФХТиБ", "ЭБТС": "ФХТиБ",
"Менеджмент": "ФЭиУ", "РиС": "ФЭиУ", "РиСОМ": "ФЭиУ", "УП": "ФЭиУ", "ЭиО": "ФЭиУ",
"ЦПД": "ЦПД",
}
# ==============================================================================
# 3. ВСПОМОГАТЕЛЬНЫЕ ФУНКЦИИ ОЧИСТКИ И КЛАССИФИКАЦИИ
# ==============================================================================
def clean_text(val: object) -> str:
if val is None or (isinstance(val, float) and pd.isna(val)):
return ""
s = str(val).replace("\xa0", " ").replace("\t", " ").replace("\r", " ").replace("\n", " ")
return re.sub(r"\s+", " ", s).strip()
def strip_kafedra_from_teacher(teacher: str) -> str:
return re.sub(r"\s*\([^)]*\)\s*$", "", clean_text(teacher)).strip()
def make_short_abbreviation(name: str) -> str:
"""
Автоматически строит краткую аббревиатуру из длинного названия кафедры,
если вдруг попался нестандартный файл (чтобы в таблице никогда не было длинных названий).
"""
s = re.sub(r"\([^)]*\)", "", name).strip()
s = re.sub(r"^(кафедра|ноц|центр|департамент|институт|факультет|школа)\s+", "", s, flags=re.I).strip()
words = [w for w in re.split(r"[\s,\-]+", s) if w]
if len(words) <= 1 and len(s) <= 16:
return s.capitalize() if s.islower() else s
stop_words = {"в", "на", "по", "для", "при", "им", "им.", "имени", "с", "у"}
abbr_parts = []
for w in words:
w_low = w.lower()
if w_low == "и":
abbr_parts.append("и")
elif w_low in stop_words:
continue
elif w.isupper() and len(w) <= 6:
abbr_parts.append(w)
else:
abbr_parts.append(w[0].upper())
abbr = "".join(abbr_parts).strip("и")
return abbr if len(abbr) >= 2 else s[:12]
def infer_kafedra_from_filename(filepath: Path) -> Tuple[str, str]:
"""
Определяет краткое название кафедры и факультет ППС по имени файла выгрузки.
Гарантирует краткое сокращение для всех кафедр.
"""
stem = clean_text(filepath.stem)
stem_no_num = re.sub(r"^\d+[\.\-\s_]+", "", stem).strip()
stem_lower = stem_no_num.lower()
# 1. Проверяем точное совпадение с кодом кафедры
for code, fac in KAFEDRA_TO_FACULTY.items():
if stem_lower == code.lower():
norm_code = "РиС" if code == "РиСОМ" else code
return norm_code, fac
# 2. Проверяем правила по корням слов (работает для любого падежа)
for keywords, code, fac in DEPARTMENT_RULES:
for kw in keywords:
if kw in stem_lower:
return code, fac
# 3. Если в скобках в конце имени файла указана аббревиатура
m = re.search(r"\(([^)]+)\)\s*$", stem_no_num)
if m:
abbr = m.group(1).strip()
if abbr in KAFEDRA_TO_FACULTY:
norm_code = "РиС" if abbr == "РиСОМ" else abbr
return norm_code, KAFEDRA_TO_FACULTY[abbr]
# 4. Fallback: строим краткую аббревиатуру автоматически
short_code = make_short_abbreviation(stem_no_num)
fac = KAFEDRA_TO_FACULTY.get(short_code, "НЕ ОПРЕДЕЛЕН")
logging.warning(
f"Файл '{filepath.name}' не найден в базовом словаре -> сокращён до '{short_code}'."
)
return short_code, fac
def parse_group_info(
group_code: str,
academic_year_short: int = CURRENT_ACADEMIC_YEAR_SHORT,
historical_group_map: Optional[Dict[str, Tuple[str, str, str]]] = None,
) -> Optional[Tuple[str, str, str, int]]:
g = clean_text(group_code)
m = re.match(r"^(\d{2})(\d)-(\d{2,4}[а-яА-Яa-zA-Z0-9]*)$", g)
if not m:
return None
adm_year = int(m.group(1))
form_digit = m.group(2)
suffix = m.group(3)
course = academic_year_short - adm_year + 1
faculty = None
for prefix_len in (2, 1):
cand = suffix[:prefix_len]
if cand in GROUP_SUFFIX_TO_FACULTY:
faculty = GROUP_SUFFIX_TO_FACULTY[cand]
break
if not faculty and historical_group_map and g in historical_group_map:
faculty = historical_group_map[g][0]
if not faculty:
faculty = "ФЭиУ"
if form_digit in ("1", "4"):
form_edu = "Очная"
elif form_digit in ("2", "5"):
form_edu = "Очно-заочная"
elif form_digit in ("3", "6"):
form_edu = "Заочная"
else:
form_edu = f"Код {form_digit}"
if form_digit in ("4", "5", "6"):
level_edu = "Магистратура"
else:
if historical_group_map and g in historical_group_map:
_, _, hist_level = historical_group_map[g]
level_edu = hist_level
elif g in SPECIALIST_EXACT_GROUPS or suffix.startswith(SPECIALIST_SUFFIX_PREFIXES) or course >= 5:
level_edu = "Специалитет"
else:
level_edu = "Бакалавриат"
return faculty, form_edu, level_edu, course
def is_fv_discipline(discipline: str, kafedra: str) -> bool:
if kafedra == "ФВ":
return True
return any(kw in discipline.lower() for kw in FV_DISCIPLINE_KEYWORDS)
def is_excluded_discipline(discipline: str) -> bool:
d_low = discipline.lower().strip()
if d_low in EXCLUDED_DISCIPLINE_EXACT:
return True
return any(kw in d_low for kw in EXCLUDED_DISCIPLINE_KEYWORDS)
# ==============================================================================
# 4. ЧТЕНИЕ ФАЙЛОВ ВЫГРУЗКИ КАФЕДР И ПРОШЛОГОДНЕГО ШАБЛОНА
# ==============================================================================
def load_historical_reference(ref_path: Optional[Path]) -> Tuple[
Dict[str, Tuple[str, str, str]],
Dict[Tuple[str, str], Tuple[str, str]],
Dict[str, Set[str]],
]:
group_map: Dict[str, Tuple[str, str, str]] = {}
teacher_disc_map: Dict[Tuple[str, str], Tuple[str, str]] = {}
teacher_kaf_map: Dict[str, Set[str]] = {}
if not ref_path or not ref_path.exists():
return group_map, teacher_disc_map, teacher_kaf_map
try:
df_ref = pd.read_excel(ref_path, sheet_name=0)
for _, row in df_ref.iterrows():
grp = clean_text(row.get("Группа"))
fac_grp = clean_text(row.get("Факультет/институт группы"))
form_edu = clean_text(row.get("Форма обучения"))
lvl_edu = clean_text(row.get("Уровень образования"))
disc = clean_text(row.get("Наименование дисциплины"))
fio = strip_kafedra_from_teacher(clean_text(row.get("Преподаватель")))
kaf = clean_text(row.get("Кафедра ") or row.get("Кафедра"))
if kaf == "РиСОМ":
kaf = "РиС"
fac_pps = clean_text(row.get("Факультет/институт ППС"))
if grp and fac_grp and lvl_edu:
group_map[grp] = (fac_grp, form_edu, lvl_edu)
if fio and disc and kaf and fac_pps:
teacher_disc_map[(fio, disc)] = (kaf, fac_pps)
if fio and kaf:
teacher_kaf_map.setdefault(fio, set()).add(kaf)
except Exception as e:
logging.warning(f"Не удалось прочитать '{ref_path}': {e}")
return group_map, teacher_disc_map, teacher_kaf_map
def read_department_file(filepath: Path) -> pd.DataFrame:
df = pd.read_excel(filepath, sheet_name=0, header=None)
if df.empty:
return pd.DataFrame(columns=["Преподаватель", "Группы", "Дисциплина", "Тип занятий"])
header_row_idx = 0
for r_idx in range(min(10, len(df))):
row_values = [clean_text(v).lower() for v in df.iloc[r_idx].tolist()]
if "преподаватель" in row_values or "группы" in row_values or "дисциплина" in row_values:
header_row_idx = r_idx
break
headers = [clean_text(v) for v in df.iloc[header_row_idx].tolist()]
data = df.iloc[header_row_idx + 1 :].copy()
data.columns = headers
col_map: Dict[str, str] = {}
for col in data.columns:
c_low = col.lower()
if "преподават" in c_low and "Преподаватель" not in col_map:
col_map["Преподаватель"] = col
elif "групп" in c_low and "Группы" not in col_map:
col_map["Группы"] = col
elif "дисциплин" in c_low and "Дисциплина" not in col_map:
col_map["Дисциплина"] = col
elif "тип занят" in c_low and "Тип занятий" not in col_map:
col_map["Тип занятий"] = col
if "Преподаватель" not in col_map and data.shape[1] > 1:
col_map["Преподаватель"] = data.columns[1]
if "Группы" not in col_map and data.shape[1] > 3:
col_map["Группы"] = data.columns[3]
if "Дисциплина" not in col_map and data.shape[1] > 8:
col_map["Дисциплина"] = data.columns[8]
if "Тип занятий" not in col_map and data.shape[1] > 7:
col_map["Тип занятий"] = data.columns[7]
out = pd.DataFrame()
out["Преподаватель"] = data[col_map["Преподаватель"]].map(clean_text) if "Преподаватель" in col_map else ""
out["Группы"] = data[col_map["Группы"]].map(clean_text) if "Группы" in col_map else ""
out["Дисциплина"] = data[col_map["Дисциплина"]].map(clean_text) if "Дисциплина" in col_map else ""
out["Тип занятий"] = data[col_map["Тип занятий"]].map(clean_text) if "Тип занятий" in col_map else ""
return out
# ==============================================================================
# 5. ОСНОВНАЯ ЛОГИКА СБОРКИ ДАННЫХ
# ==============================================================================
def process_all_files(
input_dir: Path,
ref_path: Optional[Path] = None,
cpd_path: Optional[Path] = None,
academic_year_short: int = CURRENT_ACADEMIC_YEAR_SHORT,
only_ochnaya: bool = ONLY_OCHNAYA,
exclude_graduating: bool = EXCLUDE_GRADUATING_BACHELORS_AND_MASTERS,
deduplicate_multi_dept: bool = DEDUPLICATE_MULTI_DEPT_TEACHERS,
) -> Tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame]:
hist_group_map, hist_teacher_disc_map, hist_teacher_kaf_map = load_historical_reference(ref_path)
excel_files = sorted(
[
f for f in input_dir.iterdir()
if f.is_file()
and f.suffix.lower() in (".xlsx", ".xls")
and not f.name.startswith("~$")
and "форма сбора данных" not in f.name.lower()
]
)
if not excel_files:
raise FileNotFoundError(f"В папке '{input_dir.resolve()}' не найдено файлов расписания (.xlsx / .xls).")
logging.info(f"Найдено файлов кафедр для обработки: {len(excel_files)}")
raw_main_rows: List[Dict[str, str]] = []
fv_groups_set: Set[Tuple[str, str, str]] = set()
fv_teachers_set: Set[str] = set()
cpd_rows_from_schedule: List[Tuple[str, str, str]] = []
for fpath in excel_files:
kafedra, fac_pps = infer_kafedra_from_filename(fpath)
df_raw = read_department_file(fpath)
# Если вдруг для какого-то файла факультет ППС не определился по словарю,
# автоматически определяем его по преобладающему факультету групп в этом файле!
if fac_pps == "НЕ ОПРЕДЕЛЕН" and not df_raw.empty:
grp_fac_counter: Counter = Counter()
for g_str in df_raw["Группы"]:
for g_code in re.split(r"[|,;\s]+", str(g_str)):
g_code = g_code.strip()
if g_code:
p = parse_group_info(g_code, academic_year_short, hist_group_map)
if p and p[0] != "НЕ ОПРЕДЕЛЕН":
grp_fac_counter[p[0]] += 1
if grp_fac_counter:
fac_pps = grp_fac_counter.most_common(1)[0][0]
else:
fac_pps = "ФБК"
logging.info(f"Обработка: {fpath.name} -> Кафедра: {kafedra} | Факультет: {fac_pps}")
for _, row in df_raw.iterrows():
teacher_fio = strip_kafedra_from_teacher(row["Преподаватель"])
groups_str = row["Группы"]
discipline = row["Дисциплина"]
if not discipline and not groups_str and not teacher_fio:
continue
if kafedra == "ФВ" and teacher_fio and "вакансия" not in teacher_fio.lower():
fv_teachers_set.add(teacher_fio)
if not groups_str or not discipline:
continue
group_list = [g.strip() for g in re.split(r"[|,;\s]+", groups_str) if g.strip()]
for grp in group_list:
parsed = parse_group_info(grp, academic_year_short, hist_group_map)
if not parsed:
continue
fac_group, form_edu, level_edu, course = parsed
if fac_group == "НЕ ОПРЕДЕЛЕН":
fac_group = fac_pps
if only_ochnaya and form_edu != "Очная":
continue
if exclude_graduating:
if level_edu == "Бакалавриат" and course >= 4:
continue
if level_edu == "Магистратура" and course >= 2:
continue
if is_fv_discipline(discipline, kafedra):
if level_edu != "Магистратура":
fv_groups_set.add((fac_group, grp, "Элективные дисциплины по физической культуре и спорту"))
if teacher_fio and "вакансия" not in teacher_fio.lower():
fv_teachers_set.add(teacher_fio)
continue
if discipline.lower().strip() == "проектная деятельность":
if teacher_fio and "вакансия" not in teacher_fio.lower():
cpd_rows_from_schedule.append((grp, "Проектная деятельность", teacher_fio))
continue
if is_excluded_discipline(discipline) or not teacher_fio or "вакансия" in teacher_fio.lower():
continue
raw_main_rows.append(
{
"Факультет/институт группы": fac_group,
"Группа": grp,
"Форма обучения": form_edu,
"Уровень образования": level_edu,
"Наименование дисциплины": discipline,
"ФИО": teacher_fio,
"Преподаватель": f"{teacher_fio} ({kafedra})",
"Кафедра ": kafedra,
"Факультет/институт ППС": fac_pps,
"Примечание": "",
}
)
df_main = pd.DataFrame(raw_main_rows)
if not df_main.empty:
df_main = df_main.drop_duplicates(
subset=["Группа", "Наименование дисциплины", "ФИО", "Кафедра "]
).copy()
if deduplicate_multi_dept:
df_main = _resolve_multi_department_duplicates(
df_main, hist_teacher_disc_map, hist_teacher_kaf_map
)
df_main = df_main.sort_values(
by=["Группа", "Кафедра ", "Наименование дисциплины", "Преподаватель"],
kind="mergesort",
).reset_index(drop=True)
df_main = df_main[
[
"Факультет/институт группы",
"Группа",
"Форма обучения",
"Уровень образования",
"Наименование дисциплины",
"Преподаватель",
"Кафедра ",
"Факультет/институт ППС",
"Примечание",
]
]
else:
df_main = pd.DataFrame(
columns=[
"Факультет/институт группы", "Группа", "Форма обучения",
"Уровень образования", "Наименование дисциплины",
"Преподаватель", "Кафедра ", "Факультет/институт ППС", "Примечание",
]
)
if not df_main.empty and not fv_groups_set:
for _, r in df_main[["Факультет/институт группы", "Группа", "Уровень образования"]].drop_duplicates().iterrows():
if r["Уровень образования"] != "Магистратура":
parsed = parse_group_info(r["Группа"], academic_year_short, hist_group_map)
if parsed and (parsed[3] <= 3 or r["Факультет/институт группы"] == "ИГРИК"):
fv_groups_set.add(
(r["Факультет/институт группы"], r["Группа"], "Элективные дисциплины по физической культуре и спорту")
)
df_fv_groups = pd.DataFrame(
sorted(list(fv_groups_set), key=lambda x: (x[0], x[1])),
columns=["Факультет", "Группа", "Дисциплина"],
)
df_fv_groups["Проверено"] = ""
df_fv_teachers = pd.DataFrame(
sorted(list(fv_teachers_set)),
columns=["Преподаватель"],
)
df_fv_teachers["Проверено "] = ""
df_cpd = _load_or_build_cpd(cpd_path, ref_path, cpd_rows_from_schedule)
return df_main, df_fv_groups, df_fv_teachers, df_cpd
def _resolve_multi_department_duplicates(
df: pd.DataFrame,
hist_teacher_disc_map: Dict[Tuple[str, str], Tuple[str, str]],
hist_teacher_kaf_map: Dict[str, Set[str]],
) -> pd.DataFrame:
"""
Если одна и та же тройка (Группа, Наименование дисциплины, ФИО) встретилась сразу в нескольких
файлах кафедр (из-за совместительства преподавателя), выбирает правильную кафедру:
1. По историческому справочнику (ФИО, Дисциплина) -> Кафедра.
2. По профильной кафедре группы (первые 2 цифры после дефиса, например 231-631 -> '63' -> 'РиС').
3. По совпадению Факультета ППС с Факультетом группы.
4. Приоритет профильной кафедры над ПИШ ТЛ для групп других факультетов.
"""
dup_mask = df.duplicated(subset=["Группа", "Наименование дисциплины", "ФИО"], keep=False)
if not dup_mask.any():
return df
df_unique = df[~dup_mask].copy()
df_dups = df[dup_mask].copy()
resolved_rows = []
for (grp, disc, fio), group_df in df_dups.groupby(["Группа", "Наименование дисциплины", "ФИО"], sort=False):
chosen_row = None
# 1. Точное совпадение (ФИО, Дисциплина) с прошлым годом
if (fio, disc) in hist_teacher_disc_map:
hist_kaf, _ = hist_teacher_disc_map[(fio, disc)]
m_hist = group_df[group_df["Кафедра "] == hist_kaf]
if len(m_hist) == 1:
chosen_row = m_hist.iloc[0].to_dict()
# 2. Совпадение с профильной кафедрой группы по коду направления (например, *-63* -> РиС)
if chosen_row is None:
m_sfx = re.match(r"^\d{3}-(\d{2})", str(grp))
if m_sfx:
pref2 = m_sfx.group(1)
expected_kaf = GROUP_PREFIX2_TO_KAFEDRA.get(pref2)
if expected_kaf:
m_kaf = group_df[group_df["Кафедра "] == expected_kaf]
if len(m_kaf) == 1:
chosen_row = m_kaf.iloc[0].to_dict()
# 3. Совпадение факультета кафедры с факультетом учебной группы
if chosen_row is None:
fac_grp = group_df.iloc[0]["Факультет/институт группы"]
m_fac = group_df[group_df["Факультет/институт ППС"] == fac_grp]
if len(m_fac) == 1:
chosen_row = m_fac.iloc[0].to_dict()
elif len(m_fac) > 1:
chosen_row = m_fac.iloc[0].to_dict()
elif len(m_fac) == 0 and fac_grp != "ПИШ ТЛ" and "ПИШ ТЛ" in group_df["Кафедра "].values:
non_pish = group_df[group_df["Кафедра "] != "ПИШ ТЛ"]
if len(non_pish) >= 1:
chosen_row = non_pish.iloc[0].to_dict()
# 4. Fallback: берем первую запись без засорения колонки Примечание
if chosen_row is None:
chosen_row = group_df.iloc[0].to_dict()
resolved_rows.append(chosen_row)
return pd.concat([df_unique, pd.DataFrame(resolved_rows)], ignore_index=True)
def _load_or_build_cpd(
cpd_path: Optional[Path],
ref_path: Optional[Path],
cpd_rows_from_schedule: List[Tuple[str, str, str]],
) -> pd.DataFrame:
if cpd_path and cpd_path.exists():
df_cpd = pd.read_excel(cpd_path, sheet_name=0)
if len(df_cpd.columns) >= 3:
df_cpd = df_cpd.iloc[:, :3].copy()
df_cpd.columns = ["Учебная группа", "Название проекта", "Руководители проекта"]
return df_cpd.drop_duplicates().sort_values(by=["Учебная группа", "Название проекта"]).reset_index(drop=True)
if cpd_rows_from_schedule:
df_cpd = pd.DataFrame(
cpd_rows_from_schedule,
columns=["Учебная группа", "Название проекта", "Руководители проекта"],
)
return df_cpd.drop_duplicates().sort_values(by=["Учебная группа", "Название проекта"]).reset_index(drop=True)
return pd.DataFrame(columns=["Учебная группа", "Название проекта", "Руководители проекта"])
# ==============================================================================
# 6. ОФОРМЛЕНИЕ И СОХРАНЕНИЕ EXCEL-КНИГИ
# ==============================================================================
def export_to_excel(
output_path: Path,
df_main: pd.DataFrame,
df_fv_groups: pd.DataFrame,
df_fv_teachers: pd.DataFrame,
df_cpd: pd.DataFrame,
) -> None:
wb = openpyxl.Workbook()
font_header_12 = Font(name="Calibri", size=12, bold=True, color="1F2937")
font_body_12 = Font(name="Calibri", size=12, bold=False, color="111827")
font_header_11 = Font(name="Calibri", size=11, bold=True, color="1F2937")
font_body_11 = Font(name="Calibri", size=11, bold=False, color="111827")
font_cpd_header = Font(name="Tahoma", size=9, bold=True, color="1F2937")
fill_header = PatternFill(start_color="E8EEF5", end_color="E8EEF5", fill_type="solid")
fill_sub = PatternFill(start_color="F3F6FA", end_color="F3F6FA", fill_type="solid")
fill_zebra = PatternFill(start_color="F9FAFB", end_color="F9FAFB", fill_type="solid")
thin_side = Side(style="thin", color="D1D5DB")
med_bottom = Side(style="medium", color="9CA3AF")
border_data = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
border_header = Border(left=thin_side, right=thin_side, top=thin_side, bottom=med_bottom)
align_left = Alignment(horizontal="left", vertical="center", wrap_text=False)
align_center = Alignment(horizontal="center", vertical="center", wrap_text=False)
# ЛИСТ 1
ws1 = wb.active
ws1.title = "Общая (кроме ФВ, ЦПД)"
ws1.views.sheetView[0].showGridLines = True
headers_s1 = [
"Факультет/институт группы", "Группа", "Форма обучения",
"Уровень образования", "Наименование дисциплины",
"Преподаватель", "Кафедра ", "Факультет/институт ППС", "Примечание",
]
ws1.append(headers_s1)
ws1.row_dimensions[1].height = 24
for col_idx in range(1, len(headers_s1) + 1):
cell = ws1.cell(row=1, column=col_idx)
cell.font = font_header_12
cell.fill = fill_header
cell.border = border_header
cell.alignment = align_center if col_idx in (1, 2, 3, 4, 7, 8) else align_left
for row_idx, r in enumerate(df_main.itertuples(index=False), start=2):
ws1.append(list(r))
is_even = row_idx % 2 == 0
for col_idx in range(1, len(headers_s1) + 1):
c = ws1.cell(row=row_idx, column=col_idx)
c.font = font_body_12
c.border = border_data
c.alignment = align_center if col_idx in (1, 2, 3, 4, 7, 8) else align_left
if is_even:
c.fill = fill_zebra
ws1.auto_filter.ref = f"A1:I{max(2, ws1.max_row)}"
ws1.freeze_panes = "A2"
col_widths_s1 = {
"A": 25.0, "B": 14.0, "C": 16.0, "D": 21.0,
"E": 55.0, "F": 42.0, "G": 20.0, "H": 24.0, "I": 26.0,
}
for col_letter, width in col_widths_s1.items():
ws1.column_dimensions[col_letter].width = width
# ЛИСТ 2
ws2 = wb.create_sheet(title="Список групп и ППС ФВ")
ws2.views.sheetView[0].showGridLines = True
ws2.cell(row=2, column=1, value="Список групп ").font = font_header_11
ws2.cell(row=2, column=1).fill = fill_sub
ws2.cell(row=2, column=7, value="Список ППС каф. ФВ ").font = font_header_11
ws2.cell(row=2, column=7).fill = fill_sub
ws2.row_dimensions[2].height = 20
left_headers = ["Факультет", "Группа", "Дисциплина", "Проверено"]
for idx, text in enumerate(left_headers, start=1):
c = ws2.cell(row=3, column=idx, value=text)
c.font = font_header_12
c.fill = fill_header
c.border = border_header
c.alignment = align_center if idx in (1, 2, 4) else align_left
right_headers = ["Преподаватель", "Проверено "]
for idx, text in enumerate(right_headers, start=7):
c = ws2.cell(row=3, column=idx, value=text)
c.font = font_header_11
c.fill = fill_header
c.border = border_header
c.alignment = align_center if idx == 8 else align_left
ws2.row_dimensions[3].height = 22
for r_offset, r in enumerate(df_fv_groups.itertuples(index=False), start=4):
ws2.cell(row=r_offset, column=1, value=r[0])
ws2.cell(row=r_offset, column=2, value=r[1])
ws2.cell(row=r_offset, column=3, value=r[2])
ws2.cell(row=r_offset, column=4, value=r[3] if r[3] else None)
for c_idx in range(1, 5):
c = ws2.cell(row=r_offset, column=c_idx)
c.font = font_body_11 if c_idx == 3 else font_body_12
c.border = border_data
c.alignment = align_center if c_idx in (1, 2, 4) else align_left
for r_offset, r in enumerate(df_fv_teachers.itertuples(index=False), start=4):
ws2.cell(row=r_offset, column=7, value=r[0])
ws2.cell(row=r_offset, column=8, value=r[1] if r[1] else None)
for c_idx in (7, 8):
c = ws2.cell(row=r_offset, column=c_idx)
c.font = font_body_11
c.border = border_data
c.alignment = align_center if c_idx == 8 else align_left
ws2.auto_filter.ref = f"A3:D{max(3, len(df_fv_groups) + 3)}"
ws2.freeze_panes = "A4"
col_widths_s2 = {
"A": 14.0, "B": 14.0, "C": 70.0, "D": 14.0,
"E": 6.0, "F": 6.0, "G": 42.0, "H": 14.0,
}
for col_letter, width in col_widths_s2.items():
ws2.column_dimensions[col_letter].width = width
# ЛИСТ 3
ws3 = wb.create_sheet(title="ЦПД ")
ws3.views.sheetView[0].showGridLines = True
headers_s3 = ["Учебная группа", "Название проекта", "Руководители проекта"]
ws3.append(headers_s3)
ws3.row_dimensions[1].height = 22
for col_idx in range(1, 4):
c = ws3.cell(row=1, column=col_idx)
c.font = font_cpd_header
c.fill = fill_header
c.border = border_header
c.alignment = align_center if col_idx == 1 else align_left
for row_idx, r in enumerate(df_cpd.itertuples(index=False), start=2):
ws3.append(list(r))
for col_idx in range(1, 4):
c = ws3.cell(row=row_idx, column=col_idx)
c.font = font_body_11
c.border = border_data
c.alignment = align_center if col_idx == 1 else align_left
ws3.auto_filter.ref = f"A1:C{max(1, ws3.max_row)}"
ws3.freeze_panes = "A2"
col_widths_s3 = {"A": 16.0, "B": 52.0, "C": 52.0}
for col_letter, width in col_widths_s3.items():
ws3.column_dimensions[col_letter].width = width
output_path.parent.mkdir(parents=True, exist_ok=True)
wb.save(str(output_path))
logging.info(f"Файл успешно сохранён: {output_path.resolve()}")
def main() -> None:
logging.basicConfig(
level=logging.INFO,
format="%(asctime)s | %(levelname)s | %(message)s",
datefmt="%H:%M:%S",
)
parser = argparse.ArgumentParser(
description="Сборка формы анкетирования из 43 файлов выгрузки расписания по кафедрам."
)
parser.add_argument(
"--input-dir",
"-i",
type=Path,
default=Path("./выгрузка"),
help="Папка с 43 файлами выгрузки по кафедрам",
)
parser.add_argument(
"--output",
"-o",
type=Path,
default=Path("Форма сбора данных для анкетирования 2026.xlsx"),
help="Путь к итоговому файлу Excel",
)
parser.add_argument(
"--ref",
"-r",
type=Path,
default=Path("Форма сбора данных для анкетирования ЗАПОЛНЕНО 2.0.xlsx"),
help="Прошлогодний файл для сверки (необязательно)",
)
parser.add_argument(
"--cpd",
type=Path,
default=None,
help="Отдельный файл выгрузки проектов ЦПД (необязательно)",
)
parser.add_argument(
"--year",
type=int,
default=CURRENT_ACADEMIC_YEAR_SHORT,
help="Две последние цифры учебного года (26 для 2026/2027)",
)
parser.add_argument(
"--all-forms",
action="store_true",
help="Включить все формы обучения (по умолчанию только Очная)",
)
parser.add_argument(
"--exclude-graduating",
action="store_true",
help="Исключить выпускные курсы",
)
args = parser.parse_args()
input_dir: Path = args.input_dir
if not input_dir.exists():
input_dir = Path(".")
ref_path = args.ref if args.ref and args.ref.exists() else None
df_main, df_fv_groups, df_fv_teachers, df_cpd = process_all_files(
input_dir=input_dir,
ref_path=ref_path,
cpd_path=args.cpd,
academic_year_short=args.year,
only_ochnaya=not args.all_forms,
exclude_graduating=args.exclude_graduating,
deduplicate_multi_dept=DEDUPLICATE_MULTI_DEPT_TEACHERS,
)
export_to_excel(
output_path=args.output,
df_main=df_main,
df_fv_groups=df_fv_groups,
df_fv_teachers=df_fv_teachers,
df_cpd=df_cpd,
)
logging.info(
f"\nУспешно собрано:\n"
f" - Лист 1: {len(df_main)} строк ({df_main['Группа'].nunique() if not df_main.empty else 0} групп)\n"
f" - Лист 2: {len(df_fv_groups)} групп, {len(df_fv_teachers)} преподавателей ФВ\n"
f" - Лист 3: {len(df_cpd)} строк"
)
if __name__ == "__main__":
main()