PyQT5 和使用多列过滤表 [英] PyQT5 and Filtering a Table using multiple columns

问题描述

我正在尝试制作一个 PyQt5 GUI 来以表格的形式显示 Pandas 数据框并提供列过滤选项，类似于 Microsoft Excel 过滤器.到目前为止，我设法采用了类似的

如上图所示，过滤列有两种方式:Regex Filter和点击每一列.然而，我需要帮助解决一个问题:当我过滤第二列时，当前应用的过滤器(正则表达式过滤器或列单击)消失了.我希望第二个过滤器为 AND，即满足第 1 列 AND 第 2 列的过滤器.

这是我的代码:

#!/usr/bin/env python#-*- 编码:utf-8 -*-从 PyQt5 导入 QtCore、QtGui、QtWidgets将熊猫导入为 pd类 PandasModel(QtCore.QAbstractTableModel):def __init__(self, df=pd.DataFrame(), parent=None):QtCore.QAbstractTableModel.__init__(self, parent=parent)self._df = df.copy()def toDataFrame(self):返回 self._df.copy()def headerData(self, section,orientation, role=QtCore.Qt.DisplayRole):如果角色 != QtCore.Qt.DisplayRole:返回 QtCore.QVariant()如果方向 == QtCore.Qt.Horizo​​ntal:尝试:返回 self._df.columns.tolist()[section]除了(索引错误，):返回 QtCore.QVariant()elif 方向 == QtCore.Qt.Vertical:尝试:# 返回 self.df.index.tolist()返回 self._df.index.tolist()[section]除了(索引错误，):返回 QtCore.QVariant()定义数据(自我，索引，角色=QtCore.Qt.DisplayRole):如果角色 != QtCore.Qt.DisplayRole:返回 QtCore.QVariant()如果不是 index.isValid():返回 QtCore.QVariant()返回 QtCore.QVariant(str(self._df.iloc[index.row(), index.column()]))def setData(self, index, value, role):row = self._df.index[index.row()]col = self._df.columns[index.column()]如果 hasattr(value, 'toPyObject'):# PyQt4 得到一个 QVariant值 = value.toPyObject()别的:# PySide 得到一个 unicodedtype = self._df[col].dtype如果 dtype != 对象:value = None if value == '' else dtype.type(value)self._df.set_value(row, col, value)返回真def rowCount(self, parent=QtCore.QModelIndex()):返回 len(self._df.index)def columnCount(self, parent=QtCore.QModelIndex()):返回 len(self._df.columns)定义排序(自我，列，顺序):colname = self._df.columns.tolist()[列]self.layoutAboutToBeChanged.emit()self._df.sort_values(列名，升序= 顺序== QtCore.Qt.AscendingOrder，就地=真)self._df.reset_index(inplace=True, drop=True)self.layoutChanged.emit()类 myWindow(QtWidgets.QMainWindow):def __init__(self, parent=None):super(myWindow, self).__init__(parent)self.centralwidget = QtWidgets.QWidget(self)self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)self.view = QtWidgets.QTableView(self.centralwidget)self.comboBox = QtWidgets.QComboBox(self.centralwidget)self.label = QtWidgets.QLabel(self.centralwidget)self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)self.gridLayout.addWidget(self.lineEdit, 0, 1, 1, 1)self.gridLayout.addWidget(self.view, 1, 0, 1, 3)self.gridLayout.addWidget(self.comboBox, 0, 2, 1, 1)self.gridLayout.addWidget(self.label, 0, 0, 1, 1)self.setCentralWidget(self.centralwidget)self.label.setText("正则过滤器")self.load_sites()self.comboBox.addItems(["{0}".format(col) for col in self.model._df.columns])self.lineEdit.textChanged.connect(self.on_lineEdit_textChanged)self.comboBox.currentIndexChanged.connect(self.on_comboBox_currentIndexChanged)self.horizo​​ntalHeader = self.view.horizo​​ntalHeader()self.horizo​​ntalHeader.sectionClicked.connect(self.on_view_horizo​​ntalHeader_sectionClicked)def load_sites(self):df = pd.DataFrame({'site_codes': ['01', '02', '03', '04'],'状态':['打开'，'打开'，'打开'，'关闭']，'位置':['东'，'北'，'南'，'东']，'data_quality': ['差', '中等', '高', '高']})self.model = PandasModel(df)self.proxy = QtCore.QSortFilterProxyModel(self)self.proxy.setSourceModel(self.model)self.view.setModel(self.proxy)self.view.resizeColumnsToContents()@QtCore.pyqtSlot(int)def on_view_horizo​​ntalHeader_sectionClicked(self, logicalIndex):self.logicalIndex = logicalIndexself.menuValues = QtWidgets.QMenu(self)self.signalMapper = QtCore.QSignalMapper(self)self.comboBox.blockSignals(真)self.comboBox.setCurrentIndex(self.logicalIndex)self.comboBox.blockSignals(真)valuesUnique = self.model._df.iloc[:, self.logicalIndex].unique()actionAll = QtWidgets.QAction("All", self)actionAll.triggered.connect(self.on_actionAll_triggered)self.menuValues.addAction(actionAll)self.menuValues.addSeparator()对于 enumerate(sorted(list(set(valuesUnique)))) 中的 actionNumber、actionName:action = QtWidgets.QAction(actionName, self)self.signalMapper.setMapping(action, actionNumber)action.triggered.connect(self.signalMapper.map)self.menuValues.addAction(action)self.signalMapper.mapped.connect(self.on_signalMapper_mapped)headerPos = self.view.mapToGlobal(self.horizo​​ntalHeader.pos())posY = headerPos.y() + self.horizo​​ntalHeader.height()posX = headerPos.x() + self.horizo​​ntalHeader.sectionPosition(self.logicalIndex)self.menuValues.exec_(QtCore.QPoint(posX, posY))@QtCore.pyqtSlot()def on_actionAll_triggered(self):filterColumn = self.logicalIndexfilterString = QtCore.QRegExp("",QtCore.Qt.CaseInsensitive,QtCore.QRegExp.RegExp)self.proxy.setFilterRegExp(filterString)self.proxy.setFilterKeyColumn(filterColumn)@QtCore.pyqtSlot(int)def on_signalMapper_mapped(self, i):stringAction = self.signalMapper.mapping(i).text()filterColumn = self.logicalIndexfilterString = QtCore.QRegExp( stringAction,QtCore.Qt.CaseSensitive,QtCore.QRegExp.FixedString)self.proxy.setFilterRegExp(filterString)self.proxy.setFilterKeyColumn(filterColumn)@QtCore.pyqtSlot(str)def on_lineEdit_textChanged(self, text):搜索 = QtCore.QRegExp(文本，QtCore.Qt.CaseInsensitive,QtCore.QRegExp.RegExp)self.proxy.setFilterRegExp(搜索)@QtCore.pyqtSlot(int)def on_comboBox_currentIndexChanged(self, index):self.proxy.setFilterKeyColumn(index)如果 __name__ == "__main__":导入系统app = QtWidgets.QApplication(sys.argv)main = myWindow()main.show()main.resize(800, 600)sys.exit(app.exec_())

解决方案

如果要实现自定义过滤过程，则必须重写 filterAcceptsRow 方法，获取每列的文本并验证它们是否满足条件，如果它们确实返回True，否则返回False.要重新计算过滤器，您必须调用 invalidateFilter 方法:

class CustomProxyModel(QtCore.QSortFilterProxyModel):def __init__(self, parent=None):super().__init__(parent)self._filters = dict()@财产def过滤器(自我):返回 self._filtersdef setFilter(self, expresion, column):如果表达式:self.filters[column] = 表达式self.filters 中的 elif 列:del self.filters[列]self.invalidateFilter()def filterAcceptsRow(self, source_row, source_parent):对于列，self.filters.items() 中的表达式:text = self.sourceModel().index(source_row, column, source_parent).data()正则表达式 = QtCore.QRegExp(表达式，QtCore.Qt.CaseInsensitive，QtCore.QRegExp.RegExp)如果 regex.indexIn(text) == -1:返回错误返回真

class myWindow(QtWidgets.QMainWindow):# ...def load_sites(self):# ...self.model = PandasModel(df)self.proxy = CustomProxyModel(self)self.proxy.setSourceModel(self.model)self.view.setModel(self.proxy)self.view.resizeColumnsToContents()打印(完成加载站点")# [e-mail]()def on_actionAll_triggered(self):filterColumn = self.logicalIndexself.proxy.setFilter("", filterColumn)@QtCore.pyqtSlot(int)def on_signalMapper_mapped(self, i):stringAction = self.signalMapper.mapping(i).text()filterColumn = self.logicalIndexself.proxy.setFilter(stringAction, filterColumn)@QtCore.pyqtSlot(str)def on_lineEdit_textChanged(self, text):self.proxy.setFilter(text, self.proxy.filterKeyColumn())@QtCore.pyqtSlot(int)def on_comboBox_currentIndexChanged(self, index):self.proxy.setFilterKeyColumn(index)

<小时>

另外:如果要更改 QHeaderView 的字体，则必须返回 headerData 中的字体，如下所示:

class PandasModel(QtCore.QAbstractTableModel):def __init__(self, df=pd.DataFrame(), parent=None):QtCore.QAbstractTableModel.__init__(self, parent=parent)self._df = df.copy()self.bolds = dict()def toDataFrame(self):返回 self._df.copy()def headerData(self, section,orientation, role=QtCore.Qt.DisplayRole):如果方向 == QtCore.Qt.Horizo​​ntal:如果角色== QtCore.Qt.DisplayRole:尝试:返回 self._df.columns.tolist()[section]除了(索引错误，):返回 QtCore.QVariant()elif 角色 == QtCore.Qt.FontRole:返回 self.bolds.get(section, QtCore.QVariant())elif 方向 == QtCore.Qt.Vertical:如果角色== QtCore.Qt.DisplayRole:尝试:# 返回 self.df.index.tolist()返回 self._df.index.tolist()[section]除了(索引错误，):返回 QtCore.QVariant()返回 QtCore.QVariant()def setFont(self, section, font):self.bolds[section] = 字体self.headerDataChanged.emit(QtCore.Qt.Horizo​​ntal, 0, self.columnCount())# ...

class myWindow(QtWidgets.QMainWindow):# [e-mail]()def on_actionAll_triggered(self):filterColumn = self.logicalIndexself.proxy.setFilter("", filterColumn)字体 = QtGui.QFont()self.model.setFont(filterColumn, font)@QtCore.pyqtSlot(int)def on_signalMapper_mapped(self, i):stringAction = self.signalMapper.mapping(i).text()filterColumn = self.logicalIndexself.proxy.setFilter(stringAction, filterColumn)字体 = QtGui.QFont()font.setBold(True)self.model.setFont(filterColumn, font)

I am trying to make a PyQt5 GUI to show a Pandas dataframe in the form of a table and provide column filtering options, similar to the Microsoft Excel filters. So far I managed to adopt a similar SO answer. Here is the picture of my table in the GUI:

As shown in the figure above, there are two ways to filter columns: the Regex Filter and clicking on each column. There is however a problem I need help to address: the currently applied filters (either regex filter or column click) disappear when I filter a second column. I want the second filter as AND, i.e. a filter that satisfies column 1 AND column 2.

Here is my code:

#!/usr/bin/env python
#-*- coding:utf-8 -*-

from PyQt5 import QtCore, QtGui, QtWidgets

import pandas as pd

class PandasModel(QtCore.QAbstractTableModel):
    def __init__(self, df=pd.DataFrame(), parent=None):
        QtCore.QAbstractTableModel.__init__(self, parent=parent)
        self._df = df.copy()

    def toDataFrame(self):
        return self._df.copy()

    def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
        if role != QtCore.Qt.DisplayRole:
            return QtCore.QVariant()

        if orientation == QtCore.Qt.Horizontal:
            try:
                return self._df.columns.tolist()[section]
            except (IndexError, ):
                return QtCore.QVariant()
        elif orientation == QtCore.Qt.Vertical:
            try:
                # return self.df.index.tolist()
                return self._df.index.tolist()[section]
            except (IndexError, ):
                return QtCore.QVariant()

    def data(self, index, role=QtCore.Qt.DisplayRole):
        if role != QtCore.Qt.DisplayRole:
            return QtCore.QVariant()

        if not index.isValid():
            return QtCore.QVariant()

        return QtCore.QVariant(str(self._df.iloc[index.row(), index.column()]))

    def setData(self, index, value, role):
        row = self._df.index[index.row()]
        col = self._df.columns[index.column()]
        if hasattr(value, 'toPyObject'):
            # PyQt4 gets a QVariant
            value = value.toPyObject()
        else:
            # PySide gets an unicode
            dtype = self._df[col].dtype
            if dtype != object:
                value = None if value == '' else dtype.type(value)
        self._df.set_value(row, col, value)
        return True

    def rowCount(self, parent=QtCore.QModelIndex()):
        return len(self._df.index)

    def columnCount(self, parent=QtCore.QModelIndex()):
        return len(self._df.columns)

    def sort(self, column, order):
        colname = self._df.columns.tolist()[column]
        self.layoutAboutToBeChanged.emit()
        self._df.sort_values(colname, ascending= order == QtCore.Qt.AscendingOrder, inplace=True)
        self._df.reset_index(inplace=True, drop=True)
        self.layoutChanged.emit()


class myWindow(QtWidgets.QMainWindow):
    def __init__(self, parent=None):
        super(myWindow, self).__init__(parent)
        self.centralwidget  = QtWidgets.QWidget(self)
        self.lineEdit       = QtWidgets.QLineEdit(self.centralwidget)
        self.view           = QtWidgets.QTableView(self.centralwidget)
        self.comboBox       = QtWidgets.QComboBox(self.centralwidget)
        self.label          = QtWidgets.QLabel(self.centralwidget)

        self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)
        self.gridLayout.addWidget(self.lineEdit, 0, 1, 1, 1)
        self.gridLayout.addWidget(self.view, 1, 0, 1, 3)
        self.gridLayout.addWidget(self.comboBox, 0, 2, 1, 1)
        self.gridLayout.addWidget(self.label, 0, 0, 1, 1)

        self.setCentralWidget(self.centralwidget)
        self.label.setText("Regex Filter")

        self.load_sites()
        self.comboBox.addItems(["{0}".format(col) for col in self.model._df.columns])

        self.lineEdit.textChanged.connect(self.on_lineEdit_textChanged)
        self.comboBox.currentIndexChanged.connect(self.on_comboBox_currentIndexChanged)

        self.horizontalHeader = self.view.horizontalHeader()
        self.horizontalHeader.sectionClicked.connect(self.on_view_horizontalHeader_sectionClicked)


    def load_sites(self):

        df = pd.DataFrame({'site_codes': ['01', '02', '03', '04'],
                           'status': ['open', 'open', 'open', 'closed'],
                           'Location': ['east', 'north', 'south', 'east'],
                           'data_quality': ['poor', 'moderate', 'high', 'high']})

        self.model = PandasModel(df)
        self.proxy = QtCore.QSortFilterProxyModel(self)
        self.proxy.setSourceModel(self.model)
        self.view.setModel(self.proxy)
        self.view.resizeColumnsToContents()

    @QtCore.pyqtSlot(int)
    def on_view_horizontalHeader_sectionClicked(self, logicalIndex):

        self.logicalIndex   = logicalIndex
        self.menuValues     = QtWidgets.QMenu(self)
        self.signalMapper   = QtCore.QSignalMapper(self)
        self.comboBox.blockSignals(True)
        self.comboBox.setCurrentIndex(self.logicalIndex)
        self.comboBox.blockSignals(True)

        valuesUnique = self.model._df.iloc[:, self.logicalIndex].unique()

        actionAll = QtWidgets.QAction("All", self)
        actionAll.triggered.connect(self.on_actionAll_triggered)
        self.menuValues.addAction(actionAll)
        self.menuValues.addSeparator()
        for actionNumber, actionName in enumerate(sorted(list(set(valuesUnique)))):
            action = QtWidgets.QAction(actionName, self)
            self.signalMapper.setMapping(action, actionNumber)
            action.triggered.connect(self.signalMapper.map)
            self.menuValues.addAction(action)
        self.signalMapper.mapped.connect(self.on_signalMapper_mapped)
        headerPos = self.view.mapToGlobal(self.horizontalHeader.pos())
        posY = headerPos.y() + self.horizontalHeader.height()
        posX = headerPos.x() + self.horizontalHeader.sectionPosition(self.logicalIndex)

        self.menuValues.exec_(QtCore.QPoint(posX, posY))

    @QtCore.pyqtSlot()
    def on_actionAll_triggered(self):
        filterColumn = self.logicalIndex
        filterString = QtCore.QRegExp(  "",
                                        QtCore.Qt.CaseInsensitive,
                                        QtCore.QRegExp.RegExp
                                        )

        self.proxy.setFilterRegExp(filterString)
        self.proxy.setFilterKeyColumn(filterColumn)

    @QtCore.pyqtSlot(int)
    def on_signalMapper_mapped(self, i):
        stringAction = self.signalMapper.mapping(i).text()
        filterColumn = self.logicalIndex
        filterString = QtCore.QRegExp(  stringAction,
                                        QtCore.Qt.CaseSensitive,
                                        QtCore.QRegExp.FixedString
                                        )

        self.proxy.setFilterRegExp(filterString)
        self.proxy.setFilterKeyColumn(filterColumn)

    @QtCore.pyqtSlot(str)
    def on_lineEdit_textChanged(self, text):
        search = QtCore.QRegExp(    text,
                                    QtCore.Qt.CaseInsensitive,
                                    QtCore.QRegExp.RegExp
                                    )

        self.proxy.setFilterRegExp(search)

    @QtCore.pyqtSlot(int)
    def on_comboBox_currentIndexChanged(self, index):
        self.proxy.setFilterKeyColumn(index)


if __name__ == "__main__":
    import sys
    app  = QtWidgets.QApplication(sys.argv)
    main = myWindow()
    main.show()
    main.resize(800, 600)
    sys.exit(app.exec_())

解决方案

If you want to implement a custom filtering process then you must override the filterAcceptsRow method, obtain the texts of each column and verify if they meet the condition, if they do return True, otherwise False. To recalculate the filter you must call the invalidateFilter method:

class CustomProxyModel(QtCore.QSortFilterProxyModel):
    def __init__(self, parent=None):
        super().__init__(parent)
        self._filters = dict()

    @property
    def filters(self):
        return self._filters

    def setFilter(self, expresion, column):
        if expresion:
            self.filters[column] = expresion
        elif column in self.filters:
            del self.filters[column]
        self.invalidateFilter()

    def filterAcceptsRow(self, source_row, source_parent):
        for column, expresion in self.filters.items():
            text = self.sourceModel().index(source_row, column, source_parent).data()
            regex = QtCore.QRegExp(
                expresion, QtCore.Qt.CaseInsensitive, QtCore.QRegExp.RegExp
            )
            if regex.indexIn(text) == -1:
                return False
        return True

class myWindow(QtWidgets.QMainWindow):
    # ...

    def load_sites(self):
        # ...

        self.model = PandasModel(df)
        self.proxy = CustomProxyModel(self)
        self.proxy.setSourceModel(self.model)
        self.view.setModel(self.proxy)
        self.view.resizeColumnsToContents()
        print("finished loading sites")

    # ...

    @QtCore.pyqtSlot()
    def on_actionAll_triggered(self):
        filterColumn = self.logicalIndex
        self.proxy.setFilter("", filterColumn)

    @QtCore.pyqtSlot(int)
    def on_signalMapper_mapped(self, i):
        stringAction = self.signalMapper.mapping(i).text()
        filterColumn = self.logicalIndex
        self.proxy.setFilter(stringAction, filterColumn)

    @QtCore.pyqtSlot(str)
    def on_lineEdit_textChanged(self, text):
        self.proxy.setFilter(text, self.proxy.filterKeyColumn())

    @QtCore.pyqtSlot(int)
    def on_comboBox_currentIndexChanged(self, index):
        self.proxy.setFilterKeyColumn(index)

---

Plus: If you want to change the font of the QHeaderView then you must return the font in the headerData as shown below:

class PandasModel(QtCore.QAbstractTableModel):
    def __init__(self, df=pd.DataFrame(), parent=None):
        QtCore.QAbstractTableModel.__init__(self, parent=parent)
        self._df = df.copy()
        self.bolds = dict()

    def toDataFrame(self):
        return self._df.copy()

    def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
        if orientation == QtCore.Qt.Horizontal:
            if role == QtCore.Qt.DisplayRole:
                try:
                    return self._df.columns.tolist()[section]
                except (IndexError,):
                    return QtCore.QVariant()
            elif role == QtCore.Qt.FontRole:
                return self.bolds.get(section, QtCore.QVariant())
        elif orientation == QtCore.Qt.Vertical:
            if role == QtCore.Qt.DisplayRole:
                try:
                    # return self.df.index.tolist()
                    return self._df.index.tolist()[section]
                except (IndexError,):
                    return QtCore.QVariant()
        return QtCore.QVariant()

    def setFont(self, section, font):
        self.bolds[section] = font
        self.headerDataChanged.emit(QtCore.Qt.Horizontal, 0, self.columnCount())
    # ...

class myWindow(QtWidgets.QMainWindow):
    # ...
   @QtCore.pyqtSlot()
    def on_actionAll_triggered(self):
        filterColumn = self.logicalIndex
        self.proxy.setFilter("", filterColumn)
        font = QtGui.QFont()
        self.model.setFont(filterColumn, font)

    @QtCore.pyqtSlot(int)
    def on_signalMapper_mapped(self, i):
        stringAction = self.signalMapper.mapping(i).text()
        filterColumn = self.logicalIndex
        self.proxy.setFilter(stringAction, filterColumn)
        font = QtGui.QFont()
        font.setBold(True)
        self.model.setFont(filterColumn, font)

这篇关于PyQT5 和使用多列过滤表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！