Pandas merge 열 충돌·중복행 생길 때 완전 정리

서로 다른 테이블을 pandas.merge로 합치다 보면 두 가지 문제가 거의 항상 따라옵니다. 첫째는 열 이름 충돌로 인해 *_x, *_y 같은 suffix가 붙어 컬럼이 난잡해지는 문제, 둘째는 중복행(행 폭증) 이 생겨 집계 값이 틀어지거나 데이터가 부풀어 오르는 문제입니다.

이 글에서는 단순히 "suffix를 바꾸세요" 수준이 아니라, 왜 이런 일이 생기는지(조인 카디널리티), 어디서부터 진단해야 하는지(키 중복 검사, 조인 결과 검증), 그리고 팀에서 재사용 가능한 정리 패턴(사전 중복 제거, validate, indicator, suffix 정규화)을 한 번에 정리합니다.

데이터를 CSV에서 읽어온 직후부터 문제가 시작되는 경우도 많으니, 인코딩/문자 깨짐으로 키가 달라지는 이슈까지 함께 점검해두면 좋습니다. 관련해서는 Python CSV UTF-8 깨짐·UnicodeDecodeError 5분 해결도 같이 보면 도움이 됩니다.

1) merge가 꼬이는 대표 증상

1-1. 열 이름 충돌로 `_x`, `_y`가 생긴다

양쪽 DataFrame에 같은 컬럼명(예: status, created_at)이 존재
조인 키가 아닌데도 동일 이름이면 자동으로 suffix가 붙음
이후 어떤 컬럼이 "진짜"인지 혼란이 생김

1-2. 조인 후 행 수가 갑자기 늘어난다

원본은 df_left 10만 행, df_right 5만 행인데 merge 결과가 200만 행
대부분 키 중복으로 인해 many-to-many 조인이 발생한 케이스
이 상태에서 합계/평균을 내면 값이 여러 번 더해져 잘못된 지표가 됨

1-3. 조인이 조용히 실패한다(매칭이 거의 없다)

키 컬럼 dtype 불일치: 문자열 vs 정수
공백/제로패딩/대소문자 차이
CSV 인코딩 문제로 보이지 않는 문자 포함

2) 핵심 원인: 조인 카디널리티를 먼저 이해하자

merge는 SQL 조인과 동일하게 동작합니다. 키가 유일(unique)하다는 전제가 깨지면 결과는 기하급수적으로 늘어날 수 있습니다.

one-to-one: 양쪽 키가 유일
one-to-many: 한쪽만 유일
many-to-many: 양쪽 모두 중복 키 존재, 결과 폭증의 주범

따라서 해결의 출발점은 항상 다음 질문입니다.

조인 키가 정말 키인가(유일해야 하는가)?
유일해야 한다면, 어디에서 중복이 생겼는가?
유일하지 않아도 된다면, 중복을 어떤 규칙으로 축약할 것인가(최신값, 합계, 우선순위)?

3) 진단 체크리스트: merge 전에 반드시 확인

아래 코드는 merge 전에 습관처럼 넣을 만한 진단 템플릿입니다.

import pandas as pd

def profile_key(df: pd.DataFrame, key_cols):
    if isinstance(key_cols, str):
        key_cols = [key_cols]

    n = len(df)
    nunique = df[key_cols].drop_duplicates().shape[0]
    dup_rows = df.duplicated(subset=key_cols, keep=False).sum()

    return {
        "rows": n,
        "unique_keys": nunique,
        "dup_key_rows": int(dup_rows),
        "dup_key_ratio": float(dup_rows / n) if n else 0.0,
    }

left_prof = profile_key(df_left, ["user_id"])
right_prof = profile_key(df_right, ["user_id"])
print(left_prof)
print(right_prof)

dup_key_rows가 0이 아니면, 해당 DataFrame은 그 키 기준으로 유일하지 않습니다.
유일해야 하는 키인데 중복이 있다면, merge 결과는 기대와 달라질 가능성이 큽니다.

키 dtype 불일치도 같이 확인

print(df_left["user_id"].dtype, df_right["user_id"].dtype)

# 예: 한쪽은 int64, 다른쪽은 object(문자열)
df_left["user_id"] = df_left["user_id"].astype("string").str.strip()
df_right["user_id"] = df_right["user_id"].astype("string").str.strip()

4) 중복행(행 폭증) 문제: 원인별 해결 패턴

4-1. 유일해야 하는데 중복이 생긴 경우: 먼저 중복을 "정의"하고 제거

예를 들어 df_right는 사용자별 최신 프로필 1건만 있어야 하는데, 이력 데이터가 섞여 중복이 발생했다고 가정합니다.

# 최신 updated_at 한 건만 남기기
idx = df_right.sort_values("updated_at").groupby("user_id")["updated_at"].idxmax()
df_right_latest = df_right.loc[idx].copy()

merged = df_left.merge(df_right_latest, on="user_id", how="left")

이 방식의 핵심은 "중복 제거"가 아니라 업무 규칙을 코드로 명시하는 것입니다.

최신값 1건
상태가 ACTIVE인 것 우선
점수가 높은 것 우선
여러 건이면 합계/평균으로 축약

4-2. many-to-many가 의도된 경우: 조인 뒤에 집계로 형태를 고정

예를 들어 사용자와 주문을 붙이면 사용자 1명당 주문이 여러 개라 one-to-many가 됩니다. 이 상황에서 사용자 테이블에 주문 금액 합계를 붙이고 싶다면, 먼저 주문을 사용자 단위로 집계한 뒤 조인해야 합니다.

orders_agg = (
    df_orders
    .groupby("user_id", as_index=False)
    .agg(order_cnt=("order_id", "nunique"), total_amount=("amount", "sum"))
)

merged = df_users.merge(orders_agg, on="user_id", how="left")

조인 후에 집계하면 중복으로 인해 이미 값이 불어난 상태에서 계산할 수 있으니, 집계는 가급적 merge 전에 하는 습관이 안전합니다.

4-3. merge 결과가 예상과 다를 때: `indicator`로 매칭 상태를 눈으로 확인

merged = df_left.merge(
    df_right,
    on="user_id",
    how="left",
    indicator=True,
)

print(merged["_merge"].value_counts())
# left_only / both / right_only

unmatched = merged.loc[merged["_merge"] == "left_only", ["user_id"]].drop_duplicates()

left_only가 많으면 키 정규화(공백, 패딩, dtype) 문제일 가능성이 큼
both인데 행 수가 폭증하면 중복 키(카디널리티) 문제일 가능성이 큼

4-4. merge 자체에서 검증을 강제: `validate` 옵션

validate는 기대한 카디널리티가 깨질 때 즉시 예외를 발생시켜, "조용히" 잘못된 결과가 퍼지는 걸 막아줍니다.

# df_left.user_id는 유일, df_right.user_id도 유일해야 한다면
merged = df_left.merge(
    df_right,
    on="user_id",
    how="left",
    validate="one_to_one",
)

자주 쓰는 값:

"one_to_one"
"one_to_many"
"many_to_one"
"many_to_many" (검증 의미가 약하므로 보통은 앞의 3개를 권장)

운영 파이프라인에서 데이터 품질을 지키려면, validate는 사실상 필수에 가깝습니다.

5) 열 이름 충돌 정리: suffix 이후의 "정규화"까지

5-1. suffix를 명시하고, 충돌 컬럼을 사전에 선별

merged = df_left.merge(
    df_right,
    on="user_id",
    how="left",
    suffixes=("_left", "_right"),
)

기본 suffix인 "_x", "_y"보다 의미가 명확해집니다.

5-2. 정말 필요 없는 중복 컬럼은 merge 전에 제거

가장 깔끔한 방법은 조인에 필요한 컬럼만 가져오는 것입니다.

needed_cols = ["user_id", "tier", "status"]
df_right_small = df_right[needed_cols].copy()

merged = df_left.merge(df_right_small, on="user_id", how="left")

이렇게 하면 충돌 자체가 사라집니다.

5-3. 같은 의미의 컬럼이면 하나로 합치고 드롭

예를 들어 양쪽에 status가 있고, 오른쪽이 더 최신/권위 있는 값이라고 합시다.

merged = df_left.merge(
    df_right[["user_id", "status"]],
    on="user_id",
    how="left",
    suffixes=("", "_ref"),
)

# 오른쪽 값이 있으면 오른쪽으로 덮어쓰기
merged["status"] = merged["status_ref"].combine_first(merged["status"])
merged = merged.drop(columns=["status_ref"])

반대로 왼쪽이 기준이면 combine_first의 순서를 바꾸면 됩니다.

5-4. 충돌 컬럼을 일괄 처리하는 유틸 패턴

충돌 컬럼이 많다면 규칙적으로 정리하는 함수가 유용합니다.

def coalesce_columns(df, base_cols, ref_suffix="_ref"):
    for c in base_cols:
        ref = f"{c}{ref_suffix}"
        if ref in df.columns and c in df.columns:
            df[c] = df[ref].combine_first(df[c])
            df = df.drop(columns=[ref])
    return df

merged = df_left.merge(
    df_right,
    on="user_id",
    how="left",
    suffixes=("", "_ref"),
)

merged = coalesce_columns(merged, base_cols=["status", "tier", "country"])

6) merge 키 설계 실전 팁: 문자열 키는 특히 정규화가 중요

현업에서 merge 실패의 상당수는 키 값이 "같아 보이지만" 실제로는 다르기 때문입니다.

공백: "A" vs "A "
제로패딩: "0012" vs "12"
대소문자: "abc" vs "ABC"
로캘/인코딩: 눈에 안 보이는 제어문자

정규화 예시:

def normalize_id(s: pd.Series) -> pd.Series:
    s = s.astype("string")
    s = s.str.strip()
    s = s.str.upper()
    # 필요 시 제로패딩 규칙 적용
    # s = s.str.zfill(6)
    return s

df_left["user_id"] = normalize_id(df_left["user_id"])
df_right["user_id"] = normalize_id(df_right["user_id"])

7) 재현 가능한 디버깅 루틴: "merge 전/후"를 수치로 고정

merge 이슈는 감으로 보면 끝이 없습니다. 아래처럼 숫자로 고정하면 원인 파악이 빨라집니다.

key = ["user_id"]

print("left:", profile_key(df_left, key))
print("right:", profile_key(df_right, key))

merged = df_left.merge(df_right, on=key, how="left", indicator=True)

print("merged rows:", len(merged))
print("merge indicator:\n", merged["_merge"].value_counts())

# 조인 결과에서 키 기준 중복이 얼마나 생겼는지
print("merged dup keys:", merged.duplicated(subset=key, keep=False).sum())

merge 이후 merged dup keys가 급증하면, 조인으로 인해 one-to-many 또는 many-to-many가 발생했다는 뜻입니다.
이때는 "어느 쪽이 중복 키를 갖고 있는지"를 다시 확인하고, 4장에서 소개한 축약 전략으로 돌아가야 합니다.

8) 운영 관점: merge는 데이터 파이프라인의 "조인 계약"이다

애플리케이션에서 API 계약을 깨면 장애가 나는 것처럼, 데이터 파이프라인에서도 조인 카디널리티 계약이 깨지면 지표가 망가집니다. 따라서 다음을 권장합니다.

merge마다 validate로 계약을 코드에 박아두기
키 정규화 함수(공백 제거, dtype 통일)를 공용 유틸로 만들기
many-to-many가 의도된 경우, merge 전에 집계해서 형태를 고정하기
열 충돌은 suffix로 끝내지 말고, "권위 컬럼"을 정해 coalesce 후 드롭하기

분산 시스템에서 중복 이벤트로 정확히 한 번 처리가 깨지는 것처럼, 데이터 조인에서도 중복 키가 들어오면 결과가 폭증합니다. 이런 관점은 Kafka Exactly-Once 실패? 멱등키·Outbox 실전에서 다루는 "멱등성" 사고방식과도 연결됩니다.

9) 결론: 가장 안전한 merge 템플릿

마지막으로, 실무에서 그대로 복사해 쓸 수 있는 "안전한 merge" 템플릿을 정리합니다.

import pandas as pd

def safe_merge_left(
    left: pd.DataFrame,
    right: pd.DataFrame,
    key,
    right_cols=None,
    validate="many_to_one",
    suffixes=("", "_ref"),
):
    if isinstance(key, str):
        key = [key]

    l = left.copy()
    r = right.copy()

    # 키 dtype 통일 및 공백 제거(필요에 맞게 커스터마이즈)
    for k in key:
        l[k] = l[k].astype("string").str.strip()
        r[k] = r[k].astype("string").str.strip()

    if right_cols is not None:
        r = r[key + right_cols].copy()

    merged = l.merge(
        r,
        on=key,
        how="left",
        validate=validate,
        indicator=True,
        suffixes=suffixes,
    )

    return merged

merged = safe_merge_left(
    df_left,
    df_right,
    key="user_id",
    right_cols=["tier", "status"],
    validate="many_to_one",
)

print(merged["_merge"].value_counts())
merged = merged.drop(columns=["_merge"])

right_cols로 가져올 컬럼을 제한해 열 충돌을 줄이고
validate로 카디널리티를 강제하며
indicator로 매칭 품질을 수치화합니다.

이 3가지만 습관화해도 Pandas merge에서 발생하는 열 이름 충돌과 중복행 문제의 대부분을 예방하거나, 최소한 "언제부터" 깨졌는지 빠르게 특정할 수 있습니다.