Before writing the data summary and grouping tool, sort out the requirements. Requirement 1: excel data can be displayed in the list. Requirement 2: it supports data summary by column and multi column grouping summary. Requirement 3: be able to preview the data after grouping summary, and finally save the grouped summary data to a new excel data file.

[read the full text]

The way to obtain the complete source code is at the end of the article. If necessary, you can download and use it yourself.

The following are the main third-party python modules. Different from the previous PyQt5 applications, a style module qdarkstyle is added this time. Finally, by adding this module directly to QApplication, it can be displayed as a black cool application. I personally like this style

'''Application operation Library'''
import sys
import os

'''Apply Style Library'''
from qdarkstyle import load_stylesheet_pyqt5

'''UI Interface library'''
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *

'''Data extraction Library'''
import pandas as pd

Write UI interface component layout, UI layout function init_ui(). init_ The overall content of UI () function is posted below, and big guys can play it at will according to their own needs.

  def init_ui(self):
        # Title and icon settings
        self.setWindowTitle('Excel The official account of data collection tool:[Python concentration camp]')
        self.setWindowIcon(QIcon(':/data_sum.ico'))

        # Initialize horizontal layout
        hbox = QHBoxLayout()

        # Initialize grid layout
        grid = QGridLayout()

        self.data_source_text = QLineEdit()
        self.data_source_text.setReadOnly(True)

        self.data_source_btn = QPushButton()
        self.data_source_btn.setText('data')
        self.data_source_btn.clicked.connect(self.data_source_btn_click)

        self.data_group_column = QLabel()
        self.data_group_column.setText('Set grouping column')

        self.data_group_column_text = QLineEdit()
        self.data_group_column_text.setPlaceholderText('Column name 1,Column name 2...')

        self.save_dir_text = QLineEdit()
        self.save_dir_text.setReadOnly(True)

        self.save_dir_btn = QPushButton()
        self.save_dir_btn.setText('route')
        self.save_dir_btn.clicked.connect(self.save_dir_btn_click)

        self.view_data_btn = QPushButton()
        self.view_data_btn.setText('preview data ')
        self.view_data_btn.clicked.connect(self.view_data_btn_click)

        self.save_data_btn = QPushButton()
        self.save_data_btn.setText('preservation')
        self.save_data_btn.clicked.connect(self.save_data_btn_click)

        grid.addWidget(self.data_source_text, 0, 0, 1, 2)
        grid.addWidget(self.data_source_btn, 0, 2, 1, 1)
        grid.addWidget(self.data_group_column, 1, 0, 1, 1)
        grid.addWidget(self.data_group_column_text, 1, 1, 1, 2)

        grid.addWidget(self.save_dir_text, 2, 0, 1, 2)
        grid.addWidget(self.save_dir_btn, 2, 2, 1, 1)
        grid.addWidget(self.view_data_btn, 3, 0, 1, 2)
        grid.addWidget(self.save_data_btn, 3, 2, 1, 1)

        self.table_view = QTableView()
        self.table_view.setFixedWidth(500)
        self.table_view.setFixedHeight(100)

        hbox.addWidget(self.table_view)
        hbox.addLayout(grid)

        self.setLayout(hbox)

There are four slot functions in total, which are the following.

save_data_btn_click: save the grouped and summarized DataFrame data directly.

data_source_btn_click: used to load excel files to be grouped and summarized, and display the loaded DataFrame data directly on the QTableView component, so that you can see the loaded original data in real time.

save_dir_btn_click: click the slot function triggered when selecting the storage path, which is used to call QFileDialog to select the file path.

view_data_btn_click: call to preview the data after grouping and summary, and display the grouped data on the window.

Slot function data_source_btn_click to load excel source data.

    def data_source_btn_click(self):
        xlsx_file = QFileDialog.getOpenFileName(self, 'Select file', self.cwd, 'Excel File(*.xlsx)')
        self.data_source_text.setText(xlsx_file[0])
        self.data_frame = pd.read_excel(self.data_source_text.text().strip())
        print(self.data_frame)
        model = TableModelView(self.data_frame)
        self.table_view.setModel(model)

Slot function save_data_btn_click to save the final excel data.

    def save_data_btn_click(self):
        dir = self.save_dir_text.text().strip()
        self.data_frame_group.to_excel(dir + 'group_data.xlsx',sheet_name='Data information summary')

Slot function view_data_btn_click to preview the data grouped and summarized.

    def view_data_btn_click(self):
        columns = self.data_group_column_text.text().strip()
        column_list = []
        if columns != '':
            column_list = columns.split(',')
        self.data_frame_group = self.data_frame.groupby(column_list, as_index=False).sum()
        print(self.data_frame_group)
        model = TableModelView(self.data_frame_group)
        self.table_view.setModel(model)

Slot function save_dir_btn_click to store the file selection.

    def save_dir_btn_click(self):
        save_path = QFileDialog.getExistingDirectory(self, 'Select Folder', self.cwd)
        self.save_dir_text.setText(save_path + '/')

Finally, displaying the list data in the main code block always uses a QTableView component custom TableModelView. Because of the length of the problem, we can not share it here. You can download the source code from the official account.

[welfare] reply to the Excel data collector in official account and get the complete source code.

[selected from previous periods]

● exception: store MYSQL escape data to ensure the accuracy of data storage

● make a small alarm clock and do things according to the plan

● use pyqt5 the date control to make a small calendar for easy viewing

● synthesize tens of thousands of pictures into one picture to make a great mosaic!

● gadgets convert mp3 audio format to wav format in batches

● use pywebio module directly to realize web page without H5

● what can python callback functions do?

● solve the problem that external resources cannot be loaded during pyinstaller packaging

● how to implement a data management system on the console (including addition, deletion, modification and query of MYSQL database)

● pyqt5 made a QR code generator, which has been packaged into exe executable program

● self made document format converter, support txt/.xlsx/.csv format conversion

● how can PyPDF2 extract and save as a PDF file according to the PDF page number?

● PyQt5 GUI: Baidu picture downloader (source code attached at the end of the text)

● romantic turtle, a Christmas tree for programmers!