개표결과 엑셀변환 01: 제공된 자료의 형태

 제21대 국회의원 선거 결과 데이터가 선거관리위원회 사이트에 올려져 있다. 외국의 사이트, 예를 들면 [ MIT Election Lab ] 등에서 제공되는 방식이나 형태와 비교하다면, [ 선거관리위원회 ]에서 제공하는 데이터는 잘 정돈되어 쉽게 가공할 수 있는 형태라고 보기 힘들다. R이나 Python이 아니더라도, 엑셀에 붙여넣어 사용하려고 하더라도 한참 자료를 만져야만 한다. 

 

1. 자료 제공 형태가 다양하지 못 하다. 

 

 개표결과 데이터를 파일로 제공하고 있지 않다. 홈페이지에 자료를 게시하였더라도 따로 raw data를 첨부하여야 한다. xls, csv 등의 다양한 파일 형태로 자유롭게 다운로드를 할 수 있도록 개선되어야만 한다. 제공한 자료에 대한 설명도 자료와 함께 제공되어야 한다. 예를 들면, 음수 표기된 숫자, 실제로 엑셀에서 합산한 숫자와 불일치 등에 대한 정보를 곁들이면 불필요한 오해를 줄일 수 있을 것 같다. 보기 편하게 시각화를 하기 위해서라면, 표 외에도 그래프 등의 방법도 고려해보아야 한다. 

 

2. 제공된 자료의 table 기록 방식이 복잡하며, 가공이 필요하다. 

 

 아래 그림1은 MIT Election Lab의 자료다. Table 혹은 Data Frame의 자료가 분석을 위해 가공하기 쉽도록 기록되었으며, missing data가 있다고 하더라도 처리에 유리하다. [ OsakaUx: MED101xIntroduction to Applied Biostatistics: Statistics for Medical Research ] 등의 MOOC 과정을 들어보면 도움. 

 

그림 1. MIT Lab 자료 일부 자료 인용

 그러나 한국 선거관리위원회의 자료인 그림 2는 그렇지 않다. (a) merged cell이 있어 heading 처리를 위해 손을 대야 한다. (b) blank cell이 있어 자료를 정리하는데 꽤 시간과 노력이 필요하고 missing data 처리도 신경쓰인다. (c) cross table로 항목이 가로 세로로 교차되어 있어 자료 분석을 위해서는 다시 표를 만들어야 한다. 결국 엄청난 시간과 노력이 필요하다. 

 

그림 2. 중앙선거관리위원회 개표결과 일부 자료 인용

 

3. 엑셀 VBA, macro 기능을 사용하더라도 그 과정이 험난하다. 

 

 중앙선거관리위원회에서 제공하는 자료에서 초록색 사각형 부분이 공통된 형식으로 보인다. (a) 처음 세 행은 heading,

(b) A4 : A8 범위는 모든 sheet에서 공통적인 항목, (c) 동별 소계, 관내사전투표 항목 등이다. 그러나 그 공통된 부분에 "총 관내사전투표수"와 "총 당일투표수"가 보이지 않는다. 이는 각 표를 보더라도 선거 결과를 직관적으로 알기 힘들며, 추가로 계산기 등으로 합산을 해봐야만 한다는 것이다. 이는 굉장히 불편하다. 위 표에서, 간성읍과 거진읍 등의 당일 투표 합계를 각각 구하고, 그 후에 모든 읍단위의 당일 투표수 총합을 구하여야만 한다. 엑셀 VBA를 해본 적은 없지만, 배우기 쉽다고 알려진 언어고 구글을 찾아가면서 배운다면 뭔가 해볼 수는 있을 것 같다. 

 

 그런데 엑셀 VBA macro 기능을 사용하더라도 이건 쉬운 일이 아니다. 아래 VBA 코드는 단지 선거인수라고 표시된 열에 한하여, 각 읍단위마다 당일 투표수 합계를 구하고, "관내사전투표"라 표시된 행 밑에 새로운 행을 추가하여 그 값을 표시되도록 한 것이다. 그렇게 하면 그림 3과 같은 결과를 얻을 수 있다. 이후 모든 열 -> 모든 sheet 순으로 적용하면 "읍별 당일투표수"를 모든 sheet에 표시할 수 있을 테고, 조금 더 시간을 들인다면 각 sheet에 모든 읍의 관내사전 및 당일 투표수 합도 추가할 수는 있을 거다. 

Sub insRow()
' insert a new row below
' label it with a string value
' get sum of all the number of votes in the same local area (dong, myeun, eup)
    Dim i As Integer         ' for for-next, do-until loop
    Dim j As Integer
    Dim lRows As Integer     ' the number of rows
    Dim cell_lbl As Range    ' cell for label (to be added)
    Dim cell_sum As Range    ' cell for sum value (to be added)
    Dim dong_name As String  ' the name of dong, eup, myeun
    Dim sum As Integer       ' total sum
    
    ' Be careful! The number of rows will be changed on and on.
    ' So, we need to add some value to original value.
    ' If not, macro will be applied partially.
    lRows = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row + 200
    lCols = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    
    For i = 1 To lRows
        If Cells(i, 2) = "관내사전투표" Then
            Rows(i + 1).EntireRow.Insert Shift:=xlDown
            Set cell_lbl = Cells(i + 1, 2)
            cell_lbl.Value = "관내당일투표"
            Set cell_sum = Cells(i + 1, 3)
            dong_name = Cells(i + 2, 2).Value
            
            j = i + 2
            sum = 0
            Do Until Left(Cells(j, 2).Value, 2) <> Left(dong_name, 2)
                sum = sum + Cells(j, 3).Value
                j = j + 1
            Loop
            cell_sum.Value = sum
            
        End If
    Next i
End Sub

그림 3. 간단한 엑셀 VBA macro를 적용한 모습

 그런데 그렇게 해서 그림 2에 좌측 "국외부재자투표(공관)"이란 cell 아래에 "관내사전투표"와 "당일투표" 항목과 데이터를 추가했다 치자. 그래도 문제다. 여전히 가공이 쉽지 않은, 복잡한 형태라는 것은 변하지 않았다. 

 

4. 누군가 이미 자료를 정리해보지 않았을까?

 

 찾아보니 누군가 이미 엑셀로 잘 정리를 해놓은 것 같다. [ 바실리아TV ]라는 유투브 채널에서 자료와 자신의 분석까지 구글 드라이브에 공유해놓았다. 분석 내용과 별도로 자료의 정리 면에서는 끈기가 대단한 분.