엑셀 시트 데이터 병합

개요

엑셀 파일에 시트별로 동일한 구조의 데이터가 저장된 경우, 이를 하나의 데이터프레임으로 병합해 분석해야 할 때가 있습니다. 예를 들어, 각 시트가 서로 다른 지역 데이터를 담고 있다면, 시트를 구분하는 정보를 추가해 데이터 간 구별이 가능하도록 만들어야 합니다. 이번 글에서는 엑셀 시트별 데이터를 병합하고, 시트명을 새로운 칼럼으로 추가해 데이터를 구분하는 방법을 알려드리겠습니다.

데이터 준비하기

먼저, 필요한 패키지를 로드합니다. 이번 글에서는 관광지식정보시스템에서 제공하는 주요관광지점 입장객 통계의 서울, 경기, 인천 데이터를 각 시트에 저장한 엑셀 파일을 활용하겠습니다. 파일 경로를 지정하고, excel_sheets 함수를 사용해 엑셀 파일의 모든 시트 이름을 가져옵니다.

# 패키지 로드
library(readxl)
library(tidyverse)

# 파일 경로
file_path <- "데이터/수도권 주요관광지점 입장객/주요관광지점 입장객_수도권_2023.xls"

# 엑셀파일의 모든 시트 이름 가져오기
sheet_names <- excel_sheets(file_path)

# 시트 목록 확인하기
sheet_names

## [1] "경기도"     "서울특별시" "인천광역시"

시트 병합하기

sheet_names에는 파일에 포함된 모든 시트명이 저장됩니다. 이렇게 가져온 시트명을 활용해 각 시트 데이터를 반복적으로 읽어와 필요한 열만 추출하고, 시트명을 데이터프레임의 새로운 칼럼으로 추가할 수 있습니다.

lapply 함수를 사용해 각 시트 데이터를 읽어오고, 데이터를 가공한 후 bind_rows로 모든 시트 데이터를 하나의 데이터프레임으로 결합합니다. 이를 통해 각 시트의 데이터를 구분할 수 있는 시도 칼럼이 추가된 통합 데이터프레임을 생성할 수 있습니다.

각 시트에 대한 가공 과정을 설명하면, skip = 1로 두 번째 행부터 읽고 select(1:4)로 첫 번째부터 네 번째 열을 선택하며 mutate(시도 = sheet)로 시도 칼럼을 생성해 시트명을 저장합니다. colnames 함수를 이용해 칼럼명을 통일시켜줍니다.

# 하나의 데이터프레임 결합
combined_data <- bind_rows(
  lapply(sheet_names, function(sheet){
    tmp <- read_excel(file_path, 
               sheet = sheet, 
               skip = 1) %>% # 두 번째 행부터 읽기
      select(1:4) %>% # 필요한 열 선택
      mutate(시도 = sheet) # 시트명을 시도 칼럼에 추가하기
    
    colnames(tmp) <- c("시군구", "관광지", "구분", "총계", "시도")
    
    return(tmp)
  })
)

# 결과 확인
head(combined_data)

## # A tibble: 6 × 5
##   시군구 관광지                     구분     총계 시도  
##   <chr>  <chr>                      <chr>   <dbl> <chr> 
## 1 수원시 kbs수원센터(kbs수원아트홀) 내국인  19612 경기도
## 2 수원시 kbs수원센터(kbs수원아트홀) 합계    19612 경기도
## 3 수원시 경기대학교소성박물관       내국인  18150 경기도
## 4 수원시 경기대학교소성박물관       합계    18150 경기도
## 5 수원시 서수원칠보체육관           내국인 113703 경기도
## 6 수원시 서수원칠보체육관           합계   113703 경기도

이 방법을 사용하면 여러 시트의 데이터를 하나의 통합 데이터로 구성할 수 있어, 데이터를 보다 쉽게 분석하고 가공할 수 있습니다. 예를 들어, 특정 지역 데이터를 필터링하거나, 시도별 데이터를 비교하는 작업이 훨씬 수월해집니다. 다양한 엑셀 파일에서 동일한 구조의 데이터를 반복적으로 처리해야 할 때도 활용 가능하니, 필요에 따라 응용해 보세요!

---