동일 구조 엑셀파일 병합

개요

구조가 동일한 여러 개의 엑셀 파일을 하나의 테이블로 합치는 방법을 소개합니다. 이런 상황은 데이터를 지역별, 연도별 등으로 나눠 제공받을 때 흔히 발생합니다. 이번 글에서는 관광지식정보시스템에서 제공하는 주요관광지점 입장객 통계를 예로 들어, 서울, 경기, 인천 자료를 합쳐 하나의 테이블로 만드는 과정을 보여드리겠습니다.

데이터 준비하기

먼저, 데이터를 확인하고 합치는 작업을 위해 필요한 패키지를 로드합니다. 데이터를 구조적으로 살펴보기 위해 서울 자료를 불러와 확인해 보겠습니다. 데이터를 살펴보면 첫 번째 행뿐만 아니라 두 번째 행에도 칼럼명이 포함되어 있어, 데이터 가공이 필요하다는 것을 알 수 있습니다. 이 구조를 염두에 두고 데이터를 처리해 나가겠습니다.

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

# 확인용 데이터 로드
tmp <- read_xls("데이터/수도권 주요관광지점 입장객/주요관광지점 입장객_서울_2023.xls")

# 데이터 확인
head(tmp)

## # A tibble: 6 × 6
##   시도       군구   관광지 `내/외국인`    총계 `(2023년 ~ 2023년)`
##   <chr>      <chr>  <chr>  <chr>         <dbl> <chr>              
## 1 <NA>       <NA>   <NA>   <NA>             NA 2023년             
## 2 서울특별시 종로구 경복궁 내국인      4485430 4485430            
## 3 서울특별시 종로구 경복궁 외국인      1094475 1094475            
## 4 서울특별시 종로구 경복궁 합계        5579905 5579905            
## 5 서울특별시 종로구 종묘   내국인       272304 272304             
## 6 서울특별시 종로구 종묘   외국인        66629 66629

폴더 내 파일 목록 가져오기

list.files 함수를 사용해 특정 폴더에 저장된 확장자가 ’.xls’인 파일 목록을 가져옵니다. 이 목록을 file_list 변수에 저장합니다.

# 데이터 폴더 경로
folder_path <- "데이터/수도권 주요관광지점 입장객"

# 확장자가 .xls인 파일 목록 가져오기
file_list <- list.files(folder_path, pattern = "\\.xls$", full.names = TRUE)

# 파일 목록 확인
file_list

## [1] "데이터/수도권 주요관광지점 입장객/주요관광지점 입장객_경기_2023.xls"
## [2] "데이터/수도권 주요관광지점 입장객/주요관광지점 입장객_서울_2023.xls"
## [3] "데이터/수도권 주요관광지점 입장객/주요관광지점 입장객_인천_2023.xls"

여러 파일 데이터 가공 및 병합

각 파일의 데이터를 읽어와 필요한 열만 선택한 후, 하나의 테이블로 병합하겠습니다. 이를 위해 lapply 함수를 사용하여 파일 목록의 각 파일에 대해 동일한 작업을 적용합니다.

lapply는 리스트를 입력으로 받아 각 요소에 지정한 함수를 적용한 결과를 리스트로 반환합니다. 이를 이용하면, 파일 목록을 입력으로 받아 각 파일의 데이터를 읽고 가공하는 작업을 쉽게 반복할 수 있습니다.

데이터 읽기: read_xls 함수를 사용해 두 번째 행부터 데이터를 읽습니다
열 선택: 필요한 열(예: 첫 번째부터 다섯 번째 열까지)만 선택합니다
칼럼명 지정: 데이터 병합을 위해 칼럼명을 통일합니다
데이터 병합: bind_rows 함수를 사용해 모든 데이터를 하나의 테이블로 결합합니다

# 모든 파일 읽고 가공
all_data <- lapply(file_list, function(file) {
  data <- read_xls(file, skip = 1) %>%  # 두 번째 행부터 읽기
    select(1:5)                  # 필요한 열 선택
  
  colnames(data) <- c("시도", "시군구", "관광지", "구분", "총계")
  
  return(data)
})

# 하나로 병합
united_data <- bind_rows(all_data)

# 데이터 확인
head(united_data)

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

이제 여러 파일로 나뉘어 있던 데이터를 하나의 테이블로 성공적으로 병합했습니다. 데이터를 이렇게 한 번에 병합한 후 가공 작업을 진행하면, 파일별로 따로 분석하는 비효율을 피하고 일괄적으로 처리할 수 있어 시간을 크게 절약할 수 있습니다. 예를 들어, 서울, 경기, 인천 데이터를 각각 열어서 동일한 작업을 반복하기보다는, 병합된 데이터를 활용해 한 번에 계산하고 분석하는 것이 훨씬 효과적입니다. 구조가 동일한 파일을 병합할 일이 있다면, 이 방법으로 데이터 분석 과정을 더 효율적으로 관리해 보세요!