news.shamcode.ru | Электронная демократия или как собрать и обработать данные по голосованию (и явке) за реновацию в Мо

Электронная демократия или как собрать и обработать данные по голосованию (и явке) за реновацию в Мо

вторник, 18 июля 2017 г. в 03:13:04

Кодобред
SQLite
Python

Стоило голосованию за вход или выход из программы реновации завершиться — и с сайта мэра Москвы почему-то пропали данные о явке по каждому конкретному дому, остались только голоса за и против в целом. В новостях, конечно, пишут некие цифры, но ведь хочется посмотреть их самому, поиграться со статистикой, построить графики, не правда ли?

Да и после заявлений типа:

Что касается популярности этих сервисов, то центры госуслуг «Мои документы» привлекли чуть больше половины всех проголосовавших, незначительно уступив порталу «Активный гражданин»

как-то возникают лёгкие сомнения. Так что — приступим к сбору информации! А потом будем её анализировать. Для этого нам понадобится какой-нибудь язык (скажем, питон), какая-нибудь бд (скажем, sqlite) и какой-нибудь веб-скраппер, благо для питона их множество. Сразу говорю, в конце дам ссылку на получившуюся базу данных, можно сделать с ней что угодно.

Заходим на сайт мэрии, крутим колёсико. Надо собрать данные с 4543 домов. Кликам по случайному дому, по районным спискам, и смотрим, в каком формате вообще данные есть.

Судя по всему, у каждого дома есть некий id, который мы и видим в url:

https://www.mos.ru/otvet-stroitelstvo/itogi-golosovaniya-zhitelej-po-proektu-programmy-renovacii/?u=121

Следовательно, для того, чтобы обработать все дома, нам нужно где-то достать список всех айдишников и перебрать их, забирая с каждой странички нужную информацию, а именно — количество голосов за, количество голосов против, количество голосов всего (ведь есть и «неопределившиеся» голоса, когда у одной квартиры половина голосует за, а половина — против) и было ли общедомовое собрание (зачем? а вдруг пригодится).

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

Для начала сделаем гет-запрос на какой-нибудь дом, и посмотрим на получившийся ответ, чтобы понять, что нам вообще оттуда скраппить.

import requests
r = requests.get('https://www.mos.ru/otvet-stroitelstvo/itogi-golosovaniya-zhitelej-po-proektu-programmy-renovacii/?u=121')
print(r.text)

С удивлением обнаруживаем, что в ответе никаких данных по голосованию нет, нам отдаётся какой-то шаблон страницы. Посмотрим глазами, как происходит загрузка результатов. И действительно — сначала у нас в браузере страница с незаполненными полями, а через некоторое время в них появляются данные. Возьмём браузер с подходящими средствами «для разработчика», например, мозиллу, и посмотрим, что же там, собственно, происходит.

Ага! Мы обнаружили запрос браузера к некоему апи, причём айдишник совпадает с айдишником дома, который мы смотрим.

Иными словами, гет-запрос на www.mos.ru/altmosprx/api/1/renovation/house_result/121 безо всяких параметров даёт нам в ответ JSON такого вида:

{
  "execTime": 0.044450044631958,
  "errorMessage": "",
  "result": {
    "table": "<table class="table table-big"><thead><tr class="table-header"><td>Код квартиры</td><td>Идентификатор голоса</td><td>Решение</td><td>Дата</td><td>Выбор квартиры</td></tr></thead><tbody><tr><td class="apartment-id">0G6O4</td><td class="voting-info"><p>bf659227e8e3</p><p>5f9403659209</p></td><td class="voting-choice"><p>За</p><p>За</p></td><td class="voting-date"><p>18.05</p><p>18.05</p></td><td class="apartment-position apartment-agree"><p>За</p></td></tr><tr><td class="apartment-id">0G6O5</td><td class="voting-info"><p>3f12be5cea77</p></td><td class="voting-choice"><p>За</p></td><td class="voting-date"><p>15.05</p></td><td class="apartment-position apartment-agree"><p>За</p></td></tr><tr> ... <td class="apartment-id">0G6V1</td><td class="voting-info"><p>5acd126a410ea1a842e67066ea68fa8f</p></td><td class="voting-choice"><p>За</p></td><td class="voting-date"><p>24.05</p></td><td class="apartment-position apartment-agree"><p>За</p></td></tr></tbody></table>",
    "total": {
      "und": 0,
      "za": 100,
      "protocol_res": 0,
      "protiv": 0,
      "gorod_mark": 0,
      "protocol_date": null,
      "house_status": 1,
      "gorod": 0
    },
    "und_table": "<table class=\"table table-big\"><thead><tr class=\"table-header\"><td>Код квартиры</td><td>Идентификатор голоса</td><td>Решение</td><td>Дата</td><td>Выбор квартиры</td></tr></thead><tbody></tbody></table>",
    "address": "Авиационная улица, дом 63, корпус 2"
  },
  "request_id": "empty_requestid",
  "errorCode": 0
}

Отлично, тут нам даже не понадобится ничего скрапать, все данные и так есть сразу из апи. Всего-то нужно — посчитать количество apartment-id (всего голосов), взять готовые данные о проценте голосов за, об общедомовом собрании.

Но всё-таки, в каком диапазоне айдишников мы будем смотреть? Всего домов должно быть 4543. Даём апи 0 — есть такой дом. Даём -1 — ошибка, ну и на том спасибо. Нижнюю границу определили. Даём 10000 — есть такой дом. Ладно, их явно больше 4543. Попробуем определить верхнюю границу приблизительно, посмотрев какой-нибудь дом из недавно присоединённых территорий… Возвращаемся к карте, лезем куда-нибудь в «Новую Москву» подальше от «старой», о: поселение Кокошкино, дачный посёлок Кокошкино, улица Труда, дом 2, id: 440931. Ну, как минимум полмиллиона их у нас есть.

Перебирать полмиллиона ссылок в обычном loop'е — идея не самая лучшая, поэтому воспользуемся модулем concurrent.futures. Можно, конечно, было бы использовать что-нибудь вроде asyncio, но у нас не настолько масштабная задача и можно обойтись «малой кровью». Всё очень просто. Смотрим, что отдаёт апи при заведомо правильном номере дома, при заведомо неправильном, и делаем функцию для чекинга этого всего. Потом крутим это всё в цикле с паралелльными запросами, т.к. айдишников нужно обработать довольно много. Потом оформляем результат и записываем его. В целом получается примерно такой код:

import requests
from concurrent.futures import ProcessPoolExecutor
import concurrent.futures

def check(url):
    #try тут нужен, потому что гет-реквест, не смотря на таймаут, иногда плохо себя ведёт
    #мы же не хотим в конце перебора миллиона страничек получить краш с ошибкой
    try:
        r = requests.get('https://www.mos.ru/altmosprx/api/1/renovation/house_result/' + str(url) + '/', timeout=10)
        print(url)
        r.encoding = 'utf-8'
        if '400: Bad Request' not in r.text:
            return str(url)
    except:
    #ошибок будет немного, но лучше эти номера потом пробить ещё раз, для этого пишем и их
        woops = str(url) + ' failed'
        return woops
    
results = []
with ProcessPoolExecutor(max_workers=6) as executor:
    future_results = {executor.submit(check, url): url for url in range(0, 1000000)}
    #если нужны конкретные номера, то просто берём их из списка: url in somelist
    for future in concurrent.futures.as_completed(future_results):
        results.append(future.result())

results[:] = [item for item in results if item or item == 0]
#check возвращает None, если дома нет, убираем; а вот ноль нам нужен, не забудем о нём

with open('/home/deb/mosres.txt', 'w') as f:
    for item in results:
        f.write('{}\n'.format(item))

Запускаем и идём по своим делам — это надолго даже с 6 worker'ами. Забегая вперёд, скажу, что обработав миллион, получил где-то на 70 домов меньше, чем было в результатах, так что пришлось крутить эту волынку до 10 миллионов. Это долго, оставил работать и ушёл на работу.
Конечно, можно было бы поднять количество параллельных запросов, но мы же пользуемся чужим айпи, нужно вести себя вежливо (а то вдруг забанят).

В общем, у нас есть список всех айдишников домов, теперь надо их обработать, забрать всю имеющуюся информацию и т.п. В текстовый файл такой массив засовывать не с руки — обрабатывать будет неудобно. Воспользуемся sqlite3.

Создадим бд. Все поля очевидны, разве что дом и дополнительные элементы типа корпуса, строения и т.п. разнесём — на всякий случай, если захотим, например, смотреть тенденции у рядом стоящих домов.

import sqlite3

schema = "CREATE TABLE `houses` (\
	`id`	INTEGER PRIMARY KEY,\
      `street` TEXT NOT NULL ,\
	`house_nbr`	TEXT NOT NULL,\
 	`house_additional`	TEXT,\
	`total_votes`	INTEGER,\
	`total_za`	INTEGER,\
	`meeting`	INTEGER DEFAULT '0',\
      `flats` INTEGER\
);"

conn = sqlite3.connect('renovation.db')
cur = conn.cursor()
db = cur.execute(schema)
conn.commit()
conn.close()

Ну что же, к делу! Сделаем функцию, достающую и добавляющую информацию по домам в базу, попутно будем записывать все JSON-ошибки (скорее всего, в ответе не было JSON) и неизвестные ошибки (скорее всего, ответа вообще не было), а потом можно будет их посмотреть и прогнать отдельно, если такие будут, конечно.

import requests
import re
import sqlite3

def gethouseinfo(idd):
    print(idd)
    urly = 'https://www.mos.ru/altmosprx/api/1/renovation/house_result/' + str(idd) + '/'
    try:
        r = requests.get(urly) 
        r.encoding = 'utf-8'
        results = r.json()
        adress = results['result']['address']
        print(adress)
        if re.match('(.*), (дом.*), (.*)', adress):
            adress_street = re.match('(.*), (дом.*), (.*)', adress).group(1)
            adress_house = re.match('(.*), (дом.*), (.*)', adress).group(2)
            adress_building = re.match('(.*), (дом.*), (.*)', adress).group(3)
        else:
            adress_street = re.match('(.*), (дом.*)', adress).group(1)
            adress_house = re.match('(.*), (дом.*)', adress).group(2)
            adress_building = ''
        totalvotes = len(re.findall('apartment-id', results['result']['table'])) + len(re.findall('apartment-id', results['result']['und_table']))
        aye = results['result']['total']['za']
        meetinghappened = bool(results['result']['total']['protocol_res'])
        iddlist = []
        iddlist.append(idd)
        check = cur.execute('SELECT * FROM houses WHERE id=?', iddlist)
        res = check.fetchone()
        if res:
            print('already exists')
        else:
            insert = cur.execute('INSERT INTO houses (id, street, house_nbr, house_additional, total_votes, total_za, meeting) values (?, ?, ?, ?, ?, ?, ?)', [idd, adress_street, adress_house, adress_building, totalvotes, aye, meetinghappened])
            print('added ' + str(idd))
    except ValueError:
        print('no data for id '+ str(idd))
        jsonerror.append(idd)
    except:
        print('unknown eggog')
        unknownerror.append(idd)

jsonerror = []
unknownerror = []
with open('/home/deb/mosres.txt') as fc:
    mosres = fc.read().splitlines()

conn = sqlite3.connect('/home/deb/renovation.db')
cur = conn.cursor()

for house in mosres:
    gethouseinfo(house)

conn.commit()
conn.close()

if jsonerror:
    with open('/home/deb/jsonerror.txt', 'w') as f:
        for item in jsonerror:
            f.write('{}\n'.format(item))
            
if unknownerror:
    with open('/home/deb/unknownerror.txt', 'w') as f:
        for item in unknownerror:
            f.write('{}\n'.format(item))

Итак, это уже кое-что. Сейчас у нас есть база данных по голосованию за реновацию, созданная на основе открытой информации на сайте мэрии. Уже можно вникать и (наконец-то!) рисовать графики!

Все результаты (отсортированные от малого до великого, чтобы наглядно посмотреть процент за и против, икс — дома, игрек — проценты за, зелёная линия — для удобства, отсекает 66 процентов, которые надо было преодолеть):

Распределение голосов «за»:

Всё это хорошо, но нас же интересовала явка. И вот тут становится тяжело. Дело в том, что нормального, простого, централизованного ресурса с количеством квартир по адресу нет. Возможно, оно есть у 2ГИС, по крайней мере, в примерах платной выгрузки информации количество квартир есть, но — платно? Это же не наш метод! Мы пойдём другим путём.

Нещадный гуглинг и яндекс указал на сайт tvoyadres.ru/doma, где дома, казалось бы, есть, зачастую — с информацией по квартире. Но как их собирать? В идеале нужно сначала собрать весь список домов (ну хорошо, хотя бы улиц), на которые есть данные, а потом попытаться связать адреса в нашей базе данных, взятые в формате мэрии, с адресами в формате этого сайта, а потом вытащить у тех, что удалось связать, квартиры. Наверное, стоит начать с улиц? tvoyadres.ru/ulitsy — но перебирать 200 страниц, скрапя и обрабатывая каждую выдачу, очень муторно. Может быть, удастся найти и тут какой-нибудь апи?

Успех ждал на странице города, где есть не только список улиц, но и кнопка «Ещё улицы»: tvoyadres.ru/moskovskaya-oblast/goroda/551

Ага! Запрос вида
http://tvoyadres.ru/js/street.php?region=81&city=Москва&count=2073&_=1499809159225
даёт нам список улиц со ссылками на них (а в ссылки — айдишник). Отлично, осталось понять, что значат символы в запросе. Регион, очевидно, мы трогать не будем, город, тоже. Попробуем убрать последнее непонятное, оставив лишь tvoyadres.ru/js/street.php?region=81&city=Москва&count=2073 — результат такой же. ОК, нажмём ещё раз на кнопку — увидим, что пошёл такой же запрос, но с каунтом на 100 меньше. Поиграем вручную с этим параметром.

0 — возвращается ошибка. 1 — возвращается одна улица. 2 — возвращается две улицы, причём одну мы уже видели. 100 — возвращается 100 улиц. 200 — возвращается ещё 100 улиц. Начинали мы с 2073 — попробуем 2173? Да, это те самые первые сто улиц, что отображались на странице города. 2174?

Critical Error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1, 100' at line 1

Оп-па. Ну, похоже, count идёт в LIMIT SELECT-запроса, причём в лимите всегда выдаётся 100 строк, а вот строка, с которой начинать, вычисляется как count минус 2173. Кстати, вроде довольно секурно — как сделать тут sql-инъекцию я не придумал, оно же не передаётся, а вычисляется, и если сунуть туда не число — банально падает. Ну да ладно. Результат есть. Отлично, теперь к делу.

Всё даже проще, чем обычно:

def getstreets(num):
    r = requests.get(url + str(num))
    results = r.json()
    result = results['string']
    return(result)

for i in range(1, 2272, 100):
    totalres += getstreets(i)

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

sids = re.findall('ulitsy\/(.*?)">(.*?)<\/a>', totalres)
#sids = streets with ids
streetsdict = {}

for i in range(len(sids)):
    key = sids[i][1]
    value = sids[i][0]
    streetsdict[key] = value

А вот дальше нам предстоит доставать дома со страниц улиц, иными словами — работать над кучей куч html. Такие кучу регулярными выражениями пытаться отпарсить — себе дороже. Поэтому познакомимся с BeautifulSoup.

Делаем функцию, запускаем её для отдельного дикта, где ключами будет уже айдишник, а значениями — квартиры. Логика такая — по названию квартиры можно будет получить айди, по айди — квартиры.

import re
from bs4 import BeautifulSoup

def gethouses(num):
    r = requests.get('http://tvoyadres.ru/moskovskaya-oblast/moskva/ulitsy/' + str(num) + '/')
    results = r.text
    soup = BeautifulSoup(results, 'html.parser')
    ul = soup.find("ul", {"class": "next"})
    houses = []
    try:
        for li in ul.find_all("li"):
            urly = li.a['href']
            urly = re.search('doma\/(.*)\/', urly).group(1)
            houses.append([li.get_text(), urly])
        return(houses)
    except:
        print('None')
        return('None')

totalyres = {}

for key in sids:
    num = sids[key]
    totalyres[num] = gethouses(num)

Тут нам приходит в голову сразу две вещи. Первая — надо бы сделать не дикт, в котором на каждую улицу приходится список, в котором каждое значение — список с названием дома и его айдишником, а дикт со вложенными диктами. Это можно превратить простым loop'ом, не будем заострять внимание, но назовём такой дикт totre — да, фантазия под конец совсем меня покинула. Извините.

Вторая — а ведь юрл каждого дома включает в себя не только айдишник, но и транслитерацию улицы! Пример.

for key in totre:
    urlo = 'http://tvoyadres.ru/moskovskaya-oblast/moskva/ulitsy/' + key + '/'
    ra = requests.get(urlo)
    try:
        streetname = re.search('<ul class="next"><li><a href="\/moskovskaya-oblast\/moskva\/(.+?)\/doma\/', ra.text).group(1)
        totre[key]['streetname'] = streetname
    except:
        print(key)

И вот мы подошли к почти самому сложному. Нужно те названия улиц, что есть у нас в базе данных из мэрии, связать с айдишниками, используя те названия улиц, что есть из твоегоадресаточкару. Помогать в этом нам будет difflib, благо в питон он встроен. Но мало надеяться на диффлиб, нужно будет сделать проверку пользователем, потому что частотность и схожесть это, конечно, хорошо, но нам нужно избежать глупых ошибок. В общем, смотрим на форматы, замечаем, что где-то нет букв ё как класс, где-то слово «улица» из названия улицы убрано, и делаем такое вот:

conn = sqlite3.connect('renovation.db')
cur = conn.cursor()
streets = cur.execute('SELECT DISTINCT street FROM houses order by street asc')
streeets = streets.fetchall()
conn.close()

exactmatches = {}
keyslist = []
for key in sids.keys():
    keyslist.append(key)

def glue(maxres=3, freq=0.6):
    for each in streeets:
        eachnoyo = each[0].replace('ё', 'е')
        diffres = difflib.get_close_matches(eachnoyo, keyslist, maxres, freq)
        if each[0] not in exactmatches.keys():
            if len(diffres) == 1:
                print(each[0] + ': ' + diffres[0])
                notcompleted = False
                while notcompleted == False:
                    inp = input('Correct? y/n ')
                    if inp == 'y':
                        notcompleted = True
                        exactmatches[each[0]] = sids[diffres[0]]
                    elif inp == 'n':
                        notcompleted = True
                    else:
                        print('Incorrect input, try again')
            elif len(diffres) == 0:
                print('No matches for ' + each[0])
            elif len(diffres) > 1:
                print(each[0] + ': ' + str(diffres))
                notcompleted = False
                while notcompleted == False:
                    inp = input('List number? Or n ')
                    try:
                        listnum = int(inp)
                    except:
                        listnum = None
                    if inp == 'n':
                        notcompleted = True
                    elif listnum in range(0, len(diffres)):
                        notcompleted = True
                        exactmatches[each[0]] = sids[diffres[0]]
                    else:
                        print('Incorrect input, try again')
    with open('exactmatches.json', 'w') as f:
        json.dump(exactmatches, f, ensure_ascii=False)

Сидим в консоли, смотрим выдачу, нажимаем кнопки. Когда прошли весь цикл — запускаем функцию с более щадящими параметрами, например, glue(10, freq=0.4).

Мне терпения хватило на 506 улиц из 700 с копейками — по-моему, отличный результат, а главное, статистически значимый (скорее всего).

А теперь нужно сделать то же самое для домов, а также взять, собственно, их количество квартир. И поместить в базу данных.

conn = sqlite3.connect('renovation.db')
cur = conn.cursor()
allhouses = cur.execute('SELECT * FROM houses WHERE flats IS NULL ORDER BY id')
allhousesres = allhouses.fetchall()

url2 = 'http://tvoyadres.ru/moskovskaya-oblast/moskva/'


def getnumberofflats(streetname, houseid):
    urlo = url2 + str(streetname) + '/doma/' + str(houseid) + '/'
    r = requests.get(urlo)
    results = r.text
    numbe = re.search('<span class="left">Количество квартир<\/span> <span class="right">(\d*)<', results).group(1)
    return numbe

def gluehousesnumbers(freq=3, ratio=0.6):
    for house in allhousesres:
        if house[1] in exactmatches.keys():
            housenbr = house[2].replace('дом', '')
            if house[3]:
                housenbr = housenbr + ' ' + house[3]
            housenbr = housenbr.lower()
            diffres = difflib.get_close_matches(housenbr, totre[exactmatches[house[1]]].keys(), freq, ratio)
            if len(diffres) == 1:
                print(housenbr + ': ' + diffres[0])
                notcompleted = False
                while notcompleted == False:
                    inp = input('Correct? y/n ')
                    if inp == 'y':
                        notcompleted = True
                        try: 
                            flatsnumber = getnumberofflats(totre[exactmatches[house[1]]]['streetname'], totre[exactmatches[house[1]]][diffres[0]])
                            insertion = cur.execute('UPDATE houses SET flats = ? WHERE id = ?', [flatsnumber, house[0]])
                        except:
                            print('weird, no flat number for ' + str(house))
                    elif inp == 'n':
                        notcompleted = True
                    else:
                        print('Incorrect input, try again')
            elif len(diffres) > 1:
                print(housenbr + ': ' + str(diffres))
                notcompleted = False
                while notcompleted == False:
                    inp = input('List number? Or n ')
                    try:
                        listnum = int(inp)
                    except:
                        listnum = None
                    if inp == 'n':
                        notcompleted = True
                    elif listnum in range(0, len(diffres)):
                        notcompleted = True
                        try:
                            flatsnumber = getnumberofflats(totre[exactmatches[house[1]]]['streetname'], totre[exactmatches[house[1]]][diffres[0]])
                            insertion = cur.execute('UPDATE houses SET flats = ? WHERE id = ?', [flatsnumber, house[0]])
                        except:
                            print('weird, no flat number for ' + str(house))
                    else:
                        print('Incorrect input, try again')

conn.commit()
conn.close()

Продолжаем развлекаться. Главное — не забывать закоммитить изменения после каждого прогона функции. Я не стал добавлять коннект и коммит в сами функции, чтобы не теребить постоянно базу данных.

Да, это довольно утомительно. Но, опять же, если отдать всё на откуп диффлибу — будут дурацкие ошибки, как на скриншоте, где он считает «35б» ближе к «35», чем к «35 'б'». Это, конечно, не ошибка диффлиба, но, если честно, я потратил бы больше времени на поск идеального запроса, а потом бы всё равно где-нибудь споткнулся. Лучшее с подтверждением пользователя, увереннее.

Итого: количество квартир есть для 3592 из примерно 4500 домов! Отличный результат (сам себя не похвалишь — никто не похвалит). Но, конечно, если подтверждать так много совпадений — будут ошибки.

Уберём 43 квартиры, в которых проголосовало больше квартир, чем квартир вроде бы есть всего. Ясно, что там либо ошибся я при подтверждении совпадений, либо были заведомо неверные данные.

Ну а с оставшимися уже можно развлекаться. Явка очевидно считается как количество проголосовавших, поделённое на количество квартир. Графики тоже рисуются очевидно, единственное, для каждой точки данных по явке нет смысла рисовать график, т.к. он будет похож на заштрихованную бумагу, лучше округлить данные по явке и брать средние данные по результатам для этой точки данных.

Округляем до ближайшего целого:

Округляем до ближайшего, кратного пяти:

В общем и целом, за исключением пары антипиков в районе 80% явки, или, если смотреть с округлением пожиже, в районах 30%, 40% и 80%, в целом, явка на результат не влияла. Разве что 100%-ая явка удивительным образом всегда давала 100%-й результат. А в среднем явка была 58,7%.

Стоило ли оно того? Для меня да, узнал много нового. А для читателя? Ну, читателю я выкладываю саму базу данных.

Может быть, вы сможете сделать с этими данными что-то поинтереснее.