write "votes"
print
read "seats.csv"
write "seats"
join natural
read "votingdata.csv"
write "values"
join natural
extend weighted = value * seats
extend xy = value*votes
extend x2 = value*value
extend y2 = votes*votes
extend delta = (votes-value)*(votes-value)
project inline row, key count, delta avg
order delta_avg 1, row 1
set k = key_count
limit 1 k
project inline row, votes avg, value avg, xy sum, x2 sum, y2 sum
extend cov = xy_sum/key_count - value_avg*votes_avg
extend varx = x2_sum/key_count - value_avg*value_avg
extend vary = y2_sum/key_count - votes_avg*votes_avg
extend r = cov / sqrt(varx*vary)
extend r2 = r*r
select (x2_sum - key_count*value_avg*value_avg) != 0
extend a = (xy_sum - key_count*value_avg*votes_avg)/(x2_sum - key_count*value_avg*value_avg)
extend b = votes_avg - a * value_avg
extend error = (value*a + b - votes)*(value*a + b - votes)
project row, a, b, r2, error avg, delta_avg
order delta_avg 1, row 1
update error_avg = sqrt(error_avg)
write "selection"
read "seats"
read "votes"
join left
join cross
read "values"
join natural
extend estimate = votes
extend estimate_min = votes
extend estimate_max = votes
update estimate = format(value * a + b, "%1.1f") where estimate = 0
update estimate_min = format((value * a + b - 2*error_avg), "%1.1f") where estimate_min = 0
update estimate_max = format((value * a + b + 2*error_avg), "%1.1f") where estimate_max = 0
project key, seats, senate, estimate_min, estimate, estimate_max
extend estimatesenate = ( estimate > 50.0 ) * senate
write "detail"
extend weighted_min = seats * estimate_min
extend weighted = seats * estimate
extend weighted_max = seats * estimate_max
project seats sum, weighted_min sum, weighted sum, weighted_max sum, estimatesenate sum
extend estimate_min = format(weighted_min_sum / seats_sum,"%1.1f")
extend estimate = format(weighted_sum / seats_sum,"%1.1f")
extend estimate_max = format(weighted_max_sum / seats_sum,"%1.1f")
project estimate_min, estimate, estimate_max, estimatesenate_sum
print
read "votes"
project key, votes
relation Von, Bis, Farbe
insert "0", "9", "#F11"
insert "10", "19", "#E22"
insert "20", "29", "#D44"
insert "30", "39", "#C66"
insert "40", "44", "#B77"
insert "45", "49", "#A88"
insert "50", "54", "#8A8"
insert "55", "59", "#7B7"
insert "60", "69", "#6C6"
insert "70", "79", "#4D4"
insert "80", "89", "#2E2"
insert "90", "99", "#1F1"
write "legende"
join votes >= Von and votes <= Bis
rename key kt
project kt, Farbe
// print
template "map_cantons"
read "legende"
echo "<div style='width:200px'>"
update Farbe = "<span style='color:".Farbe."'>█</span>"
print
echo "</div>"
read "detail"
project key, estimate_min, estimate, estimate_max
order estimate 9
print
read "selection"
format a "%1.3f", b "%1.3f", r2 "%1.3f", error_avg "%1.3f", delta_avg "%1.3f"
print