[2024-08-27] python 으로 excel(엑셀) 파일 다룰 때 주의점

안녕하세요. 클스 입니다.

오늘은 python으로 엑셀 파일을 다룰 때 경험을 써보겠습니다.

----------- 환경 ------------

인텔 맥 프로 2019 16인치, 메모리 32기가

-----------------------------

약 70만건 대량의 데이터를 엑셀에서 불러옵니다. 약 10분이 걸립니다.

그래서 매번 불러서 분석하기에 시간이 너무 많이 들어서 불러들인 엑셀을 df.to_csv 해서 저장합니다.

그 다음 부터는 csv를 부르니 엄청 빠릅니다. 30초

'''

# pip install openpyxl xlsxwriter pandas numpy plotly nbformat

poetry install

or

poetry add openpyxl xlsxwriter pandas numpy plotly nbformat

'''

import os

import pandas as pd

import numpy as np

import plotly.express as px

version = 'v3'

data_dir = os.path.expanduser('~/data')

installation_type_mapping_full = {

"0001": "ㅁㅁㅁ",

"0002": "ㅁㅁㅁ1",

... 보안상 생략 ....}

# "송달구분" 코드에 따른 맵핑 딕셔너리 생성

delivery_type_mapping = {

"0001": "ㅁㅁㅁ",

"0002": "ㅁㅁㅁ1",

... 보안상 생략 ....

}

excel_source_file1 = f'{data_dir}/7월_{version}.xlsx'

excel_source_file2 = f'{data_dir}/8월_{version}.xlsx'

# 주의할 점은 숫자 데이터가 "3,2322.00" 이렇게 문자형식으로 되어 있는 경우 불러올 때 숫자로 변환

# 작업이 필요합니다. 그리고 문자를 숫자로 읽으면 "0001"은 1로 되기 때문에 문자열로 읽게 합니다.

def load_excel_data(file_path:str, month:str) :

df = pd.read_excel(file_path, dtype={'CA': str, '설치': str, '송달구분': str, '설치유형': str})

df['설치유형명'] = df['설치유형'].map(installation_type_mapping_full)

df['송달구분명'] = df['송달구분'].map(delivery_type_mapping)

df['차액'] = df['차액'].replace({',': ''}, regex=True).astype(float).round(2)

df['(전)금액'] = df['(전)금액'].replace({',': ''}, regex=True).astype(float).round(2)

df['(후)금액'] = df['(후)금액'].replace({',': ''}, regex=True).astype(float).round(2)

df['(전)금액(10원절사)'] = (df['(전)금액'] // 10) * 10

df['(후)금액(10원절사)'] = (df['(후)금액'] // 10) * 10

df['이상고지금액'] = df['(후)금액(10원절사)'] - df['(전)금액(10원절사)']

df['이상고지'] = df.apply(lambda row: 'N' if (row['이상고지금액'] == 0) else 'Y', axis=1)

df['월구분'] = month

csv_file_path = file_path.replace('.xlsx', '.csv')

if not os.path.exists(csv_file_path):

df.to_csv(csv_file_path, index=False)

return

data_7mon_df = load_excel_data(excel_source_file1, '7월')

data_8mon_df = load_excel_data(excel_source_file2, '8월')

이제 csv로 저장된 파일을 불러와서 여러가지 분석을 하시면 됩니다.

def load_csv_data(file_path:str) :

df = pd.read_csv(file_path, dtype={'CA': str, '설치': str, '송달구분': str, '설치유형': str})

return df

여기서 원본 데이터를 계산한 필드가 여러개 추하해서 다시 엑셀로 저장합니다. 시간 오래걸려요 20분

그 다음에 분석 결과를 같은 엑셀 파일에 추가를 하면 시간이 정말 많이 걸립니다.

이유는 시트를 추가하려면 기존 파일을 읽어야 하는데 약 70만건이라 로딩하는 시간이 걸립니다.

그래서 분석할 전체 데이터를 a.xlsx 에 저장하고, 분석 요약 데이터는 b.xlsx 에 저장한 다음

엑셀 파일을 열어서 "이동/복사"를 하시는게 훨씬 바르게 작업됩니다.

# 파일이 없을 경우는 ExcelWriter에 파일명, engine을 하면됩니다.

df_result1_1 = df.groupby(['설치유형', '설치유형명', '이상고지', '월구분']).agg(이상고지_갯수=('CA', 'size'), 차액_합계=('이상고지금액', 'sum')).reset_index()

df_result1_1

with pd.ExcelWriter(excel_save_path, engine='openpyxl') as writer:

df_result1_1.to_excel(writer, sheet_name='CA_이상고지_전체_현황', index=False)

# 동일한 엑셀 파일에 시트를 추가하려면 mode='a', if_sheet_exists='replace' 를 추가하면

# 시트가 있으면 덮어쓰게 할 수 있습니다.

df_result1_1 = df_y.groupby(['CA', '설치유형', '설치유형명', '월구분']).agg(이상고지_갯수=('CA', 'size'), 차액_합계=('이상고지금액', 'sum')).reset_index()

df_result1_1

with pd.ExcelWriter(excel_save_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:

df_result1_1.to_excel(writer, sheet_name='CA_설치유형_현황', index=False)

주의할 점은 index를 저장하지 않도록 False 해주시는 것이 편합니다.

그리고 vscode에서 그래프, 챠트를 그릴 때, matplotlib 을 많이 사용하는데, plotly를 권장합니다.

import plotly.express as px

def draw_chart(df_plot, title):

df_filtered = df_plot[df_plot['도수'] > 0]

df_top_20 = df_filtered.sort_values(by='도수', ascending=False).head(20).sort_values(by='구간', ascending=True)

df_top_20['구간'] = df_top_20['구간'].astype(str)

df_top_20

fig = px.bar(df_top_20, x='구간', y='도수', title=f'{title} 도수 분포표', labels={'구간': '구간', '도수': '도수'}, text='도수')

fig_max_y = df_top_20['도수'].max() * 1.3 # 최대 도수 값보다 10% 더 크게 설정

fig.update_yaxes(tickformat=',', range=[0, fig_max_y])

fig.update_traces(texttemplate='%{text}', textposition='outside')

fig.show()

return df_top_20

아래와 같이 도수 분포표를 잘 그려줍니다. 한글도 잘 나오네요. 제가 폰트를 설치했는지도 모르겠네요

이상 클수 였습니다.