### Satisfaction with Democracy: When Government by the People brings Electoral Losers and Winners Together
# Code Lucas Leemann and Isabelle Stadelmann-Steffen
# First version: December 2018
# Final version: April 2021



# load index data
load("Output/Data/IndexUSCHATDE.Rdata")

# load individual data
dataUS <- read.dta("Data/Individual/anes_timeseries_2016_Stata12.dta", convert.factors = FALSE)
dataCH <- read_dta("Data/Individual/726_Selects2015_PES_Data_v1.03.dta")
dataAT <- read_dta("Data/Individual/AUTNES 2013 ZA5856_de_v2-0-0.dta")
dataDE <- read_dta("Data/Individual/ZA6801_v4-0-0.dta")




###############################################################
### CH DATA
###############################################################





# outcome: satisfaction with democracy
dataCH$satisfactionD <- dataCH$f13700
dataCH$satisfactionD[dataCH$satisfactionD==8|dataCH$satisfactionD==9] <- NA
table(dataCH$satisfactionD)
dataCH$satisfactionD <- abs(dataCH$satisfactionD-5)
table(dataCH$satisfactionD)
# recoded: high values mean now high satisfaction

# age
dataCH$birthyear[dataCH$birthyear==-9] <- NA
dataCH$age <- 2015-dataCH$birthyear
hist(dataCH$age)

# female
dataCH$female <- dataCH$gender-1

# education
dataCH$edu<- dataCH$educ_r
dataCH$edu[dataCH$edu==98|dataCH$edu==99] <- NA
# education nach ISCED
dataCH$edu2[dataCH$edu==1] <- 0 # 0 bedeutet bei ISCED eigentlich auch Kindergarten... evtl. andere Auspr?gung ?berlegen
dataCH$edu2[dataCH$edu==2] <- 1
dataCH$edu2[dataCH$edu==3] <- 2
dataCH$edu2[dataCH$edu==4|dataCH$edu==5|dataCH$edu==6|dataCH$edu==7|dataCH$edu==8|dataCH$edu==9] <- 3
dataCH$edu2[dataCH$edu==10|dataCH$edu==11] <- 5
dataCH$edu2[dataCH$edu==12|dataCH$edu==13] <- 6 

# Interest
dataCH$f10100[dataCH$f10100==8|dataCH$f10100==9] <- NA
dataCH$interest <- abs(dataCH$f10100 - 5)




# read-in data on cantonal parties
CH.parties <- read_excel("Data/Subnationale Exekutiven.xlsx", sheet="CH")

# parties -- 1:FDP	2:CVP	3:SP	4:SVP	5:LP	6:CSP	7:BDP	8:GP	9:Lega	10: MCG	Übrige
dataCH$parties <- rep(NA,length(dataCH$f11800))
dataCH$parties[dataCH$f11800==10] <- 1
dataCH$parties[dataCH$f11800==20] <- 2
dataCH$parties[dataCH$f11800==30] <- 3
dataCH$parties[dataCH$f11800==40] <- 4
dataCH$parties[dataCH$f11800==208] <- 5
dataCH$parties[dataCH$f11800==100] <- 6
dataCH$parties[dataCH$f11800==70] <- 7
dataCH$parties[dataCH$f11800==50] <- 8
dataCH$parties[dataCH$f11800==80] <- 9
dataCH$parties[dataCH$f11800==90] <- 10
table(dataCH$parties)

dataCH$winner <- rep(0,dim(dataCH)[1])
for (i in 1:length(dataCH$winner)){
  cr <- dataCH$canton[i]
  pp <- as.numeric(CH.parties[as.numeric(as.character(cr)),])[-c(1:2)]
  parties.in.power <- which(pp==1)
  if (dataCH$parties[i] %in% parties.in.power) dataCH$winner[i] <- 1
}


# canton
table(dataCH$canton)
dataCH$unitID <- dataCH$canton + 100


# employed? (full time)
dataCH$employed1 <- dataCH$f21400
dataCH$employed1[dataCH$employed1==98|dataCH$employed1==99] <- NA
dataCH$employed[dataCH$employed1==1] <- 1
dataCH$employed[dataCH$employed1==6|dataCH$employed1==7] <- 2
dataCH$employed[dataCH$employed1==5] <- 3
dataCH$employed[dataCH$employed1==3|dataCH$employed1==4] <- 4
dataCH$employed[dataCH$employed1==2] <- 5
# 1: working full time, 2: unempl or disabled, 3: retired, 4: student/homemaker, 5: part-time

# participation
dataCH$participation <- as.numeric(dataCH$f11100r)
dataCH$participation[dataCH$participation>2] <- 0
# 0: no, dk, na - 1: yes


dataCH$country <- "CH"
dataCH$income <- dataCH$f28910
dataCH$income[dataCH$income>20] <- NA
dataCH1 <- data.frame(dataCH[,c("satisfactionD","age","female","edu2","unitID","employed","country","participation", "income", "interest","winner")]) 




###############################################################
### AT DATA
###############################################################

# outcome: satisfaction with democracy
dataAT$satisfactionD <- dataAT$q24
dataAT$satisfactionD[dataAT$satisfactionD==88|dataAT$satisfactionD==99] <- NA
table(dataAT$satisfactionD)
dataAT$satisfactionD <- abs(dataAT$satisfactionD-5)
table(dataAT$satisfactionD)
# recoded: high values mean now high satisfaction




# age
dataAT$q2[dataAT$q2==88|dataAT$q2==99] <- NA
dataAT$age <- 2013-dataAT$q2
hist(dataAT$age)

# female
dataAT$female <- dataAT$q3-1

# education
dataAT$edu<- dataAT$q78
dataAT$edu[dataAT$edu==88|dataAT$edu==99] <- NA
# education nach ISCED
dataAT$edu2[dataAT$edu==1|dataAT$edu==2] <- 0 # 0 bedeutet bei ISCED eigentlich auch Kindergarten... evtl. andere Auspr?gung ?berlegen
dataAT$edu2[dataAT$edu==3|dataAT$edu==4] <- 1
dataAT$edu2[dataAT$edu==6|dataAT$edu==7|dataAT$edu==8|dataAT$edu==9] <- 3
dataAT$edu2[dataAT$edu==10] <- 4
dataAT$edu2[dataAT$edu==11] <- 5 
dataAT$edu2[dataAT$edu==12|dataAT$edu==13|dataAT$edu==14] <- 6


# read-in data on cantonal parties
AT.parties <- read_excel("Data/Subnationale Exekutiven.xlsx", sheet="AT")

# parties -- 1:SPÖ	2:FPÖ	3:ÖVP	4:Grüne	5:Team Stronach
dataAT$parties <- rep(NA,length(dataAT$q17))
dataAT$parties[dataAT$q17==1] <- 1
dataAT$parties[dataAT$q17==2] <- 3
dataAT$parties[dataAT$q17==3] <- 2
dataAT$parties[dataAT$q17==6] <- 4
dataAT$parties[dataAT$q17==9] <- 5

table(dataAT$parties)

dataAT$winner <- rep(0,dim(dataAT)[1])
for (i in 1:length(dataAT$winner)){
  cr <- dataAT$q113[i]
  pp <- as.numeric(AT.parties[as.numeric(as.character(cr)),])[-c(1:2)]
  parties.in.power <- which(pp==1)
  if (dataAT$parties[i] %in% parties.in.power) dataAT$winner[i] <- 1
}



# canton
table(dataAT$q113)
dataAT$q113[dataAT$q113==88|dataAT$q113==99] <- NA
dataAT$unitID <- dataAT$q113 + 200


# employed? (full time)
dataAT$employed <- dataAT$q83
dataAT$employed[dataAT$employed==88|dataAT$employed==99] <- NA
dataAT$employed[dataAT$employed==1] <- 1
dataAT$employed[dataAT$employed==2|dataAT$employed==3|dataAT$employed==4] <- 5
dataAT$employed[dataAT$employed==5|dataAT$employed==7] <- 4
dataAT$employed[dataAT$employed==6] <- 2
dataAT$employed[dataAT$q88==1] <- 3
dataAT$employed[dataAT$q88==2|dataAT$q88==3|dataAT$q88==4|dataAT$q88==5|dataAT$q88==6|dataAT$q88==7] <- 4
dataAT$employed[dataAT$q88==8] <- 2
dataAT$employed[dataAT$q88==9] <- 2
# 1: working full time, 2: unempl or disabled, 3: retired, 4: student/homemaker, 5: part-time

# participation
dataAT$participation[dataAT$q16==1|dataAT$q16==2|dataAT$q16==3|dataAT$q16==88|dataAT$q16==99] <- 0
dataAT$participation[dataAT$q16==4|dataAT$q16==5] <- 1
dataAT$participation <- as.numeric(dataAT$participation)
# 0: no, dk, na; 1: yes


dataAT$country <- "AT"
dataAT$income <- NA

dataAT1 <- data.frame(dataAT[,c("satisfactionD","age","female","edu2","unitID","employed","country","participation","income","winner")]) 

###############################################################
### US DATA
###############################################################

# outcome: satisfaction with democracy (high values, low satisfaction)
dataUS$satisfactionD <- dataUS$V162290
dataUS$satisfactionD[dataUS$satisfactionD< -5] <- NA
dataUS$satisfactionD[dataUS$satisfactionD==4]<- 3
dataUS$satisfactionD[dataUS$satisfactionD==5]<- 4
dataUS$satisfactionD <- abs(dataUS$satisfactionD-5)
table(dataUS$satisfactionD) # high values, high satisfaction 
# recoded: high values mean now high satisfaction



# age
dataUS$age <- dataUS$V161267
dataUS$age[dataUS$age<0] <- NA

# female
dataUS$female <- dataUS$V161342 - 1
dataUS$female[dataUS$female== -10|dataUS$female==2] <- NA
table(dataUS$female)

# education
dataUS$edu <- dataUS$V161270
dataUS$edu[dataUS$edu<0|dataUS$edu>=90] <- NA
# education nach ISCED
dataUS$edu2[dataUS$edu==1] <- 0 # 0 bedeutet bei ISCED eigentlich auch Kindergarten... evtl. andere Auspr?gung ?berlegen
dataUS$edu2[dataUS$edu==2|dataUS$edu==3] <- 1
dataUS$edu2[dataUS$edu==4|dataUS$edu==5] <- 2
dataUS$edu2[dataUS$edu==6|dataUS$edu==7|dataUS$edu==8|dataUS$edu==9] <- 3
dataUS$edu2[dataUS$edu==10|dataUS$edu==11|dataUS$edu==12] <- 5 
dataUS$edu2[dataUS$edu==13|dataUS$edu==14|dataUS$edu==15|dataUS$edu==16] <- 6

# Interest
#dataCH$f10100[dataCH$f10100==8|dataCH$f10100==9] <- NA
#dataCH$interest <- abs(dataCH$f10100 - 5)



# read-in data on cantonal parties
US.parties <- read_excel("Data/Subnationale Exekutiven.xlsx", sheet="USA")

# parties -- 1:Demo, 2 republ
dataUS$parties <- rep(0,length(dataUS$V162279))
dataUS$parties[dataUS$V162279==1] <- 1
dataUS$parties[dataUS$V162279==5] <- 2

table(dataUS$parties)

dataUS$winner <- rep(0,dim(dataUS)[1])
dataUS$state <- dataUS$V161015b
dataUS$state[dataUS$state==-1]<- NA
www <- as.numeric(names(table(dataUS$state)))

q <- 0
dataUS$state1 <- rep(0,length(dataUS$state))
for (i in www){
  q <- q+1
  dataUS$state1[dataUS$state==i] <- q
}



for (i in 1:length(dataUS$winner)){
  cr <- dataUS$state1[i]
  pp <- as.numeric(US.parties[cr,])[-c(1:2)]
  parties.in.power <- which(pp==1)
  if (dataUS$parties[i] %in% parties.in.power) dataUS$winner[i] <- 1
}

table(dataUS$winner)


# state
dataUS$unitID <- dataUS$V163001a+300


# employed? (full time) ANES16: V161277
dataUS$employed <- rep(0, length(dataUS$V161277))
dataUS$employed[dataUS$employed < 0 ] <- NA
dataUS$employed[dataUS$V161277==1] <- 1
dataUS$employed[dataUS$V161277==2|dataUS$V161277==4|dataUS$V161277==6] <- 2
dataUS$employed[dataUS$V161277==5] <- 3
dataUS$employed[dataUS$V161277==7|dataUS$V161277==8] <- 4


# 1: working full time, 2: unempl or disabled, 3: retired, 4: student/homemaker, 5: part-time

# participation (Alternative: dataUS$V162031x => summary PRE-POST )
dataUS$participation <- dataUS$V162031
dataUS$participation[dataUS$participation!=4] <- 0
dataUS$participation[dataUS$participation==4] <- 1
table(dataUS$participation)

dataUS$country <- "US"

dataUS$income <- dataUS$V161361x
dataUS$income[dataUS$income< 0] <- NA

dataUS1 <- data.frame(dataUS[,c("satisfactionD","age","female","edu2","unitID","employed","country","participation", "income", "winner")]) 



###############################################################
### German DATA
###############################################################

# outcome: satisfaction with democracy (high values, low satisfaction)
dataDE$satisfactionD <- dataDE$q33
dataDE$satisfactionD[dataDE$satisfactionD<0] <- NA
dataDE$satisfactionD <- abs(dataDE$satisfactionD-5)



# age
dataDE$age <- 2016-dataDE$q2c
dataDE$age[dataDE$age<0] <- NA
dataDE$age[dataDE$age>100] <- NA

# female
dataDE$female <- dataDE$q1-1

# education
dataDE$edu<- dataDE$q135
dataDE$edu[dataDE$edu==-99] <- NA
# education nach ISCED
dataDE$edu2[dataDE$edu==1|dataDE$edu==6|dataDE$edu==9] <- 1 
dataDE$edu2[dataDE$edu==2|dataDE$edu==4] <- 2
dataDE$edu2[dataDE$edu==3|dataDE$edu==3] <- 3
dataDE$edu2[dataDE$edu==5] <- 4
dataDE$edu2[dataDE$q136c==1] <- 3
dataDE$edu2[dataDE$q136f==1] <- 4
dataDE$edu2[dataDE$q136g==1|dataDE$q136h==1|dataDE$q136i==1|dataDE$q136j==1|dataDE$q136k==1|dataDE$q136l==1] <- 5
dataDE$edu2[dataDE$q136m==1] <- 6
table(dataDE$edu2)


# employed? (full time)
dataDE$employed <- NA

dataDE$employed[dataDE$q137==1] <- 1
dataDE$employed[dataDE$q137==7|dataDE$q137==8] <- 2
dataDE$employed[dataDE$q137==10] <- 3
dataDE$employed[dataDE$q137==5|dataDE$q137==11|dataDE$q137==12|dataDE$q137==3|dataDE$q137==4|dataDE$q137==6] <- 4
dataDE$employed[dataDE$q137==2] <- 5
table(dataDE$employed)
# 1: working full time, 2: unempl or disabled, 3: retired, 4: student/homemaker, 5: part-time

# participation
dataDE$participation <- dataDE$q17
dataDE$participation[dataDE$participation <0] <- NA
dataDE$participation[dataDE$participation==2] <- 0
dataDE$participation[dataDE$participation==1] <- 1
table(dataDE$participation)

dataDE$country <- "DE"
dataDE$income <- NA



# read-in data on cantonal parties
DE.parties <- read_excel("Data/Subnationale Exekutiven.xlsx", sheet="DE")

# parties -- 1: SPD	2:CDU	(CSU)	4:Grüne	5:Linke	6:FDP	
#SSW
dataDE$parties <- rep(0,length(dataDE$q36a))
dataDE$parties[dataDE$q36a==4] <- 1
dataDE$parties[dataDE$q36a==1|dataDE$q36a==2|dataDE$q36a==3] <- 2

dataDE$parties[dataDE$q36a==6] <- 4
dataDE$parties[dataDE$q36a==7] <- 5





table(dataDE$parties)

dataDE$winner <- rep(0,dim(dataDE)[1])

www <- as.numeric(names(table(dataDE$bula)))



for (i in 1:length(dataDE$winner)){
  cr <- dataDE$bula[i]
  pp <- as.numeric(DE.parties[as.numeric(as.character(cr)),])[-c(1:2)]
  parties.in.power <- which(pp==1)
  if (dataDE$parties[i] %in% parties.in.power) dataDE$winner[i] <- 1
}

table(dataDE$winner)





# Unit ID -- State
# 400's
dataDE$unitID <- NA
dataDE$unitID[dataDE$bula==8] <- 401
dataDE$unitID[dataDE$bula==9] <- 402
dataDE$unitID[dataDE$bula==11] <- 403
dataDE$unitID[dataDE$bula==12] <- 404
dataDE$unitID[dataDE$bula==4] <- 405
dataDE$unitID[dataDE$bula==2] <- 406
dataDE$unitID[dataDE$bula==6] <- 407
dataDE$unitID[dataDE$bula==13] <- 408
dataDE$unitID[dataDE$bula==3] <- 409
dataDE$unitID[dataDE$bula==5] <- 410
dataDE$unitID[dataDE$bula==7] <- 411
dataDE$unitID[dataDE$bula==10] <- 412
dataDE$unitID[dataDE$bula==14] <- 413
dataDE$unitID[dataDE$bula==15] <- 414
dataDE$unitID[dataDE$bula==1] <- 415
dataDE$unitID[dataDE$bula==16] <- 416


dataDE1 <- data.frame(dataDE[,c("satisfactionD","age","female","edu2","unitID","employed","country","participation", "income","winner")]) 






###############################################################
### GENERATE MEGASAMPLE DATA
###############################################################

#DATA1 
dataCH2 <- dataCH1[,-10]
DATA1 <- data.frame(rbind(dataCH2,dataUS1,dataAT1,dataDE1))#, dataMX1))
names(DATA1) <- c("satisfactionD", "age", "female", "edu", "unitID", "employed", "country","participation", "income", "winner")
# unitID: 100er Zahlen: CH; 200er Zahlen: AT; 300er Zahlen: US; 400er Zahlen: US2; 500er Zahlen: MX.

rm(list=(ls()[ls()!="DATA1"]))


######


load("Output/Data/IndexUSCHATDE.Rdata")


DATA2 <- merge(DD.data, DATA1, by="unitID")
dim(DATA2)
dim(DATA1)


save(DATA2, file="Output/Data/Final_data.RData")

