[무근본 파이썬] Pandas 를 통한 Excel 데이터 활용하기 !

[질문]

 

파이썬으로 엑셀 다른 시트에 있는 값을 vlookup 하여 변경하고
컴마 기준으로 데이터를 split 하여 생성 후 저장 할 수 있을까요?..
배우면서 공부하는 중인데 쉽지 않네요 ㅠ.. 

예를 들어 
첫번째 시트에 밑에 와 같은 데이터가 있으면
ID 성별 좋아하는숫자 좋아하는음식
1 남자  1,2,3            빵
2 여자  1,2              밥
3 남자  2,3,4            빵

두번째 시트에
성별          남자   1
성별          여자   2
좋아하는음식  빵    1
좋아하는음식  밥    2

최종적으로 나와야 하는 데이터 :
ID 성별 좋아하는 숫자1 좋아하는 숫자2 좋아하는 숫자3 좋아하는 숫자4 좋아하는음식
1 1 1 2 3 1
2 2 1 2 2
3 1 2 3 4 1

 

[답변]

 

파이썬에서 엑셀 파일을 다루는 경우에는 대표적으로 pandas 라이브러리를 많이 사용합니다. pandas 라이브러리를 사용하면 엑셀 파일을 읽고 쓰는 것이 매우 쉬워지기 때문입니다.

 

우선 엑셀 파일을 읽어와서 데이터를 처리합니다. pandas의 read_excel() 함수를 사용하여 엑셀 파일을 읽어올 수 있습니다.

import pandas as pd

df1 = pd.read_excel('data.xlsx', sheet_name='Sheet1')
df2 = pd.read_excel('data.xlsx', sheet_name='Sheet2')

위 코드는 data.xlsx 파일에서 Sheet1과 Sheet2 시트의 데이터를 읽어와서 df1, df2 변수에 저장합니다.

이제 df1 데이터프레임의 값을 vlookup 하여 변경하고, 컴마 기준으로 데이터를 분리하여 새로운 열을 생성합니다.

# 성별, 좋아하는 숫자, 좋아하는 음식 데이터프레임 생성
df_gender = pd.DataFrame(df2[df2['column1'] == '성별']['column2'])
df_num = pd.DataFrame(df2[df2['column1'] == '좋아하는숫자'].set_index('column2')['index'])
df_food = pd.DataFrame(df2[df2['column1'] == '좋아하는음식']['column2'])

# 컴마로 분리하여 숫자 열을 분할
df1['좋아하는숫자'] = df1['좋아하는숫자'].str.split(',')
df1['좋아하는숫자'] = df1['좋아하는숫자'].apply(lambda x: [int(i) for i in x])

# 숫자 열을 각각 분할하여 새로운 열 생성
df1 = df1.join(pd.DataFrame(df1['좋아하는숫자'].tolist(), columns=['좋아하는숫자1', '좋아하는숫자2', '좋아하는숫자3', '좋아하는숫자4']))

# vlookup 하여 값을 변경
df1['성별'] = df1['성별'].map(df_gender['column2'])
df1['좋아하는숫자1'] = df1['좋아하는숫자1'].map(df_num['index'])
df1['좋아하는숫자2'] = df1['좋아하는숫자2'].map(df_num['index'])
df1['좋아하는숫자3'] = df1['좋아하는숫자3'].map(df_num['index'])
df1['좋아하는숫자4'] = df1['좋아하는숫자4'].map(df_num['index'])
df1['좋아하는음식'] = df1['좋아하는음식'].map(df_food['column2'])

# 분할한 열 삭제
df1 = df1.drop('좋아하는숫자', axis=1)

# 결과를 엑셀 파일에 저장
with pd.ExcelWriter('result.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)

최종적으로 생성된 result.xlsx 파일의 Sheet1 시트에는 다음과 같은 결과가 저장됩니다.

 

ID 성별 좋아하는숫자1 좋아하는숫자2 좋아하는숫자3 좋아하는숫자4 좋아하는음식
1 1 1 2 3 0 1
2 2 1 2 0 0 2
3 1 2 3 4 0 1

 

위 코드에서 column1, column2, index 등은 예시 데이터의 열 이름에 맞춰서 수정해주어야 합니다. 또한 데이터를 읽어올 때 사용하는 read_excel() 함수의 매개변수는 예시 파일과 다를 수 있으므로, 해당 파일에 맞게 수정해주어야 합니다.