Exercises for Chapter 9
In these exercises, we use again the database dbadvanced and the tables we have created in the chapter. Make sure that this database exists exactly as described in the chapter. Alternatively, you can regenerate this database with
library(RPostgres)
db <- dbConnect(Postgres(),
dbname = "dbadvanced",
user = "postgres",
password = "pgpasswd"
)
dbExecute(db, "DROP TABLE IF EXISTS elections")
dbExecute(db, "DROP TABLE IF EXISTS parties")
dbExecute(db, "DROP TABLE IF EXISTS first_elections")
elections <- read.csv(file.path("ch09", "elections.csv"), header = T)
elections$election_date <- as.Date(elections$election_date)
dbWriteTable(db, "elections", elections)
dbExecute(db, "ALTER TABLE elections
ADD COLUMN seat_share real,
ADD COLUMN year integer")
dbExecute(db, "UPDATE elections
SET seat_share=seats::real / seats_total,
year=extract(year from election_date)")
parties <- read.csv(file.path("ch09", "parties.csv"), header = T)
dbWriteTable(db, "parties", parties)
populist <- read.csv(file.path("ch09", "populist.csv"), header = T)
dbWriteTable(db, "populist", populist)
dbExecute(db, "ALTER TABLE parties ADD COLUMN populist integer")
dbExecute(
db,
"UPDATE parties
SET populist = populist.populist
FROM populist
WHERE parties.party_id = populist.parlgov_id"
)
dbExecute(db, "ALTER TABLE parties ADD PRIMARY KEY (party_id)")
dbExecute(db, "ALTER TABLE elections ADD FOREIGN KEY (party_id) REFERENCES parties (party_id)")Exercise 1: Grouping and Aggregation with Joined Tables
The PopuList dataset that we have used in the Chapter also includes information about whether a party is considered to be “far left” or “far right”. In this exercise, we want to find out which of these extremist parties have the highest electoral experience, measured by the number of elections they have participated in. As a first step, merge the respective variables farleft and farright from the PopuList into the parties table. Next, use grouping and aggregation to retrieve the number of elections that parties of either type have participated in. Can you order this list such that the parties with the most electoral experience appear at the top?
Solution
We first merge the two new variables into the parties table, using a similar approach as presented in the chapter. Note that we create and update the two new variables in single ALTER TABLE and UPDATE statements:
dbExecute(db, "ALTER TABLE parties ADD COLUMN farleft INTEGER, ADD COLUMN farright integer")
dbExecute(
db,
"UPDATE parties
SET farright = populist.farright, farleft = populist.farleft
FROM populist
WHERE parties.party_id = populist.parlgov_id"
)To retrieve the number of elections a party party has run in, we use a SELECT statement on the joined parties and elections tables, as in the chapter. However, here we group on the country name and the party name. Strictly speaking, grouping on country is not necessary since parties are always uniquely assigned to a single country, but improves presentation of the results. We include only far left and far right parties in the WHERE clause, and use a simple count(*) as aggregation function. We sort the results by the number of elections in descending order, to display the far left and far right parties with the highest electoral experience at the top.
dbGetQuery(
db,
"SELECT
elections.country_name,
party_name_english,
count(*) as num_elections
FROM elections JOIN parties USING (party_id)
WHERE farright = 1 OR farleft = 1
GROUP BY elections.country_name, parties.party_name_english
ORDER BY num_elections DESC"
)## country_name
## 1 Sweden
## 2 Switzerland
## 3 Finland
## 4 France
## 5 Switzerland
## 6 Denmark
## 7 Austria
## 8 Luxembourg
## 9 Finland
## 10 Greece
## 11 Iceland
## 12 Norway
## 13 Spain
## 14 Portugal
## 15 Denmark
## 16 Norway
## 17 Italy
## 18 United Kingdom
## 19 Belgium
## 20 Switzerland
## 21 Ireland
## 22 Netherlands
## 23 Belgium
## 24 Slovakia
## 25 Norway
## 26 Ireland
## 27 Cyprus
## 28 Portugal
## 29 Slovenia
## 30 Slovenia
## 31 Germany
## 32 Czech Republic
## 33 France
## 34 Denmark
## 35 Italy
## 36 Hungary
## 37 Greece
## 38 Greece
## 39 Hungary
## 40 Slovenia
## 41 Switzerland
## 42 Romania
## 43 Spain
## 44 Italy
## 45 Denmark
## 46 Greece
## 47 Portugal
## 48 Hungary
## 49 Poland
## 50 Belgium
## 51 Estonia
## 52 United Kingdom
## 53 Spain
## 54 Ireland
## 55 Switzerland
## 56 Poland
## 57 Switzerland
## 58 Romania
## 59 Czech Republic
## 60 Croatia
## 61 Bulgaria
## 62 Netherlands
## 63 Lithuania
## 64 Greece
## 65 Croatia
## 66 Luxembourg
## 67 Denmark
## 68 Slovakia
## 69 Sweden
## 70 Hungary
## 71 Greece
## 72 Hungary
## 73 Lithuania
## 74 Switzerland
## 75 Austria
## 76 Netherlands
## 77 Bulgaria
## 78 Poland
## 79 Slovakia
## 80 Croatia
## 81 Hungary
## 82 Poland
## 83 Greece
## 84 Lithuania
## 85 Ireland
## 86 Spain
## 87 Luxembourg
## 88 Slovakia
## 89 Cyprus
## 90 Spain
## 91 Greece
## 92 Germany
## 93 Bulgaria
## 94 Romania
## 95 Belgium
## 96 Latvia
## 97 Slovenia
## 98 Spain
## 99 Italy
## 100 Switzerland
## 101 Poland
## 102 Estonia
## 103 Spain
## 104 Spain
## 105 Spain
## 106 Sweden
## 107 Italy
## 108 Croatia
## 109 Italy
## 110 Latvia
## 111 Romania
## 112 Slovakia
## 113 Netherlands
## 114 Poland
## 115 Lithuania
## 116 France
## 117 Slovakia
## 118 Latvia
## 119 Italy
## 120 Poland
## 121 Lithuania
## 122 Bulgaria
## 123 Ireland
## 124 Latvia
## 125 France
## 126 Poland
## 127 Czech Republic
## 128 Czech Republic
## 129 United Kingdom
## 130 Cyprus
## 131 Poland
## 132 France
## 133 Italy
## 134 Croatia
## 135 Poland
## 136 Romania
## 137 Lithuania
## 138 Czech Republic
## 139 Czech Republic
## 140 Bulgaria
## party_name_english
## 1 Left Party (Communists)
## 2 Swiss People's Party
## 3 Democratic Union | Left Alliance
## 4 French Communist Party
## 5 Swiss Party of Labour
## 6 Socialist Peoples Party
## 7 Freedom Party of Austria
## 8 Communist Party of Luxembourg
## 9 Finnish Party | True Finns
## 10 Communist Party of Greece
## 11 People's Alliance
## 12 Progress Party
## 13 Communist Party | United Left
## 14 Portuguese Communist Party
## 15 Progress Party
## 16 Socialist Left Party
## 17 Italian Social Movement
## 18 Sinn Fein
## 19 Flemish Block
## 20 National Action -- Swiss Democrats
## 21 Sinn Fein The Workers' Party
## 22 Socialist Party
## 23 Workers' Party of Belgium
## 24 Slovak National Party
## 25 Red Electoral Alliance
## 26 Sinn Fein
## 27 Progressive Party of Working People
## 28 Unified Democratic Coalition
## 29 Slovenian Democratic Party
## 30 United List -- Social Democrats
## 31 The Left / PDS
## 32 Communist Party of Bohemia and Moravia
## 33 National Front
## 34 Red-Green Alliance
## 35 North League
## 36 Christian Democratic People's Party
## 37 Coalition of the Radical Left
## 38 Coalition of the Left
## 39 Fidesz -- Hungarian Civic Union
## 40 Slovenian National Party
## 41 Ticino League
## 42 Greater Romania Party
## 43 United People
## 44 Communist Refoundation Party
## 45 Danish Peoples Party
## 46 Popular Orthodox Rally
## 47 Bloc of the Left
## 48 Hungarian Workers' Party
## 49 Real Politics Union | Congress of the New Right
## 50 National Front
## 51 People's Union of Estonia
## 52 United Kingdom Independence Party
## 53 Galician Nationalist Block
## 54 Socialist Party
## 55 Automobile Party | Freedom Party of Switzerland
## 56 Law and Justice
## 57 Autonomous Socialist Party
## 58 Romanian National Unity Party
## 59 Rally for the Republic -- Republican Party of Czechoslovakia
## 60 Croatian Democratic Alliance of Slavonia and Baranja
## 61 Attack
## 62 Party for Freedom
## 63 Young Lithuania
## 64 Peoples Association -- Golden Dawn
## 65 Croatian Party of Rights
## 66 The Left
## 67 Republic (Faroe Islands)
## 68 Communist Party of Slovakia
## 69 Sweden Democrats
## 70 Hungarian Justice and Life Party
## 71 Democratic Social Movement
## 72 Fidesz -- Hungarian Civic Party / Christian Democratic People's Party
## 73 Lithuanian Liberty Union
## 74 Solidarity
## 75 Alliance for the Future of Austria
## 76 Centre Democrats
## 77 National Front for the Salvation of Bulgaria
## 78 League of Polish Families
## 79 People's Party Our Slovakia
## 80 Croatian Party of Rights -- Dr. Ante Starcevic
## 81 Jobbik Movement for a Better Hungary
## 82 Movement for the Reconstruction of Poland
## 83 Political Spring
## 84 Socialist People's Front
## 85 Democratic Left
## 86 Compromise | A la valenciana
## 87 National Movement
## 88 Association of Workers of Slovakia
## 89 National Popular Front
## 90 Basque Country Unite
## 91 Alternative Ecologists
## 92 Alternative for Germany
## 93 Order, Lawfulness and Justice
## 94 Socialist Party of Labour
## 95 People's Party
## 96 For Fatherland and Freedom
## 97 Socialist Party of Slovenia
## 98 Aragonese Council
## 99 Party of the Italian Communists
## 100 Geneva Citizens' Movement
## 101 Party X
## 102 Estonian Citizens
## 103 We Can
## 104 In Tide
## 105 In Common We Can
## 106 New Democracy
## 107 Southern Action League
## 108 Croatian Labourists -- Labour Party
## 109 Left (Ecology) Freedom
## 110 Socialist Party of Latvia
## 111 United Romania Party
## 112 We are family -- Boris Kollar
## 113 Forum for Democracy
## 114 Polish United Workers' Party
## 115 Communist Party of Lithuania
## 116 Arise the Republic | France Arise
## 117 Real Slovak National Party
## 118 Communist Party of Latvia
## 119 Brothers of Italy -- National Centre-right
## 120 Together Party
## 121 Lithuanian Communist Party on the CPSU Platform
## 122 IMRO -- Bulgarian National Movement
## 123 People Before Profit Alliance
## 124 Latvian Unity Party
## 125 Rally for France
## 126 Polish Western Union
## 127 Freedom and Direct Democracy Tomio Okamura
## 128 Communist Party of Czechoslovakia
## 129 Respect -- The Unity Coalition
## 130 Citizen's Alliance
## 131 Kukiz'15
## 132 Unbowed France
## 133 Fiamma Tricolore
## 134 Croatian Growth
## 135 Coalition for the Renewal of the Republic -- Liberty and Hope
## 136 Romanian Socialist Democratic Party
## 137 Lithuanian National Union List
## 138 Dawn of Direct Democracy
## 139 Sovereignty -- Jana Bobosikova Bloc
## 140 Will
## num_elections
## 1 29
## 2 26
## 3 24
## 4 24
## 5 23
## 6 21
## 7 21
## 8 18
## 9 15
## 10 14
## 11 13
## 12 12
## 13 12
## 14 12
## 15 12
## 16 12
## 17 11
## 18 11
## 19 11
## 20 11
## 21 10
## 22 10
## 23 9
## 24 9
## 25 9
## 26 9
## 27 9
## 28 9
## 29 8
## 30 8
## 31 8
## 32 8
## 33 8
## 34 8
## 35 7
## 36 7
## 37 7
## 38 7
## 39 7
## 40 7
## 41 7
## 42 7
## 43 6
## 44 6
## 45 6
## 46 6
## 47 6
## 48 5
## 49 5
## 50 5
## 51 5
## 52 5
## 53 5
## 54 5
## 55 5
## 56 5
## 57 5
## 58 4
## 59 4
## 60 4
## 61 4
## 62 4
## 63 4
## 64 4
## 65 4
## 66 4
## 67 4
## 68 4
## 69 4
## 70 4
## 71 3
## 72 3
## 73 3
## 74 3
## 75 3
## 76 3
## 77 3
## 78 3
## 79 3
## 80 2
## 81 2
## 82 2
## 83 2
## 84 2
## 85 2
## 86 2
## 87 2
## 88 2
## 89 2
## 90 2
## 91 2
## 92 2
## 93 2
## 94 2
## 95 2
## 96 2
## 97 2
## 98 2
## 99 2
## 100 2
## 101 2
## 102 2
## 103 2
## 104 2
## 105 2
## 106 2
## 107 2
## 108 2
## 109 1
## 110 1
## 111 1
## 112 1
## 113 1
## 114 1
## 115 1
## 116 1
## 117 1
## 118 1
## 119 1
## 120 1
## 121 1
## 122 1
## 123 1
## 124 1
## 125 1
## 126 1
## 127 1
## 128 1
## 129 1
## 130 1
## 131 1
## 132 1
## 133 1
## 134 1
## 135 1
## 136 1
## 137 1
## 138 1
## 139 1
## 140 1Exercise 2: Foreign Keys and Joins If Tables Do Not Match
Rather than merging values from populist into parties as new attributes, somebody suggests that we could simply join the two tables when we need them, and add a foreign key relationship such that referential integrity is maintained. What foreign key do you need for this? What happens if you try to set up a foreign key, and why?
It seems that there is a number of entries in the PopuList dataset that cause problems. Can you find these entries with a JOIN? Since the standard join retrieves only those records with matching combinations, we will have to use a different type of join. Use only a single SELECT statement for this!
Solution
When we try to set up a foreign key in the populist table, we get an error message:
dbExecute(db, "ALTER TABLE populist ADD FOREIGN KEY (parlgov_id) REFERENCES parties (party_id)")## Error:
## ! Failed to fetch row : ERROR: insert or update on table "populist" violates foreign key constraint "populist_parlgov_id_fkey"
## DETAIL: Key (parlgov_id)=(2781) is not present in table "parties".The message tells us that a particular value of the foreign key is not present in the parties table. Obviously, there are parlgov_id values in parties that do not exist in parties. How can we locate them with a join?
A standard JOIN (an INNER JOIN) links only those records from the parties and populist tables match, and drops all the others. Here, we would like to retain those from populist that do not have a match in parties. If populist is the second (=right) table in our join, we can use a RIGHT JOIN to include also the non-matching records from this table. They will be added to the result, but all the attributes from parties will be empty (since there is no match).
dbGetQuery(db, "SELECT * from parties RIGHT JOIN populist ON parlgov_id = party_id")## party_id party_name_short
## 1 1536 BZO
## 2 50 FPO
## 3 669 Martin
## 4 2150 TS
## 5 171 FN
## 6 221 LD|LDD
## 7 438 Pp
## 8 256 PA-PTB
## 9 993 VB
## 10 535 Ataka
## 11 2362 BBZ
## 12 599 BBB
## 13 1541 GERB
## 14 2211 NFSB
## 15 544 NDSV
## 16 636 RZS
## 17 2212 VMRO
## 18 2637 Volya
## 19 2193 HGS
## 20 1044 HSP
## 21 2181 HSP-AS
## 22 2135 HDSSB
## 23 2182 CL-LP
## 24 2185 Hrast
## 25 2615 Most
## 26 2612 ZiZi
## 27 572 AKEL
## 28 1768 ELAM
## 29 2341 SYM
## 30 2263 ANO
## 31 1173 KSCM
## 32 665 KSC
## 33 872 SPR-RSC
## 34 363 SSO
## 35 1613 S-JB
## 36 2654 SPD
## 37 2262 UPD
## 38 336 VV
## 39 1418 DF
## 40 306 En-O
## 41 978 FrP
## 42 NA <NA>
## 43 1644 SF
## 44 2568 T
## 45 1064 EKo
## 46 417 ERa
## 47 428 ERP
## 48 25 SK
## 49 1292 DL|VAS
## 50 2645 UV
## 51 200 SP|P
## 52 2399 DLR|DLF
## 53 270 FN
## 54 2644 FI
## 55 1101 MF
## 56 686 PCF
## 57 2252 RPF
## 58 2253 AfD
## 59 791 Li/PDS
## 60 2091 AE
## 61 720 DIKKI
## 62 NA <NA>
## 63 NA <NA>
## 64 614 KKE
## 65 1179 LAOS
## 66 2092 LS-CA
## 67 2163 OE
## 68 1412 POLAN
## 69 1592 SYRIZA
## 70 1441 SYN
## 71 921 Fi-MPSz
## 72 437 Fi+KDNP
## 73 600 Jobbik
## 74 434 KDNP
## 75 95 MIEP
## 76 1202 MMP
## 77 NA <NA>
## 78 1360 Ab
## 79 587 B-H
## 80 2632 FlF
## 81 1455 F
## 82 2441 FF
## 83 2655 M
## 84 1342 Sj
## 85 1580 DLP
## 86 1804 PBPA
## 87 2217 SF
## 88 433 SFWP
## 89 1014 SP
## 90 176 MSFT
## 91 596 FI-PdL
## 92 2154 FdI-CN
## 93 67 CeD
## 94 1436 LN
## 95 436 LAM
## 96 1080 LV
## 97 2155 M5S
## 98 831 MSI
## 99 1661 PdCI
## 100 1321 PRC
## 101 465 SL
## 102 1518 JL
## 103 NA <NA>
## 104 1260 LKP
## 105 309 LSP
## 106 1042 LVP
## 107 1942 RP
## 108 203 TB
## 109 581 DP
## 110 2121 DK
## 111 383 JL
## 112 432 LTS
## 113 2635 LCP
## 114 1446 LKP
## 115 1623 TSKP
## 116 1562 LLaS
## 117 1056 SPF
## 118 1502 TPP
## 119 1421 TT-LDP
## 120 1582 AR|ADR
## 121 457 DL
## 122 151 KPL
## 123 374 NB
## 124 2109 50+
## 125 209 CD
## 126 2642 FvD
## 127 625 GPV
## 128 744 LN
## 129 456 LPF
## 130 990 PvdD
## 131 1501 PVV
## 132 357 SP
## 133 1251 SGP
## 134 351 Fr
## 135 1538 KrF
## 136 780 Kp
## 137 1638 RV
## 138 702 Sp
## 139 81 SV
## 140 2602 KORWIN
## 141 2600 K
## 142 NA <NA>
## 143 987 LPR
## 144 2625 Razem
## 145 181 X
## 146 1077 PZPR
## 147 1074 PZZ
## 148 528 PiS
## 149 2398 RN
## 150 148 ROP
## 151 207 SRP
## 152 1549 UPR|KNP
## 153 1758 ZChN
## 154 557 BE
## 155 NA <NA>
## 156 1295 CDU
## 157 514 PCP
## 158 2130 PP-DD
## 159 713 PRM
## 160 2648 PRU
## 161 562 PSM
## 162 307 PSoDR
## 163 648 PUNR
## 164 1200 ANO
## 165 44 KSS
## 166 586 LsNS
## 167 1645 OKS
## 168 1759 OLaNO
## 169 20 PSNS
## 170 1460 SaS
## 171 1072 SNS
## 172 2623 SR
## 173 220 Smer
## 174 1016 SOP
## 175 1563 ZRS
## 176 NA <NA>
## 177 NA <NA>
## 178 179 SDS
## 179 981 SNS
## 180 317 SSS
## 181 706 ZL-SD
## 182 520 BNG
## 183 1367 CA
## 184 2607 C|AV
## 185 2606 ECP
## 186 2604 EM
## 187 2603 EHB
## 188 NA <NA>
## 189 1317 HB
## 190 118 PCE|IU
## 191 2376 P
## 192 2380 Vox
## 193 951 NyD
## 194 1546 SD
## 195 882 V
## 196 1602 FPS
## 197 1318 EDU-UDF
## 198 1500 LdT
## 199 2599 MCR
## 200 628 NA|SD
## 201 1167 PdA
## 202 224 PSA
## 203 750 SVP-UDC
## 204 1226 S
## 205 773 Con
## 206 319 DUP
## 207 1057 R
## 208 689 SF
## 209 1272 UKIP
## party_name_english
## 1 Alliance for the Future of Austria
## 2 Freedom Party of Austria
## 3 Hans-Peter Martin's List
## 4 Team Stronach
## 5 National Front
## 6 List Dedecker | Libertarian, Direct, Democratic
## 7 People's Party
## 8 Workers' Party of Belgium
## 9 Flemish Block
## 10 Attack
## 11 Bulgaria Without Censorship
## 12 Bulgarian Business Bloc
## 13 Citizens for European Development of Bulgaria
## 14 National Front for the Salvation of Bulgaria
## 15 National Movement Simeon II
## 16 Order, Lawfulness and Justice
## 17 IMRO -- Bulgarian National Movement
## 18 Will
## 19 Croatian Civic Party
## 20 Croatian Party of Rights
## 21 Croatian Party of Rights -- Dr. Ante Starcevic
## 22 Croatian Democratic Alliance of Slavonia and Baranja
## 23 Croatian Labourists -- Labour Party
## 24 Croatian Growth
## 25 Bridge of Independent Lists
## 26 Human Shield
## 27 Progressive Party of Working People
## 28 National Popular Front
## 29 Citizen's Alliance
## 30 Action of Dissatisfied Citizens
## 31 Communist Party of Bohemia and Moravia
## 32 Communist Party of Czechoslovakia
## 33 Rally for the Republic -- Republican Party of Czechoslovakia
## 34 Party of Free Citizens
## 35 Sovereignty -- Jana Bobosikova Bloc
## 36 Freedom and Direct Democracy Tomio Okamura
## 37 Dawn of Direct Democracy
## 38 Public Affairs
## 39 Danish Peoples Party
## 40 Red-Green Alliance
## 41 Progress Party
## 42 <NA>
## 43 Socialist Peoples Party
## 44 Republic (Faroe Islands)
## 45 Estonian Citizens
## 46 People's Union of Estonia
## 47 Res Publica Party
## 48 Independent Royalists
## 49 Democratic Union | Left Alliance
## 50 New Alternative
## 51 Finnish Party | True Finns
## 52 Arise the Republic | France Arise
## 53 National Front
## 54 Unbowed France
## 55 Movement for France
## 56 French Communist Party
## 57 Rally for France
## 58 Alternative for Germany
## 59 The Left / PDS
## 60 Independent Greeks
## 61 Democratic Social Movement
## 62 <NA>
## 63 <NA>
## 64 Communist Party of Greece
## 65 Popular Orthodox Rally
## 66 Peoples Association -- Golden Dawn
## 67 Alternative Ecologists
## 68 Political Spring
## 69 Coalition of the Radical Left
## 70 Coalition of the Left
## 71 Fidesz -- Hungarian Civic Union
## 72 Fidesz -- Hungarian Civic Party / Christian Democratic People's Party
## 73 Jobbik Movement for a Better Hungary
## 74 Christian Democratic People's Party
## 75 Hungarian Justice and Life Party
## 76 Hungarian Workers' Party
## 77 <NA>
## 78 People's Alliance
## 79 Civic Movement -- The Movement
## 80 People's Party
## 81 Progressive Party
## 82 Liberal Party
## 83 Centre Party
## 84 Independence Party
## 85 Democratic Left
## 86 People Before Profit Alliance
## 87 Sinn Fein
## 88 Sinn Fein The Workers' Party
## 89 Socialist Party
## 90 Fiamma Tricolore
## 91 Go Italy -- The People of Freedom
## 92 Brothers of Italy -- National Centre-right
## 93 Centre Right
## 94 North League
## 95 Southern Action League
## 96 Venetian League
## 97 Five Star Movement
## 98 Italian Social Movement
## 99 Party of the Italian Communists
## 100 Communist Refoundation Party
## 101 Left (Ecology) Freedom
## 102 New Era
## 103 <NA>
## 104 Communist Party of Latvia
## 105 Socialist Party of Latvia
## 106 Latvian Unity Party
## 107 Reform Party
## 108 For Fatherland and Freedom
## 109 Labour Party
## 110 The Way of Courage
## 111 Young Lithuania
## 112 Lithuanian National Union List
## 113 Lithuanian Centre Party
## 114 Communist Party of Lithuania
## 115 Lithuanian Communist Party on the CPSU Platform
## 116 Lithuanian Liberty Union
## 117 Socialist People's Front
## 118 National Resurrection Party
## 119 Order and Justice -- Liberal Democratic Party
## 120 Action Committee Pensions | Alternative Democratic Reform Party
## 121 The Left
## 122 Communist Party of Luxembourg
## 123 National Movement
## 124 50PLUS
## 125 Centre Democrats
## 126 Forum for Democracy
## 127 Reformed Political League
## 128 Livable Netherlands
## 129 Fortuyn List
## 130 Party for the Animals
## 131 Party for Freedom
## 132 Socialist Party
## 133 Political Reformed Party
## 134 Progress Party
## 135 Christian Democratic Party
## 136 Coastal Party
## 137 Red Electoral Alliance
## 138 Centre Party
## 139 Socialist Left Party
## 140 Coalition for the Renewal of the Republic -- Liberty and Hope
## 141 Kukiz'15
## 142 <NA>
## 143 League of Polish Families
## 144 Together Party
## 145 Party X
## 146 Polish United Workers' Party
## 147 Polish Western Union
## 148 Law and Justice
## 149 National Movement
## 150 Movement for the Reconstruction of Poland
## 151 Self-Defense of the Republic Poland
## 152 Real Politics Union | Congress of the New Right
## 153 Christian National Union
## 154 Bloc of the Left
## 155 <NA>
## 156 Unified Democratic Coalition
## 157 Portuguese Communist Party
## 158 People's Party -- Dan Diaconescu
## 159 Greater Romania Party
## 160 United Romania Party
## 161 Socialist Party of Labour
## 162 Romanian Socialist Democratic Party
## 163 Romanian National Unity Party
## 164 Alliance of the New Citizen
## 165 Communist Party of Slovakia
## 166 People's Party Our Slovakia
## 167 Civic Conservative Party
## 168 Ordinary People and Independent
## 169 Real Slovak National Party
## 170 Freedom and Solidarity
## 171 Slovak National Party
## 172 We are family -- Boris Kollar
## 173 Direction -- Social Democracy
## 174 Party of Civic Understanding
## 175 Association of Workers of Slovakia
## 176 <NA>
## 177 <NA>
## 178 Slovenian Democratic Party
## 179 Slovenian National Party
## 180 Socialist Party of Slovenia
## 181 United List -- Social Democrats
## 182 Galician Nationalist Block
## 183 Aragonese Council
## 184 Compromise | A la valenciana
## 185 In Common We Can
## 186 In Tide
## 187 Basque Country Unite
## 188 <NA>
## 189 United People
## 190 Communist Party | United Left
## 191 We Can
## 192 Voice
## 193 New Democracy
## 194 Sweden Democrats
## 195 Left Party (Communists)
## 196 Automobile Party | Freedom Party of Switzerland
## 197 Federal Democratic Union of Switzerland
## 198 Ticino League
## 199 Geneva Citizens' Movement
## 200 National Action -- Swiss Democrats
## 201 Swiss Party of Labour
## 202 Autonomous Socialist Party
## 203 Swiss People's Party
## 204 Solidarity
## 205 Conservatives
## 206 Democratic Unionist Party
## 207 Respect -- The Unity Coalition
## 208 Sinn Fein
## 209 United Kingdom Independence Party
## country_name family_name populist farleft farright parlgov_id
## 1 Austria Right-wing 1 0 1 1536
## 2 Austria Right-wing 1 0 1 50
## 3 Austria Special issue 1 0 0 669
## 4 Austria Liberal 1 0 0 2150
## 5 Belgium Right-wing 1 0 1 171
## 6 Belgium Liberal 1 0 0 221
## 7 Belgium Conservative 1 0 1 438
## 8 Belgium Communist/Socialist 0 1 0 256
## 9 Belgium Right-wing 1 0 1 993
## 10 Bulgaria Right-wing 1 0 1 535
## 11 Bulgaria Conservative 1 0 0 2362
## 12 Bulgaria Liberal 1 0 0 599
## 13 Bulgaria Conservative 1 0 0 1541
## 14 Bulgaria Right-wing 1 0 1 2211
## 15 Bulgaria Liberal 1 0 0 544
## 16 Bulgaria Right-wing 1 0 1 636
## 17 Bulgaria Right-wing 1 0 1 2212
## 18 Bulgaria Liberal 1 0 1 2637
## 19 Croatia Right-wing 1 0 0 2193
## 20 Croatia Right-wing 0 0 1 1044
## 21 Croatia Right-wing 0 0 1 2181
## 22 Croatia Right-wing 1 0 1 2135
## 23 Croatia Communist/Socialist 1 1 0 2182
## 24 Croatia Christian democracy 0 0 1 2185
## 25 Croatia Conservative 1 0 0 2615
## 26 Croatia Right-wing 1 0 0 2612
## 27 Cyprus Communist/Socialist 0 1 0 572
## 28 Cyprus Right-wing 0 0 1 1768
## 29 Cyprus Social democracy 1 1 0 2341
## 30 Czech Republic Liberal 1 0 0 2263
## 31 Czech Republic Communist/Socialist 0 1 0 1173
## 32 Czech Republic Communist/Socialist 0 1 0 665
## 33 Czech Republic Right-wing 1 0 1 872
## 34 Czech Republic Conservative 0 0 0 363
## 35 Czech Republic Right-wing 1 0 1 1613
## 36 Czech Republic Right-wing 1 0 1 2654
## 37 Czech Republic Conservative 1 0 1 2262
## 38 Czech Republic Liberal 1 0 0 336
## 39 Denmark Right-wing 1 0 1 1418
## 40 Denmark Communist/Socialist 0 1 0 306
## 41 Denmark Liberal 1 0 1 978
## 42 <NA> <NA> NA NA NA 2781
## 43 Denmark Green/Ecologist 0 1 0 1644
## 44 Denmark Communist/Socialist 0 1 0 2568
## 45 Estonia Right-wing 1 0 1 1064
## 46 Estonia Conservative 1 0 1 417
## 47 Estonia Conservative 0 0 0 428
## 48 Estonia Special issue 1 0 0 25
## 49 Finland Communist/Socialist 0 1 0 1292
## 50 Finland Right-wing 1 0 0 2645
## 51 Finland Agrarian 1 0 1 200
## 52 France Conservative 1 0 1 2399
## 53 France Right-wing 1 0 1 270
## 54 France Communist/Socialist 1 1 0 2644
## 55 France Conservative 0 0 0 1101
## 56 France Communist/Socialist 0 1 0 686
## 57 France Conservative 0 0 1 2252
## 58 Germany Right-wing 1 0 1 2253
## 59 Germany Communist/Socialist 1 1 0 791
## 60 Greece Right-wing 1 0 0 2091
## 61 Greece Social democracy 1 1 0 720
## 62 <NA> <NA> NA NA NA 2780
## 63 <NA> <NA> NA NA NA 2742
## 64 Greece Communist/Socialist 0 1 0 614
## 65 Greece Right-wing 1 0 1 1179
## 66 Greece Right-wing 0 0 1 2092
## 67 Greece Green/Ecologist 0 1 0 2163
## 68 Greece Conservative 1 0 1 1412
## 69 Greece Communist/Socialist 1 1 0 1592
## 70 Greece Communist/Socialist 1 1 0 1441
## 71 Hungary Conservative 1 0 1 921
## 72 Hungary Conservative 1 0 1 437
## 73 Hungary Right-wing 1 0 1 600
## 74 Hungary Christian democracy 0 0 1 434
## 75 Hungary Right-wing 1 0 1 95
## 76 Hungary Communist/Socialist 0 1 0 1202
## 77 <NA> <NA> NA NA NA 2745
## 78 Iceland Communist/Socialist 0 1 0 1360
## 79 Iceland Liberal 1 0 0 587
## 80 Iceland Communist/Socialist 1 0 0 2632
## 81 Iceland Agrarian 0 0 0 1455
## 82 Iceland Liberal 0 0 0 2441
## 83 Iceland no family 1 0 0 2655
## 84 Iceland Conservative 0 0 0 1342
## 85 Ireland Communist/Socialist 0 1 0 1580
## 86 Ireland Communist/Socialist 0 1 0 1804
## 87 Ireland Communist/Socialist 1 1 0 2217
## 88 Ireland Communist/Socialist 0 1 0 433
## 89 Ireland Communist/Socialist 0 1 0 1014
## 90 Italy Right-wing 0 0 1 176
## 91 Italy Conservative 1 0 0 596
## 92 Italy Conservative 1 0 1 2154
## 93 Italy Conservative 1 0 0 67
## 94 Italy Right-wing 1 0 1 1436
## 95 Italy Right-wing 1 0 1 436
## 96 Italy Special issue 1 0 0 1080
## 97 Italy Green/Ecologist 1 0 0 2155
## 98 Italy Right-wing 0 0 1 831
## 99 Italy Communist/Socialist 0 1 0 1661
## 100 Italy Communist/Socialist 0 1 0 1321
## 101 Italy Communist/Socialist 0 1 0 465
## 102 Latvia Conservative 0 0 0 1518
## 103 <NA> <NA> NA NA NA 2715
## 104 Latvia Communist/Socialist 0 1 0 1260
## 105 Latvia Communist/Socialist 0 1 0 309
## 106 Latvia Communist/Socialist 0 1 0 1042
## 107 Latvia Conservative 1 0 0 1942
## 108 Latvia Right-wing 0 0 1 203
## 109 Lithuania Liberal 1 0 0 581
## 110 Lithuania Special issue 1 0 0 2121
## 111 Lithuania Right-wing 1 0 1 383
## 112 Lithuania Right-wing 0 0 1 432
## 113 Lithuania Right-wing 1 0 0 2635
## 114 Lithuania Communist/Socialist 0 1 0 1446
## 115 Lithuania Communist/Socialist 0 1 0 1623
## 116 Lithuania Right-wing 1 1 0 1562
## 117 Lithuania Communist/Socialist 0 1 0 1056
## 118 Lithuania Liberal 1 0 0 1502
## 119 Lithuania Conservative 1 0 0 1421
## 120 Luxembourg Conservative 1 0 0 1582
## 121 Luxembourg Communist/Socialist 0 1 0 457
## 122 Luxembourg Communist/Socialist 0 1 0 151
## 123 Luxembourg Right-wing 0 0 1 374
## 124 Netherlands Liberal 0 0 0 2109
## 125 Netherlands Right-wing 1 0 1 209
## 126 Netherlands Conservative 1 0 1 2642
## 127 Netherlands Conservative 0 0 0 625
## 128 Netherlands Right-wing 1 0 0 744
## 129 Netherlands Right-wing 1 0 0 456
## 130 Netherlands Special issue 0 0 0 990
## 131 Netherlands Conservative 1 0 1 1501
## 132 Netherlands Communist/Socialist 1 1 0 357
## 133 Netherlands Conservative 0 0 0 1251
## 134 Norway Right-wing 1 0 1 351
## 135 Norway Christian democracy 0 0 0 1538
## 136 Norway Conservative 1 0 0 780
## 137 Norway Communist/Socialist 0 1 0 1638
## 138 Norway Agrarian 0 0 0 702
## 139 Norway Communist/Socialist 0 1 0 81
## 140 Poland Conservative 0 0 1 2602
## 141 Poland Right-wing 1 0 1 2600
## 142 <NA> <NA> NA NA NA 2790
## 143 Poland Conservative 1 0 1 987
## 144 Poland Social democracy 0 1 0 2625
## 145 Poland Right-wing 1 0 1 181
## 146 Poland Communist/Socialist 0 1 0 1077
## 147 Poland Communist/Socialist 0 1 0 1074
## 148 Poland Conservative 1 0 1 528
## 149 Poland Right-wing 0 0 1 2398
## 150 Poland Conservative 0 0 1 148
## 151 Poland Agrarian 1 0 0 207
## 152 Poland Conservative 0 0 1 1549
## 153 Poland Conservative 0 0 0 1758
## 154 Portugal Communist/Socialist 0 1 0 557
## 155 <NA> <NA> NA NA NA 2789
## 156 Portugal Communist/Socialist 0 1 0 1295
## 157 Portugal Communist/Socialist 0 1 0 514
## 158 Romania Communist/Socialist 1 0 0 2130
## 159 Romania Right-wing 1 0 1 713
## 160 Romania Right-wing 1 0 1 2648
## 161 Romania Communist/Socialist 0 1 0 562
## 162 Romania Communist/Socialist 0 1 0 307
## 163 Romania Right-wing 1 0 1 648
## 164 Slovakia Liberal 1 0 0 1200
## 165 Slovakia Communist/Socialist 0 1 0 44
## 166 Slovakia Right-wing 0 0 1 586
## 167 Slovakia Conservative 0 0 0 1645
## 168 Slovakia Conservative 1 0 0 1759
## 169 Slovakia Right-wing 1 0 1 20
## 170 Slovakia Liberal 0 0 0 1460
## 171 Slovakia Right-wing 1 0 1 1072
## 172 Slovakia Right-wing 1 0 1 2623
## 173 Slovakia Social democracy 1 0 0 220
## 174 Slovakia Social democracy 1 0 0 1016
## 175 Slovakia Communist/Socialist 1 1 0 1563
## 176 <NA> <NA> NA NA NA 2670
## 177 <NA> <NA> NA NA NA 2668
## 178 Slovenia Social democracy 1 0 1 179
## 179 Slovenia to be coded 1 0 1 981
## 180 Slovenia Communist/Socialist 0 1 0 317
## 181 Slovenia Social democracy 1 1 0 706
## 182 Spain Special issue 0 1 0 520
## 183 Spain Special issue 0 1 0 1367
## 184 Spain Communist/Socialist 0 1 0 2607
## 185 Spain Communist/Socialist 1 1 0 2606
## 186 Spain Communist/Socialist 1 1 0 2604
## 187 Spain Special issue 0 1 0 2603
## 188 <NA> <NA> NA NA NA 2786
## 189 Spain Special issue 0 1 0 1317
## 190 Spain Communist/Socialist 0 1 0 118
## 191 Spain Communist/Socialist 1 1 0 2376
## 192 Spain Liberal 1 0 1 2380
## 193 Sweden Right-wing 1 0 1 951
## 194 Sweden Right-wing 1 0 1 1546
## 195 Sweden Communist/Socialist 0 1 0 882
## 196 Switzerland Right-wing 1 0 1 1602
## 197 Switzerland Conservative 1 0 0 1318
## 198 Switzerland Right-wing 1 0 1 1500
## 199 Switzerland Right-wing 1 0 1 2599
## 200 Switzerland Right-wing 0 0 1 628
## 201 Switzerland Communist/Socialist 0 1 0 1167
## 202 Switzerland Communist/Socialist 0 1 0 224
## 203 Switzerland Agrarian 1 0 1 750
## 204 Switzerland Communist/Socialist 0 1 0 1226
## 205 United Kingdom Conservative 0 0 0 773
## 206 United Kingdom Conservative 0 0 0 319
## 207 United Kingdom to be coded 1 1 0 1057
## 208 United Kingdom Communist/Socialist 1 1 0 689
## 209 United Kingdom Right-wing 1 0 1 1272
## populist farleft farright
## 1 1 0 1
## 2 1 0 1
## 3 1 0 0
## 4 1 0 0
## 5 1 0 1
## 6 1 0 0
## 7 1 0 1
## 8 0 1 0
## 9 1 0 1
## 10 1 0 1
## 11 1 0 0
## 12 1 0 0
## 13 1 0 0
## 14 1 0 1
## 15 1 0 0
## 16 1 0 1
## 17 1 0 1
## 18 1 0 1
## 19 1 0 0
## 20 0 0 1
## 21 0 0 1
## 22 1 0 1
## 23 1 1 0
## 24 0 0 1
## 25 1 0 0
## 26 1 0 0
## 27 0 1 0
## 28 0 0 1
## 29 1 1 0
## 30 1 0 0
## 31 0 1 0
## 32 0 1 0
## 33 1 0 1
## 34 0 0 0
## 35 1 0 1
## 36 1 0 1
## 37 1 0 1
## 38 1 0 0
## 39 1 0 1
## 40 0 1 0
## 41 1 0 1
## 42 1 0 1
## 43 0 1 0
## 44 0 1 0
## 45 1 0 1
## 46 1 0 1
## 47 0 0 0
## 48 1 0 0
## 49 0 1 0
## 50 1 0 0
## 51 1 0 1
## 52 1 0 1
## 53 1 0 1
## 54 1 1 0
## 55 0 0 0
## 56 0 1 0
## 57 0 0 1
## 58 1 0 1
## 59 1 1 0
## 60 1 0 0
## 61 1 1 0
## 62 1 0 1
## 63 1 1 0
## 64 0 1 0
## 65 1 0 1
## 66 0 0 1
## 67 0 1 0
## 68 1 0 1
## 69 1 1 0
## 70 1 1 0
## 71 1 0 1
## 72 1 0 1
## 73 1 0 1
## 74 0 0 1
## 75 1 0 1
## 76 0 1 0
## 77 1 0 1
## 78 0 1 0
## 79 1 0 0
## 80 1 0 0
## 81 0 0 0
## 82 0 0 0
## 83 1 0 0
## 84 0 0 0
## 85 0 1 0
## 86 0 1 0
## 87 1 1 0
## 88 0 1 0
## 89 0 1 0
## 90 0 0 1
## 91 1 0 0
## 92 1 0 1
## 93 1 0 0
## 94 1 0 1
## 95 1 0 1
## 96 1 0 0
## 97 1 0 0
## 98 0 0 1
## 99 0 1 0
## 100 0 1 0
## 101 0 1 0
## 102 0 0 0
## 103 1 0 0
## 104 0 1 0
## 105 0 1 0
## 106 0 1 0
## 107 1 0 0
## 108 0 0 1
## 109 1 0 0
## 110 1 0 0
## 111 1 0 1
## 112 0 0 1
## 113 1 0 0
## 114 0 1 0
## 115 0 1 0
## 116 1 1 0
## 117 0 1 0
## 118 1 0 0
## 119 1 0 0
## 120 1 0 0
## 121 0 1 0
## 122 0 1 0
## 123 0 0 1
## 124 0 0 0
## 125 1 0 1
## 126 1 0 1
## 127 0 0 0
## 128 1 0 0
## 129 1 0 0
## 130 0 0 0
## 131 1 0 1
## 132 1 1 0
## 133 0 0 0
## 134 1 0 1
## 135 0 0 0
## 136 1 0 0
## 137 0 1 0
## 138 0 0 0
## 139 0 1 0
## 140 0 0 1
## 141 1 0 1
## 142 0 0 0
## 143 1 0 1
## 144 0 1 0
## 145 1 0 1
## 146 0 1 0
## 147 0 1 0
## 148 1 0 1
## 149 0 0 1
## 150 0 0 1
## 151 1 0 0
## 152 0 0 1
## 153 0 0 0
## 154 0 1 0
## 155 1 0 1
## 156 0 1 0
## 157 0 1 0
## 158 1 0 0
## 159 1 0 1
## 160 1 0 1
## 161 0 1 0
## 162 0 1 0
## 163 1 0 1
## 164 1 0 0
## 165 0 1 0
## 166 0 0 1
## 167 0 0 0
## 168 1 0 0
## 169 1 0 1
## 170 0 0 0
## 171 1 0 1
## 172 1 0 1
## 173 1 0 0
## 174 1 0 0
## 175 1 1 0
## 176 1 1 0
## 177 1 0 0
## 178 1 0 1
## 179 1 0 1
## 180 0 1 0
## 181 1 1 0
## 182 0 1 0
## 183 0 1 0
## 184 0 1 0
## 185 1 1 0
## 186 1 1 0
## 187 0 1 0
## 188 0 1 0
## 189 0 1 0
## 190 0 1 0
## 191 1 1 0
## 192 1 0 1
## 193 1 0 1
## 194 1 0 1
## 195 0 1 0
## 196 1 0 1
## 197 1 0 0
## 198 1 0 1
## 199 1 0 1
## 200 0 0 1
## 201 0 1 0
## 202 0 1 0
## 203 1 0 1
## 204 0 1 0
## 205 0 0 0
## 206 0 0 0
## 207 1 1 0
## 208 1 1 0
## 209 1 0 1When we now select only those records from the result where the party_id field from parties is empty, we get exactly what we want
dbGetQuery(db, "SELECT * from parties RIGHT JOIN populist ON parlgov_id = party_id WHERE party_id IS NULL")## party_id party_name_short party_name_english country_name family_name
## 1 NA <NA> <NA> <NA> <NA>
## 2 NA <NA> <NA> <NA> <NA>
## 3 NA <NA> <NA> <NA> <NA>
## 4 NA <NA> <NA> <NA> <NA>
## 5 NA <NA> <NA> <NA> <NA>
## 6 NA <NA> <NA> <NA> <NA>
## 7 NA <NA> <NA> <NA> <NA>
## 8 NA <NA> <NA> <NA> <NA>
## 9 NA <NA> <NA> <NA> <NA>
## 10 NA <NA> <NA> <NA> <NA>
## populist farleft farright parlgov_id populist farleft farright
## 1 NA NA NA 2781 1 0 1
## 2 NA NA NA 2780 1 0 1
## 3 NA NA NA 2742 1 1 0
## 4 NA NA NA 2745 1 0 1
## 5 NA NA NA 2715 1 0 0
## 6 NA NA NA 2790 0 0 0
## 7 NA NA NA 2789 1 0 1
## 8 NA NA NA 2670 1 1 0
## 9 NA NA NA 2668 1 0 0
## 10 NA NA NA 2786 0 1 0It is not difficult to see why these parties have no match in ParlGov: they only competed in elections in 2018 or later, which is after the release date of the ParlGov version we are using.
Exercise 3: Saving Intermediate Results
Let us assume that for a new project, we want to know which election results parties achieve when they first run. For this, we need to flag those election results in elections where a party first shows up in an election. All the data we need for this is already contained in the elections table.
Write a short SQL statement that shows you the party_id and the election_date when the respective party first ran.
Create a separate table first_elections, which contains the data created in the previous step. Hint: You can create a new table from a SELECT statement using CREATE TABLE xyz AS <select statement>. This creates a copy of the result and stores it as a new table.
Finally, we want to update the elections table such that it gets a new boolean variable first_election, which indicates whether the party first ran in the given election. Hint: as described in the chapter, create the variable first, and then UPDATE it from the first_elections table.
What is the average election result that parties achieve in their first elections?
Solution
We can find out the first election that a party competed in with a simple aggregation, where we determine the smallest election date for all elections a party competed in.
dbGetQuery(db, "SELECT party_id, min(election_date) FROM elections GROUP BY party_id")## party_id min
## 1 2429 1918-04-22
## 2 1989 2011-12-04
## 3 652 2000-10-08
## 4 1560 1987-06-14
## 5 1587 1996-11-10
## 6 2574 1984-01-10
## 7 951 1991-09-15
## 8 350 1996-04-21
## 9 1108 1958-07-07
## 10 1128 1903-06-16
## 11 2425 1936-05-24
## 12 874 1990-06-09
## 13 1284 1945-07-05
## 14 2080 2012-06-17
## 15 278 1995-10-01
## 16 1691 1991-10-27
## 17 2614 2015-11-08
## 18 2466 1925-07-01
## 19 2196 2000-01-03
## 20 176 2008-04-13
## 21 1003 1947-10-27
## 22 2182 2011-12-04
## 23 1045 1992-11-15
## 24 1331 1971-09-21
## 25 1552 1994-03-27
## 26 1493 2000-01-03
## 27 417 1999-03-07
## 28 2204 2013-04-27
## 29 2642 2017-03-15
## 30 638 1999-03-21
## 31 1268 1954-04-11
## 32 791 1990-12-02
## 33 2040 2000-11-26
## 34 42 1990-04-29
## 35 1263 2002-06-16
## 36 189 1968-01-23
## 37 2631 1992-09-20
## 38 161 1981-11-08
## 39 113 1995-03-05
## 40 2420 1933-10-16
## 41 1431 1994-03-27
## 42 1595 1963-05-15
## 43 658 2002-06-16
## 44 802 1964-09-20
## 45 440 1990-06-09
## 46 1415 1990-06-09
## 47 2054 1927-07-01
## 48 2652 2017-10-15
## 49 57 2005-06-25
## 50 19 1990-06-09
## 51 1368 1993-06-06
## 52 357 1982-09-08
## 53 1194 1918-07-03
## 54 2504 1918-12-14
## 55 2366 2014-10-04
## 56 1782 1992-11-15
## 57 1463 1966-03-21
## 58 769 1945-11-25
## 59 2451 1919-10-26
## 60 1287 2001-05-13
## 61 315 1989-06-18
## 62 307 1990-05-20
## 63 366 1992-04-05
## 64 2593 1910-05-08
## 65 1644 1960-11-15
## 66 1030 1948-04-18
## 67 1239 1926-12-02
## 68 1606 1924-04-11
## 69 181 1991-10-27
## 70 743 1990-06-09
## 71 35 1902-10-26
## 72 2576 1994-09-21
## 73 2063 2015-05-07
## 74 2636 2016-10-09
## 75 220 2002-09-21
## 76 2514 1922-11-15
## 77 1602 1987-10-18
## 78 1278 2008-04-13
## 79 822 1989-06-18
## 80 2544 1909-10-25
## 81 2533 1925-03-01
## 82 2015 2012-03-10
## 83 1118 1919-03-03
## 84 596 1994-03-27
## 85 285 1986-03-16
## 86 2116 1987-04-25
## 87 50 1949-10-09
## 88 2441 1927-07-09
## 89 1319 1942-07-05
## 90 1776 1981-05-24
## 91 1104 1991-10-27
## 92 487 1953-06-07
## 93 2 2010-05-29
## 94 1942 2011-09-17
## 95 2275 1990-04-12
## 96 2296 1918-12-14
## 97 1321 1992-04-05
## 98 152 1990-05-20
## 99 534 1976-09-05
## 100 1216 1989-06-18
## 101 2140 1934-06-03
## 102 428 2003-03-02
## 103 1049 2001-05-27
## 104 637 1982-10-28
## 105 1967 1983-10-23
## 106 2575 1984-01-10
## 107 1786 1947-10-28
## 108 2581 1910-05-08
## 109 2599 2011-10-23
## 110 1006 1999-05-08
## 111 1324 1973-12-04
## 112 2333 2014-07-13
## 113 453 1918-12-14
## 114 2099 1992-09-27
## 115 2198 2003-11-23
## 116 501 2003-05-18
## 117 750 1919-10-26
## 118 1679 2011-10-09
## 119 18 1991-10-27
## 120 528 2001-09-23
## 121 1507 1953-09-06
## 122 27 1924-10-21
## 123 2586 1906-05-20
## 124 2220 1992-06-06
## 125 143 1992-12-06
## 126 1549 1991-10-27
## 127 811 1998-10-03
## 128 58 1990-03-10
## 129 228 1919-11-15
## 130 1613 2010-05-29
## 131 1616 1996-06-01
## 132 1800 1967-06-11
## 133 1629 1901-04-03
## 134 645 1977-06-15
## 135 1779 1991-05-19
## 136 1009 1995-03-19
## 137 1460 2010-06-12
## 138 2098 1992-09-27
## 139 382 2008-04-13
## 140 1804 2011-02-25
## 141 438 2010-06-13
## 142 378 1996-10-20
## 143 2526 1927-09-15
## 144 2579 1911-10-29
## 145 2178 2005-06-25
## 146 1546 2002-09-15
## 147 1050 1946-06-02
## 148 2459 1918-07-03
## 149 1619 1990-04-12
## 150 276 2000-01-03
## 151 2626 2016-05-22
## 152 1523 1996-05-26
## 153 646 1974-02-28
## 154 2452 1922-05-28
## 155 1044 2000-01-03
## 156 379 1987-02-17
## 157 1062 1983-03-21
## 158 108 1989-06-18
## 159 59 1962-11-25
## 160 1535 1983-04-25
## 161 1167 1922-10-29
## 162 2232 1990-06-09
## 163 44 1994-10-01
## 164 2171 1962-02-19
## 165 2121 2012-10-14
## 166 1144 1990-03-10
## 167 2157 2013-02-25
## 168 2150 2013-09-29
## 169 816 1947-10-27
## 170 2598 1987-04-25
## 171 1447 1996-10-20
## 172 2302 1946-06-02
## 173 1987 2011-12-04
## 174 676 2008-10-12
## 175 944 1918-07-03
## 176 419 1990-05-20
## 177 664 1991-10-27
## 178 2601 2015-10-25
## 179 2218 1997-06-06
## 180 2235 1992-06-06
## 181 551 2008-03-09
## 182 2214 2013-05-12
## 183 945 1976-09-05
## 184 1371 1990-04-12
## 185 948 1990-05-20
## 186 95 1994-05-29
## 187 1951 1919-10-26
## 188 259 2002-06-16
## 189 519 1992-11-15
## 190 1203 1990-04-08
## 191 1250 2010-05-06
## 192 409 1946-06-02
## 193 840 1996-04-21
## 194 30 1922-06-22
## 195 2567 2015-06-18
## 196 521 1998-10-03
## 197 2303 1919-10-26
## 198 2109 2012-09-12
## 199 1378 1978-12-17
## 200 3 1948-07-07
## 201 936 1992-06-06
## 202 2605 2015-12-20
## 203 981 1992-12-06
## 204 2273 2017-06-18
## 205 2149 1919-10-26
## 206 921 1990-04-08
## 207 1221 1977-06-15
## 208 2465 1933-04-26
## 209 255 1990-12-02
## 210 1295 1987-07-19
## 211 1126 1974-02-28
## 212 209 1989-09-06
## 213 984 1947-10-27
## 214 639 1945-07-05
## 215 74 1984-01-10
## 216 138 1978-03-19
## 217 418 1990-04-08
## 218 2538 1931-06-07
## 219 947 1945-10-21
## 220 978 1973-12-04
## 221 972 2007-06-10
## 222 407 1998-09-20
## 223 90 2003-11-23
## 224 2130 2012-12-09
## 225 904 1911-09-24
## 226 2244 1992-12-06
## 227 443 1986-06-22
## 228 2597 2015-10-04
## 229 2005 1953-06-28
## 230 2329 2002-05-17
## 231 526 1993-06-06
## 232 2531 1925-03-01
## 233 1209 1973-09-09
## 234 1477 2013-02-25
## 235 2594 1910-05-08
## 236 393 1962-02-19
## 237 524 1990-12-02
## 238 118 1977-06-15
## 239 317 1990-04-12
## 240 793 1915-10-21
## 241 702 1915-10-21
## 242 488 2001-05-13
## 243 2255 2013-09-29
## 244 1048 1994-03-27
## 245 1101 2002-06-16
## 246 2230 1974-11-17
## 247 1620 2010-06-12
## 248 360 1992-02-22
## 249 463 1977-11-20
## 250 99 1994-03-27
## 251 2096 2012-05-06
## 252 1570 1994-12-18
## 253 629 1991-10-27
## 254 2156 2013-02-25
## 255 2093 2012-05-06
## 256 374 1989-06-18
## 257 1544 1991-10-27
## 258 1632 1996-10-20
## 259 1210 1974-02-28
## 260 224 1975-10-26
## 261 756 1989-09-06
## 262 318 1922-06-22
## 263 2105 1996-11-03
## 264 2224 1996-06-01
## 265 647 1900-07-01
## 266 442 2002-06-16
## 267 2254 2013-09-09
## 268 1296 1976-06-20
## 269 1792 1992-09-20
## 270 2143 1997-06-01
## 271 2087 1996-11-03
## 272 1120 1992-09-27
## 273 1976 2011-11-20
## 274 2486 1939-04-02
## 275 234 1993-06-06
## 276 657 1911-09-24
## 277 474 1918-10-21
## 278 2477 1924-09-21
## 279 675 1990-03-10
## 280 748 1991-11-24
## 281 1666 2010-10-02
## 282 696 1991-10-27
## 283 2557 1921-09-26
## 284 969 1965-05-23
## 285 1179 2004-03-07
## 286 270 1986-03-16
## 287 898 1956-01-02
## 288 384 1993-06-06
## 289 351 1973-09-09
## 290 1713 1948-04-18
## 291 2447 1922-06-22
## 292 2318 2014-04-06
## 293 2475 1924-09-21
## 294 1974 2011-11-20
## 295 2535 1928-06-03
## 296 2654 2017-10-21
## 297 1627 2000-01-03
## 298 2114 1991-04-20
## 299 1226 1995-10-22
## 300 2037 1996-10-20
## 301 491 2006-05-21
## 302 2556 1917-09-16
## 303 2053 1991-10-27
## 304 1272 2001-06-07
## 305 1563 1994-10-01
## 306 436 1994-03-27
## 307 180 2007-10-19
## 308 2501 1918-12-14
## 309 2193 2011-12-04
## 310 967 1945-10-21
## 311 870 1990-04-08
## 312 1219 1919-03-03
## 313 2160 2002-09-21
## 314 784 1997-04-19
## 315 780 1997-09-16
## 316 1565 1930-10-01
## 317 2632 2016-10-29
## 318 2243 1992-12-06
## 319 838 1993-09-19
## 320 2034 1949-08-14
## 321 2292 1933-07-01
## 322 280 1922-06-22
## 323 1057 2005-05-05
## 324 2155 2013-02-25
## 325 1328 1910-05-08
## 326 2536 1925-03-01
## 327 650 1993-03-28
## 328 226 2006-04-09
## 329 210 1999-05-08
## 330 2413 2015-04-19
## 331 710 1997-04-19
## 332 1016 1998-09-26
## 333 2428 1935-10-22
## 334 81 1973-09-09
## 335 1015 1990-05-20
## 336 1661 2001-05-13
## 337 2129 2012-12-09
## 338 1218 1992-06-06
## 339 1639 1990-05-20
## 340 2440 1919-11-15
## 341 2267 1999-03-07
## 342 2455 1934-06-03
## 343 800 1991-10-13
## 344 1495 1994-10-01
## 345 2153 2013-02-25
## 346 2181 2011-12-04
## 347 5 2008-11-30
## 348 1077 1989-06-18
## 349 456 2002-05-15
## 350 1608 2002-10-05
## 351 1281 1975-04-25
## 352 531 1902-10-26
## 353 1429 1986-11-23
## 354 1409 1946-05-17
## 355 2453 1919-10-26
## 356 546 1990-04-08
## 357 686 1924-05-25
## 358 1173 1992-06-06
## 359 1261 1996-10-20
## 360 1508 1975-04-25
## 361 974 1958-05-25
## 362 2583 1932-05-08
## 363 2227 1979-03-01
## 364 829 1992-06-06
## 365 1557 1993-06-06
## 366 2395 2016-12-11
## 367 1149 1997-04-19
## 368 1370 1992-12-06
## 369 2570 1990-04-12
## 370 2290 1919-10-26
## 371 93 1946-06-02
## 372 373 1994-03-27
## 373 723 1968-03-31
## 374 2448 1937-06-06
## 375 1086 1945-10-21
## 376 2078 2012-06-17
## 377 609 1999-03-07
## 378 2474 1921-10-24
## 379 1286 1991-10-13
## 380 2288 1922-07-05
## 381 1137 1995-03-05
## 382 199 1990-04-12
## 383 369 1991-10-27
## 384 1374 1989-06-18
## 385 441 1990-05-20
## 386 243 1992-09-20
## 387 2412 2011-04-17
## 388 1744 1994-10-09
## 389 1264 1935-10-27
## 390 902 1977-06-15
## 391 1142 1992-06-06
## 392 1047 1990-04-12
## 393 2036 1996-10-20
## 394 2620 2016-02-26
## 395 2195 2011-12-04
## 396 892 1911-09-24
## 397 2027 1999-06-13
## 398 856 2000-10-08
## 399 1083 1993-06-06
## 400 1154 1982-09-19
## 401 2295 2000-01-03
## 402 987 2001-09-23
## 403 1451 1918-07-03
## 404 2602 2015-10-25
## 405 2199 2000-01-03
## 406 865 2009-09-27
## 407 1246 2006-04-09
## 408 2257 2013-10-20
## 409 2209 2013-04-27
## 410 2175 1991-10-13
## 411 1206 2002-05-15
## 412 1200 2002-09-21
## 413 1010 1964-09-20
## 414 2177 1994-12-18
## 415 795 2010-05-29
## 416 809 1992-04-05
## 417 2479 1931-10-27
## 418 585 1917-10-02
## 419 1556 1918-12-14
## 420 708 1925-08-05
## 421 370 1979-03-01
## 422 713 1992-09-27
## 423 1592 2004-03-07
## 424 1205 1935-11-14
## 425 701 1919-10-26
## 426 1395 1995-03-05
## 427 1092 1966-03-31
## 428 514 1975-04-25
## 429 1414 1951-06-17
## 430 2525 1923-08-27
## 431 1234 1945-10-21
## 432 2346 2015-01-25
## 433 2600 2015-10-25
## 434 2097 2012-05-06
## 435 1056 2008-10-12
## 436 2422 1900-05-27
## 437 863 1996-10-20
## 438 2382 2015-11-08
## 439 1299 1990-05-20
## 440 2247 1992-06-06
## 441 65 1990-04-12
## 442 2571 1990-04-12
## 443 1202 1990-04-08
## 444 693 2001-05-13
## 445 2450 1919-10-26
## 446 2349 2014-04-06
## 447 744 2002-05-15
## 448 2542 1933-04-26
## 449 527 1994-10-01
## 450 1462 1964-06-07
## 451 235 1977-05-25
## 452 627 1996-04-21
## 453 2612 2015-11-08
## 454 306 1990-12-12
## 455 376 2007-11-13
## 456 147 1983-04-23
## 457 2473 1900-07-01
## 458 2634 2016-10-09
## 459 412 1939-04-03
## 460 2432 1932-11-16
## 461 2442 1933-01-24
## 462 26 1902-10-26
## 463 564 1967-10-29
## 464 535 2005-06-25
## 465 1960 1925-10-25
## 466 1260 1990-04-29
## 467 2603 2015-12-20
## 468 77 1992-06-06
## 469 725 1975-04-25
## 470 131 2002-09-21
## 471 310 1979-06-10
## 472 1001 1993-06-06
## 473 806 2003-03-16
## 474 353 1991-10-13
## 475 831 1948-04-18
## 476 2024 2010-05-29
## 477 1298 1947-10-27
## 478 574 1992-06-06
## 479 2293 1939-07-01
## 480 1335 1906-05-27
## 481 1496 1950-02-23
## 482 2263 2013-10-25
## 483 1446 1990-03-10
## 484 1375 2005-06-25
## 485 134 1923-10-27
## 486 1297 1945-10-21
## 487 2508 1918-12-14
## 488 2619 2016-02-26
## 489 2527 1919-10-26
## 490 1790 1977-06-15
## 491 2133 2003-11-23
## 492 1292 1922-07-03
## 493 915 2003-05-18
## 494 207 1993-09-19
## 495 1783 1992-11-15
## 496 979 1995-03-19
## 497 2135 2007-11-25
## 498 2103 1997-05-01
## 499 1418 1998-03-11
## 500 1180 1949-08-14
## 501 2123 1981-10-18
## 502 385 2004-03-14
## 503 1758 1991-10-27
## 504 2375 2015-12-20
## 505 544 2001-06-18
## 506 2163 1989-11-05
## 507 2563 1922-06-22
## 508 2134 2000-01-03
## 509 1667 2010-10-02
## 510 618 1946-06-02
## 511 308 2007-10-21
## 512 313 1998-09-26
## 513 1072 1990-06-09
## 514 1080 1983-06-26
## 515 2364 2014-10-05
## 516 248 1947-10-27
## 517 137 1949-08-14
## 518 614 1974-11-17
## 519 2399 2017-06-18
## 520 191 2004-10-24
## 521 94 1917-10-02
## 522 1539 1902-05-11
## 523 1500 1991-10-20
## 524 671 1994-03-27
## 525 1131 1949-08-14
## 526 2110 1918-07-03
## 527 742 1946-05-17
## 528 2623 2016-03-06
## 529 912 1949-08-14
## 530 1502 2008-10-12
## 531 846 1999-03-07
## 532 1478 1966-11-22
## 533 1964 1979-10-21
## 534 1158 1989-06-18
## 535 87 1971-04-28
## 536 1580 1992-11-25
## 537 550 1998-10-03
## 538 2407 2015-01-25
## 539 2640 2017-03-26
## 540 2431 1939-04-03
## 541 2564 1921-11-20
## 542 1362 2001-06-18
## 543 2414 1933-10-16
## 544 2606 2015-12-20
## 545 2595 2013-05-12
## 546 406 2010-05-29
## 547 2094 2012-05-06
## 548 2443 1927-06-09
## 549 309 1995-10-01
## 550 1573 1989-06-15
## 551 509 1978-03-19
## 552 2365 2014-10-04
## 553 2055 1945-11-25
## 554 2423 1900-05-27
## 555 156 1944-05-30
## 556 1599 1918-12-14
## 557 1894 1987-09-08
## 558 1273 1975-04-25
## 559 2463 1937-05-26
## 560 1408 1990-03-10
## 561 1475 1946-06-02
## 562 2298 1943-06-23
## 563 888 1990-05-20
## 564 282 1964-09-20
## 565 1781 2011-06-05
## 566 733 1993-09-13
## 567 1594 1981-11-08
## 568 2523 1922-06-22
## 569 257 2002-10-05
## 570 2434 1917-10-02
## 571 1426 1990-04-08
## 572 2300 1904-05-29
## 573 587 2009-04-25
## 574 2592 1902-05-11
## 575 2115 2007-05-12
## 576 349 1970-06-18
## 577 187 1949-08-14
## 578 1119 1983-03-21
## 579 506 1999-05-08
## 580 2644 2017-06-18
## 581 1501 2006-11-22
## 582 242 1948-04-18
## 583 599 1991-10-13
## 584 2655 2017-01-01
## 585 1576 1987-03-16
## 586 2291 1975-04-25
## 587 2633 2016-10-29
## 588 1954 1925-10-25
## 589 1461 1914-09-13
## 590 1543 1979-12-02
## 591 1028 1946-06-02
## 592 29 1990-06-09
## 593 466 2002-10-05
## 594 2568 2001-11-20
## 595 2643 2017-06-18
## 596 1617 1990-04-08
## 597 2439 1934-06-24
## 598 2176 1994-12-18
## 599 2059 1953-06-07
## 600 2028 1986-06-22
## 601 1778 1981-05-24
## 602 107 1979-12-02
## 603 2265 1978-03-19
## 604 6 1996-05-26
## 605 1163 1995-10-01
## 606 2628 2015-11-08
## 607 920 1929-07-01
## 608 1058 2007-06-17
## 609 219 2007-03-04
## 610 363 2013-10-25
## 611 1432 1990-06-09
## 612 66 1948-07-02
## 613 512 2001-09-23
## 614 1213 2011-10-23
## 615 873 1978-03-19
## 616 356 1992-09-27
## 617 523 1961-10-04
## 618 1311 1977-11-20
## 619 458 1902-10-26
## 620 640 1971-11-07
## 621 1572 1947-10-27
## 622 1970 2014-04-06
## 623 1452 1959-03-12
## 624 538 1953-09-22
## 625 142 1994-03-27
## 626 2529 1925-03-01
## 627 872 1990-06-09
## 628 1140 2005-09-25
## 629 2211 2013-05-12
## 630 792 1990-05-20
## 631 896 1994-03-27
## 632 651 1987-02-17
## 633 2044 1946-06-02
## 634 1005 2001-05-13
## 635 597 1977-11-20
## 636 1435 1900-07-01
## 637 1012 1971-10-31
## 638 2488 1900-05-27
## 639 548 1991-10-27
## 640 217 1953-12-14
## 641 1457 2000-01-03
## 642 709 2000-10-08
## 643 47 1974-11-17
## 644 177 1978-03-19
## 645 1105 1995-03-19
## 646 886 1902-10-26
## 647 2180 2005-06-25
## 648 1332 2004-11-28
## 649 2430 1901-04-03
## 650 1601 1990-05-20
## 651 2101 1990-06-09
## 652 1276 1923-12-06
## 653 2092 2012-05-06
## 654 1710 1993-09-19
## 655 1251 1922-07-05
## 656 2173 1991-10-13
## 657 557 1999-10-10
## 658 1712 1997-09-21
## 659 1081 2011-12-04
## 660 229 1987-03-16
## 661 559 1990-06-09
## 662 1102 2000-01-03
## 663 2338 2014-07-13
## 664 2106 1996-11-03
## 665 52 2006-04-09
## 666 633 1961-09-17
## 667 341 1985-10-06
## 668 1506 1998-09-27
## 669 450 1992-09-20
## 670 900 1991-10-27
## 671 405 1918-07-03
## 672 2241 1992-12-06
## 673 996 1997-04-19
## 674 2246 1992-06-06
## 675 1740 1983-04-24
## 676 1063 2006-04-09
## 677 473 1992-04-05
## 678 2170 1994-12-18
## 679 1059 1989-06-18
## 680 1575 1976-09-05
## 681 375 1980-10-05
## 682 983 2004-10-24
## 683 2264 1986-03-16
## 684 2415 1903-09-16
## 685 1160 1991-10-13
## 686 689 1918-12-14
## 687 1338 1974-11-17
## 688 11 1979-12-02
## 689 1330 1946-06-02
## 690 1384 2000-01-03
## 691 1492 1973-03-11
## 692 401 1902-05-11
## 693 2191 2011-12-04
## 694 1701 1961-09-17
## 695 659 1918-12-14
## 696 2238 1994-10-01
## 697 2233 1990-06-09
## 698 1094 1946-11-10
## 699 1110 1971-11-07
## 700 1743 1966-03-06
## 701 543 1949-08-14
## 702 2578 1927-10-17
## 703 565 1980-10-05
## 704 1562 1992-11-15
## 705 566 2009-07-05
## 706 457 1999-06-13
## 707 281 1995-10-01
## 708 1440 1978-12-17
## 709 2245 1996-11-10
## 710 1520 2002-10-05
## 711 2457 1918-07-03
## 712 2294 1979-03-01
## 713 288 1990-04-29
## 714 21 1990-03-10
## 715 622 1980-10-05
## 716 927 2006-04-09
## 717 1027 1977-11-20
## 718 1593 1968-05-19
## 719 2641 2017-03-15
## 720 2266 1999-03-07
## 721 151 1922-05-28
## 722 2252 2002-06-16
## 723 620 1999-06-13
## 724 894 1977-06-15
## 725 205 1995-04-08
## 726 2615 2015-11-08
## 727 67 1994-03-27
## 728 1377 1977-06-15
## 729 2084 1977-06-15
## 730 1638 1973-09-09
## 731 2222 1992-06-06
## 732 1017 1922-11-15
## 733 1794 1995-03-05
## 734 334 1991-10-27
## 735 2216 1923-08-27
## 736 558 1949-08-14
## 737 2651 2017-10-15
## 738 1538 1933-10-16
## 739 2221 1992-06-06
## 740 1051 1946-02-17
## 741 1739 1983-04-24
## 742 1424 1976-06-20
## 743 2462 1918-07-03
## 744 2417 1995-10-01
## 745 1109 1954-03-08
## 746 167 1946-02-17
## 747 2625 2015-10-25
## 748 2261 2013-10-25
## 749 1777 1981-05-24
## 750 290 1949-06-26
## 751 300 1918-07-03
## 752 662 2006-10-07
## 753 1254 2009-07-05
## 754 2368 2014-05-25
## 755 342 1971-04-28
## 756 2367 2014-10-04
## 757 2058 1918-04-22
## 758 1441 1989-06-18
## 759 2449 1937-06-06
## 760 2217 1982-02-18
## 761 367 1987-09-08
## 762 2458 1918-07-03
## 763 2481 1900-05-27
## 764 1019 1993-09-19
## 765 41 2017-09-24
## 766 2091 2012-05-06
## 767 1773 1961-09-11
## 768 1389 1975-09-22
## 769 586 2010-06-12
## 770 32 1993-06-06
## 771 53 1971-06-13
## 772 572 1976-09-05
## 773 2478 1932-09-18
## 774 2317 2010-06-12
## 775 336 2010-05-29
## 776 275 1982-09-08
## 777 1735 2006-05-21
## 778 260 1975-04-25
## 779 1412 1993-10-10
## 780 2635 2016-10-09
## 781 423 1991-10-13
## 782 139 1975-09-22
## 783 2649 2017-09-24
## 784 1621 1996-05-26
## 785 1946 2008-09-21
## 786 411 1986-06-22
## 787 1033 1950-09-04
## 788 1537 1965-09-19
## 789 425 1990-05-20
## 790 2083 1993-03-28
## 791 825 1976-06-20
## 792 1040 2008-04-13
## 793 316 2003-11-23
## 794 403 2010-04-25
## 795 2639 2017-03-26
## 796 1597 2007-03-04
## 797 2219 1992-06-06
## 798 256 1974-03-10
## 799 332 1918-12-14
## 800 628 1967-10-29
## 801 885 1973-03-11
## 802 2095 2012-05-06
## 803 380 2004-10-03
## 804 1275 1997-09-21
## 805 1339 1979-06-10
## 806 2648 2016-12-11
## 807 1465 2000-01-03
## 808 486 1992-09-20
## 809 625 1952-06-25
## 810 2341 2016-05-22
## 811 1521 2014-09-14
## 812 1277 2004-10-24
## 813 2253 2013-09-22
## 814 1541 2009-07-05
## 815 552 1992-09-20
## 816 887 1992-11-15
## 817 1772 1979-12-02
## 818 2607 2015-12-20
## 819 817 1987-04-25
## 820 1274 1950-09-04
## 821 1891 2001-11-20
## 822 433 1927-06-09
## 823 364 1991-10-13
## 824 2518 1931-10-27
## 825 2561 1919-11-15
## 826 1123 1992-06-06
## 827 1113 2003-05-18
## 828 345 1967-02-15
## 829 92 1948-02-04
## 830 1317 1979-03-01
## 831 1702 1963-04-28
## 832 2363 2014-10-05
## 833 1759 2010-06-12
## 834 910 1992-04-05
## 835 437 2006-04-09
## 836 957 1933-07-01
## 837 998 1980-10-05
## 838 2433 1917-10-02
## 839 135 1977-06-15
## 840 2362 2014-10-05
## 841 2334 2014-07-13
## 842 2624 2016-03-06
## 843 493 1996-10-20
## 844 1064 1992-09-20
## 845 665 1990-06-09
## 846 454 1971-11-07
## 847 1536 2006-10-01
## 848 759 1995-10-01
## 849 2270 2003-11-23
## 850 2077 2007-06-17
## 851 482 2008-10-12
## 852 1547 1983-06-09
## 853 311 1955-05-26
## 854 2590 1902-05-11
## 855 73 1989-10-29
## 856 2646 2016-12-11
## 857 2406 1948-09-19
## 858 2179 2005-06-25
## 859 1252 1990-04-12
## 860 875 1990-04-12
## 861 2621 2016-02-26
## 862 239 1993-06-06
## 863 1651 1990-06-09
## 864 1407 1979-03-01
## 865 2418 1995-10-01
## 866 845 1989-10-29
## 867 196 1990-06-09
## 868 2158 2013-02-25
## 869 15 2001-06-18
## 870 2194 1949-10-10
## 871 2376 2015-12-20
## 872 2461 1922-07-05
## 873 1361 1977-06-15
## 874 2100 1912-10-21
## 875 1367 2000-03-12
## 876 2627 2016-05-22
## 877 1043 2002-10-05
## 878 602 1919-10-26
## 879 1192 1968-03-31
## 880 2122 2012-10-14
## 881 1088 1946-06-02
## 882 2206 2013-04-27
## 883 2604 2015-12-20
## 884 721 2010-09-19
## 885 2584 1932-05-08
## 886 251 1975-04-25
## 887 1100 2006-10-07
## 888 2112 1974-06-30
## 889 104 1900-07-01
## 890 1436 1992-04-05
## 891 2379 2014-10-05
## 892 171 1991-11-24
## 893 1591 1990-04-08
## 894 1516 1992-09-20
## 895 1988 2011-12-04
## 896 2464 1925-07-01
## 897 397 1994-03-27
## 898 2381 2015-12-20
## 899 2569 1936-05-24
## 900 427 1979-12-02
## 901 179 1990-04-12
## 902 562 1992-09-27
## 903 2079 1956-01-02
## 904 2210 2013-04-27
## 905 1945 2011-10-09
## 906 1455 1919-11-15
## 907 636 2009-07-05
## 908 2223 1992-06-06
## 909 2424 1919-11-16
## 910 1518 2002-10-05
## 911 2456 1925-03-01
## 912 2622 2016-02-26
## 913 402 1971-04-28
## 914 2573 1992-12-06
## 915 889 1987-07-19
## 916 2104 1996-11-03
## 917 467 1987-06-11
## 918 1746 2008-09-28
## 919 2004 1942-07-05
## 920 2503 1918-12-14
## 921 581 2004-10-24
## 922 109 1997-09-21
## 923 2174 1991-10-13
## 924 2237 1994-10-01
## 925 2262 2013-10-25
## 926 2248 1992-06-06
## 927 1566 1983-06-09
## 928 1134 1974-05-26
## 929 1323 1981-06-11
## 930 2405 1932-09-18
## 931 1714 1946-06-02
## 932 1358 1990-04-08
## 933 2041 2000-11-26
## 934 600 2010-04-25
## 935 2647 2016-12-11
## 936 2287 1991-10-27
## 937 772 1980-10-05
## 938 2019 2004-10-03
## 939 335 1946-06-02
## 940 383 1992-11-15
## 941 808 1949-08-14
## 942 2638 2017-03-26
## 943 1011 1989-06-18
## 944 757 1977-06-15
## 945 1768 2011-05-22
## 946 2154 2013-02-25
## 947 1360 1956-06-24
## 948 2088 1996-11-03
## 949 952 2006-04-09
## 950 432 1992-11-15
## 951 1582 1989-06-18
## 952 619 1991-10-27
## 953 2239 1994-10-01
## 954 890 1946-06-02
## 955 20 2002-09-21
## 956 1061 1990-06-09
## 957 319 1974-02-28
## 958 1074 1991-10-27
## 959 1957 1935-10-27
## 960 2260 1978-03-19
## 961 2102 1990-06-09
## 962 203 1993-06-06
## 963 789 1990-06-09
## 964 1181 1946-02-17
## 965 267 1995-10-01
## 966 773 1918-12-14
## 967 1491 1967-03-12
## 968 1023 1974-02-28
## 969 1172 1953-06-28
## 970 2192 2000-01-03
## 971 2249 2002-06-16
## 972 861 1992-09-27
## 973 1208 1991-10-27
## 974 2517 1931-10-27
## 975 2269 1958-11-30
## 976 1183 1990-04-08
## 977 2500 1918-12-14
## 978 797 1987-09-08
## 979 1633 1946-06-02
## 980 2013 2012-03-10
## 981 2408 2015-01-25
## 982 451 1946-05-17
## 983 1892 1984-01-10
## 984 1363 1974-02-28
## 985 1634 1945-10-30
## 986 399 2006-04-09
## 987 238 1953-09-06
## 988 112 1994-05-03
## 989 1042 1995-10-01
## 990 613 1983-06-26
## 991 1745 1999-10-03
## 992 569 1983-10-23
## 993 200 1962-02-05
## 994 988 1983-04-23
## 995 2289 1949-08-14
## 996 2596 2015-09-20
## 997 2472 1909-10-25
## 998 2212 2013-05-12
## 999 964 1975-10-26
## 1000 601 1975-04-25
## 1001 2585 1902-05-11
## 1002 1623 1990-03-10
## 1003 590 1901-04-03
## 1004 82 1991-10-06
## 1005 2197 2000-01-03
## 1006 2337 2014-07-13
## 1007 990 2003-01-22
## 1008 202 1979-06-10
## 1009 2203 2000-01-03
## 1010 973 1945-11-25
## 1011 610 1949-08-14
## 1012 1532 2002-06-15
## 1013 2056 2008-03-09
## 1014 812 1920-09-21
## 1015 1448 1992-09-20
## 1016 1176 1973-03-11
## 1017 2587 1902-05-11
## 1018 198 1990-04-08
## 1019 212 1977-05-25
## 1020 28 1992-11-15
## 1021 2409 2015-03-01
## 1022 1290 1980-10-05
## 1023 1796 1995-03-05
## 1024 1656 2002-10-05
## 1025 2208 2013-04-27
## 1026 720 1996-09-22
## 1027 2588 1994-05-29
## 1028 489 1974-11-17
## 1029 1280 2007-09-16
## 1030 1029 1978-12-17
## 1031 1605 1901-04-03
## 1032 1000 1990-06-09
## 1033 1245 1990-06-09
## 1034 706 1990-04-12
## 1035 2205 2013-04-27
## 1036 1393 1937-07-01
## 1037 63 1900-05-27
## 1038 2139 1954-05-30
## 1039 2437 1931-06-12
## 1040 516 1962-11-25
## 1041 956 2002-10-05
## 1042 395 1917-10-02
## 1043 1294 2006-10-07
## 1044 958 2008-11-30
## 1045 882 1920-09-17
## 1046 213 1973-03-11
## 1047 955 1994-10-09
## 1048 1304 1987-06-14
## 1049 1590 1995-03-05
## 1050 801 2006-10-07
## 1051 669 2006-10-01
## 1052 2416 1992-09-20
## 1053 1512 2000-10-15
## 1054 221 2007-06-10
## 1055 752 2006-06-17
## 1056 2002 2012-12-09
## 1057 2589 1902-05-11
## 1058 1013 1945-11-25
## 1059 688 1998-06-20
## 1060 1553 1992-09-20
## 1061 460 1968-03-31
## 1062 851 1976-09-05
## 1063 16 1990-04-12
## 1064 968 1979-06-03
## 1065 2202 2000-01-03
## 1066 62 1977-06-15
## 1067 2052 1919-11-16
## 1068 2213 2013-05-12
## 1069 326 2008-09-21
## 1070 1318 1979-10-21
## 1071 993 1978-12-17
## 1072 2256 2013-10-20
## 1073 2111 1971-06-13
## 1074 649 1949-08-14
## 1075 2637 2017-03-26
## 1076 2165 2015-01-25
## 1077 1225 1962-02-19
## 1078 2039 2000-10-08
## 1079 1342 1923-10-27
## 1080 421 1958-05-25
## 1081 2539 1918-07-03
## 1082 25 1992-09-20
## 1083 141 1979-10-21
## 1084 1355 1997-09-21
## 1085 648 1990-05-20
## 1086 2207 2013-04-27
## 1087 923 1943-06-23
## 1088 1026 1977-06-15
## 1089 434 1990-04-08
## 1090 211 1906-05-29
## 1091 1420 1949-08-14
## 1092 1014 1997-06-06
## 1093 928 1993-06-06
## 1094 465 2013-02-25
## 1095 64 2002-09-21
## 1096 520 1996-03-03
## 1097 1421 2004-10-24
## 1098 2185 2015-11-08
## 1099 55 1979-06-03
## 1100 148 1997-09-21
## 1101 828 1987-04-25We turn this result into new table, to use it later:
dbExecute(db, "CREATE TABLE first_elections AS SELECT party_id, min(election_date) AS election_date FROM elections GROUP BY party_id")## [1] 1101The new column first_election will be our new variable. The default value is false, since most election results are not first elections for the respective party.
dbExecute(db, "ALTER TABLE elections ADD COLUMN first_election BOOLEAN DEFAULT false")## [1] 0We now update the old table from the new one. For all records in elections that have a match in first_elections, we set first_election = true.
dbExecute(db, "UPDATE elections SET first_election = true FROM first_elections
WHERE elections.party_id = first_elections.party_id AND elections.election_date = first_elections.election_date")## [1] 1101Finally, we compute the average vote share that parties achieve in their first elections:
dbGetQuery(db, "SELECT avg(vote_share) FROM elections WHERE first_election = true")## avg
## 1 7.295471Exercise 4: Cascading
Our elections table depends on parties, since the latter contains information on the parties participating in the elections. This is why in the chapter, we have defined party_id to be a foreign key in election, since it points to the parties table. As we have seen in the chapter, the database system ensures that these relationships remain intact at the level of the individual records - for example, a record that is still referenced from the other table cannot be deleted. However, the DBMS also maintains these relationships at the level of entire tables. Try deleting the parties table with DROP TABLE. What happens, and why?
PostgreSQL gives you a specific recommendation for dealing with this problem: using the CASCADE keyword. Change your statement such that is uses this keyword. What exactly does it do? Does it delete the elections table? If not, what do you think has happened?
Solution
Simply deleting the entire parties table does not work, since elections depends on it:
dbExecute(db, "DROP TABLE parties")## Error:
## ! Failed to fetch row : ERROR: cannot drop table parties because other objects depend on it
## DETAIL: constraint elections_party_id_fkey on table elections depends on table parties
## HINT: Use DROP ... CASCADE to drop the dependent objects too.However, the CASCADE keyword solves this, and the table is successfully deleted:
dbExecute(db, "DROP TABLE parties CASCADE")## NOTICE: drop cascades to constraint elections_party_id_fkey on table elections## [1] 0What has happened? The parties table is gone, but elections is still there:
dbListTables(db)## [1] "elections" "first_elections" "populist"The database system has removed the foreign key in elections, since this key would have pointed to a table that no longer exists. This automatic resolution of dependencies is called “cascading”, and is also exists at the level of individual records.