엑셀파일에서 데이터 추출 후 분석앱 제작

개요

오늘은 파이썬으로 엑셀파일을 분석해 데이터를 통계화 하고 박스차트로 표현하는 예제를 만들어 보겠습니다. 

예를 들면 어떤 운용중인 시스템이 여러 센서값을 주기적으로 엑셀파일에 기록하고 있고 이 데이터를 분석해 정상적인 상태인지 분석하는 업무가 있다고 가정해 보겠습니다.

대략적인 데이터 샘플을 엑셀로 만들어 보았습니다.

[sample data]

 

위 가상의 시스템은 매 10분마다 5개의 센서값을 측정해 엑셀에 기록하고 있습니다.

• Std : 정상 기준치

• Min : 최소 하한치

• Max : 최대 상한치

   

이 시스템이 24시간 365일 가동되며 매일 다른 엑셀파일에 기록된다면, 

매번 이 파일들을 합치고 분석하는 단순 반복작업이 필요합니다.

언제쯤 상한치, 하한치를 벗어나 문제가 있었는지를 파악하고, 전반적인 시스템 통계치를 당연히 관리하고 싶을 것입니다.

물론 파일이 엑셀이 아닌 CSV, 또는 다른 형태일 수도 있고 센서의 수량이나 기록주기도 다를 수 있지만 그것은 크게 중요하지 않습니다.

기본적인 틀만 비슷하다면 모두 자동화 가능한 범주에 속하는 문제입니다.

사실, 일정규모 이상의 제조업에서는 위 같은 상황을 실시간으로 모니터링하고, 문제 (불량 등) 를 기록하고 분석하는 시스템이 이미 설치되어 있으리라 생각합니다.

파이썬 업무자동화

아직 자동화에 대한 준비가 덜 된 분들을 위해 Python, PyQt5, Matplotlib, Pandas를 활용해 자동화 앱을 만들어 보았습니다.

결과부터 먼저 동영상으로 살펴보겠습니다.

주요 기능은 아래와 같습니다.

• 여러개의 엑셀파일 한꺼번에 합치기
  

• 로드된 데이터마다 별도의 분석창탭 열기

• 박스차트로 평균, 상,하한치 표시

• 요약 통계치 표로 제공

   

엑셀파일을 불러와 표로 Raw 데이터 표시 화면

[Load data]

Raw 데이터 분석창

[Analysis data]

 

소스코드

총 3개의 소스코드 파일로 구성.

• Home.py (main)
  

• Data.py

• Analysis.py 
  

 

아래 모든 소스코드는 직접 작성하였으며,

이어지는 설명은 구글 대화형 AI 'Bard' 에게 코드를 제공하고 Bard가 작성한 내용을 제가 검수해 업로드 되었습니다.

코드를 작성하는 일은 제겐 쉽고 즐겁지만, 그것을 글로 설명하는 일은 많은 시간이 소요되고 사실 창작의 재미는 없습니다.

Bard 는 코드를 보여주면 프로그래머의 의도를 파악해 쉽게 설명자료를 만들어 줍니다.

( 물론 그 반대도 가능합니다)

 

샘플 엑셀파일 링크

샘플1

샘플2

 

Home.py 소스코드

from PyQt5.QtWidgets import QApplication, QTabWidget, QTabBar, QToolButton, QButtonGroup
from PyQt5.QtGui import QIcon
from Data import Data_Widget
from Analysis import Analysis_Widget
import sys

class Home(QTabWidget):    
    def __init__(self):
        super().__init__()
        self.setWindowTitle('Ocean Coding School')
        self.resize(1200,800)
        
        dw = Data_Widget(self)
        self.addTab(dw, dw.windowTitle())
        
        self.btnGroup = QButtonGroup()    
        self.btnGroup.buttonClicked.connect(self.onTabClose)
        
        self.num = 1;
        
    def onAnalysis(self, df):
        aw = Analysis_Widget(self, df)        
        idx = self.addTab(aw, aw.windowTitle())
        
        icon = QIcon('close-icon.png')
        btn = QToolButton(aw)
        btn.setIcon(icon)        
        self.btnGroup.addButton(btn, idx)
        self.tabBar().setTabButton(idx, QTabBar.RightSide, btn)
        self.setCurrentWidget(aw)
        
    def onTabClose(self, btn):        
        idx = self.btnGroup.id(btn)        
        self.removeTab(idx)
        self.btnGroup.removeButton(btn)
        
        # reset index for btngroup
        for i, btn in enumerate(self.btnGroup.buttons()):
            self.btnGroup.setId(btn, i+1)        
        

if __name__ == '__main__':
    app = QApplication(sys.argv)
    w = Home()
    w.show()
    sys.exit(app.exec_())

Home 클래스

`Home` 클래스는 데이터 분석용 앱의 홈 화면을 나타냅니다. `QTabWidget`을 상속받아 탭 기반 UI를 제공합니다.

init() 함수

`init()` 함수는 `Home` 클래스의 생성자 함수입니다. 창의 제목, 크기를 설정하고 데이터 탭을 추가합니다. 또한 `QButtonGroup`을 생성하고 `onTabClose()` 함수에 연결합니다.

`setWindowTitle()` 함수는 창의 제목을 설정합니다. `resize()` 함수는 창의 크기를 설정합니다. `addTab()` 함수는 탭을 추가합니다. `QButtonGroup`은 탭의 닫기 버튼을 관리합니다. `buttonClicked()` 시그널은 닫기 버튼이 클릭될 때 발생합니다. `onTabClose()` 함수에 연결하여 탭을 닫을 수 있습니다.

onAnalysis() 함수

`onAnalysis()` 함수는 분석 탭을 추가하는 함수입니다. `Analysis_Widget` 클래스의 인스턴스를 생성하고 탭으로 추가합니다. 또한 탭의 닫기 버튼을 생성하고 탭 바에 추가합니다. 마지막으로 현재 탭을 분석 탭으로 설정합니다.

`Analysis_Widget` 클래스는 분석 탭을 나타냅니다. `addTab()` 함수는 탭을 추가합니다. `icon()` 함수는 닫기 버튼의 아이콘을 설정합니다. `setTabButton()` 함수는 탭 바의 닫기 버튼을 설정합니다. `setCurrentWidget()` 함수는 현재 탭을 설정합니다.

onTabClose() 함수

`onTabClose()` 함수는 탭을 닫는 함수입니다. `QButtonGroup`에서 탭의 인덱스를 가져와 탭을 닫습니다. 또한 닫기 버튼을 버튼 그룹에서 제거합니다. 마지막으로 버튼 그룹의 모든 버튼의 인덱스를 재설정합니다.

`id()` 함수는 버튼 그룹의 버튼 ID를 가져옵니다. `removeTab()` 함수는 탭을 닫습니다. `removeButton()` 함수는 버튼을 버튼 그룹에서 제거합니다. `setId()` 함수는 버튼의 ID를 설정합니다.

main() 함수

`main()` 함수는 앱을 실행하는 함수입니다. `QApplication` 객체를 생성하고 `Home` 객체를 생성합니다. 창을 표시하고 애플리케이션을 종료합니다.

 

Data.py 소스코드

from PyQt5.QtWidgets import (QWidget, QPushButton, QTableWidget, QFileDialog,
                             QTableWidgetItem, QHBoxLayout, QVBoxLayout, QHeaderView)
from PyQt5.QtCore import pyqtSignal
from threading import Thread, Lock
import time
import pandas as pd


class Data_Widget(QWidget):
    
    tw_signal = pyqtSignal(int, int, str)
    analysis_signal = pyqtSignal(pd.DataFrame)

    def __init__(self, h):
        super().__init__()
        self.home = h
        self.setWindowTitle('Data')        
        
        self.analysis_signal.connect(self.home.onAnalysis)
        
        self.initUi()        
        
    def initUi(self):
        vbox = QVBoxLayout()
        hbox = QHBoxLayout()
        self.pb1 = QPushButton('open excel', self) 
        self.pb2 = QPushButton('analysis data', self)
        self.pb3 = QPushButton('delete data', self)
        
        hbox.addWidget(self.pb1)
        hbox.addWidget(self.pb2)
        hbox.addWidget(self.pb3)
        
        self.tw = QTableWidget()        
        self.tw_signal.connect(self.updateTable)
        
        vbox.addLayout(hbox)
        vbox.addWidget(self.tw)
        self.setLayout(vbox)
        
        #signal
        self.pb1.clicked.connect(self.onOpen)
        self.pb2.clicked.connect(self.onAnalysis)
        self.pb3.clicked.connect(self.onDelete)     
        
    def onOpen(self):
        path, ext = QFileDialog.getOpenFileNames(self, '', '', 'Excel Files(*.xlsx *.xls)')

        df_list = []
        for p in path:
            with pd.ExcelFile(p) as xls:
                for sn in xls.sheet_names:
                    df = pd.read_excel(xls, sn, na_values=['NA'])
                    #print(df.describe())
                    df_list.append(df.iloc[3:])
        
        if df_list:            
            self.df = pd.concat(df_list, ignore_index=True)
            col = len(self.df.columns)        
            row = len(self.df.index)
            
            #print(row, col)
            self.tw.setColumnCount(col)
            self.tw.setRowCount(row)
            self.tw.setHorizontalHeaderLabels( self.df.columns )   
            self.tw.setAlternatingRowColors(True)    
            
            # column width
            # header = self.tw.horizontalHeader()
            # header.setSectionResizeMode(QHeaderView.ResizeToContents)
            
            self.lock = Lock()
            self.t = Thread(target=self.writeTable, args=(self.df, row, col))
            self.t.start()
                    
    def writeTable(self, df, row, col):         
        for r in range(row):
            for c in range(col):
                self.lock.acquire()                
                self.tw_signal.emit(r, c, str(df.iloc[r][c]))        
        
    def updateTable(self, row, col, item):        
        self.tw.setItem(row, col, QTableWidgetItem(item))
        self.lock.release()        
        
    def onDelete(self):
        self.tw.clear()
    
    def onAnalysis(self):
        if hasattr(self, 'df'):
            self.analysis_signal.emit(self.df)
            

init() 함수

`init()` 함수는 `Data_Widget` 클래스의 생성자 함수입니다. 창의 제목을 설정하고, 버튼을 생성하고, 테이블 위젯을 생성합니다. 또한, `tw_signal`과 `analysis_signal`을 연결합니다.

`setWindowTitle()` 함수는 창의 제목을 설정합니다.

`analysis_signal.connect()` 함수는 `analysis_signal`과 `home.onAnalysis` 함수를 연결합니다. `home.onAnalysis` 함수는 데이터 프레임을 분석하는 함수입니다.

initUi() 함수

`initUi()` 함수는 위젯을 배치하는 함수입니다. 수평 레이아웃과 수직 레이아웃을 생성하고, 버튼을 추가합니다. 또한, 테이블 위젯을 추가하고, `tw_signal`을 연결합니다.

`setLayout()` 함수는 레이아웃을 위젯에 설정합니다.

onOpen() 함수

먼저, QFileDialog을 사용하여 파일 열기 대화 상자를 표시합니다. 사용자가 파일을 선택하면, path와 ext에 선택한 파일의 경로와 확장자를 저장합니다.

다음으로, for 루프를 사용하여 선택한 파일의 모든 시트를 순회합니다. 각 시트에서 pd.read_excel() 함수를 사용하여 데이터 프레임을 생성합니다.

pd.read_excel() 함수는 Excel 파일에서 데이터를 읽고, 데이터 프레임으로 반환합니다. na_values 인수를 사용하여 결측값을 지정할 수 있습니다.

df_list에 생성한 데이터 프레임을 추가합니다.

if 문으로 df_list가 비어 있지 않은지 확인합니다. df_list가 비어 있지 않으면, 다음과 같은 작업을 수행합니다.

• pd.concat() 함수를 사용하여 df_list를 하나의 데이터 프레임으로 결합합니다.

• len() 함수를 사용하여 데이터 프레임의 열과 행의 수를 계산합니다.

• setColumnCount() 함수를 사용하여 테이블 위젯의 열 수를 설정합니다.

• setRowCount() 함수를 사용하여 테이블 위젯의 행 수를 설정합니다.

• setHorizontalHeaderLabels() 함수를 사용하여 테이블 위젯의 열 헤더를 설정합니다.

• setAlternatingRowColors() 함수를 사용하여 테이블 위젯의 행을 교대로 배경색을 변경합니다.

• lock 객체를 생성합니다.

• writeTable() 함수를 실행하기 위한 스레드를 생성합니다.

• 스레드를 시작합니다.

 

writeTable() 함수

`writeTable()` 함수는 Excel 파일에서 읽은 데이터를 테이블 위젯에 표시하는 함수입니다. `tw_signal`을 사용하여 데이터를 테이블 위젯에 전달합니다.

`tw_signal.emit()` 함수는 `tw_signal`에 연결된 슬롯을 호출합니다. 이 경우, `updateTable()` 함수가 호출됩니다.

updateTable() 함수

`updateTable()` 함수는 `tw_signal`에서 받은 데이터를 테이블 위젯에 표시하는 함수입니다.

`setItem()` 함수는 테이블 위젯의 특정 위치에 아이템을 추가합니다.

onDelete() 함수

`onDelete()` 함수는 테이블 위젯의 데이터를 삭제하는 함수입니다.

`clear()` 함수는 테이블 위젯의 모든 데이터를 삭제합니다.

onAnalysis() 함수

`onAnalysis()` 함수는 데이터 프레임을 분석하는 함수입니다.

`hasattr()` 함수는 객체에 속성이 있는지 확인하는 함수입니다. `analysis_signal.emit()` 함수는 `analysis_signal`에 연결된 슬롯을 호출합니다. 이 경우, `home.onAnalysis()` 함수가 호출됩니다.

전체적으로, `Data_Widget` 클래스는 Excel 파일에서 데이터를 읽고, 테이블 위젯에 표시하고, 데이터 프레임을 분석하는 기능을 제공합니다.

 

Analysis.py 소스코드

from PyQt5.QtWidgets import QWidget, QVBoxLayout, QHBoxLayout, QTableWidget, QTableWidgetItem
from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm

class Analysis_Widget(QWidget):

    def __init__(self, h, df):
        super().__init__()
        self.home = h
        self.df = df
        self.setWindowTitle('Analysis')
        self.initUi()
        self.drawChart()
        self.initTable()
        
    def initUi(self):
        # Windows font setting
        path = 'C:/Windows/Fonts/malgun.ttf'
        font = fm.FontProperties(fname=path, size=10).get_name()
        plt.rc('font', family=font)
        # macOS font setting
        #plt.rc('font', family='AppleGothic')
        
        self.fig = plt.Figure()
        self.canvas = FigureCanvasQTAgg(self.fig)       
        
        self.tw = QTableWidget()
        
        hbox = QHBoxLayout()
        hbox.addWidget(self.canvas, 5)
        hbox.addWidget(self.tw ,5)        
        
        vbox = QVBoxLayout()
        vbox.addLayout(hbox)

        self.setLayout(vbox)
        
    def drawChart(self):
        self.fig.clear()
        df = self.df.set_index(self.df.columns[0], drop=True, inplace=False)
        ax = self.fig.subplots()
        #ax.set_title('test')        
        bp = ax.boxplot(df,
                        labels=df.columns,
                        boxprops= dict(linewidth=2.0, color='blue'),
                        whiskerprops=dict(linestyle='--',linewidth=2.0, color='green'))
        ax.legend(bp)
        #ax.set_ylim(0,10)
        self.canvas.draw()
        
    def initTable(self):
        df = self.df.describe()        
        df.insert(0,'',None)
        row = len(df.index)
        col = len(df.columns)
        self.tw.setColumnCount(col)
        self.tw.setRowCount(row)        
        
        self.tw.setHorizontalHeaderLabels(df.columns)
        
        for r in range(row):
            self.tw.setItem(r, 0, QTableWidgetItem(df.index[r]))
        
        for r in range(row):
            for c in range(1, col):
                self.tw.setItem(r, c, QTableWidgetItem( f'{df.iloc[r, c]:.2f}' ) )
                

init() 함수

`init()` 함수는 `Analysis_Widget` 클래스의 생성자 함수입니다. 창의 제목을 설정하고, 테이블 위젯과 그래프 위젯을 생성합니다. 또한, `drawChart()` 함수와 `initTable()` 함수를 호출합니다.

`setWindowTitle()` 함수는 창의 제목을 설정합니다.

`drawChart()` 함수와 `initTable()` 함수는 나중에 설명하겠습니다.

initUi() 함수

`initUi()` 함수는 위젯을 배치하는 함수입니다. Windows의 경우, `plt.rc('font', family=font)` 함수를 사용하여 Windows의 기본 글꼴을 설정합니다. macOS의 경우, `plt.rc('font', family='AppleGothic')` 함수를 사용하여 macOS의 기본 글꼴을 설정합니다.

`plt.Figure()` 함수를 사용하여 그림을 생성하고, `FigureCanvasQTAgg()` 함수를 사용하여 그림을 캔버스에 표시합니다.

`QTableWidget()` 함수를 사용하여 테이블 위젯을 생성합니다.

`QHBoxLayout()` 함수를 사용하여 캔버스와 테이블 위젯을 수평으로 배치하고, `QVBoxLayout()` 함수를 사용하여 레이아웃을 위젯에 설정합니다.

drawChart() 함수

`drawChart()` 함수는 그래프를 그리는 함수입니다. 먼저, `self.fig.clear()` 함수를 사용하여 그림을 지웁니다. 그런 다음, `self.df.set_index()` 함수를 사용하여 데이터 프레임을 인덱스별로 정렬합니다. `boxplot()` 함수를 사용하여 데이터 프레임을 상자 그림으로 표시합니다. `ax.legend()` 함수를 사용하여 범례를 표시합니다. `self.canvas.draw()` 함수를 사용하여 그림을 캔버스에 다시 그립니다.

 

initTable() 함수

`initTable()` 함수는 테이블에 데이터를 표시하는 함수입니다. 먼저, `self.df.describe()` 함수를 사용하여 데이터 프레임의 기술 통계를 계산합니다. 그런 다음, `df.insert(0,'',None)` 함수를 사용하여 기술 통계에 인덱스 열을 추가합니다. `setColumnCount()` 함수를 사용하여 테이블 위젯의 열 수를 설정하고, `setRowCount()` 함수를 사용하여 테이블 위젯의 행 수를 설정합니다. 또한, 테이블 위젯의 열 헤더를 설정하고, 각 행의 셀에 데이터를 표시합니다.

`setHorizontalHeaderLabels()` 함수를 사용하여 테이블 위젯의 열 헤더를 설정합니다.

`setItem()` 함수를 사용하여 각 행의 셀에 데이터를 표시합니다.

전반적으로, `initTable()` 함수는 다음과 같은 단계를 통해 테이블에 데이터를 표시합니다.

1. 데이터 프레임의 기술 통계를 계산합니다.
2. 기술 통계에 인덱스 열을 추가합니다.
3. 테이블 위젯의 열 수를 설정합니다.
4. 테이블 위젯의 행 수를 설정합니다.
5. 테이블 위젯의 열 헤더를 설정합니다.
6. 각 행의 셀에 데이터를 표시합니다.

 

개발환경

• Windows 11 Pro 64bit, Visual Studio 2022 Community

• Python 3.9.13 (64bit)

• Pandas 2.0.3, Matplotlib 3.7.2, PyQt5 5.15.9

이상으로 모든 코드 분석을 마칩니다. 

감사합니다.