Python, Проекты | Рамиль Мамедов, Валерий Хлебнов, Олег Брагинский
Строительная документация пугает обывателей заковыристостью формулировок и обилием томов документации для освоения. Ученики «Школы Траблшутеров» Рамиль Мамедов и Валерий Хлебнов подскажут начинающим подрядчикам как перевести сметы в читаемый вид.
Расчёт стоимости работ – жизненно важный этап строительства. Понимание цены проекта предсказывает ожидаемые прибыли застройщиков. Подрядчики скрупулёзно калькулируют себестоимость затрат, не желают трудиться ради опыта под крылом громкого бренда.
Специализированные компании знают подводные камни дорогостоящих манипуляций. Представляют КП, включающие полный список затрат на выполнение проекта и ожидаемую прибыль. Осознают планку, ниже которой объект строительства становится неинтересен.
Сметчики познали тайные знания государственных справочников расценок на заработные платы и материалы. Ловко жонглируют коэффициентами и перечнем наименований, добиваясь целевого удорожания/удешевления реализации. Злоупотребляют абсолютным доверием руководителя.
Для тендера получили от Заказчика сметы проектного института. Правило участия: заключение договора с общим понижающим коэффициентом 0,9. Простые условия требуют серьёзной разборки расчётов и немалых временных затрат. Объем предстоящих работ ~ 1,5 млрд. руб.
Сформулировали задачу: создать конвертор сметы в ведомость объёмов работ. Упростить понимание кратности измерения. Единичная расценка и стоимость позиции должна учитывать все коэффициенты, а также налог на добавочную стоимость при необходимости.
Тестирование скрипта проводили на смете, имеющей 5’109 строк. Обилие табличной информации обусловлено возможностью проверки печатного экземпляра при помощи калькулятора.
Отсутствуют скрытые индексы и формулы, коэффициенты заданы в явном виде.
Обозначены цифры государственных справочников и наименования разделов расчётов. Обилие инфоримации стреноживает попытки разобраться в представленной вакханалии цифр, странных определений и незнакомых сокращений. Стоимости кажутся оторванными от реальности:
«Подвал» сметы собирает агрегированные данные основных бюджетов. Реалии рынка зачастую критично разнятся с теоретическими выкладками федеральных, территориальных или иных утверждённых справочников. Обясняет нежелание исполнителей работать в убыток.
По опыту скажем, что малые строители из всей сметы понимают только последнее число – общую стоимость проекта. Прикидывают по наитию собственную затратную часть на реализацию, суммируют аппетит по прибыли и принимают решение о целесообразности участия в процессе.
В качестве ключевого инструмента выбрали язык программирования Python.Использовали среду разработки JupyterLab и библиотеки из дистрибутива для анализа данных Anaconda. Модуль Numpy, сокращённо от Numerical Python, для выполнения математических вычислений.
Фреймворк Pandas, название которого происходит от эконометрического термина «панельные данные», используемого для описания многомерных структурированных наборов информации. Pandas ассистировал для быстрых расчётов, обработки и формирования табличных данных.
JupyterLab открывается в окне браузера, владеет многооконностью, имеет встроенный файловый менеджер, терминал и поддерживает скрипты в формате ipynb. Среда позволяет интерактивно запускать код на Python, визуализировать результаты расчётов и графики.
Описали User Story – считаем, что пользователь конвертора преобразует excel-файлы с не отформатированными, неоднородными выгрузками из сметной системы в структурированные таблицы с рассчитанными ведомостями: количество объединённых ячеек пугает оригинальностью.
Спроектировали части программы:
- Импорт пакетов, чтение входных данных.
- Приведение структуры таблиц к нормальной форме.
- Вычисления для формирования ведомости объёмов работ.
- Преобразование внешнего вида таблицы к отчётному формату.
- Выгрузка файла типа excel с названием и директорией по шаблону.
Помимо аналитических Pandas и Numpy сокращенных до pd и np, использовали встроенные стандартные библиотеки: из пакета String извлекли набор символов для очистки данных, из модуля Copy – функционал для глубокого копирования с полным дублированием зависимостей.
Входными параметрами программы установили: путь к файлу, название требуемого листа excel-книги, количество пустых строк в начале массива – зависит от типа выгрузки, финансовый коэффициент для расчёта показателей. Для чтения использовали функцию pd.read_excel.
Исходный датафрейм df разбили на несколько таблиц: разделы сметы parts, виды работ и их характеристики rows, промежуточные итоги result, общий итог total_result. Использовали loc для навигации по столбцам и строкам, apply и lambda для фильтрации и преобразования значений.
При помощи ffill, bfill заполнили пустоты, reindex – привели в целевой вид нумерацию строк, merge – выполнили объединение таблиц по номеру строки. Произвели векторизованные вычисления расчётных показателей через numpy-обёртку для математических функций и операторов.
Комбинацией apply, lambda и split выделили из исходной колонки с единицами измерения и объёмами. Применили условие – если в значении столбца первый объект является числом, записываем как значение величины, иначе – ставим единицу. Второй принимаем за меру.
Сформированные расчётные показатели функцией insert добавили в результирующую таблицу при помощи astype – поменяли тип данных, drop – удалили лишние колонки, которые создали на промежуточных этапах. Итоговый датафрейм записали в excel-файл через метод to_excel.
Смоделированная программа позволила автоматизировать распознование и вычисления для формирования ведомости объемов работ. Остается ручная часть процесса – проверяем входные данные на соответствие формату или добавляем в скрипт логику просмотра нового типа документа.
Среда разработки JupyterLab позволила быстро протестировать гипотезы, отобразить выходные таблицы в удобном для анализа виде. Благодаря возможности тонкой настройки пользовательского интерфейса, использовали ночной режим, программировали в нескольких окнах одновременно.
Визуальный осмотр выявил, что ведомость объёмов будет сформирована из 586 наименований работ. Приведём результаты к простому табличному виду со столбцами: №, наименование, ед. изм., объём, цена, стоимость. Добавим стоблец «Раздел» в случае необходимости.
Единицы измерения запишем в читаемом формате. Объёмы работ запишем без дополнительных коэффициентов. Цены и стоимости сразу будут включать индексы, накладные расходы, сметную прибыль и НДС. JupyterLab подитожил визуализацию массива подсчётом строк и колонок:
Последующую обработку получившейся таблицы проводим в MS Excel. Инструмент помогает визуализировать данные в привычном интерфейсе, использовать условное форматирование. Цветовое обрамление и выделение позволяют упростить понимание расчитанных значений.
Чрезстрочное выделение облегчает читаемость. Подсвечивание отрицательных значений красным цветом указывает на «изобретальность» сметчиков и несовершенство государственных справочников расценок: часто выкручиваются при уменьшении толщин материалов и конструкций.
Представление перечня работ стало менее запутанным, но не идеальным. Поочерёдно отфильтровывая разделы, получаем полное представление о перечне и количестве расчитанных позиций. Рассматриваем таблицу на предмет неучтённых, либо избыточные наименований.
Наглядно проявляется трудночитаемость некоторых названий процедур: отсутсвует явное разделение на материалы, работы, механизмы. Рассматриваем в комплексе полным разделом. Вывод сумм по разделам для упрощения контроля в рассматриваемом примере не требовалось.
Наш мини-скрипт позволил оптимизировать 12 смет для 10 сооружений. Участвовали в тендере, понимая предел финансовых ожиданий Заказчика по каждому разделу таблиц перечисленных объектов. Искали возможности производственной оптимизации ранее отработанных решений.
Тендер разыгрывался по лотам, поэтому выполнили преоретизацию по сумме смет, ожидаемой прибыли, сложности производства работ. Сформулировали стратегию дальнейших действий в случае возможности выбора отдельных лотов. Позиции 8, 9, 10 обособленно не привлекательны.
Взаимодействия со сметами – неотъемлемая часть сотрудничества с государственными заказчиками по 44-ФЗ и 223-ФЗ. Государственные казначеи не готовы работать по коммерческим расценкам, требуют обоснования утверждёнными справочниками. Без сметчиков не обойтись.
Разработанный скрипт позволяет облегчить рутинные операции специалистов. Руководители требуют легкочитаемые документы. Перевод смет к ведомости вручную – непозволительная трата времени. Если действие повторяется более четырёх раз, пора задуматься об автоматизации!