엑셀과 파이썬 함께 쓰는 방법(예제포함 10월 업데이트)

엑셀과 파이썬은 광범위한 영역에서 데이터를 수집하며 기업의 중대한 의사결정에 기여하는 분석도구이며, 파이썬은 AI 데이터 분석에 활용됩니다. 엑셀과 파이썬 함께 쓰는 방법에 대해 공유하고자 합니다.

엑셀은 누구나 쉽게 사용하지만, 광범위한 영역에서 데이터를 수집하며 기업의 중대한 의사결정에 기여하는 데이터 분석 도구입니다. 파이썬은 이러한 부분에서 엑셀과 많이 닮았습니다. 그 어떤 언어보다 쉽지만, 전 세계에서 코드를 공유하면서 최신 AI와 데이터 분석에 활용됩니다.

엑셀과 파이썬을 함께 쓰면 좋은 이유 

파이썬이 전 세계적으로 많이 쓰이고, 데이터와 AI 분야에서 중요한 언어로 자리 잡은 것은 다음과 같은 세 가지 장점 덕분입니다.

1. 읽기 편하고 공유하기 쉬운 언어

파이썬이 학생이나 전문가 할 것 없이 널리 사용되는 이유는 간결하고 읽기 쉬운 문법으로 다른 사람의 코드를 간편하게 활용할 수 있기 때문입니다. 다른 언어와 달리 코드를 한 줄씩 실행하면서 결과를 확인할 수 있어 프로그램을 완성시켜야 하는 부담이 없고, 읽기 쉬워서 프로그래밍에 대한 지식이 없어도 필요한 부분만 발췌하여 사용할 수 있습니다.

2. 오픈소스, 그리고 활성화된 커뮤니티

파이썬은 교육, 연구, 산업 등 방대한 분야에서 활용되면서 강력한 커뮤니티를 구축했습니다. 수많은 개발자와 연구자가 다양한 해결책과 코드를 공유했으며, 이로 인해 사용자가 다양한 상황에 맞는 코드를 쉽게 찾고 활용할 수 있는 환경이 마련되었습니다. 풍부한 데이터는 AI 학습에도 활용되어 챗GPT와의 대화를 통해 원하는 파이썬 코드를 얻을 수 있습니다.

3. AI와 데이터 분석의 핵심

최신 데이터 분석 및 AI 프로젝트에는 대부분 파이썬을 활용합니다. 이는 파이썬의 강력한 확장성이 다른 언어를 연결하는 접착제 역할을 하기 때문입니다. 파이썬 사용자는 수많은 개인과 비영리 재단을 비롯한 구글, 메타, 아마존 같은 빅테크 기업이 공개한 코드와 알고리즘을 누구보다 빠르게 접하고 쉽게 사용할 수 있습니다.

Part A – 데이터 합치기

데이터가 여러 개로 나뉘어 있을 때 하나로 합치는 것은 엑셀에서 상당히 까다로운 작업입니다. 엑셀의 VLOOKUP 함수를 이용하여 기준 열이 같은 값을 찾을 수는 있지만, 데이터를 통째로 연결하려면 결국 엄청난 양의 수작업이 필요합니다. 이번 장에서는 DataFrame을 활용하여 데이터를 쉽게 합치는 방법을 알아보겠습니다.

공통 항목을 기준으로 데이터 연결하기 

서로 다른 데이터에 공통된 항목(열)이 있다면 해당 항목을 기준으로 데이터를 연결할 수 있습니다. 예를 들어 주민등록번호와 성별, 나이가 있는 데이터와 주민등록번호와 주소가 있는 데이터의 경우 공통 항목인 주민등록번호를 기준으로 성별, 나이, 주소 데이터를 합치는 것이죠.

이때, 공통 항목인 주민등록번호를 기준으로 새로운 데이터를 연결하면 데이터를 추가할 때마다 기준이 되는 주민등록번호 열의 오른쪽으로 데이터가 추가되므로 주민등록번호, 성별, 나이, 주소 순으로 데이터가 나열됩니다.

실습 예제 다운로드

예제는 길벗출판사 홈페이지(www.gilbut.co.kr)에서 다운로드할 수 있습니다. 홈페이지 회원으로 가입하지 않아도 누구나 실습 예제 파일을 다운로드할 수 있습니다.

1. 길벗 홈페이지에서 ‘도서명’를 검색하세요.

2. 해당 도서의 페이지에서 [자료실]을 클릭해 실습 예제 파일을 다운로드하세요.

3. 압축을 해제하여 실습에 활용해 보세요.

1. 각 데이터 영역을 DataFrame으로 생성하고 변수로 저장하기

1) [M1] 셀의 파이썬 코드 입력 창에 다음 코드를 입력하고 실행합니다.

2) [M1]에 [A1:E264] 영역의 데이터가 변수 ‘df_base’로 저장됩니다.

3) [M3] 셀의 파이썬 코드 입력 창에 다음 코드를 입력하고 실행합니다.

4) [M3] 셀에 [G1:K264] 영역의 데이터가 변수 ‘df_merge’로 저장됩니다.

2. pd.merge 함수로 데이터 합치기

1) [M5] 셀의 파이썬 코드 입력 창에 다음 코드를 입력하고 실행합니다.

2) [M5] 셀이 선택된 상태에서 수식 입력 창의 메뉴 아이콘 중 ‘[  ]’, ‘123’을 차례로 클릭하면 변수 df_base와 df_ merge의 데이터가 합쳐진 것을 확인할 수 있습니다.

(이미지 내 진행 순서와 아이콘을 참고해주세요)

✅ Tip. 공통 항목을 기준으로 데이터를 연결할 때, 중복되는 값이 있을 경우 여러 개의 행이 연결되어 오류가 발생할 수 있으므로 중복되지 않는 값을 기준으로 삼아야 합니다.

공통 항목의 데이터에 행 추가하기

엑셀과 파이썬을 함께 사용하기 위해서는 데이터 합치기와 달리 항목이 같은 데이터에 행을 추가해야합니다. 현재작업까지는 그다지 어렵지 않은 작업이므로 굳이 엑셀 파이썬을 이용할 필요는 없지만 추가해야 할 데이터가 많다면 엑셀 파이썬의 코드를 이용해 간단하게 여러 개의 데이터를 추가할 수 있습니다.

엑셀 파이썬에서는 데이터를 추가할 때도 pandas 라이브러리의 pd.concat 함수를 이용합니다. concat은 ‘연결하다’라는 의미의 ‘concatenate’의 약자로 pd.concat([“기존 DataFrame 변수명”, “연결할 변수명”]) 형식으로 대괄호([]) 안에 연결할 변수명을 쉼표(,)로 구분하여 순서대로 입력하면 됩니다.

이번 실습의 예제는 서울교통공사 지하역사 공기질 측정 정보를 연번이 100인 행을 기준으로 데이터를 [A1:I100] 영역과 [A102:I266] 영역으로 나눈 것입니다. 만약 다른 데이터를 활용할 경우 각각의 데이터는 첫 번째 행에 항목명이 있어야 합니다.

1. 각 데이터 영역을 DataFrame으로 생성하고 변수로 저장하기

1) [K1] 셀의 파이썬 코드 입력 창에 다음 코드를 입력하고 실행합니다.

2) [K1] 셀에 [A1:I100] 영역의 데이터가 변수 ‘df_base’로 저장됩니다.

3) [K3] 셀의 파이썬 코드 입력 창에 다음 코드를 입력하고 실행합니다.

4) [K3] 셀에 [A102:I266] 영역의 데이터가 변수 ‘df_concat’으로 저장됩니다.

2. pd.concat 함수를 이용해 데이터에 행 추가하기

1) [K5] 셀의 파이썬 코드 입력 창에 다음 코드를 입력하고 실행합니다.

2) [K5] 셀에 [A1:I100] 영역과 [A102:I266] 영역의 데이터가 변수 ‘all_df’로 저장됩니다. [K5] 셀이 선택된 상태에서 수식 입력 창의 메뉴 아이콘 중 ‘[  ]’, ‘123’을 차례로 클릭하면 연번 99 아래로 100번부터 263번까지 데이터가 추가된 것을 확인할 수 있습니다.

(이미지 내 진행 순서와 아이콘을 참고해주세요)

Part B – 데이터 가공하기

챗GPT로 차트 디자인 바꾸기

일상적인 업무에서 발생할 수 있는 상황을 가정하여 차트 디자인을 변경하는 방법을 알아보겠습니다. 예제 파일은 공공데이터포털의 ‘서울교통공사 지하철혼잡도 정보’로 요일, 노선별 혼잡도를 30분 간격으로 정리한 데이터입니다. 여기서는 평일 2호선의 데이터만 가공한 예제를 활용하여 실습하겠습니다.

혼잡도는 정원 대비 승차 인원으로, 빈 좌석이 없으면 혼잡도를 34%로 산정합니다.

1. DataFrame을 만들고 df 변수로 저장하기 

1) [BA1] 셀의 파이썬 코드 입력 창에 다음 코드를 입력하고 실행합니다.

2) [BA1] 셀에 [B1:AY40] 영역이 변수 ‘df’로 저장됩니다.

✅ Tip. [A] 열은 시간당 혼잡도를 기록한 시간 데이터로 여러 개의 하위 차트를 생성할 때는 오류가 발생할 수 있으므로 Dataframe에서 제외했습니다.

3) [BA3] 셀의 파이썬 코드 입력 창에 다음 코드를 입력하고 실행합니다.

4) [BA3] 셀을 마우스 오른쪽 버튼으로 클릭한 다음 [셀 위에 플롯 표시]를 선택하면 차트가 표시됩니다.

차트가 생성되었지만 데이터가 많아 기본 차트로는 전체 데이터를 파악하기 어렵습니다. 이번에는 챗GPT를 활용하여 새로운 차트를 생성하는 방법을 알아보겠습니다.

2. 챗GPT에 인포그래픽 차트 요청하기

Matplotlib와 seaborn 라이브러리에는 데이터 분석을 위한 범용적인 차트뿐만 아니라 정보를 시각화하는 데 유용한 인포그래픽(infographic) 차트도 포함되어 있습니다. 인포그래픽은 정보(Information)와 도표(Graphic)의 합성어로 복잡한 데이터를 시각적으로 표현하는 기법입니다.

인포그래픽 차트는 데이터 형태 등의 생성 조건이 까다로워 관련 지식이 부족하면 쉽게 구현하기 어려울 수 있습니다. 이번에는 챗GPT를 활용하여 인포그래픽 차트에서 자주 사용되는 생키(Sankey) 차트를 생성하는 방법을 알아보겠습니다.

1. 챗GPT에 생키 차트 코드와 데이터 요청하기

생키 차트는 고객의 이동이나 자원의 흐름을 시각화하는 유용한 인포그래픽 차트입니다. 최근에는 웹사이트로 유입된 고객이 어떤 서비스를 이용하는지 파악하는 용도로 자주 활용됩니다. 챗GPT에서 다음과 같이 파이썬 코드를 요청해 보겠습니다.

이 코드는 단순한 Sankey 차트를 생성합니다. flows는 각 흐름의 값이며, labels는 해당 흐름의 레이블입니다. orientations는 각 흐름의 방향을 나타냅니다. flows의 양수 값은 오른쪽으로 향하는 흐름을 의미하고, 음수 값은 왼쪽으로 향하는 흐름을 의미합니다.

이 예제에서는 ‘Input’은 오른쪽으로 향하고, ‘Process 1’, ‘Process 2’, ‘Output’은 왼쪽으로 향하도록 설정되어 있습니다. orientations에서 1은 위쪽으로 향하는 흐름을 나타내고, 0은 방향이 변하지 않음을 의미하며, -1은 아래쪽으로 향하는 흐름을 나타냅니다. 필요에 따라 flows, labels, orientations를 조정하여 차트를 원하는 대로 수정할 수 있습니다.

챗GPT의 답변에 따르면 생키 차트에서 labels, flows, orientations의 역할은 다음과 같습니다.

여기서는 공공데이터포털의 ‘제주특별자치도 세계자연유산지구 방문자현황’에서 관광지별 외국인 방문자 수를 가공한 예제를 사용합니다. 2022년 기준 제주시에서 외국인이 가장 많이 방문한 세계자연유산은 성산일출봉, 만장굴, 한라산, 거문오름 순입니다. 이에 따라 생키 차트 구성을 위한 데이터를 만들어 보면 다음과 같습니다.

[label] 열은 외국인 관광객과 대표 관광지 4곳입니다. [flows] 열은 각 관광지를 방문하는 외국인으로 차트의 가시성을 높이기 위해 상대적인 비율을 입력합니다. [orientations] 열은 화살표가 여러 방향으로 분산되도록 입력하였습니다.

2. DataFrame을 만들고 df 변수로 저장하기

1) [E1] 셀의 파이썬 코드 입력 창에 다음 코드를 입력하고 실행합니다.

2) [E1] 셀에 [A1:C6] 영역이 변수 ‘df ‘로 저장됩니다.

3. 차트를 생성하는 코드 붙여 넣기

1) [E3] 셀의 파이썬 코드 입력 창에 챗GPT가 생성한 코드 중 #데이터 프레임 생성을 제외한 나머지 부분을 붙여 넣고 코드를 실행합니다.

2) [E3] 셀을 마우스 오른쪽 버튼으로 클릭한 다음 [셀 위에 플롯 표시]를 선택하면 차트가 표시됩니다.

생성된 생키 차트는 외국인 관광객의 동선을 나타냅니다. 화살표를 이용해 외국인(foreign tourist)이 유입되면 오른쪽으로 이동하며 각 방향으로 분산되고 있습니다. 방문객 수는 화살표의 두께를 통해 많고 적음을 한눈에 파악할 수 있습니다.

엑셀 시트에서 화살표의 이름(labels), 두께(flows), 방향(orientations)을 수정하면 원하는 형태로 차트를 변경할 수 있습니다. 값을 변경한 후 다시 실행해 보세요.

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Leave a Comment