엑셀을 이용한 테이블 자료 분석 한국어 정보의 전산 처리 2017. 3. 20.

테이블 자료란? 테이블 자료: 행과 열이 교차하는 사각형 형태의 자료 엑셀, gawk, csvkit, R 등의 많은 데이터과학의 tool들은 테이블 자료를 처리하는 데 최 적화되어 있음. 행(가로줄, row): 하나의 개체에 해당. 줄바꿈으로 구분. 테이블의 첫 행은 실제 데이터가 아니라, 각 변수의 이름인 경우가 많음. 열(세로줄, column): 하나의 속성/변수에 해당. 쉼표나 탭으로 구분 쉼표로 구분된 파일은 csv(comma separated value), 탭으로 구분된 파일은 tsv(tab separated value)라 함. 변수의 종류 수치형(numerical): 정수, 실수 등의 수치로 표현되는 변수 범주형(categorical): 수치가 아닌, 범주로 표현되는 변수 범주형 변수에는 순서가 없는 것과 순서가 있는 것(예: 수, 우, 미, 양, 가)이 있음. 테이블에 대해 흔히 행해지는 조작 요약 통계: 수치형 변수의 합계, 평균, 표준편차 등 추출 집계: 범주형 변수의 값별로 관측치의 빈도나 여러 통계량 추출 변수간 상관관계: 범주형: 카이제곱검정, 수치형: 상관분석

테이블과 테이블의 통합 두 테이블이 key가 되는 열을 공유할 때, 두 테이블을 통합(join)할 수 있음. join, csvjoin 등의 command line tool을 이용하여 통합할 수 있음. R에서도 쉽게 가능. score.csv와 party.csv를 csvjoin으로 통합 csvjoin –c name score.csv party.csv >50students.csv name이라는 이름의 열/변수를 통합의 축으로 하여 두 파일을 통합 이 파일들은 utf-8로 되어 있는데, 이를 cp949로 코드 변환 uniconv utf-8 50students.csv cp949 50students_cp949.csv

엑셀에서 테이블을 불러와 정돈하기 50students_cp949.csv를 엑셀에서 연다. 표 서식을 적용하여 시각적으로 더 보기 좋게 만든다. 수치형 변수에 조건부 서식을 적용하여 각 점수대가 다른 색으 로 보이게 한다. 기존 열/변수들을 바탕으로 필요한 열/변수를 추가한다. 국어, 영어, 수학, 물리, 화학 점수를 더하여 총점을 추가한다. 열을 추가하여 첫째 행에 변수 이름(sum)을 써 넣고, 둘째 행에 =SUM(B2:F2)라고 써 넣으면 나머지 행들에도 자동으로 적용된다. 총점을 과목 수(5)로 나누어 평균을 추가한다. 열을 추가하여 첫째 행에 변수 이름(avg)을 써 넣고, 둘째 행에 =G2/5라고 써 넣으면 나머지 행들에도 자동으로 적용된다.

피벗 테이블로 집계하기 1 [삽입-피벗테이블] 또는 [삽입-추천 피벗 테이블] 메뉴를 이용하 여 피벗테이블을 만든다. 가장 단순한 형태의 피벗 테이블은 하나의 범주형 변수에 대하 여 특정 값을 집계한 것. ‘피벗 테이블 필드’ 메뉴에서 ‘행‘ 필드에 관심의 대상이 되는 범 주형 변수를 추가하고, ‘값‘ 필드에 집계할 변수를 추가한다. 집계 방법의 default는 합계임. 이것을 변경하고 싶으면, ‘값’ 필 드의 드롭다운 메뉴 ‘값 필드 지정’에서 다른 집계 방법을 선택 한다.

피벗 테이블로 집계하기 2 범주형 변수와 범주형 변수를 조합하여 집계할 수도 있다. 피벗 테이블의 열(세로줄)에 추가할 변수를 ‘열‘ 필드에 추가하고 피벗 테이블의 행(가로줄)에 추가할 변수를 ‘행‘ 필드에 추가한다. 하나의 변수만 추가할 수도 있고, 둘 이상의 변수를 추가할 수도 있다. 2개의 변수를 추가하면, 일단 첫째 변수를 기준으로 집계표를 만들고 둘째 변수는 +를 클릭하면 확장된 팝업 창으로 보여준다. ‘값’ 필드의 드롭다운 메뉴 ‘값 필드 지정’에서 집계 방법을 선택한다. 가장 단순한 경우는 ‘개수(빈도, frequency)’를 선택한다. 두 범주형 변수의 각 조합에 대한 집계된 값을 시각화하려면, 피벗 테이블에 커서를 둔 상태에서 [삽입-차트-2차원 세로 막대형]을 선 택한다.

피벗 테이블로 집계하기 3 범주형 변수와 범주형 변수를 조합하여 집계하되, 각 조합 셀에 들어갈 값을 ‘개수‘가 아닌 다른 집계 방법을 선택할 수 있다. 예컨대 ‘열’ 필드에 party 변수를 추가하고, ‘행‘ 필드에 job 변수 를 추가하고, ‘값’ 필드에 avg 변수를 추가하되, 집계 방법을 ‘평 균‘으로 할 수 있다. 이렇게 하면, 각 지지정당-직업별로 그 조합 범주에 속하는 사 람들의 5과목 평균점수의 평균이 각 셀에 나타난다. [삽입-차트-2차원 세로 막대형]을 선택하면, 각 직업-지지정당별 로 평균점수의 평균을 막대 그래프로 보여준다.

특정 변수에 대한 히스토그램 특정 열/변수를 일정한 구간별로 빈도를 집계하여 히스토그램을 그리 려면, 우선 [파일-옵션-추가기능] 메뉴의 ‘이동‘ 버튼을 눌러, 팝업 창에 서 ‘분석 도구‘를 선택한다. 그러면 [테이터] 메뉴의 맨 우측에 [테이터 분석] 메뉴가 추가된다. 테이블에서 열을 추가하여, 첫째 행에 적당한 이름을 써 주고, 둘째 행 부터 집계하고자 하는 변수의 각 구간의 끝 값을 써 준다. [데이터-테이터 분석-히스토그램]을 선택하여 뜨는 팝업 창에서 ‘입력 범위‘에는 집계하고자 하는 변수를 $H:$H와 같은 형식으로 써 주고, ‘계급 구간‘에는 추가한 열을 $M$1:$M$7와 같은 형식으로 써 준다. 첫째 행에는 변수 이름이 있으므로 ‘이름표‘에 체크를 해 준다. ‘확인’ 버튼을 누르면 집계표가 만들어지고 [삽입-차트-2차원 세로 막대형]을 선택하면 히스토그램을 보여준다.

기술 통계량(descriptive statistic) 평균(mean), 중앙값(median), 최빈값(mode), 분산, 범위, 최소값, 최대값, 범위, 첨도, 왜도 등의 기술 통계량을 얻기 위해서는 [데이터-데이터분석-기술통계법] 메뉴를 선택한다. 팝업 창의 ‘입력 범위‘에 관심의 대상인 변수를 써 준다. $B:$H 라고 하면 B열부터 H열까지의 변수가 모두 포함된다. ‘출력 옵션‘에서는 관심의 대상인 통계량을 모두 선택한다.

수치형 변수들 사이의 상관관계 수치형 변수들 사이의 상관관계를 알아보고 싶으면 [데이터-데이터분석-상관분석] 메뉴를 선택한다. 팝업 창의 ‘입력 범위‘에 관심의 대상인 변수들을 써 준다. 첫째 행에는 변수 이름이 있으므로 ‘첫째 행 이름표 사용‘을 체 크해 준다. ‘확인’을 클릭하면 상관분석 결과표가 나타나는데 대각선 위치의 셀들은 같은 변수끼리의 상관계수이므로 1인 것 이 당연하다. 이것들은 지운다. 나머지 셀들에 대해 [조건부 서식-색조]를 선택하면, 상관계수의 크기에 따라 서로 다른 색깔로 보여준다.

수치형 변수들 사이의 scatterplot 한 변수는 X축에, 다른 변수는 Y축에 표시된다. 메인 테이블에서 관심의 대상인 두 변수를 선택한다. 첫째 변수/열의 맨 위의 명칭(예컨대 B)을 클릭하고, Ctrl 키를 누른 상 태에서 둘째 변수/열의 맨 위의 명칭(예컨대 C)를 클릭한다. [삽입-차트-분산형] 메뉴에서 적절한 그래프를 선택한다. 차트 제목을 더블클릭하여 제목을 적절히 수정한다.

B열과 C열을 선택한 모습

엑셀 vs. gawk vs. R 테이블 형식의 데이터를 처리할 때, command line에서 간단한 처리를 할 때에는 gawk가 편할 때가 많고 <50students.csv gawk 'BEGIN{FS=",";OFS=","} { sum=$2+$3+$4+$5+$6; avg=sum/5; print $1,$2,$3,$4,$5,$6,sum,avg,$7,$8,$9,$10 }' 통계 처리 및 시각화를 위해서는 R이 대개 훨씬 더 강력하다. students <- read_csv("50students_ext.csv") #csv 파일을 읽어들임 by_class <- group_by(students, class) #class별로 그룹화 summarize(by_class, avg2=mean(avg)) #class별로 avg의 평균을 냄 by_class_party <- group_by(students, class, party) #class, party별로 그룹화 summarize(by_class_party, cnt=n()) #class, party별로 개체의 수를 count함

R에서의 처리 ggplot(students) + geom_bar(aes(party,fill=class), position="dodge") class, party별로 개체의 수를 count의 시각화 ggplot(students) + geom_count(aes(party,class) 위의 수치를 다른 방식으로 시각화 by_party_job <- group_by(students, party, job) party, job별로 그룹화 by_party_job_summary <- summarize(by_party_job, avg2=mean(avg)) 위의 그룹별로 avg의 평균을 냄 ggplot(by_party_job_summary, aes(job,avg2,color=party))+geom_point() 위의 수치의 시각화 ggplot(students)+geom_histogram(aes(avg), binwidth=10) 평균점수를 10점 구간별로 히스토그램 그리기

R에서의 처리 ggplot(students)+geom_bar(aes(job, fill=gender), position=“dodge”) 직업별로 막대그래프를 그리되, 性에 따라 막대를 따로 표시 ggplot(students) + geom_bar(aes(job, fill=party)) 직업별로 막대그래프를 그리되, 각 막대에서 지지 정당을 색조로 채움 summary(students) #테이블의 각 변수에 대한 기초 통계 요약 cor(students$kor, students$eng) #국어점수와 영어점수의 상관계수 ggplot(students, aes(phy,che)) + geom_point() + geom_smooth() 물리점수를 X축에, 화학점수를 Y축에 scatterplot으로 표시하고, 두 점수 사이 의 상관관계를 나타내는 선을 추가 ggplot(students) + geom_point(aes(kor,math,color=job)) 국어점수를 X축에, 수학점수를 Y축에 scatterplot으로 표시하되, 각 학생의 직 업을 색조로 표시