Getting data of an election

31 minute read

Getting data of an election

image.png

On the 26th of May, we had the european parliamentary elections. In Romania, the results and progress of the vote were published online in real time on the official electoral site.

As far as I know it’s the first time we had such data exposed to the public, and with such granularity.

Since my daily work involves working closely with data, I couldn’t miss the oportunity to get my hands on that dataset. Of course, the site is not documented and there aren’t any publicly available API’s to begin with. So I spent some hours debugging the underlying stack to see how I can query it and compile it in a usable format.

As far as I see it’s built with React, using some NoSQL as the backend. I’m betting on NoSQL because, while doing the ETL I’ve found some schema inconsistencies that shouldn’t normally happen if the data sat on top of a SQL DB.

Understanding the API

Maybe there’s better way to do this, but what I did was start the developer console of the browser, refresh the election page and look for a request that seemed to contain the data that I was looking for. Using this approach I’ve found the following endopoints that I could query.

An example of how you interogate the BEC site, for the presence data. You need to know the county (in this case AR, code for ARAD).

!curl 'https://prezenta.bec.ro/europarlamentare26052019/data/pv/json//pv_AR.json' -H 'accept: */*' -H 'referer: https://prezenta.bec.ro/europarlamentare26052019/romania-pv-part' -H 'authority: prezenta.bec.ro' --compressed -o "_data/AR.json"
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 73871    0 73871    0     0   319k      0 --:--:-- --:--:-- --:--:--  319k

There is also an endpoint publishing the presence count (the number of people that voted so far ). Again, we also need to query this for each county.

!curl 'https://prezenta.bec.ro/europarlamentare26052019/data/presence/json/presence_AR_now.json' -H 'accept: */*' -H 'referer: https://prezenta.bec.ro/europarlamentare26052019/romania-precincts' -H 'authority: prezenta.bec.ro' --compressed -o "_data/AR-presence.json"
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 60786    0 60786    0     0   690k      0 --:--:-- --:--:-- --:--:--  690k

There is also another csv that we can use, which contains the presence data in a single big file.

!curl 'https://prezenta.bec.ro/europarlamentare26052019/data/presence/csv/presence_now.csv' -H 'Referer: https://prezenta.bec.ro/europarlamentare26052019/abroad-pv-part' --compressed -o "_data/all_presence.csv"

Fetching the data

Getting all the data ouf of the site. Each county has a dedicated page which contains information about its stats. By looking over the source of the site we can compile a list of all counties that we need to inspect. It’s instersting that the S1..S6 (Bucharest’s sectors) were modeled as counties.

counties = ["AR", "AB", "AR", "AG", "BC", "BH", "BN", "BT", "BV", "BR", "BZ", "CS", "CL", "CJ", "CT", "CV", "DB", "DJ", "GL", "GR", "GJ", "HR", "HD", "IL", "IS", "IF", "MM", "MH", "MS", "NT", "OT", "PH", "SM", "SJ", "SB", "SV", "TR", "TM", "TL", "VS", "VL", "VN", "B", "SR", "S1", "S2", "S3", "S4", "S5", "S6"]
len(counties)

The vote information is stored on the data/pv/json/ route, specific for each county. In order not to make multiple queries while testing, we first cache all the results localy and we can refer to them later on.

Above we’ve deduced the counties we can have, but I’ve found that there are slight asymetries for certain cases (mostly regarding the expat data and the way Bucharest is represented).

It’s because of this that we need to handle the counties list in a case-by-case fashion.

import json
from tqdm import tqdm_notebook as tqdm
counties = ["AR", "AB", "AR", "AG", "BC", "BH", "BN", "BT", "BV", "BR", "BZ", "CS", "CL", "CJ", "CT", "CV", "DB", "DJ", "GL", "GR", "GJ", "HR", "HD", "IL", "IS", "IF", "MM", "MH", "MS", "NT", "OT", "PH", "SM", "SJ", "SB", "SV", "TR", "TM", "TL", "VS", "VL", "VN", "S1", "S2", "S3", "S4", "S5", "S6"]
for county in tqdm(counties):
    !curl 'https://prezenta.bec.ro/europarlamentare26052019/data/pv/json//pv_{county}.json' -H 'accept-encoding: gzip, deflate, br'  -H 'accept: */*' -H 'referer: https://prezenta.bec.ro/europarlamentare26052019/romania-pv-part' -H 'authority: prezenta.bec.ro' --compressed -o "_data/{county}.json"

The presence data is (as above) stored in a different (data/presence/json/) route specific to each county. Again, we fetch everything an cache localy.

counties = ["AR", "AB", "AR", "AG", "BC", "BH", "BN", "BT", "BV", "BR", "BZ", "CS", "CL", "CJ", "CT", "CV", "DB", "DJ", "GL", "GR", "GJ", "HR", "HD", "IL", "IS", "IF", "MM", "MH", "MS", "NT", "OT", "PH", "SM", "SJ", "SB", "SV", "TR", "TM", "TL", "VS", "VL", "VN", "B", "SR"]
for county in tqdm(counties[-8:-6]):
    !curl 'https://prezenta.bec.ro/europarlamentare26052019/data/presence/json/presence_{county}_now.json' -H 'accept: */*' -H 'referer: https://prezenta.bec.ro/europarlamentare26052019/romania-precincts' -H 'authority: prezenta.bec.ro' --compressed -o "_data/{county}-presence.json"

Let’s also get the all in one data about the presence.

!curl 'https://prezenta.bec.ro/europarlamentare26052019/data/presence/csv/presence_now.csv' -H 'Referer: https://prezenta.bec.ro/europarlamentare26052019/abroad-pv-part' --compressed -o "_data/all_presence.csv"
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 2618k    0 2618k    0     0  5729k      0 --:--:-- --:--:-- --:--:-- 5729k

Compiling the data

Loading a presence file

When reading the presence file, there’s some manipulation that we need to do because the original returned json contains lots of information that seemed either useless or redundant (info we already had in other places), or information that I didn’t know how to interpret.

There was also the age_ranges field which was contained actually a list of values, that I needed to exapend into individual columns, by using a transform function.

def process_row(row):
    return tuple(row.age_ranges.values())

def load_presence(presence_file_name):
    _json = read_json_file(presence_file_name)
    _df = pd.DataFrame.from_records(_json['precinct'])
    _df[["men_18_24", "men_25_34", "men_35_44", "men_45_64", "men_65+", "women_18_24", "women_25_34", "women_35_44", "women_45_64", "women_65+"]] = _df.apply(process_row, axis=1, result_type='expand')
    _df.drop(columns=['age_ranges'], inplace=True)
    _df.columns = [
        'liste_permanente', 
        'lista_suplimentare', 
        'total', 
        'urna_mobila', 
        'county_code',
        'county_name',
        'id_county',
        'id_locality',
        'id_precinct',
        'id_uat',
        'initial_count',
        'latitude',
        'locality_name',
        'longitude',
        'medium',
        'precinct_name',
        'precinct_nr',
        'presence',
        'siruta',
        'uat_code',
        'uat_name',
        'men_18_24',
        'men_25_34',
        'men_35_44',
        'men_45_64',
        'men_65+',
        'women_18_24',
        'women_25_34',
        'women_35_44',
        'women_45_64',
        'women_65+',
    ]
    return _df

tulcea = load_presence("_data/TL-presence.json")
tulcea.head()
liste_permanente lista_suplimentare total urna_mobila county_code county_name id_county id_locality id_precinct id_uat ... men_18_24 men_25_34 men_35_44 men_45_64 men_65+ women_18_24 women_25_34 women_35_44 women_45_64 women_65+
0 409 31 440 0 TL TULCEA 38 8884 15848 2882 ... 13 23 36 84 29 17 22 55 110 51
1 471 73 544 0 TL TULCEA 38 8884 15849 2882 ... 11 28 55 90 71 10 40 62 92 85
2 14 20 34 0 TL TULCEA 38 8909 15943 2892 ... 1 1 5 10 4 0 2 4 5 2
3 134 35 169 0 TL TULCEA 38 8914 15948 2894 ... 5 6 17 37 21 4 9 7 37 26
4 638 70 708 0 TL TULCEA 38 8912 15946 2894 ... 19 42 46 185 78 17 42 53 142 84

5 rows × 31 columns

Getting all the individual result files for all precint_nr

The voting results (what voted for what, in which place) are stored in the data/pv route. The problem is that if we only use the county file, downloaded in step 3, there isn’t any keys that will link the results to the presence rows. To be more exact, the results are listed per voting facilty, whereas the presence is aggregated at a locality level. This means that we can’t really jon the two.

Fortuantely, I’ve found that if you know the id of a specific voting facilty, you could ask for the resuls of that specific facility through a version of the original results API.

So the final strategy that worked was something along the following lines:

  • For all countyes, we will load the presence file associated to it
  • inspect all the precint_nr that is contains
  • individually query the api for the results of that precint_nr.
counties = ["AR", "AB", "AR", "AG", "BC", "BH", "BN", "BT", "BV", "BR", "BZ", "CS", "CL", "CJ", "CT", "CV", "DB", "DJ", "GL", "GR", "GJ", "HR", "HD", "IL", "IS", "IF", "MM", "MH", "MS", "NT", "OT", "PH", "SM", "SJ", "SB", "SV", "TR", "TM", "TL", "VS", "VL", "VN", "SR"]
for county in tqdm(counties):
    df_county = load_presence(f"_data/{county}-presence.json")
    for precinct_nr in tqdm(df_county['precinct_nr'].values, leave=False):
        file_name = f"_data/{county}_results_{precinct_nr}.json"
        if not os.path.exists(file_name): 
            !curl 'https://prezenta.bec.ro/europarlamentare26052019/data/pv/csv/pv_{county}_{precinct_nr}_EUP_PART.csv' -H 'accept-encoding: gzip, deflate, br' -H 'accept-language: en-GB,en-US;q=0.9,en;q=0.8' -H 'user-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36' -H 'accept: */*' -H 'referer: https://prezenta.bec.ro/europarlamentare26052019/romania-pv-part' -H 'authority: prezenta.bec.ro' -H 'cookie: _ga=GA1.2.772980748.1558943895; _gid=GA1.2.1466959792.1561374632' --compressed --silent -o "_data/{county}_results_{precinct_nr}.json"
        else:
            with open(file_name) as f:
                file_contents = f.read()
            if "Cod birou electoral" not in file_contents:
                print(f"File: {file_name} has bad content {file_contents[:50]}. Will retry")
                os.remove(file_name)
                !curl 'https://prezenta.bec.ro/europarlamentare26052019/data/pv/csv/pv_{county}_{precinct_nr}_EUP_PART.csv' -H 'accept-encoding: gzip, deflate, br' -H 'accept-language: en-GB,en-US;q=0.9,en;q=0.8' -H 'user-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36' -H 'accept: */*' -H 'referer: https://prezenta.bec.ro/europarlamentare26052019/romania-pv-part' -H 'authority: prezenta.bec.ro' -H 'cookie: _ga=GA1.2.772980748.1558943895; _gid=GA1.2.1466959792.1561374632' --compressed --silent -o "_data/{county}_results_{precinct_nr}.json"

Bucharest is a special case. It’s treated as a county but the results are stored by sectors so we need to do things a bit different.

county = "B"
df_county = load_presence(f"_data/{county}-presence.json")
for id_sector in tqdm(df_county.id_locality.unique()):
    sector = f"S{int(id_sector) - 9910 + 1}"
    print(f"Processing: {sector}")
    county = sector
    for precinct_nr in tqdm(df_county[df_county.id_locality == id_sector]['precinct_nr'].values, leave=False):
        file_name = f"_data/{county}_results_{precinct_nr}.json"
        if not os.path.exists(file_name): 
            !curl 'https://prezenta.bec.ro/europarlamentare26052019/data/pv/csv/pv_{county}_{precinct_nr}_EUP_PART.csv' -H 'accept-encoding: gzip, deflate, br' -H 'accept-language: en-GB,en-US;q=0.9,en;q=0.8' -H 'user-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36' -H 'accept: */*' -H 'referer: https://prezenta.bec.ro/europarlamentare26052019/romania-pv-part' -H 'authority: prezenta.bec.ro' -H 'cookie: _ga=GA1.2.772980748.1558943895; _gid=GA1.2.1466959792.1561374632' --compressed --silent -o "_data/{county}_results_{precinct_nr}.json"
        else:
            with open(file_name) as f:
                file_contents = f.read()
            if "Cod birou electoral" not in file_contents:
                print(f"File: {file_name} has bad content {file_contents[:50]}. Will retry")
                os.remove(file_name)
                !curl 'https://prezenta.bec.ro/europarlamentare26052019/data/pv/csv/pv_{county}_{precinct_nr}_EUP_PART.csv' -H 'accept-encoding: gzip, deflate, br' -H 'accept-language: en-GB,en-US;q=0.9,en;q=0.8' -H 'user-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36' -H 'accept: */*' -H 'referer: https://prezenta.bec.ro/europarlamentare26052019/romania-pv-part' -H 'authority: prezenta.bec.ro' -H 'cookie: _ga=GA1.2.772980748.1558943895; _gid=GA1.2.1466959792.1561374632' --compressed --silent -o "_data/{county}_results_{precinct_nr}.json"

In SR we have data about the foreign offices.

load_presence("_data/SR-presence.json").head().T
0 1 2 3 4
liste_permanente 0 0 0 0 0
lista_suplimentare 18 57 865 79 1330
total 18 57 865 79 1330
urna_mobila 0 0 0 0 0
county_code SR SR SR SR SR
county_name STRAINATATE STRAINATATE STRAINATATE STRAINATATE STRAINATATE
id_county 43 43 43 43 43
id_locality 10244 10178 10334 10206 9996
id_precinct 18619 18627 19096 18716 18723
id_uat 3230 3218 3245 3219 3186
initial_count 0 0 0 0 0
latitude None None None None None
locality_name KABUL BUENOS AIRES RENNES TBILISI STUTTGART
longitude None None None None None
medium U U U U U
precinct_name Eticheta Credentiale/Tableta - 1 Eticheta Credentiale/Tableta - 10 Eticheta Credentiale/Tableta - 100 Eticheta Credentiale/Tableta - 101 Eticheta Credentiale/Tableta - 102
precinct_nr 1 10 100 101 102
presence 0 0 0 0 0
siruta None None None None None
uat_code AF AR FR GE DE
uat_name AFGANISTAN ARGENTINA FRANȚA GEORGIA GERMANIA
men_18_24 0 1 50 1 71
men_25_34 4 3 155 9 258
men_35_44 11 4 172 18 270
men_45_64 3 7 141 14 249
men_65+ 0 1 8 1 4
women_18_24 0 0 37 1 50
women_25_34 0 10 119 13 182
women_35_44 0 13 93 11 128
women_45_64 0 11 76 9 112
women_65+ 0 7 14 2 6

Reading all the presence data into a single DataFrame

We now have all the presence data cached, and we’ll read it into a single dataframe.

counties = ["AR", "AB", "AR", "AG", "BC", "BH", "BN", "BT", "BV", "BR", "BZ", "CS", "CL", "CJ", "CT", "CV", "DB", "DJ", "GL", "GR", "GJ", "HR", "HD", "IL", "IS", "IF", "MM", "MH", "MS", "NT", "OT", "PH", "SM", "SJ", "SB", "SV", "TR", "TM", "TL", "VS", "VL", "VN", "SR", "B"]
 
df_precints = pd.concat((load_presence(f) for f in tqdm(glob("_data/*-presence.json"))), ignore_index=True)
df_precints.shape
HBox(children=(IntProgress(value=0, max=43), HTML(value='')))





(19171, 31)
df_precints.head().T
0 1 2 3 4
liste_permanente 696 140 501 571 680
lista_suplimentare 63 10 25 41 55
total 759 150 526 612 736
urna_mobila 0 0 0 0 1
county_code VS VS VS VS VS
county_name VASLUI VASLUI VASLUI VASLUI VASLUI
id_county 39 39 39 39 39
id_locality 9015 9015 9006 9006 9006
id_precinct 16128 16187 16086 16087 16088
id_uat 2936 2936 2933 2933 2933
initial_count 1470 1840 1354 1375 1570
latitude 46.6389853639550 46.6421141774663 46.2240238056566 46.2278431009305 46.2278431009305
locality_name VASLUI VASLUI BÂRLAD BÂRLAD BÂRLAD
longitude 27.7326775437114 27.7289502189002 27.6775710052581 27.6686353095150 27.6686353095150
medium U U U U U
precinct_name CASA DE CULTURĂ A SINDICATELOR ”CONSTANTIN TĂN... ȘCOALA GIMNAZIALĂ ”CONSTANTIN PARFENE” ŞCOALA GIMNAZIALĂ ”VICTOR IOAN POPA” CASA DE CULTURĂ A SINDICATELOR ”GEORGE TUTOVEANU” CASA DE CULTURĂ A SINDICATELOR ”GEORGE TUTOVEANU”
precinct_nr 1 10 100 101 102
presence 51.6327 8.1522 38.8479 44.5091 46.879
siruta 161954 161954 161801 161801 161801
uat_code None None None None None
uat_name MUNICIPIUL VASLUI MUNICIPIUL VASLUI MUNICIPIUL BÂRLAD MUNICIPIUL BÂRLAD MUNICIPIUL BÂRLAD
men_18_24 13 4 18 15 11
men_25_34 31 5 25 29 40
men_35_44 66 17 60 52 60
men_45_64 110 28 104 113 158
men_65+ 115 20 43 72 81
women_18_24 22 6 17 22 14
women_25_34 34 8 33 32 46
women_35_44 78 15 64 55 52
women_45_64 171 28 117 127 178
women_65+ 119 19 45 95 96

The all_presence.csv file contains information about age groups, more granular than the bucketed info found in the county files. We will merge it with the current dataframe.

_all_df = pd.read_csv("_data/all_presence.csv")
_all_df.head().T
0 1 2 3 4
Judet AB AB AB AB AB
UAT MUNICIPIUL ALBA IULIA MUNICIPIUL ALBA IULIA MUNICIPIUL SEBEŞ MUNICIPIUL SEBEŞ MUNICIPIUL SEBEŞ
Localitate ALBA IULIA ALBA IULIA SEBEŞ SEBEŞ SEBEŞ
Siruta 1026 1026 1883 1883 1883
Nr sectie de votare 1 10 100 101 102
Nume sectie de votare CENTRUL DE ZI PENTRU PERSOANE VÂRSTNICE COLEGIUL NAŢIONAL „HOREA CLOŞCA ŞI CRIŞAN” ŞCOALA GIMNAZIALĂ NR. 2 SEBEŞ COLEGIUL NAŢIONAL ”LUCIAN BLAGA” SEBEŞ COLEGIUL NAŢIONAL ”LUCIAN BLAGA” SEBEŞ
Mediu U U U U U
Votanti lista 1612 1443 1415 1303 1362
LP 901 648 769 697 765
LS 45 143 66 73 27
UM 0 0 14 42 0
LT 946 791 849 812 792
Barbati 18-24 26 38 34 24 31
Barbati 25-34 58 68 56 56 35
Barbati 35-44 88 69 84 70 82
Barbati 45-64 165 128 157 136 136
Barbati 65+ 102 71 88 78 58
Femei 18-24 32 40 30 30 35
Femei 25-34 72 56 61 61 48
Femei 35-44 107 79 76 92 122
Femei 45-64 178 161 163 167 160
Femei 65+ 118 81 100 98 85
Barbati 18 7 9 5 4 3
Barbati 19 4 4 4 6 5
Barbati 20 5 7 6 2 9
Barbati 21 1 2 2 2 5
Barbati 22 2 7 7 1 7
Barbati 23 5 4 4 4 0
Barbati 24 2 5 6 5 2
Barbati 25 5 9 2 2 1
... ... ... ... ... ...
Femei 91 0 0 0 0 0
Femei 92 1 0 0 0 0
Femei 93 0 0 0 0 0
Femei 94 0 0 0 0 0
Femei 95 0 0 0 0 0
Femei 96 0 0 0 0 0
Femei 97 0 0 0 0 0
Femei 98 0 0 0 0 0
Femei 99 0 0 0 0 0
Femei 100 0 0 0 0 0
Femei 101 0 0 0 0 0
Femei 102 0 0 0 0 0
Femei 103 0 0 0 0 0
Femei 104 0 0 0 0 0
Femei 105 0 0 0 0 0
Femei 106 0 0 0 0 0
Femei 107 0 0 0 0 0
Femei 108 0 0 0 0 0
Femei 109 0 0 0 0 0
Femei 110 0 0 0 0 0
Femei 111 0 0 0 0 0
Femei 112 0 0 0 0 0
Femei 113 0 0 0 0 0
Femei 114 0 0 0 0 0
Femei 115 0 0 0 0 0
Femei 116 0 0 0 0 0
Femei 117 0 0 0 0 0
Femei 118 0 0 0 0 0
Femei 119 0 0 0 0 0
Femei 120 0 0 0 0 0

228 rows × 5 columns

It’s interesting that this file contains presence data on a per year-of-birth grouping (which is more granular than the 10 years buckets we had prior).

Reading all the results data into a single dataframe.

The individual results files we’ve got from two steps above, we will load them into a single big pandas DataFrame

df_results = pd.concat((pd.read_csv(f) for f in tqdm(glob("_data/*_results_*"))), ignore_index=True)
df_results.shape
(19171, 36)
df_results.head().T
0 1 2 3 4
Cod birou electoral 22 5 35 35 6
Județ HUNEDOARA BIHOR SUCEAVA SUCEAVA BISTRIŢA-NĂSĂUD
Uat ORAŞ CĂLAN CEICA VICOVU DE JOS MUNICIPIUL SUCEAVA BUDEŞTI
Localitate CĂLAN BUCIUM VICOVU DE JOS SUCEAVA BUDEŞTI-FÂNAŢE
Secție ŞCOALA GIMNAZIALĂ ȘCOALA BUCIUM SCOALA CU CLASELE I-VIII IOAN VICOVEANU GRĂDINIŢA CU PROGRAM NORMAL NR.7 ŞCOALA PRIMARĂ BUDEŞTI-FÎNAŢE
Nr 190 320 532 61 97
Tip Europarlamentare Europarlamentare Europarlamentare Europarlamentare Europarlamentare
a 1471 172 1344 1393 256
a1 1471 172 1344 1393 256
a2 0 0 0 0 0
b 534 116 520 625 162
b1 505 88 479 560 141
b2 0 0 0 0 0
b3 29 28 41 65 21
c 1600 188 1500 1500 300
d 1066 72 980 875 138
e 514 113 504 605 153
f 20 3 16 20 9
h 0 0 NU ESTE CAZUL NU ESTE CAZUL 0
i 0 0 FOARTE BUNA FOARTE BUNA 0
g1 111 46 174 128 18
g2 86 14 51 126 29
g3 29 5 34 43 7
g4 15 0 0 2 1
g5 176 28 162 153 62
g6 18 4 19 27 10
g7 2 3 2 6 1
g8 25 5 29 55 11
g9 6 0 2 3 3
g10 3 0 3 1 2
g11 3 0 3 2 2
g12 1 0 2 3 2
g13 2 0 0 1 0
g14 18 0 4 21 3
g15 9 2 9 12 1
g16 10 6 10 22 1

Joining the results with the presence data

Some code cleanup are neede. In order to join the two dataframes we need to make slight conversions to make all the keys from both side match.

df_results.loc[df_results.Județ == "STRĂINĂTATE", "Județ"] = "STRAINATATE"
df_results.loc[df_results.Uat == "OMAN", "Uat"] = "SULTANATUL OMAN"
df_results.loc[df_results.Județ == "SECTOR 1", "Județ"] = "MUNICIPIUL BUCUREŞTI"
df_results.loc[df_results.Județ == "SECTOR 2", "Județ"] = "MUNICIPIUL BUCUREŞTI"
df_results.loc[df_results.Județ == "SECTOR 3", "Județ"] = "MUNICIPIUL BUCUREŞTI"
df_results.loc[df_results.Județ == "SECTOR 4", "Județ"] = "MUNICIPIUL BUCUREŞTI"
df_results.loc[df_results.Județ == "SECTOR 5", "Județ"] = "MUNICIPIUL BUCUREŞTI"
df_results.loc[df_results.Județ == "SECTOR 6", "Județ"] = "MUNICIPIUL BUCUREŞTI"

Now, if we merge the two we will get a single big dataframe with the same number of rows but double the columns.

df_precint_with_results = pd.merge(left=df_precints, right=df_results, left_on=["county_name", "uat_name", "precinct_nr"], right_on=["Județ", "Uat", "Nr"])
df_precint_with_results.shape
(19171, 67)

Let’s print one example of how one entry this looks like in practice.

dict(df_precint_with_results.iloc[0])
{'liste_permanente': 696,
 'lista_suplimentare': 63,
 'total': 759,
 'urna_mobila': 0,
 'county_code': 'VS',
 'county_name': 'VASLUI',
 'id_county': '39',
 'id_locality': '9015',
 'id_precinct': '16128',
 'id_uat': '2936',
 'initial_count': 1470,
 'latitude': '46.6389853639550',
 'locality_name': 'VASLUI',
 'longitude': '27.7326775437114',
 'medium': 'U',
 'precinct_name': 'CASA DE CULTURĂ A SINDICATELOR ”CONSTANTIN TĂNASE”',
 'precinct_nr': 1,
 'presence': 51.6327,
 'siruta': '161954',
 'uat_code': None,
 'uat_name': 'MUNICIPIUL VASLUI',
 'men_18_24': 13,
 'men_25_34': 31,
 'men_35_44': 66,
 'men_45_64': 110,
 'men_65+': 115,
 'women_18_24': 22,
 'women_25_34': 34,
 'women_35_44': 78,
 'women_45_64': 171,
 'women_65+': 119,
 'Cod birou electoral': 39,
 'Județ': 'VASLUI',
 'Uat': 'MUNICIPIUL VASLUI',
 'Localitate': 'VASLUI',
 'Secție': 'CASA DE CULTURĂ A SINDICATELOR ”CONSTANTIN TĂNASE”',
 'Nr': 1,
 'Tip': 'Europarlamentare',
 'a': 1470,
 'a1': 1470,
 'a2': 0,
 'b': 759,
 'b1': 695,
 'b2': 0,
 'b3': 64,
 'c': 1500,
 'd': 741,
 'e': 741,
 'f': 18,
 'h': 0,
 'i': 0,
 'g1': 185,
 'g2': 232,
 'g3': 51,
 'g4': 0,
 'g5': 118,
 'g6': 37,
 'g7': 2,
 'g8': 68,
 'g9': 0,
 'g10': 4,
 'g11': 5,
 'g12': 3,
 'g13': 2,
 'g14': 18,
 'g15': 9,
 'g16': 7}

We will also join the data with the all_presence.csv file.

df_full = pd.merge(left=df_precint_with_results, right=_all_df, left_on=["county_code", "uat_name", "precinct_nr"], right_on=["Judet", "UAT", "Nr sectie de votare"])
df_full.shape
(19171, 295)

Applying the legend

Some of the columns in the above dataframe are not quite obvious (g1, .., g16, etc..). These are party names that I was only able to find in a legend in the dropdown of a button in the UI of the site. I’ve copied it here, along with explanations of some fields that I’ve been able to figure out by looking over the PDF’s of official scanned documents.

We also need to convert these column names into more meaningfull lables.

df_full.rename(columns={
    "g1": "PSD",
    "g2": "USR-PLUS",
    "g3": "PRO Romania",
    "g4": "UDMR",
    "g5": "PNL",
    "g6": "ALDE",
    "g7": "PRODEMO",
    "g8": "PMP",
    "g9": "Partidul Socialist Roman",
    "g10": "Partidul Social Democrat Independent",
    "g11": "Partidul Romania Unita",
    "g12": "Uniunea Nationala Pentur Progresul Romaniei",
    "g13": "Blocul Unitatii Nationale",
    "g14": "Gregoriana-Carmen Tudoran",
    "g15": "George-Nicaolae Simion",
    "g16": "Peter Costea",
    "a": "Total alegatori",
    "a1": "Total lista permanenta",
    "a2": "Total urna mobila",
    "b": "Total prezenti",
    "b1": "Prezenti lista permanenta",
    "b2": "Prezenti urna mobila",
    "b3": "Prezenti lista suplimentara",
    "c": "Total voturi",
    "d": "Voturi nefolosite",
    "e": "Voturi valabile",
    "f": "Voturi anulate",
    "h": "Contestatii",
    "i": "Starea sigiliilor"
}, inplace=True)

Ok, let’s check for the amount of missing data

na_series = df_full.isna().sum()
na_series[na_series != 0]
latitude               479
longitude              479
siruta                 441
uat_code             18730
Contestatii             19
Starea sigiliilor        8
Siruta                 441
dtype: int64

Removing duplicate columns

Because we’ve basically merged two types of presence datasets (the per-county one and the all_presence.csv one) we ended up with some duplicate columns in the joined dataframe. We also have as duplicates the join on columns, and columns that contained the same type of information.

We want to eliminate those. We will find the duplicated columns by:

  • using the pandas.duplicated method (used on the transposed matix - duplicated only works on rows)
  • looking at the correlation matrix of the resulting columns and get the pairs of columns that have the highes correlation.
duplicate_columns = df_full.columns[df_full.T.duplicated(keep=False)]
duplicate_columns
Index(['liste_permanente', 'lista_suplimentare', 'total', 'urna_mobila',
       'county_code', 'county_name', 'initial_count', 'locality_name',
       'medium', 'precinct_name', 'precinct_nr', 'uat_name', 'men_18_24',
       'men_25_34', 'men_35_44', 'men_45_64', 'men_65+', 'women_18_24',
       'women_25_34', 'women_35_44', 'women_45_64', 'women_65+', 'Județ',
       'Uat', 'Nr', 'Judet', 'UAT', 'Localitate_y', 'Nr sectie de votare',
       'Nume sectie de votare', 'Mediu', 'Votanti lista', 'LP', 'LS', 'UM',
       'LT', 'Barbati 18-24', 'Barbati 25-34', 'Barbati 35-44',
       'Barbati 45-64', 'Barbati 65+', 'Femei 18-24', 'Femei 25-34',
       'Femei 35-44', 'Femei 45-64', 'Femei 65+', 'Barbati 104', 'Barbati 106',
       'Barbati 108', 'Barbati 109', 'Barbati 110', 'Barbati 112',
       'Barbati 113', 'Barbati 114', 'Barbati 115', 'Barbati 116',
       'Barbati 117', 'Barbati 118', 'Barbati 119', 'Barbati 120', 'Femei 105',
       'Femei 106', 'Femei 107', 'Femei 108', 'Femei 110', 'Femei 111',
       'Femei 112', 'Femei 113', 'Femei 114', 'Femei 115', 'Femei 116',
       'Femei 117', 'Femei 118', 'Femei 119', 'Femei 120'],
      dtype='object')

With these, we will compare each with each and see what searies are equals. This will results in a long list of pairs of columns that are duplicates of one another.

_pairs = set()
for i, _a in enumerate(duplicate_columns):
    for _b in duplicate_columns[i+1:]:
        if (df_full[_a] == df_full[_b]).all():
            _pairs.add(tuple(sorted([_a, _b])))
_pairs
{('Barbati 104', 'Barbati 106'),
 ('Barbati 104', 'Barbati 108'),
 ('Barbati 104', 'Barbati 109'),
 ('Barbati 104', 'Barbati 110'),
 ('Barbati 104', 'Barbati 112'),
 ('Barbati 104', 'Barbati 113'),
 ('Barbati 104', 'Barbati 114'),
 ('Barbati 104', 'Barbati 115'),
 ('Barbati 104', 'Barbati 116'),
 ('Barbati 104', 'Barbati 117'),
 ('Barbati 104', 'Barbati 118'),
 ('Barbati 104', 'Barbati 119'),
 ('Barbati 104', 'Barbati 120'),
 ('Barbati 104', 'Femei 105'),
 ('Barbati 104', 'Femei 106'),
 ('Barbati 104', 'Femei 107'),
 ('Barbati 104', 'Femei 108'),
 ('Barbati 104', 'Femei 110'),
 ('Barbati 104', 'Femei 111'),
 ('Barbati 104', 'Femei 112'),
 ('Barbati 104', 'Femei 113'),
 ('Barbati 104', 'Femei 114'),
 ('Barbati 104', 'Femei 115'),
 ('Barbati 104', 'Femei 116'),
 ('Barbati 104', 'Femei 117'),
 ('Barbati 104', 'Femei 118'),
 ('Barbati 104', 'Femei 119'),
 ('Barbati 104', 'Femei 120'),
 ('Barbati 106', 'Barbati 108'),
 ('Barbati 106', 'Barbati 109'),
 ('Barbati 106', 'Barbati 110'),
 ('Barbati 106', 'Barbati 112'),
 ('Barbati 106', 'Barbati 113'),
 ('Barbati 106', 'Barbati 114'),
 ('Barbati 106', 'Barbati 115'),
 ('Barbati 106', 'Barbati 116'),
 ('Barbati 106', 'Barbati 117'),
 ('Barbati 106', 'Barbati 118'),
 ('Barbati 106', 'Barbati 119'),
 ('Barbati 106', 'Barbati 120'),
 ('Barbati 106', 'Femei 105'),
 ('Barbati 106', 'Femei 106'),
 ('Barbati 106', 'Femei 107'),
 ('Barbati 106', 'Femei 108'),
 ('Barbati 106', 'Femei 110'),
 ('Barbati 106', 'Femei 111'),
 ('Barbati 106', 'Femei 112'),
 ('Barbati 106', 'Femei 113'),
 ('Barbati 106', 'Femei 114'),
 ('Barbati 106', 'Femei 115'),
 ('Barbati 106', 'Femei 116'),
 ('Barbati 106', 'Femei 117'),
 ('Barbati 106', 'Femei 118'),
 ('Barbati 106', 'Femei 119'),
 ('Barbati 106', 'Femei 120'),
 ('Barbati 108', 'Barbati 109'),
 ('Barbati 108', 'Barbati 110'),
 ('Barbati 108', 'Barbati 112'),
 ('Barbati 108', 'Barbati 113'),
 ('Barbati 108', 'Barbati 114'),
 ('Barbati 108', 'Barbati 115'),
 ('Barbati 108', 'Barbati 116'),
 ('Barbati 108', 'Barbati 117'),
 ('Barbati 108', 'Barbati 118'),
 ('Barbati 108', 'Barbati 119'),
 ('Barbati 108', 'Barbati 120'),
 ('Barbati 108', 'Femei 105'),
 ('Barbati 108', 'Femei 106'),
 ('Barbati 108', 'Femei 107'),
 ('Barbati 108', 'Femei 108'),
 ('Barbati 108', 'Femei 110'),
 ('Barbati 108', 'Femei 111'),
 ('Barbati 108', 'Femei 112'),
 ('Barbati 108', 'Femei 113'),
 ('Barbati 108', 'Femei 114'),
 ('Barbati 108', 'Femei 115'),
 ('Barbati 108', 'Femei 116'),
 ('Barbati 108', 'Femei 117'),
 ('Barbati 108', 'Femei 118'),
 ('Barbati 108', 'Femei 119'),
 ('Barbati 108', 'Femei 120'),
 ('Barbati 109', 'Barbati 110'),
 ('Barbati 109', 'Barbati 112'),
 ('Barbati 109', 'Barbati 113'),
 ('Barbati 109', 'Barbati 114'),
 ('Barbati 109', 'Barbati 115'),
 ('Barbati 109', 'Barbati 116'),
 ('Barbati 109', 'Barbati 117'),
 ('Barbati 109', 'Barbati 118'),
 ('Barbati 109', 'Barbati 119'),
 ('Barbati 109', 'Barbati 120'),
 ('Barbati 109', 'Femei 105'),
 ('Barbati 109', 'Femei 106'),
 ('Barbati 109', 'Femei 107'),
 ('Barbati 109', 'Femei 108'),
 ('Barbati 109', 'Femei 110'),
 ('Barbati 109', 'Femei 111'),
 ('Barbati 109', 'Femei 112'),
 ('Barbati 109', 'Femei 113'),
 ('Barbati 109', 'Femei 114'),
 ('Barbati 109', 'Femei 115'),
 ('Barbati 109', 'Femei 116'),
 ('Barbati 109', 'Femei 117'),
 ('Barbati 109', 'Femei 118'),
 ('Barbati 109', 'Femei 119'),
 ('Barbati 109', 'Femei 120'),
 ('Barbati 110', 'Barbati 112'),
 ('Barbati 110', 'Barbati 113'),
 ('Barbati 110', 'Barbati 114'),
 ('Barbati 110', 'Barbati 115'),
 ('Barbati 110', 'Barbati 116'),
 ('Barbati 110', 'Barbati 117'),
 ('Barbati 110', 'Barbati 118'),
 ('Barbati 110', 'Barbati 119'),
 ('Barbati 110', 'Barbati 120'),
 ('Barbati 110', 'Femei 105'),
 ('Barbati 110', 'Femei 106'),
 ('Barbati 110', 'Femei 107'),
 ('Barbati 110', 'Femei 108'),
 ('Barbati 110', 'Femei 110'),
 ('Barbati 110', 'Femei 111'),
 ('Barbati 110', 'Femei 112'),
 ('Barbati 110', 'Femei 113'),
 ('Barbati 110', 'Femei 114'),
 ('Barbati 110', 'Femei 115'),
 ('Barbati 110', 'Femei 116'),
 ('Barbati 110', 'Femei 117'),
 ('Barbati 110', 'Femei 118'),
 ('Barbati 110', 'Femei 119'),
 ('Barbati 110', 'Femei 120'),
 ('Barbati 112', 'Barbati 113'),
 ('Barbati 112', 'Barbati 114'),
 ('Barbati 112', 'Barbati 115'),
 ('Barbati 112', 'Barbati 116'),
 ('Barbati 112', 'Barbati 117'),
 ('Barbati 112', 'Barbati 118'),
 ('Barbati 112', 'Barbati 119'),
 ('Barbati 112', 'Barbati 120'),
 ('Barbati 112', 'Femei 105'),
 ('Barbati 112', 'Femei 106'),
 ('Barbati 112', 'Femei 107'),
 ('Barbati 112', 'Femei 108'),
 ('Barbati 112', 'Femei 110'),
 ('Barbati 112', 'Femei 111'),
 ('Barbati 112', 'Femei 112'),
 ('Barbati 112', 'Femei 113'),
 ('Barbati 112', 'Femei 114'),
 ('Barbati 112', 'Femei 115'),
 ('Barbati 112', 'Femei 116'),
 ('Barbati 112', 'Femei 117'),
 ('Barbati 112', 'Femei 118'),
 ('Barbati 112', 'Femei 119'),
 ('Barbati 112', 'Femei 120'),
 ('Barbati 113', 'Barbati 114'),
 ('Barbati 113', 'Barbati 115'),
 ('Barbati 113', 'Barbati 116'),
 ('Barbati 113', 'Barbati 117'),
 ('Barbati 113', 'Barbati 118'),
 ('Barbati 113', 'Barbati 119'),
 ('Barbati 113', 'Barbati 120'),
 ('Barbati 113', 'Femei 105'),
 ('Barbati 113', 'Femei 106'),
 ('Barbati 113', 'Femei 107'),
 ('Barbati 113', 'Femei 108'),
 ('Barbati 113', 'Femei 110'),
 ('Barbati 113', 'Femei 111'),
 ('Barbati 113', 'Femei 112'),
 ('Barbati 113', 'Femei 113'),
 ('Barbati 113', 'Femei 114'),
 ('Barbati 113', 'Femei 115'),
 ('Barbati 113', 'Femei 116'),
 ('Barbati 113', 'Femei 117'),
 ('Barbati 113', 'Femei 118'),
 ('Barbati 113', 'Femei 119'),
 ('Barbati 113', 'Femei 120'),
 ('Barbati 114', 'Barbati 115'),
 ('Barbati 114', 'Barbati 116'),
 ('Barbati 114', 'Barbati 117'),
 ('Barbati 114', 'Barbati 118'),
 ('Barbati 114', 'Barbati 119'),
 ('Barbati 114', 'Barbati 120'),
 ('Barbati 114', 'Femei 105'),
 ('Barbati 114', 'Femei 106'),
 ('Barbati 114', 'Femei 107'),
 ('Barbati 114', 'Femei 108'),
 ('Barbati 114', 'Femei 110'),
 ('Barbati 114', 'Femei 111'),
 ('Barbati 114', 'Femei 112'),
 ('Barbati 114', 'Femei 113'),
 ('Barbati 114', 'Femei 114'),
 ('Barbati 114', 'Femei 115'),
 ('Barbati 114', 'Femei 116'),
 ('Barbati 114', 'Femei 117'),
 ('Barbati 114', 'Femei 118'),
 ('Barbati 114', 'Femei 119'),
 ('Barbati 114', 'Femei 120'),
 ('Barbati 115', 'Barbati 116'),
 ('Barbati 115', 'Barbati 117'),
 ('Barbati 115', 'Barbati 118'),
 ('Barbati 115', 'Barbati 119'),
 ('Barbati 115', 'Barbati 120'),
 ('Barbati 115', 'Femei 105'),
 ('Barbati 115', 'Femei 106'),
 ('Barbati 115', 'Femei 107'),
 ('Barbati 115', 'Femei 108'),
 ('Barbati 115', 'Femei 110'),
 ('Barbati 115', 'Femei 111'),
 ('Barbati 115', 'Femei 112'),
 ('Barbati 115', 'Femei 113'),
 ('Barbati 115', 'Femei 114'),
 ('Barbati 115', 'Femei 115'),
 ('Barbati 115', 'Femei 116'),
 ('Barbati 115', 'Femei 117'),
 ('Barbati 115', 'Femei 118'),
 ('Barbati 115', 'Femei 119'),
 ('Barbati 115', 'Femei 120'),
 ('Barbati 116', 'Barbati 117'),
 ('Barbati 116', 'Barbati 118'),
 ('Barbati 116', 'Barbati 119'),
 ('Barbati 116', 'Barbati 120'),
 ('Barbati 116', 'Femei 105'),
 ('Barbati 116', 'Femei 106'),
 ('Barbati 116', 'Femei 107'),
 ('Barbati 116', 'Femei 108'),
 ('Barbati 116', 'Femei 110'),
 ('Barbati 116', 'Femei 111'),
 ('Barbati 116', 'Femei 112'),
 ('Barbati 116', 'Femei 113'),
 ('Barbati 116', 'Femei 114'),
 ('Barbati 116', 'Femei 115'),
 ('Barbati 116', 'Femei 116'),
 ('Barbati 116', 'Femei 117'),
 ('Barbati 116', 'Femei 118'),
 ('Barbati 116', 'Femei 119'),
 ('Barbati 116', 'Femei 120'),
 ('Barbati 117', 'Barbati 118'),
 ('Barbati 117', 'Barbati 119'),
 ('Barbati 117', 'Barbati 120'),
 ('Barbati 117', 'Femei 105'),
 ('Barbati 117', 'Femei 106'),
 ('Barbati 117', 'Femei 107'),
 ('Barbati 117', 'Femei 108'),
 ('Barbati 117', 'Femei 110'),
 ('Barbati 117', 'Femei 111'),
 ('Barbati 117', 'Femei 112'),
 ('Barbati 117', 'Femei 113'),
 ('Barbati 117', 'Femei 114'),
 ('Barbati 117', 'Femei 115'),
 ('Barbati 117', 'Femei 116'),
 ('Barbati 117', 'Femei 117'),
 ('Barbati 117', 'Femei 118'),
 ('Barbati 117', 'Femei 119'),
 ('Barbati 117', 'Femei 120'),
 ('Barbati 118', 'Barbati 119'),
 ('Barbati 118', 'Barbati 120'),
 ('Barbati 118', 'Femei 105'),
 ('Barbati 118', 'Femei 106'),
 ('Barbati 118', 'Femei 107'),
 ('Barbati 118', 'Femei 108'),
 ('Barbati 118', 'Femei 110'),
 ('Barbati 118', 'Femei 111'),
 ('Barbati 118', 'Femei 112'),
 ('Barbati 118', 'Femei 113'),
 ('Barbati 118', 'Femei 114'),
 ('Barbati 118', 'Femei 115'),
 ('Barbati 118', 'Femei 116'),
 ('Barbati 118', 'Femei 117'),
 ('Barbati 118', 'Femei 118'),
 ('Barbati 118', 'Femei 119'),
 ('Barbati 118', 'Femei 120'),
 ('Barbati 119', 'Barbati 120'),
 ('Barbati 119', 'Femei 105'),
 ('Barbati 119', 'Femei 106'),
 ('Barbati 119', 'Femei 107'),
 ('Barbati 119', 'Femei 108'),
 ('Barbati 119', 'Femei 110'),
 ('Barbati 119', 'Femei 111'),
 ('Barbati 119', 'Femei 112'),
 ('Barbati 119', 'Femei 113'),
 ('Barbati 119', 'Femei 114'),
 ('Barbati 119', 'Femei 115'),
 ('Barbati 119', 'Femei 116'),
 ('Barbati 119', 'Femei 117'),
 ('Barbati 119', 'Femei 118'),
 ('Barbati 119', 'Femei 119'),
 ('Barbati 119', 'Femei 120'),
 ('Barbati 120', 'Femei 105'),
 ('Barbati 120', 'Femei 106'),
 ('Barbati 120', 'Femei 107'),
 ('Barbati 120', 'Femei 108'),
 ('Barbati 120', 'Femei 110'),
 ('Barbati 120', 'Femei 111'),
 ('Barbati 120', 'Femei 112'),
 ('Barbati 120', 'Femei 113'),
 ('Barbati 120', 'Femei 114'),
 ('Barbati 120', 'Femei 115'),
 ('Barbati 120', 'Femei 116'),
 ('Barbati 120', 'Femei 117'),
 ('Barbati 120', 'Femei 118'),
 ('Barbati 120', 'Femei 119'),
 ('Barbati 120', 'Femei 120'),
 ('Barbati 18-24', 'men_18_24'),
 ('Barbati 25-34', 'men_25_34'),
 ('Barbati 35-44', 'men_35_44'),
 ('Barbati 45-64', 'men_45_64'),
 ('Barbati 65+', 'men_65+'),
 ('Femei 105', 'Femei 106'),
 ('Femei 105', 'Femei 107'),
 ('Femei 105', 'Femei 108'),
 ('Femei 105', 'Femei 110'),
 ('Femei 105', 'Femei 111'),
 ('Femei 105', 'Femei 112'),
 ('Femei 105', 'Femei 113'),
 ('Femei 105', 'Femei 114'),
 ('Femei 105', 'Femei 115'),
 ('Femei 105', 'Femei 116'),
 ('Femei 105', 'Femei 117'),
 ('Femei 105', 'Femei 118'),
 ('Femei 105', 'Femei 119'),
 ('Femei 105', 'Femei 120'),
 ('Femei 106', 'Femei 107'),
 ('Femei 106', 'Femei 108'),
 ('Femei 106', 'Femei 110'),
 ('Femei 106', 'Femei 111'),
 ('Femei 106', 'Femei 112'),
 ('Femei 106', 'Femei 113'),
 ('Femei 106', 'Femei 114'),
 ('Femei 106', 'Femei 115'),
 ('Femei 106', 'Femei 116'),
 ('Femei 106', 'Femei 117'),
 ('Femei 106', 'Femei 118'),
 ('Femei 106', 'Femei 119'),
 ('Femei 106', 'Femei 120'),
 ('Femei 107', 'Femei 108'),
 ('Femei 107', 'Femei 110'),
 ('Femei 107', 'Femei 111'),
 ('Femei 107', 'Femei 112'),
 ('Femei 107', 'Femei 113'),
 ('Femei 107', 'Femei 114'),
 ('Femei 107', 'Femei 115'),
 ('Femei 107', 'Femei 116'),
 ('Femei 107', 'Femei 117'),
 ('Femei 107', 'Femei 118'),
 ('Femei 107', 'Femei 119'),
 ('Femei 107', 'Femei 120'),
 ('Femei 108', 'Femei 110'),
 ('Femei 108', 'Femei 111'),
 ('Femei 108', 'Femei 112'),
 ('Femei 108', 'Femei 113'),
 ('Femei 108', 'Femei 114'),
 ('Femei 108', 'Femei 115'),
 ('Femei 108', 'Femei 116'),
 ('Femei 108', 'Femei 117'),
 ('Femei 108', 'Femei 118'),
 ('Femei 108', 'Femei 119'),
 ('Femei 108', 'Femei 120'),
 ('Femei 110', 'Femei 111'),
 ('Femei 110', 'Femei 112'),
 ('Femei 110', 'Femei 113'),
 ('Femei 110', 'Femei 114'),
 ('Femei 110', 'Femei 115'),
 ('Femei 110', 'Femei 116'),
 ('Femei 110', 'Femei 117'),
 ('Femei 110', 'Femei 118'),
 ('Femei 110', 'Femei 119'),
 ('Femei 110', 'Femei 120'),
 ('Femei 111', 'Femei 112'),
 ('Femei 111', 'Femei 113'),
 ('Femei 111', 'Femei 114'),
 ('Femei 111', 'Femei 115'),
 ('Femei 111', 'Femei 116'),
 ('Femei 111', 'Femei 117'),
 ('Femei 111', 'Femei 118'),
 ('Femei 111', 'Femei 119'),
 ('Femei 111', 'Femei 120'),
 ('Femei 112', 'Femei 113'),
 ('Femei 112', 'Femei 114'),
 ('Femei 112', 'Femei 115'),
 ('Femei 112', 'Femei 116'),
 ('Femei 112', 'Femei 117'),
 ('Femei 112', 'Femei 118'),
 ('Femei 112', 'Femei 119'),
 ('Femei 112', 'Femei 120'),
 ('Femei 113', 'Femei 114'),
 ('Femei 113', 'Femei 115'),
 ('Femei 113', 'Femei 116'),
 ('Femei 113', 'Femei 117'),
 ('Femei 113', 'Femei 118'),
 ('Femei 113', 'Femei 119'),
 ('Femei 113', 'Femei 120'),
 ('Femei 114', 'Femei 115'),
 ('Femei 114', 'Femei 116'),
 ('Femei 114', 'Femei 117'),
 ('Femei 114', 'Femei 118'),
 ('Femei 114', 'Femei 119'),
 ('Femei 114', 'Femei 120'),
 ('Femei 115', 'Femei 116'),
 ('Femei 115', 'Femei 117'),
 ('Femei 115', 'Femei 118'),
 ('Femei 115', 'Femei 119'),
 ('Femei 115', 'Femei 120'),
 ('Femei 116', 'Femei 117'),
 ('Femei 116', 'Femei 118'),
 ('Femei 116', 'Femei 119'),
 ('Femei 116', 'Femei 120'),
 ('Femei 117', 'Femei 118'),
 ('Femei 117', 'Femei 119'),
 ('Femei 117', 'Femei 120'),
 ('Femei 118', 'Femei 119'),
 ('Femei 118', 'Femei 120'),
 ('Femei 119', 'Femei 120'),
 ('Femei 18-24', 'women_18_24'),
 ('Femei 25-34', 'women_25_34'),
 ('Femei 35-44', 'women_35_44'),
 ('Femei 45-64', 'women_45_64'),
 ('Femei 65+', 'women_65+'),
 ('Judet', 'county_code'),
 ('Județ', 'county_name'),
 ('LP', 'liste_permanente'),
 ('LS', 'lista_suplimentare'),
 ('LT', 'total'),
 ('Localitate_y', 'locality_name'),
 ('Mediu', 'medium'),
 ('Nr', 'Nr sectie de votare'),
 ('Nr', 'precinct_nr'),
 ('Nr sectie de votare', 'precinct_nr'),
 ('Nume sectie de votare', 'precinct_name'),
 ('UAT', 'Uat'),
 ('UAT', 'uat_name'),
 ('UM', 'urna_mobila'),
 ('Uat', 'uat_name'),
 ('Votanti lista', 'initial_count')}

There’s only one more step that we need to do: find the groups of columns that have the same information. There are cases where the columns are not only duplicated but triplicated, which results in (A == B), (B == C), (C == A) pairs in the analisys above.

This is the perfect job of the disjoint-set datastructure.

_groups = DisjointSets()
for _a, _b in _pairs:
    _groups.union(_a, _b)
_groups.sets()
[['Barbati 109',
  'Femei 118',
  'Femei 110',
  'Femei 114',
  'Barbati 104',
  'Femei 115',
  'Barbati 116',
  'Barbati 117',
  'Barbati 114',
  'Femei 107',
  'Femei 119',
  'Femei 105',
  'Barbati 115',
  'Femei 108',
  'Barbati 118',
  'Barbati 108',
  'Barbati 119',
  'Femei 120',
  'Femei 116',
  'Barbati 112',
  'Femei 113',
  'Barbati 113',
  'Barbati 120',
  'Femei 117',
  'Barbati 106',
  'Barbati 110',
  'Femei 106',
  'Femei 112',
  'Femei 111'],
 ['LP', 'liste_permanente'],
 ['Femei 35-44', 'women_35_44'],
 ['LT', 'total'],
 ['Nr sectie de votare', 'precinct_nr', 'Nr'],
 ['UM', 'urna_mobila'],
 ['Mediu', 'medium'],
 ['Barbati 65+', 'men_65+'],
 ['Barbati 35-44', 'men_35_44'],
 ['Femei 18-24', 'women_18_24'],
 ['Votanti lista', 'initial_count'],
 ['Femei 25-34', 'women_25_34'],
 ['Barbati 25-34', 'men_25_34'],
 ['UAT', 'Uat', 'uat_name'],
 ['Barbati 18-24', 'men_18_24'],
 ['Barbati 45-64', 'men_45_64'],
 ['Localitate_y', 'locality_name'],
 ['Femei 45-64', 'women_45_64'],
 ['Nume sectie de votare', 'precinct_name'],
 ['Judet', 'county_code'],
 ['Femei 65+', 'women_65+'],
 ['Județ', 'county_name'],
 ['LS', 'lista_suplimentare']]

From the list above we know we choose to drop the following columns:

drop_columns = ['Barbati 104', 'Barbati 106',
       'Barbati 108', 'Barbati 109', 'Barbati 110', 'Barbati 112',
       'Barbati 113', 'Barbati 114', 'Barbati 115', 'Barbati 116',
       'Barbati 117', 'Barbati 118', 'Barbati 119', 'Barbati 120', 'Femei 105',
       'Femei 106', 'Femei 107', 'Femei 108', 'Femei 110', 'Femei 111',
       'Femei 112', 'Femei 113', 'Femei 114', 'Femei 115', 'Femei 116',
       'Femei 117', 'Femei 118', 'Femei 119', 'Femei 120', 'LP', 'Femei 35-44', 'LT', 'Nr sectie de votare', 'Nr', 'UM', 
       'Mediu', 'Barbati 65+', 'Barbati 35-44','Femei 18-24',  'initial_count', 'Femei 25-34', 'Barbati 25-34', 
       'UAT', 'Uat', 'Barbati 18-24', 'Barbati 45-64', 'Localitate_y', 'Femei 45-64', 'Femei 45-64', 'Nume sectie de votare',
       'Judet', 'Femei 65+', 'Județ', 'LS', 
]
df_final = df_full.drop(columns=drop_columns)
df_final.columns
Index(['liste_permanente', 'lista_suplimentare', 'total', 'urna_mobila',
       'county_code', 'county_name', 'id_county', 'id_locality', 'id_precinct',
       'id_uat',
       ...
       'Femei 96', 'Femei 97', 'Femei 98', 'Femei 99', 'Femei 100',
       'Femei 101', 'Femei 102', 'Femei 103', 'Femei 104', 'Femei 109'],
      dtype='object', length=242)

And we end up with..

df_final.head().T
0 1 2 3 4
liste_permanente 696 140 501 571 680
lista_suplimentare 63 10 25 41 55
total 759 150 526 612 736
urna_mobila 0 0 0 0 1
county_code VS VS VS VS VS
county_name VASLUI VASLUI VASLUI VASLUI VASLUI
id_county 39 39 39 39 39
id_locality 9015 9015 9006 9006 9006
id_precinct 16128 16187 16086 16087 16088
id_uat 2936 2936 2933 2933 2933
latitude 46.6389853639550 46.6421141774663 46.2240238056566 46.2278431009305 46.2278431009305
locality_name VASLUI VASLUI BÂRLAD BÂRLAD BÂRLAD
longitude 27.7326775437114 27.7289502189002 27.6775710052581 27.6686353095150 27.6686353095150
medium U U U U U
precinct_name CASA DE CULTURĂ A SINDICATELOR ”CONSTANTIN TĂN... ȘCOALA GIMNAZIALĂ ”CONSTANTIN PARFENE” ŞCOALA GIMNAZIALĂ ”VICTOR IOAN POPA” CASA DE CULTURĂ A SINDICATELOR ”GEORGE TUTOVEANU” CASA DE CULTURĂ A SINDICATELOR ”GEORGE TUTOVEANU”
precinct_nr 1 10 100 101 102
presence 51.6327 8.1522 38.8479 44.5091 46.879
siruta 161954 161954 161801 161801 161801
uat_code None None None None None
uat_name MUNICIPIUL VASLUI MUNICIPIUL VASLUI MUNICIPIUL BÂRLAD MUNICIPIUL BÂRLAD MUNICIPIUL BÂRLAD
men_18_24 13 4 18 15 11
men_25_34 31 5 25 29 40
men_35_44 66 17 60 52 60
men_45_64 110 28 104 113 158
men_65+ 115 20 43 72 81
women_18_24 22 6 17 22 14
women_25_34 34 8 33 32 46
women_35_44 78 15 64 55 52
women_45_64 171 28 117 127 178
women_65+ 119 19 45 95 96
... ... ... ... ... ...
Femei 76 5 2 0 7 2
Femei 77 4 0 2 0 0
Femei 78 7 0 2 2 3
Femei 79 4 1 1 2 3
Femei 80 5 0 3 1 1
Femei 81 4 0 0 3 0
Femei 82 2 0 0 4 3
Femei 83 1 0 1 2 1
Femei 84 0 1 0 3 1
Femei 85 1 0 0 1 0
Femei 86 2 1 0 1 0
Femei 87 1 1 0 0 2
Femei 88 2 1 0 0 0
Femei 89 0 0 0 0 1
Femei 90 0 0 0 0 0
Femei 91 0 0 0 0 0
Femei 92 0 0 0 0 0
Femei 93 0 0 0 0 0
Femei 94 0 0 0 0 0
Femei 95 0 0 0 0 0
Femei 96 0 0 0 0 0
Femei 97 0 0 0 0 0
Femei 98 0 0 0 0 0
Femei 99 0 0 0 0 0
Femei 100 0 0 0 0 0
Femei 101 0 0 0 0 0
Femei 102 0 0 0 0 0
Femei 103 0 0 0 0 0
Femei 104 0 0 0 0 0
Femei 109 0 0 0 0 0

242 rows × 5 columns

Save the data to csv format

We’re almost done. We only need to save the dataset on disk and start using it (to be continued, in a future post)!

df_final.to_csv("_data/final.csv")

Updated: