파이썬 pandas 실전 분석: CSV·엑셀 병합과 groupby 집계

여러 CSV와 엑셀 파일을 하나로 합쳐 요약표를 만들려면, 먼저 read_csv()와 read_excel()로 열 구조를 통일하고, 같은 구조의 파일은 concat(), 기준키로 연결할 파일은 merge()로 결합한 뒤 groupby().agg()로 집계하시면 됩니다. 실무에서는 병합 전 키 중복과 자료형을 검사하고, 병합 후 행 수가 예상대로인지 확인하는 과정이 가장 중요합니다.

pandas 데이터 병합 공식 문서 확인하기

CSV·엑셀을 안전하게 불러오는 방법

파일을 읽자마자 병합하지 말고 열 이름, 자료형, 결측치, 키의 중복 여부를 먼저 확인해야 합니다. 주문번호나 상품코드처럼 앞의 0이 의미 있는 값은 숫자가 아니라 문자열로 읽어야 합니다. 날짜는 parse_dates로 변환하거나 읽은 뒤 pd.to_datetime()을 적용할 수 있습니다.

import pandas as pd

sales = pd.read_csv(
    "sales_2026_08.csv",
    dtype={"order_id": "string", "product_id": "string"},
    parse_dates=["order_date"],
    usecols=["order_id", "order_date", "product_id", "region", "qty", "amount"]
)

products = pd.read_excel(
    "product_master.xlsx",
    sheet_name="products",
    dtype={"product_id": "string"},
    usecols=["product_id", "category", "product_name"]
)

불러온 뒤에는 df.shape, df.columns, df.dtypes, df.isna().sum()을 확인합니다. 엑셀 파일은 형식에 맞는 읽기 엔진이 필요할 수 있으므로 실행 환경에 관련 의존성이 설치되어 있는지도 점검하십시오.

concat·merge·join 중 무엇을 써야 하나요?

함수 선택은 파일 형식이 아니라 표 사이의 관계로 결정합니다. 같은 열을 가진 월별 파일을 아래로 이어 붙이는 작업과, 서로 다른 열을 가진 두 표를 상품코드로 연결하는 작업은 구분해야 합니다.

방법	적합한 상황	핵심 기준	주의점
concat()	월별·지점별 동일 구조 파일	행 또는 열 방향	열 이름이 다르면 NaN 열이 생길 수 있음
merge()	주문표와 상품표 연결	공통 키와 조인 방식	키 중복 시 행이 예상보다 늘 수 있음
join()	인덱스를 기준으로 연결	인덱스	인덱스 정합성을 먼저 확인

예를 들어 폴더의 월별 CSV를 한 번에 합치려면 파일 목록을 정렬한 뒤 읽고 ignore_index=True로 연결합니다.

from pathlib import Path

files = sorted(Path("sales").glob("sales_*.csv"))
frames = [pd.read_csv(f, dtype={"product_id": "string"}) for f in files]
all_sales = pd.concat(frames, ignore_index=True, sort=False)

각 파일에 출처를 남겨야 한다면 읽을 때 source_file 열을 추가하십시오. 오류가 발견됐을 때 원본 파일을 빠르게 추적할 수 있습니다.

merge 병합과 결과 검증

매출표에 상품 분류를 붙이는 경우 매출표는 같은 상품코드를 여러 번 포함할 수 있지만 상품 마스터의 상품코드는 하나씩만 있어야 합니다. 이 관계를 validate="many_to_one"으로 명시하면 마스터 키가 중복됐을 때 조용히 잘못된 결과를 만들지 않고 오류로 알 수 있습니다.

merged = all_sales.merge(
    products,
    on="product_id",
    how="left",
    validate="many_to_one",
    indicator=True
)

print(merged["_merge"].value_counts())
unmatched = merged.loc[merged["_merge"] == "left_only", "product_id"].unique()
merged = merged.drop(columns="_merge")

left 병합은 왼쪽 매출 행을 보존하므로 누락된 상품코드를 찾기 좋습니다. 반면 양쪽에 모두 있는 데이터만 분석할 목적이면 inner를 사용할 수 있습니다. 다만 pandas의 병합은 양쪽 키가 결측값일 때 서로 매칭될 수 있어 일반적인 SQL 동작과 다를 수 있으므로 키의 결측값을 먼저 제거하거나 별도로 처리하는 편이 안전합니다.

병합 전후에 꼭 비교할 값

• 왼쪽 데이터의 행 수와 병합 결과 행 수
• 키 열의 결측치 개수와 고유값 개수
• 마스터 데이터의 중복 키
• indicator=True로 확인한 미매칭 행
• 병합 전후 금액 합계가 의도대로 유지되는지 여부

열 이름이 양쪽에 겹치면 suffixes=("_sales", "_master")처럼 의미 있는 접미사를 지정하십시오. 자동으로 붙는 _x, _y를 그대로 두면 후속 계산에서 잘못된 열을 선택하기 쉽습니다.

groupby와 agg로 집계표 만들기

groupby는 데이터를 그룹으로 나누고, 각 그룹에 계산을 적용한 뒤 결과를 다시 결합하는 방식입니다. 지역과 상품 분류별 매출액, 판매수량, 주문 수를 한 번에 구하려면 이름 있는 집계를 사용하면 결과 열을 이해하기 쉽습니다.

summary = (
    merged.groupby(["region", "category"], as_index=False, dropna=False)
    .agg(
        total_amount=("amount", "sum"),
        total_qty=("qty", "sum"),
        order_count=("order_id", "nunique"),
        average_amount=("amount", "mean")
    )
    .sort_values("total_amount", ascending=False)
)

as_index=False를 쓰면 그룹 키가 일반 열로 남아 저장과 추가 병합이 편해집니다. 기본 설정에서는 그룹 키가 결측치인 행이 제외될 수 있으므로 미분류 항목도 보고서에 포함하려면 dropna=False를 검토하십시오. 단순 행 개수는 size, 결측값을 제외한 값 개수는 count, 고유 주문 수는 nunique처럼 목적에 맞게 구분해야 합니다.

날짜별·월별 집계가 필요하면 날짜 열이 실제 datetime 자료형인지 확인한 뒤 pd.Grouper(key="order_date", freq="ME") 또는 파생 월 열을 이용할 수 있습니다.

월별 CSV와 상품 엑셀을 합치는 실전 흐름

1. 월별 CSV 파일 목록을 정렬하고 필요한 열만 읽습니다.
2. 각 파일의 열 이름과 자료형을 같은 규칙으로 표준화합니다.
3. concat()으로 월별 매출 행을 합칩니다.
4. 상품 마스터의 키 중복을 검사합니다.
5. merge(..., validate="many_to_one")로 분류 정보를 붙입니다.
6. 미매칭 상품코드를 별도 파일로 저장해 원본을 정비합니다.
7. groupby().agg()로 지역·분류별 집계표를 만듭니다.
8. 총액과 행 수를 대조한 뒤 CSV 또는 엑셀로 저장합니다.

# 마스터 키 검사
duplicated_keys = products.loc[
    products["product_id"].duplicated(keep=False), "product_id"
]
if not duplicated_keys.empty:
    raise ValueError(f"상품 마스터 중복 키: {duplicated_keys.unique()[:10]}")

# 숫자 열 정리
all_sales["amount"] = pd.to_numeric(all_sales["amount"], errors="coerce")
all_sales["qty"] = pd.to_numeric(all_sales["qty"], errors="coerce")

# 분석 전에 손실 규모 확인
print(all_sales[["amount", "qty"]].isna().sum())

errors="coerce"는 변환 불가능한 값을 결측치로 바꾸므로 편리하지만, 바로 삭제하면 원본 오류를 놓칠 수 있습니다. 변환 전후 결측치 증가량과 문제 행을 따로 저장한 뒤 처리 기준을 정하는 것이 좋습니다.

결과 저장과 대용량 성능 관리

CSV는 다른 시스템과 교환하기 쉽고 빠르며, 엑셀은 사람이 검토하는 보고서에 편리합니다. 한글 CSV를 엑셀에서 바로 열 목적이라면 환경에 따라 encoding="utf-8-sig"를 고려할 수 있습니다.

summary.to_csv("sales_summary.csv", index=False, encoding="utf-8-sig")

with pd.ExcelWriter("sales_report.xlsx") as writer:
    summary.to_excel(writer, sheet_name="summary", index=False)
    unmatched_df.to_excel(writer, sheet_name="unmatched", index=False)

엑셀의 서식, 수식, 열 너비까지 세밀하게 자동화해야 한다면 pandas로 데이터를 만든 뒤 openpyxl로 후처리하는 구성이 효율적입니다.

파일이 클 때 줄일 수 있는 메모리

• usecols로 필요한 열만 읽습니다.
• dtype을 명시해 불필요한 객체형 사용을 줄입니다.
• 큰 CSV는 chunksize로 나눠 읽고 부분 집계 후 다시 합칩니다.
• 반복 병합 전에 키와 열을 정리해 중간 DataFrame 크기를 줄입니다.
• 중간 결과를 무조건 여러 복사본으로 만들지 말고 처리 단계별 필요성을 확인합니다.

실수 방지 체크리스트

• 상품코드와 주문번호를 문자열로 읽었는지 확인합니다.
• 월별 파일의 열 이름과 단위를 통일했는지 확인합니다.
• 병합 키의 공백과 대소문자 차이를 정리했는지 확인합니다.
• 마스터 키가 고유한지 검사하고 validate를 설정합니다.
• 병합 전후 행 수와 금액 합계를 비교합니다.
• 미매칭 키를 삭제하지 않고 별도 목록으로 확인합니다.
• count, size, nunique의 차이를 구분합니다.
• 결측 그룹을 포함할지 dropna 기준을 결정합니다.
• 내보낼 때 불필요한 인덱스 열을 저장하지 않도록 index=False를 씁니다.
• 작은 표본으로 결과를 검산한 뒤 전체 파일에 적용합니다.

자주 묻는 질문

1. CSV 파일 여러 개를 가장 간단히 합치는 방법은 무엇인가요?

열 구조가 같다면 각 파일을 DataFrame으로 읽어 리스트에 담고 pd.concat(frames, ignore_index=True)를 사용합니다. 합치기 전에 열 이름과 자료형이 같은지 확인하십시오.

2. concat과 merge의 가장 큰 차이는 무엇인가요?

concat은 같은 구조의 데이터를 행이나 열 방향으로 이어 붙이고, merge는 공통 키를 기준으로 서로 다른 표의 열을 연결합니다.

3. merge 후 행 수가 갑자기 늘어난 이유는 무엇인가요?

양쪽 병합 키에 중복이 있으면 다대다 조합이 만들어질 수 있습니다. 키 중복을 확인하고 기대 관계에 맞춰 validate="one_to_one" 또는 many_to_one을 지정하십시오.

4. left merge와 inner merge 중 무엇을 써야 하나요?

왼쪽 원본 행을 보존하며 미매칭을 찾으려면 left, 양쪽에 공통으로 존재하는 행만 필요하면 inner가 적합합니다. 분석 목적과 누락 허용 여부로 결정합니다.

5. 엑셀 여러 시트를 한 번에 읽을 수 있나요?

pd.read_excel(..., sheet_name=None)을 사용하면 시트 이름을 키로 하는 DataFrame 딕셔너리를 받을 수 있습니다. 필요한 시트만 지정해 읽는 것이 메모리 관리에는 유리합니다.

6. groupby 결과의 그룹 열이 인덱스로 들어가지 않게 하려면 어떻게 하나요?

groupby(..., as_index=False)를 사용하거나 집계 뒤 reset_index()를 적용합니다.

7. 주문 건수는 count와 nunique 중 무엇을 써야 하나요?

한 주문이 여러 상품 행으로 나뉠 수 있다면 고유 주문번호 수를 세는 nunique가 적합합니다. 단순히 결측값이 아닌 행 수를 세려면 count를 씁니다.

8. groupby에서 결측 분류 행이 사라지는 이유는 무엇인가요?

그룹 키의 결측값이 기본적으로 제외될 수 있기 때문입니다. 미분류 항목도 집계하려면 데이터 의미를 확인한 뒤 dropna=False를 사용하십시오.

9. 큰 CSV가 메모리에 들어오지 않으면 어떻게 하나요?

read_csv(chunksize=...)로 나눠 읽어 각 청크를 부분 집계한 뒤, 부분 결과를 다시 groupby로 합산하는 방식을 사용할 수 있습니다.

10. 결과를 엑셀로 저장할 때 인덱스 열을 빼려면 어떻게 하나요?

to_excel(..., index=False)를 지정합니다. 여러 시트는 ExcelWriter 문맥 안에서 각각 저장하면 됩니다.

공식 출처

• pandas 사용자 가이드: Merge, join, concatenate and compare
• pandas 사용자 가이드: Group by
• pandas API: read_csv
• pandas API: read_excel

공식 문서 확인일: 2026년 8월 31일. pandas 버전에 따라 기본값과 지원 옵션이 달라질 수 있으므로 실제 실행 환경의 버전 문서를 함께 확인하십시오.