범주형 데이터 정규화

11/10(화)

한 줄 요약: 제각각인 문장·표기("상수도 긴급 누수복구 공사", "상수도긴급누수복구공사")를 정해진 몇 가지 값("상수도")으로 맞추는 일이다. 이렇게 해야 "종류별로 세고 거르기"가 된다.

📍 만난 상황

• 2026-11-10 — use_llm 범주 정규화가 무슨 일인지 묻다가 이어진 질문이다(17:29). 직전에 "에이전트를 안 쓰니 시맨틱 레이어는 꼭 필요 없다. 다만 자유 문장 category를 표준 열(category_std)로 만들면 화면에서 쓸모가 있다"는 답을 들었다. 그래서 "원문도 있는데 category의 자연어를 LLM으로 분류하는 작업이 왜 필요한지"를 물었다. 이어서 LLM이 꼭 필요한지도 확인했다. 도로굴착 데이터로 재 보니 키워드 규칙 8개로 88.5%가 분류됐다. → 그날 일지

🧩 쉽게 말하면

학급 설문에서 "좋아하는 과일"을 자유롭게 쓰게 했다고 하자. "사과", "사과요", "빨간 사과", "apple", "부사"가 다 따로 적혀 있다. 이대로 세면 사과 표가 5칸으로 쪼개진다. "사과를 고른 사람이 몇 명?"에 답하려면 누군가 이 다섯을 "사과" 하나로 묶어야 한다. 원래 답지는 그대로 두고, 옆에 "분류: 사과" 칸을 하나 더 붙이는 것이 범주 정규화다.

📖 제대로 알기

용어

• 범주형 데이터(categorical data: 정해진 몇 가지 값 중 하나를 갖는 데이터). 예: 공사 종류 = 상수도·하수도·가스.
• 자유 텍스트(free text: 아무 문장이나 들어가는 값). 예: "23년 서울서초 사임당로 지중화공사".
• 카디널리티(cardinality: 한 열에 있는 서로 다른 값의 개수). 범주라면 보통 수 개~수십 개다. 도로굴착 category는 53,361개였다.
• 정식값(canonical value: 대표로 정한 값 하나)과 동의어(같은 뜻의 다른 표기).
• 매핑 표(mapping table: "원문 값 → 정식값"을 적어 둔 표). 분류 결과를 여기에 쌓아 다시 쓴다.
• 코드표(code table: 018001 같은 코드의 뜻을 적은 기준표). 코드는 분류가 아니라 풀이만 하면 된다.

왜 필요한가: 세고 거르려면 값이 유한해야 한다

GROUP BY(같은 값끼리 묶기)와 WHERE x = 값(거르기)은 글자가 정확히 같아야 같은 것으로 본다. 사람 눈에는 같은 뜻이어도 띄어쓰기 하나가 다르면 다른 값이다. 그래서 자유 문장 그대로는 "종류별 건수", "이 종류만 보기"를 할 수 없다.

같은 이름, 다른 뜻의 "정규화"

말	무엇을 맞추나	예
범주 정규화(이 문서)	값의 표기를 정해진 범주로	"누수복구공사" → 상수도
위치·시간 정규화	원천마다 다른 위치·시간 형식을 공통 열로	좌표 → h3_r9, 날짜 → t_start
DB 정규화	표 구조를 중복 없이 나누기	한 표 → 사용자 표 + 즐겨찾기 표

⚙️ 어떻게 동작하나

flowchart LR
  R["원문 category"] --> U["고유값만 추리기"]
  U --> C["공백·기호 정리"]
  C --> K["키워드 규칙"]
  K -->|"남은 것"| L["LLM 분류"]
  L --> H["사람 검토<br/>애매하면 보류"]
  K --> M[("매핑 표")]
  H --> M
  M --> S["category_std 열"]

행마다 분류하지 않고 고유값마다 한 번 분류한다. 결과는 매핑 표에 쌓아 두고 다시 쓴다.

1. 표준 항목을 정한다(사람). 예: 공사 종류 = 상수도·하수도·전력/통신 지중화·가스·도로 포장·시설물 보수·기타·보류.
2. 고유값만 추린다. 행이 수십만 개여도 분류할 대상은 고유값이다. 같은 문장이 245번 나오면 한 번만 분류한다.
3. 기계적으로 정리한다. 띄어쓰기·괄호·숫자를 지우면 "상수도 긴급 누수복구 공사"와 "상수도긴급누수복구공사"가 같아진다.
4. 규칙으로 분류한다. "누수"·"수도관" → 상수도, "지중화" → 전력/통신처럼. 대부분 여기서 끝난다.
5. 남은 것만 LLM에 묻는다. 뜻을 알아야 분류되는 표현이 여기에 남는다.
6. 사람이 검토한다. 애매하면 억지로 넣지 않고 "보류"로 둔다. 잘못 넣으면 통계가 조용히 틀린다.
7. 매핑 표로 표준 열을 붙인다. 원문은 attrs에 그대로 둔다. 새 값이 들어오면 매핑 표에 없는 값만 다시 분류한다.

🧪 예시

• 예시 1 · 일상에 빗대어: 설문 응답 300장에 서로 다른 답이 40가지 적혀 있다. 선생님은 300장을 다 읽지 않는다. 40가지만 칠판에 적고 옆에 "사과/배/포도/기타"를 붙인다. 그다음 300장은 칠판을 보고 기계적으로 센다. 처음 보는 답이 나오면 칠판에 한 줄만 더한다.

• 예시 2 · 실제로는: 도로굴착 공사 데이터다(표 이름은 예시).

  -- ① 고유값과 건수 보기
  SELECT category, count() AS n
  FROM disaster.norm_road_dig
  GROUP BY category
  ORDER BY n DESC;
  

  ① 결과 맨 위에 "상수도긴급누수복구공사 245", "상수도 긴급 누수복구 공사 139"가 따로 나온다. 묶음은 5만 개가 넘는다.

RULES = [
    ("상수도", ["상수도", "누수", "수도관"]),
    ("하수도", ["하수", "우수관", "맨홀"]),
    ("전력통신", ["지중화", "통신"]),
    ("가스", ["가스"]),
]

def classify(text: str) -> str:
    t = text.replace(" ", "")              # ② 띄어쓰기 차이 없애기
    for code, words in RULES:              # ③ 위에서부터 차례로
        if any(w in t for w in words):
            return code
    return "보류"                           # ④ 모르면 추측하지 않음

② 245건과 139건이 같은 문자열이 된다. ③ 규칙 순서가 결과를 바꾼다. "하수맨홀과 상수도관"처럼 둘 다 걸리는 문장이 있으면 검토 대상으로 뺀다. ④ 남은 "보류"만 LLM이나 사람에게 넘긴다.

-- ⑤ 매핑 표로 종류별 건수
SELECT m.category_std, count() AS n
FROM disaster.norm_road_dig AS r
JOIN category_map AS m ON r.category = m.raw_value
GROUP BY m.category_std;

⑤ 이제 "상수도 384(=245+139+…)"처럼 한 줄로 나온다. 화면에서 "공사 종류: 상수도" 필터도 category_std = '상수도' 하나로 걸린다.

⚖️ 비교 · 흔한 오해

방법	장점	문제
글자 검색(LIKE '%누수%')	준비가 필요 없다	"상수도관 파열"은 빠지고 엉뚱한 행이 섞인다
사람이 하나씩	정확하다	고유값 5만 개는 너무 많다
키워드 규칙	싸고 빠르고 설명 가능	표현이 다양하면 규칙이 끝없이 는다
LLM	뜻을 보고 분류("지중화" → 전력/통신)	사람 검토와 "애매하면 보류"가 꼭 필요

• 헷갈렸던 점: "원문이 남아 있는데 왜 바꾸나?" 원문을 바꾸는 게 아니다. 옆에 표준 열을 더하는 것이다. 원문은 행 하나를 자세히 볼 때 쓰고, 표준 열은 여러 행을 세고 거를 때 쓴다. 쓰는 곳이 다르다.
• 왜 그렇게 생각하기 쉬운가: 화면에서 행을 하나씩 보면 원문으로 충분하다. 사람은 뜻을 읽을 수 있기 때문이다. 하지만 GROUP BY는 뜻을 못 읽는다. 문제는 집계할 때 처음 드러난다.
• 오해: "LLM이 있어야 한다." 이 일의 본질은 분류이고, LLM은 수단 중 하나다. 그날 재 본 결과처럼 규칙만으로 대부분 풀리는 경우가 많다. 규칙부터 해 보고 남은 것에만 LLM을 쓴다.
• 오해: "코드 값(018001)도 LLM으로." 코드는 뜻이 이미 정해져 있다. 코드표를 구해 풀이하면 된다.

🛠️ 실무에서는

• 할 때: "종류별 건수", "이 종류만 지도에 칠하기", "원인 중 하수관 노후·파손이 몇 %" 같은 질문이 계획에 있을 때.
• 안 해도 될 때: 그런 질문이 없고 행을 하나씩 보기만 할 때. 원문이 attrs에 있으니 지금도 충분하다.
• 체크리스트
• 먼저 고유값 개수(카디널리티)를 잰다. 수십 개면 사람이 바로 매핑하면 된다.
• 문장인가, 코드인가? 코드면 코드표부터 찾는다.
• 표준 항목에 "기타"와 "보류"가 있는가? 없으면 억지 분류가 생긴다.
• 매핑 표에 분류 방법(규칙·LLM·사람)을 함께 적는다. 나중에 틀린 걸 찾기 쉽다.
• 원문을 지우지 않았는가?
• 문제가 생기면 먼저 볼 것: "보류" 비율, 종류별 건수가 갑자기 바뀐 날(새 표현이 들어왔는지), 규칙 두 개에 동시에 걸린 값.

❓ 자주 묻는 질문

• Q. 새 데이터가 매일 들어오면 매번 다시 분류하나? A. 아니다. 매핑 표에 없는 고유값만 분류한다. 대부분은 이미 본 값이다.
• Q. LLM 비용이 크지 않나? A. 행이 아니라 고유값마다 한 번이고, 규칙으로 걸러진 나머지만 묻는다. 그래서 생각보다 적다.
• Q. 표준 항목은 누가 정하나? A. 사람이다. "누가 어떤 종류별 질문을 할 것인가"를 보고 정한다. 기계가 정하게 두면 쓰는 사람의 질문과 어긋난다.
• Q. 시맨틱 레이어가 없어도 의미가 있나? A. 있다. 대시보드의 범주 필터·종류별 지도는 표준 열만 있으면 된다.

🔭 더 깊이 보기

• 분류 품질은 어떻게 재나? — 표본 몇 백 개를 사람이 맞춰 보고 정확도를 재야 "88%"가 믿을 만한 숫자가 된다.
• 범주가 시간이 지나며 바뀌면(새 공사 종류)? — 표준 항목의 버전 관리가 다음 운영 과제다.
• 문자열 유사도(편집 거리, trigram)로 묶기 — 규칙과 LLM 사이의 중간 수단이다.
• 관련 주제: 시맨틱 레이어의 개념과 용도 · DB 정규화와 연결 표 · 데이터 파이프라인 실행과 갱신 · 지오코딩과 참조 데이터 매칭

📚 참고 자료

• Categorical data (pandas 공식 문서, 영문) — 범주형 데이터가 무엇이고 어떻게 다루는지 · 링크
• Conditional Expressions (CASE) (PostgreSQL 공식 문서, 영문) — 규칙 분류를 SQL로 쓸 때 쓰는 CASE · 링크
• OpenRefine 문서 — Clustering (OpenRefine 공식 문서, 영문) — 표기만 다른 값을 묶어 정리하는 도구와 방법 · 문서

🗓️ 이력

• 2026-11-10: 원문이 있는데 왜 자연어 category를 범주로 분류해야 하는지 물음 → 일지

🕸️ 연결된 주제

바로 이어진 주제 4개전체 지도에서 보기 →

다른 주제 랜덤으로 보기