Power BI to comb elections 2019 results

2019 PRESIDENCIAL ELECTIONS

In 2019 there was a presidential election in Argentina with a political change in the national government and in several provinces.

There were two electoral instances:

1) Primary elections (PASO [open, simultaneous and obligatory primary elections]) on August 11th.

2) General elections on October 27th.

La Nación Data analyzed the results in each one of the approximately 100.000 voting tables throughout the country. Even though it was considered the results for all the candidates, for this presentation the focus was only on the elections of the President of the Nation and the Governor of the Province of Buenos Aires, and in each case on the two main candidates, who, in the end, led this change.

1)      As regards the Presidency of the Nation, Mauricio Macri (Juntos por el Cambio) was succeeded by Alberto Fernandez (Frente de Todos)

2)      As regards the provincial government of the Province of Buenos Aires, María Eugenia Vidal (Juntos por el Cambio) was succeeded by Axel Kicillof (Frente de Todos).

Each one of the 100.000 voting tables have a maximum of 350 electors, and for very small and remote districts, until 15 electors.

Electoral tables or ballot box, are distributed as follows:

–          Provinces (24)

–          Electoral districts (135)

–          Electoral circuits (2.047)

–          Electoral institutions (11.450)

–          And, finally, electoral tables (100.000),

PURPOSE

The purpose of this project is to define different scenarios to analyze and compare the electoral behaviors among the different electoral instances, in different cities and for the different candidates and also to detect possible “anomalous results” that could be useful to determine human or technical errors or evidence of electoral manipulation.

For doing this, linear regression was applied to find individual behaviors that differ from the behaviors of the same electoral institution (e.g. voting tables with much fewer votes than expected for one candidate and many more for another and very different behavior from that of other voting tables in the same institution or Circuit or Section or Province.

And verify whether this behavior also applies to other candidates for other offices of the same political party.

PROJECT

On the election day, the designated authorities for each one of the 100,000 voting tables prepare a form with the results for each candidate, said form is then scanned and sent to the counting centers that load the forms by hand for the vote counting.

At the same time, the authorities sent the scanned forms to the website of the National Electoral Insitution, from which La Nación Data has been downloading since 2007, which, in turn, allows the analysis of behavior in the same city over the years.

Five Excel documents were also published, and these files allow to create a database for its systemic analysis

METHODOLOGY

A SQL Server database was designed for the analysis and it was then imported from Microsoft Power BI, the tool used to create different scenarios for analysis and graphics.

The results for candidates for president are presented on Figure 1, this information was collected from 100.147 voting tables for the PASO (P) elections, on each one of the 24 provinces. The names of the two main candidates are colored with the party colors.

There is a column with the percentages obtained from the candidates, where there appears NaN (Non Available Number) results, with “0” vote for the candidate.

As it may be observed, this candidate did not obtain a vote, by mistake or omission.

Figure 1.

The “votes to consider” ranges may be selected with the 3 “sliders”, between 0 and the maximum number that each candidate may obtain throughout the 100.147 voting tables.  

As it may be noted on Figure 2, this may be avoided by selecting “1” as minimum value. Thus, with “at least one vote” range, the mistake is corrected and with the decrease to 95.658 voting tables, it means that there are 4.218 voting tables with “0” vote for somebody (or both) candidates.

Figure 2.

Figure 3. Analysis for the Province of Buenos Aires (34.768 voting tables) for candidates Macri and Fernández. At the top, there appears the “Standard deviation” graphic, where the separate dots show non compatible behaviors. On the right, the dot represents Voting Table 082 in Necochea, identified as 2-82-5479.

Figure 3.

Similarly, the electoral instance (P -> PASO o G -> Generales) may be selected with all these controls and per Province or Circuit.

We selected those cases where Macri obtained 0 votes and by ordering the number of votes for Fernández, from largest to smallest, it may be noted on Figure4 that the first 17 votes correspond to the province of Santiago del Estero.

Figure 4.

Furthermore, as it may be observed on Figure 5, other columns “Macri – Fern” were added that reflect the difference of the percentages obtained by the two main candidates, so that 100% refers to all the votes for each one of the candidates, 0 means a tie (50 – 50%) and 100% means all the votes for the other candidate.

Another column was added to define 10 ranges for said differences for President, with a formula:

#”Added Custom7″ = Table.AddColumn(#”Added Custom6″, “RangoPRES”, each Number.Round([#”Macri-Fern”]/10,0)), where, for example, “1” corresponds to a difference between 0,1%  and 10%, “-1” between 0,1% and 10%, “5” between 5,1% and 50%.

The same analysis was made for the provincial government election.

Figure 5

To better view where each candidate wins, this graphic represents the differences between the percentages of each candidate; those percentages on line 0 means a tie, those above 0 are in favor of María Eugenia Vidal, and those below for Axel Kicilof.

Figure 6 and Figure 7.

Figure 6

Figure 7

Foreigners may vote for Governor and Mayor, so it may be selected check box “E” for Foreigners and “X” for native Argentines.

And the “Electoral Section” check boxes allow to select within each province the municipalities that make up that Electoral Section. For example, as regards Buenos Aires, the different suburban areas or the agricultural areas. 

Figure 8 reflects details of the vote blank per Province, Municipality, etc. both for PASO and GENERAL ELECTIONS

Figure 8

Hundreds of scenarios may be easily created and determined with these data, only by clicking in some of the check boxes the number of citizens voting on each election and district, percentages of blank vote or nule vote, distribution of votes for each candidate comparing PASO and GENERAL elections, etc., to arrive at a conclusion for country, province, city, etc.

Figure 9. Data collected from 471 voting tables for PASO elections where there were “0” votes for candidate Macri. Similar analysis made for each candidate and for each election (PASO and GENERAL).

Figure 9.

By ordering from smaller to larger the number of Total Votes (Figure 10), the first case is a Foreigners voting table in Carlos Tejedor with only one vote, and it can be reasonable, but the second case, in Lobería, there appears only 1 vote.

When looking at the telegram sent (Figure 11), it is mentioned 2 votes, but it is signed by the president of the voting table and 5 representatives of the political parties, this circumstance may infer that this is not an isolated city with so few inhabitants that only one votes.


Figure 10

Figure 11

All these scenarios allowed the editorial department of La Nación to make multiple considerations and define scenarios for publication, among others. 

The effort in monitoring reduced the pollling stations with zero votes.

https://www.lanacion.com.ar/politica/el-refuerzo-de-fiscalizacion-redujo-las-mesas-con-cero-votos-nid2309419