[Python] 1:n 관계의 데이터를 1:1 관계로 변환하기

Sehee·2024년 7월 17일

Python 활용기

목록 보기

2/4

시작하며,

회사 서비스의 데이터를 추출하여 정제할 일이 생겼다
지금까지의 데이터를 분석하기 위함이나, 기획자님이 머리 싸매고 절규하시길래 도와드렸다
~~(구글 스프레드 시트는 본인도 익숙하지 않아서 기획자님과 나란히 앉아서 같이 머리 싸매긴 했지만,,,,)~~

다른 부분은 GPT랑 싸우면서 잘 해결했는데, 유독 한 부분에서 막혀서 계속 안되길래 파이썬으로 해결했다

데이터 통합

요구사항 분석

쉽게 예를 들자면, 회원 테이블과 제품 테이블, 주문 테이블 3가지를 엮은 것이다
어떤 회원이 어떤 제품을 많이 구매하는지를 확인하기 위해, 주문 테이블에서 회원과 제품을 추출하고자 한다

요구사항 분석

주문 테이블에는 회원id와 제품id가 있다

주문 테이블의 회원id로 회원 테이블에서 회원 이름, 성별, 연령대를 가져온다

주문 테이블의 제품id로 제품 테이블에서 제품명, 가격을 가져온다

여러 제품을 한 번에 구매한 경우, 회원id와 상품id가 1:n 형태이며, 이를 1:1 형태로 변환한다

여기서, 가장 마지막 요구사항에서 막힌 것이었다

여러 제품을 한 번에 구매한 경우, 다음과 같이 표시된다

회원id	제품id
12	32,33,34
13	31,40
15	53,43,54,15,86
...	...

⇡ before 테이블

그러나 우리가 원하는 테이블은 다음과 같이 회원id와 제품id가 1:1 관계를 가진다

회원id	제품id
12	32
12	33
12	34
13	31
13	40
15	53
...	...

⇡ after 테이블

데이터 변환을 위한 작업; 문자열로 병합

이를 위해선, 데이터를 우선 문자열로 병합해둘 필요가 있다
before 테이블에서 회원id와 제품id를 구분자로 병합하는데, 회원id와 제품id 사이의 구분자($)는 제품id끼리의 구분자(@)와 달라야한다!
확실하게 구분되게 하기 위해, 회원id$제품id@제품id@... 형태로 병합했다

회원id	제품id	병합된 문자열
12	32,33,34	12$32@33@34
13	31,40	13$31@40
15	53,43,54,15,86	15$53@43@54@15@86
...	...	...

병합된 문자열 열만 새로운 시트에 붙여넣고 .csv 파일로 내보내기하면 된다

파이썬으로 데이터 변환

pandas 라이브러리를 활용했는데, 로컬에서 실행하는 것보다 구글 코랩을 활용하는 게 덜 귀찮을 거라고 장담한다

csv 파일 읽어오기

코랩에서 일단 냅다 csv 파일을 업로드한 후, csv 파일을 잘 읽어오는지 먼저 확인해보자

import pandas as pd

# CSV 파일 경로 설정
csv_file = './sample_data/user_and_product_ids.csv'

# CSV 파일 불러오기
df = pd.read_csv(csv_file, header=None, names=['data'])

# head 출력
print(df.head())

그러면 데이터프레임의 일부만 출력될텐데, 오류가 나지 않고 값이 잘 출력되면 읽어오는데에는 성공한 것이다

오류가 난다면, 파일의 경로가 맞는지, 파일명이 맞는지 등을 먼저 확인해보는 게 좋을 것 같다

데이터 변환하기

이제 읽어온 csv 파일에서 데이터를 원하는 형태에 맞게 변환하면 된다

데이터를 저장해둘 리스트(변환된 데이터 리스트)를 하나 만들고,
반복문으로 csv 파일 데이터 한 행씩 문자열을 구분자로 잘라 리스트에 삽입하는 방법이다

제품id가 여러개가 될 수 있으므로, 회원id와 제품id로 우선 $로 구분하여 데이터를 자르고, 제품id는 다시 @로 구분하여 잘라 제품ids 리스트에 저장한다

제품ids 리스트의 각 제품id가 한 행이 되어야 하므로, 간단하게 for문 돌려서 변환된 데이터 리스트에 저장하면 된다

# 데이터를 저장할 리스트
transformed_data = []

# 데이터 정제 및 변환
for index, row in df.iterrows():
    entries = row['data'].split(',')
    for entry in entries:
        member_id, product_ids = entry.split('$')
        products = product_ids.split('@')
        for product in products:
            transformed_data.append({
                '회원id': member_id.strip(),  # 공백 제거
                '제품id': product.strip()    # 공백 제거
            })

데이터를 다시 구글 스프레드 시트로 가져오기 위해, 판다스 데이터프레임으로 바꾸고 csv 파일로 내보낸다

# 변환된 데이터를 데이터프레임으로 변환
transformed_df = pd.DataFrame(transformed_data)

# 변환된 데이터프레임을 CSV 파일로 내보내기
output_csv = 'transformed_data.csv'
transformed_df.to_csv(output_csv, index=False, encoding='utf-8-sig')

print(f"변환된 데이터가 {output_csv} 파일로 저장되었습니다.")