Как мы искали должников при помощи Pandas

Петербургский Фонд капитального ремонта опубликовал документы, в которых указана задолженность за каждую квартиру в городе по итогам 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 операций в секунду.

Результаты и выводы

Так можно получить базу данных, с которой, правда, потребуется ещё поработать, чтобы увидеть в ней интересные закономерности. Нормализация заключается, в частности, в приведении столбцов к числовым типам. А для того, чтобы построить интерактивную карту, нужно ещё получить координаты по адресам. Но геокодинг — это отдельная тема, которой возможно, как-нибудь посвятим публикацию.

В итоге, мы обнаружили, что чаще всего за капитальный ремонт не платит бизнес. То есть владельцы нежилых (коммерческих) помещений в многоквартирных жилых домах. Так как в центре Петербурга, по сравнению с другими частями нашего города, таких помещений больше всего, то и должников там значительно больше. По словам экспертов, если собственник не платит за капитальный ремонт, то и другие жилищно-коммунальные услуги он, скорее всего, не будет аккуратно оплачивать. Поэтому можно предполагать, что существенная часть долга за ЖКУ — это долги бизнеса.

Итоговый материал можно прочитать на сайте газеты.