판다스를 활용한 데이터 전처리 및 MySQL Workbench 데이터 가공

 

판다스를 활용한 데이터 전처리 및 MySQL Workbench 데이터 가공

 

 

이번 포스팅 에서는 Python의 pandas를 이용하여 데이터를 전처리한 뒤, MySQL Workbench를 통해 데이터를 가공하고 이를 다시 export하는 학습 과정을 정리합니다.
이 과정은 실제 프로젝트에서 공공 데이터를 RDBMS로 적재하여 분석하거나 가공할 때 매우 자주 사용되는 방법론입니다.

 

 

 

1. 학습 배경

이번 학습은 공공데이터 포털에서 제공하는 전국자동차검사소표준데이터를 활용하여 데이터를 수집하고, 이를 가공 및 전처리하는 과정으로 진행되었습니다.
이 데이터는 CSV 파일 형태로 제공되며, 실무에서의 데이터 정제 및 DB 적재 프로세스를 체험해볼 수 있는 좋은 사례가 됩니다.

주요 학습 목표는 다음과 같습니다.

1. pandas를 이용한 데이터 전처리 이해 및 활용.
2. MySQL Workbench를 사용한 데이터 적재 및 SQL 기반 데이터 가공.
3. 적재된 데이터를 CSV 파일로 재추출(export)하여 결과물 생성.

 

 

 

 

2. 학습 순서

2-1. 공공데이터 다운로드 및 데이터 확인

• 데이터 출처
  공공데이터 포털: 전국자동차검사소표준데이터

전국자동차검사소표준데이터

 

 

2-2. 데이터 전처리 (pandas 활용)

Python의 pandas 라이브러리를 활용하여 다운로드한 데이터를 전처리합니다.

import pandas as pd  

# 데이터 로드  
file_path = '전국자동차검사소표준데이터.csv'  
df = pd.read_csv(file_path, encoding='cp949')  

# 불필요한 컬럼 제거  
df = df[['자동차검사소명', '소재지도로명주소', '위도', '경도', '검사소전화번호', '검사소인력수', '운영시간', '제공기관코드', '제공기관명', '데이터기준일']]  

# 결측치 처리  
df['검사소인력수'] = df['검사소인력수'].fillna(0)  

# 위도/경도 소수점 2자리로 제한  
df['위도'] = df['위도'].round(2)  
df['경도'] = df['경도'].round(2)  

# 특수문자 제거 및 공백 제거  
df['자동차검사소명'] = df['자동차검사소명'].str.replace(r'[^가-힣a-zA-Z0-9 ]', '', regex=True).str.strip()  

# 결과 확인  
print(df.head())

 

 

 

 

 

 

2-3. MySQL Workbench를 활용한 데이터 적재

1. MySQL Workbench 설치 및 설정
   • MySQL 또는 MariaDB를 설치합니다.
   • 데이터베이스 및 테이블 구조는 아래와 같이 설정합니다.

-- 데이터베이스 생성  
CREATE DATABASE min;  
USE min;  

-- 테이블 생성  
CREATE TABLE min_db (  
    car_test_nm VARCHAR(255),  
    addr_road VARCHAR(255),  
    x DECIMAL(10, 2),  
    y DECIMAL(10, 2),  
    tel VARCHAR(20),  
    tester INT,  
    work_time VARCHAR(50),  
    agency_cd VARCHAR(20),  
    agency_nm VARCHAR(100),  
    user_id VARCHAR(20),  
    reg_dt DATE  
);

 

 

 

2. CSV 데이터 Import

• pandas로 전처리한 데이터를 다시 CSV 파일로 저장한 후, MySQL Workbench를 사용하여 테이블로 데이터를 Import합니다.

 

 

# 전처리된 데이터 저장  
df.to_csv('processed_data.csv', index=False, encoding='utf-8-sig')

 

MySQL Workbench에서 Table Data Import Wizard를 사용하여 파일을 테이블로 적재합니다.

 

 

 

3. SQL 쿼리를 활용한 추가 가공

-- user_id 입력
UPDATE min_db SET user_id = 'mins';  

-- 특수문자 제거 및 공백 처리  
UPDATE min_db  
SET car_test_nm = REPLACE(REPLACE(car_test_nm, ' ', ''), '-', '');  

-- 결측값 처리  
UPDATE min_db  
SET tester = 0  
WHERE tester IS NULL;

 

 

 

2-4. 데이터 Export

가공된 데이터를 특정 지역 기준으로 추출하여 CSV 파일로 저장합니다.

-- 특정 지역 데이터 추출  
SELECT * FROM min_db  
WHERE addr_road LIKE '%서울%';  

-- Export: Workbench의 Export 기능 사용

 

 

 

3. 학습 결과 및 느낀 점

이번 학습을 통해 데이터를 전처리하고, 데이터베이스를 활용하여 데이터를 가공하는 과정을 체험해봤다.
pandas와 SQL 각각의 강점을 활용한 데이터 처리의 효율성을 배울 수 있었으며, 실무에서의 데이터 전처리 중요성을 다시금 느꼈다.

추후에는 더 복잡한 데이터 가공 실습이나, 자동화 프로세스를 구축해보는 방향으로 학습해보고자 한다.