Петербургский Фонд капитального ремонта опубликовал документы, в которых указана задолженность за каждую квартиру в городе по итогам 2024 года. Мы изучили эти файлы, чтобы ответить на вопрос: где и почему хуже всего платят за ремонт в своём доме.
Я занимаюсь анализом данных и дата-журналистикой в газете "Деловой Петербург". Расскажу о том, как объединяли информацию из множества локальных html-таблиц и приведу примеры кода на "Питоне".
Документы — это более 20 тыс. отчётов (по одному отчёту на каждый многоквартирный жилой дом в Петербурге), собранных в папки по восемнадцати районам города. Очевидно, это результат автоматической выгрузки из базы данных.
Часто журналисты не раскрывают свои источники. Но в этом случае данные открыты, хоть и расположены не в самом очевидном месте (вообще, опыт показывает: то, что прямо называется "открытыми данными", часто особого интереса обычно не представляет). Всё выглядит легальным: организация сама заявляет, что публикует эти документы на основании одного из приказов Минстроя.
Архив за последний год (а при желании, и архивы за последние несколько лет) можно загрузить с файлового сервера Фонда капремонта. Распакуем его в каталог "Долги" в папке нашего проекта. Каждый отчёт в архиве — это html-файл. Кто-то скажет: наверное, намного легче было бы написать SQL-запросы, чтобы получить нужную информацию, чем копаться в этих файлах. Но, увы, к самой базе у нас доступа нет, так что будем работать с отчётами.
Итак, у нас есть 18 папок, которые нужно пройти, в каждой более 1 тыс. документов, всё вместе содержит информацию о более чем 2 млн помещений (квартир и нежилых помещений). Автоматически прочитать таблицу из html-документа можно при помощи метода Pandas read_html(). Нам нужно прочитать их все и объединить содержащиеся в файлах таблицы.
Прямолинейное решение
Первое, что приходит в голову — объединить маленькие таблицы в большую средствами самой библиотеки Pandas:
#Это пример кода на языке Python 3
#В нём собираем нужную информацию из html-таблиц в одну базу данных
#Предполагается, что выгруженные с сервера каталоги с
#html-файлами дежат в папке data нашего проекта.
import pandas as pd #Наш главный помощник - библиотека Pandas.
#Не забудьте установить её, если будете запускать этот код.
from pathlib import Path #Для подсчёта числа файлов
import os #Для прохода по списку файлов
from tqdm import tqdm #Необязательно: используем, чтобы
#следить за ходом выполнения программы.
#Этот модуль тоже устанавливается отдельно.
dfconcat=pd.DataFrame() #Готовим пустую базу данных
path='Долги' #Путь к папке, где лежат файлы отчётов
files_count=len(list(Path(path).rglob("*"))) #Подсчитаем общее число файлов
pbar=tqdm(total=files_count)
for foldername, subfolders, filenames in os.walk(path): #Проходим по всем подкаталогам
for filename in (filenames): #И берём каждый файл
filepath=os.path.join(foldername, filename) #Получаем полный путь к файлу
try: #По очень небольшому числу домов нет данных о долгах, потому что
#их жители собирают взносы на отдельных счетах. Пропускаем эти дома.
df=pd.read_html(filepath, thousands='.', decimal=',') #Считываем таблицы
#из html-файла
except:
continue
#Каждый отчёт содержит несколько таблиц. Для нашего примера
#мы будем брать только строчки с долгами по каждому помещению в доме
debts=df[3].iloc[4:-2]
dfconcat=dfconcat._append (debts, ignore_index=True) #Присоединяем собранные
#строки к единой таблице
#Можно также
#использовать похожий
#метод concat
pbar.update()
pbar.close()
print (dfconcat) #Выводим результат в командную строкуВначале всё идёт хорошо, и ожидаемое время завершения формирования единой таблицы, как показывает индикатор полосы прогресса tqdm, составляет около 5 минут. Но с каждым присоединением промежуточной таблицы оно растёт. Когда индикатор хода выполнения задачи подходит к 20 %, прогнозируемое время выполнения её приближается уже к 30 минутам, а скорость падает с 30 до 8 итераций в секунду. Затем прогноз оставшегося времени и вовсе приближается к 2 часам.
Объединение таблиц средствами "Питона"
Причина, судя по всему, в медленной работе метода append в Pandas. Попробуем обойтись без него. Следующее решение, предполагающее двойное преобразование, я нашёл на Stack Owerflow. Подготовим пустой словарь-накопитель. Каждую маленькую таблицу, прочитанную из html-файла, будем преобразовывать в словарь и добавлять к словарю-накопителю. Затем уже преобразуем словарь-накопитель обратно в таблицу Pandas, чтобы можно было его анализировать.
from pathlib import Path
import pandas as pd
import os
from tqdm import tqdm
path='Долги'
files_count=len(list(Path(path).rglob("*")))
pbar=tqdm(total=files_count)
d = {} #Готовим словарь-накопитель
i = 0 #Итератор
for foldername, subfolders, filenames in os.walk(path):
for filename in (filenames):
filepath=os.path.join(foldername, filename)
try:
df=pd.read_html(filepath, thousands='.', decimal=',')
except:
continue
debts=df[3].iloc[4:-2]
#Убираем лишние колонки, которые не несут смысловой нагрузки
debts.drop(debts.columns[[-1,-2,-3,-4,-5,-6]],axis=1,inplace=True)
#Дадим колонкам понятные названия
debts.columns=['Номер помещения', 'Задолженность на начало периода', 'Аванс на начало периода', 'Начислено нарастающим итогом', 'Уплачено нарастающим итогом', 'Засчитано за оказанные услуги', 'Задолженность на конец периода', 'Аванс на конец периода', 'Уплачено пени']
data = debts.to_dict('records') #Преобразуем таблицу в словарь
for entry in data: #И добавляем каждую строчку к словарю-накопителю
d[i] = {"Номер помещения": entry["Номер помещения"], "Задолженность на начало периода": entry["Задолженность на начало периода"], "Аванс на начало периода":entry["Аванс на начало периода"], "Начислено нарастающим итогом":entry["Начислено нарастающим итогом"], "Уплачено нарастающим итогом":entry["Уплачено нарастающим итогом"], "Засчитано за оказанные услуги":entry["Засчитано за оказанные услуги"], "Задолженность на конец периода":entry["Задолженность на конец периода"], "Аванс на конец периода":entry["Аванс на конец периода"], 'Уплачено пени':entry["Уплачено пени"]}
# increment the counter
i = i + 1
pbar.update()
pbar.close()
dfconcat = pd.DataFrame.from_dict(d, "index") #Обратное преобразование из словаря
#в таблицу
print (dfconcat)Вышло около 13 минут, что значительно лучше. Но и этот способ оказался не самым быстрым.
Объединение таблиц через базу данных
Чуть быстрее на моём компьютере оказалось загружать извлечённую из html-файлов табличную информацию в базу данных. Хотя для работы c SQL базами данных из "Питона" часто используют библиотеку SQLAlchemy, здесь это не строго обязательно. Мы будем напрямую отправлять несложные SQL команды из Pandas во входящий в стандартный пакет поставки "Питона" модуль SQLite.
#Это пример кода похож на предыдущий, но таблицы
#из html-файлов объединяются при помощи SQLite
import pandas as pd
import os
import sqlite3 #На этот раз будем использовать SQL-базу для накопления данных
from pathlib import Path
from tqdm import tqdm
# Подключаем файл базы данных и создаём таблицу
con=sqlite3.connect('est.db')
cur=con.cursor()
cur.execute("DROP TABLE IF EXISTS result;") #На всякий случай, если прошлый
#проход программы был прерван
cur.execute("CREATE TABLE result (foldername, filename, num_of_ap,
start_debt, start_avans, accrued, paid, counted, final_debt,
final_avans, peni);")
path='Долги'
files_count=len(list(Path(path).rglob("*")))
pbar=tqdm(total=files_count)
#Эта функция вызывается для каждой строки таблицы, считанной Pandas,
#и записывает эту строку в базу SQLite
def addrows (row):
params=(foldername, filename, row['Номер помещения'],
row['Задолженность на начало периода'],
row['Аванс на начало периода'],
row['Начислено нарастающим итогом'],
row['Уплачено нарастающим итогом'],
row['Засчитано за оказанные услуги'],
row['Задолженность на конец периода'],
row['Аванс на конец периода'],
row['Уплачено пени'])
cur.execute('INSERT INTO result VALUES (?,?,?,?,?,?,?,?,?,?,?);', params)
for foldername, subfolders, filenames in os.walk(path):
for filename in (filenames):
filepath=os.path.join(foldername, filename)
try:
df=pd.read_html(filepath, thousands='.', decimal=',')
except:
continue
debts=df[3].iloc[4:-2]
debts.drop(debts.columns[[-1,-2,-3,-4,-5,-6]],axis=1,inplace=True)
#Дадим понятные названия колонкам, чтобы потом записать по ним их в
#базу в функции addrows
debts.columns=['Номер помещения',
'Задолженность на начало периода',
'Аванс на начало периода',
'Начислено нарастающим итогом',
'Уплачено нарастающим итогом',
'Засчитано за оказанные услуги',
'Задолженность на конец периода',
'Аванс на конец периода',
'Уплачено пени']
debts.apply(addrows, axis=1)
pbar.update()
pbar.close()
dfconcat = pd.read_sql_query("SELECT * FROM result", con)
print (dfconcat)Выполнение этой программы заняло около 12 минут, что намного быстрее первоначального варианта, и чуть быстрее варианта со словарём. На моём компьютере она выполнялась со скоростью около 30 операций в секунду.
Результаты и выводы
Так можно получить базу данных, с которой, правда, потребуется ещё поработать, чтобы увидеть в ней интересные закономерности. Нормализация заключается, в частности, в приведении столбцов к числовым типам. А для того, чтобы построить интерактивную карту, нужно ещё получить координаты по адресам. Но геокодинг — это отдельная тема, которой возможно, как-нибудь посвятим публикацию.
В итоге, мы обнаружили, что чаще всего за капитальный ремонт не платит бизнес. То есть владельцы нежилых (коммерческих) помещений в многоквартирных жилых домах. Так как в центре Петербурга, по сравнению с другими частями нашего города, таких помещений больше всего, то и должников там значительно больше. По словам экспертов, если собственник не платит за капитальный ремонт, то и другие жилищно-коммунальные услуги он, скорее всего, не будет аккуратно оплачивать. Поэтому можно предполагать, что существенная часть долга за ЖКУ — это долги бизнеса.
Итоговый материал можно прочитать на сайте газеты.
Автор: alfablend
