################################
# 1993 

# We transform the data from columns to rows:

tab <- data.frame(code_dep = don_leg_1993$code_dep,
                  code_circ = don_leg_1993$code_circ,
                  score = don_leg_1993$voix_1,  
                  exprimes = don_leg_1993$voix_1/don_leg_1993$exprimes,
                  inscrits = don_leg_1993$voix_1/don_leg_1993$inscrits,
                  parti = don_leg_1993$nuance_1)
j <- 2
while(j <= 18) {
  tab <- rbind(tab,
               data.frame(code_dep = don_leg_1993$code_dep,
                          code_circ = don_leg_1993$code_circ,
                          score = don_leg_1993[[paste0("voix_", j)]],
                          exprimes = don_leg_1993[[paste0("voix_", j)]]/don_leg_1993$exprimes,
                          inscrits = don_leg_1993[[paste0("voix_", j)]]/don_leg_1993$inscrits,
                          parti = don_leg_1993[[paste0("nuance_", j)]])
  )
  j <- j + 1
}
tab <- na.omit(tab)
parti_1993 <- aggregate(tab$score, by = list(parti = tab$parti), sum)
winner_1993 <- tab[tab$exprimes > 0.5 & tab$inscrits > 0.25, 
                   c("code_dep", "code_circ", "parti")]

# We import the data basis related to the 2nd tour:
don_leg_1993_T2 <- readxl::read_xls(path = "base_1993.xls", 
                                    sheet = "Circo leg T2",
                                    col_names = nom_var_1993[1:36],
                                    skip = 1)

# We look who are the winners for each row :

winner_1993_T2 <- don_leg_1993_T2[, c("code_dep", "code_circ")]
vec_T2 <- apply(don_leg_1993_T2[, c("voix_1", "voix_2", "voix_3")], 
                1, which.max)
winner_1993_T2$parti <- character(nrow(don_leg_1993_T2))
for (k in 1:nrow(don_leg_1993_T2)) {
  winner_1993_T2$parti[k] <- as.character(don_leg_1993_T2[k, 
                                                          c("nuance_1", "nuance_2", "nuance_3")[vec_T2[k]]])
}
winner_1993 <- rbind(winner_1993,
                     winner_1993_T2)

tab_93 <- table(winner_1993$parti)
tab_93 <- data.frame(parti = names(tab_93),
                     deputes = as.numeric(tab_93))

# We merge the score of the parties and the number of representants
parti_1993 <- merge(parti_1993, tab_93, by = "parti")


# We look at the ratio representative/electors :
parti_1993$ratio <- parti_1993$deputes / parti_1993$x


# We rank the parties with respect to the ratio and the number of inhabitants
parti_1993 <- parti_1993[order(parti_1993$ratio, -parti_1993$x), ]

parti_1993$rank <- 1:nrow(parti_1993)
parti_1993$year <- "1993" 
parti_1993$p <- cumsum(parti_1993$deputes)/sum(parti_1993$deputes)
parti_1993$L <- cumsum(parti_1993$x)/sum(parti_1993$x)
parti_1993$ratio_parti <- parti_1993$x / sum(parti_1993$x)
parti_1993$ratio_siege <- parti_1993$deputes/ sum(parti_1993$deputes)


################################
# 1997

# We transform the data from columns to rows:

tab <- data.frame(code_dep = don_leg_1997$code_dep,
                  code_circ = don_leg_1997$code_circ,
                  score = don_leg_1997$voix_1,  
                  exprimes = don_leg_1997$voix_1/don_leg_1997$exprimes,
                  inscrits = don_leg_1997$voix_1/don_leg_1997$inscrits,
                  parti = don_leg_1997$nuance_1)
j <- 2
while(j <= 29) {
  tab <- rbind(tab,
               data.frame(code_dep = don_leg_1997$code_dep,
                          code_circ = don_leg_1997$code_circ,
                          score = don_leg_1997[[paste0("voix_", j)]],
                          exprimes = don_leg_1997[[paste0("voix_", j)]]/don_leg_1997$exprimes,
                          inscrits = don_leg_1997[[paste0("voix_", j)]]/don_leg_1997$inscrits,
                          parti = don_leg_1997[[paste0("nuance_", j)]])
  )
  j <- j + 1
}
tab <- na.omit(tab)
parti_1997 <- aggregate(tab$score, by = list(parti = tab$parti), sum)
winner_1997 <- tab[tab$exprimes > 0.5 & tab$inscrits > 0.25, 
                   c("code_dep", "code_circ", "parti")]

# We import the data basis related to the 2nd tour:
don_leg_1997_T2 <- readxl::read_xls(path = "base_1997.xls", 
                                    sheet = "Circo leg T2",
                                    col_names = nom_var_1997[1:36], 
                                    skip = 1)


# We look who are the winners for each row :

winner_1997_T2 <- don_leg_1997_T2[, c("code_dep", "code_circ")]
vec_T2 <- apply(don_leg_1997_T2[, c("voix_1", "voix_2", "voix_3")], 
                1, which.max)
winner_1997_T2$parti <- character(nrow(don_leg_1997_T2))
for (k in 1:nrow(don_leg_1997_T2)) {
  winner_1997_T2$parti[k] <- as.character(don_leg_1997_T2[k, 
                                                          c("nuance_1", "nuance_2", "nuance_3")[vec_T2[k]]])
}
winner_1997 <- rbind(winner_1997,
                     winner_1997_T2)

tab_97 <- table(winner_1997$parti)
tab_97 <- data.frame(parti = names(tab_97),
                     deputes = as.numeric(tab_97))

# We merge the score of the parties and the number of representants
parti_1997 <- merge(parti_1997, tab_97, by = "parti")


# We look at the ratio representative/electors :
parti_1997$ratio <- parti_1997$deputes / parti_1997$x

# We rank the parties with respect to the ratio and the number of inhabitants
parti_1997 <- parti_1997[order(parti_1997$ratio, -parti_1997$x), ]

parti_1997$rank <- 1:nrow(parti_1997)
parti_1997$year <- "1997" 
parti_1997$p <- cumsum(parti_1997$deputes)/sum(parti_1997$deputes)
parti_1997$L <- cumsum(parti_1997$x)/sum(parti_1997$x)
parti_1997$ratio_parti <- parti_1997$x / sum(parti_1997$x)
parti_1997$ratio_siege <- parti_1997$deputes/ sum(parti_1997$deputes)



################################
# 2002

### Data basis related to the parties

# We transform the data from columns to rows:
tab <- data.frame(code_dep = don_leg_2002$code_dep,
                  code_circ = don_leg_2002$code_circ,
                  score = don_leg_2002$voix_1,  
                  exprimes = don_leg_2002$voix_1/don_leg_2002$exprimes,
                  inscrits = don_leg_2002$voix_1/don_leg_2002$inscrits,
                  parti = don_leg_2002$nuance_1)
j <- 2
while(j <= 27) {
  tab <- rbind(tab,
               data.frame(code_dep = don_leg_2002$code_dep,
                          code_circ = don_leg_2002$code_circ,
                          score = don_leg_2002[[paste0("voix_", j)]],
                          exprimes = don_leg_2002[[paste0("voix_", j)]]/don_leg_2002$exprimes,
                          inscrits = don_leg_2002[[paste0("voix_", j)]]/don_leg_2002$inscrits,
                          parti = don_leg_2002[[paste0("nuance_", j)]])
  )
  j <- j + 1
}
tab <- na.omit(tab)
parti_2002 <- aggregate(tab$score, by = list(parti = tab$parti), sum)
winner_2002 <- tab[tab$exprimes > 0.5 & tab$inscrits > 0.25, 
                   c("code_dep", "code_circ", "parti")]

# We import the data basis related to the 2nd tour:

don_leg_2002_T2 <- readxl::read_xls(path = "base_2002.xls", sheet = "Circo leg T2",
                                    col_names = nom_var_2002[1:36], skip = 1)


# We look who are the winners for each row :
  
winner_2002_T2 <- don_leg_2002_T2[, c("code_dep", "code_circ")]
vec_T2 <- apply(don_leg_2002_T2[, c("voix_1", "voix_2", "voix_3")], 
                1, which.max)
winner_2002_T2$parti <- character(nrow(don_leg_2002_T2))
for (k in 1:nrow(don_leg_2002_T2)) {
  winner_2002_T2$parti[k] <- as.character(don_leg_2002_T2[k, 
                                                          c("nuance_1", "nuance_2", "nuance_3")[vec_T2[k]]])
}
winner_2002 <- rbind(winner_2002,
                     winner_2002_T2)


tab_02 <- table(winner_2002$parti)
tab_02 <- data.frame(parti = names(tab_02),
                     deputes = as.numeric(tab_02))

# We merge the score of the parties and the number of representants
parti_2002 <- merge(parti_2002, tab_02, by = "parti")


# We look at the ratio representative/electors :
parti_2002$ratio <- parti_2002$deputes / parti_2002$x


# We rank the parties with respect to the ratio and the number of inhabitants
parti_2002 <- parti_2002[order(parti_2002$ratio, -parti_2002$x), ]


parti_2002$rank <- 1:nrow(parti_2002)
parti_2002$year <- "2002" 
parti_2002$p <- cumsum(parti_2002$deputes)/sum(parti_2002$deputes)
parti_2002$L <- cumsum(parti_2002$x)/sum(parti_2002$x)
parti_2002$ratio_parti <- parti_2002$x / sum(parti_2002$x)
parti_2002$ratio_siege <- parti_2002$deputes/ sum(parti_2002$deputes)


################################
# 2007

### Data basis related to the parties

# We transform the data from columns to rows:
tab <- data.frame(code_dep = don_leg_2007$code_dep,
                  code_circ = don_leg_2007$code_circ,
                  score = don_leg_2007$voix_1,  
                  exprimes = don_leg_2007$voix_1/don_leg_2007$exprimes,
                  inscrits = don_leg_2007$voix_1/don_leg_2007$inscrits,
                  parti = don_leg_2007$nuance_1)
j <- 2
while(j <= 20) {
  tab <- rbind(tab,
               data.frame(code_dep = don_leg_2007$code_dep,
                          code_circ = don_leg_2007$code_circ,
                          score = don_leg_2007[[paste0("voix_", j)]],
                          exprimes = don_leg_2007[[paste0("voix_", j)]]/don_leg_2007$exprimes,
                          inscrits = don_leg_2007[[paste0("voix_", j)]]/don_leg_2007$inscrits,
                          parti = don_leg_2007[[paste0("nuance_", j)]])
  )
  j <- j + 1
}
tab <- na.omit(tab)
parti_2007 <- aggregate(tab$score, by = list(parti = tab$parti), sum)
winner_2007 <- tab[tab$exprimes > 0.5 & tab$inscrits > 0.25, 
                   c("code_dep", "code_circ", "parti")]

# We import the data basis related to the 2nd tour:
don_leg_2007_T2 <- readxl::read_xls(path = "base_2007.xls", 
                                    sheet = "Circo leg T2",
                                    col_names = nom_var_2007[1:36], 
                                    skip = 1)
# We look who are the winners for each row :
winner_2007_T2 <- don_leg_2007_T2[, c("code_dep", "code_circ")]
vec_T2 <- apply(don_leg_2007_T2[, c("voix_1", "voix_2", "voix_3")], 
                1, which.max)
winner_2007_T2$parti <- character(nrow(don_leg_2007_T2))
for (k in 1:nrow(don_leg_2007_T2)) {
  winner_2007_T2$parti[k] <- as.character(don_leg_2007_T2[k, 
                                                          c("nuance_1", "nuance_2", "nuance_3")[vec_T2[k]]])
}
winner_2007 <- rbind(winner_2007,
                     winner_2007_T2)

tab_07 <- table(winner_2007$parti)
tab_07 <- data.frame(parti = names(tab_07),
                     deputes = as.numeric(tab_07))

# We merge the score of the parties and the number of representants
parti_2007 <- merge(parti_2007, tab_07, by = "parti")
# We look at the ratio representative/electors :
parti_2007$ratio <- parti_2007$deputes / parti_2007$x

# We rank the parties with respect to the ratio and the number of inhabitants
parti_2007 <- parti_2007[order(parti_2007$ratio, -parti_2007$x), ]

parti_2007$rank <- 1:nrow(parti_2007)
parti_2007$year <- "2007" 
parti_2007$p <- cumsum(parti_2007$deputes)/sum(parti_2007$deputes)
parti_2007$L <- cumsum(parti_2007$x)/sum(parti_2007$x)
parti_2007$ratio_parti <- parti_2007$x / sum(parti_2007$x)
parti_2007$ratio_siege <- parti_2007$deputes/ sum(parti_2007$deputes)

################################
# 2012

### Data basis related to the parties

# We compute the number of votes per party after the 1st tour. In 2012:
parti_leg_2012 <- don_leg_2012_T1 %>%
  select(parti, score) %>%
  group_by(parti) %>%
  summarize(score_2012 = sum(score))

# We compute winners after the 1st and 2nd tour
winner_leg_2012_T1 <- don_leg_2012_T1 %>%
  select(code_dep, code_circ, candidate, parti, score, exprimes, inscrits) %>%
  group_by(code_dep, code_circ, parti, candidate) %>%
  summarize(score = sum(score),
            exprimes = sum(exprimes),
            inscrits = sum(inscrits))

winner_leg_2012_T1 <- winner_leg_2012_T1 %>%
  mutate(winner_T1 = (score/exprimes > 0.5) & (score/inscrits > 0.25)) %>%
  filter(winner_T1) %>%
  select(code_dep, code_circ, winner_2012 = parti)
# We compute the number of votes per party after the 2nd tour
winner_leg_2012 <- don_leg_2012_T2 %>%
  select(code_dep, code_circ, candidate, parti, score) %>%
  group_by(code_dep, code_circ, parti, candidate) %>%
  summarize(score = sum(score))
# We then compute the winner in each circonscription:
winner_leg_2012 <- winner_leg_2012 %>%
  group_by(code_dep, code_circ) %>%
  summarise(winner_2012 = parti[which.max(score)]) 

# We aggregate with the results of the tour 1:
winner_2012 <- rbind(winner_leg_2012_T1,
                     winner_leg_2012)

# We compute the number of seats won by the parties:
tab_2012 <- table(winner_leg_2012$winner_2012)
winner_leg_2012 <- data.frame(
  parti = names(tab_2012),
  siege_2012 = as.numeric(tab_2012)
)
parti_leg_2012 <- merge(winner_leg_2012, parti_leg_2012, 
                        by = "parti",  all = T)
parti_leg_2012$siege_2012[is.na(parti_leg_2012$siege_2012)] <- 0


parti_2012 <- data.frame(parti = parti_leg_2012$parti,
                         x = parti_leg_2012$score_2012,
                         deputes = parti_leg_2012$siege_2012)

# We look at the ratio representative/electors :
parti_2012$ratio <- parti_2012$deputes / parti_2012$x
# We rank the parties with respect to the ratio and the number of inhabitants
parti_2012 <- parti_2012[order(parti_2012$ratio, -parti_2012$x), ]


parti_2012$rank <- 1:nrow(parti_2012)
parti_2012$year <- "2012" 
parti_2012$p <- cumsum(parti_2012$deputes)/sum(parti_2012$deputes)
parti_2012$L <- cumsum(parti_2012$x)/sum(parti_2012$x)
parti_2012$ratio_parti <- parti_2012$x / sum(parti_2012$x)
parti_2012$ratio_siege <- parti_2012$deputes/ sum(parti_2012$deputes)

################################
# 2017

### Data basis related to the parties
# We transform the data from columns to rows:
  
candidates_2 <- NULL
for(k in 1:27) {
  ex <-  don_leg_2017_T1[, c("code_dep", "code_circ", 
                             "inscrits", "exprimes",
                             paste0(paste(c("panneau", "sexe", "nom", "prenom", 
                                            "nuance", "voix"), k, sep = "_")))]
  names(ex) <- c("code_dep", "code_circ", "inscrits", "exprimes", 
                 "panneau", "sexe", "nom", "prenom", 
                 "parti", "score")
  candidates_2 <- rbind(candidates_2, ex)
}
# We compute the number of votes per party after the 1st tour. In 2017:
parti_leg_2017 <- candidates_2 %>%
  select(parti, score) %>%
  group_by(parti) %>%
  summarize(score_2017 = sum(score))
# We compute winners after the 1st and 2nd tour
winner_leg_2017_T1 <- candidates_2 %>%
  select(code_dep, code_circ, panneau, parti, score, 
         exprimes, inscrits) %>%
  group_by(code_dep, code_circ, parti, panneau) %>%
  summarize(score = sum(score),
            exprimes = sum(exprimes),
            inscrits = sum(inscrits))

winner_leg_2017_T1 <- winner_leg_2017_T1 %>%
  mutate(winner_T1 = (score/exprimes > 0.5) & (score/inscrits > 0.25)) %>%
  filter(winner_T1) %>%
  select(code_dep, code_circ, winner_2017 = parti)

candidates_3 <- NULL
for(k in 1:3) {
  ex <-  don_leg_2017_T2[, c("code_dep", "code_circ", 
                             "inscrits", "exprimes",
                             paste0(paste(c("panneau", "sexe", "nom", "prenom", 
                                            "nuance", "voix"), k, sep = "_")))]
  names(ex) <- c("code_dep", "code_circ", "inscrits", "exprimes", 
                 "panneau", "sexe", "nom", "prenom", 
                 "parti", "score")
  candidates_3 <- rbind(candidates_3, ex)
}
# We compute the number of votes per party after the 2nd tour
winner_leg_2017 <- candidates_3 %>%
  select(code_dep, code_circ, panneau, parti, score) %>%
  group_by(code_dep, code_circ, parti, panneau) %>%
  summarize(score = sum(score))
winner_leg_2017 <- na.omit(winner_leg_2017)
# We then compute the winner in each circonscription:
winner_leg_2017 <- winner_leg_2017 %>%
  group_by(code_dep, code_circ) %>%
  summarise(winner_2017 = parti[which.max(score)]) 

# We aggregate with the results of the tour 1:
winner_2017 <- rbind(winner_leg_2017_T1,
                     winner_leg_2017)


# We compute the number of seats won by the parties:
tab_2017 <- table(winner_leg_2017$winner_2017)
winner_leg_2017 <- data.frame(
  parti = names(tab_2017),
  siege_2017 = as.numeric(tab_2017)
)
parti_leg_2017 <- merge(winner_leg_2017, parti_leg_2017, 
                        by = "parti",  all = T)
parti_leg_2017$siege_2017[is.na(parti_leg_2017$siege_2017)] <- 0
parti_leg_2017 <- na.omit(parti_leg_2017)

parti_2017 <- data.frame(parti = parti_leg_2017$parti,
                         x = parti_leg_2017$score_2017,
                         deputes = parti_leg_2017$siege_2017)

# We look at the ratio representative/electors :
parti_2017$ratio <- parti_2017$deputes / parti_2017$x

# We rank the parties with respect to the ratio and the number of inhabitants
parti_2017 <- parti_2017[order(parti_2017$ratio, -parti_2017$x), ]

parti_2017$rank <- 1:nrow(parti_2017)
parti_2017$year <- "2017" 
parti_2017$p <- cumsum(parti_2017$deputes)/sum(parti_2017$deputes)
parti_2017$L <- cumsum(parti_2017$x)/sum(parti_2017$x)
parti_2017$ratio_parti <- parti_2017$x / sum(parti_2017$x)
parti_2017$ratio_siege <- parti_2017$deputes/ sum(parti_2017$deputes)

save(winner_1993, parti_1993, winner_1997, parti_1997, winner_2002, 
     parti_2002, winner_2007, parti_2007, winner_2012, parti_2012, 
     winner_2017, parti_2017,
     file = "parti_winner.RData")